General Comments
A lot of students stress over the measurement and forecast of individual items from the income statement and balance sheet. Please keep in my mind that what is important is the process rather than the actual output. For example, when you are conducting a regression, what I consider important is your judgement of whether the results are reliable enough to use rather than the numbers themselves. You should be able to interpret the regression outputs and decide whether they should be included in the valuation or not. Similarly, I do not have in mind a $value of what the intrinsic value of any of the firms being analysed should be, however I have a good idea of the methodology you should be using, and it is the methodology that is most important.
Purpose
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. This assignment is to be done in groups of 5 students. Please read the following carefully.
The assignment due date is Friday, October 23rd, by 5 pm, and is worth 20% of the total assessment for this subject. The marks for this assessment piece is not redeemable. You are required to submit 1 word file (a maximum of 1200 words) and an excel file electronically to ‘MyUni’.
There are 2 sections to this assignment. In Section A you are required to perform a valuation of BHP Group Limited (ASX Issuer code: BHP) using the discounted cash flow methodology and in Section B you are required to perform a smaller valuation by multiples. The majority of the work required from you is in Section A and the marking reflects this (see next page). You are required to submit both an Excel file and a Word document electronically and each will be marked separately.
Section A
You are required to perform a valuation of BHP Group Limited (ASX Issuer code: BHP) employing the discounted cash flow methodology to determine whether the share price of BHP on the 1^{st} of July of the year in your analysis period (see below) is fair. The definition of cash flows to be forecast is free cash flows to equity and free cash flows to the firm. Before performing the valuation, you are required to conduct preliminary research.
Year selection is based on the sum of each group member’s student id:
2019: sum ends in ‘0’, ‘1’ or ‘2’
2018: sum ends in ‘3’ or ‘4’
2017 sum ends in ‘5’, ‘6’ or ‘7’
2016: sum ends in ‘8’ or ‘9’
Required
You have been asked by your manager to perform a valuation of shares in BHP by addressing the following tasks.
Section B
Determine whether each of the following stocks (based on the sum of each group member’s student id) is currently under or overvalued (as of January 15^{th}, 2020) using the trailing P/E ratio or trailing P/B ratio (including fundamental measures of these ratios) and an appropriate peer group. You should select two peer firms for your comparative analysis. Show relevant calculations and discuss the choice of multiple and the selection of the peer group. This step must be completed in Word document only.
Commonwealth Bank of Australia (ASX Issuer code: CBA): sum of each group member’s student id ending in ‘0’, ‘1’ or ‘2’
Woolworths Group Limited (ASX Issuer code: WOW): sum of each group member’s student id ending in ‘3’, ‘4’, ‘5’, or ‘6’
Origin Energy Limited: (ASX Issuer code: ORG): sum of each group member’s student id ending in ‘7’, ‘8’ or ‘9’
Marking Scheme
The assignment is worth 20 marks and marks will be distributed as follows:
Section 1: Overview 3
Section 2: Pro-forma and DCF valuation 10
Section 3: Recommendation 1
Section B: Valuation by multiples 4
Presentation 2
Total marks 20
ASSIGNMENT SUBMISSION
The assignment should be submitted electronically on MyUni under the Assignments tab. Marks are deducted for late submission (see guidelines in Course Outline). A separate folder will be provided for the Word file document using ‘turnitin’ and Excel file (don’t mix them up!). Please make sure that you provide your student ID on the first spreadsheet of your excel submission.
Statement of Acknowledgement of Original Work
By submitting your assignment you are agreeing to the following:
I declare that all material in this assessment is my own work except where there is clear acknowledgement and reference to the work of others. I have also read the University’s Academic Honesty Policy.
I give permission for my assessment work to be reproduced and submitted to other academic staff for the purposes of assessment and to be copied, submitted and retained in a form suitable for electronic checking of plagiarism.
Please be aware of policy and guidelines regarding plagiarism (see Course Outline for website link).
DCF Valuation Instructions (Microsoft excel only)
Perform the following instructions in Microsoft excel spreadsheet. A single spreadsheet containing actual, adjusted and forecast financial data together with FCF calculations and valuation should be produced and clearly marked. All other analysis should be performed on a separate spreadsheet sheet. See following example of spreadsheet presentation below.
Required
Producing Pro-forma financial statements and valuation:-
You should download a spreadsheet of data for the income statement and balance sheet obtained from the library website (select Databases by subject/ Business/ DatAnalysis Premium from Morningstar/BHP/Financial Data). You will need the spreadsheets corresponding to the Profit and Loss and Balance Sheet.
Your manager is a well-organised professional and wants your valuation to be professional document. Before proceeding to the next step, your manager advises you to make the following modifications to improve the presentation of the data.
Remove the prefixes CA, NCA, CL NCL from the third column for each row and create row headings with the titles Current Assets, Non-current Assets, Current liabilities and Non-current liabilities in the appropriate places by adding new rows.
Your manager expects the following:-
On the same excel spreadsheet, 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.
An estimate of the cost of equity and WACC is required. The discount rate for equity should be estimated using CAPM. Estimate beta by regressing share returns against the market index returns using the market data provided along with the assignment information on “MyUni”. Supporting beta analysis should be provided on a separate spreadsheet and you will need to provide a summary table of data (see below).
Two tables, one containing the value of equity and a share using FCFE and the other according to FCFF should be presented as follows at the bottom of the pro-forma financial statements spreadsheet. Tables containing the summary of beta estimates and discount rate data should be provided underneath (see below).
Data Sources:-
All information including annual reports and share prices can be obtained from the library website (select Databases by subject/ Business/ DatAnalysis Premium). Share price data can also be obtained from https://au.finance.yahoo.com/. For government information such as GDP and the CPI Index go to Reserve Bank of Australia http://www.rba.gov.au/.
Columns SINGLE SPREADSHEET |
A | B | C | D | E | F | G | H | I | J | K | L |
Income statement | Actual data X1 | Actual data X2 | Adjusted data X1 | Adjusted data X2 | % Revenue X1 | % Revenue X2 | Ave % Revenue | Formula description | Actual excel formula | Forecast X3 | Forecast X4 |
Revenue etc | |||||||||||
Cost of sales | 60% of revenues | <=average()> |
|||||||||
Balance sheet | % | ||||||||||
Current assets etc | |||||||||||
FCFE | Forecast X3 | Forecast X4 | |||||||||
E, NCC etc | |||||||||||
FCFF | Forecast X3 | Forecast X4 | |||||||||
E, NCC etc | |||||||||||
Valuation FCFE | See below | ||||||||||
Valuation FCFF | See below |
FREE CASH FLOW TO EQUITY | F20XX | F20 XX | ||
FCFE | ||||
Discount rate | ||||
Present value | $x | $x | $x | |
Terminal Value and formula | ||||
PVTV | ||||
Total Present Value of Equity | ||||
Number of shares (m) | ||||
Price per share |
Market price as at | |
Recommendation buy/ sell/ hold |
Presentation of Excel file.
Cost of equity calculation | |||
Risk free rate | Beta | Risk premium | |
Cost of equity | |||
WACC Calculation | |||||
Cost of equity | D/V | Cost of debt | D/V | (1-T) | |
WACC |
Important: You will marked on how well you link each cell by ‘excel’ formulae. You will lose marks if you ‘cut & paste’ or manually type-in data when it is not necessary. Provide a very brief description of how you forecast each item in ‘column F’ and the actual formula in ‘column G’, for example ‘cost of goods sold’ may be forecast based on average percentage of revenues in which case ‘average % of past revenues’ should be ‘keyed in’ in ‘column F’ and the excel formula of this calculation should be provided in ‘column G’. In this way ‘cost of goods sold’ can then be forecast by linking it to the formula in ‘column G’ and the value of revenue in ‘column H’. Items can also be linked to sources from other spreadsheets by formula.
[Button id=”1″]
Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.
Hi there! Click one of our representatives below and we will get back to you as soon as possible.