Project Task a: Valuation and Sensitivity Analysis

Task A I choose Qantas Airways Limited (ASX code: QAN. AX) as the target company which is listed on the Australian Stock Exchange (ASX). Qantas Airways is principally engaged in providing transport services at both international and domestic level. It is also engaged in the provision of time definite freight services. Qantas focuses on selling of international and domestic holiday tours, and associated support activities including information technology, catering, ground handling, and engineering and maintenance.

It operates its business through six divisions namely Qantas, Qantas holidays, Jetstar and Qantas flight catering, Qantas freight and Qantas frequent player. In the section 1, the report deals with the valuation of the company. I will estimate the value of QAN’s on a per share basis. According to the historical prices of the company and all ordinaries, I will calculate QAN’s beta by calculating a regression on the monthly returns from QAN on the market returns in the last 5 years. And according to the QAN’s financial data, I will rebuild its historical Cash Flows.

We will write a custom essay sample on
Project Task a: Valuation and Sensitivity Analysis
or any similar topic only for you
Order now

In addition, I gather relevant financial data and other non-financial information to estimate future FCF for the next 5 years. By discounting Future Cash Flows (FCF) using a discount rate given by QAN’s Weighted Average Cost of Capital (WACC) I will compute the Net Present Value (NPV) of the company. So, we are able to compare our calculated NPV per share with the market share price and analyse the factors which cause the deviations. In the second section, I will conduct a sensitivity analysis to assess the sensitivity of the NPV on changes of the assumptions.

