BUS522Unit6Assignment.pdf
Due Date: 11:59 p.m. EST, Sunday of Unit 6 Points: 100
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 changeschange 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-1would equal to March, Wt-2 would equal February and Wt-3 would equalJanuary.
o Ut-X = the units for the prior months, similar to how the weights work.
Requirements:
• Microsoft Excel file
Be sure to read the criteria by which your work will be evaluated before you start and again after before you submit.
BUS522 – Financial Tools for Managers
Firefly Shipping Model
Evaluation Rubric for Firefly Shipping Model Assignment
CRITERIA Deficient Needs Improvement
Proficient Exemplary
(0-4 Points) (16-18 Points) (20-24 Points) (25 Points) 3-Month WMA Missing, or
incomplete. Complete but showing inaccurate or static formula.
Complete and showing all or mostly correct.
All formulas are correct, formatting has been applied to outputs.
Weights Adjusted
Missing, or incomplete.
Complete but showing inaccurate or static formula.
Complete and showing all or mostly correct.
All formulas are correct, formatting has been applied to outputs.
(0-29 Points) (30-39 Points) (40-49 Points) (50 Points) Chart Graph missing
or showing the wrong data.
Graph has been included, but not well labeled and may be showing the incorrect data.
Graph presents the data in a readable format, adequate labeling.
Graph is clear, data is shown and is correct. All axes have been labeled with correct number formatting.
