Using Excel: The second evaluation requires a forecast of customers based upon d
ID: 3357680 • Letter: U
Question
Using Excel: The second evaluation requires a forecast of customers based upon demand. Michael reviewed data for the previous 11 months in an attempt to better forecast restaurant customer volume.
Which method should the business owner use to yield the lowest amount of error and what would be the forecast for December when using 4 period moving average, 2 period weighted moving average (weights 0.15 and 0.3), and exponential smoothing (alpha 0.05)?
Month
# of Customers
January
650
February
725
March
850
April
825
May
865
June
915
July
900
August
930
September
950
October
899
November
935
December
?
Month
# of Customers
January
650
February
725
March
850
April
825
May
865
June
915
July
900
August
930
September
950
October
899
November
935
December
?
Explanation / Answer
for 4 period moving average :
forecast =average of last 4 months actual
for december forecast 4 period moving average=928.5
and MSE for 4 period moving average =3937.473
2)
for 2 period weighted moving average =0.3*last month actual+0.15*last to last month actual
for december forecast from 2 month weighted moving average method =923
and MSE for 2 month weighted moving average method =3516.21
3)
for exponential smoothing:
from exponential smoothing ; december forecast =745.06
and MSE =38156.67
therefore 2 month weighted moving average forecast is best due to lowest MSE>
Month # of Customers(Actual) Forecast (A-F)^2 January 650 February 725 March 850 April 825 May 865 762.5 10506.25 June 915 816.25 9751.563 July 900 863.75 1314.063 August 930 876.25 2889.063 September 950 902.5 2256.25 October 899 923.75 612.5625 November 935 919.75 232.5625 December 928.5 total 27562.31 average=total/n 3937.473 MSERelated Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.