Finally, summarise and give a recommendation regarding the assessment of the company. Section 1: Valuation 1. Download data and Calculate Return Collect the historical prices of the company and all ordinaries, Calculate the monthly returns on QAN’s share and the all ordinary index over the last 5 years by using the following formula: R= (Pt-Pt-1)/ Pt_1 Date| Adj Close(QAN)| QAN Return| Adj Close(ORD)| Market Return| | | | | | 2009-12-1| 2. 7| 3. 85%| 4733. 1| 0. 37%| 2009-11-2| 2. 6| -7. 14%| 4715. 5| 1. 48%| 2009-10-1| 2. 8| -2. 10%| 4646. 9| -1. 95%| 2009-9-1| 2. 86| 13. 4%| 4739. 3| 5. 69%| 2009-8-3| 2. 53| 9. 05%| 4484. 1| 5. 52%| 2009-7-1| 2. 32| 15. 42%| 4249. 5| 7. 64%| 2009-6-1| 2. 01| 5. 24%| 3947. 8| 3. 53%| 2009-5-1| 1. 91| -3. 54%| 3813. 3| 1. 83%| 2009-4-1| 1. 98| 13. 14%| 3744. 7| 6. 01%| 2009-3-2| 1. 75| 11. 46%| 3532. 3| 7. 14%| 2009-2-2| 1. 57| -35. 92%| 3296. 9| -5. 21%| 2009-1-2| 2. 45| -6. 84%| 3478. 1| -4. 95%| 2008-12-1| 2. 63| 13. 36%| 3659. 3| -0. 36%| 2008-11-3| 2. 32| -4. 13%| 3672. 7| -7. 78%| 2008-10-1| 2. 42| -22. 68%| 3982. 7| -14. 00%| 2008-9-1| 3. 13| -7. 40%| 4631. 3| -11. 20%| 2008-8-1| 3. 38| 1. 81%| 5215. 5| 3. 22%| 008-7-1| 3. 32| 9. 21%| 5052. 6| -5. 26%| 2008-6-2| 3. 04| -12. 39%| 5332. 9| -7. 64%| 2008-5-1| 3. 47| 2. 06%| 5773. 9| 2. 07%| 2008-4-1| 3. 4| -13. 49%| 5657| 4. 57%| 2008-3-3| 3. 93| -6. 87%| 5409. 7| -4. 67%| 2008-2-1| 4. 22| -9. 64%| 5674. 7| -0. 39%| 2008-1-1| 4. 67| -14. 15%| 5697| -11. 28%| 2007-12-3| 5. 44| -7. 01%| 6421| -2. 62%| 2007-11-1| 5. 85| -1. 02%| 6593. 6| -2. 74%| 2007-10-1| 5. 91| 5. 91%| 6779. 1| 3. 01%| 2007-9-3| 5. 58| 0. 00%| 6580. 9| 5. 32%| 2007-8-1| 5. 58| -2. 79%| 6248. 3| 0. 98%| 2007-7-2| 5. 74| 2. 50%| 6187. 5| -1. 95%| 2007-6-1| 5. 6| -1. 75%| 6310. | -0. 49%| 2007-5-1| 5. 7| 7. 14%| 6341. 8| 2. 98%| 2007-4-2| 5. 32| 1. 33%| 6158. 3| 3. 00%| 2007-3-1| 5. 25| 1. 94%| 5978. 8| 2. 79%| 2007-2-1| 5. 15| -4. 45%| 5816. 5| 1. 02%| 2007-1-1| 5. 39| 3. 26%| 5757. 7| 2. 01%| 2006-12-1| 5. 22| 5. 45%| 5644. 3| 3. 35%| 2006-11-1| 4. 95| 16. 75%| 5461. 6| 2. 03%| 2006-10-2| 4. 24| 8. 44%| 5352. 9| 4. 69%| 2006-9-1| 3. 91| 13. 66%| 5113| 0. 65%| 2006-8-1| 3. 44| 12. 79%| 5079. 8| 2. 48%| 2006-7-3| 3. 05| 3. 04%| 4957. 1| -1. 53%| 2006-6-1| 2. 96| -6. 03%| 5034| 1. 24%| 2006-5-1| 3. 15| -8. 96%| 4972. 3| -4. 51%| 2006-4-3| 3. 46| -2. 6%| 5207| 2. 35%| 2006-3-1| 3. 54| -13. 66%| 5087. 2| 4. 28%| 2006-2-1| 4. 1| -0. 73%| 4878. 4| -0. 04%| 2006-1-2| 4. 13| 2. 23%| 4880. 2| 3. 64%| 2005-12-1| 4. 04| 7. 16%| 4708. 8| 2. 73%| 2005-11-1| 3. 77| 10. 23%| 4583. 6| 3. 87%| 2005-10-3| 3. 42| 1. 48%| 4412. 7| -3. 92%| 2005-9-1| 3. 37| 4. 98%| 4592. 6| 4. 06%| 2005-8-1| 3. 21| -3. 31%| 4413. 5| 1. 54%| 2005-7-1| 3. 32| -1. 48%| 4346. 7| 2. 76%| 2005-6-1| 3. 37| 4. 66%| 4229. 9| 3. 92%| 2005-5-2| 3. 22| 0. 00%| 4070. 4| 3. 23%| 2005-4-1| 3. 22| -9. 30%| 3943. 1| -3. 84%| 2005-3-1| 3. 55| -2. 20%| 4100. 6| -1. 34%| 2005-2-1| 3. 3| 1. 97%| 4156. 5| 1. 21%| 2005-1-3| 3. 56| -4. 04%| 4106. 7| 1. 32%| 2004-12-1| 3. 71| ? | 4053. 1| ? | 2. Characteristic line and Beta The beta factor of a stock is an indicator of the degree to which a stock responds to changes of the market return. The characteristic Line shows the expected return for a selected share for a given rate of return for the market. The slope of the Characteristic Line is commonly referred to as the stock’s beta factor, or ?. Use the regression calculation to regress QAN’s return on the index return. This relationship is provided by the regression nd is shown below(Use Excel to build the chart): As we can read from the regression analysis, the beta is 0. 287. It shows that if the overall market return goes up by 1% QAN’s return will increases by 0. 287%. The estimated Beta in the regression is an equity beta, because it is the proportion of the market risk shareholders bear. 3. Five years historical cash flow analysis I obtained the necessary figures from QAN’s last five years annual reports to rebuild its last five years’ cash flow. The FCF is the theoretical basis for all present value (PV) calculations.

The FCF method is a more reliable measurement for the PV calculation than using either dividends or earnings per share (EPS). Therefore, we use the FCF method to assess the value of QAN. Following figures are extracted from the financial statements in order to rebuild the historical CF. Because of lacking information we do not count capital investment in our calculation, which normally is included. CF = (Rev – Cost – Depr. )(1-T) + Depr. + ? WC ?| 30/06/2005$m| 30/06/2006$ m| 30/06/2007$ m| 30/06/2008$ m| 30/06/2009$ m| Revenue| 12,564| 13,647| 15,060| 15,627| 14,552|

