This project requires you to use information about Alaska Air Group to examine issues related to cost behavior, revenue drivers, and profit estimation. For those who are unfamiliar with Alaska Air, I have included their summary of business from a recent 10-K below. The Excel file contains historical information from 2004 to 2016 for the consolidated operations, which encompasses subsidiaries Alaska Airlines and Horizon Airlines. The file contains a variety of operating expenses (the costs) and several potential cost drivers (revenue passenger miles, available seat miles, and average number of full-time equivalent employees) as well as historical non-company data (GDP, population numbers etc.).
Excerpt from Alaska Air Group’s 2016 10-K describing their business
Air Group operates Alaska, Virgin America and Horizon Air. We completed the acquisition of Virgin America on December 14, 2016, at which time Virgin America became our wholly-owned subsidiary. Together with our regional partner airlines, we fly to 118 destinations with nearly 1,200 daily departures through our expansive network across the United States, Mexico, Canada, Costa Rica and Cuba. With our global airline partners, we can provide our guests with a virtual network of more than 900 destinations worldwide. During 2016, we carried an all-time high 34 million guests and earned adjusted net income of $911 million, which includes operating and financial results for Virgin America for the period December 14, 2016 through December 31, 2016, and excludes pretax special items and merger-related costs of $117 million.
Our acquisition of Virgin America positions us as the fifth largest airline in the U.S., with an unparalleled ability to serve West Coast travelers. Virgin America provides a platform for growth of our low-fare, premium product providing a powerful West Coast network for our guests as well as enhanced international partnerships. Additionally, Virgin America provides an opportunity to grow and improve our loyalty program while gaining access to constrained gates, particularly on the East Coast. The combined company now provides more seats from the West Coast than any other carrier, allowing us to serve our guests better.
Our mission is "creating an airline people love." The "ing" is to recognize we are never done—we are continually working to get better. We believe our success depends on our ability to provide safe air transportation, develop relationships with guests by providing exceptional customer service and low fares, and maintain a low cost structure to compete effectively. It is important to us that we achieve our objective as a socially responsible company that values not just our performance, but also our people, our community and our environment.
Policy on Academic Honesty
This is an individual assignment. You can ask me for assistance, but you are not to work with other students on this project or seek answers from other students. This includes comparing answers. Doing so will constitute a major violation of academic integrity standards and, if detected, will have serious consequences.
By typing your name below, you assert that you have completed this project on your own:
Enter text here Questions (100 points in total)
Use the file “Alaska Air Group Data” to answer the questions in this document. Enter all your numbers and explanations in this Word document. For grading, also submit your Excel calculations on which your answers in this document are based.
You might find it helpful to create scatter plots with a trend lines before you use the “linest” command. However, any graphs that you create will not be graded.
Cost Estimation (53 points)
Explain Total Operating Expenses using available seat miles (ASM) as the cost driver.
(6 pts) Compute the slope, t-statistic of the coefficient, and the intercept for a linear cost function.
Slope = Enter text here t-stat = Enter text here Intercept = Enter text here
(2 pt.) How much does an additional seat mile cost according to your regression output? Enter text here
Explain Total Operating Expenses using revenue passenger miles (RPM) as the cost driver.
(6 pts) Compute the slope, t-statistic of the coefficient, and the intercept for a linear cost function.
Slope = Enter text here t-stat = Enter text here Intercept = Enter text here
(4 pts) Which of the two cost drivers (ASM or RPM) would you choose if you had to decide for one of them? Name an empirical (based on your numbers) and a theoretical reason.
Enter text here
(10 pts) Substantiate your suggestion from b. with multiple regression analysis. Also, describe how the results support your choice from b.
Name of cost driver: Enter text here Slope = Enter text here t-stat = Enter text here
Name of cost driver: Enter text here Slope = Enter text here t-stat = Enter text here
Intercept = Enter text here t-stat = Enter text here
Explanation: Enter text here
Analyze the behavior of Wages and Benefits. The obvious cost driver is the average number of full-time employees.
(1 pt.) Based on economic intuition, do you expect the cost function to be variable, mixed, or fixed? Why? Enter text here
(4 pts) Based on a linear regression, is the cost function most similar to a variable, mixed, or fixed cost? Explain your answer by referring to numbers from your regression output. Enter text here
Analyze the behavior of the cost of Food and beverage service.
(2 pts) Before performing any analysis, which two operating statistic variables are the most likely to be a cost driver that influences food and beverage costs and why?
Enter text here
(8 pts)Test your intuition from part (a) by running linear regression analysis. First, perform a separate analysis for each cost driver (8 points).
Cost driver = Enter text here Slope = Enter text here t-stat = Enter text here Intercept = Enter text here
Cost driver = Enter text here Slope = Enter text here t-stat = Enter text here Intercept = Enter text here
(8 pts) Now, perform multiple regression with both cost drivers.
Name of cost driver: Enter text here Slope = Enter text here t-stat = Enter text here
Name of cost driver: Enter text here Slope = Enter text here t-stat = Enter text here
Intercept = Enter text here t-stat = Enter text here
(2 pts) Which of the three models best predicts food and beverage service costs and why?
Enter text here
Revenue Estimation (23 points)
Now turn to the non-company data as possible explanatory variables of Total Operating Revenues:
The Alaska Gross Domestic Product (GDP) per capita in $. Since the Alaska economy is strongly driven by commodity prices, you can assume that a high GDP correlates significantly with a high oil price.
The population of the state of Alaska in million.
The US Gross Domestic Product (GDP) per capita in $. Note the dent during the Great Recession in 2008 and 2009.
The population of the US in million.
A measure of customer satisfaction for Alaska Airlines (one of the airlines in the Alaska Air Group portfolio).
A measure of inflation for the US.
(12 pts) Comment on the economic plausibility of each variable. Which type of relationship (positive/negative/none) with Total Operating Revenue do you expect and why?
Enter text here
(7 pts) Pick 3 of the variables above and conduct multiple regression analysis using those variables simultaneously. You might want to experiment with different combinations of variables to see if one “model” works better than other models. Be careful when labeling your regression outputs with the correct variable names. Show your results here (note only 3 of the 6 will be filled in based on which variables you select):
GDP (Alaska): Slope = Enter text here t-stat = Enter text here
Population (Alaska): Slope = Enter text here t-stat = Enter text here
GDP (US): Slope = Enter text here t-stat = Enter text here
Population (US): Slope = Enter text here t-stat = Enter text here
Consumer satisfaction: Slope = Enter text here t-stat = Enter text here
Inflation: Slope = Enter text here t-stat = Enter text here
Intercept = Enter text here t-stat = Enter text here
(4 pts) Considering the results on statistical significance, how do you explain possible discrepancies between your expectations from a. and the directional effect of some variables or their observed t-stats?
Enter text here
Profit Estimation and Forecast (24 points)
Analyze Operating Income (excluding “other net” revenue, “other” costs, and “restructuring charges”).
(1 pt.) Prepare a new line in your spreadsheet for Adjusted Operating Income excluding the items listed above so that you can use it as a new dependent variable that represents profit.
(4 pts) Explain what Load Factor represents (hint: the equation to calculate it in the spreadsheet should help). Consider Load Factor as a potential profit driver. Why is it economically plausible to assume a causal relationship? Enter text here
(8 pts) Compute the slope, t-statistic of the coefficient, intercept, and R2 for a linear profit function using Load Factor.
Slope = Enter text here t-stat = Enter text here Intercept = Enter text here R2 = Enter text here
(4 pts) Despite economic plausibility, you will find that the fit of your model in c. is relatively low and that the statistical significance of the proposed profit driver is borderline. Considering that aircraft fuel is a large item that affects operating income, explain how fuel costs can interfere with the analysis that you conducted.
Enter text here
(4 pts) Propose an alternative analysis that overcomes the problem described in d. and conduct this analysis. Show whether the goodness of fit and the statistical significance of Load Factor’s slope coefficient improve.
Enter text here
(3 pts) Forecast Alaska Air Group’s (unadjusted) Operating Income for 2017 by using your results from the cost and revenue estimation parts of this project. For estimating Total Operating Expenses, use the cost driver that you have selected. You will find “forecast” data on the second tab in your Excel file. Assume that Alaska Air Group will be able to maintain their J.D. Powers score from 2016.
2017 Operating Income = Enter text here.
USA 
India