Build a 13 Week Cash Flow Forecast Your MSP Can Update

Set Up the Weekly Columns

Put row labels in column A and the 13 weeks in columns B through N. Give every weekly column a clear date range. Use the same cutoffs when collecting bank actuals, so a payment doesn't fall into a different week merely because somebody prepared a different report.

Choose a start date and enter the opening cash at that date in cell B2. Reconcile that amount to the bank records and make the treatment of clearing transactions explicit. Keep any funds you cannot use for general operating payments separately identified.

Don't subtract an unpaid bill from opening cash and then enter it again in the forecast. If it hasn't left the starting balance, it belongs on its expected payment date. The beginning balance and future transactions need to meet without an overlap.

Use Rows That Match the Bank Movements

I'd begin with the following layout. Add detail to the transaction list when you need it, while keeping the weekly summary short enough to inspect. You can adjust the categories without changing the opening-balance and closing-balance logic.

Use actual expected cash amounts. Debt repayments, capital purchases, and distributions belong in the forecast when paid even though the profit report treats them differently from ordinary operating expenses. Have the finance owner confirm the categories and payment dates from your records.

Include employer payments that settle separately from net payroll. Use the payroll and payment schedules rather than a salary-burden estimate. The costing allowance used to price labor isn't another cash bill to add to payroll.

Make the Formulas Easy to Check

With payments entered as positive amounts, cell B9 is SUM(B4:B8), and B18 is SUM(B11:B17). Net movement in B20 is B9-B18. Closing cash in B21 is B2+B20. Those formulas keep the sign convention visible: payments are subtracted once.

Set C2 equal to B21, then carry that relationship across the remaining columns. Copy the subtotal and closing-balance formulas across as well. Check that each opening balance points to the previous column's closing balance, not back to the original bank amount.

Keep formulas in the total rows and inputs in the receipt and payment rows. If you need an adjustment, identify the underlying transaction or add a clearly described entry. Don't overwrite a closing-balance formula to make it agree with an expected answer.

A weekly closing balance can hide a payment due before a receipt in the same week. When a balance looks tight, inspect the dated transactions by day. The weekly sheet is the summary; the dates behind it decide whether a particular payment is funded.

Keep Evidence Behind Each Input

In the transaction tab, record the client or supplier, invoice or commitment reference, amount, expected clearing date, category, and the reason for that date. Name who will resolve an uncertain receipt or payment. Preserve the distinction between a confirmed arrangement and a request still awaiting agreement.

For recurring managed fees, start with actual invoices and payment arrangements. The US model's average managed client generates $4,500 in monthly revenue. That figure doesn't establish when the cash arrives, so don't divide it evenly across weekly columns unless the actual collection arrangement works that way.

Keep hardware receipts and distributor payments separate. The client may pay at a different time from the supplier, even when the order has a positive margin. Netting them into one expected profit entry removes the timing gap you need to inspect.

Avoid counting a transfer between your own bank accounts as new business cash. Decide which accounts the forecast covers, then reconcile movements across that boundary. The same funds shouldn't appear as both retained opening cash and another external receipt.

Change a Receipt in a Separate Scenario

Copy the working forecast and label the assumption you are changing. Say the hypothetical model client has a $4,500 receipt expected in a particular week, but an unresolved invoice issue gives you reason to expect it later. Move the full receipt to the date you now consider credible.

The original week has $4,500 less cash coming in, and the later week has $4,500 more. The revenue hasn't necessarily changed; the cash timing has. Recalculate every closing balance in between rather than looking only at the final column.

Keep supplier and payroll dates unchanged unless there is a separate reason to move them. If you ask for an extension, leave it unconfirmed until agreed. A spreadsheet can move a payment more easily than a supplier will.

Use the resulting low point to identify the obligation and date that need attention. Don't hide the base case by replacing every expected date with the worst date imaginable. Keep the alternatives and their reasons inspectable.

Replace the Finished Week With Actuals

At each update, preserve the previous forecast, then enter the actual receipts and payments from reconciled bank records. Compare them with what you expected. Explain whether a difference came from timing, amount, omission, or a genuine change in the business.

Carry the actual closing cash into the next opening balance. Add a new week at the far end to maintain the rolling view, checking the formulas in the new column. Keep obligations beyond the current horizon visible in the supporting records so they can enter the forecast before they fall due.

Assign the update to a person, with a review arrangement that fits your payment cycle. Give collections, supplier-date questions, and missing payroll information to the people who can resolve them. A question in a cell needs an owner as much as a task on the service desk does.

Use the Balance to Make a Dated Decision

The model targets 20% EBITDA after fair-market owner pay and treats 10% as a danger line. Neither percentage determines a bank balance. Keep the earnings view beside the cash sheet so repeated funding gaps can be investigated without assuming they're all collection problems.

I'd finish an update by recording the lowest projected cash point, the transactions responsible, and the confirmed action the owner will take. Reconcile the first opening balance, check the roll-forward formulas, and give the uncertain dates to named people. That's a forecast you can maintain as the bank movements arrive.