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

Purchase answer to see full attachment

## Explanation & Answer

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