Regression Analysis
The project is derived from a case study (attatched) located at the end of chapter 4 dealing with regression analysis. Please note, however that some of the numbers in the project tables in the text have been changed so students should get their complete instructions from the Project area provided in Getting Started section of the Table of Contents. Students should use the Data Analysis add-on pack from the standard Microsoft Excel software available in every Microsoft Office software since 2007. The project requirements are:
1. Prepare Excel Data Analysis Regression Tables demonstrating your excellence at determining Northern and Southeast Costs to Average Age. Besides the data tables, copied from the project instructions, four regression models are required each on a separate tab. STUDENTS CANNOT USE MULTIPLE REGRESSION as this is not part of Excel software. Place each regression model with supporting data labels, line fit plots, and other required items on a separate worksheet tab.
2. On each worksheet tab (other than the data table tab) include:
a. a copy of your data entry screen (Use Alt+Print Screen to copy picture of Regression Data Entry from Data Analysis in Excel and paste on correct worksheet tab).
b. The regression model derived from the data tables.
c. Line Fit Plot for each Worksheet tab.
d. Labels of the data included.
e. Highlight with yellow and label the following four items on each regression model:
i. Coefficient of determination
ii. Coefficient of correlation or covariance
iii. Slope, and
iv. Beta or intercept
3. Finally prepare a formal response, using Microsoft Word, from Peg Jones’s to Stephen Ruth explaining your numbers and calculations. Which costs are correlated with the average age of the aircraft? What is the slope and beta? Explain the coefficient of determination and covariance. Explain how this information benefits each airline. Finally, Stephen wants to know:
a. If Northern’s average age gets to 20,000 hours how much will the Airframe and Engine cost.
b. If Southeast’s average age gets to 12,000 hours how much will the Airframe and Engine cost.
Submit your Excel Worksheet with five tabs (data, plus 4 tabs for the regressions) to the assignment drop box. Also include your formal response in a Microsoft Word document. Late work will not be accepted. The Excel worksheet and Word documents must be submitted BEFORE then end of Unit 7. This project is worth 160 points.
Note: Dates and names of airlines and individuals have been changed in this case to maintain confidentiality. The data and issues described here are real.
Northern Airline Data (numbers have been changed from text)
Airframe Cost
Engine Cost
Average Age
Year
per Aircraft
per Aircraft
(Hours)
2001
61.80
33.49
6,512
2002
54.92
38.58
8,404
2003
69.70
51.48
11,077
2004
68.90
58.72
11,717
2005
63.72
45.47
13,275
2006
84.73
50.26
15,215
2007
78.74
80.60
18,390
Southeast Airline Data (numbers have been changed from text)
Airframe Cost
Engine Cost
Average Age
Year
Per Aircraft
per Aircraft
(Hours)
2001
14.29
19.86
5,107
2002
25.15
31.55
8,145
2003
32.18
40.43
7,360
2004
31.78
22.10
5,773
2005
25.34
19.69
7,150
2006
32.78
32.58
9,364
2007
35.56
37.07
8,259
