25705 Financial Modelling and Forecasting
Autumn Semester 2015
Case Study
Contributes 30% to your total grade Due by 9pm Monday 1 June 2015
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 Webjet Limited (stock code WEB). The purpose of the case study is to produce monthly industry sales forecasts for management’s sales planning purposes, and to build a financial model of Webjet for investment recommendation purposes.
A class presentation of your work-to-date contributes 5 marks and the report contributes the remaining 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 of Webjet depends heavily on your assumptions and since each group will have 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. Forecast Retail Industry Turnover (5 marks)
i. Visit the ABS website and download data for the publication ‘8501.0 – Retail Trade, Australia; TABLE 1. Retail Turnover, By Industry Group’. In worksheet “Data1”, copy columns A and F to a new worksheet and name the new worksheet “OtherRetail.xls”.
ii. Use the data up to June 2014 only to produce seasonally adjusted Turnover figures. Check the textbook p131 for monthly seasonal adjustment.
iii. Use Holt’s method and Double Moving Average to produce Turnover forecasts. Calculate the MSE for the estimation period from the start of the sample to December 2012 and the holdout period from January 2013 to January 2015, for each forecasting model. Use “Solver” in Excel to determine the optimal parameters for Holt’s method. Choose DMA for K=3 and K=6. Which model do you prefer? Why?
iv. Produce out-of-sample forecasts of turnover (in original units) for the next five months (i.e., Feb to Jun 2015) using your preferred forecasting model.
2. Generate Webjet’s Sales Forecasts (4 marks)
i. Download historical financial data of Webjet (stock code WEB, 1998 - 2014) from UTS Library’s DatAnalysis premium database.
ii. Select up to 3 explanatory variables and estimate the corresponding linear regression model to forecast Webjet’s annual sales. 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 Webjet’s annual sales for 2015, 2016 and 2017 using the better technique from (ii).
3. Estimate Financial Model Parameters (5 marks)
i. In sales-driven spreadsheet models, many items on the Income Statement and Balance Sheet are a proportion of sales, including (but not limited to):
• Receivables
• Accounts Payable
Determine appropriate forecasts of all relevant sales-related ratios (not just the two above examples) for Webjet. 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 Webjet 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.
ii. There are other parameters that are not a ratio of sales, such as:
• Depreciation
• Interest expense
• Dividends paid
Determine appropriate forecasts for non-sales-related ratios (not just the three above examples) for Webjet by examining the historical values or making reasonable assumptions.
4. Estimate Discount Rate and Perpetual Growth Rate (3 marks) To value Webjet 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 Webjet’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 Webjet? 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 Webjet. Be aware that DatAnalysis sometimes uses different terminology than you might be used to, so be careful mapping Webjet’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, Sales 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 Webjet’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 Webjet. You will need to find out the number of shares on issue for Webjet. 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 Webjet’s share price to your chosen ratio. Plot the sensitivity graph (see Benninga Section 3.6) and discuss the chart.
ii. Create a two-dimensional EXCEL data table to demonstrate the sensitivity of Webjet’s share price to the long-run FCF growth rate and the WACC.
7. Justify the Valuation (1 mark)
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.
Further Information
Your case study must be submitted as a report. All twenty five marks are based on the report only. Your report must be self-contained. An executive summary is crucial. 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 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 textbook Financial Modelling 3rd Edition by Simon Benninga that is available online through the UTS Library’s website 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 an 8-page limit (double space with 12 size font) applies to the body of the report. Appendices, figures, and the table of contents are not included in the page limit. There will be no penalty if it is more than 8 pages, but only the first 8 pages will be marked.
Your case study report should be placed, by the due date, in the appropriate assignment box located in Building 8 (details to be provided later). Your report and EXCEL file 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.
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.