**Scenario: You are a consultant who works for the Diligent Consulting Group. Your client, theNew Star Grocery Company, believes that there may be a relationship between the number ofcustomers who visit the store during any given month (“customer traffic”) and the total salesfor that same month. In other words, the greater the customer traffic, the greater the sales forthat 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 AssignmentUsing the customer traffic data and matching sales for each month of Year 1, create a LinearRegression (LR) equation in Excel, assuming all assumptions for linear regression have beenmet. Use the Excel template provided (see �Module 2 Case � LR �Year 1� spreadsheettab), and be sure to include your LR chart (with a trend line) where noted. Also, be sure thatyou include the LR formula within your chart.After you have developed the LR equation above, you will use the LR equation to forecastsales for Year 2 (see the second Excel spreadsheet tab labeled “Year 2 Forecast”). You willnote that the customer has collected customer traffic data for Year 2. Your role is to completethe 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 actualsales 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 youused above, and that analyzes the results for Year 2. (What is the difference between forecastvs. actual sales for Year 2 by month and for the year as a whole?) Make a recommendationconcerning how the LR equation might be used by New Star Grocery Company to forecastfuture sales.Data: Download the Module 2 Case template here: Data chart for BUS520 Case 2. Use thistemplate to complete your Excel analysis.Excel AnalysisConduct accurate and complete Linear Regression analysis in Excel. Use Excel support to findinformation 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.