News and Blog Articles

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

The structures at work may not be immediately clear. 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.

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

Mapping the structures and flows (perhaps jotting them down on a piece of paper, and at least 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.

“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.

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, to modify the existing section in some way. Imagine the existing section getting something added to it or being multiplied by another number.

Your aim right now is to take the existing calculation and modify it. But the next step requires you to do something that could seem somewhat counter-intuitive. In making the new modification, 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.

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 links into the right places in the model. It’ll save you having to sever all the links from the existing calculation. 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).

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.

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 ends up being changed.

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 post!