Let’s discover more about learning financial modelling with the 10 Rules of Financial Modelling. This post looks at why it’s essential to have ‘timings’ in your financial model and how to add them for the best effect.
Timing is much more than just adding a timeline to your spreadsheet. Periodicities must be apparent, and this is executed by using separate sheets.
Every good financial model needs to deliver understandable and transparent results that give the user confidence to make informed decisions. Any changes made to timings mustn’t compromise the reliability of the model.
Why are ‘timings’ important?
Timings are crucial when it comes to financial modelling.
You may recall that this is s topic linked to other issues that we’ve already looked at in this series, such as model structure and left-to-right consistency.
Typically, timings are displayed in the row across the top of each sheet that describes the dates for each column setting out the timeline. Most models will suggest that several date dependent events will occur.
However, despite this, it’s essential not to restrict or constrain how dates are used in your spreadsheet.
For example, you may have a construction phase and an operations phase. In most cases, you tend to find that the construction phase will often be modelled monthly, and the operations phase, perhaps quarterly, semi-annual or indeed annual.
You want to be clear about the transition between those two phases of the project’s life. The risk is that you type in a series of construction type monthly calculations in the first few columns, and then in subsequent columns, you flip over to the semi-annual or annual type calculations.
In that transition phase or cross that transition phase, there’s a complete change in a formula, probably. There is a rule that we describe as left-right consistency: you should only have one formula.
You should firmly avoid having two or more periodicities set out side-by-side. They should be modelled on different sheets and consolidated as required.
Single timeline versus multiple timelines
In the case of the construction phase dates that slip, then where does that information go?
The problem is it might flow into your semi-annual operating phase. Therefore, you’re going to have to go back into the spreadsheet and update all the formulas in the first column of the operational phase and change them back to the formulas you’ve got in the monthly construction period.
It may not be you making the changes, but it could be later in the model’s life when a user is asked to update the model to reflect the changes and the actual timings of events. If you haven’t planned for it, you could then have a little time bomb sat in the middle of the spreadsheet. The user changes the project and starts the operation, then the start date, and the whole thing falls apart. That’s not a good situation to be in.
Timing does have a habit of catching people out. And again, it’s one of those things you can plan for in advance.
Questions you need to ask to get your timings right
A good modeller will sit there and think all these issues through right from the outset:
- So what are the date dependent features of my timeline?
- What’s going to happen? At what point in time will it happen, and what would happen if that slipped or if that was brought forward or indeed if it was cancelled altogether?
- Set up a specific monthly sheet for the monthly timeline, a separate sheet for quarterly, and so on.
So that sort of thinking will reflect itself in a very formulaic solution. As long as date changes have been planned for and anticipated, a good modeller will have techniques to allow these trivial changes to be put in place by the model user. There will be no concern on their part that they’ve broken anything and that the results produced by the spreadsheet are anything other than the correct results.
In summary, timings need to be added to the financial model so that any changes don’t compromise the model.