Question

The Excel file: OfficeEnergy.xlsx contains information recorded on a sample of 40 of the company’s city branches. The file contains the amount of renewable energy (kWh) consumed in the previous year and the size (square metres) of each branch. Download these data and fit a linear regression model for predicting RenewableEnergy (Y) from Size (X).

Use your Excel output, and any other information provided, to answer the following questions. For each question, either choose the most correct option, or type in the answer to the number of decimal places specified.

Size	RenewableEnergy
36	3106.946
38	3255.913
30	2847.328
33	3111.678
42	3007.902
35	3242.256
33	3162.706
34	2982.56
36	3253.409
26	3136.54
42	3380.081
29	3114.586
35	3342.566
35	3006.045
35	3377.869
40	3203.741
38	3364.498
43	3212.088
33	3258.666
35	3430.249
33	3463.207
41	3148.291
36	3144.409
35	3219.305
40	3253.939
45	3507.016
34	3035.045
37	3269.05
33	3121.051
40	3322.869
40	3412.624
40	3240.611
28	3240.646
35	2967.708
39	3380.973
31	3007.116
39	3342.504
35	3281.907
38	3241.095
35	3251.641

Answer the following questions by choosing the most correct option or typing the answer:

1. (1 mark) The slope of the least squares regression line is positive or negative

2. (1 mark) The largest branch in size used the greatest amount of renewable energy in the previous year true or false

3. The branch with the largest residual had used 3463.207 kWh of renewable energy in the previous year and was 33 square metres in size. Calculate the value of the residual. Type your answer with 3dp Answer

4. The absolute value of the test statistic for testing the slope of the regression line is: Answer (3dp) with degrees of freedom equal to: Answer (integer).

5. (1 mark) The p-value for testing the slope of the regression line is less than 0.05. true or false

6. (1 mark) The correlation is: Answer (3dp - remember to include a negative sign if appropriate)

7. Use the regression equation to predict the renewable energy consumption for a branch that is 32 square metres in size (3dp): Answer

8. We are 95% confident that for each extra square metre in the size of a branch, the average annual renewable energy consumption is expected to increase or decrease? between Answer kWh (lower limit) and Answer kWh (upper limit). Type your answers with 3dp.

Answer 1

we will solve it by using excel and the steps are

Enter the Data into excel

Click on Data tab

Click on Data Analysis

Select Regression

Select input Y Range as Range of dependent variable as renewable energy.

Select Input X Range as Range of independent variable as Size.

click on labels if your selecting data with labels

click on ok.

So this is the output of Regression in Excel.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.407
R Square	0.166
Adjusted R Square	0.144
Standard Error	138.221
Observations	40.000
ANOVA
	df	SS	MS	F	Significance F
Regression	1.000	144364.004	144364.004	7.556	0.009
Residual	38.000	725988.994	19104.974
Total	39.000	870352.998
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	2688.577	193.187	13.917	0.000	2297.490	3079.663
Size	14.636	5.324	2.749	0.009	3.858	25.415

a)The slope of the least squares regression line is positive or negative?

The slope = 14.636 and it is positive.

b)The largest branch in size used the greatest amount of renewable energy in the previous year.

False

c)The branch with the largest residual had used 3463.207 kWh of renewable energy in the previous year and was 33 square metres in size. Calculate the value of the residual.

renewable energy = 2688.577+14.636*Size

renewable energy = 2688.577+14.636*33

renewable energy = 3171.565

residual = actual value - predicted value

residual = 3463.207 -3171.565

residual = 291.642

d)The absolute value of the test statistic for testing the slope of the regression line is

t-statistic= 2.749 with 38 degrees of freedom

e) The p-value for testing the slope of the regression line is less than 0.05

True

f)The correlation is 0.407

g) Use the regression equation to predict the renewable energy consumption for a branch that is 32 square metres in size

renewable energy = 2688.577+14.636*Size

renewable energy = 2688.577+14.636*32

renewable energy = 3156.929