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.
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 Flight Centre 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 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). 2
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
ï‚· Accounts Payable
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:
ï‚· Interest expense
ï‚· Dividends paid
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.