25705 Financial Modelling and Forecasting
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 Flight Centre Travel Group
Limited (stock code FLT). 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 Flight Centre for investment recommendation purposes.
1. Forecast Short-Term 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 p176 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 June
2014, for each forecasting model. Use “Solver” in Excel to determine the
optimal parameters for Holt’s method. Choose DMA between K=6 and K=12.
Which model do you prefer? Why?
iv. Produce out-of-sample forecasts of turnover (in original units) for the next six
months (i.e., July 2014 to December 2014) using your preferred forecasting
2. Generate Flight Centre’s Sales Forecasts (4 marks)
i. Download historical financial data of Flight Centre (stock code FLT, 1996 –
2013) from UTS Library’s DatAnalysis premium database.
ii. Select up to 3 explanatory variables and estimate the corresponding linear
regression model to forecast Flight Centre’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 Flight Centre’s annual sales for 2014, 2015 and 2016
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):
Cost of Goods Sold
Determine appropriate forecasts of all relevant sales-related ratios (not just the
three above examples) for Flight Centre. 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 Flight Centre 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:
Determine appropriate forecasts for non-sales-related ratios (not just the three
above examples) for Flight Centre by examining the historical values or
making reasonable assumptions.
4. Estimate Discount Rate and Perpetual Growth Rate (3 marks)
To value Flight Centre 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 Flight Centre’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 Flight Centre? 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 Flight Centre. Be aware that DatAnalysis
sometimes uses different terminology than you might be used to, so be careful
mapping Flight Centre’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 Short-term Debt and Accounts Payable.
b) Include the three separate Income Statement items, Cost of Goods Sold,
Employee Expenses, and Operating Lease Expense. You may also insert any
other items in the model you feel are necessary to make your financials model
correspond with Flight Centre’s financial statements.
ii. Follow the techniques you learnt in Lab 9 to develop the pro forma financial
statements for 2014, 2015 and 2016. 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 2014 to 2016 to calculate a share price for Flight
Centre. You will need to find out the number of shares on issue for Flight
Centre. Make sure you state this figure in your report.
v. How does your forecast profit for the 2014 financial year compare with
management’s profit figure stated in the media release announced on
25/07/14? 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
Flight Centre’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
Flight Centre’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.
YOU CAN ALSO PLACE OTHER SIMILAR ORDERS ON OUR WEBSITE AND GET AMAZING DISCOUNTS!!!
Our Service Charter
Excellent Quality / 100% Plagiarism-FreeWe employ a number of measures to ensure top quality essays. The papers go through a system of quality control prior to delivery. We run plagiarism checks on each paper to ensure that they will be 100% plagiarism-free. So, only clean copies hit customers’ emails. We also never resell the papers completed by our writers. So, once it is checked using a plagiarism checker, the paper will be unique. Speaking of the academic writing standards, we will stick to the assignment brief given by the customer and assign the perfect writer. By saying “the perfect writer” we mean the one having an academic degree in the customer’s study field and positive feedback from other customers.
Free RevisionsWe keep the quality bar of all papers high. But in case you need some extra brilliance to the paper, here’s what to do. First of all, you can choose a top writer. It means that we will assign an expert with a degree in your subject. And secondly, you can rely on our editing services. Our editors will revise your papers, checking whether or not they comply with high standards of academic writing. In addition, editing entails adjusting content if it’s off the topic, adding more sources, refining the language style, and making sure the referencing style is followed.
Confidentiality / 100% No DisclosureWe make sure that clients’ personal data remains confidential and is not exploited for any purposes beyond those related to our services. We only ask you to provide us with the information that is required to produce the paper according to your writing needs. Please note that the payment info is protected as well. Feel free to refer to the support team for more information about our payment methods. The fact that you used our service is kept secret due to the advanced security standards. So, you can be sure that no one will find out that you got a paper from our writing service.
Money Back GuaranteeIf the writer doesn’t address all the questions on your assignment brief or the delivered paper appears to be off the topic, you can ask for a refund. Or, if it is applicable, you can opt in for free revision within 14-30 days, depending on your paper’s length. The revision or refund request should be sent within 14 days after delivery. The customer gets 100% money-back in case they haven't downloaded the paper. All approved refunds will be returned to the customer’s credit card or Bonus Balance in a form of store credit. Take a note that we will send an extra compensation if the customers goes with a store credit.
24/7 Customer SupportWe have a support team working 24/7 ready to give your issue concerning the order their immediate attention. If you have any questions about the ordering process, communication with the writer, payment options, feel free to join live chat. Be sure to get a fast response. They can also give you the exact price quote, taking into account the timing, desired academic level of the paper, and the number of pages.