please read through the pdf instruction file and use the excel sheets spe]readsheet for each chapter to answer the questions from the pdf instruction file. Please before contacting me show that you understand what needs to be done.
Singleton, Fall ’14
Data for textbook problems
WileyPLUS contains a link for each chapter, “Problem Files in Excel: Chapter
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: ANOVA
Do problems 11.11, and 11.13 using the Excel ANOVA: Single Factor procedure. Incidentally note that
problem 11.13 appears to involve ordinal-level data, whereas in Chapter 1 we talked about the idea that it
was only proper to use interval- and ratio-level data in analyses. (In the real world there is frequent use of
ordinal-level data.)
Part B: 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 C —- 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
enlightening.
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 C 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,
etc).
Mail Typist Recept. Sec. Tele
1 4 5 3 7 6
2 2 4 4 5 4
3 3 3 2 6 7
4 4 4 4 5 4
5 3 5 1 3 5
6 3 4 2 7 7
7 2 2 2 4 4
8 3 4 3 6 6
