Forecasting Lost Sales Case Study

Topics: Forecasting, Mean absolute percentage error, Mean squared error Pages: 7 (1039 words) Published: April 19, 2015
﻿Tiffany Henault
March 3rd, 2015
Quan901-CH2
Forecasting Lost Sales
Case Study
Section I: Summary
Carlson Department store suffered heavy damage from a hurricane on August 31. As a result the store was closed for four months, September through December. Carlson is in dispute with its insurance company regarding the lost sales for the length of time the store was closed. Section II: Problem Identification

Two issues to address are the amount of sales Carlson department store would have made if there had been no hurricane and if they are entitled to any compensation for excess sales due to increased business activity after the storm. One further important factor is that eight billion dollars in federal disaster relief and insurance money came in to the county which in turn increased sales at department stores and numerous other businesses in the area. Section III: Approach:

The method to be used is forecasting with seasonality in order to obtain approximate sales data for the months that Carlson was closed. Section IV: Data
Sales for Carlson Department Store (\$ Millions) Figure 6.1

Month
Year One
Year Two
Year Three
Year Four
Year Five
January

1.45
2.31
2.31
2.56
February

1.8
1.89
1.99
2.28
March

2.03
2.02
2.42
2.69
April

1.99
2.23
2.45
2.48
May

2.32
2.39
2.57
2.73
June

2.2
2.14
2.42
2.37
July

2.13
2.27
2.4
2.31
August

2.43
2.21
2.5
2.23
September
1.71
1.9
1.89
2.09

October
1.9
2.13
2.29
2.54

November
2.74
2.56
2.83
2.97

December
4.2
4.16
4.04
4.35

Department Store Sales for the County (\$ Millions)
Figure 6.2

Month
Year One
Year Two
Year Three
Year Four
Year Five
January

46.8
46.8
43.8
48
February

48
48.6
45.6
51.6
March

60
59.4
57.6
57.6
April

57.6
58.2
53.4
58.2
May

61.8
60.6
56.4
60
June

58.2
55.2
52.8
57
July

56.4
51
54
57.6
August

63
58.8
60.6
61.8
September
55.8
57.6
49.8
47.4
69
October
56.4
53.4
54.6
54.6
75
November
71.4
71.4
65.4
67.8
85.2
December
117.6
114
102
100.2
121.8

Linear Trend-Carlson Department Store
Figure 6.3

Linear Trend-County Department Stores
Figure 6.4

Analysis of Forecasting Methods
Figure 6.5
Breakdown of Forecasting Methods

Forecast Accuracy
Linear Trend
Naïve
3 month Weighted
Historical Data
MFE
-0.00055
0.01106383
-0.00433333
0.138894435
MAE
0.374945833
0.514043
0.50137037
0.383448966
MSE
0.350677567
0.620881
0.552
0.42314074
MAPE
14.44119315
21.30164
20.16
14.19457954

Lost Sales For Carlson
Figure 6.6
Month Number
Deseasonalised Forecast Volume
Reseasonlised Forecast
49
2.710503844
2.113274184
50
2.72179992
2.477159454
51
2.733095995
3.116319008
52
2.74439207
4.721989438
Sales Lost during 4 months
12.42874208

Carlson Average Sales/Seasonal Index
Figure 6.7

Month
Average for the Month
Seasonal Index

September
1.8975
0.77966102

October
2.215
0.91011813

November
2.775
1.14021572

December
4.1875
1.72059579

January
2.1575
0.88649204

February
1.99
0.81766821

March
2.29
0.94093477

April
2.2875
0.93990755

May
2.5025
1.02824859

June
2.2825
0.93785311

July
2.2775
0.93579866

August
2.3425
0.96250642

County Average Sales/Seasonal Index
Figure 6.8
Month
Average for the Month
Seasonal Index

September
52.65
0.869708858
October
54.75
0.9043981
November
69
1.139789387
December
108.45
1.79145158
January
46.35
0.765641132
February
48.45
0.800330374
March
58.65
0.968820979
April
56.85
0.939087343
May
59.7
0.9861656
June
55.8
0.921742721
July
54.75
0.9043981
August
61.05
1.008465827

County Department Reseasonlised/Excess Sales
Figure 6.9
Quarter Number
Deseasonalised Forecast Volume...

References: Anderson, D. R., Sweeny, D., Williams, T., Cann, J., Cochran, J., Fry, M., & Ohlmann, J. (2013). Quantitative Methods For Business. Mason, OH: South-Western Cengage Learning.

Please join StudyMode to read the full document