Group Project

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.

1. Provide an overview of the current operations of BHP and the industry sector to which BHP belongs. This should include a discussion of the type of goods or services that are produced by BHP, its recent performance and the key challenges faced by it and the industry sector to which it belongs. For comparison, compare the performance of BHP with a listed company operating in the same or very similar industry. This step must be completed in word document only.
2. Produce pro-forma financial statements and perform a DCF valuation using free cash flows to equity and the firm. See DCF Valuation Instructions below. This step must be completed in excel spreadsheet only. Please follow the presentation/format guidelines otherwise you may lose marks or some of your work may not be marked.
3. Provide a recommendation to Buy/ Sell or Hold shares in BHP at the valuation date. This step must be completed in Word document only.

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:-

1. Downloading actual financial reports.

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.

• If your valuation year is 2019 you will need data for years 2016-19; if 2018 you will need data for years 2015-18; if 2017 you will need data for years 2014–17; and if 2016 you will data for years 2013-16. Delete all other years of data by deleting the corresponding spreadsheet columns.
• Delete the first 2 columns from the spreadsheets which correspond to the ASX code and Company name (these are not needed).
• Remove items where the amount is $0 for each year by deleting the corresponding spreadsheet rows.
• Round all numbers to the nearest $m (do not show cents).
• Make sure all spreadsheet cells containing totals (ie total assets) or subtotals (ie EBIT) are determined by way of spreadsheet formula. This means that you will have to delete the corresponding data for each total and determine the cell equation that determines that amount (as an example, sum (A5:A10)).
• From the Profit and Loss statement only remove the last 4 rows, from ‘Shares Outstanding’ to ‘EPS after abnormal’ (as these are not needed) and create a new row with heading ‘Parent shareholders’ interest in Profits’ and calculate the correct balance for each year by spreadsheet formula.
• From the balance sheet only create two new rows: one immediately below the row headed ‘Total liabilities’ with the heading ‘Net Assets’ and calculate the correct balance for each year; the other immediately above the row ‘Outside equity’ interest with the heading ‘Total Parent shareholders’ interest’ and calculate the correct balance for each year by spreadsheet formula

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.

2. Your manager has indicated in previous discussion that financial reports should be adjusted for items such as goodwill and deferred taxes using the Retained profits account to ensure that the balance sheet actually balances.

3. Forecasting financial reports.

Your manager expects the following:-

1. Provide ‘pro-forma’ income statement (profit and loss) and balance sheets for the next 5 years following the year of your valuation ie 2017 – 2021 if valuation year is 2016 and years 2018 – 2022 if year of valuation is 2017 and so on.
2. Forecast revenues (sales) based on a sound methodology.
3. Retained profits. This should represent an accumulated balance of undistributed profits. Profits distributed as dividends should be determined from an appropriate analysis of dividends distributed from past data (show all analysis on a separate spreadsheet). Note: although some firms pay dividends in excess of current profits you should count the dividend payout ratio as 100% maximum for any one year. Similarly count dividend payout ratios as 0% in cases when it is negative. Provide a separate line representing dividends in the spreadsheet immediately below the net profit section even though this is not normally disclosed as part of the income statement.
4. Select ‘Cash’ as your 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 should be the last item determined in the balance sheet.
5. Tax expense should be forecast based on the past average effective tax rate.
6. Depreciation should be forecast based on the past average depreciation rate, where the depreciation rate_t = Depreciation_t / PPE_t-1.
7. Interest expense should be forecast based on the past average interest rate, where the interest rate_t = Finance costs_t / Interest- bearing debt_t-1.
8. Interest bearing debt (non-current portion only) should be forecast based on its past average percentage of paid up capital (Share Capital).
9. Forecast all other items of the income statement and balance sheet as a % of operating revenues based on past association with revenues. Any item that has a zero balance for the most current financial year should not be forecast.
10. All sub-totals and totals should be determined as totals.
11. Except for sub-totals and totals, a simple description of the formula of each item should be provided in a separate column in the spreadsheet together with the actual cell containing the formula in the next column (see spreadsheet format example on following page).
12. Your pro-forma financial statements should be provided on a single spreadsheet together with actual and ‘adjusted’ past data, free cash flows and valuation (see spreadsheet format example on following page).

4. Forecast free cash flow

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.

5. Estimate the discount rate

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).

6. Valuation

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.