Beyond the spreadsheet: common financial modelling traps (and how to avoid them)

At Amberside Advisors, while our expertise spans the full breadth of financial and commercial advisory, including bids, debt raises, refinancings, valuations, and exits, one activity remains central to all these endeavours: financial modelling.

Financial modelling is often perceived as "just spreadsheets", but as we’ve discovered, things can get quite complicated. Our journey through hundreds of model reviews, audits, and rebuilds has revealed some fascinating, albeit subtle, errors that can significantly impact outcomes. Here are a few of the more intriguing ones:

Overloaded Operations Flags - Consider how would you tell a model to only draw debt during construction. You could use a flag that tells you whether the period is during operations – that sounds sensible, right? That is until you realise your model is automatically drawing debt 40 years into the future to cover a working capital shortfall after the project has ended. It's a classic case of overloading flags, and the effects aren’t always obvious, especially if your model is short on integrity checks.  A close relative of this error is double-flagging – using a percentage operations flag to reduce a cost or revenue in a partial period, but also scaling it by the number of days in the period too, resulting in a half-period only having a quarter of the cost.  Bonus points if a model does this with costs, but correctly models the revenue, resulting in overstated profits!

Impenetrable Indexation - Indexation can be a tricky beast. One common error we've encountered is modelling a year’s revenues as increasing by the inflation that will occur in that same year.  This overstates indexation by a year, as you can't base prices on inflation figures that haven't been published yet!  What is more, many tariffs and subsidies rely on specific inflation points, like CPI December (e.g. RHI) or CPI January (e.g. CfD). Despite uplifting in April, different items might use indices from different months and land on very different rates. With the recent inflation volatility, this has at times resulted in swings as large as a couple of percent.  They’re both subtle mistakes, but revenues being off by a couple of percent can make or break a project.

Factors of Ten Troubles - When working with figures in MW, GWh, hours, months, etc., it's surprisingly easy to be off by a factor of ten or worse. A good understanding of the industry and the ability to estimate expected results are essential to catching these errors. We've seen it all; from misplaced decimal points to the far more subtle issue of Megawatt Hours being treated as Gigajoules.  It happens more than you might think!

Spiralling File Sizes - There are usually at least five ways of doing the same thing in a financial model. Some methods might make sense but end up taking a lot of space or calculation time.  One key issue is with so called “volatile formulas”, where Excel recalculates them every time you make a change, regardless of whether that cell is affected by the change.  We’ve seen spreadsheets that took 5 minutes to update every time a cell was changed, and we’ve seen spreadsheets that were over 600MB in size (yes, really!).  We take great satisfaction in being able to reduce the time people spend poring over enormous tables of data.

Debt Interest Dilemmas - Calculating debt interest on just the principal and ignoring the arrangement fees rolled into the loan balance is a surprisingly common oversight. It’s usually less than 1% of the loan, but on a large enough balance this can be an eye-watering error.  Small details can indeed make a big difference.

YEARFRAC Formula Fiascos - Interest calculations are commonly understated by one day each period due to inputting the start and end dates of that period into the YEARFRAC formula, rather than the end dates of the current and previous period. It's a tiny error, but in a semi-annual model it understates interest by about 0.5% every period, which adds up to quite a lot over a 40-year timeframe.

Cell Anchoring Accidents - Cell anchoring issues can lead to some perplexing results, and sometimes they’re not obvious at all. For instance, depreciation that is supposed to be straight-line can end up looking like a reducing balance calculation because a critical cell in the formula wasn't correctly anchored. At first glance, it doesn’t look wrong at all, but it can still throw off your entire model.

Amberside Advisors’ experience and technical skills help us navigate these complexities and ensure our clients' financial models are robust and accurate. While these errors can be interesting in hindsight, they have all been responsible for substantial amounts of stress for many stakeholders - from analysts to decision-makers.  What they do provide however, is a valuable reminder of the importance of a well-constructed financial model. If you need one of those, look no further – we’re here to help.