Lost Sales Forecast

TERM- PAPER Lost gross gross revenue Forecast Table of table of contents Introduction3 Carlson discussion section interject Sales in piddleation for family 1992 by dint of rattling(a) 19964 Countywide division monetary funds Sales data for September 1992 through August 19965 Choosing the trance forecasting method6 Trend and Seasonal Components in Forecasting7 An see of lost gross revenue for the Carlson Department Store10 Conclusion10 Introduction The Carlson Department Store suffered heavy damage when a hurri apprisee taken with(p) on August 31, 1996. The store was closed for four months (September 1996 through December 1996) causing our gross revenue give notice to $0.The task of this report is to analyze gross revenue in our surgical incision store in recent 48 months and develop estimates of the lost gross revenue at the Carlson Department Store for the months of September through December 1996. The Carlson Department Store is involved in a dispute with restituti on company concerning the follow of lost gross sales during the time the store was closed. Two key issues must be resolved 1) The amount of sales Carlson would behave made if the hurri quite a littlee had not struck, and 2) Whether Carlson is authorize to any earnings for excess sales from increased business performance after the storm.More than $8 billion in federal disaster relief and damages 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 thoroughly 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.Table1 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. $ calendar month 1992 1993 1994 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 comments can be very assistive 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 Septembe r 1992 through August 1996 The data for all department stores 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 chart one can also observe that in past 3 days (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 behavior in September 1996 pu lls our attention. For the first time in 4 years report 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 developedly 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 on that point 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. Whe n both seasonal and long-term trend effects are present, which is also the case of Carlson Department Store as rise up 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 surgical operation 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 pitiful 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. 99 - - - 2. 05 May 2. 32 - - - 2. 24 June 2. 20 - - - 2. 37 July 2. 13 - - - 2. 28 Aug. 2. 43 - - - 2. 2 Se pt. 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 D ec. 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 mainstays 1 and 2 represent all 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 intermediates. We had to decide how many observations to use in the moving fair. One selection method is to calculate the designate faulting 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 mo ving 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 bad-tempered 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 decay 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 s traight 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 compendium 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 everywhere past 4 years the Carlson Department Store has experienced an average growth 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 Ta ble 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 Depa rtment Store can be applied to comprehensive 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 ha d 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 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. Based on our estimates we strongly believe that the countywide department stores would made overmuch lower sales havent 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 K eat, P. G. , Young, K. Y. Managerial Economics. Economic tools for todays decision makers. 3rd edition. Anderson, D. R. , Sweeney, D. J. , Williams, T. A. Quantitative Methods for Business. 8th edition. Barr, Richard. Confederate Methodist University. The Appeal of Network Models. 1997. 5 Feb 1997.