Linear Regression
Use the spreadsheet data provided for US Airways. The Excel file is posted on Moodle in the Regression Assignment folder.
Select one of the following costs as a dependent variable:
- Aircraft Fuel & Related Taxes
- Aircraft Maintenance
- Aircraft Rent
- Other Rent & Landing Fees
- Selling Expenses
Using the data provided, propose a model that uses one or more independent variables (all other variables included in the spreadsheet other than year) to explain the dependent variable that you selected.
Note: A glossary of airline terms is also posted on Moodle. It is very important that you understand what the variables in your model represent in order to propose a logical model. You might also find it helpful to review US Airways' 10-K for the most recent year included in the data set if you need further information about the variables.
1. Describe your model by identifying the dependent and independent variables. Briefly explain the rationale for your model (why you believe that the chosen independent variable(s) should explain the dependent variable). Please include why you believe the independent variable(s) you selected is(are) preferred to others you could have chosen.
2. Using Excel, plot the data with your dependent variable on the vertical axis and the independent variable on the horizontal axis. If your model includes more than one independent variable, make separate plots to show the relationship between the dependent variable (vertical axis) and each independent variable (horizontal axis). Be sure to label your axes and include the units used to measure each variable.
3. Using Excel, perform a linear regression analysis to test your model. Be sure to use the data provided for the years 2005 to 2013. Be sure to use Excel's data analysis tool to obtain the regression output.
4. Express your model in the form of a regression equation. Please express your regression equation using descriptive terms for your dependent and independent variables (e.g., Aircraft Fuel & Related Taxes rather than y, etc.) and the results from part 3 for your intercept and regression coefficient(s). Be sure to use the regression output from part 3 when expressing this equation. If you request a trend line when preparing your scatterplot, Excel will provide an equation. You should not simply copy this equation since it will not use descriptive terms for your dependent and independent variables and it may round the intercept and regression coefficient(s) (note: Excel uses scientific notation in an unusual manner; if you use scientific notation in your equation, please use the conventional format such as 4.44 x 10-6 rather than Excel's format which would be 4.44E-06 for the same number). Be sure to include appropriate units for all terms in your equation.
5. Analyze your results by addressing the following issues and answering each of the following questions.
a. Does the model have significant explanatory power? Identify the statistic(s) you use to reach this conclusion and explain its(their) associated level of statistical significance, if any. Is the relationship between the dependent and independent variable(s) stronger than, weaker than, or about what you'd expected? Explain.
b. Do the results make sense?
- Do the sign and magnitude of the intercept make sense? If not, are these concerns? Explain. Is the intercept statistically significant? Be sure to identify the statistic you use to reach this conclusion.
- Is(Are) the regression coefficient(s) of the expected sign(s)? Is(Are) they statistically significant? Identify the statistic(s) you use to reach this conclusion.
- Interpret each of the regression coefficients. For example, say my model uses Aircraft Fuel & Related Taxes ($ millions) as the dependent variable and Full time Equivalent Employees at End of Period as the independent variable (I'm not saying this is a logical model) and the coefficient on Full time Equivalent Employees at End of Period is 0.5. I would interpret the coefficient to mean that if Full time Equivalent Employees at End of Period increase by one (a one unit increase in the independent variable), the cost for Aircraft Fuel & Related Taxes increases by $0.5 million, with all else held constant.
6. Use the equation from part 4 to predict the value of your dependent variable using the value of the pertinent independent variable given in the bottom section of the Excel data file. If your model incorporates more than one independent variable, you should include the values for each of your independent variables when predicting the value of your dependent variable.
7. US Airways merged with American Airlines on December 9, 2013. How would this impact incorporating data for 2014 in your model once the 10-K for 2014 has been filed with the Securities and Exchange Commission?