FNS60215 Advanced Diploma of Accounting: Financial Modeling
Please read the scenarios below and answer the questions or complete the activities that follow.
Financial Modelling
Scenario 1:
You work for Australian Super, one of Australia’s largest super funds. This Superannuation Fund is committed to passing on all good things that size has to offer. The vision of this organisation is:” Every Australian should have a solid financial future, so our purpose is to help achieve better retirement outcome for all”.
Australian Super is committed to providing quality service by employing managers and specialist staff in- house, and complements their expertise with external service providers.
Your manager has asked you to prepare a comprehensive financial model for the purpose of determining where $100 million dollars will be invested. The Strategic Plan for Australian Super over the next 3 years is the achieve 10% return on investment.
Your task is to choose 2 ASX listed companies from the list below. Using Microsoft Excel prepare a financial model to determine the value and ongoing viability of both companies for a period of 3 years. Make your recommendations as to your preferred organisation and explain your reasons.
- Develop an asset pricing model to determine the required or expected rate of return on the assets
- Determine the value in relation to the capital structure of both companies.
- Determine in the theoretically appropriate rate of return will be 10% over the 5 years’ period from the purchase of shares in the organisation.
- The model should take into account asset’s sensitivity to non- diversifiable risk, as well as the expected return of the market and the expected return of a theoretical risk- free asset.
- Detailed market analysis and other information to the support projected numbers
- Consider how the economic and political climate might relate to the financial industry when undertaking financial modelling.
ASX Listed companies include:
- Woolworths Ltd
- Lend Lease Group
- Toll Holdings
- Goodman Groups
- Qantas Airway
Solution
Scenario 1:
The two funds that are evaluated for their valuation for investment in the coming three years are Woolworths and Qantas Airways. Both of these stocks are listed on the Australian stock exchange and are considered for the investment of $100 million dollars for the next three years. The investment is required to achieve an expected return on investment of 10% or above in the future three years of investment in any one of the stocks. The valuations for both the stocks are conducted as follows.
Woolworths Limited (ASX: WOW):
Woolworths is one of the leading firms in the Australian retail industry for food and staples. The company has over the years redefined its strategy of conducting business in the retail industry. The Woolworths Group has recently adopted its lean retail operating model which has enabled it to invest its savings from efficient services into every aspect of the customer’s offer. This enabled them to transfer the savings of around $125 million in the fiscal year of 2016 into lower prices of their services. The retail giant has been focusing on improving the customer experience by offering innovative offers, through refurbishments and improved service. The value position of Woolworths is currently at its most competitive level since 2014. The company has shown that it is going to follow a lean retail operating model in the coming three years. This model will yield cost reduction that can then be transferred to the customers in the form of discounts and reduced prices. The company is going to invest around $500 million in the coming three years to ensure they are not beaten on prices and that they offer better and attractive services to the customers (Woolworths Group, 2019).
Industry Profile & Valuation:
The Woolworths Company has delivered a Return on Equity of 7.3% over the years, which show that the company has delivered 7.3 cents on each of the dollar spent by the investors. In light of the performance of the industry the return on equity delivered by the industry is around 4.67%.
The breakdown of the ROE shows that the company ROE is 7.3% while it’s Return on Assets in 6.2% and its Return on Capital is around 16%. The ROE of the company can be broken into the Net Profit Margin of the company times the asset turnover times the financial leverage of the company, which is also known as the DuPont Equation. The results show that the company has a better ROE as compared to the industry because of the excessive raising of the debt or either because of the balanced capital structure of the company as depicted from its debt-to-equity ratio in the past (Simply Wall Street, 2017).
Valuation:
The intrinsic value computation for Woolworths showed that the company Future cash flow value is going to be AUD 27.89 while its current share price AUD 36.01. This shows that the company is overvalued and is going to devalue in the future.
The computations are shown below.
The cash flow Sources of Data are.
| Source Used | Answer | |
| Valuation Technique | 2 Stage Free Cash Flow to Equity | |
| Levered FCF | Estimates Used by Analysts | Shown Below |
| Discount Rate Ke | Shown below | 7.10% |
| Perpetual Rate of Growth | 10-Year Rate of Australian Govt bond | 2.30% |
The computations for the discount rate used are shown below.
| Value | Calculation/ Source | Answer |
| Rf Rate | 10-Year Rate of Australian Govt bond | 2.30% |
| Risk Premium | S&P Global | 6% |
| Beta (Unlevered) | S&P Global | 0.5 |
| Beta (Re levered) | = Unlevered beta (1 + (1- Rate of Tax) (Dbt/Equ)) | 0.677 |
| = 0.497 (1 + (1- 30%) (6.34%)) | ||
| Beta Levered | Range of 0.8 to 2.0 | 0.8 |
| Ke/Cost of Equity/CAPM | = Discount Rate= RF + (Beta * Equity Risk Premium) | 7.08% |
| 0.07078 |
The calculations below ignore the cash or debt obligations of the company and are dependent on the discounted cash flows.
DCF 1st Stage: Next 10-year cash flow forecast
| Levered Free Cash Flow in millions | Source | PV | |
| Discounted (@ 7.08%) | |||
| 2020 | 1,521.60 | Estimates | 1,420.99 |
| 2021 | 1,609.40 | Estimates | 1,403.61 |
| 2022 | 1,683.79 | 4.62% | 1,371.39 |
| 2023 | 1,749.95 | 3.93% | 1,331.04 |
| 2024 | 1,810.21 | 3.44% | 1,285.84 |
| 2025 | 1,866.41 | 3.1% | 1,238.10 |
| 2026 | 1,919.91 | 2.87% | 1,189.39 |
| 2027 | 1,971.75 | 2.7% | 1,140.74 |
| 2028 | 2,022.70 | 2.58% | 1,092.84 |
| 2029 | 2,073.31 | 2.5% | 1,046.12 |
| PV of FCF in the coming 10 years | A$12,520.07 | ||
DCF 2nd Stage: Terminal Value
| Computations | Answers | |
| TV | = FCF2029 × (1 + growth rate) ÷ (Ke – growth rate) | A$44,489.27 |
| = A$2,073.31 × (1 + 2.31%) ÷ (7.08% – 2.31%) | ||
| PV of TV | = TV ÷ (1 + r)10 | A$22,447.69 |
| = A$44,489.27 ÷ (1 + 7.08%)10 | ||
| Total Value of Equity | ||
| Computations | Answers | |
| Total Value of Equity | = PV of FCF in coming 10 years + TV | A$34,967.76 |
| = A$12,520.07 + A$22,447.69 | ||
| EV per Share | = Total value / No of Shares | A$27.89 |
| (AUD) | = A$34,967.76 / 1,253.79 | |
| Share Price Discount | ||
| Computations | Answers | |
| Stock Price | From above. | A$27.89 |
| discount | Discount of A$36.01 | -29.1% |
| = -1 x (A$36.01 – A$27.89) / A$27.89 | ||
The results show that the stock is going to devalue in the coming 10 years. Thus, this does not look a viable option for investment.
Qantas Airways (AUX: QAN):
Qantas Airways is the flag carrier for Australia and is one of the largest airlines in terms of the international flights, destinations and fleet size. The company has been using its key strategic pillars for the measurement of its performance. These include the key financial and non-financial metrics of Return on Invested Capital, Gross Annual Benefits from the transformations, Net Promoter Score, Employee engagements, Progress on the innovations in operations, services and products, and EBIT Annual growth rate for the loyalty program. The company Return on Invested Capital has shown the performance to be greater than 10% for all of its operating segments (Qantas Airways, 2019).
Qantas Airways Industry Profile and Valuation:
Qantas Airways Return on Invested Capital for the last year has been 9.33%. The company has been generating higher returns as compared to its costs, which is depicted from its 3.26% cost of capital. This shows that the company is yielding excess returns in the past. The ROE of Qantas Airways is 24.8% as compared to the 17% industry mean ROE. The company ROE has been 25% while its ROA has been 6% and its Return on Capital has been 13%. The company has not taken extreme leverage which means that the above-average ROE yielded by the company is from its own capacity to yield massive profits without having any huge debt obligations (Simply Wall Street, 2018).
Valuation:
The computations are shown below.
The cash flow Sources of Data are.
| Source Used | Answer | |
| Valuation Technique | 2 Stage Free Cash Flow to Equity | |
| Levered FCF | Estimates Used by Analysts | Shown Below |
| Discount Rate Ke | Shown below | 9.20% |
| Perpetual Rate of Growth | 10-Year Rate of Australian Govt bond | 2.30% |
The computations for the discount rate used are shown below.
| Value | Calculation/ Source | Answer |
| Rf Rate | 10-Year Rate of Australian Govt bond | 2.30% |
| Risk Premium | S&P Global | 6% |
| Beta (Unlevered) | S&P Global | 0.87 |
| Beta (Re levered) | = Unlevered beta (1 + (1- Rate of Tax) (Dbt/Equ)) | 1.154 |
| = 0.873 (1 + (1- 30%) (58.42%)) | ||
| Beta Levered | Range of 0.8 to 2.0 | 1.15 |
| Ke/Cost of Equity/CAPM | = Discount Rate= RF + (Beta * Equity Risk Premium) | 9.19% |
| 0.0918784 |
The calculations below ignore the cash or debt obligations of the company and are dependent on the discounted cash flows.
DCF 1st Stage: Next 10-year cash flow forecast
| Levered Free Cash Flow in millions | Source | PV | |
| Discounted (@ 7.08%) | |||
| 2020 | 883.87 | Estimates | 809.47 |
| 2021 | 1,002.23 | Estimates | 840.61 |
| 2022 | 810 | 4.62% | 622.19 |
| 2023 | 773 | 3.93% | 543.79 |
| 2024 | 753.05 | 3.44% | 485.16 |
| 2025 | 744.67 | 3.1% | 439.38 |
| 2026 | 744.03 | 2.87% | 402.05 |
| 2027 | 748.75 | 2.7% | 370.54 |
| 2028 | 757.26 | 2.58% | 343.21 |
| 2029 | 768.54 | 2.5% | 319 |
| PV of FCF in the coming 10 years | A$5,175.40 | ||
DCF 2nd Stage: Terminal Value
| Computations | Answers | |
| TV | = FCF2029 × (1 + growth rate) ÷ (Ke – growth rate) | A$11,430.34 |
| = A$768.54 × (1 + 2.31%) ÷ (9.19% – 2.31%) | ||
| PV of TV | = TV ÷ (1 + r)10 | A$4,744.43 |
| = A$11,430.34 ÷ (1 + 9.19%)10 | ||
| Total Value of Equity | ||
| Computations | Answers | |
| Total Value of Equity | = PV of FCF in coming 10 years + TV | A$9,919.83 |
| = A$5,175.40 + A$4,744.43 | ||
| EV per Share | = Total value / No of Shares | A$6.41 |
| (AUD) | = A$9,919.83 / 1,547.11 | |
| Share Price Discount | ||
| Computations | Answers | |
| Stock Price | Above | A$6.41 |
| discount | Discount of A$5.86 | 8.6% |
| = -1 x (A$5.86 – A$6.41) / A$6.41 | ||
Recommendations:
The results show that the stock is going to increase in value in the coming 10 years. Thus, this does look like a viable option for investment. This means that the company can gain around 8.6% of gain in the 10-year period, which is near the investment return target. Therefore, this stock of Qantas Airways is better in terms of the investment of $100 million with around 8.6% of the profit in the 10 years period.
References:
Qantas Airways. (2019). Qantas Airways WACC. Retrieved August 22, 2019, from https://www.gurufocus.com/term/wacc/QUBSF/WACC/Qantas+Airways+Ltd
Simply Wall Street. (2018, August 26). Can Qantas Airways Limited (ASX: QAN) Continue to Outperform Its Industry? Retrieved August 22, 2019, from Simply Wall Street: https://simplywall.st/stocks/au/transportation/asx-qan/qantas-airways-shares/news/can-qantas-airways-limited-asxqan-continue-to-outperform-its-industry/
Simply Wall Street. (2017, July 6). Woolworths Limited (ASX: WOW) Delivered A Better ROE Than the Industry, Here’s Why. Retrieved August 22, 2019, from Simply Wall Street: https://simplywall.st/news/woolworths-limited-asxwow-delivered-a-better-roe-than-the-industry-heres-why/
Woolworths Group. (2019, August 22). Lean Retail Model to drive three-year growth plans. Retrieved August 22, 2019, from Woolworths Group: https://www.woolworthsgroup.com.au/page/media/Latest_News/Lean_Retail_Model_to_drive_three-year_growth_plans
