This article was first published in the November 2018 UK edition of Accounting and Business magazine.

If we see financial modelling as a tool for business, better modelling means better decision-making. In this series we have flagged some danger signs (‘Too good to be true,’ AB, June 2018) and talked about why leadership and teamwork are as important as the spreadsheet (‘Division of labour’, AB, July 2018). Here we look at the challenges of managing working capital.

Working example

Take this scenario: three people from a bank’s credit team are reviewing an application to increase the facilities of a borrower (an engineering contractor), just 11 months after the contractor banked a £500m deal. Trading is really good, but the revolving credit facility (RCF) is too small. The bankers dig out the original application and the model that supported it, and compare it to the latest management accounts.

The cause is clear: the net working capital swing was modelled to be minus £500,000 for the last quarter. The actuals show minus £6.5m. It is not huge relative to the loan, but more than enough to leave the company with no RCF and an updated forecast showing more working capital needs in the next six months.

The need for working capital – the cash a company needs for day-to-day trading – fluctuates a great deal, depending on how fast the business sells to customers, how quickly customers pay and how quickly the company has to pay its suppliers.

Forecasting this can get unexpectedly tricky. A swing in working capital is one of the main differences between what your profit and loss statement says you earned and what you then actually find in the bank at the end of each month. Higher sales means higher debtors and a longer wait before the cash comes in. Negotiating an early settlement discount with suppliers is great for your profit margin, but the cash goes out earlier.

Reality check

The bankers in the scenario above found that the model from just one year earlier forecast working capital in line with business growth. Flawless maths, and, at the materiality levels everyone was focusing on, sensible enough. But, in fact, real life is not so simple.

The engineering firm was told to get a better view of its forecasts and a better model, and fast. That is where it needed some help creating a much sharper business tool.

The problem was that one arm of the business had indeed grown strongly, gaining several large contracts with big, strategic customers, but margins were lower. As an engineering contractor, the company would do a lot of work, invoice once a surveyor confirmed progress, and then receive the cash on terms a month later. Surveyors hadn’t been surveying on time, though, and these big customers also demanded longer credit terms.

The old model missed these dynamics and had no variables to change in order to check for such an impact. Growth costs cash. Worse still, many businesses have a real disconnection between when revenues are recognised and when the cash comes in. Even simple businesses have seasonal swings, heightened by growth. It is important to have a forecast that understands this.

There are many ways to set a spreadsheet up to forecast working capital ups and downs. Senior input into cycles and risks is vital in picking good techniques. Some common tricks and their pitfalls are shown above.

Getting Excel-based tools to calculate the working capital fluctuations with any rigour can be challenging. It takes practice, thought and collaboration, and not just the feverish late-night spreadsheeting of a stressed CFO or a junior Excel wizard.

You need to get these fluctuations modelled in a way that allows you to flex the growth or risks of the business and see what happens – in a way that balances enough detail to be credible with the elegance and simplicity to be transparent and not blow up your laptop.

Improved guesswork

Forecasting those swings will include some guesswork. Some historic patterns will repeat, but others won’t. A good model will help make those guesses better and help you avoid being one of those rattled bankers, or the embarrassed FD going back to the bank with cap in hand.

Rob Bayliss and Mark Robson are part of a team at Grant Thornton UK that pulls together beautiful forecasts for clients making important decisions