Description
Please use the excel formulas and answer the questions as wanted. Thank you
Unformatted Attachment Preview
House
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Appraised Value
119,370
148,930
130,390
135,700
126,300
137,080
123,490
150,830
123,480
132,050
148,210
139,530
114,340
140,040
136,010
140,930
132,420
118,300
122,140
149,820
128,910
134,610
121,990
150,500
142,870
155,550
128,500
143,360
119,650
122,570
145,270
149,730
147,700
117,530
140,130
136,570
130,440
118,130
130,980
131,330
141,100
117,870
160,580
151,100
120,150
133,170
Square Feet Bedrooms Bathrooms
2050
4
5
2200
4
4
1590
3
3
1860
3
3
1210
2
3
1710
3
2
1670
3
3
1780
3
4
1520
4
4
1830
2
3
1700
3
3
1720
3
4
1670
3
4
1650
3
3
1610
2
1
1570
3
4
1650
4
5
1640
3
4
1420
2
3
2070
4
3
1610
2
3
1910
4
4
1410
2
2
1860
4
3
1990
4
3
2270
5
4
1965
4
4
1820
3
3
1650
3
3
1470
2
2
1850
4
3
2170
4
4
1930
3
3
1380
2
1
1810
3
4
1760
3
4
1530
2
3
1700
3
2
1980
4
4
1590
2
2
1740
3
2
1730
3
2
2100
5
6
2040
4
4
1730
2
1
1680
2
2
Selling Price
121,870
150,250
122,780
144,350
116,200
139,490
115,730
140,590
120,290
147,250
152,260
144,800
107,060
147,470
135,120
140,240
129,890
121,140
111,230
145,140
139,010
129,340
113,610
141,050
152,900
157,790
135,570
151,990
120,530
118,640
149,510
146,860
143,880
118,520
146,070
135,350
121,540
132,980
147,530
128,490
141,930
123,550
162,030
157,390
114,550
139,540
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
140,160
124,560
127,970
101,930
131,470
121,270
143,550
136,890
106,110
137,540
134,330
127,590
137,440
114,090
145,460
141,900
116,340
149,200
141,810
116,440
137,740
144,700
149,660
118,170
137,660
119,700
143,120
129,910
141,780
159,190
156,130
126,720
133,220
118,090
141,630
138,560
134,100
132,240
145,820
127,150
105,070
127,200
111,560
150,410
129,150
130,310
129,230
2050
1750
1870
1330
1700
1460
1910
1610
1470
1810
1650
1520
1920
1410
2030
1950
1340
1850
1780
1440
2040
2160
2170
1710
1640
1380
2100
1610
1950
2080
1960
1920
1920
1490
1600
1650
1890
1620
1820
1750
1460
1530
1540
1950
1480
2050
1660
4
3
4
2
3
2
3
2
2
3
2
2
4
2
3
3
2
3
3
2
4
5
4
2
3
2
5
2
4
5
3
4
4
2
2
3
4
3
3
3
2
3
2
3
3
4
3
4
3
3
3
3
3
3
1
1
4
2
1
5
1
3
4
1
4
2
1
4
4
3
3
2
1
6
2
4
4
2
4
3
1
1
2
5
4
4
2
1
2
3
2
2
4
2
149,920
122,080
136,510
109,410
127,290
120,450
151,960
132,540
114,330
141,320
83,760
118,200
140,200
113,550
156,520
137,350
110,610
153,690
153,330
111,950
143,460
142,130
155,460
135,440
127,300
113,770
141,110
130,080
139,350
160,030
152,840
122,270
145,880
115,470
135,720
136,160
144,920
131,290
138,530
124,050
107,900
123,450
111,700
145,140
120,440
136,870
140,300
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
105,060
134,210
109,250
127,350
104,010
133,940
141,130
136,530
118,040
153,700
126,310
134,020
141,560
142,960
118,530
121,590
146,400
141,250
130,730
132,650
125,570
125,740
120,220
128,290
136,890
142,430
119,310
120,370
118,830
124,490
140,570
133,620
105,050
147,090
115,430
125,370
115,970
125,210
130,370
119,750
120,930
126,800
118,820
144,080
142,490
140,550
130,360
1540
1720
1400
1578
1700
1880
1850
1430
1390
2130
1890
1640
2070
1810
1460
1410
2190
1580
1840
1930
1930
1820
1480
1600
1880
1780
1920
1830
1470
1690
1930
1970
1530
2030
1830
2070
1630
1580
1760
1810
1710
1890
1640
2010
1730
1770
1750
2
3
2
3
3
4
3
2
2
4
4
2
3
3
2
2
4
3
3
3
4
3
2
2
3
3
3
4
2
3
4
2
2
5
3
3
2
3
3
4
3
3
2
4
3
4
4
3
4
1
4
3
5
4
3
1
5
4
3
3
2
1
2
3
4
3
3
5
4
1
1
2
2
2
3
3
4
5
3
2
6
3
4
3
2
4
5
2
3
1
4
3
3
5
113,780
141,230
104,830
118,790
112,040
137,270
145,710
138,380
109,460
144,680
133,270
133,270
150,380
135,260
112,600
114,230
153,240
125,890
135,620
138,820
129,430
136,450
126,740
130,090
132,680
142,890
127,040
131,450
114,570
129,560
149,550
140,820
111,550
142,760
124,250
132,320
121,450
132,450
135,830
125,760
125,840
135,320
120,140
147,530
144,940
136,010
119,330
141
142
143
144
145
146
147
148
124,270
167,730
129,190
125,180
157,510
126,670
137,570
133,460
1660
2510
1700
1660
2110
1430
1710
1580
3
5
2
3
4
2
4
2
3
6
3
4
5
2
5
1
131,150
172,360
137,170
124,710
148,650
128,520
132,020
128,030
School
Harvard University (MA)
Stanford University (CA)
Northwestern University (Kellogg) (IL)
University of Pennsylvania (Wharton)
Massachusetts Institute of Technology (Sloan)
University of Chicago
University of California–Berkeley (Haas)
Dartmouth College (Tuck) (NH)
Columbia University (NY)
Yale University (CT)
New York University (Stern)
Duke University (Fuqua) (NC)
University of Michigan–Ann Arbor
University of California–Los Angeles (Anderson)
Carnegie Mellon University (PA)
University of Virginia (Darden)
Cornell University (Johnson) (NY)
University of Texas–Austin (McCombs)
Georgetown University (McDonough) (DC)
University of North Carolina–Chapel Hill (Kenan-Flagler)
University of Southern California (Marshall)
Emory University (Goizueta) (GA)
Georgia Institute of Technology (DuPree)
Indiana University–Bloomington (Kelley)
Washington University in St. Louis (Olin)
Ohio State University (Fisher)
University of Washington
University of Wisconsin–Madison
Arizona State University–Main Campus (W. P. Carey)
Brigham Young University (Marriott) (UT)
University of Rochester (Simon) (NY)
Purdue University–West Lafayette (Krannert) (IN)
Texas A&M University–College Station (Mays)
University of Minnesota–Twin Cities (Carlson)
University of Notre Dame (Mendoza) (IN)
Vanderbilt University (Owen) (TN)
University of Florida (Hough)
Rice University (Jones) (TX)
University of Illinois–Urbana-Champaign
Michigan State University (Broad)
Penn State University–University Park (Smeal)
University of California–Davis
University of Maryland–College Park (Smith)
Boston College (Carroll)
University of Iowa (Tippie)
Boston University
Overall Rating Peers Rating
100
4.8
99
4.8
93
4.7
93
4.8
92
4.8
92
4.7
89
4.6
87
4.3
86
4.5
85
4.3
83
4.3
82
4.4
81
4.4
80
4.2
79
4.0
79
4.1
78
4.2
74
4.0
71
3.6
70
4.0
70
3.9
69
3.8
69
3.3
69
3.8
69
3.8
67
3.6
67
3.4
65
3.6
64
3.5
64
3.1
64
3.3
63
3.6
62
3.3
62
3.5
62
3.4
62
3.5
61
3.3
60
3.3
60
3.5
59
3.4
59
3.4
59
3.3
59
3.5
58
3.4
58
3.2
57
3.1
Southern Methodist University (Cox) TX
Tulane University (Freeman) (LA)
Babson College Olin) (MA)
University of Texas–Dallas
55
54
53
53
3.2
3.1
3.4
2.8
Recruiters Rating Avg. GPA Avg. GMAT AcceptRate
4.5
3.66
720
11.5%
4.5
3.64
726
7.5%
4.3
3.51
710
19.4%
4.3
3.50
714
16.3%
4.3
3.54
708
15.0%
4.3
3.50
713
21.9%
4.0
3.56
714
11.7%
4.1
3.44
712
16.0%
4.1
3.40
709
15.1%
4.0
3.52
718
14.4%
3.9
3.43
708
13.6%
4.0
3.40
696
30.4%
4.0
3.32
706
20.1%
3.9
3.50
711
19.5%
3.8
3.35
690
28.3%
4.0
3.35
693
24.6%
3.9
3.30
697
20.6%
3.8
3.39
681
26.7%
3.6
3.35
678
30.2%
3.8
3.30
678
34.3%
3.5
3.32
692
23.0%
3.7
3.40
680
28.1%
3.5
3.40
681
30.1%
3.7
3.35
663
34.0%
3.6
3.42
681
33.8%
3.3
3.41
674
30.5%
3.6
3.38
688
30.9%
3.3
3.36
666
33.1%
3.4
3.43
676
24.2%
3.5
3.52
673
56.4%
3.3
3.50
675
32.9%
3.5
3.40
662
29.9%
3.2
3.39
643
26.1%
3.5
3.31
663
39.1%
3.4
3.30
677
34.1%
3.5
3.30
656
35.7%
3.4
3.34
687
31.4%
3.2
3.29
667
31.1%
3.3
3.40
639
34.9%
3.3
3.24
642
26.9%
3.4
3.20
646
27.8%
3.1
3.40
675
24.9%
3.1
3.34
660
27.8%
3.2
3.30
661
27.9%
3.1
3.37
653
38.7%
3.1
3.38
680
27.8%
Starting Salary First Job
$144,261
$140,771
$130,365
$136,676
$131,087
$130,839
$126,886
$133,407
$130,281
$117,366
$128,968
$122,742
$125,773
$117,253
$122,944
$126,362
$122,776
$116,484
$114,463
$110,202
$104,382
$108,107
$105,266
$109,329
$103,184
$101,146
$98,192
$100,907
$94,599
$102,026
$97,206
$96,470
$96,485
$104,745
$102,028
$103,262
$84,990
$108,596
$97,725
$104,445
$99,655
$93,712
$93,927
$93,581
$90,714
$100,322
Tuition
$50,830
$48,921
$46,791
$53,030
$47,034
$47,938
$40,605
$45,900
$48,566
$47,300
$43,942
$45,813
$45,439
$38,563
$48,204
$45,500
$46,510
$40,900
$41,934
$42,239
$44,502
$41,428
$32,076
$38,221
$41,586
$37,845
$32,451
$26,568
$29,856
$9,240
$40,005
$34,940
$28,022
$39,238
$37,740
$40,917
$24,066
$38,234
$30,352
$29,183
$31,128
$36,894
$42,305
$66,304
$26,167
$37,456
Enrollment
1,801
740
1,254
1,611
788
1,144
500
506
1,234
382
841
875
843
731
392
644
593
540
522
562
426
373
154
474
294
277
223
231
170
314
352
293
164
199
315
390
142
250
198
214
187
120
257
202
140
315
3.2
2.9
3.3
3.2
3.23
3.30
3.18
3.70
642
655
630
651
35.7%
59.7%
47.8%
32.7%
$94,361
$91,844
$105,470
$67,011
$39,308
$45,748
$38,500
$37,265
171
175
387
83
QMB 3200
Homework #8
Instructions:
1) Solve all the problems. Each problem carries 20 points. Maximum score possible for
this Homework is 140 points. When both p-value and critical-value approaches are
asked, you have to use both as points are allocated to various sections and questions
posed in a problem.
2) Presenting only the final answer is not sufficient to get complete credit. Show the steps in
solution approach. That way partial credit can be earned to various steps in final solution.
It is your responsibility to demonstrate mastery of the subject matter through your
answers.
3) Use EXCEL. Doing so will save you plenty of time. Submit your report as a single Excel
file. Solve each problem on a separate tab (worksheet). No Exceptions. DO NOT, I repeat,
DO NOT try to solve the problems using calculator. Organize your solutions on the Excel
worksheet properly. Show where your answers are for each problem and the sections of the
problem. Use proper formatting. Name Your File to show your Full Name and the HW
Number.
4) Upload your report file on Canvas and verify if everything is fine by opening up the uploaded
file. It is your responsibility to ensure your report is uploaded properly.
5) Do not wait until the last minute. The deadline is strictly enforced by Canvas. No hardcopy
submissions are accepted. No e-mail submissions are accepted. If your file does not appear on
Canvas by the deadline, zero points will be recorded for you for that HW. No exceptions are
entertained for any reason under any circumstance in this regard.
1
HW Problems:
1. Jensen Tire & Auto is in the process of deciding whether to purchase a maintenance contract
for its new computer wheel alignment and balancing machine. Managers feel that
maintenance expense should be related to usage, and they collected the information on
weekly usage (hours) and annual maintenance expense (in thousands of dollars).
Note: You would not need sample data to answer the questions
SUMMARY OUTPUT
Regression Statistics
Multiple R
R Square
Adjusted R Square
Standard Error
Observations
0.9272
0.8597
0.8422
4.1466
10
ANOVA
df
Regression
Residual
Total
Intercept
Weekly Usage (in Hrs)
1
8
9
Coefficients
10.3350
0.9586
SS
843.1737
137.5513
980.7250
Standard Error
3.8186
0.1369
MS
843.1737
17.1939
t Stat
2.7065
7.0028
F
49.0391
P-value
0.0268
0.0001
Significance
F
0.00011
Lower 95%
1.5293
0.6429
Upper
95%
19.1408
1.2742
a) Write down what the estimated regression equation is that relates annual maintenance
expense to weekly usage.
b) Test the significance of regression at .05 level of significance using p-value approach
c) Test the significance of regression at .05 level of significance using critical-value approach
d) Comment on how good the relationship fit is. How is that determined?
e) What is R-Sq? How is that determined?
f) What is the value of the correlation coefficient in this problem?
g) Jensen expects to use the new machine 30 hours per week. What is the 95% confidence
interval for the company’s annual maintenance expense (Use the correct units) based on the
above report for a given “x bar” = 30 hours per week.
h) If maintenance contract costs $30,000 per year for a machine, would you recommend
purchasing it for the new machine in part (g)? Why or why not?
2
2. A sales manager collected the following data on annual sales and years of experience.
a) Develop a scatter diagram for these data with years of experience as the independent
variable.
b) Develop an estimated regression equation that can be used to predict annual sales given
the years of experience. Write down the estimated regression equation for average sales.
c) Use the estimated regression equation to predict annual sales for a salesperson with 9
years of experience (Pay attention to Units while answering the question).
d) What are the values for: SST, SSR, and SSE.
e) Compute the coefficient of determination r2. Comment on the goodness of fit.
f) What is the value of the sample correlation coefficient?
g) Conduct p-Value as well as Critical-Value based Hypothesis Tests to determine the
significance of regression. Use α = 0.05. Is the regression statistically significant?
3
3. Sporty cars are designed to provide better handling, acceleration, and a more responsive
driving experience than a typical sedan. But, even within this select group of cars,
performance as well as price can vary. Consumer Reports provided road- test scores and
prices for the following 10 sporty cars. Prices are in thousands of dollars and road- test scores
are based on a 0– 100 rating scale, with higher values indicating better performance.
a) Develop a scatter diagram with price as the independent variable.
b) What does the scatter diagram developed in part (a) indicate about the relationship
between the two variables?
c) Develop the estimated regression equation. Write down the estimated regression equation
for this problem.
d) Provide an interpretation for the slope of the estimated regression equation. (Pay attention
to Units while answering the question).
e) Another sporty car that Consumer Reports tested is the BMW 135i; the price for this car
was $ 36,700. Predict the road- test score for the BMW 135i using the estimated
regression equation developed (Pay attention to Units while answering the question).
f) Find the 98% Confidence and Prediction Intervals for the BMW 135i in part (e).
g) Conduct p-Value as well as Critical-Value based Hypothesis Tests to determine the
significance of regression. Use α = 0.01. Is the regression statistically significant?
4
4. Outside Magazine tested 9 different models of day hikers and backpacking boots. The
following data show the upper support and price for each model tested. Upper support was
measured using a rating from 1 to 5, with a rating of 1 denoting average upper support and a
rating of 5 denoting excellent upper support.
a) Use these data to develop an estimated regression equation to estimate the price of a day
hiker and backpacking boot given the upper support rating. Write down the estimated
regression equation for this problem.
b) At a .10 level of significance, determine whether upper support and price are related.
c) Would you feel comfortable using the estimated regression equation developed in part (a)
to estimate the price for a day hiker or backpacking boot given the upper support rating?
Explain the basis for your answer.
d) Estimate the price for a day hiker with an upper support rating of 4.
e) Find the 90% Confidence and Prediction Intervals for the data in part (d).
f) Provide an interpretation for the slope of the estimated regression equation.
5
5. Copy the first sheet in QMB3200-Homework#8Data.xlsx called “HomePrices” to your file.
This sheet has some data on some homes’ appraised values and selling prices and some other
fields.
a. Find the correlation coefficient for Selling Price and Appraised Value, Selling Price
and Square Feet, Selling Price and Bedrooms and Selling Price and Bathrooms.
Which independent variable is most strongly related to selling price. (Use the Excel
function =CORREL( ) as shown in the chapter word document to find correlation
coefficient between two variables).
b. Create a scatter plot of Selling Price (on y axis) and Appraised Value (x axis).
Interpret the chart. Talk about whether the relationship is linear or not, strong or not.
c. Mentally fit a straight line through the above scatter plot and guess the y-intercept and
the slope.
d. Add a trendline to the scatterplot. Also, display the equation of the trendline.
e. How close was your guess in part d above.
f. Using the data Analysis Toolpak, run Regression between Appraised Value
(independent variable) and Selling price (dependent variable). Use the default 95%
confidence interval.
g. What is the R-square value? Interpret this R-square value.
h. Predict the selling price of a house whose appraised value is 150,000.
i. Predict the selling price of a house whose appraised value is 125,000.
j. Create a model using Num of Bedrooms as the independent variable and predict the
value of a house with 4 bedrooms.
6. Copy the second sheet in QMB3200-Homework#8Data.xlsx called “Top 50 MBA Programs”
to your file. This data is according to US News and World Report, 2009 survey.
a. Which of the variables in columns E through J are most highly correlated with
Overall Rating? (Use the Excel function =CORREL( ) as shown in the chapter word
document to find correlation coefficient between two variables).
b. Run a simple regression with the variable identified above and Overall Rating.
c. What percent of variation in overall rating explained by the variable identified in a?
d. Which of the remaining variables in columns E through J is most highly correlated to
Overall Rating?
e. Run a regression with the second most correlated variable as independent variable to
predict the Overall rating.
f. What percent variation is explained by the variable identified above?
7. An important application of regression analysis in accounting is in the estimation of cost. By
collecting data on volume and cost and using the least squares method to develop an estimated
regression equation relating volume and cost, an accountant can estimate the cost associated with
a particular manufacturing volume. Sample data on production volumes and total cost data for a
6
manufacturing operation was gathered and the following analysis results were obtained.
8000
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.9791
R Square
0.9587
Adjusted R Square
0.9484
Standard Error
241.5229
Observations
6
Total Cost ($)
6000
4000
2000
0
0
200
400
600
800
Production Volume (Units)
ANOVA
df
SS
5415000.00
Regression
Residual
Total
Intercept
Production Volume
(units)
MS
F
Significance F
0.0006
5648333.33
Coefficients Standard Error
1246.67
464.16
7.60
0.79
t Stat
P-value
2.686
0.055
9.635
0.001
Lower 95% Upper 95% Lower 99.0% Upper 99.0%
-42.05
2535.38
-890.37
3383.70
5.41
9.79
3.97
11.23
a. Complete the above ANOVA table.
b. Based on the scatter diagram developed, do you expect a relationship between Total Cost
($) and Production Volume (Units)? Indicate the expected type of relationship if any.
c. What is the sample size?
d. Write down what the estimated regression equation is that relates Total Cost with
Production Volume.
e. State the Null and Alternate Hypotheses used to test statistical significance of regression
between Total Cost and Production Volume
f. Read and state what the p-value is from the report. Test the significance of the regression
at a .01 level using p-value approach. What is the test decision?
g. Determine Critical-value for the test. Test the significance of the regression at a .01 level
using Critical-value approach. What is the test decision?
h. Write down the 99% confidence interval on the “Slope Coefficient” based on the report.
Based on the CI would you Reject or Not Reject the Null Hypothesis? Why or Why not?
i. What conclusion you arrive at based on the Hypothesis tests with respect to the
relationship between Total Cost and Production Volume?
j. State why the estimated regression equation is “good” or “no good.”
k. What is the value of sample correlation coefficient?
l. Why is R Square value equal to 95.87%?
m. Interpret what the value of slope coefficient = 7.6 means.
7
Purchase answer to see full
attachment
