I first started constructing forecasts a long time ago using Lotus 123 on a laptop with a gas plasma screen and printing on a dot-matrix printer. The younger reader might have to google Lotus 123, gas plasma screens and dot-matrix printers. As time has gone on, Excel has superseded Lotus 123, laptops have become hugely better, and printers a lot quieter. As the COVID-19 crisis goes on and the lockdown is gradually lifted, forecasting cash flow in the short term has never been more important. I have recently seen many people advocating various approaches and ideas, some of which I disagree with, particularly those that are over-complicated.
There are two main types of cash flow forecast. One type is a model which looks to forecast the businesses profit & loss account, balance sheet and the cash flows that result from them. It usually takes a medium to long term view over several years and can be flexed to see the effect of various scenarios and assumptions. The other is a short-term forecast focused on cash receipts and payments and it is the latter I am focusing on here.
Those of us that work in Turnaround and Restructuring deal with businesses in some degree of financial stress all the time. Short-term cash forecasting is absolutely critical, and a rolling Thirteen Week Cashflow Forecast (TWCF) is the gold standard in the Turnaround profession. I have personally been involved in many of these in a long career and have both constructed forecast models and taken responsibility for the day to day cash management. I have done this in businesses with revenues of less than £1m on a single site to businesses with revenues in the £ billions with multiple sites in multiple countries. In this short paper, I want to look at not only how they should be constructed but how they should be used and why that makes them so beneficial.
Let’s start out with why 13 weeks? In many businesses, 13 weeks approximates the length of a business cycle. By that, I mean the time from a customer placing an order through to the provision of the goods or service and eventual receipt of payment. Similarly, for any goods or services bought in it covers from order to payment. It is long enough to catch most variations in seasonal patterns, holidays etc. It is therefore capable of a high degree of accuracy. Beyond 13 weeks that becomes increasingly difficult. It is the immediate future in which you can identify looming problems and do something about them. It is an action-focused time period.
Weekly is also the best basis. I have seen some people advocating daily forecasts. Those become way too big and are consequently much harder to understand and take much longer to prepare. It is spurious accuracy and quite unnecessary. There is no cash related decision that needs that degree of accuracy and can’t be flexed a few days either way. On the other hand, monthly is way too long and intra-month variations are not visible.
TWCF’s need to be done on a receipts and payments basis and not calculated from changes in balance sheet working capital categories. Balance sheet changes work in longer-term multi-year forecasts but not in a TWCF. They are also much harder for people without a financial background to understand. For a TWCF to work properly, everybody must understand it. In a liquidity crisis, it is an essential management tool driving many key business decisions.
TWCF’s are normally constructed using Excel spreadsheets. I have recently seen much advice floating around with some Excel experts advocating the use of complex formulas. Don’t go near them! That advice applies mainly to the formulas but also to experts who have built models but never actually used them on the front line in a distressed business. I am firmly of the opinion that a TWCF should only use formulas to add, subtract, multiply and divide. Anything more and you are over-complicating it and increasing the risk of errors. There may be some small exceptions such as a handful of “If” formulas to provide error checking. I always ask myself whether if I was knocked over by the proverbial bus, somebody else could quickly pick up my spreadsheet and easily understand it, even if they didn’t read my explanatory notes.
The basic tenets of good spreadsheet construction should apply. There are many excellent guides on this. In particular, inputs should be separated from calculations and from outputs and the spreadsheet should be secured against any accidental alteration. A bit of colour to mark out where inputs are required is always useful, but I would put in a personal plea not to overdo it. I am one of the 8% of people, almost all men, who have some degree of colour blindness. That’s millions worldwide. The same message applies to the use of colour as to everything else in a TWCF; keep it simple.
The level of detail is a fine judgement. Too little and you may miss something important. Too much and it is harder to understand, harder to manage and important issues can be lost in a mass of figures. As a general principl, use the 80/20 rule. 80% of your cash inflows will come from 20% of your customers. Have the 80% shown individually and the rest aggregated. Similarly, payment to suppliers. All businesses are different and 80/20 is just a guide. In some cases, it will be necessary to analyse by different means, for example by sales channel. It’s all about what works in your business and gives insights that lead to action.
The start point of the forecasting exercise should be the actual receivables and payables per the accounting system but do ensure these are up to date. All other cash related items have to then be factored in at the right times such as payroll, payroll taxes, VAT, rent, etc. All recurring items need to be included and watch out for annual or semi-annual payments that can easily be missed, such as insurance premiums and one-offs such as capital expenditure.
The model should be rolled forward each week. It is always good practice to ensure the previous week’s actual is compared to the previous week’s forecast and explanations given for any significant differences. There is no real excuse for there being big differences; it should normally be possible to forecast the next week accurately and I always hold people’s feet to the fire on this. When looking at actual vs forecast and other changes vs previous week, it is also important to identify what are permanent differences and what are just timing differences. By looking at this actual to forecast comparison and changes from the previous forecast, lessons are learned, and the accuracy and usefulness of the whole process is improved.
The last step in the forecasting process is to look at what the forecast cash flow means for available cash and against any borrowing facilities. At the simplest level this may be just the bank balance at the end of every week, or the balance compared to a borrowing limit. In more complex situations it will incorporate the borrowing base calculation for an asset-based lending facility and in the most complex cases needs to look at multiple facilities in multiple currencies in multiple countries. The aim is to see how much headroom you have, if you are going to run out of cash, and if so, when. Lenders will always look more favourably on a business that can demonstrate a good understanding of its cashflow.
It should always be remembered that the cheapest and easiest source of additional cash in any business is internally generated cashflow. The insights a TWCF gives you can often result in improvements to cashflow which are interest free and don’t have to be repaid. This is incredibly valuable at any time but especially so in a liquidity crisis. In groups or companies, especially in different countries and with inter-company trading, it also gives a valuable insight into where cash is within a group and highlights cash hoarding at a local level. Cash may not be where you would like it to be but until you know that, you can’t start to work out how that might be altered to benefit the group as a whole.
Constructing the forecast model is relatively easy. It shouldn’t take a great deal of time if unnecessary complexity is avoided. When cash is tight, creditors are pressing and you need to gain control of the situation, you don’t have the luxury of spending weeks on an all singing, all dancing forecast. I have normally been able to get something in place within days, maybe a little more for a large multi country/currency model. I would always rather build something quickly, implement it quickly and then refine it over the next couple of iterations than strive for perfection up front.
Once the model is up and running you can start to reap the benefits. Most businesses have to undergo a transition in thinking to looking at all decisions in terms of cash. It needs the CEO, the board and all functional and local leadership to get involved. Cash forecasting and management is not something that “somebody in finance” does in a silo. In distress, every business decision must have the cash implications clearly understood. I normally organise a short meeting with all senior management early each week to review the latest forecast and decide on appropriate actions.
These actions need to involve senior management. If a customer or supplier needs to be contacted then it should be somebody senior, preferably whoever has the best relationship. It is also essential that the messaging is agreed. Careless language can cause unnecessary alarm and be counter-productive in these situations. Everybody has to give a consistent message and it should be honest but positive. I have often sat in on such calls or attended face to face meetings with clients to help ensure that the message is correct and that any awkward questions can be answered.
Once a high quality TWCF is in place it makes such meetings easier. Whether you are speaking to a customer, a supplier, a bank, a trade union, whoever, you can communicate with confidence. You can promise something with the conviction that the commitment will be met rather than offering empty promises. This builds confidence with all stakeholders as you devise and implement plans to recover from the situation.
Directors themselves will also benefit from a TWCF on a personal basis. In most countries there are laws governing Director behaviour and when to file for insolvency, often with personal liability if breached. These are not meant to put the fear of god into directors but do need to be considered. A TWCF evidences what was known at the time, aids compliance with such laws and helps the board make decisions which are defendable if challenged later.
In the current COVID-19 crisis and for some time to come, more businesses than ever will be under financial pressure. Having a TWCF is essential and will give any business a much better chance of coming through the pressures of these unprecedented times. Without it you are flying blind. At BM&T, we are always happy to talk to anybody needing advice on cash flow forecasting. We don’t charge for an initial conversation and can often help with a light touch involvement that provides real value at an affordable price. As the government’s business department (BEIS) says: