Here is my answer :)

Simple Linear Regression Problem

Excel Problem
The owner of Original Italian Pizza restaurant chain wants to understand which vairable most
strongly influences the sales of his specialty deep-dish pizza. He has gathered data on the monthly
sales of other potentially relevant variables for each of several outlets in central Indiana. These
data are provided in the file P10_04.xlsx. Estimate a simple linear regression equation between
the quantity sold (Y) and each of the following candidates for the best explanatory variable:
average price of deep-dish pizzas, monthly advertising expenditures, and disposable income per
household in the areas surrounding the outlets. Which variables is most strongly associated with
the number of pizzas sold? Explain your choice.

To make a simple linear regression equation we need to use Excel Data Analysis.
1.
2.
3.
4.

Open the excel sheet P10_04.XLSX
Select the tab Data and select Data Analysis.
Then, select Regression and press Accept
Then, select the input range Y for the quatity sold and X for the differents variables
average price of deep-dish pizzas, monthly advertising expenditures, and disposable
income per household.

We will make three simple linear regressions, the first one will be between the quatity sold (Y) and
Average price of deep-dish pizzas (X); the second one will be between the Quantity sold (Y) and
Monthly advertising expenditures; and the last one will be between Quantity sold (Y) and
Disposable income per household.

1

Simple Linear Regression Problem
First Estimation: Quantity sold (Y) and Average price of deep-dish pizzas (X)

SUMMARY OUTPUT
Regression Statistic
Multiple R
0.350203681
R Square
0.122642618
Adjusted R Square
0.055153589
Standard Error
12800.1112
Observations
15

-0.35020368

ANOVA
df
Regression
Residual
Total

Intercept
Variable X 1

1
13
14

SS
297738993.6
2129957006
2427696000

MS
F
Significance F
297738994 1.81722303 0.200665334
163842847

Coefficients Standard Error
t Stat
P-value
Lower 95% Higher 95% Lower 95% Higher 95%
117762.5535
40210.81578 2.92862881 0.01174426
30892.3674 204632.74 30892.3674 204632.7395
-4713.461504
3496.518649 -1.34804415 0.20066533 -12267.2308 2840.30779 -12267.2308 2840.307791

Figure 1. Summary Output Simple linear regression between Quantity sold (Y) and Average price
of deep-dish pizzas (X).

Using the excel function “CORREL(Data!B2:B16,Data!C2:C16)” we obtain that the correlation
coefficient is 0.4835 which means a weak downhill relationship.
From the summary output of the regression between Quatity sold (Y) and Average price of deepdish pizzas (X) we can see that the coefficient of correlation is equal to -0.3502 and the simple
linear regression equation is:
𝑌 = 𝑎 + 𝑏𝑋
Where,
a: Intercept = 117762.55
b: slope = -4713.46

𝑌 = 117762.55 − 4713.46𝑋1
So,
𝑄𝑢𝑎𝑛𝑡𝑖𝑡𝑦 𝑠𝑜𝑙𝑑 = 117762.55 − 4713.46(𝐴𝑣𝑒𝑟𝑎𝑔𝑒 𝑝𝑟𝑖𝑐𝑒 𝑜𝑓 𝑑𝑒𝑒𝑝 𝑝𝑖𝑧𝑧𝑎)

2

Simple Linear Regression Problem
Second Estimation: Quantity sold (Y) and Monthly advertising expenditures.

SUMMARY OUTPUT
Regression Statistic
Multiple R
0.9339944
R Square
0.87234554
Adjusted R Square
0.86252597
Standard Error
4882.51372
Observations
15
ANOVA
df
Regression
Residual
Total

SS
MS
1 2117789777 2117789777
13 309906223 23838940.2
14 2427696000

F
Significance F
88.837413 3.55929E-07

CoefficientsStandard Error t Stat
P-value
Lower 95% Higher 95% Lower 95% Higher 95%
-32655.0449 10304.6051 -3.16897586 0.00739643 -54916.79083 -10393.2989 -54916.7908 -10393.2989
1.75476417 0.18617476 9.4253601 3.5593E-07 1.352558048 2.15697029 1.35255805 2.15697029

Intercept
Variable X 2

Figure 2. Summary Output Simple linear regression between Quantity sold (Y) and Monthly
advertising expenditures (X).

Using the excel function “CORREL(Data!B2:B16,Data!D2:D16)” we obtain that the correlation
coefficient is 0.4835 which means a strong uphill relationship.
From the summary output of the regression between Quatity sold (Y) and Monthly advertising
expenditures (X) we can see that the coefficient of correlation is equal to 0.9340 and the simple
linear regression equation is:
𝑌 = 𝑎 + 𝑏𝑋
Where,
a: Intercept = -32655.04
b: slope = 1.7548
𝑌 = −32655.04 + 1.754...