Expenses| 10,309| 11,671| 12,667| 12,796| 13,004| Depreciation| 1,241| 1,250| 1,363| 1,469| 1,390| EBIT| 1,014| 726| 1,030| 1,362| 158| Tax Expense| 304| 218| 309| 409| 47| NPAT| 710| 508| 721| 953| 111| Depreciation| 1,241| 1,250| 1,363| 1,469| 1,390| changes in Working Capital| -34| -1,076| 552| 913| -1,261| Rebuild cash flow| 1,917| 682| 2,636| 3,335| 240| The figures of trading revenue, expenses, depreciation and amortisation are obtained from QAN’s financial statements. Expenses don’t include depreciation and interest expenses. Tax expense is calculated as 30% of EBIT.

The following formulas are used to compute working capital: WC = Operating Current Assets – Non Interest Bearing Current Liabilities ? WC = WCt-1 – WCt ?| 30/06/2004| 30/06/2005| 30/06/2006| 30/06/2007| 30/06/2008| 30/06/2009| Operating Current Assets| 3322| 3,705| 5,053| 5,587| 5,616| 5,966| Non Int Bearing CL| 4368| 4,717| 4,989| 6,075| 7,017| 6,106| Operating Wkg Capital| -1,046| -1,012| 64| -488| -1,401| -140| Increase of working capital| ? | -34| -1,076| 552| 913| -1,261| 4. Forecast of relevant data for another five years The following sections deal with the forecasting of FCF over the next 5 years.

After calculating the average increasing rate from QAN’s last 5 years’ financial data, combining with other contributing factors such as inflation, we can forecast its FCF in the following 5 years. The relevant cash flow data forecasted are in nominal terms. ?| 30/06/2005| 30/06/2006| 30/06/2007| 30/06/2008| 30/06/2009| Revenue| 12,564| 13,647| 15,060| 15,627| 14,552| Growth| ? | 8. 62%| 10. 35%| 3. 76%| -6. 88%| Average Growth| 3. 96%| Expenses| 10,309| 11,671| 12,667| 12,796| 13,004| Growth| ? | 13. 21%| 8. 53%| 1. 02%| 1. 63%| Average Growth| 6. 10%| 4. 1 Forecast of revenues in the next five years

As to industry environment, aviation transportation is an industry with high risk, cost and competition. We believe that Australian economic growth is likely to decelerate after the year 2009 as well, due to the increasing of interest rates, inflation and boosting of fuel price. The price competition among airline companies will be more intense, which will put further pressure on QAN’s profitability. In order to relief the great pressure on its flying business, QAN developed a diverse portfolio of airline related businesses such as freight, engineering, airports and flight catering.

All these businesses are essential components of the QAN Group’s growth strategy. In the last five years, Australia’s GDP growth rates 0. 3–1. 6% and inflation has typically been 2–3% with base interest rates 4–6%. Combining with the average growth of the avenue, we estimate that the growth rate of revenues from 2010 to 2014will be 4% each year. ?| 30/06/2010| 30/06/2011| 30/06/2012| 30/06/2013| 30/06/2014| Revenue| 15,134| 15,739| 16,369| 17,024| 17,705| 4. 2 Forecast of cost in the next five years

From the financial statement we can see that, fuel, depreciation and foreign exchange hedge are the most important factors contribute to total expenses. 4. 2. 1 Fuel price The oil price has increased dramatically in recent years, due to the increasing demand of oil from the development countries and the hedge fund which see crude oil as a lucrative investment because of tight supplies, political turmoil in oil-producing nations, and disruptions at pipelines, oil rigs and refineries. In next five years, we estimate the fuel expense will still be fluctuating and high. . 2. 2 Hedge QAN uses several financial instruments to control its fuel and foreign exchange risks. It uses options and swaps on aviation fuel and crude oil to hedge the exposure to movements in the price of aviation fuel. To the extent that the hedges are assessed as highly effective and control the Fuel price and foreign exchange risks successfully. Thus we estimate that the growth rate of costs from 2010 to 2014 will be 3. 5%. ?| 30/06/2010| 30/06/2011| 30/06/2012| 30/06/2013| 30/06/2014| Expenses| 13,459| 13,930| 14,418| 14,922| 15,445| 5. Forecast of depreciation and tax over the next five years According to last five annual reports, QAN spent to invest in aircrafts stably every year. Thus, depending on average increase rate, the depreciation we expect it will increase 8% p. a. in next 5 year. This is because there will be 20 new plane acquire by QAN in the future. This will increase the depreciation in a steady rate, and we assume that corporate tax will be 30% on EBIT. ?| 30/06/2010| 30/06/2011| 30/06/2012| 30/06/2013| 30/06/2014| Depreciation| 1,501| 1,621| 1,751| 1,891| 2,042| . 4 Forecast of working capital over the next five years The increase of WC was very volatile in the last five years, and there is no rule we can find from its average growth rate. Cash, accounts receivable, accounts payable and revenue received in advance are the major factors contributing to the fluctuation, which are hard to forecast as well. Here we assume that there is a relationship between trading revenue and working capital, and the ratio between trading revenue and working capital will be 5% each year from 2010 to 2014. | 30/06/2009| 30/06/2010| 30/06/2011| 30/06/2012| 30/06/2013| 30/06/2014| Revenue| 14552| 15,134| 15,739| 16,369| 17,024| 17,705| Operating Current Assets| 728| 757| 787| 818| 851| 885| Increase of working capital| ? | -29| -30| -31| -33| -34| Following table sums up our forecasts: ?| 30/06/2010| 30/06/2011| 30/06/2012| 30/06/2013| 30/06/2014| Revenue| 15,134| 15,739| 16,369| 17,024| 17,705| Expenses| 13,459| 13,930| 14,418| 14,922| 15,445| Depreciation| 1,501| 1,621| 1,751| 1,891| 2,042| EBIT| 174| 188| 200| 210| 218| Tax Expense| 52| 56| 60| 63| 65|

