1.
(30 points) Using the data below,
a)
Use a 3-month moving average
model to forecast sales for periods 6-11.
b)
Use a 5-month moving average
model to forecast sales for periods 6-11.
c)
Use an exponential smoothing
model witha =.2 to forecast sales for periods 6-11.
Assume a forecast of 65,000 for period 5.
d)
Use an exponential smoothing
model witha =.6 to forecast sales for periods 6-11.
Assume a forecast of 65,000 for period 5.
e)
Compare the forecasts in
(a)-(d) for periods 6 – 10. Which is
best? Why? Support your answer with appropriate
statistics.
f)
Starting with your solution to part (d), complete a double smoothing
model for
periods 6 -10 for sales.
Again, assume a forecast of 65,000 for period 5.
Note: Do parts a-d, and
f using Excel.
Period Sales
1 $35,725
2 $47,180
3 $54,965
4 $63,220
5 $66,315
6 $57,730
7 $62,700
8 $60,025
9 $74,590
10 $83,900
2. See the posted Excel
file with UD tuition data from 2000 – 2014.
You have a
daughter who is currently in 7th grade. She hopes to attend UD right after she
finishes high school. Given the data in
the attached file, use a linear model to forecast her TOTAL associated 4-year
tuition and mandatory fees (rounded to the nearest $1,000). Use the data set that reflects your
residency status.
If
you use the ‘trendline’ option to generate the linear model, I’d suggest using
t as the independent variable. Otherwise, Excel will generate your trend line
equation using scientific notation and you’ll have to change the formatting to
get the exact number you need. 12 pts
Note: The data
does NOT include study abroad tuition and program fees. When you finish the problem, pick your jaw
up off the floor and proceed to Problem #3.
3. The
table below shows historical data regarding actual and projected inventory
levels. 8 pts
|
Period |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
|
Actual |
50 |
70 |
60 |
-20 |
-10 |
65 |
60 |
85 |
|
Forecasted |
40 |
65 |
70 |
80 |
70 |
75 |
50 |
80 |
a)
For
periods 1 – 8, on average, did the forecast underestimate or overestimate? And, by how many units, on average, did the
forecast underestimate /overestimate per period? You must justify your answer with
calculations.
b)
For
periods 1 – 8, the forecast deviated from actual by an average of _______ units
per period. You must justify your
answer with calculations.
c) In this particular case, which performance
measure would be best to evaluate various forecasting methods for monthly ending
inventory – BIAS, MAD or MSE? Justify
your answer.
EXTRA CREDIT (4 pts)
My policy is to provide no input/help on extra credit questions. Also, it’s all or nothing – full credit or
no credit.
You just took over as regional
manager for one of your company’s up-and-coming
products. The product went on
the market December 2013; it’s now January 2015. The product has been experiencing slow growth
(as expected). Exponential smoothing
with trend has been used to forecast monthly demand for the product during
these past 13 months. You’ve looked at
the forecasted vs actual for the past few months and the forecast seems to be
performing adequately.
One of your employees just told you
that she has discovered a typo in the initial December forecast. It was entered as 50,000 but should have
been 5,000. The forecast over the past
12 months has never been corrected.
What are you going to do about next
month’s forecast? (You must justify your course of action.)
