When Financial Models Fail – And Why It Matters

Financial models should be decision-making tools, not traps. Yet too many businesses rely on models that confuse, break, or mislead – with real financial consequences. Why does this happen so often? It usually comes down to three things…

1. Complexity
2. Overengineering
3. Lack of clear structure

Such models are hard to audit, harder to update, and easy to break. And the result is a tool that gets discarded at the first hurdle.

Many clients come to us with existing models – but they’re rarely fit for purpose.

4 Common Reasons Financial Models Go Wrong

The model is difficult to update. This could be because:

• Inputs are all over the model, rather than in logical, dedicated sections that ensure you don’t forget the location of existence of assumptions.
• The model was built targeting 100% precision. Such accuracy will never be achievable, and users will end up spending more time than it’s worth operating the model.

The model is difficult to understand. This happens when:

• The model contains no explanation of its data flows.
• The logic is specific to the way the modeller thought when building, rather than the way most people think when operating. Modellers need to understand who will be using the model, and how they will be doing so.
• Too much was done in too few cells. Long formulae can be right, but it takes time to understand them and be sure.

The model doesn’t answer the questions we need it to:

• The model was developed from a generic template rather designed as a sector and business specific tool.
• The business has changed, but the model has not. This is common in early-stage organisations that are still finding their way, but also large groups that have divested and refocused.

The model is distrusted. This might be due to:

• The model growing in random bursts, amended under pressure, so that it has become clunky, packed with both redundant and risky functionality. This is typical of models in high-growth and distressed businesses.
• Visual inconsistency. This raises alarm bells and poses questions such as: is the logic inconsistent?
• Broken links or #REF errors – these might not be driving issues in the outputs – but they send the wrong messages to users.

So if the above issues aren’t resolved, businesses can experience significant losses:

1. Losing out on investment. Potential investors might take a cursory look at the model and find it easier to look evaluate other opportunities.
2. Getting suboptimal rates when borrowing. Not giving lenders exactly what they need in an easy-to-use format may make their life harder.
3. Staff focus moved away from value-adding activities to tinkering with tools that are not fit for purpose. Reporting processes can stretch from hours to days and even weeks.
4. Review time – whether statutory or for the purposes of a transaction – the longer it takes to review and understand a model – the higher the cost of the audit.
   

Spreadsheet Mistakes That Cost Millions

Studies have shown that as many as 90% of spreadsheets contain errors. Most of them remain hidden, but there are some famous errors out there that show how critical correct design and operation of a spreadsheet can be.

• J.P. Morgan incurred $6.5bn in losses and finesas a result of a wrong copy-paste.
• Canadian TransAlta lost 24m as a spreadsheet error caused them to overpay for contracts. That was equal to 10% of the company’s profit.
• During the financial crisis, Barclays bank was in the process of buying up some assets from bankrupt Lehman Brothers. Their lawyers took a long list of assets in Excel and hid the rows which related to assets that were deemed ‘toxic’ before sending the list to the counterparty legal team. Because they did this instead of deleting them, Barclays was obliged to buy everything in the workbook, even the ‘toxic’ assets!
• More recently, a hospital ward did not meet the required ventilation standards because of a spreadsheet error made during the planning stage.

Why We Use Principles – Not Rigid Rules

There are many good modelling frameworks and codes out there, but at PPS, we follow the UK Department for Transport (DfT) best practice framework. This is because it has five, easy to use principles.

The more rules, the hard the framework is to memorise, and apply.

I distinguish between framework and rule here, because the latter is rigid. Excel is flexible, and this is exactly why it is such a powerful tool. It doesn’t make sense to apply inflexible rules to it.

At the same time, devising a specific rule for each aspect of a model will make it very hard to memorise.
For example, the exact colour of the cells isn’t important. What is important is that they are consistent throughout the model.

Five Principles Behind Models That Work

Simplicity: Don’t Overcomplicate It

This principle drives all others. Everything you do, even when conforming to other principles, needs to be done simply. To quote a mentor of mine: “Modelling is more about structure than fancy formulae.” I’ve seen huge, yet efficient models which are built using no more than a dozen Excel functions. The subject matter is often complicated enough, it is our job, to model it as simply as possible. Don’t overcomplicate your formulae. A rule of thumbs is that the formula should be no longer than the width of your screen, provided you aren’t using a giant monitor.

So how do we achieve simplicity, when financial calculations can be immensely complex?

1. Avoid duplication. If you have multiple cells doing the same thing, eg checking if a condition is being satisfied – do that check in one place and have the cells refer back to it. (e.g. checking if a certain date/milestone has been reached)
2. Small Steps – Break your formulae up. Each Excel sheet has more than one million rows. You won’t run out. Breaking up a calculation into several rows increases transparency, making your model easier to review, and less likely to break. Nesting is the placement of one function as an argument within another. Avoid nesting things like IFs. If you have more than two possible outcomes, consider splitting your function.
3. Short formulae. Our rule of thumb is that a formula should never be longer than the width of the formula bar (unless you are on a 50 inch screen, in which case it should be far shorter). A formula that spills into several rows is too hard to follow. What matters isn’t how well we understand a function when we build it, but rather, how quickly we can understand it when we go back to it after a long holiday. If it takes half an hour to dissect a function, its too long.
4. We also need to avoid volatile formulae such as OFFSET and INDIRECT, because they are not transparent, and can be difficult to follow.

