Starting with a blank Excel spreadsheet we will define and create a structure which enables flexible forecast modeling.
- Understanding the structure of a model – inputs, workings, outputs
- Scope limitations – output requirements vs. input source
- Using group edit tools to quickly construct a robust, consistent and printable financial model
- Input sheets – creating underlying assumptions / forecasts
- Setting up date flexibility throughout the model – using EDATE and similar functions
Provided with the annual report, we add the financials by constructing the integrated historic financial statements suitable for flexible forecasting. If we forecast financials there are knock-on effects to other elements of the model. Are these inter-linkages understood and can they be consistently applied so that the integrity of the model is maintained? This session ensures these connections are understood.
Extracting the relevant historical financials by defining the key numbers, e.g.
- EBITDA and net income
- Operating cash flows and CFADS
- Debt, net debt and capital structure
- Other line items needed to calculate the key numbers, e.g. operating costs, interest, tax, etc.
Understanding key operating drivers for business and looking at examples of how to model these across a wide range of scenarios
- Revenues = Price x Volume. How is this modeled across different industries
- Operating costs – understanding fixed, variable and semi-variable costs and the concept of breakeven analysis
Building the key balance sheet items:
- Property, plant and equipment (PPE)
Checking the calculations using capex/deprecation ratio
- Working capital assumptions
- Trade receivables
- Operating cash
- Equity and retained income
Integrating/linking the financials
- Income statement to cash flows
- Income statement to balance sheet
- Cash flows to balance sheet
- Building consistent checks
We have created the perfect structure for projecting the income statement, balance sheet and cash flow numbers, combining Excel analytical tools with commercial knowledge, we will now create and apply forecast drivers to calculate the integrated forecasts.
- Step by step forecasting / integration – a modular approach to forecasting
Identifying and forecasting the value drivers, e.g.
- Sales growth, margins, capex / depreciation, effective tax rates, working capital days
- Applying the concept of “fade”
- Harnessing Excel’s forecasting tools
- Forecasting for consistency
- Charts as guides
- Integrating the forecasts in the financial statements
- Approaches to ensure the avoidance of circular references
- Beginning cash
- Cash from operations for debt repayment
- Dividends from cash flow statement
- Mandatory payments
- Using the revolver if the cash movement is negative (MIN function)
- Linking debt balances and revolving credit facility on the balance sheet
We are rarely content with a unitary outcome. What happens if a product fails; the US dollar strengthens; or the business opens a new division etc.? This session focuses on adding that required flexibility to the model.
- Base, Bear, Bull
- Data validation
- Visual basic tools to enable the efficient switching between different scenarios
- IF, Choose, Vlookup, Index or Offset?
Learn the tips and tricks to check your model and to correct it if there are errors.