Forecasting Lost Sales Case Study Essay

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: Options After reviewing different methods of forecasting and their measures of forecasting accuracy the linear forecasting method is shown to be the most effective given that the mean square error, and the mean absolute error and the mean absolute percentage error are very close to zero. *See attached Excel spreadsheet for further clarification/breakdown of forecasting methods. However, although the linear trend line can be useful it can also prove to be inappropriate for business retail sales. Real trends change their slope and intercept over time and rarely tend to follow a fixed straight line. Therefore, linear regression with seasonality will be used to determine lost sales. In the past five years Carlson’s overall monthly average for sales was 2.43375. The monthly averages for the months under consideration are as follows; September: 1.8975 October: 2.215 November: 2.775 and December: 4.1875. Approximately thirty nine percent of Carlson’s sales occur within the Sept through December months. The seasonal index as show in figure 6.7 further breaks this down. While reviewing Carlson  department store’s forecasted sales for September through December and taking into account that the time frame is during the holiday season; it is apparent that sales typically increase during this period in relation to seasonality. Section V: Conclusions/Recommendations Figure 6.6 displays the forecast of lost sales for Carlson had there been no hurricane. This table displays that Carlson is entitled to 12.43 million in lost sales for the four months that it was closed. The surrounding department stores showed a consistent increase in sales during the four listed months (September through December) as shown in figure 6.9. The amount of sales were well above what was typically forecasted (On average the surrounding department stores did 18.67 million above forecast). The amount of sales during this time frame increased by 27.03 percent. Based off of this data, Carlson should be provided additional compensation for the increase of sales they would have encountered from disaster relief funds and insurance money. Carlson would have gained an approximate increase of 3.36 million in sales, therefore making the total compensation owed to Carlson from their insurance company 15.788 million for lost sales. Section VI: Other Considerations Some other factors that may require further consideration are moving holidays, or the effect of holidays on the forecasting method. Some holidays may have changing dates which can impact more than one month in a way that depends upon the date. Section VII: Resources 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.