# Excel Problem solutions are attached

User Generated

PE77

Mathematics

## Description

Word doc : has the problem questions and the solutions and steps for solving the problem (Problem Q'S & Solutions.docx)

Excel file : has the data needed to be used in order to get the answers using excel functions(P10_04.xls)

Note : YOU MUST USE EXCEL FORMULAS AND FUNCTION TO GET THE RESULTS ITS AN EXCEL CLASS

### Unformatted Attachment Preview

Outlet Number 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Quantity Sold 85.300 40.500 61.800 50.800 60.600 79.400 71.400 70.700 55.600 70.900 77.200 63.200 71.100 55.500 42.100 Average Price \$10,14 \$10,88 \$12,33 \$12,70 \$12,29 \$9,79 \$11,26 \$11,23 \$11,97 \$12,07 \$10,68 \$12,49 \$12,36 \$9,96 \$11,77 Monthly Advertising Expenditures \$64.800 \$42.800 \$58.600 \$46.500 \$50.700 \$60.100 \$55.600 \$57.900 \$52.100 \$60.700 \$64.400 \$55.600 \$60.900 \$47.200 \$46.100 Disposable Income per Household \$42.100 \$38.300 \$41.000 \$43.300 \$44.000 \$41.200 \$41.700 \$43.600 \$39.900 \$44.800 \$41.800 \$44.200 \$40.100 \$39.100 \$38.000 This is fictitious data.
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

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
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
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

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...

### Review

Anonymous
I was struggling with this subject, and this helped me a ton!

Studypool
4.7
Indeed
4.5
Sitejabber
4.4