Business Finance – Excel

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 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.
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.