There are many correct (and incorrect) ways to build a 13 week cash flow forecast. In this post we’ll talk about one way to do it right and break down the process into three distinct chunks:

1. The operational portion of the 13 week cash flow forecast.  This relates to the data sources, logic and mechanics of forecasting receipts and disbursements, using beginning cash as the starting point and flowing out the rest of it.
2. The borrowing base portion of the 13 week cash flow forecast.  This can get complicated, but let’s start on the basis of a simple Asset Backed Facility that gives you 50% availability on inventory and 90% availability on accounts receivable.  For the time being, we’ll ignore things like blocks, ineligible receivables and things like that (which in real life, you better not ignore). Some revolving facilities include machine and equipment and sometimes other asset categories – again, we’re going to keep it simple here.
3. Bringing it together and finding out how much liquidity you have (cash + borrowing capacity) so you can make decisions.

Operational components of a 13 week cash flow:

Forecasting Receipts = A/R Runoff + Receipts from Forecasted Sales:

What is your “normal DSO”?  If you’re an e-commerce business with credit card payments made to you it will likely be ‘0-2’ depending on your bank funding mechanism.  If you’re a “regular business” with a mix of small and large customers your DSO may be 45-55 days and if you work with large contracts or have complex criteria for customers to pay you the DSO of your business may be closer to 60-90 days.  The shorter your DSO, the most you’ll have to rely on forecasted sales since over the 13 week time horizon you’ll convert your existing A/R into cash well before the timeframe is up.

Oh yeah…what is DSO?  Normally, we take our accounts receivable at a point in time and divide that number by the average “daily” sales associated with that A/R.  For example, if your A/R is $5 million and your average daily sales are $250K, your DSO is 20. Ironically, depending on several factors it can be better or worse to have a longer DSO based on the circumstances you are facing and the industry/macro environment at the time.  

For this exercise you should also look closely at payment terms from your customers.  Some may have been paying you early to get a P&L benefit via early pay discount, but they might elect to stop that at any moment, so build your model in a way that is flexible and accommodates both scenarios for your top customers.

Some modeling hints:

1. Keep everything in one workbook – don’t have external links as they will only hurt you later as you try to trace the origin of elements of your model.
2. Do not hard code any values, except for maybe your opening balances – even those should really be sourced from an output of your ERP or accounting software that can be traced back to a date and time.
3. There are great online courses for basic cash flow modeling.  This is not meant to be an excel course…it is a guide to the underlying thinking necessary to produce a valid 13 week cash flow forecast you and your team can use to make decisions and communicate trade-offs between those decisions across your organization.

Ok, for the part in Excel:

1. Export your AR detail from your accounting software or ERP
2. Make sure your invoice date is part of the data you exported
3. Take historical payment speed for each customer, their payment terms and another column that lets you forecast some extension in those payment terms/payment speed
4. Now, with some 201 level excel skills, you should be able to create a “waterfall” (fancy term for a lot of rows that sums to one line at the bottom in this case) of receipts.
5. That one line will be the first line of your 13 week cash flow

Forecasting Disbursements: A/P Runoff + Disbursements from incurred expenses

1. Dump your last 90 days check register into excel
2. Export your AP and accrued liabilities detail from your accounting software or ERP
3. Identify all of the categories that make sense to break out individually in your forecast – these are typically:
   1. Product or Material Vendor payments (for materials) which should be broken down into critical vs. noncritical in times of distress
   1. Employee Payroll (direct and indirect, as well as corporate overhead)
   1. Other items such as rent, utilities, insurance, etc.
   1. If you are in the midst of restructuring or working with a third party advisor, their fees should also be accounted for here

There is a lot that can be written with respect to how to manage disbursements during normal and unusual circumstances. If you have questions, feel free to reach out via email with your own email address included and I’ll reach out directly.

The Art and Science of Forecasting:

You probably don’t have 13 weeks of receipts in your accounts receivable, and you probably don’t have 13 weeks of fixed required disbursements in your cash outflows either…so, you’re going to have to FORECAST!

This is where scenarios come in – don’t put the weight of the world on your back by coming up with only one forecast.  Use two or three scenarios that you think are logical for purposes of forecasting the near-term sales that will become tomorrow’s receipts.  Do the same for disbursements. 

You should learn how to use the switch or toggle functionality in Excel to build cases here and for the sake of brevity I’m going to assume you’ll google it if you aren’t already familiar.  Face it – none of your forecasts or scenarios are going to be correct. This is about using your best judgment to lay out two or three views of how he world may unfold and using those to develop plans or make near term decisions.

Now bring it all together: Beginning cash + Week ‘n’ receipts – Week ‘n’ disbursements = week n Ending cash.  Week ‘n’ Ending cash = Week ‘n+1’ Beginning cash, and so on and so forth until you get to week 13 Ending cash.

Building the Borrowing Base

Current and Forecasted Accounts Receivable:

Again, remember, I’m giving you the Easy way to do this…if you have ineligible receivables or need to factor in other limitations from your loan or credit agreements, do so now and get really familiar with what they are.  Big caveat, I know.

Assuming a perfect world…Today’s A/R ‘- minus’ what you receive in cash this week ‘+ plus’ what you sell this week = End of week A/R.  End of Week AR = Next Week Beginning AR.  In the real world, it is unlikely that you’ll want to do this at a totally summarized level. Look at your largest customers, most risky customers (from a credit standpoint), different lines of business that will behave differently from an A/R standpoint, etc.

Current and Forecasting Inventory:

Double caveat!!  Credit agreements usually state advance rates on raw materials, work in process and finished goods inventory.  If you’re going to do this in the big girl/boy way, you need to forecast these separately but for now, let’s assume a blended 50% advance rate on the whole kit and kaboodle.  You must read and understand your credit agreement. If you don’t, get on the phone asap with someone who does and prepare a cheat sheet of the major terms, definitions, calculations and requirements that are spelled out in the document. These are written by lawyers so they aren’t always the most exciting or clearly written documents but each word is usually there for a very specific, often highly negotiated reason so don’t skip things you don’t understand.

Your inventory Today, minus what you sold (the COGS portion) + what you purchased = your Next Period inventory.

Now…

You have 1.) your receipts and disbursements profile and 2.) your A/R and Inventory profile. In the super simple version of the world we’re living in here, we’re going to assume that your borrowing base is equal to the sum of your A/R and Inventory, and as example, that you can borrow 90% of your A/R and 50% of your Inventory.

Bringing it all Together:

From top to bottom: Start with your receipts, disbursements and change to cash on a weekly basis. Make it clear which portions of your receipts and disbursements come from forecasts vs. which are from receivables or payables already “on the books”. Use your balance sheet data to roll forward a borrowing base forecast. Finally, bring it all together with a few of your usable or accessible liquidity – this is an essential part of your 13 week cash flow forecast.

Cash forecasting and cash management brings together every discipline of corporate finance. You need to know your business, know your customers and suppliers and last but not least by any stretch – know your borrowing docs and credit agreements.