Financial Modelling and Forecasting

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.