Forecasting using Simple Linear Regression: For this forecasting we will use historical data for Pizza sales versus Advertizing dollar spent. Instead of hand calculating the regression equation, we will use Excel to do the work for us. What you will be calculating with Excel are the two coefficients in a Regression Equation (reference in your book on Page 175). "a" is the Y-intercept and b is the slope of the line. Linear Regression Formula y=a+bx

Data Table

Month 2017

Historical Pizza Sales in $ by Period (x$1000)

Historical Advertizing $ Spent by Period (x1000)

Y - Sales Actual

X - Advertising Actual

1

6

0.4

2

7

0.6

3

11

0.5

4

14

1.0

5

14

1.1

6

15

1.3

7

17

1.3

8

21

1.8

9

23

1.9

10

28

2.0

11

28

2.0

12

29

2.0

Exercise Instructions

Step 1

To begin, you must first add the Analysis Tool pack if it has not already been installed. To check if it is installed go to "Data" and Look to the far right of the tool bar to see if "Data Analysis" is installed. If not, follow these directions to add Data Analysis: Click on the "File" tab in the top row of the banner. Then, select "Options" from the drop down menu and then click on "Add-ins." Look for an Add-in titled "Analysis Tool Pack" and click on it to add to your tool bar. Look again in Data to the far left and see if you now have "Data Analysis.

Step 2

You are now ready to complete a Regression analysis of pizza Sales (Y-value) and Advertising (X- Value). As you will learn (or may have already learned) in Data Analytics (statistics) The X-value is the independent or predictor variable and the Y-value is the dependent or outcome variable. the standard formula for a linear equation is y = a +bx. Wherea is the "Y-intercept" and b is the slope of the linear regression line. What you are going to find through the "Regression" function (using the data analysis package you just installed) are the two coeficients "a" and "b." So, let's get started by first, going into the banner heading labeled "Data" and then, second, clicking on Data Analysis. Next, scroll down and find "Regression" and open it up by clicking on "Regression. You should have opened a panel that looks exactly like what is off to the side of these instructions except there will be nothing in the input Y range, input X range, the Labels box won't be checked, AND, the output rante will not be selected yet. That is the next step.

Step 3

Next, you will need to highlight your Dependent variable (y) and your independent Variable (x), highlight only from B5 to B17 for the Y-variable and from C5 to C17 for the X-variable. Next check the box that tells the tool pack you are including lables or titles for each column (Y-Sales and X-Advertising). Next, check the "OutPut Range" button and use the window to tell your tool pack where to put the analysis. You can but don't need to check the "Line Fit Plots" as shown in the window beside these instructions. Hit OK and you have now run a regression analysis. Look for the part of the analysis that looks like the window to the left side.

Step 4

To "fill out" the Formula for a linear equation, Y = a + bx, we need two constants, a, the y-intercept and b, the slope of the regression line. The print out you get from your regression analysis, should have a section that looks like the one printed to the side of these instructions. and what I have highlighted are the a and b values... So, when you stick these constants into the regression formula you get Y = .9976 + 12.64X. Now, for every value of X, you can calculate Y. Remember, there is always some "error" in this regression formula due to the line being an approximation for the y value. Your last assignment is to take one x value, stick it into the formula and show the approximation for Y. So, insert the Y-sales actual and the X-advertising Actual you want to check into the table below, insert the values for "a" and "b" you get from the regression analysis. Then use the formula you create to calcuate the approximate. Then calculate the difference between the Y-sales actual and the Y-sales Approximate to calculat the error in the Linear regression formula.

Task 1

Calculate the Y-sales approximate using the constants you obtained from the regression analysis (use month 6 and fill in the first 4 columns, then install the correct values for "a" and "b" in the last two columns. Finally, use the formula under the "Y-sales Approximation" to calculate the value you regression equation gives you for the new Sales value. Finally, calculate the difference.

Month

Y - Sales Actual

X - Advertising Actual

Y - Sales Approximate

Y-difference

a

b

6

(From table above)

(from table above)

(from table above)

Use the formula below:

Use:

from

from

=G28 +H28*D28

=C28-E28

Reg. Anal

Reg Anal