Problem 3 (20) Forecast demands by using Excel. 1. Forecast demands with Simple
ID: 327838 • Letter: P
Question
Problem 3 (20) Forecast demands by using Excel. 1. Forecast demands with Simple Moving Average method. Compare the MSE when m3 and when m-7. Which one gives you better forecast result? Month Demand(t) 40 65 49 64 2. Forecast demands with Weight Moving Average method. When m-7, what is MSE and each optimal weight? (Use the solver.) 48 38 37 45 57 28 48 43 3. Forecast demands with Exponential Smoothing method. Assume that ft) of the first month is same as its demand. Find optimal weight and MSE. (Use the solver) 10 4. When you compare the 4 results above, which method works best for this data? What does the result imply to you? 12 13 14 15 16 17 18 19 20 27 65 58 48 *Must capture the Excel screen to show the results(Show cell numbers). All the needed formulas must be explained(Those ones repeating same formula can be presented only once.)-> If there is no formulas that shows how you get the results, the mark can be deducted even if the result gives you right answer. [Example] C7-(B3 *SHS2)-(B4*SHS3) D7- B7-C7, C7-Average(B4:B6) etc.Explanation / Answer
Answer 1:
The MSE is calculated with m=3 and m=7 as per the below table.
The Moving Average method is used for forecasting the demand for the month base don the average of the previous months as per MSE.
If MSE is m=3, then we need to take the average of last 3 periods to calculate the forecasing for the 4th period
If MSE is m=7, then we need to take the average of last 7 periods to calculate the forecasing for 8th period.
the table for each MSEis calculated as below
Sum of square of errors = 3260.11
No. of Periods = 17
MSE (m=3) = Sum of square of errors / no. of months
MSE (m=3) = 3260.11 / 17
MSE (m=3) = 191.77
Sum of square of errors = 2078.49
No. of Periods = 13
MSE (m=7) = Sum of square of errors / no. of months
MSE (m=7) = 2078.49 / 13
MSE (m=7) = 159.88
MSE (m=3) gives 191.77 and MSE (m=7) gives the forecast as 159.88. The later gives better forecast.
Month Demand Exponential Smmothening Forecast Error Square of Error 1 40 2 65 3 49 4 64 51.33 51.33 12.67 160.44 5 66 59.33 59.33 6.67 44.44 6 48 59.67 59.67 -11.67 136.11 7 38 59.33 59.33 -21.33 455.11 8 37 50.67 50.67 -13.67 186.78 9 45 41.00 41.00 4.00 16.00 10 57 40.00 40.00 17.00 289.00 11 28 46.33 46.33 -18.33 336.11 12 48 43.33 43.33 4.67 21.78 13 43 44.33 44.33 -1.33 1.78 14 54 39.67 39.67 14.33 205.44 15 27 48.33 48.33 -21.33 455.11 16 55 41.33 41.33 13.67 186.78 17 65 45.33 45.33 19.67 386.78 18 58 49.00 49.00 9.00 81.00 19 48 59.33 59.33 -11.33 128.44 20 44 57.00 57.00 -13.00 169.00 Total 3260.11Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.