Question

The project is adapted from the Chapter 4 Case Study dealing with North–South Airline In January 2012, Northern Airlines merged with Southeast Airlines to create the fourth largest U.S. carrier. The new North–South Airline inherited both an aging fleet of Boeing 727-300 aircraft and Stephen Ruth. Stephen was a tough former Secretary of the Navy who stepped in as new president and chairman of the board.

Stephen’s first concern in creating a financially solid company was maintenance costs. It was commonly surmised in the airline industry that maintenance costs rise with the age of the aircraft. He quickly noticed that historically there had been a significant difference in the reported B727-300 maintenance costs (from ATA Form 41s) in both the airframe and the engine areas between Northern Airlines and Southeast Airlines, with Southeast having the newer fleet.

On February 12, 2012, Peg Jones, vice president for operations and maintenance, was called into Stephen’s office and asked to study the issue. Specifically, Stephen wanted to know whether the average fleet age was correlated to direct airframe maintenance costs and whether there was a relationship between average fleet age and direct engine maintenance costs. Peg was to report back by February 26 with the answer, along with quantitative and graphical descriptions of the relationship.

Peg’s first step was to have her staff construct the average age of the Northern and Southeast B727-300 fleets, by quarter, since the introduction of that aircraft to service by each airline in late 1993 and early 1994. The average age of each fleet was calculated by first multiplying the total number of calendar days each aircraft had been in service at the pertinent point in time by the average daily utilization of the respective fleet to determine the total fleet hours flown. The total fleet hours flown was then divided by the number of aircraft in service at that time, giving the age of the “average” aircraft in the fleet.

The average utilization was found by taking the actual total fleet hours flown on September 30, 2011, from Northern and Southeast data, and dividing by the total days in service for all aircraft at that time. The average utilization for Southeast was 8.3 hours per day, and the average utilization for Northern was 8.7 hours per day. Because the available cost data were calculated for each yearly period ending at the end of the first quarter, average fleet age was calculated at the same points in time. The fleet data are shown in the following table.

The project is derived from a case study 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. 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:
   1. 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).
   2. The regression model derived from the data tables.
   3. Line Fit Plot for each Worksheet tab.
   4. Labels of the data included.
   5. Highlight with yellow and label the following four items on each regression model:
      1. Coefficient of determination
      2. Coefficient of correlation or covariance
      3. Slope, and
      4. 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.

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

Answer 1

Solution:

Northern airlines

we enter the data in excel and then goto data > data analysis tab and select regression

The result of the regression is

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.914230219
R Square	0.835816894
Adjusted R Square	0.753725341
Standard Error	1992.530384
Observations	7
ANOVA
	df	SS	MS	F	Significance F
Regression	2	80844890.11	40422445	10.18152	0.026956
Residual	4	15880709.32	3970177
Total	6	96725599.43
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	-7198.762046	5673.798807	-1.26877	0.27333	-22951.8	8554.229	-22951.8	8554.229
AirframeCost	167.1159277	104.568994	1.59814	0.185255	-123.214	457.446	-123.214	457.446
EngineCost	151.5511099	68.9525208	2.197905	0.092869	-39.8918	342.994	-39.8918	342.994

The regression equation is age = -7198.76 +167.11*AirframeCost +151.55*EngineCost

considering an alpha of 0.05 , we see that both the variables are not statistically signficant as the p value is not less than 0.05

However , the signficant F of the model is statistically signficant as the p value is 0.026 , which is less than 0.05. This is a classic case of multicolleniarity probelm , where the independent variables are correlated with each other

the r square or coef of determintation is 0.8358 , this means that the model is able to capture 84.58% variation in the data . we calculate the correlation between airframe and engine cost and find the value to be

0.644262699 , which is decently high

The line fit plots are

for the southeast airlines

the output is

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.706645
R Square	0.499347
Adjusted R Square	0.249021
Standard Error	1278.546
Observations	7
ANOVA
	df	SS	MS	F	Significance F
Regression	2	6521675	3260838	1.994785	0.250653
Residual	4	6538724	1634681
Total	6	13060399
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	3183.518	2169.895	1.46713	0.216239	-2841.08	9208.112	-2841.08	9208.112
AirframeCost	65.18294	95.60931	0.681764	0.532818	-200.271	330.6369	-200.271	330.6369
EngineCost	78.84257	81.52549	0.967091	0.388263	-147.508	305.1936	-147.508	305.1936

The regression equation is age = 3183.5+65.18*airframecost +78.84*Enginecost

considering an alpha of 0.05 , we see that both the variables are not statistically signficant as the p value is not less than 0.05

However , the signficant F of the model is not statistically signficant as the p value is 0.25 , which is not less than 0.05. the r square or coef of determintation is 0.4993 , this means that the model is able to capture 49.94% variation in the data