Assignment Overview
Scenario: You are a consultant who works for the Diligent Consulting Group. Your client, the
New Star Grocery Company, believes that there may be a relationship between the number of
customers who visit the store during any given month (“customer traffic”) and the total sales
for that same month. In other words, the greater the customer traffic, the greater the sales for
that month. To test this theory, the client has collected customer traffic data over the past 12-
month period, and monthly sales for that same 12-month period (Year 1).
Case Assignment
Using the customer traffic data and matching sales for each month of Year 1, create a Linear
Regression (LR) equation in Excel, assuming all assumptions for linear regression have been
met. Use the Excel template provided (see �Module 2 Case � LR �Year 1� spreadsheet
tab), and be sure to include your LR chart (with a trend line) where noted. Also, be sure that
you include the LR formula within your chart.
After you have developed the LR equation above, you will use the LR equation to forecast
sales for Year 2 (see the second Excel spreadsheet tab labeled “Year 2 Forecast”). You will
note that the customer has collected customer traffic data for Year 2. Your role is to complete
the sales forecast using the LR equation from Step 1 above.
After you have forecast Year 2 sales, your Professor will provide you with 12 months of actual
sales data for Year 2. You will compare the sales forecast with the actual sales for Year 2,
noting the monthly and average (total) variances from forecast to actual sales.
To complete the Module 2 Case, write a report for the client that describes the process you
used above, and that analyzes the results for Year 2. (What is the difference between forecast
vs. actual sales for Year 2 by month and for the year as a whole?) Make a recommendation
concerning how the LR equation might be used by New Star Grocery Company to forecast
future sales.
Data: Download the Module 2 Case template here: Data chart for BUS520 Case 2. Use this
template to complete your Excel analysis.
Excel Analysis
Conduct accurate and complete Linear Regression analysis in Excel. Use Excel support to find
information on linear regression in Excel
Name:
Introduction
The New Star Grocery Company asked Diligent Consulting Group (DCG) to find out whether there
is dependence on the customers, who visit the shop (“customer traffic”), and the total sales for each
2
month. The New Star Grocery Company gave DCG the data for the 12-months period. This data
includes monthly sales for 12 months of the Year 1. After predicting the Year 2 sales, DCG was
given actual sales data for the Year 2. After this, the Company was able to compare results and
make a conclusion about reliability and truthfulness of obtained results.
This research will use linear regression method to find out whether there exists dependence
mentioned above or not.
Executive Summary
Linear regression method was used to find dependence between some customers and amount of
sales. The results of the research showed that this dependence exists.
As well, the Company predicted a number of sales for Year 2. The error of prediction in comparison
with actual sales data was 9.37%, which is relatively low for linear regression method.
The Company made a conclusion that there exists a tendency for increasing an average bill.
Linear Regression for Year 1
The Group decided to use simple linear regression to predict future sales. This method is widely
used in many areas, such as finance (e.g. finding capital asset pricing model), economics (for
example, prediction of consumption spending, inventory investment) and biology (human body
parameters prediction). (Hron, et al. 2009) It is based on finding a relationship between a scalar
dependent variable (in current case this variable is Sales for each month) and one or more
independent variables (in current case there is one independent variable – number of visitors for
each month). (Weisberg, 2012)
There exists an other type of analysis, called correlation analysis, which investigates the strength of
the relationship between two or more variables. At the same time, correlation analysis assumes a
3
dependence or relationship between one or more independent variables and one dependent variable.
Along with a previous assumption some conditions, such as a normal distribution of data, should be
met as well. (Zou, et al. 2003). The strength of the effect, forecasting, trends and future values are
four most common things, for which regression analysis is used. (Chase, 2013)
DCG team built a graph (Graph 1), which shows the number of customers and some sales for each
particular month.
Graph 1. Linear regression graph with a trend line.
050100150200250300350400450
0
50000
100000
150000
200000
250000
300000
350000
400000
450000
LR Chart
Diligent Consulting Group had a goal to test if the next statement is true: the greater the customer
traffic, the greater the sales. Dots on the Graph 1 show the number of customers and the amount of
sales for different months. On the graph, there is a trend line, equation of which was calculated
using linear regression method. One can see that there is dependence between these two variables.
Using a linear regression model, a trend line was built, which can be seen on the Graph 1. The
equation of this line is:
4
Here x is a number of customers, y – amount of sales. In other words, when x is substituted
(customers), y is obtained (sales). Here x is the independent variable, and y is dependent one.
It can be noticed, that the trend line is positively oriented, which confirms the hypothesis, that the
greater the customer traffic, the greater the sales.
Linear Regression and Prediction of Sales for Year 2
Linear regression is a powerful tool for prediction. Besides linear regression, there are logarithmical,
exponential, etc. types of regression. The difference between each type is the type of a trend line
(how it looks). In the DCG analysis of sales linear regression is used, because it can be seen, that
there is a linear dependence between some customers and amount of sales.
Having the number of customers for the Year 2, there is an opportunity to forecast sales for this
year.
Table 1. Actual and forecasted sales for Year 2.
Sales
Year 2 Customers (x) Actual Y(t) Forecast F(t) Variance
January 215 265000 250963.44 14036.6
February 259 388000 279475.02 108525.0
March 325 298000 322242.40 -24242.4
April 354 260000 341034.12 -81034.1
May 258 263000 278827.03 -15827.0
June 199 402000 240595.59 161404.4
July 254 320000 276235.07 43764.9
August 299 310000 305394.64 4605.4
September 264 307000 282714.97 24285.0
October 198 302000 239947.60 62052.4
November 223 225000 256147.36 -31147.4
December 261 361000 280771.00 80229.0
Totals 259.08 308416.67 279529.02 28887.65
The table here shows the number of customers, actual and forecasted sales along with the variance
(the difference between actual and forecasted sales).
5
Analyzing obtained results, one can see that, on average, the error is:
But there are some exceptions. have a look at months February and June, one will notice, that the
variances are 27.97% and 40.15%, respectively. Despite this fact, there are months, where the error
is minimal. For example, August, where the error is only 1.49%. DCG made a conclusion that linear
regression is not reliable in 100% of cases, but, generally, the error is quite small.
The New Star Grocery Company can use results, obtained with the help of linear regression model,
and can consider that the error is on average is 9.37%.
The New Star Grocery Company should also make additional research on the number of customers
and the amount of sales. From Year 2 Actual Sales data it can be seen, that the trend became
different: average bill for one customer became higher. This means that each customer started to
spend more in the New Star Grocery Company.
Therefore, the Company should include such trend in future predictions and analysis. This trend can
be expressed as a constant coefficient (for example, alpha). As well, the Company can use other
useful approaches and methods to make the results more valid and truthful, so that predictions will
be almost the same, as actual data.
Conclusions
Linear regression model was used to find dependence in this research. The results of calculations
showed, that hypothesis the greater the customer traffic, the greater the sales are truthful.
DCG created trend line equation, based on linear regression. Using this equation, the Group was
able to predict future sales for Year 2.
6
The results showed that there is an error 9.37% between actual and forecasted sales data. This error
is relatively small for this model.
But taking into account actual sales data for Year 2, Diligent Consulting Group made a conclusion,
which there is a tendency of increasing of an average bill among customers of the New Star Grocery
Company. Therefore, the Group proposes to use a special coefficient in the trend line equation or
improve the model in the future. This will help to make predictions more accurate.
Reference List
Chase, C. W. (2013). Demand-driven forecasting: A structured approach to forecasting. New
York. 97-98.
7
Hron, K., Filzmoser, P., Thompson, K. (2009). Linear Regression with Compositional
Explanatory Variables. Journal of Applied Statistics. 1-2.
Weisberg, S. (2012). Applied Linear Regression. New York. 34-36.
Zou, K., Tuncali, K., Silverman, S. (2003). Correlation and Simple Linear Regression.
Harvard Medical School, USA. 5-6.