Question

For the Hawkins Company, the monthly percentages of all shipments received on time over the past 12 months are 80, 82, 84,83,83,84,85,84,82,83,84, and 83. Using excel.

A. Compute the MSE’s for two forecasts, one using a 2 month moving average and the other exponential smoothing with a value of 0.25.

B. Which of these two forecasting approaches is preferred? Please be sure to explain why you have reached this conclusion.

Answer 1

Answer.

I have solved the part A in excel and i will add the snapshot of that excel here as well as share the link of uploaded excel file for reference. I have first answered both the parts,then there is link to excel file, and then in the end there is a snapshot of excel.

A.

Formula's:

• Actual demand = D_t
• Forecasted demand (2 month MA) = avg. of actual demand of last 2 months= (f_t)_ma = (D_t-2 + D_t-1)/2
• Forecated demand (exponential smoothing)= (F_t)_{es =} (F_t-1 - D_t-1) + F_t-1
• Error = e_t = Ft- D_t
• MSE =   
• Solving according to formulas in excel
• MSE from 2 months MA = 2.15%
• MSE from Exponential smoothing = 1.82 % (Answer for part A)

B. Exponential smoothing is a better option because of two key reasons:

• Most important is that, in this case it is more accurate the 2 month moving average. (as MSE is lesser for Exponetial smoothing)
• On top of that it requires to keep lesser amount of historical data , so it is giving better results with lesser efforts.

LInk to excel:

https://docs.google.com/spreadsheets/d/1KGynIDLkTU1aWwkEk1MGxpS97Znay6R14ev3pOZSG7c/edit?usp=sharing

Snapshot of excel: