On this problem, you can use either the Forecast Sheet or FORECAST.ETS function in Excel to estimate forecasts. Notice since this is monthly data, the seasonality here is 12.

Lacy's Department Store is working with its insurance company to claim the lost sales after a hurricane that stuck on August 31. After the hurricane, Lacy's was closed from September to through December due to the heavy damages it suffered. Lacy's wants to claim two kinds of lost sales:

sales that Lacy's would have made during the four months it was closed if there were no hurricane.

excess sales due to increased business activity after the storm. More than $16 billion in federal disaster relief and insurance came into the country, and other department stores experienced unusually high amount of sales. This is additional sales that Lacy's lost because it was forced to close.

In this Excel file, you are given two sales data sets:

Lacys Sales = Lacy's total sales for the 48 months just before the storm.

County Sales = Total sales for all department stores in the county for the 48 months just before the storm and the four months Lacy's was closed.

The data are in $millions.

Round all the answers to 3 decimal places.

Based on these data, you are asked to estimate the lost sales for Lacy's for the months of September through December. In addition, we want to see if Lacy's is entitled to the loss of excess sales related to the storm during the same period. Do the following.

(a) Estimate the sales for Lacy's Department during the 4 months that Lacy's was closed if there were no hurricane.

(b) Estimate the countywide department store sales during the same period if there were no hurricane. Clarification: Although you are already given the actual sales from September to December sales for year 5, you should obtain forecast these months to see what would have been normal sales for those months given the prior months. So you should use the data prior to September of year 5 to estimate the forecasts for these 4 months.

(c) Comparing the actual countywide department store sales to the forecasted sale in (b), does it seem like there was substantial excess sales due to hurricane that Lacy's lost out on due to being closed? Compute the lift factor = actual sales/forecasted sales for each of the 4 months, September to December of year 5.

(d) Estimate the lost sales for Lacy's during the 4 months due to hurricane including the excess sales it missed. Hint: For each month during September to December of year 5, multiply Lacy's forecasted sales by the lift factor computed in (c). Then sum these four forecasted monthly sales to estimate the total lost sales.

Lacy's Sales
Month	Year	t	Lacys Sales
September	1	1	3.42
October	1	2	3.8
November	1	3	5.48
December	1	4	8.4
January	2	5	2.9
February	2	6	3.6
March	2	7	4.06
April	2	8	3.98
May	2	9	4.64
June	2	10	4.4
July	2	11	4.26
August	2	12	4.86
September	2	13	3.8
October	2	14	4.26
November	2	15	5.12
December	2	16	8.32
January	3	17	4.62
February	3	18	3.78
March	3	19	4.04
April	3	20	4.46
May	3	21	4.78
June	3	22	4.28
July	3	23	4.54
August	3	24	4.42
September	3	25	3.78
October	3	26	4.58
November	3	27	5.66
December	3	28	8.08
January	4	29	4.62
February	4	30	3.98
March	4	31	4.84
April	4	32	4.9
May	4	33	5.14
June	4	34	4.84
July	4	35	4.8
August	4	36	5
September	4	37	4.18
October	4	38	5.08
November	4	39	5.94
December	4	40	8.7
January	5	41	5.12
February	5	42	4.56
March	5	43	5.38
April	5	44	4.96
May	5	45	5.46
June	5	46	4.74
July	5	47	4.62
August	5	48	4.46
September	5	49
October	5	50
November	5	51
December	5	52

County-wide Department Store Sales
Month	Year	t	County Sales
September	1	1	111.6
October	1	2	112.8
November	1	3	142.8
December	1	4	235.2
January	2	5	93.6
February	2	6	96
March	2	7	120
April	2	8	115.2
May	2	9	123.6
June	2	10	116.4
July	2	11	112.8
August	2	12	126
September	2	13	115.2
October	2	14	106.8
November	2	15	142.8
December	2	16	228
January	3	17	93.6
February	3	18	97.2
March	3	19	118.8
April	3	20	116.4
May	3	21	121.2
June	3	22	110.4
July	3	23	102
August	3	24	117.6
September	3	25	99.6
October	3	26	109.2
November	3	27	130.8
December	3	28	204
January	4	29	87.6
February	4	30	91.2
March	4	31	115.2
April	4	32	106.8
May	4	33	112.8
June	4	34	105.6
July	4	35	108
August	4	36	121.2
September	4	37	94.8
October	4	38	109.2
November	4	39	135.6
December	4	40	200.4
January	5	41	96
February	5	42	103.2
March	5	43	115.2
April	5	44	116.4
May	5	45	120
June	5	46	114
July	5	47	115.2
August	5	48	123.6
September	5	49	138
October	5	50	150
November	5	51	170.4
December	5	52	243.6