Financial Modelling and Forecasting custom essay

[pewslideshow slidename=anim2]

The case study involves an analysis of Woolworths Limited (WOW), a company listed on the Australian Securities Exchange (ASX). The case study comprises 6 parts, each worth 5 marks. The idea of the case study is to first (in Part 1) set up the basic valuation model in Excel without worrying about the actual parameter values, and then (in Parts 2 – 5) to determine the best parameter values. Even though you will submit your EXCEL spreadsheet(s), all thirty available marks are based on the report. No marks are awarded for your spreadsheet. Please structure your case study report as per the guide below to maximize clarity for the marker.

1. The Basic Spreadsheet Model (5 marks)
a. Download the FinAnalysis data for Woolworths from UTS Online.
b. Use the Lab 9 solution spreadsheet (on UTS Online) as a template for your financial model. Be aware that FinAnalysis uses different terms for some items, so take care in mapping the Woolworths financial data into the year 0 spreadsheet values.
c. Follow what we did in Lab 9 to project the financial statements one year ahead (i.e., for 2013). Use cash as the plug. At this stage, just use the parameters from Lab 9 – don’t worry that these parameter values are wrong – you will calibrate these later. To emphasize, the share price you get from Part 1 will probably not be close to the actual share price but that is ok – you will fix this in parts 2-5.
d. Once you are satisfied that the one-year projection has worked, project the statements for years 2-5 ahead (i.e., 2014 – 2017).
e. Calcluate the projected free cash flows (again following Lab 9).
f. Calculate the Woolworths share price (again, at this stage just use the WACC and long-run FCF growth rate from Lab 9 – you will determine appropriate values for these later). Note: you will need to find out the number of Woolworths’ shares on issue.
g. Make sure your spreadsheet model changes the share price when you change the parameters. So, for example, if you change the sales growth rate or the WACC, you should get a new share price.

2. Approximate Spreadsheet Parameters (5 marks)
a. In sales-driven spreadsheet models, key items on the Income Statement and Balance Sheet are modelled as ratios of sales, i.e.:
• Current Assets / Sales
• Current Liabilities / Sales
• Net Fixed Assets / Sales
• Cost Of Goods Sold / Sales
In the lab 9 solution spreadsheet, these ratios are in cells B4 to B7. Determine appropriate forecasts of these ratios for Woolworths by plotting / examining their historical values and researching company releases.
b. There are other parameters that are not a ratio to sales:
• Depreciation
• Interest rate paid on debt
• Interest rate received on cash
• Tax rate
• Dividend payout ratio
In the lab 9 solution spreadsheet, these ratios are in cells B8 to B12. Determine appropriate values of these ratios for Woolworths by examining their historical values or using common sense approximations. Note, approximations are reasonable here – so, for instance, even if Woolworths doesn’t use a constant rate of depreciation, it is probably ok to assume one in your model.

3. Sales Growth Rate Forecast (5 marks)
The Sales growth forecast is the key parameter in the sales-driven spreadsheet valuation model (cell B3 in the Lab 9 Solution). You can use any information or modelling technique to generate your forecast, but should consider:
• The historical sales growth (which you can see in a plot of the revenues in the income statements over the last decade).
• Woolworths’ own forecast of its future sales.
• Any announcements from Woolworth’s concerning its operations.
• Market commentary on Woolworths and its competitors.

After completing parts 1 – 3, you can now generate the free cashflows for 2013 to 2017. Make sure you display these cashflows clearly on your report for the marker to see.

