Modelling principles #2: logic reads like a book

Last time in our series of bite-sized posts on our best practice principles, I introduced Principle 1: keeping things simple. You can find this post here: Modelling principles #1: Keep it simple

This week will focus on Principle 2: Logic reads like a book

The logic in a model is the sequence of its calculations, from inputs, through various steps, to outputs. We'd like this to flow, so that the order you meet things is the order that the model uses that information. When you open a book, you don't expect it to start at Chapter 16 and jump at random in the story as you turn the pages. Unless it's the excellent Slaughterhouse-Five.

Why is it important?

If you’ve ever been to IKEA, you’ll know the shop has a circular, one-way system and the design often means you can’t see what is coming next. My uncredited co-author spends a lot of time there; whilst the layout might drive up in-store spending, it's the opposite of what we are looking for in a model. An IKEA-like design would make a model hard to follow and increase the risk of error or misinterpretation.

Models are used by many stakeholders who are mostly not Excel experts. They shouldn't need to be. Reading through a model should tell its story, and we (western readers) learn to read in a set way, from the Gruffalo, through all our study, work and play, to today's great articles on modelling. A financial model should give people things in the order they expect, building up its explanations, and financial items should appear in the order people are used to seeing them. This intuitive flow helps create transparency.

How do you implement this?

There are two main components to model structure:

• the split and layout of the model workbook into separate worksheets or tabs, and
• the layout on each worksheet itself.

Starting with the workbook infrastructure, another article soon will cover how we split things up. So how to order the sheet tabs? To start with, the order should mostly flow from left to right to ensure easy navigation around the model. Strict logic would dictate that sheet order should follow the calculation order, and the order we are likely to build things, like this:

However, modelling is always about compromise. Let's balance this flow order against the needs of our users, with the outputs that they're eager to see made more prominent:

Now thinking about a single worksheet, the logic should flow from left to right and top to bottom where possible.

Here is how we could approach a gross margin calculation:

Luckily, the flow from left to right will happen automatically in most models, as that's our time dimension, across the sheet.

The flow from top to bottom is where we need to think, and plan. Generally, earlier calculations should appear first; we might follow the flow of financial statements, so usually we work out sales before we work out debtors and cash collection.

In the example above, we build up sales from its input data ingredients, and then we work out gross margin, using the sales we already showed. By building on previous steps, we make sure it's easy to follow and easy to check.

In more complex models, with many interlinking parts, it starts to get harder to keep the flow in storybook order, but we aim to keep this principle at heart.?If we have to place stuff elsewhere for technical reasons, we'd use links or explanatory notes to add signposting about how things are flowing. For some related reading, see "Layout and Structure" on page 4 of the ICAEW financial modelling code: financial-modelling-code.ashx (icaew.com)

And look out for the next instalment, where I will talk about choosing your functions carefully. Update: link here: Modelling principles #3: choose your functions carefully | LinkedIn