Three tips for Error-Free Financial Model Simplification

Simplifying a model is one of our favourite projects. There’s nothing more satisfying than taking a working model, which, for whatever reason, has become too bulky, and stripping away unnecessary and complicated functionalities. If there’s a model that is slow to load or calculate, or difficult to share due to file size, point them our way, and soon you’ll have a streamlined model, purring away.

That being said, it can sometimes be a daunting task to undertake, so, here are our top financial modelling error tips to help you simplify your model.

1. Use Delta Sheets

We don’t usually expect numbers to change during simplification activities, so we want to identify even the smallest movement in outputs, in real time.

At a minimum, we want to ‘freeze’ the outputs of the model before we go and tinker with the underlying calculations. We call this our ‘Frozen’ sheet.

To create the ‘Frozen’ sheet, we take a copy of the Output sheet, select the entire sheet, copy it, and then paste it as values.

We then take another copy, which we call a ‘Delta’ sheet, and have all the cells calculate the difference between the ‘Frozen’ sheet and what we can call the ‘Live’ Outputs sheet.

This will make any changes visible.

But how can we track these changes without going back to our ‘Delta’ sheet and scrolling across all the used columns and down all the used rows? Read on to find out.

2. Temporary Error Checks

We can add an error check in the first column of each row of our delta sheet.

For the uninitiated, error checks are cells that flag if we have a ‘problem’ in our model, and this is most commonly an unexpected value.

In our current context, and unexpected value is any number other than zero.

The formula in our error checks does not sum the values in each row, because we may have compensating errors within our Delta Sheet, where, for example, one cell has a positive difference, and another has an equal and opposite difference.

Therefore, if we were to sum them together, they would cancel each other out.

What we can do instead, is sum the squares of each cell in a row. Our error check will flag as a 1 if the sum of the squares of each cell in its row is greater than 0, because the square of any number is positive.

We then want a summary check at the top of the ‘Frozen’ sheet, which we can feed into our error check network.

This means that any differences will be visible in our Master or Summary Check, which should be visible on each sheet. Now we can have an eye on any differences as we go through the model, making our changes.

Once we are happy with our changes, we can then delete the ‘Frozen’ and ‘Delta’ sheets.

3. Evaluating Each Branch

Now that we have our safety net in place, we can go in and remove unused sections and simplify long formulae.

The latter is an area where mistakes commonly slip through. If we have long formulae, they often have more than one branch, for example, through IF statements. MAX and MIN are also formulae to be aware of in this context.

If we go through our simplification by only making sure that each cell gets to the same result, we are not checking those other branches, which may currently be ‘deactivated’, but which could ‘activate’ if inputs changed.

It is therefore important to ensure that each branch arrives to the same result, and that can mean isolating out segments of formulae and reviewing their results as we go along.

The Takeaway

A good way to think of model simplification is doing building work on a house.

You may have always wanted that open plan kitchen diner, but you wouldn’t remove a wall in your home without checking its load-bearing. In the same way, you don’t want to dive in to making changes to a model without making sure there are safety measures in place that ensure the core integrity and functions of the model stay put.

Our ‘Delta’ sheets and error checks can be thought of as props, holding everything in position while we go about our structural work.

As for that work, following the above tips will put you in good stead. Any alterations to a model must be carried out meticulously and with every eventuality in mind so that everything still holds together when those props are removed.

For more financial modelling error tips, carrying out a full model review and much more, why not check out our Financial Model Review course?

If you have a project you would like assistance on, or are looking for financial modelling courses, contact us today by emailing info@pps.financial.