Outgrace
OutgraceThe Leadership Magazine, home
Capital & FinanceHow-to guide

How to build a 13-week cash forecast that survives a bad quarter

A rolling forecast with late-payment and slow-sales columns, and trigger points that name the action to take. Practitioner judgement, with the limits of the sources stated.

A business that loses money for one quarter can often survive it. A business that runs out of cash in week six usually cannot. A 13-week cash forecast is the tool that separates those two outcomes: a week-by-week view of money coming in and going out, looking three months ahead, and rebuilt each week against what actually happened. It is not an accounting statement and it does not replace one. It answers a narrower question, which is the only question that matters in a bad quarter: on which date does the bank balance fall below what the business can tolerate? The World Bank's financial inclusion pages describe the unmet credit need of micro, small and medium enterprises in developing economies as running into the trillions of dollars. Firms that cannot borrow have to hold their own buffer, and the forecast is how they see that buffer shrinking before it is gone.

Before you start

Three things must be true before the first column is typed. A named person owns the forecast and answers for its accuracy each week, not just for the spreadsheet. The business agrees one definition of cash, usually the balance the bank reports, and the forecast states whether it counts undrawn overdraft or committed facilities. And someone has the receivables ledger and the payment calendar in front of them, not a memory of them. In our experience, the first forecast takes two days to build and every later one takes about two hours. Most of that difference is the time spent finding the inputs.

A note on sources, since it bears on how much weight to give what follows. We reviewed the published material of the European Central Bank, the Bank for International Settlements, the U.S. Federal Reserve and the World Bank for treasury guidance on short-term cash forecasting for firms, and for guidance from small-business finance regulators. None of the pages we reviewed sets out a forecasting method, a scenario approach or trigger guidance for companies. The method below is practitioner judgement. Test it against your own bank's expectations and your auditor's view before anyone relies on it in a board pack.

  • Opening balances for every bank account, reconciled to the latest statement, with the date beside each figure.
  • The receivables ledger, aged by invoice, with each customer's record of paying on time or late.
  • The payment calendar for the next 13 weeks: payroll dates, rent, supplier terms and tax due dates.
  • Committed repayments, lease payments and any facility limits, taken from the signed documents rather than from a summary.
  1. Step 1

    Fix the opening cash

    Start from the bank balance on the day you build the forecast, account by account. Most forecasts that drift within a month begin with an opening figure taken from the management accounts, which carry accruals the bank has not yet seen. Reconcile to the statement first, even if that takes an afternoon. Everything after this line inherits its errors.

  2. Step 2

    Map receipts by customer

    List expected receipts customer by customer and week by week, using invoice due dates rather than the sales plan. A handful of large customers usually decide the shape of the quarter, so give each of them its own line. Where a customer has paid late before, move the expected date to when cash actually arrived, not to when the contract says it should. The gap between those two dates is where bad quarters start.

  3. Step 3

    Schedule every payment

    Put payroll, rent, tax, loan repayments and the largest supplier runs on their real dates. Group the small items, but do not spread the big ones evenly across weeks to make the sheet look tidy. That habit understates the one or two weeks in which most of the quarter's outflows fall due, and those are the weeks a bank will test first.

  4. Step 4

    Add the stress columns

    Run the base case beside two stress columns. In the first, the largest customers pay a set number of weeks later than their due dates. In the second, sales come in below plan by a set percentage, with costs that follow sales left as they are. Choose the delay and the shortfall from your own history, not from a published rate. The stress columns are not predictions. They show how early cash runs short under each condition, and that week is the number the board needs.

  5. Step 5

    Set the trigger points

    A trigger is a fixed balance or date that starts a named action. For example: if projected cash in any week falls below the agreed minimum, the finance lead calls the bank that week and the chief executive reviews discretionary spend. Write the threshold and the first action on the same line, and name the person who owns each action. Set the triggers in calm weeks. In a bad quarter, people argue about whether the number has been reached instead of acting on it.

  6. Step 6

    Roll it every week

    Each week, drop the week that has passed, add a new thirteenth week, and replace forecast figures with actuals. Record the variance for receipts and payments separately, and note which customer or supplier caused it. A forecast that is never compared with what happened teaches nobody anything. The variance log tells you which assumptions to distrust, and it is the record you will want if the bank asks how the forecast was made.

The mistakes that break the forecast

The most common failure is optimism that has been turned into a column. A sales team forecasts receipts at the rate it hopes for, the base case is built on that rate, and the stress columns are added afterwards with small delays that nobody believes. Ask who built each input and how often they have been right. A second failure is a forecast that lives in one person's spreadsheet. When that person is on leave, the bank balance stops being updated, and the triggers quietly stop being watched. Name a deputy who can roll the model in the owner's absence, and test the handover once before you need it.

The third failure is the trigger with no owner. A threshold everybody knows and nobody has been asked to act on is decoration. The fourth is a forecast that never meets the facility terms. Many businesses model cash carefully and then check their borrowing limits against a summary in a board paper. The limits, and the definitions that decide what counts toward them, sit in the signed agreements. Read the definitions page before the headline terms, and take any question about what the agreement requires to your lender's documentation and to a lawyer or accountant who has read it. That reading is not something a forecasting template can do for you.

Who should own the forecast?

One named person, usually the finance lead or the chief financial officer, with a deputy who can update it when that person is away. The owner answers for the variance between forecast and actual, not for the formulas in the sheet. Review the owner each quarter if the variance keeps coming from the same source.

How do I choose the size of the stress?

Use your own record. Look back over the last eight to twelve quarters and find the longest delay each major customer has taken and the largest shortfall against plan. Ask the sales lead and the credit controller to agree those figures in writing. If the history is short, use the worse of the two observations and say so in the pack, so that nobody mistakes a guess for a measured figure.

Do I need to tell the bank about a tight forecast?

Read your facility agreement before deciding, and ask your adviser whether it sets any reporting duty. Many business lenders expect early warning, and a conversation before a breach is usually easier than one after it. Whether and how to contact a lender is a question for a lawyer or accountant who has seen your documents, not one this guide can settle.

Is 13 weeks long enough?

For most of the decisions a business faces in a quarter, yes: payroll, supplier terms, collection effort and whether to draw on a facility. For longer commitments, such as capital projects or multi-year leases, keep a separate annual plan. Do not stretch the weekly model beyond the period over which its inputs are reliable.

This week, do three things. Reconcile the opening bank balances to the statements and write down the date. Put the five largest expected receipts and the next four weeks of payments into a single sheet, with due dates rather than hopes. Then write down the two triggers that would make you act, with the first action beside each and the name of the person who will take it. If building that sheet takes more than a day, the delay tells you something about your ledgers and your payment calendar. It is better to learn that now, while the quarter is still calm.

Newsletter

The Briefing

One email a week: the leaders we profiled, the ideas worth stealing, and nothing else.

Weekly, free, one click to leave. Read our privacy policy.