This case exposes you to the application of regression analyses to be used as a tool pursuant to understanding cost behavior and forecasting future costs using publicly available data from Continental Airlines. Specifically, the case focuses on the harsh financial situation faced by Continental as a result of the recent financial crisis and the challenges it faces to remain profitable. It then highlights the importance of reducing and controlling costs as a viable strategy to restore profitability and how regression analysis can assist in this pursuit. You are next presented with quarterly data for various categories of costs and several potential cost drivers, which the company must use to perform regressions on operating costs using a variety of cost drivers. You must then use your regression results to forecast operating costs and conduct a profitability analysis to project quarterly profits for the upcoming fiscal year. Finally, you must summarize the main results of your analysis in a memorandum addressed to Continental's management, providing recommendations to restore profits. In particular, the concept of mixed cost functions is reinforced, as is the understanding of the steps required to perform regression analysis in Excel, interpreting the regression output, and the underlying standard assumptions in regression analysis.
Please provide solved excel file and answer these three questions
CASE QUESTIONS
1. Using the quarterly data for operating costs and the various cost drivers of costs provided in the spreadsheet, estimate regressions for each of the ten cost categories listed above. Then, write the appropriate cost function for each category of cost and interpret your regression results. For example, your first regression would have “salaries and wages” as your dependent variable and “available seat miles” as independent variable.
2. Based on your regression results and your interpretation of those results, where do you see the largest reductions in costs if flying capacity is lowered by 11 percent? Also, in which areas do you see opportunities to achieve further cost reductions? Why?
3. The table below provides a quarterly forecast of revenues, jet fuel prices,5 and the projected operating activity for 2009. Using the information from your regressions and the forecast information provided, estimate Continental’s operating costs and expected profit for the upcoming fiscal year.
4. Based on the results of your profitability analysis, what can you say about the firm’s financial outlook? Would Continental be earning an operating profit in 2009? If not, what should Continental’s management do to restore profitability in 2009?
5. Summarize your conclusions in a memorandum addressed to Continental’s CEO. In the memo, you must clearly communicate your main findings, emphasizing specific areas in which you see the greatest potential to achieve further reductions in costs and, based on your profitability analysis, sum up the financial outlook for 2009.