Inserting upstream when adjusting an Excel financial model

One of the toughest things you can tackle as a financial modeller is picking up someone else’s work (when it’s of any significant scale) to alter it.

It’s nice to think that the components are going to be obvious, as will the flows from top to bottom through those blocks. Equally likely is that the work has had various past authors, the patterns are new to you, and rather than flowing nicely from top to bottom (inputs -> calculations -> outputs) links tend to go here and there, back and forth, maybe looping around on themselves a bit before arriving at a conclusion. The structures at work may not be immediately clear.

Here are some thoughts as to how you can make the process of modifying any existing work slightly safer.

Top tip no. 1: allow yourself some time

Mapping the structures and flows (perhaps jotting them down on a piece of paper, and getting a clear picture of them in your mind) is going to be the necessary precursor to touching the model. It doesn’t matter how good a modeller the people asking you to do this work judge you to be. As soon as you’re working with something that’s slightly complex, everyone around you needs to know it’s going to take you a good and careful amount of time just to understand exactly what you’re looking at – before you even think about changing it.

You're going to want to make use of Excel's formula auditing tools from the start.

“Alt” “M” “D” is going to be a fast friend (the shortcut for tracing dependents).

As is its close relative “Alt” “M” “P” (trace precedents). Either that or the shortcut shortcut “Ctrl” at the same time as “[” – that’s the “Control” key while pressing the left square bracket at the same time – which takes you quickly to the first formula precedent on most keyboards – allowing you to trace through modelling work quickly.

There are sometimes hours that you can spend pressing “Ctrl [” to trace the immediate precedent, followed by “F5” and then, without looking at the screen, pressing “enter” to get back to where you started.

A fair bit of time examining the structures and links involved is going to be a necessary investment prior to even thinking about touching the model.

Top tip no 2: slot in above not below

Imagine you’ve taken your time, and you’ve got a clear (ish) picture in your mind of what you’re dealing with, the main components, and their principal flows. Now you’re ready to slot something new into the model. Let’s pretend you have an existing calculation line (or block of cells) that requires a new section inserted, modifying the existing section in some way. Imagine the existing calculation block getting something added to it or being multiplied by another number.

Your aim right now is to take the existing calculation at the bottom of the screenshot and modify it. But the next step requires you to do something that could seem somewhat counter-intuitive. In making the change, don’t keep on working down the page. Rather, shunt the old calculation down and create some space above it, because you’re going to do a bit of work above (rather than below) the existing calculation. You're going to slot the modification in above (not below) the existing calculation.

The existing calculation could be used in a lot of different places downstream in the model

The reason you need to shunt the existing calculation down, and create some space above it (instead of making your modification below the existing calculation) is that, in a big model, that existing calculation could already be used in a lot of different places downstream in the model.

Sure you can trace all those downstream links (you know about Excel’s formula auditing tools and?“Alt” “M” “D”) but shunting the existing calculation down and working above it is going to save you some work. It’s going to save you having to rebuild all the downstream links in the model. It’ll stop you possibly messing up the recreation of those links (they’ll stay just the same as they ever were). In short, shunting the existing block down becomes a shortcut saving you a chunk of inspection, link-severing and link-recreation work – reducing the risks attached to making the modifications.

Here are the exact recommended steps (remember – it’s all happening above, rather than below the calculation you want to modify).

Step A: insert some space above the piece of the model you want to modify (all the downstream links from the existing calculation area stay preserved)

Step B: copy the exact formula and recreate the existing calculation up into the new space you have just created.

Here’s a few keyboard shortcuts to help with step B if you want them (if you do this regularly the shortcuts get pretty automatic – but they’re all entirely optional right now).

Start by pressing F2 to get into the cell that needs to be copied (B16 in the example above).? Press “Shift” and the up arrow to select the entire and exact contents of the cell. “Ctrl C” to copy those exact contents. “Esc” to exit. Up to the new block. “Ctrl V” in the equivalent cell in the new block (B8 in the example above). Then fill right and down as required.

All these shortcuts are optional and just designed to speed things up a bit should you find yourself doing a lot of this. The objective with this stage is just to get an exact copy of the formula in the old block (B16) into the new block (B8), without any of the column or row references getting modified. It doesn’t really matter how you do that but if you’re doing it a big chunk of your days you can pretty quickly get to the stage where you’ve automated this step for yourself with the help of keyboard shortcuts.

Now we're at the stage where we've rebuilt the final calculation in the set in the newly-created space. Now we're ready to modify the final calculation for the change we want to make to it.

Step C: re-wire the final block in the chain for the change that’s needed (e.g. imagine modifying it for a number as per the new cell B16 below). So the final block, with downstream links intact, makes use of the preceding (new) block and it’s that final block that has been changed.

Is this obvious?

For some experienced modellers reading this, it could sound obvious: insert above rather than below.? With downstream links that might need discovering and recreating, in a big model it just becomes a lot safer.

But everyone has to be told this at least once.? And, if you’ve been around the Excel modelling block a few times, what might seem obvious to you is not always obvious to everyone else.? Hence today’s article!