Running scenarios that incorporate potential delay
Recently we’ve been looking at all the Excel functions that can help you pick a piece of data out of a list (we’re big fans of Index) – for example for scenarios you’re wanting to run through your financial model.
Perhaps the model inputs you want to flex are set out in a series of rows, or stacked in one column, or slightly more widely spread throughout your model.
If you’re careful with your model layout Index (or sometimes Choose) will be short compact solutions that help you switch scenarios through your Excel spreadsheet. But it doesn’t really matter which exact function you opt for, as long as it’s short and neat.
Incorporating contract delay – a very simple solution
By now we know how to rise to the challenge of switching scenario sets through a model. But imagine the (complex) situation where you’ve got say a contract, or other model line item, that could be subject to delay.
One simple solution might see you laying out a few scenarios around delay for the line item in question, loading those up manually – as per the screenshot below.
Maximising flexibility with model switching
But you might find yourself in the situation e.g. where people within your business want to experiment with a wide variety of contract start and finish dates as the project evolves.
What you might want to do is establish a series of switches in the model that turn the contract on and off at particular times. In the example below you can see the contract being controlled for the duration it is running (starting at line 29).
And then you can see some further workings which are acting to delay the contract start. Note again we’ve decided to use (our favoured) Index for that job rather than ‘Offset’ – because Offset is ‘volatile’. If it’s not a large volume of calculations that need to be delayed then using Offset is unlikely to impact your model performance and, if you prefer that solution, that’s fine.
The switches we’ve employed are not complex and make use of the fact that you can multiply through by Excel logic tests as part of turning calculation areas on and off (neatly).
It's enough to keep us all thinking - hard!
Model design and development keeps us continually thinking hard – doesn’t it?
By now we fully appreciate that, when running scenarios, it’s going to be handy to cluster key inputs close together, grouping them before throwing them through our model. That’s illustrated in the first screenshot above.
Sometimes we’ll pause, stop and contemplate whether the extra complexity is justifiable, and establish some central switches that turn sections of the model on or off our push them out further (i.e. delay) them. In that case you’re likely to want to draw on Excel’s logic tests for the switching job – as shown above.
It’s enough to keep us all thinking really hard isn’t it? But only absolutely all the time!
