ACC353 ? Fall 2015 Excel Homework ? Linear Regression (30 points) Use the spreadsheet data provided for
Question:
ACC353 ? Fall 2015
Excel Homework ? Linear Regression
(30 points)
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?
Instructions for formatting and submitting the assignment.
1. Be sure to include the scatterplot(s) of your data from part 2 above. These should be on separate worksheets in your Excel file.
2. When performing your regression in Excel, please save the output to a new worksheet. Be sure to size the cells of the output sheet so that the results are fully visible. Be sure to use landscape orientation and size the output so that it all appears on a single page.
3. Your Excel file should be named using the same convention as was used previously (last name-first name-ACC353-F15-Excel-date of submission?for example, if I submitted the assignment on September 18th I would name my file Woods-Maef-ACC353-F15-Excel-9-18-15).
4. Please be sure to put your name in the upper lefthand corner on the 1st line of all worksheets in your spreadsheet file and ACC353-F15-Excel and the date of your submission on the 2nd line. Alternatively, you may place this information in the header.
5. Please include the text of your write-up in a Word document. Your Word document should be named in the same fashion as your Excel file (other than it will have a .doc or .docx identifier rather than a .xls or .xlsx identifier). You should also include your name, ACC353-F15-Excel, and the date of submission at the top of your Word document. Alternatively, you may place this information in the header.
6. Failure to follow the formatting and submission instructions will result in a lower grade.
ACC353 - Fall 2015 Excel Homework - Linear Regression (30 points) 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. 1 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? Instructions for formatting and submitting the assignment. 1. Be sure to include the scatterplot(s) of your data from part 2 above. These should be on separate worksheets in your Excel file. 2. When performing your regression in Excel, please save the output to a new worksheet. Be sure to size the cells of the output sheet so that the results are fully visible. Be sure to use landscape orientation and size the output so that it all appears on a single page. 3. Your Excel file should be named using the same convention as was used previously (last name-first name-ACC353-F15-Excel-date of submission...for example, if I submitted the assignment on September 18th I would name my file Woods-Maef-ACC353-F15-Excel-9-18-15). 4. Please be sure to put your name in the upper lefthand corner on the 1st line of all worksheets in your spreadsheet file and ACC353-F15-Excel and the date of your submission on the 2nd line. Alternatively, you may place this information in the header. 5. Please include the text of your write-up in a Word document. Your Word document should be named in the same fashion as your Excel file (other than it will have a .doc or .docx identifier rather than a .xls or .xlsx identifier). You should also include your name, ACC353-F15-Excel, and the date of submission at the top of your Word document. Alternatively, you may place this information in the header. 6. Failure to follow the formatting and submission instructions will result in a lower grade. 2 7. Assignments are to be submitted via email to mwoods@heidelberg.edu before class (no later than 11:59AM) on Friday, September 25, 2015. Please also submit a hard-copy at the beginning of class on Friday, September 25, 2015. Late assignments will not be accepted. The timestamp of your email message will be used to determine if electronic submissions are timely. 8. SurveyMonkey will be used for you to provide feedback on the assignment. The survey will be completed individually and your answers will be anonymous. You will be required to answer all questions to receive credit (3 points) for completing the survey. The survey will be posted after the assignment's due date and must be completed by 11:59AM on Friday, October 2nd). 3 Year 2005 2006 2007 2008 2009 2010 2011 2012 2013 Aircraf Fuel & Related Taxes ($ millions) 1,486 2,518 2,630 3,618 1,863 2,403 3,400 3,489 3,481 Data to use for prediction: Aircraf Maintenance ($ millions) 333 582 635 783 700 661 679 672 705 Aircraf Rent ($ millions) 391 732 727 724 695 670 646 643 593 Other Rent & Landing Fees ($ millions) 367 568 536 562 560 549 555 556 582 Selling Expenses ($ millions) 324 446 453 439 382 421 454 466 480 Full time Equivalent Employees at End of Period 32,210 34,077 34,437 32,671 31,333 30,871 31,548 31,236 32,129 Revenue Passenger Miles (millions) 63,155 60,689 61,262 60,570 57,889 58,977 60,779 62,435 65,613 Available Seat Miles (millions) 82,021 76,983 75,842 74,151 70,725 71,588 72,603 74,211 77,374 Load Factor (percent) 77.0 78.8 80.8 81.7 81.9 82.4 83.7 84.1 84.8 Passenger Enplanements (thousands) 62,107 57,345 57,871 54,820 51,016 51,853 52,959 54,277 56,745 Block Hours (thousands) 1,492 1,365 1,343 1,300 1,224 1,199 1,217 1,209 1,246 Average Stage Length (miles) 793 927 925 955 972 981 991 1,004 1,013 33,569 58,435 72,900 79.8 56,885 1,290 915 Year 2005 2006 2007 2008 2009 2010 2011 2012 2013 Data to use for prediction: Aircraf Fuel & Related Taxes ($ millions) 1,486 2,518 2,630 3,618 1,863 2,403 3,400 3,489 3,481 Aircraf Maintenance ($ millions) 333 582 635 783 700 661 679 672 705 Aircraf Rent ($ millions) 391 732 727 724 695 670 646 643 593 Other Rent & Landing Fees ($ millions) 367 568 536 562 560 549 555 556 582 Selling Expenses ($ millions) 324 446 453 439 382 421 454 466 480 Full time Equivalent Employees at End of Period 32,210 34,077 34,437 32,671 31,333 30,871 31,548 31,236 32,129 Revenue Passenger Miles (millions) 63,155 60,689 61,262 60,570 57,889 58,977 60,779 62,435 65,613 Available Seat Miles (millions) 82,021 76,983 75,842 74,151 70,725 71,588 72,603 74,211 77,374 Load Factor (percent) 77.0 78.8 80.8 81.7 81.9 82.4 83.7 84.1 84.8 Passenger Enplanements (thousands) 62,107 57,345 57,871 54,820 51,016 51,853 52,959 54,277 56,745 Block Hours (thousands) 1,492 1,365 1,343 1,300 1,224 1,199 1,217 1,209 1,246 Average Stage Length (miles) 793 927 925 955 972 981 991 1,004 1,013 33,569 58,435 72,900 79.8 56,885 1,290 915 Selling Available 600 Expenses Seat ($ Miles 500 millions) (millions) 324 82,021 400 446 76,983 453 75,842 300 439 74,151 382 70,725 200 421 71,588 454 72,603 100 466 74,211 480 77,374 0 1 2 3 90,000 90,000 80,000 8 9 30,000 20,000 20,000 7 50,000 30,000 6 60,000 40,000 5 70,000 70,000 Selling 60,000 Expenses ($ mil50,000 lions) 4 80,000 10,000 10,000 0 0 1 Selling Expenses ($ millions) Available Seat Miles (millions) 40,000 2 3 4 5 6 7 Selling Expenses ($ millions) Available Seat Miles (millions) 1 2 8 3 9 4 5 6 7 8 9 SUMMARY OUTPUT Regression Statistics Multiple R 0.9347782926 R Square 0.8738104562 Adjusted R Square 0.79809673 Standard Error 21.814940198 Observations 9 ANOVA df Regression Residual Total SS 3 16476.76414304 5 2379.458079187 8 18856.22222222 MS F Significance F 5492.254714345 11.54098 0.010990818 475.8916158375 Intercept Coefficients Standard Error 26017.034631 7370.682910839 t Stat P-value Lower 95% Upper 95% Lower 95.0% 3.5297997412 0.016743 7070.091032 44963.98 7070.091032 Upper 95.0% 44963.9782313479 Revenue Passenger Miles (millions) 0.4264767152 0.1159807462 3.6771337425 0.014336 0.128338716 0.724615 0.128338716 0.7246147144 Available Seat Miles (millions) -0.343768956 0.0941741796 -3.6503525473 0.014743 -0.58585139 -0.101687 -0.58585139 -0.101686521 Load Factor (percent) -317.2185924 90.6193973931 -3.5005595001 0.017274 -550.163169 -84.27402 -550.163169 -84.2740156258 Predicted Selling Expenses ($ millions) 329.06639001 438.28934618 440.46369829 441.15838341 412.08303608 420.80779661 428.00917645 454.58669794 500.53547504 Residuals -5.0663900061 7.7106538214 12.5363017089 -2.1583834117 -30.083036078 0.1922033899 25.9908235542 11.4133020575 -20.535475036 RESIDUAL OUTPUT Observation 1 2 3 4 5 6 7 8 9 Selling Revenue Available Expenses Passenger Seat Load ($ Miles Miles Factor millions) (millions) (millions) (percent) 324 63,155 82,021 77.0 446 60,689 76,983 78.8 453 61,262 75,842 80.8 439 60,570 74,151 81.7 382 57,889 70,725 81.9 421 58,977 71,588 82.4 454 60,779 72,603 83.7 466 62,435 74,211 84.1 480 65,613 77,374 84.8 Year 2005 2006 2007 2008 2009 2010 2011 2012 2013 Data to use for prediction: Aircraf Fuel & Related Taxes ($ millions) 1,486 2,518 2,630 3,618 1,863 2,403 3,400 3,489 3,481 Aircraf Maintenance ($ millions) 333 582 635 783 700 661 679 672 705 Aircraf Rent ($ millions) 391 732 727 724 695 670 646 643 593 Other Rent & Landing Fees ($ millions) 367 568 536 562 560 549 555 556 582 Selling Expenses ($ millions) 324 446 453 439 382 421 454 466 480 Full time Equivalent Employees at End of Period 32,210 34,077 34,437 32,671 31,333 30,871 31,548 31,236 32,129 Revenue Passenger Miles (millions) 63,155 60,689 61,262 60,570 57,889 58,977 60,779 62,435 65,613 Available Seat Miles (millions) 82,021 76,983 75,842 74,151 70,725 71,588 72,603 74,211 77,374 Load Factor (percent) 77.0 78.8 80.8 81.7 81.9 82.4 83.7 84.1 84.8 Passenger Enplanements (thousands) 62,107 57,345 57,871 54,820 51,016 51,853 52,959 54,277 56,745 Block Hours (thousands) 1,492 1,365 1,343 1,300 1,224 1,199 1,217 1,209 1,246 Average Stage Length (miles) 793 927 925 955 972 981 991 1,004 1,013 33,569 58,435 72,900 79.8 56,885 1,290 915 Selling Available 600 Expenses Seat ($ Miles 500 millions) (millions) 324 82,021 400 446 76,983 453 75,842 300 439 74,151 382 70,725 200 421 71,588 454 72,603 100 466 74,211 480 77,374 0 1 2 3 90,000 90,000 80,000 8 9 30,000 20,000 20,000 7 50,000 30,000 6 60,000 40,000 5 70,000 70,000 Selling 60,000 Expenses ($ mil50,000 lions) 4 80,000 10,000 10,000 0 0 1 Selling Expenses ($ millions) Available Seat Miles (millions) 40,000 2 3 4 5 6 7 Selling Expenses ($ millions) Available Seat Miles (millions) 1 2 8 3 9 4 5 6 7 8 9 SUMMARY OUTPUT Regression Statistics Multiple R 0.9347782926 R Square 0.8738104562 Adjusted R Square 0.79809673 Standard Error 21.814940198 Observations 9 ANOVA df Regression Residual Total SS 3 16476.76414304 5 2379.458079187 8 18856.22222222 MS F Significance F 5492.254714345 11.54098 0.010990818 475.8916158375 Intercept Coefficients Standard Error 26017.034631 7370.682910839 t Stat P-value Lower 95% Upper 95% Lower 95.0% 3.5297997412 0.016743 7070.091032 44963.98 7070.091032 Upper 95.0% 44963.9782313479 Revenue Passenger Miles (millions) 0.4264767152 0.1159807462 3.6771337425 0.014336 0.128338716 0.724615 0.128338716 0.7246147144 Available Seat Miles (millions) -0.343768956 0.0941741796 -3.6503525473 0.014743 -0.58585139 -0.101687 -0.58585139 -0.101686521 Load Factor (percent) -317.2185924 90.6193973931 -3.5005595001 0.017274 -550.163169 -84.27402 -550.163169 -84.2740156258 Predicted Selling Expenses ($ millions) 329.06639001 438.28934618 440.46369829 441.15838341 412.08303608 420.80779661 428.00917645 454.58669794 500.53547504 Residuals -5.0663900061 7.7106538214 12.5363017089 -2.1583834117 -30.083036078 0.1922033899 25.9908235542 11.4133020575 -20.535475036 RESIDUAL OUTPUT Observation 1 2 3 4 5 6 7 8 9 Selling Revenue Available Expenses Passenger Seat Load ($ Miles Miles Factor millions) (millions) (millions) (percent) 324 63,155 82,021 77.0 446 60,689 76,983 78.8 453 61,262 75,842 80.8 439 60,570 74,151 81.7 382 57,889 70,725 81.9 421 58,977 71,588 82.4 454 60,779 72,603 83.7 466 62,435 74,211 84.1 480 65,613 77,374 84.8 Expert Answer:
