Lost Sales Analysis
Report Distributed October, 18 --
Low’s Depot, Home Improvement Center
TABLE OF CONTENTS 1. Introduction 3 2. Modeling Method 3 3. Results 4
This memorandum will deal with questions submitted by Mr.. Low, CEO of Low's Depot, Oklahoma City, OK. Some background facts of the case; A storm occurred and caused major damage to the home improvement store. This in turn resulted to shut the store down for four months (September, October, November, and December).
Now the company wants to have enough evidence to convince the insurance company that the company really has made a great loss in these months, in order to obtain compensation, and there for there is two question that the company asked themself:
A: The amount of (regular) sales we would have received during the time we were closed.
B: The additional sales we would have received due to the extra business activity that we would have received because of the rebuilding activity that occurred after the storm.
Even thou over eight million dollars have flowed into the country, to build up the ruined provinces. The company did not have the right to take advantage of this, and now believes that they should receive compensation for the losses.
We have reviewed a table attached two data series, both tables provides with 48 months. The first table is the sales data from the store for the months immediately preceding the tornado, and the second one the sales for all home improvement stores and the total county-wide sales for the months that Lowe’s Depot were closed. 2. Modeling Method
In order to answer the questions that Lowe’s Depot been asking, I will perform a forecast based on the data series specified. The forecast will be based on seasonality with trend. With seasonality with trend meant a trend line in a year or less. The process measure interest in small time intervals, such as days, weeks, months or quarters. In our case, there will be monthly. This calculation is done to more easily figure out the numbers for next month. However, you should take into consideration that the numbers that are calculated is not precise. To be able to do such a calculation I will use a so called regression, which is a data analyze. A regression makes it possible to draw a strait line and shows the increase every month. Without any regression you only will get a lot of numbers without any sense. The regression is a branch of statistics, where the goal is to create a function that best fits the observed data.
The aim is basically to investigate the relationship between a dependent variable and one or more independent variables. This method also explores relations that can be described by straight lines or their generalization to numerous dimensions.
The method is used to more easily handle temporary increases or decreases in the company. These temporary increases or decreases may include for example a store. 3. Result A) Estimate the sales that would have occurred at the store if there had been NO tornado.
Below you will see a table with all your sales. I will calculate the regression from the data with help from the trends and all the Dummys.
SUMMARY OUTPUT | | | | | | | | | | | | | | | | | | Regression Statistics | | | | | | | | Multiple R | 0.945755452 | | | | | | | | R Square | 0.894453374 | | | | | | | | Adjusted R Square | 0.829694531 | | | | | | | | Standard Error | 0.325656444 | | | | | | | | Observations | 48 | | | | | | | | | | | | | | | | | ANOVA | | | | | | | | | | df | SS | MS | F | Significance F | | | | Regression | 13 | 31.45580107 | 2.419677005 | 24.71725004 | 1.47035E-13 | | | | Residual | 35 | 3.711824183 | 0.10605212 | | | | | | Total | 48 | 35.16762525 | | | | | | | | | | | | | | | | | Coefficients | Standard Error | t