Data Analysis with Microsoft Excel: Updated for Office 2007

386 Statistical Methods

1. Use Excel’s FINV function to calculate
   the critical value for the following
   F distributions (assume that the
   p value 5 .05):
   a. Numerator degrees of freedom 5 1;
   denominator degrees of freedom 5 9.
   b. Numerator degrees of freedom 5 2;
   denominator degrees of freedom 5 9.
   c. Numerator degrees of freedom 5 3;
   denominator degrees of freedom 5 9.
   d. Numerator degrees of freedom 5 4;
   denominator degrees of freedom 5 9.
   e. Numerator degrees of freedom 5 5;
   denominator degrees of freedom 5 9.


2. Use Excel’s FDIST function to calculate the
   p value for the following F distributions

(^1) assume that the critical value 5 3.5 (^2) :
a. Numerator degrees of freedom 5 1;
denominator degrees of freedom 5 9.
b. Numerator degrees of freedom 5 2;
denominator degrees of freedom 5 9.
c. Numerator degrees of freedom 5 3;
denominator degrees of freedom 5 9.
d. Numerator degrees of freedom 5 4;
denominator degrees of freedom 5 9.
e. Numerator degrees of freedom 5 5;
denominator degrees of freedom 5 9.

3. Which of the following models can be
   solved using linear regression? Justify
   your answers.
   a. y 5 b 01 b 1 x 11 b 2 x 2 1e

b. y 5

b 0 x

b 11 x

1e

c. y5b 0 1b 1 sin x1b 2 cos x1e

4. What is collinearity?


5. The Wheat workbook contains nutritional
   data on ten different wheat products.
   You’ve been asked to determine the

relationship between calories, carbohy-

drates, protein, and fat.

a. Open the Wheat workbook from the

Chapter09 folder and save it as Wheat

Multiple Regression.

b. Generate the correlation matrix for

the variables Calories, Carbo-Fiber

(the carbohydrate value minus the

fi ber value), Protein, and Fat. Also

create the corresponding scatterplot

matrix.

c. Regress Calories on the other three

variables and obtain the residual out-

put. How successful is the regression?

It is known that carbohydrates (once

adjusted for fi ber content) have 4 calo-

ries per gram, protein has 4 calories per

gram, and fats have 9 calories per gram.

How do the coeffi cients compare with

the known values?

d. Explain why the coeffi cient for fat is

inaccurate, in terms of its standard

error and in comparison with the

known value of 9. (Hint: Examine the

data and notice that the fat content is

specifi ed with the least precision.)

e. Plot the residuals against the pre-

dicted values. Is there an outlier?

Label the points of the scatter plot

by food brand to see which case is

most extreme. Do the calories add

up correctly for this case? That is,

when you multiply the carbohydrate

content (adjusted for fi ber content)

by 4, the protein content by 4, and

the fat content by 9, does it add up

to more calories than are stated on

the package? Notice also that an-

other case has close to the same val-

ues of Carbo-Fiber, Protein, and Fat,

but the Calories value is 10 higher.

How do you explain this? Would

Exercises