Modelling principle #8: use columns consistently

Welcome back to our series of bite-size best practice in your financial modelling. Last time out, we considered the sin of 'hardcoding'. Today we look at how you use the columns of your model file. In particular, using them consistently from worksheet to worksheet.

Why is it important?

A spreadsheets grow, we spread them over multiple pages. We think that's best done when the same column of each page is used for the same thing. It's easier to follow and it really reduces the risk of errors in your formulas. Whether your columns are different business units or different time periods, your references will make more sense if they use the same column for that unit or time period each time, and errors will stand out.

See how in this example column M's formula always references column M (for all of the sheets that it needs to link to)?

Whereas in this next one the reference to column L sticks out like a sore thumb! That's much easier for a reviewer to spot as they check through:

Keeping the same layout means we can also use formulas that sum through the sheets, great for producing consolidations or annual summaries, noting we hit the exact same cell every time:

Mark Robson posted a great lesson including this '3D referencing' here.

We can even use 'start' and 'end' sheets (affectionately known as 'bookends') to create those multi-sheet sums, so that sheets can be flexibly dropped in and out:

Keeping the layouts and column use the same also builds efficiency. With due care, you can group sheets and make edits to them simultaneously. You can also rearrange, drag and drop, cut and paste sections from sheet to sheet, knowing they'll just slot in - 'Insert Copied Cells' is one of my all-time favourite techniques to reproduce whole sections.

How do we implement this?

So how do we make this effortless? By putting all the effort in (or using slick tools to help) at the start, to create a template sheet, and using it for every new sheet in the model.

Be sure to plan ahead. Make sure your template has space for all the standard things you'll want to lay out on each sheet: headings, labels, units, periodic data, etc. Maybe you need a column to allocate each line to a team member, or to include commentary on data sources?

Set up your timeline across the page, freeze the panes so it's easy to read, and get any total columns in place. Please don't break the timeline to insert annual totals in the middle.

Get your template kitted out with the right formatting and fonts, and set column widths. You could also leave some simple commonly-used formulas lying around on your template in case you need them during the build.

For our team, we build on-brand with the same standardised template from our tools. Our system of headings and layout is ready made. Small fights sometimes break out as we decide what colour to make it, if our client needs something other than Grant Thornton Purple.

Sharp-eyed readers may spot a few sneak previews in that image of some upcoming things that are important to us (intuitive formatting, control accounts). Keep looking out for upcoming posts!

We've put out some great recent thoughts on the choice of timeline in your model (thanks again Mark Robson ) and how to deal with long life assets (from the superbly knowledgeable Denis Battiston ). These are definitely topics where you want to straighten out your thinking on before committing to columns in your template.

Check out the sections on 'Consistency' in the ICAEW financial modelling code for some more related reading.

We look forward to seeing you for the next instalment, on keeping your financial statements integrated.