NPAT| 122| 132| 140| 147| 152| Depreciation| 1,501| 1,621| 1,751| 1,891| 2,042| Changes in Working Capital| -29| -120| -130| -140| -151| Forecast cash flow| 1,594| 1,633| 1,761| 1,898| 2,043| 5. WACC WACC can be calculated as below: WACC= D/V? rd + E/V ? re From the formula, the capital structure of the company is the fraction of debt (D/V) and equity (E/V) based on market values, and V which is the total market value for the firm is the sum of D and E. The market value of equity equals to share price multiplied by number of shares outstanding.

Based on CAPM (Cost of Assets Pricing Model), the cost of equity equals to: re = rf + ? e ? (rm-rf) Each component of the formula is explained as follow: * rf is the risk free rate which is supposed to be the interest rate of Treasury bond. Australia Government Bond Yield for 10 Year Notes stands at 5. 42 percent. Therefore rf is approximately at 5. 42% p. a * ? equity is equity beta and estimated at 0. 287 according to part 1’s calculation. * (rm- rf) which is the market risk premium is assumed to be 8% based on market observation.

The cost of equity of QAN is therefore: re = rf + ? e ? (rm-rf) = 5. 42%+0. 287? 8%=7. 716% In Qantas airways limited’s preliminary final report, we can fine the adjusted net borrowing cost Years| 30/06/2005| 30/06/2006| 30/06/2007| 30/06/2008| 30/06/2009| Cost of Debt| 5. 60%| 6. 50%| 7. 30%| 7. 40%| 7. 80%| Which average rate is 6. 92%, therefore rd=6. 92%. Now calculate the WACC Years| 30/06/2005| 30/06/2006| 30/06/2007| 30/06/2008| 30/06/2009| Total Debt| 12,861| 13,102| 13,854| 13,965| 14,284|

Total Equity | 5,523| 6,081| 5,640| 5,735| 5,765| Total Value| 18,384| 19,183| 19,494| 19,700| 20,049| d/v| 69. 96%| 68. 30%| 71. 07%| 70. 89%| 71. 25%| e/v| 30. 04%| 31. 70%| 28. 93%| 29. 11%| 28. 75%| Average d/v=| 70. 29%| Average e/v=| 29. 71%| WACC=7. 716%*29. 71%+6. 92%*70. 29%=7. 16% 6. Estimation of QAN’s current value ?Present Value of next five years’ cash flow ?| 30/06/2010| 30/06/2011| 30/06/2012| 30/06/2013| 30/06/2014| Forcaste cash flow| 1,594| 1,633| 1,761| 1,898| 2,043|

The PV cash flows in the years 2010-2014 is calculated as: PV2010-2014 7591m ?Terminal Value of 2014 The sustainable growth rate after 2014 is calculated as follow, and the plough back ratio we use here is the average of plough back ratio of last five years g = Plough back ratio ? Return on Equity =31. 47%*7. 716%=2. 248% The calculation of terminal value as: PV terminal = ? (1+r)5 = 60580m P0 = (PV2010-2014 + PVterminal)/No. of Shares = (7591+60580)m/ 2,265,123,620=30. 009

×

Hi there, would you like to get such a paper? How about receiving a customized one? Check it out