Most businesses use excel for their cash flow forecasting with vastly varying degrees of complexity and accuracy. Whether you use excel or you are using a cash flow forecasting application, we have highlighted several insights to consider in order to improve the accuracy and usefulness of your cash flow forecast. The better you forecast your cash flows, the lower the risk of experiencing a funding crisis and the better your return on capital.

Forecast in daily buckets. Many businesses perform a monthly forecast and some use weekly buckets. But I would strongly recommend your forecast reflect daily cash flows on a rolling 12-month basis. You can then summarise the forecast on a weekly or monthly basis as required for reporting purposes.

Link and reconcile your outstanding purchase and sales orders to the forecast. Use the associated payment terms to determine when the cash will flow. This provides more clarity to your forecast in the short to medium term. If you can automate and update this daily that is first prize, but if not, at least weekly. You can then blend in your budgeted or updated sales and purchase forecasts, but these must be verified with the sales and purchasing departments and should also be stress tested, especially in the short to medium term. Many businesses just use budgeted or estimated forecast figures for sales and purchases in their cash flow forecast. This can lead to some unwanted surprises and panic finance raising.

Link and reconcile your actual accounts payables and receivables to the forecast based on the associated payment terms. Then move the transactions to when they are really expected to flow. The involvement of your creditors and debtors departments will be required for this as they will know which debtors pay late, for example.

Update for actual balances and cash flows on a daily or weekly basis, depending on your funding sensitivity. And keep the actual history. This will enable you to analyse what actually happened so that you can improve your forecasting accuracy going forward. With the continuing advances in AI technology, if you have the history, you will be able to let AI do the analysis and provide forecasting insights for you.

Include foreign currency flows in their actual currency and apply the relevant exchange rate to translate them into your home or funding currency for forecasting purposes. Where you forecast in your funding currency you may have forecast based on an outdated exchange rate. This could lead to significant inaccuracies in your funding requirements. Setting the forecast up this way also enables you to perform stress testing. By simply adjusting the exchange rate input you will immediately be able to see the impact on your cash position.

Where you make use of forward exchange contracts that you early deliver or extend (currency swaps), include the actual future flows in the forecast. This will ensure that you aren’t blindsided by the associated interim cash flows.

Ringfence capital expenditure cashflows. If you do your capital budgeting properly, you would have determined the associated cashflows and associated timings in order to determine project viability. This would have included determining how the project will be financed and the associated cost of funding. By ringfencing each capex project, even if it is to be funded out of operating cash flows, will significantly improve your project cost control and enable you to determine whether or not it is capex spend that is constraining your working capital.

Keep a buffer for unexpected timing. This is intuitive and most businesses do, but the better your forecasting accuracy, the lower this buffer and associated financing cost will need to be.

Cash flow management is fundamental to the sustainability and success of every business. By continually questioning the accuracy and agility of your cash flow forecasting tools and methodologies you will increase your growth potential and return on investment.