I have 4 problems to be solved. solve the problems in separate sheet. The first page is provided with the instruction. I would perfer the expert to answer on the excel sheet.
Instruction
Complete Problem 3
In addition to the questions in this problem, respond to the following:
1. State the linear equation.
2. Explain the overall statistical significance of the model.
3. Explain the statistical significance for each independent variable in the model
4. Interpret the Adjusted R2.
5. Is this a good predictive equation(s)? Which variables should be excluded (if any) and why? Explain.
Complete Problem 4.
Use Excel's regression option to perform the regression. Use one Excel spreadsheet file for the calculations and explanations, with one worksheet per problem. Use the problem number for each worksheet name. Cells should contain the formulas (i.e., if a formula was used to calculate the entry in that cell).
Problem 1:
The following data give the selling price, square footage , number of bedrooms, and age of house that have sold in a nieghbourhood in the past 6 months. Develop three regression models to predict the selling price based upon each of the other factors individually. Which of these is best?
| SELLING |
SQUARE |
|
AGE |
| PRICE($) |
FOOTAGE |
BEDROOMS |
(YEARS) |
| 84,000 |
1,670 |
2 |
30 |
| 79,000 |
1339 |
2 |
25 |
| 91,500 |
1712 |
3 |
30 |
| 120,000 |
1840 |
3 |
40 |
| 127,500 |
2300 |
3 |
18 |
| 132,500 |
2234 |
3 |
30 |
| 145,000 |
2311 |
3 |
19 |
| 164,000 |
2377 |
3 |
7 |
| 155,000 |
2736 |
4 |
10 |
| 168,000 |
2500 |
3 |
1 |
| 172,000 |
2500 |
4 |
3 |
| 174,000 |
2479 |
3 |
3 |
| 175,000 |
2400 |
3 |
1 |
| 177,000 |
3124 |
4 |
0 |
| 184,000 |
2500 |
3 |
2 |
| 195,000 |
4062 |
4 |
10 |
| 195,000 |
2854 |
3 |
3 |
Problem 2:
Use the data in problem 1 and develop a regression model to predict selling price based on the square footage and number of bedrooms. Use this to predict the selling price of a 2,000 square foot house with three bedrooms. Compare this model with the models in problem 1. Should the number of bedrooms be included in the model? Why or Why not?
Problem 3:
Use the data in problem 1 and develop a regression model to predict selling price based on the square footage, number of bedrooms and age. Use this to predict the selling price of a 10 yr old, 2,000 square foot house with three bedrooms.
In addition to the questions in this problem, respond to the following:
1. State the linear equation.
2. Explain the overall statistical significance of the model.
3. Explain the statistical significance for each independent variable in the model
4. Interpret the Adjusted R2.
5. Is this a good predictive equation(s)? Which variables should be excluded (if any) and why? Explain.
Problem 4:
In 2012, the total payroll for the New York Yankees was almost $200 million, while the total payroll for the Oakland Athletics (a team know for using baseball analytics or sabermetrics) was about $55 million, less than one-third of the yankees payroll. In the following table, you will see the payrolls(in millions) and the total number of victories for the baseball teams in the American League in the 2012 season. Develop a regression model to predict the total number of victories based on the payroll.
Use the model to predict the number of victories for a team with a payroll of $79 million.
Bsed on the results of the computer output, discuss the relationship between payroll and victories.
| |
|
PAYROLL |
|
NUMBER OF |
| TEAM |
|
($MILLIONS) |
|
VICTORIES |
| Baltimore Orioles |
81.4 |
|
93 |
| Boston Red Sox |
173.2 |
|
69 |
| Chicago White sox |
96.9 |
|
85 |
| Cleveland Indians |
78.4 |
|
68 |
| Detroit Tigers |
132.3 |
|
88 |
| Kansas City Royals |
60.9 |
|
72 |
| Los Angeles Angels |
154.5 |
|
89 |
| Minnesota Twins |
94.1 |
|
66 |
| New York Yankees |
198 |
|
95 |
| Oakland Athletics |
55.4 |
|
94 |
| Seattle Mariners |
82 |
|
75 |
| Tampa Bay Rays |
64.2 |
|
90 |
| Texas Rangers |
120.5 |
|
93 |
| Toronto Blue Jays |
75.5 |
|
73 |