When you make a change to a model’s inputs or workings, it can have unintended consequences. At Operis, we teach our analysts a quick and easy way to sense-check the differences between two modelled scenarios (often a “before change” scenario and an “after change” scenario). We refer to this technique as “Cashflow Deltas”, and as a diagnostic tool, it doesn’t require digging into complicated formulae or checking large sections of workings. All you need is an understanding of what impact your change should have had on the project’s cashflow.
Setting up Cashflow Deltas
Before making any changes to your model, make two copies of the worksheet containing the cashflow. For the purposes of these instructions, I will assume that this worksheet is called “Cashflow”, and I will also refer to it as the live cashflow.
Make the first copy and set it to pasted values so it is fixed at the current case. Handy shortcuts to know for fixing the values are:
- CTRL+A – to select the whole area (sometimes you need to press this a few times to capture the entire worksheet depending on how it has been set up);
- CTRL+C – to copy the contents of the highlighted area; and
- ALT E S V – to paste what has been copied as hard-coded values.
Rename this sheet to something helpful, such as “Cashflow Pasted.”
Make the second copy, select the formula cells and change these to equal the corresponding cells of the live cashflow less the pasted one. Handy shortcuts to know for selecting the formula cells are:
- CTRL+A – to select the whole area; and
- F5 ALT+S F ENTER – to select only the formula cells in the range.
Then in the active cell, make this equal to the corresponding cell on the original cashflow less the equivalent cell on the pasted cashflow. For example, if the live cell is Cashflow Delta D3, the formula you need would be “=Cashflow!D3-‘Cashflow Pasted’!D3”. Once the formula is filled in, rather than hitting enter, press:
- CTRL+ENTER to fill this formula across the highlighted range.
Rename this sheet to something like “Cashflow Delta.”
If you have set up your cashflow delta correctly, everything should be zero until you make your planned modelling changes. Once you have made the changes, you can use the delta sheet to analyse their impact on the cashflow.
Afterwards, you can either delete the two new sheets, tidy them up, and leave them to use later. You can always reset the pasted cashflow to the current case by copying over values from the current live cashflow.
Cashflow Deltas – what to look for
Let’s say that we want to add a one-off operating cost of £100K in 2025, and we have set up a pasted cashflow (which reflects the project before the change to the inputs) and a delta cashflow showing the difference between this and the live modelled scenarios.
Thinking about the impacts we would expect to see from this change, we should be looking for the following in our cashflow delta:
- a £100K higher operating cost (in 2025 only);
- some minor changes to working capital and VAT in 2025 and immediately after that;
- a decrease in total tax paid by a much smaller amount, and on a plausible profile; and
- a change in the payments made to the shareholders that are pretty much equal and opposite to the above.
We can then look through our cashflow delta, line item by line item, to see if everything is behaving as expected. Anything that we see on our cashflow delta that differs from the above is a flag that there is something that needs further review:
For example:
- if the amount is incorrect – check how inflation is applied to the input;
- if the cost is not a one-off cost in 2025 – check the inputs or flags that govern when the cost is paid;
- if the knock-on effects on the working capital/tax don’t look right – check that the cost is treated correctly in these calculations; and
- if there is no impact on the financing cashflows – check the flow of the cost line through the cash waterfall/cash cascade.
On the other hand, if everything looks exactly as you think it should, you can be highly confident that the model is doing materially what you think it should be doing without even once looking at the model’s workings.
This is what makes the cashflow delta technique such a powerful tool.
If you are interested in having macros put into your model to create and remove cashflow deltas (or deltas of other key metrics) at the push of a button, or if you need some help building a model from scratch, our modelling team will be delighted to help you. Contact us to discuss your specific requirements.
