Building a cash flow forecast model

| 5-9-2017 | Lionel Pavey |


No company can sort out its funding and investment requirements without having a cash flow forecast. This gives valuable insight into potential bottlenecks where there is a shortage of liquidity that needs to be addressed in order that the company can continue its day-to-day operations whilst optimizing its cash position.

2 methods

There are 2 methods to be a model – indirect and direct.

Indirect uses the balance sheet and, as such, will contain non-cash items like depreciation and bad debts. Direct uses the projected receipts and payments shown at specific moments in time.The indirect method is handy for long term forecasting beyond 1 year as it shows the money required to finance capital intensive investments and projects.

The direct method is essential for short term analysis up to 1 year as it shows the money for operational activities and working capital. As a cash flow forecast is mainly used for the direct needs of a company, it is prudent to use the direct method.

What steps need to be taken to transform a budget into a cash flow forecast via direct method?

  1. Adjust the budget to remove all non-cash items
  2. Analyse historical data to obtain seasonally adjusted cash flows for operational activities
  3. Integrate the standard payment terms for creditors and debtors and adjust the cash date
  4. If there are no clear trends within the month, spread the amount evenly over the month
  5. Where pay dates are hard – wages, taxes etc. input these into those dates
  6. Calculate the operational cash flow
  7. Incorporate expected investments
  8. Incorporate existing financing obligations (principal and interest)
  9. Never forget the BTW (VAT)!
  10. Analyse the forecast for shortfalls or periods of excess liquidity

As this is an exercise that incorporates all departments within a company, it is essential that full support is given by management to the design and implementation of the process. No one person can collect and collate all the data – this requires continuous input by controllers and treasury staff.

How to design the forecast?

  1. Establish clearly defined criteria and processes
  2. Define the role and cooperation required by all parties, whilst highlighting the benefits
  3. Ensure commitment from all parties
  4. 1 data source only – data must be presented in 1 format on agreed dates
  5. Structure – all data is delivered on time to a central point, normally the treasury
  6. Keep it simple – do not over design the model
  7. Give constant feedback to all stakeholders so that they can see how their contributions matter
  8. Question the validity of the data – is it created by a bottom-up approach or has a simple top-down approach been taken without looking at the individual components that make up the forecast
  9. Stress test the data – build simple scenarios (best and worse) whilst making simple assumptions such as debtors extending payment times, fall in sales, increase in demand etc.
  10. Never sit back and think that your task is done. This is a living model that needs to be constantly monitored and adjusted where necessary
  11. Do not punish – many people are reluctant to provide forecasts out of fear that they will be wrong. Use the model to educate and focus stakeholders onto the reality of their cash positions as opposed to their bookkeeping positions. It is all about timing
  12. Remember – if you do not have it, you can not use it. There is nothing more harmful for a company than running out of cash, regardless of what the company accounts are telling you!

If you want to know more about this topic you are welcome to contact me.

Lionel Pavey


Lionel Pavey

Cash Management and Treasury Specialist