In this article we’ll cover:
1. How to review an updated financial model
2. How to decide whether a Financial Close model can be updated
3. The implications of overtyping code
4. Some ideas to consider if you have to work with an updated FC model:
Basic Logic checks for amended code
High Level Output checks
Detailed Output Review Ideas
1. How to review an updated financial model
In these uncertain times, it’s crucial to have clarity on your projects’ financial robustness.
Many of you will be updating or running sensitivities on your financial models/business plans to quantify the impacts of COVID-19. These forecasts are needed by project stakeholders to make decisions, whether they are lenders, shareholders or public bodies.
Although many financial model articles focus on how to build models (see here for a good summary approach prepared by the ICAEW), this article gives guidance on how to review a financial model that has been updated or sensitised. It is aimed at SPV managers, shareholders, bank agency teams and anyone who needs to review a model.
In this article we include links to free spreadsheet analysis tools that will help your review.
2. How to decide whether a Financial Close model can be updated
During the COVID-19 pandemic we have had many conversations with sponsors/lenders/business owners asking us ‘What is the best approach to review models that have been updated or sensitised?’ We always respond with the same question:
“Was the financial model originally designed to be updated/sensitised in this way?”
If the answer to this question is “No”, it is risky to run analysis and use the outputs of these models to make decisions, as you are trying to use a model for a purpose not envisaged by the original model author.
It doesn’t matter how accurate your inputs are, your outputs will be unreliable.
For instance, if you were using a transaction or Financial Close Model (collectively “FC Model” going forwards) that was not designed to:
be updated with actual performance,
have one-off costs added, or
have macroeconomics changed,
then you are risking the model’s forecast having errors as the model was not designed to be updated in this way.
It would be like using a Formula 1 car to drive in the desert; with modification, the Formula 1 car would eventually get you where you need to go, but it would be less work and less risky to use a vehicle specifically designed for that task.
For some, answering this question can avoid both unnecessary work and heartache.
3. Implications of overtyping code
Although it is possible to update an FC Model to do the above, the chances are that you will be overtyping or “tweaking” code to make things work.
Amendments such as these may inadvertently break the model’s logic and therefore outputs will be unreliable. Also, as an important aside, if the FC Model is broken, it may not tell you it’s broken.
So, before you start analysing a model amended in this way, you need to ask yourself whether the model was actually designed for the update/analysis you are intending to do.
If the answer is:
“Yes! The model was designed with this in mind” – then great, analyse away.
“NO! The model was not designed with this in mind, but I have time/budget to do something about it” – you should consider having a modelling expert undertake the exercise or, ideally, have a dedicated operational model built to reflect the performance of the asset (I will detail more about operational models in a separate post).
“NO! The model was not designed with this in mind, and I have NO time/budget to do anything about it” – this is not ideal, and from what we have seen over the years, this happens a lot.
To help with the review of any model, I have noted below some key ideas.
4. Some ideas to consider if you have to work with an updated FC model:
When reviewing an updated FC Model, you should consider the below points, broadly in this order:
Basic Logic checks for amended code
Before looking at outputs, you have to consider the risks involved with the updated code and whether it is working correctly.
Who has updated the FC Model? – Was it a financial modelling expert/ firm? If yes, the outputs should be reasonable. If not, you will need to ask more questions of the model and be more critical with your review.
How many times has the FC Model code changed? Can the outputs be trusted? – Some FC Models are updated for years and years on end, even though they were not designed to. Ask yourself honestly whether you feel the model is still fit for purpose and reliable. Does it need to be reviewed by a professional modelling firm?
Simple output checks – review the model for simple things that might highlight a fundamental error, top ones to consider:
Does the balance sheet balance?
Does cash go overdrawn?
Are debt covenants being met? (do not always rely on model checks to tell you!)
Are debts fully paid?
If the project has a finite life, does the balance sheet wind to nil “naturally”? (I.e. the balances unwind to the cashflow or P&L as opposed to just being multiplied by zero or timeline run out).
What has changed? – Ask yourself “what has changed?”, “what were they trying to do?” and “what are the risks of these changes being implemented incorrectly?” There are different approaches to checking what has been changed.
Software Solution – if you have not seen this model before, I suggest you do the following:
Consistency Check – is code throughout the model consistent from left to right? One of financial modelling’s golden rules is to ensure that code is consistent from left to right (i.e. you write a formula, and copy it across the worksheet so it is consistent). Problems arise when code is made inconsistent (i.e. someone adds “+500” to the end of a formula.) When I see this in a model, I instantly lose trust in its integrity. For instance, how do you know they haven’t changed something else? Using a consistency tool will highlight the code inconsistencies. Please follow this link to a free Amberside spreadsheet consistency check tool add-in.
Model Compare – if you are reviewing a model that you have already seen and were happy with, I would recommend using a spreadsheet compare software that will tell you what has changed. This will give you a guide to what has changed and, crucially, what has changed that shouldn’t have changed. There are a number of spreadsheet compare software solutions available.
Output Solution – if you have the benefit of the FC Model having been previously updated and deemed reliable, but you do not have access to spreadsheet compare software, you will have to focus on reviewing the outputs. I suggest you should take this “Sense Checking” mindset on board when reviewing output changes:
What inputs have changed?
What would I expect the impact on outputs to be?
What has actually changed?
This way of thinking and mindset is a common trait in excellent analysts and should be undertaken with every model change.
One way to look at what has changed quickly is to use delta sheet analysis on key outputs. Delta sheets compare the live model outputs to a previous static base and show you the output differences (i.e. compare the pre- and post-updated models’ financial statements). This is a powerful tool, and has been a game-changer for a number of our clients. Please follow this link for a free Amberside delta sheet tool add-in.
If you are worried about any of the above points, I would suggest sending the model back to the modeller, setting out your concerns.
High Level Output checks
Once you are satisfied that the model logic appears to be calculating as expected and the model changes made are sensible, you should consider the following high-level output checks before diving into the detail:
Management Accounts match model? – Many models are regularly updated with the latest management accounts. You should consider the following points when reviewing the management accounts to the model:
On the model’s output sheets, do the values in the last actuals period match the management accounts for P&L, cashflow and balance sheet?
Of the updated values, how many of these values are actually calculated by the model, as opposed to being “typed in”? Ideally, the total cash balance per the cashflow statement, should flow to the cash balance in the balance sheet (same for the P&L and retained earnings).
Many projects rely on tax losses and tax depreciation – when was the model’s tax actuals last updated? Do they match the last tax computation?
Have “other” actuals been included? – Has the model been updated for actual base interest rates, inflation and other macroeconomics?
Accounting and Tax logic up to date? – When was the model last updated for the latest accounting regimes and tax legislation? Although tax and accounting rules change relatively infrequently, when they do change, they can have a material impact on a project’s financial performance. Given this, the question should be asked of the modeller regarding any implications (i.e. for tax, corporation tax rates or corporate interest restriction rules, and for accounting, amendments to lease accounting).
Don’t forget about the P&L! – Many reviewers will focus on the cashflow statement, which makes sense, as cash is king, BUT(!) you should not neglect the P&L as this drives:
taxation calculations, which in turn impacts cash.
dividend calculations as many jurisdictions/regimes limit dividends to the minimum of cash AND P&L in a period.
Have historic results stayed the same? – When updating financial models for current period results, historic results should not change. After an update, it is worth checking prior period statements and covenants to make sure that they haven’t changed.
Are the forecast macroeconomic assumptions reasonable? – Are the assumptions used for tax, inflation and interest receivable reasonable?
Again, if you are concerned with any of the above points, I would suggest sending the model back to the modeller.
Detailed Output Review Ideas
At this point, if you are broadly happy that the logic and the commercial basis of the forecast appear sensible, you can now step back and review the values being forecasted by the updated model.
Do the summary sheet/key metrics make sense? – Most financial models will have a summary sheet/dashboard that will show the project’s key metrics. Analyse these closer to see if they make commercial sense given what you know has changed in the model. When compared to previous versions of the model, do the movements make sense?
Do the Financial Statements/Outputs forecasts make sense? – Given the high-level position of the project is sensible, you will now be in a position to review the outputs in more detail (this tends to be financial statements). I would suggest reviewing each line item of the financial statements individually to see if they follow the trend you expect. You may want to consider using graphs or sparklines to help the trend review as it’s often easier to look at pictures, than run your eye across rows of numbers (follow this link for a useful guide on using Sparklines – the graph within a cell! They can very quickly give a high level view of a trend.)
Does the first forecast period make sense? – When reviewing updated models, I would suggest reviewing closely the first forecast period as this is where you are likely to encounter logic or forecast issues (i.e. where the actuals and model forecast period meet). Common issues include large working capital swings, odd tax movements, or double insurance payments.
Do the forecasts line up with the actuals? – If the updated model that you are reviewing has historic actuals, how do they compare with the forecasts? Do they follow the same trend? Do they go against the trend?
Hopefully the above has been useful and given you points to consider when reviewing a financial model. For any queries or to discuss your modelling requirements, drop me a line: alan.samuels@amberside.uk