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.

Videos – 38 videos each of 10-15 minutes long to allow flexible learning in YOUR timeframe

Exercises and models – the completed forecast model is downloadable in 8 different stages of completion so you can see section by section, with the tutor, how the model builds.

Solutions – solution files plus video debrief from your expert tutor

Learning management – access to a dashboard, summarising your progress and providing learning access

Certificate of Completion – CCT is an accredited learning provider for the Chartered Institute of Securities and Investment.  Upon completion you can request a completion certificate for your learning / CPD records

Email access – to your tutor

This program is for delegates who use, review or build financial models but are perhaps self-taught. The only pre-requisites are the Excel Skills for modelling if new to Excel, and /or the Introduction to Accounting and Analysis which will ensure you know the links between financial statements and understand the concepts of working capital, depreciation, EBITDA etc and the flow of information between the P&L, Cash Flow Statement and Balance Sheet. If unsure, get in touch and we will help you decide.  

Recommended: our e-learning courses: Excel Skills for Modelling; Introduction to Accounting and Analysis

The videos stream real-time, so you will be required access to a good data network or broadband / Wi-Fi network.  Videos compatible on Android and iOS Excel 2010 onwards for Exercises compatibility.