Part 4 – Building Financial Models
It’s been some time since I published my last chat with Archie (R-CHI).
In the interim I have had numerous chats with him – invariably about technical matters such as: startup financial models, mapping and validating financial models, valuing commercial real estate to name a few.
I am currently in the process of teaching Archie how to build an effective, flexible financial model according to the FAST standard, from a detailed model specification in Word format.
It has been, and is, a fascinating experience!
As you may be aware if you have ever seen a Goalfix bespoke financial model, or attended one of our training courses, we follow a detailed series of sequential steps, as follows:
The sequence of the process is as follows:
Assimilation
- Reading and assimilating the business case of the project as contained in the model specification.
- An absolutely critical aspect of financial modelling. It requires an in-depth, comprehensive understanding of the business case and all the key model drivers
- Understanding the model objectives and the key outputs required.
Design
- Deciding on the time series of the model
- Creating a sheet template to ensure consistency in all sheets, depicting the time series chosen.
- Applying specific criteria, in the form of conditional modelling, to the sheet template in accordance with
- Goalfix modelling conventions
- Creating the input sheet (containing all assumptions) as detailed in the model specification and supported by appropriate research and investigation. Specific columns are stipulated as per our standard modelling conventions and commence with a generic detailed input sheet.
- Input information is grouped in appropriate categories.
- Conceptualising the model structure in terms of model “sheets”
- Deciding on the support schedules required in respect of the income statement and the balance sheet
Building the model
- Building the income statement schedules
- Building the balance sheet schedules
- Designing the Income Statement
- Building the Income Statement
- Designing the Cash Flow Statement – alternative cash flow layouts
- Building the Cash Flow Statement
- Designing the Balance Sheet
- Building the Balance Sheet – balancing the balance sheet – validating the model
Analysing the model results – creating an executive summary
- Discounted cash flows
- Calculating tax adjusted free cash flow
- Calculating key ratios and results – NPV, IRR, Ratios, pay back periods, breakeven analysis
- Carrying out sensitivity analysis on key assumptions
- Creating scenarios
- Building data tables based on sensitivity analysis
- Graphing key results as required
- Finalising the model
- Protecting the model
At this juncture, I have succeeded in the teaching Archie regarding the creation of the input sheet, time series and input categories.
It’s been an interesting learning curve for both of us.
And has caused me to reflect / compare Archie with an aspiring first-time modeller.
In particular, I have found that I need to more informative and specific about the model structure – the anatomy of a model, and stipulate the names and line-item details (row descriptions) relating to:
- the income statement support schedules,
- the balance sheet support schedules,
- the tax schedule
- the income statement, cash flow statement and the balance sheet.
The process is a mixture of excitement / happiness and frustration!
Of course, once we have crossed the first bridge, the model build, we will then embark on the analysis.
So, don’t hold your breath
My objective is to make myself redundant!!☺
Just joking!– my true objective is to become an expert in analysing the business case, giving detailed crystal-clear instructions regarding the model structure, input assumptions, support schedules, the three financial statements and the output (model results) and analysis of thereof.
And then to sit back and wait for a few minutes while Archie builds the model.
If you don’t have big dreams, you can’t make big dreams come true!!
Colin Human
Goalfix Financial Modellers
[email protected]
