Modelling principles #5: don't hide things

Previously in our series of bite-sized posts on our best practice principles, I introduced 'Principle 4: clear and meaningful labels'. You can find this post here: ModellingPrinciples4-ClearLabels.

Today, let's consider Principle 5: don't hide things.

We often review models with hidden cells; data or formulas the creator has chosen to hide, perhaps due to a lack of importance or to ensure the model displays only the most relevant information. Much like Martin Handford’s ‘Where’s Wally’ series, we also encounter cells in which the background colour has been purposely formatted the same colour as the text within it, thus generating the illusion of the cell appearing invisible (to the untrained eye).

Here is a screenshot of a model we looked at recently. Can you guess what it does?

Its developer had a nice summary output elsewhere, driven by workings here.

Yep, clearly this is the contracts revenue recognition calculation. It's more detail than the author wanted their stakeholders to see, so they've hidden them cleverly.

They've used a custom format. Perhaps you can guess the trick in the comments below?

Why is it important?

Visibility through a model is key; hiding could prevent users from having a clear understanding of the results and the supporting calculations. Hidden rows could also be easily missed or accidentally deleted, especially as models live a long long time and are passed from team member to team member. This creates some serious risks and annoyances!

Formatting cells to appear invisible also carries inherent dangers. Again they're easily missed or overwritten, and who's to say that other users wouldn't value seeing or understanding those steps?

Fundamentally, we want the model to read like a book. I don't think many of us would enjoy a book where half the pages are blank. That's like falling asleep part way through a movie and waking up wondering what is going on.

How can we implement this?

Firstly, when it occurs to us to hide things - ask ourselves 'why?'. Is it for aesthetics or because it's too bulky? If it confuses things, just move it to another place, so the traceability isn't reduced. If it's too hard or complicated, we should have broken it into steps already as we keep it simple.

There's one common situation though that our team encounters where we do feel like hiding something - to cut down bulk or repetition. Thankfully Excel has a great way to do this whilst keeping some transparency.

The use of the ‘Group’ capability in Excel helps here. I almost wrote it conceals and reveals things seamlessly. But actually, its strength is actually that the seams are visible. Grouping rows puts little '+' signs at the side of the sheet, showing you a toggle to click things in and out of sight, showing the bigger detail when you need to.

Here's an example.

Ungrouped extent of the calculations:

(this goes on for another 200 odd rows)

Shrunk away with a grouping, bringing up the summary:

The grouping here isn't the only way to do it - some might prefer to group the calculation lines but leave each heading showing, or some other combination.

Grouping isn't the perfect solution, as that '+' toggle could easily be missed. Make sure you use the Excel settings to get the '+' showing at the top of the hidden section rather than the bottom, and consider adding a helpful note or something else to draw attention to it.

For some relevant reading, check out the ICAEW code 'Don't hide things' at page 7 of the excellent Financial modelling code. I look forward to seeing you for our next bite-size on segregating the model's different areas. UPDATE: here's a link!!