Question

1. A small mattress company recently started its operations and currently does not have enough resources to utilize high-tech forecasting software. The manager wants to utilize traditional methods of forecasting to estimate demand. The following recent data on monthly sales are available from the past year. Use this information to answer the following set of questions.

Month	Sales	Moving Average	MAD
Jan-19	119
Feb-19	72
Mar-19	113
Apr-19	82
May-19	82
Jun-19	131
Jul-19	111
Aug-19	116
Sep-19	89
Oct-19	95
Nov-19	88
Dec-19	90
Jan-20

1. If the manager uses a naïve forecasting method. What is the forecast for Jan 2020? (2 pts.)

1. Use the three-period moving average method and estimate forecast beginning from Aug-19 to Jan-20. Use 0.4, 0.3, and 0.2 as weights with highest weights given to the most recent periods (8 pts.)
   
2. Calculate the Mean Absolute Deviation of forecasts from the three-period moving average. (5 pts.)
   

              2. Use the same date from Q1. to answer the following questions. (10 pts.)

Month	Sales	Forecast
Jan-19	119
Feb-19	72
Mar-19	113
Apr-19	82
May-19	82
Jun-19	131
Jul-19	111
Aug-19	116
Sep-19	89
Oct-19	95
Nov-19	88
Dec-19	90
Jan-20

1. Use the exponential smoothing method to calculate forecast from Aug-19 to Jan-20. Use an α value of 0.3.

You need a beginning forecast of Jul-19 to start this. Use the naïve forecast for estimating Jul-19, and then use the answer to start the exponential smoothing method.

(Please show all the work for credit on this. You can use excel too, but show the logic/formula if using excel)

Answer 1

a)

forecast for Jan 2020 using Naive approach = sales of Dec-19=90

b)

Weighted Moving Average for Aug -19= =(0.4*sales of Jul -19)+(0.3*Sales of Jun-19)+(0.2*Sales of May-19)

absolute deviation = Sales - Forecast with Absolute value

1	B	C	D	E
2	Month	Sales	Weighted Moving Average	Absolute Deviation from Aug 19 to Dec 19
3	19-Jan	119
4	19-Feb	72
5	19-Mar	113
6	19-Apr	82
7	19-May	82
8	19-Jun	131
9	19-Jul	111
10	19-Aug	116	100.1	15.9
11	19-Sep	89	105.9	16.9
12	19-Oct	95	92.6	2.4
13	19-Nov	88	87.9	0.1
14	19-Dec	90	81.5	8.5
15	20-Jan		81.4	Mean absolute Deviation
16				8.76

Formula:

1	B	C	D	E
2	Month	Sales	Weighted Moving Average	Absolute Deviation from Aug 19 to Dec 19
3	43849	119
4	43880	72
5	43909	113
6	43940	82
7	43970	82
8	44001	131
9	44031	111
10	44062	116	=(0.4*C9)+(0.3*C8)+(0.2*C7)	=ABS(C10-D10)
11	44093	89	=(0.4*C10)+(0.3*C9)+(0.2*C8)	=ABS(C11-D11)
12	44123	95	=(0.4*C11)+(0.3*C10)+(0.2*C9)	=ABS(C12-D12)
13	44154	88	=(0.4*C12)+(0.3*C11)+(0.2*C10)	=ABS(C13-D13)
14	44184	90	=(0.4*C13)+(0.3*C12)+(0.2*C11)	=ABS(C14-D14)
15	43850		=(0.4*C14)+(0.3*C13)+(0.2*C12)	Mean absolute Deviation
16				=AVERAGE(E10:E14)

c)

Formula:

Forecast of Jul-19 is considered using Naive forecast, where, Forecast of Jul-19=sales of Jun -19

Exponential Smoothing Forecast using Alpha value of 0.3 for Aug 19 = (0.3*Sales of Jul-19)+(0.7*Forecast of Jul-19)

1	B	C	D	E
2	Month	Sales	Exponential Smoothing Forecast using Alpha =0.3	Absolute Deviation from Aug 19 to Dec 19
3	19-Jan	119
4	19-Feb	72
5	19-Mar	113
6	19-Apr	82
7	19-May	82
8	19-Jun	131
9	19-Jul	111	131.00
10	19-Aug	116	125.00	9.00
11	19-Sep	89	122.30	33.30
12	19-Oct	95	112.31	17.31
13	19-Nov	88	107.12	19.12
14	19-Dec	90	101.38	11.38
15	20-Jan		97.97	Mean absolute Deviation
16				18.02

Formula:

1	B	C	D	E
2	Month	Sales	Exponential Smoothing Forecast using Alpha =0.3	Absolute Deviation from Aug 19 to Dec 19
3	43849	119
4	43880	72
5	43909	113
6	43940	82
7	43970	82
8	44001	131
9	44031	111	131
10	44062	116	=(0.3*C9)+(0.7*D9)	=ABS(C10-D10)
11	44093	89	=(0.3*C10)+(0.7*D10)	=ABS(C11-D11)
12	44123	95	=(0.3*C11)+(0.7*D11)	=ABS(C12-D12)
13	44154	88	=(0.3*C12)+(0.7*D12)	=ABS(C13-D13)
14	44184	90	=(0.3*C13)+(0.7*D13)	=ABS(C14-D14)
15	43850		=(0.3*C14)+(0.7*D14)	Mean absolute Deviation
16				=AVERAGE(E10:E14)