A lot of students stress over the measurement and forecast of individual items from the income statement and balance sheet. Please keep in 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 relationship is economically meaningful and 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 and a relative valuation analysis of a company thus derive an intrinsic value for its shares. This will require students to identify whether stocks are under or overvalued in accordance with the valuation models and techniques used in this course. This assignment is to be done in groups of 4 students. Please read the following carefully.

The assignment due date is Friday, 2024 Oct 25th, by 11:59 pm, and is worth 25% of the total assessment for this subject. The marks for this assessment piece are not redeemable. You are required to submit 1 Word file (with approximately 3,000 words, i.e., 2,900 - 3,100 words) and 1 Excel file electronically to ‘MyUni’.

There are 2 sections to this assignment. In Section A you are required to perform a fundamental valuation of a selected company (see below) using the discounted cash flow methodology, and in Section B you are required to perform a relative valuation by multiples. See the marking scheme for the mark distribution on page 3. You are required to submit both an Excel file and a Word document electronically and each will be marked separately.

You will need to draw on your knowledge and skills gained performing this valuation assignment in your further studies in CORPFIN 7020 - Derivatives (M) and CORPFIN 7019 - Advanced Funds Management (M).

Section A: fundamental valuation

You are required to perform a valuation of an Australian listed company (see below) employing the discounted cash flow methodology to determine whether its share price on the valuation date 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.

The company selection and its valuation date are based on the sum of each group member’s student id:

· AGL Energy Ltd (ASX Issuer code: AGL): sum ends in ‘0’ or ‘1’ or ‘2’; Valuation date: 3^rd of July, 2024

· Wesfarmers Ltd (ASX Issuer code: WES): sum ends in ‘3’ or ‘4’ or ‘5’; Valuation date: 3^rd of July, 2024

· Sonic Healthcare Ltd (ASX Issuer code: SHL): sum ends in ‘6’ or ‘7’; Valuation date: 3^rd of July, 2024

· Telstra Corporation Ltd (ASX Issuer code: TLS): sum ends in ‘8’ or ‘9’; Valuation date: 3^rd of July, 2024

Required

You have been asked by your manager to perform a valuation of shares in your selected company by addressing the following tasks.

1. Conduct a top-down fundamental analysis of your selected company on the following aspects. This step must be completed in Word document only.

• Global & Australian economic outlook

Provide a general description of the current economic conditions, and how those affect the demand and supply of the firm’s products. For example, GDP growth; Population growth and Demographics; Employment rate; Inflation; Fiscal policies (e.g., Tax); Monetary policies (e.g., Interest rate); Trade policies; Political tension and War; pandemics etc.

•Industry analysis

-Industry outlook which is related to the economic conditions above;

-Demand and supply analysis at the industry level, for example, the elasticity of the demand curve;

-Product market structure (monopoly, oligopoly, monopolistic competition or perfect competition), industry concentration, leader or follower (market share)

-Major competitors, suppliers and customers

-Sector profitability

•Peer analysis

Financial ratios, relative valuation, market share and sales growth, etc.

•Firm-specific analysis

Business description: break down of product lines, segments and markets; financial position and performance; major policies: leverage, dividend, investment policies (including R&D and innovation; M&A asset restructures); corporate governance; ownership structure, major stakeholders; business strategies; ESG analysis; performance driver; risk factors etc.

2. Produce Pro-forma financial statements and perform a DCF valuation using free cash flows to equity and free cash flows to the firm. Based on each valuation model, provide a recommendation to Buy, Sell or Hold shares in your selected company at the valuation date. See DCF Valuation Instructions below. This step must be completed in the excel spreadsheet only. Please follow the presentation/format guidelines otherwise you may lose marks or some of your work may not be marked.

Section B: relative valuation

1. Determine whether your selected company in section A (based on the sum of each group member’s student id on page 1) is currently under or overvalued (as of the most recent date) compared to peer firms, using the trailing and leading P/E ratios, PEG ratio, and P/B ratio. You should select at least two peer firms for your comparative analysis and provide the rationale for your peer selection.