4. WACC & the Long-Run FCF Growth Rate (5 marks)
To value Woolworths from the free cashflows, you need to estimate the weighted average cost of capital (WACC) and the long-run free cashflow growth rate (see Benninga handout sections 3.4 and 3.5).
a. You can estimate the cost of equity using either the dividend growth model (assuming you can estimate the dividend growth rate g accurately from the historical dividend payouts) or the CAPM (assuming a constant-beta model fits well).
b. Use Woolworths credit rating (A- S&P/ A3 – Moodys) to determine its cost of debt from yields to maturity in the corporate bond market.
c. Read Benninga section 3.4 on the long-run free cashflow growth rate. You should use your best estimate / guess of the long-run sales growth of Woolworths for the long-run free cashflow growth rate. This is the sales growth rate we forecast will apply from 2018 onwards. Clearly this is difficult to know with any accuracy, although we might suppose that it will be equal to the historical industry sales growth rate. Put in your best guess here, and you will look at the sensitivity of the share price to your choice in part 5.

Now use your estimates of the WACC and the long-run FCF growth rate, along with your free cashflows for 2013 to 2017 from part 3, to calculate the Woolworths share price. Make sure you display this clearly on your report for the marker to see.

5. Sensitivity Analysis and Market Calibration (5 marks)
a. Use EXCEL’s data table to determine the sensitivity of the Woolworths share price to the four spreadsheet parameters from 2(a) above. Plot the sensitivity graphs (as in the Benninga handout Section 3.6 at the back of the class notes).
b. Use a two-dimensional EXCEL data table to determine the sensitivity of the Woolworths share price to the long-run FCF growth and the sales growth rate.
c. Calibrate your model to the current Woolworths share price by finding the combination (or combinations) of the long-run FCF growth and the sales growth rate that give a share price closest to the actual share price.

6. Scenario Analysis (5 marks)
a. Develop best- and worst-case scenarios for Woolworths’ sales growth based on a qualitative analysis of one or more of the following factors:
• Industry (e.g., market share) factors,
• Macro factors (e.g., GDP growth),
• Company-specific factors
For each scenario, list the values of the key spreadsheet variables (sales growth, WACC, etc.) that you selected to generate the share price. Justify your choices.
b. Develop best- and worst-case scenarios for Woolworths’ sales growth using a regression model for Woolworths’ sales. The explanatory variables could include GDP growth, Retail Sales, Consumption, etc. After collecting the data and fitting a satisfactory model, decide on which values of your explanatory variables correspond to the two scenarios (e.g., a recession would have negative GDP growth and low consumption). Use the sales growth forecasts from both scenarios to generate share prices from your spreadsheet model.

Note: Only do ONE of these options, not both.

Further Points

Your case study must be submitted as a report. All thirty available marks are based on the report only. Your report must be self-contained. An executive summary is crucial – this is often the only part of a report that management might read. To help prepare your report you may find it useful to read the Guide to Writing Assignments available at: .

Your attention is drawn to the following passage in the Guide:

“Writing is a product which demonstrates the quality of your learning.

In all these circumstances, your writing is the product which defines how much and how well you understand and can put your case, and which will constitute the substantial basis for your assessment grade.”

Marks are also awarded based on the quality of your report, which includes items such as the executive summary, table of contents, grammar and spelling.

It is also suggested that you refer to the readings from Financial Modelling 3rd Edition by Simon Benninga that are included in your Subject Workbook to assist you complete this case study. Presentation is an important aspect of your report and it should include carefully prepared tables and graphs. A concise report is essential and a 2,000 word limit applies to the body of the report. Appendices, figures, and the table of contents are not included in the 2,000 word limit. There will be no penalty if it is more than 2,000 words, but only the first 2,000 words will be read.

Your case study report should be placed, by the due date, in the appropriate assignment box located at the office of the School of Finance and Economics, Level 3, Block D, Markets Campus.

Your report and EXCEL spreadsheet are also required to be electronically submitted using the UTSOnline ‘Digital Drop Box’ and we will demonstrate its use in lectures. Structure your spreadsheet to allow third parties to easily understand it. Do not email your spreadsheet as it will not be opened or examined. We may inspect your spreadsheet if we have any concerns with your report.

Place an order of a custom essay for this assignment with us now. You are guaranteed; a custom premium paper being delivered within its deadline, personalized customer support and communication with your writer through out the order preparation period.

[pewslideshow slidename=anim3]

Still stressed from student homework?
Get quality assistance from academic writers!