Imagine a business that is forecast to grow. If things were going spectacularly well and the business expected to double sales in, say, five years’ time, by that point the business would be doing twice as much work. The amount of money owed at the end of a month by customers would double. To achieve this level of sales, the business will have purchased twice as much from its suppliers, who it likely has to pay before the cash comes in from customers. That imbalance would also double.
This, in a nutshell, is working capital. Growth is not all good – it costs you in cash. The forecast can be generated from a model, with growth rates, new customers and assumptions showing the revenue story in a Profit and Loss account. That model also needs to show how the resulting working capital needs change, and that can be much harder than it sounds.
My colleague Alex Mumford has previously explained the relationship between working capital and cash. A growing company should play working capital dynamics to its advantage, and a good model will help to understand the possible impact.
Start simple-ish
For the above example, we can estimate the growth in debtors by looking at recent trends and applying a pattern forward. Work out the debtor days (‘Days Sales Outstanding’: roughly how long, on average, your customers take to pay) and use this to calculate the forecast debtors. The overall picture of "double sales equals double debtors" is too-simple maths, but using a ‘counting backwards’ method, which looks at the last few months’ sales, will be a good start. Don’t forget to add VAT, if necessary.
This simple approach may miss key risks, however. What if the new business is overseas, in another currency or with larger customers that take longer to pay? What if new lines of business have different requirements that delay invoices to customers? And what if the business doesn’t just sell widgets, but has contract by contract dynamics with billing at milestones over time? Getting a model that can show the impact of these will help hugely in your planning, but is easier said than done.
What about liabilities?
Starting simple, using an analogous approach could work well for modelling your payables to your suppliers. Look at creditor days (‘Days Payables Outstanding’) and count backwards again for your last few months’ costs. Remember VAT, and make sure you don’t include payroll or other inappropriate costs in your creditor modelling maths.
With some other liabilities (eg, tax, VAT), the payment forecast may follow fairly predictable behaviour, but each one needs separate thought. Invest in model planning and functionality to show how these move. Figure out which ones have the biggest impact and focus modelling on these. Rent is often paid quarterly in advance. Business rates may be payable over ten months, not twelve. Your insurance premium could be a big lump upfront, once each year, and your staff bonuses a big lump in arrears once a year. If these double, the impact on your cash flow will be tremendous. Make sure your model helps you see them coming and when the business should expect peaks and troughs in its cash balance.
Taking stock
So far, we didn’t mention stock (inventories of materials or goods). For many businesses, this is a big number on the Balance Sheet, and counts towards a big working capital commitment. Grow the business and you likely grow the stock holding. That costs cash, and you might need somewhere new to store all your stock.
Modelling how stock holdings change during the forecast can be very tricky. It’s usually in advance of sales growth and new customers. Is it seasonal? Does the factory shut down in the summer? Do new product lines need longer to manufacture and mean a higher ‘work in progress’ balance to predict? Lots of questions loom, and your financial model can shed light on some of the answers. It’s a can of worms though: remember that the model we are thinking of needs to be flexible and accessible enough to quickly look at changes to the business plan, not be a monolith aiming to do detailed weekly production planning for your factory controller.
As a simple-ish start, we can use a stockdays ratio (‘Days Inventory Outstanding’ to complete our bingo card of working capital jargon) and calculate using the costs of goods sold. However, this one needs to look forward, not backwards, as you hold stocks based on what you are going to sell, not what went out the door already.
A bonus point here: if you are modelling stock with some degree of rigour, then you can link your creditors to its movements. You are usually liable for the stock when you get it into the warehouse, not when you sell it, so you can increase the accuracy and responsiveness of your model.
It's a guess, but make it the smartest-possible guess
In real-life financial modelling, looking at forward working capital requires a bit of guesswork. Make sure that your Excel model helps make that guesswork smarter. In particular, think about the key cash dynamics that come from your business and try to make the model mimic them. That way, you get much greater insight into the risks your expected results are exposed to, should assumptions change.
To discuss financial forecasting in more detail, contact our team.
This post is part of a series: How confident are you about your financial forecasts >>
