Reference no: EM132888698

Section A

You are required to perform a valuation of Woolworths Group Limited (ASX Issuer code: WOW)employing thediscounted cash flow methodology to determine whether the share price of WOW on the 1st 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 andfree cash flows to the firm. Before performing the valuation, you are required to conduct preliminary research.

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

1. Provide an overview of the current operations of WOW and the industry sector to which WOW belongs. This should include a discussion of the type of goods or services that are produced by WOW, its recent performance and the key challenges faced by it and the industry sector to which it belongs. For comparison, compare the performance of WOW 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 WOW at the valuation date. This step must be completed in Word document only.

Section B
i) 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 15th, 2021) 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.

• BHP Group Limited (ASX Issuer code: BHP): sum of each group member's student id ending in ‘0', ‘1' or ‘2'
• Telstra Corporation Limited: (ASX Issuer code: TLS): sum of each group member's student id ending in ‘3', ‘4', ‘5', or ‘6'
• CSL Limited: (ASX Issuer code: CSL): sum of each group member's student id ending in ‘7', ‘8' or ‘9'

ii) Examine how the P/Es of the firms that you have analysed in i)compare to the P/E of the sector index. In addition, calculate the one-year leading P/E for your firms and compare it with the one-year leading P/E based on analyst estimates. The objective here is toprovide a comparative analysis between your firms' and industry's P/Es and discuss how these can be impacted by firm-specific and industry-specific factors.The data for the analyst estimates can be retrieved from the Refinitiv Eikon database.

Presentation
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 (selectDatabases by subject/ Business/ DatAnalysis Premium from Morningstar/WOW/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 onlyremove 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 taxesusing 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 ie2017 - 2021if valuation year is 2016and years 2018 - 2022if year of valuation is 2017 and so on.
b. Forecast revenues (sales) based on a sound 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 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.
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 ratet = Depreciationt / PPEt-1.
g. Interest expense should be forecast based on the past average interest rate, where the interest ratet = Finance costst / Interest- bearing debtt-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 allother 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.
j. All sub-totals and totals should be determined as totals.
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 on following page).
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 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.

Attachment:- Equity Valuation and Analysis.rar