1. Introduction and Golden Rules of Model design – Setting the scene and what the model will look like.

2. Recasting the accounts – A review of how financial statements might need to be re-cast to make them usable in a model. A downloadable exercise will use Excel tools and functions such as Data Validation and SUMIF are covered.

3. Setting up the model – custom styles formats and flexible dates structure – we will show you how to use a style template for consistency of formats and why that’s important.  Also Excel’s date functions will add some flexibility. The F5 special is revealed as a great audit and formatting tool! How and where to use Named Ranges

4. Integrating historic data – historic financial data is linked together: the P&L linked to the cash flow and the balance sheet, similarly for cash flows being integrated into the cash balance.  If the historic number reconcile, then our model forecasting becomes safer! Excel efficiency is covered all the way.

5. Forecasting Operations and quality control – we start off the forecasting looking at Revenue and EBITDA. After integrating the forecast numbers we check the model still balances and build in a balance check.  We cover Excel’s build in series tools, but also look at building a flexible trend forecast formula.

6. Working capital modelling – a key source or use of liquidity…. Working capital modelling has become every more a focus during and coming out of COVID.  It’s easy to get the signs confused, so we look at a clear, methodical process for WC modelling.

7. Fixed Assets – we use a structure ‘corkscrew’ design technique to ensure we don’t get lost in formulae.  It’s simple, clear and transparent to all users. You’ll see why.

8. Debt Switches, interest and the rest – we look at SUMIF to consolidate some debt information and build in scenario switches (Data Validation and simple VBA switches) so we cangive the user some controlled ability to flex the model. Data retrieval formulae such as CHOOSE, INDEX/MATCH, and the LOOKUP family are explained.

9. DuPont / ratio analysis  – for a financial sense check we what DuPont analysis is and how useful it can be.  

10. DCF and Analysis – Goal Seek, Data Tables – we finish off with a DCF valuation based on forecast free cash flows and – moreover – look generally at how you can use Excel’s Data Tables (sensitivity analysis) to summarise the impact of changing inputs.