I need the answer in excel. i can upload the excel sheet with the data if needed

Data for textbook problems

WileyPLUS contains a link for each chapter, “Problem Files in Excel: Chapter <nn>”. By clicking on this

you get an Excel file with data sets for each problem that has one—one problem to a tabbed worksheet.

Select the problems you need and copy/paste each one of them to its own tabbed worksheet in your own

assignment file; I don’t need to see the entire chapter, just the assigned problems. Label the tabs. The

Excel Analysis Toolkit is used for everything.

Part A: Correlation

Do problem 12.5 using the Excel Correlation toolkit procedure. You should access the entire block of

data. Then the procedure will give you a correlation matrix. This will include correlation coefficients of

each variable with itself, All these = 1.

Part B —- Using Excel Automated Regression Toolkit routine:

Do problems 12.9, 12.49, 12.57 and 12.66 by Regression Toolkit. Be sure to tell Excel to draw the

“line fit plots” and afterwards stretch them out until they are readable. Type or hand-write comments, as

requested, below the computer output. It is helpful to actually see the lines in “line fit plots” for ch 12

problems. To do this you must right-click on a predicted point and click on “Format data series”.

Depending on which version of Excel you have, the options look different, but you are wanting to change

from data points to a line which will be straight. For recent versions of Excel you specify “Marker type”

as “None” and then specify “Line color” as “Solid line”. As I say in the lecture materials, for multiple

regression (Ch 13) there is unlikely to be any straight lines in the “line fit plots”, and there is usually no

benefit in doing them.

Also run the Excel Correlation toolkit procedure on the data for 12.9. How does this result compare

with the regression results?

Do 13.5 as-is and then repeat using only gasoline as independent variable — discuss results. Which

model do you prefer? Why? (Note: It appears that the numbers in this problem are not realistic, so do not

take them seriously. Personal Consumption for the USA is really much smaller. We are not THAT rich.)

There was a different version in earlier editions of the text. The old numbers were also messed up — in

that case, by conflicting definitions of variables. “Paper”, not “Gasoline” was the significant variable in

the old case. One time I tried to create a version of this problem using real data, but results were not


Also using Excel, do 13.27, 13.29, 13.30. For Chapt. 13 do not present any plots.

Presentation of regression results :

It is important (read–“on the test”) that you can relate the numbers in the output to the concepts in the

textbook. I ask you to do tables of results solely for this reason.

Turn in the Excel outputs for the Part B problems. Also type up a set of tables summarizing your

results for each regression problem. These can either be on the page below each problem or on a separate

page at the end. The headings should be as follows:

(they are for columns in a worksheet)

Prob R-squared SSE se Variable Coefficient sb t p

For each problem there should be a 1st line containing the problem number, r-squared, SSE, se For 2nd

and additional lines you can obviously leave the Prob, R-squared, SSE, and se columns blank, but these

lines should contain variable name (or “Intercept”), coefficient, sb, t, and p in the appropriate columns.

You can round the numbers appropriately. After the last line containing a coefficient, type in a line

containing the equation with all the coefficients, i.e. ” yhat = ……”.

For example, for the example problem for real estate prices (no problem number) your summary should

look like:

Prob R-squared SSE se Variable Coefficient sb t p

Demo 0.741102 2861.02 11.9604

Intercept 57.351 10.007 5.731 1.31*10^-5

SqFt 0.01772 0.003146 5.633 1.64*10^-5

AgeYr -0.6663 0.2280 -2.923 0.0084

yhat = 57.351 + 0.01772*SqFt – 0.6663*AgeYr

If you want to copy and paste the cells without rounding like I did above for hand typing, that’s fine. You

can also copy and paste 1.31E-05, etc as long as you know it means the same as above (i.e. 1.31*10-5,