BUS522 Financial Tools for Managers
Unit 6 Assignment
Firefly Shipping Model
Overview:
Mal Foil the owner of Firefly Shipping has asked you to create a model to project the number of units they must be prepared to ship in the first month of the new year. Mal has supplied you with a template of what he would like to see.
Instructions:
• Download: Unit 06 Assignment – Firefly Shipping Model.xlsx
• Mal also has a specific set of requirements:
o use a 3-month weighted average to forecast January.
o see how the 3-month weighted average method would perform against historical data.
o see the 3-month weighted average charted out within the model.
o Mal is not sure how what weights he would like to use. Your model should allow him to adjust the weights, listed in G4-I4, and see these changes change the models output.
• The 3-month weighted average formula is listed below:
????????
= (????????−1∗????????−1)+(????????−2∗????????−2)+(????????−3∗????????−3)
(????????−1+????????−2+????????????−3)
o Ft = The forecasted values (column D)
o Wt-X = the weight for the prior months. For example, if Ft = April then, Wt-1 would equal to March, Wt-2 would equal February and Wt-3 would equal January.
o Ut-X = the units for the prior months, similar to how the weights work.
