Forecasting
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 with a =.2 to forecast sales for periods 6-11. Assume a forecast of 65,000 for period 5.
d) Use an exponential smoothing model with a =.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 OU tuition data from 2000 - 2014. You have a daughter who is currently in 7thgrade. She hopes to attend OU 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.
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.
|
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
|
- 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.
- For periods 1 - 8, the forecast deviated from actual by an average of _______ units per period. You must justify your answer with calculations.(Note- deviation can be negative or positive)
c) When forecasting inventory levels, a large forecast error in any month can lead to big shortages or large quantities sitting in warehouse. 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.
Attachment:- Copy of OU Tuition Data for Question 2.xlsx