Question

Mayfair Department Store was shut down during the months of July and August due to severe flooding in the downtown area. The store’s insurance policy provides revenues to the department store in cases of natural disasters. Sales data for the preceding 6 months are listed below.

Month	Sales (1,000s)	Month	Sales (1,000s)
January	184.72	April	210.36
February	167.84	May	255.57
March	205.11	June	261.19

1. Develop a forecast for July and August using exponential smoothing with a = 0.4. (Hint: use the July forecast as actual sales for July in developing the August forecast.) Comment on the use of exponential smoothing for forecasts more than one period in the future.

b) Use linear trend to predict sales for July and August. What are the forecasted amounts?

c) Mayfair’s insurance company has offered $240,000 per month for the lost sales. Should Mayfair accept the settlement offer? Why? If Mayfair should reject the offer, what is the amount that Mayfair should make as a counter offer?

Answer 1

a. Develop a forecast for July and August using exponential smoothing with a = 0.4. (Hint: use the July forecast as actual sales for July in developing the August forecast.) Comment on the use of exponential smoothing for forecasts more than one period in the future

Ans :

Using Data Analytics in Data tab of Excel you can work out following :

Month	Actual Data	Exponential Smoothing with damping factor of 0.4
January	184.72	#N/A
February	167.84	184.72
March	205.11	174.59
April	210.36	192.90
May	255.57	203.38
June	261.19	234.69
July	250.59	250.59
August		250.59

Excel Table with Formula :

Month	Actual Data	Exponential Smoothing with damping factor of 0.4
January	184.72	#N/A
February	167.84	=C5
March	205.11	=0.6*C6+0.4*D6
April	210.36	=0.6*C7+0.4*D7
May	255.57	=0.6*C8+0.4*D8
June	261.19	=0.6*C9+0.4*D9
July	=D11	=0.6*C10+0.4*D10
August		=0.6*C11+0.4*D11

As guided to derive value of Aug forecast, July actual assumed equal to July forecast value

The July and Aug forecast value is 250.59

Using simple exponential smoothing then one should expect forecasts to stick at a constant level from 2nd Month, hence as you go further ahead they become less accurate.

If you want them to trend then use an exponential smoothing method with a trend as well as a level component;

b) Use linear trend to predict sales for July and August. What are the forecasted amounts?

July Forecast : 279.21, Aug Forecast: 297.80

Using Forecast.linear function in Excel we get following :

Month	Actual	Forecast
1	184.72
2	167.84
3	205.11
4	210.36
5	255.57
6	261.19
7		279.21
8		297.80

Excel Table with Formula : Cell range B20 :D28

Month	Actual	Forecast
1	184.72
2	167.84
3	205.11
4	210.36
5	255.57
6	261.19
7		=FORECAST.LINEAR(B27,C21:C26,B21:B26)
8		=FORECAST.LINEAR(B28,C21:C26,B21:B26)

c) Mayfair’s insurance company has offered $240,000 per month for the lost sales. Should Mayfair accept the settlement offer? Why? If Mayfair should reject the offer, what is the amount that Mayfair should make as a counter offer?

On the base of Linear relationship it shows that the forecasted sales value of July and Aug is going to exceed 240,000 hence Mayfair should not accept the settlement offer and should ask insurance company to provider offer considering Linear sales forecast value of July and Aug