Financial models are critical to the operation of a company. They determine success, highlight business issues and target the company moving forward.
Financial models are well-structured spreadsheets that detail historic performance and future financial forecasts. They are used to determine the value of a company or project, source investment and/or manage the operations of the company. The main “output” of financial models are financial statements: Profit and Loss Account, Cash Flows and Balance Sheet. From these, one can determine the financial health and a number of other metrics around investor returns and internal decision making. There are a number of steps to run through to produce a model.
Planning – a model is generally structured with inputs (assumptions), workings and the output (financial statements, analysis). Planning upfront can avoid delays and having to make tie consuming changes or worse still, start over. It is important to identify what is the purpose of the model, who is the audience and the commercial context? – is it to validate spend on a CapEx item? Is it to source finance from a third party? The purpose will define the output.
What is the outlook? Generally, the further that the model looks out (2 + years), then it should include some sensitivity analysis where various assumptions can be toggled to see the impact on the final output. This is important as it will also give you an understanding of which assumptions are the most elastic i.e. have the biggest impact on the final results.
Who will use the model? The model needs to be clear to the intended audience whether this is sophisticated investors or a targeting a sales team.
Structure – The Almax models all follow the same broad structure which has been tried and tested
Cover tab– this includes who owns the model, who signed it off, version control and a general introduction describing the general use of the model. It also contains industry standard disclaimers.
Assumptions tab – This is the main input page of the model. This needs to be clear and it is worth colour coding cells based on whether they are dynamic (formula calculated) or hard coded. This tab should control the whole workings of the model and thus the results. It is also important to note the rationale or source of the assumption as these will be scrutinised.
Calculations tab – This is the engine of the model and generally will work over a set time period and produce the scenarios driven by the assumption inputs. This should largely be locked down to the audience as many changes can have knock-on effects on the validity of the whole model.
The output tab – The output tabs are the tabs that operators of the model will use most frequently as this details the results of the model which may what is presented in reports and what decisions are made on. This will generally be a financial statement of some sort with various key metrics.
The Almax top 10 actionable modelling tips
- Model formatting – A clear and well-formatted model is much easier to analyse and interpret. Therefore, we recommend colouring cells as quick reference so different colours for inputs, formulas or hard-coded cells.
- Be consistent – it is important to have the model in a consistent currency and format (decimal points, scales) as this will avoid confusion and will minimise mistakes.
- Do no embed hardcoded numbers in formulas – this can cause issues when handing over a modelling or updating it. It is far better to have formulas working of cell inputs.
- Don’t Hide Sheets or Rows – this can cause issues with interpretation, updating the model. Within longer spreadsheet, “group” rows/columns rather than “hide” them.
- Simple wins over – minimising the number of tabs is much easier to follow than many tabs with various segments of data. This enables much quicker referencing and minimises chances of error.
- Make it highly auditable – Errors happen especially if you have been working on a model for a long time, although building them simple and consistent does mitigate this, it is best practice to build integrity checks to ensure that these are not missed.
- Understand the commercial context – A financial model should incorporate many elements of the business from sales to operations and so these teams should be engaged to ensure the model has the correct commercial context and the correct analysis. One should never create a model in a silo.
- Don’t use templates – Templates only get you halfway there, a model should be started from a simple core functionality and then built up bespoke to the business operation and those revenue and cost drivers.
- Communicate a message – Presentation is very import and a model should have a clear flow and consistency. A model should include a visual element showing powerful charts to underpin the analysis.
- Build in flexibility – Business situations change and the best financial model is one where changes can be incorporated in a straightforward manner without a great deal of re-work.
Almax Partners have successfully supported our clients’ objectives through bespoke financial modelling. This includes investment, internal business cases and tenders across sectors. If you have modelling requirements that you would like to discuss, please contact us.