The benefits of a great timeline for your Excel models

You’d expect to see dates running across the top of any Excel financial model.?But there are a few other things you might want to think about including too:

• Simple 1,0 switches that you can multiply your model numbers by, turning sections of the model on or off (e.g. around key contracts, price rises, stitching together your actual numbers with your forecast calculations)
• Some text showing whether you’re in an actual period or a forecast period
• A period counter e.g. starting at 1 going through 12 (for the first year in a monthly model) and beyond
• The year you’re in (1-5 for a 5 year model) – so that you can use SumIF to create annual totals with elegant and error-proof formulas you can fill from left to right
• Maybe the quarter number if you’re interested in quarterly results
• A switch showing when any transaction kicks in, maybe a new balance sheet gets added in, cash and equity gets adjusted for the new deal, and new debt gets drawn down.

There are a lot of possibilities and a decent amount of thought required.?The timeline is designed to make it clear what might be changing as you look across your model (forecast starts, transaction happens, price rises kick in).?More than that though, the timeline means the calculations in other parts of your model can become much simpler.?If you’ve got a timeline that uses the value of 1 to switch your actuals and your forecast on, you’ve got an elegant way of stitching your actuals together with your forecast.?(1,0 switch) x actuals + (1,0 switch) x forecast = the combined picture.?A timeline that centralises what’s on and what’s off can bring clarity and save a lot of other deep, tough and potentially confusing calculations in the rest of the model.

Formulas that help

You can download an example timeline here.?

It’s not meant to be the best timeline in the world or the only timeline you’d ever use – it’s up to you to sit and think about what you’d like to include.?But it does contain some of the formulas that regularly tend to crop up in timelines:

• EOMonth – helpful for rolling dates forward – there’s a reason why it makes it to my Excel Top 10 (ranking in at relatively-lowly #7, but it’s there nevertheless)
• Something that will turn your monthly counter into quarters (helping with quarterly totals using SumIF)
• Something that will turn your monthly counter into years (helping with annual totals using SumIF)
• A switch that will trigger based on dates e.g. for switching something on such as a transaction or a price rise
• Actual vs forecast period switching.

Timelines can really help bring clarity to modelling, centralising some of the hard work around the big shifts and changes that occur as your model marches into the future.?It’s one of those situations where some hard thought up front (“What should I include in the timeline?”) can really pay some dividends.?

Enjoy your timelines folks!

PS - does it go without saying??Make sure your timeline is on one sheet (maybe on its very own sheet if it’s complicated) and link the top of your other tabs to the timeline. Centralise your timeline so you only have to change it in one place.

Screenshots of example timeline formulas

Download the example timeline here

EOMonth formula for rolling dates forward:

A formula that turns a month number into its quarter number (helps with a SumIF on quarterly totals):

A formula that turns a month number into its year number (helps with a SumIF on annual totals):

A switch triggering maybe on a date or a period number – in this case used to denote forecast periods: