Question

AutoSave of Exam3 PartB_SP20_Due_04_24 - Excel File Home insert Draw Page Layout Formulas Data Review View Help Search ΑΙ Formula Bar с 1 Week Week D E F G H I J On 3a [10 points). Data set to your left (sheet On3a) contains information on weekly sales for a local grocery store over a 12-week period. Use the data set to answer the following questions 1. Construct a time series plot. What type of pattern exists in the data? Briefly justify your answer. [2 points) Sales ($1,000) 240 350 230 260 280 320 220 310 240 310 240 240 11. Compute a three-week moving average for the time series Compute MSE and MAPE. [3 points) in. Use a 0.8 to compute the exponential smoothing values for the time series. Compute the MSE and MAPE. [3 points] iv. Compare the three-week moving average approach with the exponential approach using a = 0.8. Which appears to provide more accurate forecasts based on MSE? (2 points]

Answer 1

Q3a)

i)

400 350 300 150 2468 10 12 14

ii)

period	demand	forecast	forecast error=demand value-forecast value	absolute forecast error	squared forcast error	Abs %error
t	Dt	Ft	et=Dt-Ft	| et |	(et)²	| et/Dt |
1	240
2	350
3	230
4	260	273.33	-13.33	13.33	177.78	5.13%
5	280	280.00	0.00	0.00	0.00	0.00%
6	320	256.67	63.33	63.33	4011.11	19.79%
7	220	286.67	-66.67	66.67	4444.44	30.30%
8	310	273.33	36.67	36.67	1344.44	11.83%
9	240	283.33	-43.33	43.33	1877.78	18.06%
10	310	256.67	53.33	53.33	2844.44	17.20%
11	240	286.67	-46.67	46.67	2177.78	19.44%
12	240	263.33	-23.33	23.33	544.44	9.72%

MSE=   Σ(et)²/n =    1935.80
      
MAPE=   Σ | et/Dt |/n =    14.61%

iii)

period	demand	forecast	forecast error=demand value-forecast value	absolute forecast error	squared forcast error	Abs %error
t	Dt	Ft	et=Dt-Ft	| et |	(et)²	| et/Dt |
1	240	240.000
2	350	240	110.00	110.00	12100.00	31.43%
3	230	328	-98.00	98.00	9604.00	42.61%
4	260	249.6	10.40	10.40	108.16	4.00%
5	280	257.92	22.08	22.08	487.53	7.89%
6	320	275.584	44.42	44.42	1972.78	13.88%
7	220	311.1168	-91.12	91.12	8302.27	41.42%
8	310	238.22336	71.78	71.78	5151.89	23.15%
9	240	295.644672	-55.64	55.64	3096.33	23.19%
10	310	251.128934	58.87	58.87	3465.80	18.99%
11	240	298.225787	-58.23	58.23	3390.24	24.26%
12	240	251.645157	-11.65	11.65	135.61	4.85%

      
MSE=   Σ(et)²/n =    4346.78
      
MAPE=   Σ | et/Dt |/n =    21.42%

iv)

three year moving average is better because MSE is smaller for it