Modelling a delay doesn’t have to take forever

Imagine you had a contract of uncertain size and duration. You’re not sure how long the contract might last, how big it might be and when it might start.?Similarly you might want to model uncertain costs or cost reductions, or a short term revenue reduction of unclear length.

In this article you can see how it’s possible to model that kind of complexity (uncertain timing) relatively simply.

You just need a few (well thought through) switches

As shown in the screenshot below, one of the tricks is developing some switching that controls the contract start and end. That involves:

• setting up some 1,0 on-off switches (or ‘timing flags’) that show you when the contract is on or off and/ or when the whole contract is delayed (see row 17 which shows when the contract is due to start and end, and row 24 which shows the impact of a further delay)
• multiplying the contract value by the timing flags (row 27 multiplies the ￡10 a month in cell C9 by what’s in row 24).

One piece of the puzzle involves developing some inputs that control the start and end for the row 17 timing flags:

• cell C13 gives us the start date for the contract, using the formula =SUMIF($E$2:$AB$2,C12,$E$1:$AB$1)
• C13 and C15 are then used to switch the contract on. For instance, cell G17 contains the formula =($C$13<=G$1)*($C$15>=G$1).?This formula compares the contract start (C13) and end (C15) to row 1. That means one of the tricks here is making sure your model has some period numbers and dates running across the top (in the example, row 2 uses the EOMonth formula (which makes it in at number 7 on my list of top 10 Excel functions).

The timing flags at row 17 show the expected start and end date for the contract.?The timing flags at row 24 work in a similar fashion and show the impact of a delay to the whole contract, and are then used to control what’s appearing at row 27.

You can model the potential for a pretty-complex delay in 3 simple steps

So here it’s a 3 step process really:

1. develop some timing flags that control contract start and finish (row 17, controlled by cells C13 to C15)
2. incorporate the expectation/ risk of delay (row 24, controlled by cells C21-22)
3. use the switches to dictate what’s appearing in the model (row 24 controls what’s appearing at row 27).

If you structure and lay out your model carefully, and isolate some clear switches, dealing with this complexity doesn't have to require formulas that are terribly long or embedded.

Subsequent steps: adding a scenario switch

Imagine the contract could be delayed a relatively short time, or a long time, or for some period in between. You know how to model a potential delay (using the timing flags laid out above) but what you don't want to do is sit there having to change the inputs all day. What you want to do is store say three sets of inputs (for early, mid, late) and set up another switch in your model - enabling you instantly to flick between the various delay scenarios and straight-away see the impact on your business. See here for some thoughts on modelling delay scenarios.