Modelling in lockdown: Short Term Cash Flow forecasting

Rob Bayliss Rob Bayliss

Lots of businesses in recent weeks have found a need to prepare Short Term Cash Flow (STCF) forecasts, for their own benefit or to share with their funders.

Indeed, this four-letter initialism is suddenly part of business vocabulary. We are seeing a lot of enquiries regarding how to model STCF. Thankfully, it’s an area we are proud to excel at (pun intended). Here are a few practical modelling questions and prompts for your thinking:

How far forward to forecast? That depends on what you need to see or share, but the further out you go, the less useful or reliable it will be. Generally accepted good practice has settled over two decades on getting to a 13 week forecast, as this will always cross a full quarter of dates, so major payments like rent, VAT and corporation tax get included.

Will you forecast daily or weekly? By definition, monthly results won’t cut it here. Daily forecasting can mean a ton of extra work that may not create much more insight, so weekly projections are likely your best option. Every daily model we’ve seen over the years has tended to take more than a day to update or sensitise. If daily liquidity is a serious worry, then how about overlaying an extra page that just does the first two weeks on a daily basis?

Be ready to change format. It's possible most cash flow statements you've prepared before are in a format that starts with EBITDA or another profit number, and then makes adjustments. That approach suits longer term forecasts and the use by deal-makers; however, a STCF will want to adopt a 'receipts and payments' format, which may need some different maths.

Make it easy to ‘roll forward’ – chances are you’ll be doing it again next week, so build a spreadsheet structure that can easily move on from week to week. If you are doing 13 weeks, will you use the same 13 columns again in the next update and relabel them? Or will you move a column to the right, leaving the labels (and numbers) where they were? We favour the latter approach.

Modelling or typing? STCF models are often more of a template for gathering and reporting input cash flows rather than calculations of cash flow numbers from a set of input drivers. For example, your credit controller probably knows what cash receipts to expect from customers in the next month or two, so it’ll be more accurate to type these in instead of trying to estimate them using days, ratios or other spreadsheet maths.

You may need to change your financial statement forecasting methodology. Most businesses do a profit and loss forecast first (at least in an annual budget, and it’s often the first part done in a financial model) and then map it to cash flow and balance sheet second. For STCF, it’s very likely that you’ll need to work from your recent balance sheet first. For example, most of the cash payments you’ll be making over the next 2-3 months are likely to be in your trade creditors category now – so it’s easier to read down the purchase ledger and see what gets paid when, rather than look at a budget of costs and guess at the mapping.

Weekly forecasts need trends and variations you ignore in a monthly model. Purchase ledger payments and sales ledger receipts may smooth evenly across the weeks of a month (or may be back-ended), but lumps like salaries and rent need putting into the right week.

Weekends rarely match month ends, so it will be increasingly hard to reconcile your weekly forecast to the same period of your monthly forecast. Trying to do so is a rabbit warren – don’t go there. Of course, an overall sense-check for directional consistency is needed, but presumably, your STCF will be more detailed and more accurate than a ‘rules based’ forecast from your monthly planning model. Trying to reconcile the two will prove fruitless.

Pulling a Short Term Cash Flow forecast together is a new challenge needing different modelling and commercial expertise to make it useful and sustainable. We are helping businesses by providing both restructuring expertise and the financial modelling skills needed to manage cash and gain insight into the near future.

For more information get in touch with Rob Bayliss.

Liquidity, contingency and claims Find out more