Consistency: Make It Predictable

Consistency has two effects: One, it makes your model look professional. And that instils confidence in the users of your results. But the other, more important effect, is that it genuinely reduces errors and makes it simpler for one to update the model.

We will look at three key areas of consistency:

1. Calculations: A good model will have cells used consistency across rows, and where possible, down columns. This means that you would have a single unique formula in the first cell of your row, and it will be dragged across.
2. Column Usage: Column usage should be the same in as many sheets as possible. If you have a dedicated column for row labels, it should be the same column in each sheet. If your model has a timeline, it should start and end in the same column on each sheet, and have the same intervals, be they annual, quarterly, monthly etc.
3. Formatting of cells: A model with no formatting is a mess. A model with too much formatting is overwhelming. Excel has a useful functionality, the ability to create and use cell styles, that will allow you to uniformly format your model.

Remember – the formatting of a cell should make its purpose immediately obvious. Is it an input, a calculation, or a check?

Separation: Keep Inputs and Outputs Apart

Separation is key to new users when working with our models. It’s also powerful when we come back to our on models after some time, because we don’t have to dig around for specific inputs.
This is because we have separate sheets, or sections in a single sheet model, for the following groups:

1. Inputs
2. Calculations
3. Outputs
4. Controls

Within calculation sheets, we can separate out callups, that is, references to other sheets, and calculations, what work with those components.

There are exceptions to this principle to be aware of, hence our choice of framework over rigid rules.

1. Error checks are technically calculations, but we have them on every sheet in the workbook, because they are key to avoiding mistakes.
2. Subtotals can consolidate information so that we can simplify dataflows in the model. For example, you many have many inputs relating to a single P&L line, so you may sum them up on an input sheet and feed the summary through rather than dragging through the data on a granular level.
3. Finally, macro pasted values are allowed on calculation sheets. We won’t go into detail on this one, but there are models which require pasted values on calculation sheets to avoid circularities. We allow these because they are technical not USER inputs, therefore it’s not like we would need to look for these cells to update them. The macro would know where to go and what to do.

Linearity: Structure It Like a Story

Linearity is best thought about during your model design process. It’s about planning with a logical flow of data in mind. It helps us avoid confusing logic, and what I call spaghetti references, where cell references are all over the place. It also helps us work around circular references, where cells indirectly or directly, refer back to themselves. Excel doesn’t cope very well with this.

Linearity should guide your model’s design from the start. A model should read top to bottom, left to right – like a book. We want to avoid cells referencing to ranges both above and below them, because it leads to jumping around when tracing logic. The more jumping around, the less clear the model becomes.

Integrity: Build In Trust and Checks

Integrity is all about making sure that your numbers stand up. We want to avoid ‘fudges’, and make sure that the numbers balance by themselves. We don’t want to miss any data, nor have inputs that invalidate our model.

We want to include robust error checks, but also to build them into a network that gives us full visibility of the model’s errors at any point in time, without scrolling through the sheets.

We have two complimentary tools to manage errors in our models:

1. Avoid them altogether through careful formula design. We need to think about each branch of our formula and foresee what could go wrong.
2. Relates to how we capture anything that does slip through the net. Our checks can flag inappropriate inputs, incomplete data flowing through, invalid calculations and pretty much anything else you want them to, for example, checking compliance to any lender-imposed ratios.

The key to a structured network is to set out the three types of error checks we build into our models.

• We have the individual checks, which can be located in any row, checking for one or more specific error in a range.
• The sheet level summary check is a single check on a given sheet that summarises the values of all the individual checks on that sheet.
• The model level summary check is a single check on a summary sheet that gives us the total number of errors in a model.

From Spreadsheet to Strategic Tool

Why Best Practice Structure Makes Financial Models Strategic
A well-built model isn’t just technically accurate — it’s structurally sound, easy to navigate, and aligned with decision-making needs. This is what transforms it from a spreadsheet into a strategic tool.

Why Structured Models Give You the Edge

• Raise capital faster: Investors need to grasp the business model quickly. A clean, logically structured model builds trust and credibility. It allows funders to explore sensitivities without confusion or risk of breaking the model.
• Streamline your exit: During due diligence, acquirers often inherit the model. A best-practice model accelerates the process by being transparent and easy to audit.
• Plan with confidence: When models are intuitive and adaptable, they support real-time decision-making. Scenario planning becomes faster, board packs more defensible, and leadership alignment easier.

The Bottom Line: Good Models Drive Great Decisions

Excel is a powerful tool, and the reason that it remains relevant in finance is its flexibility to model out complex and vastly varied problems. But its greatest strength can also be a weakness if it is not used properly. Unless we safeguard our models by applying best practices in their design and operation, our long-term plans will have weak foundations, and we will make decision based on incorrect information.