Question

Heating Cost Sy Outside Temp Ideal Attle Insalatice inches) 2 Age of Furnace years) x3 The following data points were collected at random from a sample of 10 recently sold homes. What is the estimated heating cost for a home if; the mean outside temperature is 32 degrees, there are 7 inches of insulation, and the furnace is 7 years old? 10 165 36 200

Answer 1

We will first create multiple regression model using Excel -> Data analysis function as shown below

Y I-Consolidate Relationships ?, Solver Data Analysis Show Queries From Table Get External New Data Query - Lo Recent Sources Get & Transform 2 Connections al 24 E Properties Refresh All Edit Links Connections Il Sort Clear E Flash Fill Reapply - Remove Duplicates Text to Data Validation Advanced Columns Data Tools Sort & Filter till Group Ungroup -- Subtotal Outline E What If Forecast Analysis - Sheet Forecast Led Edit Links Analysis 118 - fic X в А с E F G H I J K L M N O A 36 43 60 65 2 Homes Sold Heating cost $| Mean outside Temp x1 Attic insulation x2 Age of furnace years x3 3 250 35 2 360 29 10 53 165 3 64 6 9 92 86 200 30 5 97 355 10 6 10 8 290 7 10 10 11 9 230 219 11 12 10 120 55 2 5 13 5 | 14 15 16 17 18 19 20 21

Go to Data -> Data Analysis ->select Regression and set up table as below:

2 x Clear 1- Consolidate Relationships ? Solver Data Analysis 1 Show Queries From Table Get External New Data Query - Lo Recent Sources Get & Transform Flash Fill 3- Remove Duplicates Text to Data Validation Advanced Columns Data Tools Reapply Connections E Properties Refresh All Edit Links Connections Filter Āj Sort What-If Forecast Analysis Sheet Forecast Group Ungroup -- Subtotal Outline Sort & Filter Analysis - : V fix 118 2 X B A C D E F G Regression 1 Input ок Input Y Range: $B$3:$B$12 Cancel 29 Input X Range: $C$3:$E$12 mno00 165 2 Homes Sold Heating cost $ Mean outside Temp x1 Attic insulatiion X2 Age of furnace years x3 31250 353 2 360 4 10 3 36 73 6 4 43 60 6 9 5 92 65 5 6 200 30 5 7 355 6 10 8 290 10 10 119 230 21 9 11 12 10 120 55 2 5 Help Labels Confidence Level: Constant is Zero 95 % 10 Output options 7 $1$9 13 O Output Range: O New Worksheet Ply: New Workbook Residuals Residuals Standardized Residuals 14 Residual Plots Line Fit Plots 16 17 18 Normal Probability Normal Probability Plots 19 20 21 22

Below is the regression output.

1- Consolidate Relationships Show Queries From Table Get External New Data Query - Lo Recent sources Lo Recent Sources Get & Transform ? Solver Data Analysis Connections E Properties Refresh All All Locat Edit Links Connections Group - Ungroup -- Subtotal Clear B Flash Fill Reapply 5- Remove Duplicates Filter Text to Advanced Columns - Data Validation Sort & Filter Data Tools Zj Sort What-If Forecast Analysis - Sheet Forecast Outline Analysis K14 - X fc B C D E F K L M N O P Q R A SUMMARY OUTPUT 1 Regression Statistics 4 Multiple R 0.948771488 5 R Square 0.900167337 6 Adjusted R Square 0.850251006 7 Standard Error 41.59057095 8 Observations 10 9 SS MS F gnificance F 3 93581.85 31193.95 18.03352 0.002093 6 10378.65 1729.776 9 103960.5 10 ANOVA 11 12 Regression 13 Residual 14 Total 15 16 17 Intercept 18 X Variable 1 19 X Variable 2 20 X Variable 3 21 Coefficients andard Errit Stat P-value Lower 95%Upper 95%ower 95.09pper 95.0% 475.5153174 68.47563 6.9443 0.000442 307.9615 643.0691 307.9615 643.0691 -5.734279062 0.837752 -6.84484 0.000478 -7.78418 -3.68437 -7.78418 -3.68437 -22.40225134 7.126119 -3.14368 0.019974 -39.8392 -4.96527 -39.8392 -4.96527 8.643114798 5.991852 1.442478 0.199264 -6.01842 23.30465 -6.01842 23.30465

Hence, the regression equation is :

Y (Heating cost $) = 475.515 + (-5.734) X1 + (-22.402) X2 + (8.643) X3

For mean outside temperature (X1) = 32 degrees,

Insultation (X2) = 7 inches

Furnace (years) = 7 years

Y (Heating cost $) = 475.515 + (-5.734)*32 + (-22.402)*7 + (8.643)*7

Y (Heating cost $) = 195.714

Heating cost $ is 195.714

Please give thumbs up/ likes if you find this answer helpful. Thank you!