2. Determine whether your selected company in section A is currently under or overvalued using fundamental measures of P/E ratios and fundamental measures of P/B ratios.

3. Determine whether your selected company in section A is currently under or overvalued compared to the sector index (sector P/E ratios can be retrieved from the Refinitiv workspace database), using both the trailing and leading P/E ratios. The objective here is to provide a comparative analysis between your firm’s and its industry’s P/Es, and discuss how these can be impacted by firm-specific and industry-specific factors.

4. Are there any inconsistencies among the above ratio analysis (Section B.1, B.2, and B.3)? Provide your investment recommendation of the selected company in section A based on the above relative valuation methods and justify your choice.

The whole Section B must be completed in the Word document. “The most recent date” refers to the date when your group retrieves the required data from the Refinitiv Workspace, which should be specified in your Word document.

Marking Scheme

The assignment is worth 25 marks and marks will be distributed as follows:

Section A1: Overview (4 marks)

Section A2: Pro-forma and DCF valuation (12 marks)

-Adjusted financial data (1 mark)

-Revenue analysis (2 marks)

-Forecast financial data (1 mark)

-FCFE, valuation (FCFE) and recommendation (2 marks)

-FCFF, valuation (FCFF) and recommendation (2 marks)

-Cost of equity (2 mark)

-WACC (2 mark)

Section B1: Relative valuation based on peer firms (4 marks)

Section B2: Relative valuation based on fundamental ratios (2 marks)

Section B3: Relative valuation based on sector index (2 marks)

Section B4: Reconciliation of conflicting ratios and recommendation (1 mark)

Total marks: 25 (Section A2 needs to be completed in the Excel file, the rest in the Word file)

ASSIGNMENT SUBMISSION

Each group needs to submit 1 Word file (with approximately 3,000 words) and 1 Excel file. 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 include a signed and dated assignment cover sheet in your word file, and 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).

Equity Valuation and Analysis (CORPFIN 7039)

DCF Valuation Instructions (Microsoft excel only)

Perform the following instructions on the 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 analyses should be performed on a separate spreadsheet sheet. See the following example of a 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 Refinitiv workspace database.

Your manager is a well-organised professional and wants your valuation to be a professional document. Before proceeding to the next step, your manager advises you to make the following modifications to improve the presentation of the data.

· Given your valuation year is 2024 you will need data for the years 2020-2024. Delete all other years of data by deleting the corresponding spreadsheet columns.

· 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 total current liabilities) are determined by way of a spreadsheet formula. This means that you will have to delete the corresponding data for each total and key in the cell equation that determines that amount (as an example, sum (A5:A10)).

· Remove the row “Share/Per Share – Basic” and all those rows below “Share/Per Share – Basic” from the income statement.

· Remove the row “Share/Per Share - Common” and all those rows below “Share/Per Share - Common” from the balance sheet.

· Remove the section “Debt related” between the row of “Total Current Assets” and the row of “Non-Current Assets” from the balance sheet.

2. Your manager has indicated in a 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:-

a. Provide ‘Pro-forma’ income statement (profit and loss) and balance sheets for the next 5 years following the year of your valuation ie 2025 – 2029.

b. Forecast revenues (sales) based on the regression methodology.

c. 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 analyses 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.

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

e. Tax expense should be forecast based on the past average effective tax rate.

f. Depreciation should be forecast based on the past average depreciation rate, where the depreciation rate_t = Depreciation_t / PPE_t-1.

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

h. Interest bearing debt (non-current portion only) should be forecast based on its past average percentage of paid-up capital (Share Capital).

i. Forecast the remaining items of the income statement and balance sheet as a % of operating revenues based on past association with revenues, or other methods that your team deems more appropriate (along with an explanatory note on the spreadsheet). Any item that has a zero balance for the most current financial year should not be forecast.

j. All sub-totals and totals should be determined as totals (using the sum function).

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

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

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 component separately. Each component should be determined by a 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).