Assignment
The purpose of this assignment is to conduct a fundamental analysis of a company and derive an intrinsic value for its shares. This will require students to identify whether stocks are under or over valued in accordance with the valuation models and techniques used in this course together with appropriate analysis of the fundamental drivers of a company performance. This assignment can be done in pairs or in groups of 3 students maximum but students are discouraged from doing it individually. The assignment is due on Friday 23rd October 4pm and is worth 15% of the total assessment for this subject and is not redeemable.
1. ASSIGNMENT REQUIREMENTS
The assignment has 3 parts. In the first part you are required to conduct an analysis of the Australian economy, the industry sector to which the company belongs and the company itself. The second part necessitates that you determine whether each stock selected is under or over-valued according to multiple analysis and the third part requires you to perform a valuation employing a free cash flow methodology.
Students are required to select a stock based on their student number (see below for instructions).
The assignment is worth 100 marks. Marks will be distributed as follows:
PART A
Requirement: To fulfill this requirement you should conduct an investigation of the operations of the company, identifying the key drivers of the company's performance and the industry sector to which it belongs and then identify how recent trends in the Australian economy are likely to impact on the company's future performance.
For information on individual company and industry sector to which it belongs see library website (select 'quick links'- Databases by subject/ Business/ Data Analysis Premium). Data on the Australian economy can be obtained from Reserve Bank of Australia https://www.rba.gov.au/.
Output: Discussion should be provided in word document only (together with other requirements from Parts B & C below).
PART B Analysis of Multiples
Requirement: You are required to determine whether the stock is under or over-valued according to analysis of multiples and then determine its intrinsic value. A suitable fundamental ratio should be used as the benchmark and appropriate adjustments to the measurement of earnings and book value should be considered.
All ratio formula inputs (i.e. earnings, discount rate, retention ratio etc) should be measured by performing an appropriate analysis of firm data over the last 4 years and performed in excels (see below for instructions on Downloading Data). The discount rate should be estimated using the capital asset pricing model. Students will therefore need to perform regression analysis to determine its beta and provide evidence that the estimate is reliable (i.e. R Square, p-value) as well as estimating the risk free rate and equity risk premium. Outputs of this section should also be used in Part C of the assignment.
Output: Provide a separate spreadsheet for the following:
- 'Unadjusted' income statement and balance sheet downloaded for the last 4 years.
- 'Adjusted' income statement and balance sheet for the last 4 years together with relevant analysis (ie ROE, b) and calculation of ratios
- Cost of equity calculations
- Supporting beta regression outputs
Provide in the word document justification of your methodology, including choice of ratio and choice of data for determination of the discount rate
PART C: Analysis of free cash flow Requirement: You are required to determine the intrinsic value of a share using both FCFE and FCFF models. This will require you to use the 'adjusted' financial data and discount rate determined in Part B and forecast financial statements for the next 5 years from which free cash flows are determined.
1. Forecasting financial reports.
Provide 'pro-forma' income statement and balance sheets for years 2016 - 2020.
a. From an analysis of past income (revenues) forecast revenues for the next 5 years. Provide appropriate analysis of revenues in a separate spreadsheet and provide a discussion on the relevance of your methodology in the word document (see below).
b. Retained profits. This should represent an accumulated balance of profits not distributed as dividends from year to year. The dividend payout ratio should be determined from an appropriate analysis of dividends distributed from past data. Provide a separate line representing dividends in the spreadsheet immediately below the net profit section even though this is not normally disclosed.
c. Taxation. Estimate the tax expense based on the annual tax liability and consider the relationship between the effective tax rate and the marginal (statutory) tax rate over time.
d. Debt. Determine an appropriate level of debt so that a target D/E ratio is achieved by 2020.
e. Plug. To ensure that your projected balance sheet actually balances year by year an item from the balance sheet must be selected to be a PLUG - this item ensures that the balance sheet actually balances and therefore is not determined from revenues but should be the last item determined for the balance sheet. Select an appropriate item to be used as the PLUG and clearly mark this in the formula column.
f. For brevity and with the exception of items mentioned above, forecast remaining items of the income statement & balance sheet based on their association with past income (for example, 'cost of sales' based on past 4 years data may on average represent 45% of revenues and therefore cost of sales can be forecasted based on this relationship) once sales have been forecasted. Sub-totals and totals should be determined independently.
g. The formula of each item should be provided in a separate column in the spreadsheet (which in turn is based on calculation and linked with other cells).
h. Your forecast financial statements should be provided on the same single excel spread sheet as your 'adjusted' past data. Separate forecasts years from past years by a single column that should contain the formula for each item, for example, the cell for 'cost of sales' should contain the formula in which it is forecasted: = average (cost of sales/ sales).
|
Item
|
Actual 20X1
|
Actual
20X2
|
Actual
20X3
|
Actual
20X4
|
Formula
|
Expected
20X5
|
Expected
20X6
|
|
Cost of sales
|
x
|
x
|
x
|
x
|
0.45 or 45%
|
x
|
x
|
2. Forecast free cash flow
Produce forecast free cash flows to equity (FCFE) and the firm (FCFF) year by year, showing each item separately. Each item should be determined by formula, linked with the appropriate items from the forecast income statement and balance sheet.
3. Estimate the cost of capital
For the estimate of WACC, use the cost of equity already determined in Part B.
4. Calculate the value of shares
Estimate the value of shares in excel (using same excel spreadsheet). The following provides an example.
|
FREE CASH FLOW TO EQUITY
|
E20X5
|
F20X6
|
F20X7
|
F20X8
|
F20X9
|
|
|
130.0
|
48.4
|
52.5
|
57.0
|
61.8
|
|
Discount rate
|
|
|
|
|
|
|
Terminal Value and formula
|
|
|
|
|
|
|
PVTV
|
|
|
|
|
|
|
Total Present Value of Equity
|
|
|
|
|
|
|
Number of shares (m)
|
|
|
|
|
|
|
Price per share
|
|
|
|
|
|
5. Provide a reconciliation of the value of equity (VE) between the two models
Output: Provide a separate spreadsheet for the following:
- 'Adjusted' income statement and balance sheet together with forecasts (pro-forma financial data), formula, forecast free cash flows to equity and the firm, present value calculation and reconciliation between FCFE & FCFF models.
- Analysis of revenues
- Cost of equity and capital calculations (use same spreadsheet as for Part B)
- Supporting beta regressions (use same spreadsheet as for Part B)
Provide in the word document a brief discussion of method chosen to forecast revenues together with justification.
Selection of company
Students are required to select a company based on the combined student number of their group. To determine the combined student number for a group add together the last digit of each student's ID number (ie if there are 2 students in a group and the individual student numbers are a10503 and a14932 then the combined student number is 5).
o If your combined student number ends in 0, 1 or 2 then your company is REA Group Ltd (ASX: REA).
o If your combined student number ends in 3 or 4 then your company is James Hardie Industries Ltd (ASX: JHX).
o If your combined student number ends in 5 or 6 then your company is Brambles Ltd (ASX: BXB).
o If your combined student number ends in 7, 8 or 9 then your company is TPG Telecom Ltd (ASX: TPM).