Financial Modelling and Forecasting
Case Study
This case study gives you the opportunity to demonstrate your understanding
of the two fundamental parts of this subject – forecasting and financial modelling. To
demonstrate the practical nature and real-world applicability of your learning, the case
study requires you to conduct a financial analysis of ASX Limited (stock code ASX).
The purpose of the case study is to produce monthly industry Revenue forecasts for
management’s Revenue planning purposes, and to build a financial model of ASX for
investment recommendation purposes. It is suggested that you refer to the textbook
Financial Modelling 3rd Edition by Simon Benninga that is available online to assist
you complete this case study.
A 10-minute class presentation of your work-to-date contributes 5 marks and
the report contributes 25 marks. You need to submit the EXCEL file that contains the
numerical analysis. It will be used to verify the numerical procedures. Your valuation
depends heavily on your assumptions. Since each group has different assumptions
your mark is based, in part, on the quality of your analysis, how well you support your
assumptions/methods, and on the professionalism of your report.
1. Business summary and growth strategy (3 marks)
? Main business components and sources of revenue
? Business performance in recent years
? Growth strategy for the next 5 years
? Potential risk factors in future growth
2
2. Generate ASX’s Revenue Forecasts (3 marks)
i. Download historical financial data of ASX (stock code ASX, 1997 – 2014)
from UTS Library’s DatAnalysis premium database. While the data are up to
2014, the annual report for 2014-15 is expected to be release in August 2015.
ii. Select up to 3 explanatory variables and estimate the corresponding linear
regression model to forecast ASX’s annual Revenue. Ensure that your
regression model satisfies the underlying assumptions. Choose an alternative
method (not based on regression) to forecast, e.g. DMA, SES, or Holt’s. Use
MSE to compare in-sample forecasting performance.
iii. Generate forecasts of ASX’s annual Revenue for 2015, 2016 and 2017 using
the better technique from (ii).
3. Estimate Financial Model Parameters (4 marks)
i. In Revenue-driven spreadsheet models, many items on the Income Statement
and Balance Sheet are a proportion of Revenue, including (but not limited to):
? Receivables
? Accounts Payable
Determine appropriate forecasts of all relevant Revenue-related ratios (not just
the two above examples) for ASX. One method is to plot and analyse the
historical values to obtain forecasts for the next three years. Alternatively,
companies sometimes disclose their own management’s forecasts of financial
ratios. Therefore, if ASX provides forecasts then you may use these forecasts
in your model. Note: as explained in Question 5 part (i), your financial model
must include some additional line items. Therefore, be sure to also develop
forecasts for these new line items.
3
ii. There are other parameters that are not a ratio of Revenue, such as:
? Depreciation
? Interest expense
? Dividends paid
Determine appropriate forecasts for non-Revenue-related ratios (not just the
three above examples) for ASX by examining the historical values or making
reasonable assumptions.
4. Estimate Discount Rate and Perpetual Growth Rate (4 marks)
To value ASX from the free cash flows, you need to estimate the weighted
average cost of capital (WACC) and the long-run free cash flow growth rate
(see Benninga Sections 3.4 and 3.5).
i. You will first need to estimate the cost of equity. If you use the CAPM you
must state the values and the source for each variable. Beta may be obtained
from a reliable source, or you may estimate your own value. Similarly, explain
how you calculate the cost of debt.
ii. Estimate ASX’s WACC.
iii. Read Benninga section 3.4 on the long-run free cash flow growth rate. What is
your estimate of this figure for ASX? You must explain how you obtained
your estimate. You will study the sensitivity of the share price to your choice
of this variable in Question 6.
5. Construct the Financial Model (5 marks)
i. Use the Lab 9 solution spreadsheet as a starting point to build a more
advanced financial model of ASX. Be aware that DatAnalysis sometimes uses
4
different terminology than you might be used to, so be careful mapping ASX’s
accounting data into the corresponding year 0 spreadsheet values. You must
make the following two changes to the template. a) Decompose Current
Liabilities into Accounts Payable and Provisions. b) Include the three separate
Income Statement items, Revenue and Marketing Expenses, Staff and
Employee Expenses, and Other Operating Expenses. You may also insert any
other items in the model you feel are necessary to make your financial model
correspond with ASX’s financial statements.
ii. Follow the techniques you learnt in Lab 9 to develop the pro forma financial
statements for 2015, 2016 and 2017. Explain your plug variable. Include the
current and pro forma financial statements in your report.
iii. Calculate the projected free cash flows (again, following Lab 9).
iv. Use your estimates of the WACC, the long-run FCF growth rate, with your
forecasted free cash flows for 2015 to 2017 to calculate a share price for ASX.
You will need to find out the number of shares on issue for ASX. Make sure
you state this figure in your report.
v. How does your forecast profit for the 2015 financial year compare with
management’s profit figure stated in the half-year results announced on
19/02/2015? What changes do you need to make to your assumptions?
6. Perform Sensitivity Analysis and Market Calibration (2 marks)
i. Identify one variable with high forecasting risk and perform a sensitivity
analysis of that variable. Use EXCEL’s data table to indicate the sensitivity of
ASX’s share price to your chosen ratio. Plot the sensitivity graph (see
Benninga Section 3.6) and discuss the chart.
5
ii. Create a two-dimensional EXCEL data table to demonstrate the sensitivity of
ASX’s share price to the long-run FCF growth rate and the WACC.
7. Justify the Valuation (2 marks)
How does your valuation compare to the market price? Include a one-year
share price history chart and explain the reason for any large changes.
Quality of Writing (2 marks)
? The report should have a clear structure that addresses the above issues.
? Paragraphs should be clearly connected and coherent. Each paragraph should
start with a topic sentence. The sentences flow logically from point to point.
? Written expressions should be clear, complete, and grammatically correct.
? All tables and graphs should be referenced and discussed in the text of the
report. Unreferenced tables and graphs will not be considered.
? Sources of information should be fully referenced in the text with details
provided in the reference list.
Report Format
? The report should have a cover page containing
o Subject number and name
o Report title
o Team members: name, ID, and UTS email
? The all-inclusive page limit is 15, including the cover, text, tables, graphs, and
the reference list. To comply with the page limit, you need to carefully
6
structure the report and the paragraphs and carefully design the tables and
graphs. Any materials beyond the page limit will not be considered.
? All text should be double space with 12 size font.
? To help prepare your report you may find it useful to read the Guide to Writing
Assignments available at: http://www.uts.edu.au/sites/default/files/businesswriting-
guide-2014.pdf
Your report should be placed by the due date in the assignment box located in
Building 8 (details to be provided later). Your report and EXCEL file are to be
electronically submitted using the UTSOnline ‘Digital Drop Box’. Structure your
spreadsheet to allow the lecturer 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.
Penalties
Case reports with more than 5 team members attract a penalty. For each
person over the limit, 20% of the full mark is deducted. For example, case reports
with 6 members will be marked out of 24 instead of 30.
Case reports deposited after the due date attract a penalty. For each business
day, or part of a business day the case study is late, 20% of the full mark is deducted.
For example, case reports received 2 business days after the due date will be marked
out of 18 instead of 30.