Question

Can someone just answer 8 A B C D and E please!!!

Regression Analysis (Excel 2010 & 2007)

1.         Open a new Excel worksheet (which will be saved as REGRESSION.xlsx).  In cell A1 type your name.  In cell A2 type the course and section number (i.e. ECON225-01).  In cell A3 type the date.  Skip cell A4.  In cell A5 type “Assignment: Regression Analysis”.  In cell A6 type “File: REGRESSION.xlsx”.

2.         Type X in cell B8 and type Yin cell C8.  Type Miles in cell B9 and type Minutes  in cell C9.

In cells B10 through B18 enter the following values:

11, 10, 15, 7, 3, 6, 9, 12, 5

                        In cells C10 through C18, enter the following data values:  

                        28, 27, 35, 15, 8, 14, 20, 29, 13  

            Center format cells B8 through C18 for a more professional appearance.

3.         Click on the Datatab in the toolbar, then select Data Analysis. Next, select   Regressionfrom the Analysis Tools and click on OK.  In the Regression dialog boxes type C10:C18in the Input Y Range dialog box, then type B10:B18in the Input X Range dialog box.  Under Output Options select Output Range and type A20:I40in the output range dialog box.  Click onOK. A Summary Output table will appear.

4.         Select cell D27 and type Forecast for Y when X = 13:  Next select cell G27, then click on the Formulas tabin the toolbar, then select More Functions. Under the Function category select Statistical.  Under the Function name select  FORECAST.  In the dialog boxes type 13in the X dialog box, type C10:C18in the Known Y’s dialog box, and type B10:B18in the Known X’s dialog box.  Click on OK. The forecasted value for Y when X=13 will appear in cell G27.

5.         Return to the Home tab in the toolbar.  Select the columns of X and Y data values from B10 through C18(do not select their headings).  Next, click on the Insert tabin the toolbar, under Charts select Scatter, then select the first choice of a scatter diagram graph.    Resize and reposition the scatter diagram to the location of cell E9 for the top left corner of the diagram, and cell I 22 for the bottom right corner of the diagram.  (This will allow everything to fit on one printed page.) Delete the “Series 1” label box. You can label the axes with the variable names (Miles and Minutes) by clicking on the outside corner of the graph, then select Axis Titles in the toolbar. Label both the X and Y axes of the graph with their appropriate variable names.

            (Instructions continue on the next page.)

6.         Next, click any place inside of the scatter diagram. Under the Analysis options, click on Trendline,thenselectLinear Trendline. Click on OK. A trend line will be added to the scatter diagram.  Do a Print Preview to make sure that your graph fits onto the printed page.

7.         Save your worksheet on a disk as REGRESSION.xlsx and print-out the worksheet to submit to the instructor.  

8.         In addition to submitting a print-out of the worksheet(s), also submit typed answers to the following questions, referencing the data in your print-out and your textbook or Notes:

(a)        What is the regression equation for this data set? (Write the printed “a” and “b” values into the equation.  Hint: Under the Coefficient column the value for the Intercept is the value for “a” and the X Variable value is the value for “b”.)

(b)       Interpret the printed value for “a” relative to its definition, the X and Y variable names, and its value.  

(c)        Interpret the printed value for “b” relative to its definition, the X and Y variable names, and its value.

(d)       Interpret the printed value for “r” relative to its definition, the X and Y variable names, and its value.  (**Hint: Under the Regression Statistics section the Multiple R value is the correlation coefficient and the R Square value is the Coefficient of Determination.  The printed table value for “r” does not always indicate direction (+ or -), therefore, check that the sign for your “r” value agrees with the sign for your “b” value.)  

(e)        Interpret the printed value for “r²” relative to its definition, the X and Y variable names, and its value.  

Answer 1

Answer (1, 2 and 3):

Regression Analysis using MS-Excel:

Enter the given data into Excel worksheet as:

Miles (x)	Minutes(y)
11	28
10	27
15	35
7	15
3	8
6	14
9	20
12	29
5	13

1.Enter the data into Excel sheet.

2.If this is the first time you have used an Excel add-in, click the File tab, otherwise skip to step 7.

3.Click Options from the list on the left.

4.Select Add-ins in the Excel Options box.

5.In the Add-in list box, select Analysis Toolbox-VBA from the Inactive Application Add-ins list.

6.Click OK.

7.Then select Data/ Data Analysis tab from the menu bar.

8.The Data Analysis dialog box will appear on the screen.

9.From the Data Analysis dialog box, select Regression and click OK.

10.The Regression dialog box will appear on the screen.

11.Place independent variables (Miles) in Input X Range and place dependent variable (Minutes) in Input Y Range.

12.Place appropriate confidence level in Confidence Level box. (If necessary)

13.Click OK.

Regression Analysis output:

Summary:

Regression Statistics
Multiple R	0.984992
R Square	0.97021
Adjusted R Square	0.965954
Standard Error	1.681016
Observations	9

ANOVA Table:

	df	SS	MS	F	Significance F
Regression	1	644.2193	644.2193	227.9765	1.34E-06
Residual	7	19.7807	2.825815
Total	8	664

	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	0.397661	1.475067	0.269588	0.795251	-3.09032	3.88564
Miles	2.377193	0.157442	15.09889	1.34E-06	2.004903	2.749483

Answer (8 A):

From above output, the regression equation is given as:

Answer (8 B):

The intercept (printed value of a) is the expected mean value of minutes (Y) when all miles =0 (X=0). If miles sometimes equals 0, the intercept is simply the expected mean value of minutes at that value.

Answer (8 C):

The slope (printed value of b) is 2.3772, you can write this as 2.3772/1 and say that as you move along the line, as the value of the miles (X variable) increases by 1 unit, the value of the Minutes (Y variable) increases by 2.3772 unit.

Answer (8 D):

From the above output, r = 0.9850. Therefore, we can say that, there is strong positive (perfect) relationship between miles and minutes variables. That is, they are related with each other.

Answer (8 E):

From the above output, r² = 0.9702. The coefficient of determination r² represents the fraction of the variance of minutes (Y) "explained" by the correlation of minutes with miles (Y with X). It gives the proportion of the variance (fluctuation) of one variable that is predictable from the other variable.

This means that 97% of the variance of minutes (Y) is explained by the correlation: the correlation is good. The other 3% of the total variation in minutes (Y) remains unexplained.

In other words an r² of 0.97 means that 97% of the variance in minutes (Y) is predictable from miles (X).

Answer (4):

Forecast for y when x = 13:

Answer (5 and 6):

Scatter Plot: