Lost Sales Forecast

TERM- PAPER Lost Sales Forecast Table of Contents Introduction3 Carlson Department Store Sales data for September 1992 through August 19964 Countywide Department Stores Sales data for September 1992 through August 19965 Choosing the appropriate forecasting method6 Trend and Seasonal Components in Forecasting7 An estimate of lost sales for the Carlson Department Store10 Conclusion10 Introduction The Carlson Department Store suffered heavy damage when a hurricane struck on August 31, 1996. The store was closed for four months (September 1996 through December 1996) causing our sales drop to $0.The task of this report is to analyze sales in our department store in past 48 months and develop estimates of the lost sales at the Carlson Department Store for the months of September through December 1996. The Carlson Department Store is involved in a dispute with insurance company concerning the amount of lost sales during the time the store was closed. Two key issues must be resolved: 1) The amount of sales Carlson would have made if the hurricane had not struck, and 2) Whether Carlson is entitled to any compensation for excess sales from increased business activity after the storm.More than $8 billion in federal disaster relief and insurance money came into the county, resulting in increased sales at department stores and numerous other businesses. Carlson Department Store Sales data for September 1992 through August 1996 Certain conditions should be met by any good forecast. A good forecast should usually be based on adequate knowledge of the relevant past. With our company – The Carlson Department Store – we have the sales data for the 48 months preceding the storm available. This amount of historical data fulfills the requirement for the volume of relevant data.Table  1 shows the sales data for the Carlson Department Store for the months of September 1992 through August 1996. Table 1: Sales for Carlson Department Store [mil. $] |Month |1992 |1993 |19 94 |1995 |1996 | |February | |1. 80 |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. 3 | |June | |2. 20 |2. 14 |2. 42 |2. 37 | |July | |2. 13 |2. 27 |2. 40 |2. 31 | |August | |2. 43 |2. 21 |2. 50 |2. 23 | |September |1. 71 |1. 90 |1. 89 |2. 09 | | |October |1. 90 |2. 13 |2. 29 |2. 54 | | |November |2. 74 |2. 56 |2. 83 |2. 97 | | |December |4. 20 |4. 16 |4. 04 |4. 5 | | Series of numbers is often difficult to interpret. Graphing the observations can be very helpful since the shape of a complicated series is more easily discerned from a picture. The data for Carlson Department Store, as can be seen in Graph 1, indicate some seasonal fluctuations. It can be seen that the sales in last quarter are higher than in the first 3 quarters of a year, with the highest volume of sales in December. [pic] Countywide Department Stores Sales data for September 1992 through August 1996 The data for all department stor es in the county are summarized in Table 2.Table 2: Department Store Sales for the county [mil. $] |Month |1992 |1993 |1994 |1995 |1996 | |February | |48. 0 |48. 6 |45. 6 |51. 6 | |March | |60. 0 |59. 4 |57. 6 |57. 6 | |April | |57. 6 |58. 2 |53. 4 |58. 2 | |May | |61. 8 |60. 6 |56. 4 |60. 0 | |June | |58. 2 |55. 2 |52. 8 |57. 0 | |July | |56. 4 |51. |54. 0 |57. 6 | |August | |63. 0 |58. 8 |60. 6 |61. 8 | |September |55. 8 |57. 6 |49. 8 |47. 4 |69. 0 | |October |56. 4 |53. 4 |54. 6 |54. 6 |75. 0 | |November |71. 4 |71. 4 |65. 4 |67. 8 |85. 2 | |December |117. 6 |114. 0 |102. 0 |100. 2 |121. 8 | Sales of all department stores in the county, as can be seen from Graph 2, show similar seasonal fluctuations as sales of Carlson Department Store. pic] From the above graph one can also observe that in past 3 years (years 1993-1995) the volume of sales in the month of September went down, and slowly went up again in October and November and usually reached its peak in December. The unusual b ehavior in September 1996 pulls our attention. For the first time in 4 years history we observe that the sales volume in September compared to August sales went up by 11. 7 % whereas in September 1993 they were down by 8. 6%, in September 1994 down by 15. 3%, and in September 1995 actually down by 21. 8%.The question is why such a change occurred? And the answer is that more than $8 billion in federal disaster relief and insurance money came into the county, which resulted in these increased sales at department stores. Choosing the appropriate forecasting method There are many different forecasting methods. One of the challenges we had to face was to choose the right technique. Smoothing methods are appropriate for a stable time series. When a time series consist of random fluctuations around a long-term trend line, a linear equation may be used to estimate the trend.When seasonal effects are present, seasonal indexes can be computed and used to deseasonalize the data and to develop forecasts. When both seasonal and long-term trend effects are present, which is also the case of Carlson Department Store as well as the case of all department stores in the county, a trend line is fitted to the deseasonalized data; the seasonal indexes are then used to adjust the trend projections. Trend and Seasonal Components in Forecasting The procedure of forecasting the sales for months September through December 1996 (had there been no hurricane) for The Carlson Department Store is summarized in Table 3.Table 3: Procedure of forecasting sales for Sep. -Dec. 1996 |   |   |Sales |12-month |Centered |Seasonal |Deseasonalized | | | | |Moving |Moving |Irregular |Sales | | | | |Average |Average |Value | | |1992 |Sept. |1. 71 |- |- |- |2. 09 | |   |Oct. |1. 90 |- |- |- |1. 95 | |   |Nov. |2. 74 |- |- |- |2. 35 | |   |Dec. |4. 20 |- |- |- |2. 41 | |1993 |Jan. 1. 45 |- |- |- |1. 46 | |   |Feb. |1. 80 |- |- |- |2. 13 | |   |Mar. |2. 03 |- |- |- |2. 09 | |   |Apr. |1. 9 9 |- |- |- |2. 05 | |   |May |2. 32 |- |- |- |2. 24 | |   |June |2. 20 |- |- |- |2. 37 | |   |July |2. 13 |- |- |- |2. 28 | |   |Aug. |2. 43 |- |- |- |2. 2 | |   |Sept. |1. 90 |2. 24 |- |- |2. 32 | |   |Oct. |2. 13 |2. 26 |2. 25 |0. 95 |2. 18 | |   |Nov. |2. 56 |2. 28 |2. 27 |1. 13 |2. 19 | |   |Dec. |4. 16 |2. 26 |2. 27 |1. 83 |2. 38 | |1994 |Jan. |2. 31 |2. 26 |2. 26 |1. 02 |2. 32 | |   |Feb. |1. 89 |2. 33 |2. 29 |0. 82 |2. 23 | |   |Mar. |2. 02 |2. 34 |2. 33 |0. 87 |2. 08 | |   |Apr. |2. 23 |2. 34 |2. 34 |0. 5 |2. 30 | |   |May |2. 39 |2. 36 |2. 35 |1. 02 |2. 31 | |   |June |2. 14 |2. 36 |2. 36 |0. 91 |2. 30 | |   |July |2. 27 |2. 36 |2. 36 |0. 96 |2. 43 | |   |Aug. |2. 21 |2. 37 |2. 36 |0. 94 |2. 38 | |   |Sept. |1. 89 |2. 35 |2. 36 |0. 80 |2. 31 | |   |Oct. |2. 29 |2. 35 |2. 35 |0. 97 |2. 34 | |   |Nov. |2. 83 |2. 36 |2. 36 |1. 20 |2. 42 | |   |Dec. 4. 04 |2. 39 |2. 37 |1. 70 |2. 31 | |1995 |Jan. |2. 31 |2. 38 |2. 38 |0. 97 |2. 32 | |   | Feb. |1. 99 |2. 38 |2. 38 |0. 84 |2. 35 | |   |Mar. |2. 42 |2. 38 |2. 38 |1. 02 |2. 49 | |   |Apr. |2. 45 |2. 42 |2. 40 |1. 02 |2. 52 | |   |May |2. 57 |2. 44 |2. 43 |1. 06 |2. 48 | |   |June |2. 42 |2. 45 |2. 44 |0. 99 |2. 60 | |   |July |2. 40 |2. 47 |2. 46 |0. 7 |2. 57 | |   |Aug. |2. 50 |2. 49 |2. 48 |1. 01 |2. 70 | |   |Sept. |2. 09 |2. 51 |2. 50 |0. 84 |2. 55 | |   |Oct. |2. 54 |2. 53 |2. 52 |1. 01 |2. 60 | |   |Nov. |2. 97 |2. 55 |2. 54 |1. 17 |2. 54 | |   |Dec. |4. 35 |2. 56 |2. 55 |1. 70 |2. 49 | |1996 |Jan. |2. 56 |2. 58 |2. 57 |1. 00 |2. 57 | |   |Feb. |2. 28 |2. 61 |2. 59 |0. 88 |2. 69 | |   |Mar. |2. 9 |2. 63 |2. 62 |1. 03 |2. 77 | |   |Apr. |2. 48 |2. 65 |2. 64 |0. 94 |2. 55 | |   |May |2. 73 |2. 65 |2. 65 |1. 03 |2. 64 | |   |June |2. 37 |2. 67 |2. 66 |0. 89 |2. 55 | |   |July |2. 31 |2. 66 |2. 67 |0. 87 |2. 47 | |   |Aug. |2. 23 |2. 66 |2. 66 |0. 84 |2. 40 | |   |Total |   |   |   |   |113. 72 | Columns 1 and 2 represent al l the years and months.Column 3 shows the monthly sales data of Carlson Department Store. The first step of the deseasonalizing process is to calculate the moving averages. We had to decide how many observations to use in the moving average. One selection method is to calculate the mean error and the mean squared error of the differences between the actual data and the forecast. The series with the smallest squared error would be preferred. The Management Scientist results for the Carlson Department Store show that the 12-month moving average gives the smallest squared error. The 12-month moving average values are shown in the Column 4.If the number of data points in a moving average calculation is an even number, we need to center the moving average values to correspond to a particular time period, as we did in the calculations in Column 5. By dividing each time series observation by the corresponding centered moving average value, we could identify the seasonal-irregular effect in the time series. Column 6 summarizes the resulting seasonal-irregular values for the entire time series. By dividing each time series observation by the corresponding seasonal index, we remove the effect of season from the time series.Deseasonalized sales data are shown in Column 7 and a graph of the data (graph 3) is on the next page. The first step of the decomposition procedure has now been completed. The new series has eliminated the seasonality. The next step is to calculate the trend. The observation of the deseasonalized sales data of Carlson Department Store appears to indicate that a straight line would be most appropriate form of equation that would describe the trend. Graph 3: Deseasonalized sales data of Carlson Department Store [pic] Applying regression analysis we have arrived to this the linear trend equation: Tt = 2. 875 + 0. 0118t. The slope of 0. 0118 in the trend equation indicates that over past 4 years the Carlson Department Store has experienced an average gro wth in sales of about $0. 0118 per year. If we assume that the past 4-year trend in sales is a good indicator for the future, we can use the equation above to project the trend component of the time series. Substituting t = 49, 50, 51, and 52 into the equation we yield the deseasonalized sales of Carlson DS for September through December 1996. For September 1996 we get $2. 67 mil, for October 1996 $2. 68, November 1996 $2. 9 and for December 1996 $2. 70. In order to apply the seasonal effects we multiply these projected deseasonalized sales by the relevant seasonal indexes calculated in Table 4. Table 4: Seasonal Indexes |Month |Seasonal-Irregular Component Values |Seasonal | | | |Index | |Jan. |- |0. 63 |0. 64 |0. 65 |0. 69 |0. 65 | |Feb. |- |0. 78 |0. 80 |0. 81 |0. 87 |0. 82 | |March |- |1. 12 |1. 11 |1. 4 |1. 06 |1. 11 | |April |- |1. 00 |1. 01 |0. 99 |1. 02 |1. 01 | |May |- |1. 04 |1. 03 |1. 03 |1. 03 |1. 03 | |June |- |0. 99 |0. 97 |0. 97 |0. 98 |0. 98 | |July |- |0. 96 |0. 92 |0. 98 |0. 98 |0. 96 | |Aug. |- |1. 07 |1. 09 |1. 10 |1. 02 |1. 07 | |Sep. |- |0. 98 |0. 93 |0. 88 |1. 05 |0. 96 | |Oct. |- |0. 90 |0. 8 |0. 99 |1. 03 |0. 98 | |Nov. |1. 00 |1. 02 |1. 00 |1. 04 |1. 00 |1. 01 | |Dec. |1. 47 |1. 45 |1. 41 |1. 37 |- |1. 43 | An estimate of lost sales for the Carlson Department Store By multiplying the projected deseasonalized sales by the relevant seasonal indexes calculated in Table 4 we will arrive to the levels of sales for months September 1996 through December 1996 had there been no hurricane: September 1996 $2. 19 mil. October 1996$2. 62 mil November 1996 $3. 14 mil December 1996$4. 1 mil The above-described procedure for forecasting sales for Carlson Department Store can be applied to countywide department stores too. It would give following results: The estimated countywide department store sales had there been no hurricane (and no disaster relief money) for September 1996 is $46. 65 mil, for October 1996 $51. 22, for November 1996 $64. 4, and for December 1996 $99. 3. Comparing these figures to the actual sales of the countywide department stores one can see that the actual sales are over-valuated. I attribute this to the $8 billion of disaster relief money.Had the county department stores not received the disaster relief money they would probably continue their downward trend described by function Y = 63. 64 – 0. 13t. The slope of –0. 13 in the trend equation indicates that over past 4 years countywide department stores have experienced an average decline in sales of about $0. 13 per year. Conclusion The task of this report was to resolve two key issues. 1. Estimate the amount of sales Carlson would have made if the hurricane had not struck. We have come to a conclusion that the sales for September 1996 had there been no hurricane would be $2. 9 mil, in October 1996 it would be $2. 62 mil, in November 1996 it would be $3. 14 mil and in December 1996 it would be $4. 71 mil. 2. Find out whether Carlson is e ntitled to any compensation for excess sales from increased business activity after the storm. More than $8 billion in federal disaster relief and insurance money came into the county, resulting in increased sales at department stores and numerous other businesses. Based on our estimates we strongly believe that the countywide department stores would made much lower sales haven’t they received the relief money.Therefore we believe that our department store is entitled to compensation for excess sales from increased business activity after the hurricane and we will inquire the insurance company to cover our lost sales for months September through December 1996 in the amount of $12. 66 mil. †¢ Reference: †¢ Keat, P. G. , Young, K. Y. : Managerial Economics. Economic tools for today’s decision makers. 3rd edition. †¢ Anderson, D. R. , Sweeney, D. J. , Williams, T. A. : Quantitative Methods for Business. 8th edition. †¢ Barr, Richard. Southern Methodist University. â€Å"The Appeal of Network Models†. 1997. 5 Feb 1997.