In: Operations Management
Use Excel to calculate the values to fill in the empty boxes. Feel free to add additional tables and calculations as |
needed. |
Historical Demand Data 2012 to 2016: |
The table reproduced below is the demand data for a company (aggregated) for the previous five years. |
2012 |
2013 |
2014 |
2015 |
2016 |
|
Q1 |
11632 |
15034 |
16117 |
15565 |
16470 |
Q2 |
22509 |
26824 |
24169 |
20151 |
42858 |
Q3 |
21646 |
13314 |
14505 |
13392 |
19278 |
Q4 |
11355 |
10698 |
11176 |
10613 |
13934 |
Annual Demand |
67,142 |
65,870 |
65,967 |
59,721 |
92,540 |
Average Quarterly Demand |
16,785.50 |
16,467.50 |
16,491.75 |
14,930.25 |
23,135.00 |
Forecasting Using Moving Average Methods |
Using the historical demand data above, you are to determine the total annual demand forecast for 2016 and 2017 using: |
Ø the three-period moving average forecasting method |
Ø the three-period weighted moving average method with weights of .6, .3, and .1 |
Enter your forecast results in the following tables. |
2016 Annual Forecast Using a Moving Average |
2016 Annual Forecast Using a Weighted Moving Average |
|
63,852.67 |
62209.7 |
|
2017 Annual Forecast Using a Moving Average |
2017 Annual Forecast Using a Weighted Moving Average |
|
72742.67 |
80037 |
|
Calculate a Time Series Linear equation using the all of the above demand data: |
|||||||||||||||
Using the historical demand data for 2012 through 2016, create a linear equation with the year as the independent variable and the annual volume as the dependent variable. |
|||||||||||||||
Enter your linear equation in text from here: |
|||||||||||||||
Calculated 2017 Annual Forecast from Linear Equation:
|
|||||||||||||||
2012 |
2013 |
2014 |
2015 |
2016 |
2017 |
||||||||||
Actual Annual Demand |
67,142 |
65,870 |
65,967 |
59,721 |
92,540 |
||||||||||
Forecasted Annual Demand |
67,142 |
67142 |
66251.6 |
66052.38 |
61620.414 |
||||||||||
Forecast Error |
-1,272 |
-285 |
-6,331 |
30,920 |
|||||||||||
Values |
Seasonal Factor for each Quarter |
2017 Quarterly Forecast |
Quarter 1 |
0.89 |
|
Quarter 2 |
1.63 |
|
Quarter 3 |
0.98 |
|
Quarter 4 |
0.69 |
|
Totals |
4.19 |
What is the MAD value for the exponential smoothing forecast? Answer = |
What is the CFE value for the exponential smoothing forecast? Answer = |
What is the MAPE value for the exponential smoothing forecast? Answer = |
Forecasting using trend with regression: |
|
Calculate forecasts for 2017, 2016 and 2015 using a linear regression of the previous three actual demand values. |
|
(Hint: You will need to calculate three different linear equations.) |
2012 |
2013 |
2014 |
2015 |
2016 |
2017 |
||||
Actual Annual Demand |
67,142 |
65,870 |
65,967 |
59,721 |
92,540 |
slope |
-587.5 |
||
Forecasted Annual Demand |
67,142 |
67,142 |
66,252 |
66,052 |
61,620 |
intercept |
|||
Forecast Error |
-6,331 |
30,920 |
What is the MAD value for the trend with regression forecast? Answer = |
What is the CFE value for the trend with regression forecast? Answer = |
What is the MAPE value for the trend with regression forecast? Answer = |
rs
Formulas:
A10 =AVERAGE(C6:E6)
B10 =E6*0.6+D6*0.3+C6*0.1
E10 =AVERAGE(D6:F6)
F10 =F6*0.6+E6*0.3+D6*0.1
B13 =ROUND(INTERCEPT(B6:F6,B1:F1),2)&" + "&ROUND(SLOPE(B6:F6,B1:F1),2)&"X"
D15 =-8921657.8+4464.7*2017
C20 =B20+(B19-B20)*$F$17 copy to C20:F20
C21 =C19-C20 copy to C21:F21
C22 =ABS(C21) copy to C22:F22
C23 =C22/C19 copy to C23:F23
E25 =AVERAGE(C22:F22)
E26 =SUM(C21:F21)
E27 =AVERAGE(C23:F23)
B33 =-8921657.8+4464.7*B31 copy to B33:F33
C34 =C32-C33 copy to C34:F34
C35 =ABS(C34) copy to C35:F35
C36 =C35/C32 copy to C36:F36
E38 =AVERAGE(C35:F35)
E39 =SUM(C34:F34)
E40 =AVERAGE(C36:F36)