Last week we were in conversation with a training delegate who wanted to ‘bump’ or ‘slide’ data in a model to the right to accommodate project delay. Normally we’d think of Excel’s Offset formula for this job. Offset is perfectly designed to ‘slide’ data and incorporate project delay.
Click on the picture below and you can download the example Excel spreadsheet:
Elements of the Offset solution
The Offset solution definitely works in this case, but it was starting to become a little too complicated for our liking. The Offset solution sees us:
- Adding some blue input cells and including data validation so we can vary them easily;
- Comparing the start date (the blue cells C5:C9) against the model date in row 14 – look at cell D16;
- Using Offset to ‘slide’ the start date by the delays in cells D5:D9;
- Evaluating that result (TRUE, FALSE) and multiplying by the contract amount in cells B5:B9;
- Adding an Iferror for the times Offset gets confused;
- Using some green conditional formatting to help us see what’s going on.
The fact that we’re taking so long to explain the Offset solution tells us that maybe there’s a better way!
In Excel simple is good
Working with Excel we find ourselves on a never-ending quest to find the simplest most elegant solution to a financial modelling problem. So we put our thinking caps on and simplified things further. Employing a tiny bit of logic allows us to dispense with Offset if we want to. It’s possible to ‘slide’ data and incorporate project delay without having to touch Offset. Our Excel formulas instantly become shorter and neater. Surely that’s a wonderful thing!
Adjusting the dates
The breakthrough in this next example happened in cells E5:E9 where we decided to make a bit more space in the model and adjust the contract dates for project delay. Those formulas are simple (the EOMonth date formula was our friend) and so here we’ve got an example where making use of a bit more space in the model can end up simplifying the logic.
The next step saw us comparing the dates as before (look at cell D16) simplifying the logic and dispensing with the need for the Offset function.
Thinking. It’s the longcut that’s the shortcut for all Excel shortcuts
We like Excel keyboard shortcuts. We teach them and if you bother to learn shortcuts they can definitely make it easier to work in Excel. But sometimes there’s another way. Instead of speeding up the pace by shaving a few micro seconds here and there and whacking your keys super hard, just stop. Pause. Take a deep breath and think. Sometimes the real gains you make are in revisiting the flow and logic of your modelling. Long complex Excel formulas are always a sign that you should be stopping to think really hard and that something might be going wrong. That’s probably enough Zen modelling for today!
Read more stories
You’re reading stories from a world we love: the world of financial modelling.
Excel has hundreds of formulas on offer. That can present a challenge: you need to become fluent in the formulas that are going to be most helpful in financial modelling.
We’re happy to provide you with an Excel modelling tune up – for free. The program covers all the formulas attendees on our financial modelling course ask about and find the most useful of all.
Sign up online
We’ll send you one email a day for a month – each email should take only around ten minutes a day to work through – and you can unsubscribe at any time.