Soren Sabet Sarvestany
NΨ 1T7 + PEY
University of Toronto
Discounted Cash Flow Analysis:
Valuation of a Company
In my economics course, our final team project was to value an airline company using discounted cash flow analysis. The spreadsheet we created to conduct our valuation can be found here. Data from 2012 and 2013 was provided to us, and we had to make reasonable assumptions about values changed from year to year.
After creating the spreadsheet, we conducted sensitivity analysis on the Weighted Average Cost of Capital (WACC). We modeled how a percentage change in a variable affecting the WACC resulted in a percentage change in the WACC. Our full analysis can be found here, but our final graph is reproduced below.
Our analysis showed that the WACC was most sensitive to the expected return on the market. After completing sensitivity analysis, I was tasked with conducting scenario analysis. The three scenarios I chose were:
Scenario 1: Change in Oil Prices (Change in COGS)
• Jet fuel is sold in USD
• Changes in currency exchange rates (this airline operated in South Korea) would result in changes to Jet
Fuel price.
• Current price is 1812.37 KRW03/17/2015)
• Historical max was 3999 KRW in May 2008
• Highest cost incurred by airline is Jet fuel
• Change in COGS will increase as a percent of revenue
To analyze this scenario, I modeled the effect of a percentage changes in COGS to a change in present value of the company being considered for purchase. I determined that every 0.02% increase in the COGS (as a percentage of revenue) resulted in a 4% decrease in the present value of the company, indicating that oil prices significantly affect the profitability of an airline. The spreadsheet used to analyze this scenario can be found here.
Scenario 2: Plane Crash
• Assume the plane crashes in 2015
• Assume loss of investor confidence (15% loss in equity, growing back at 2% per year)
• Assume loss of revenues from lost plane (6% less growth in 2014-2015), normal growth of airline
resumes afterward.
• Assume settlement is 5 million USD (From similar case in Malaysian airlines, SG&A + $5645 M)
The above assumptions were incorporated into the projection. They resulted in a 32% decrease in the projected value of the company, which is very significant. Spreadsheet can be found here.
Scenario 3: Change in Government of South Korea
• Assume policies might change after legislative election in 2016
• Assume tax rate changes would go into effect fiscal year 2017
• 3 main parties, conservative government currently in power.
• Other parties are further left on political spectrum - more likely to expect corporate tax increase
• Model's discounting calculations should take into account that WACC changes between 2016 and 2017
because of tax changes.
•
When the above assumptions were entered into the model, it was found that the present value was not significantly affected (as compared to scenarios 1 and 2) for reasonable changes in tax rates. Spreadsheet can be found here.
Our final report achieved a Grade of 79.75% (shown below) and can be found here.
