A profitable month can still contain a difficult Friday. Rent, payroll and suppliers may leave before customers pay. A 13-week cash-flow forecast in Excel makes that timing visible while there is still time to act.
BankSync can bring supported UK bank transactions into your workbook. You add the part a bank feed cannot know: future customer receipts, bills and the dates you expect them. The result is a rolling forecast with a clear boundary between money that moved and money you expect.
Give each worksheet one job
| Tab | What belongs there | Who maintains it |
|---|---|---|
| Transactions | Posted bank movements and source identifiers | BankSync feed |
| Commitments | Expected receipt or payment, due date, forecast date, status | You or your bookkeeper |
| Forecast | Thirteen weekly opening and closing balances | Excel formulas |
| Checks | Balance reconciliation and unexplained differences | Weekly reviewer |
Connect the actuals before forecasting
Save the workbook in OneDrive. In BankSync, connect the correct UK bank entity and your Microsoft Excel integration. In the feed's Destination tab, choose that workbook and a dedicated Transactions worksheet. Map date, amount, description, account and currency; retain transaction identifiers when available.
Run a first sync and inspect a known receipt and payment. Confirm the sign convention: in this example, inflows are positive and outflows negative. Keep GBP records separate from other currencies, and verify that dates are Excel dates rather than text. The Excel integration guide covers the connection and workbook picker.
Set a real opening-cash figure
Pick a cutoff, such as the end of Sunday. Record the closing balance of every bank account included in the forecast at that cutoff. Do not use the sum of downloaded transactions as the starting balance: the feed may contain only part of the account's history.
Write down the scope beside the number. A forecast covering one operating account must include transfers into or out of that account. A consolidated forecast covering both sides of a transfer should eliminate the internal movement. Credit-card purchases and the later payment of the card also need a consistent treatment; otherwise the forecast counts the same cash requirement twice.
Build the forecast columns
Lay out thirteen week-start dates
Use actual Excel dates across the header. Each column covers its start date up to, but not including, the next week-start date.
Enter expected receipts
List customer, amount, currency, invoice reference and expected cash date. Keep the invoice due date separately so a late-payment assumption is visible.
Enter expected payments
List rent, wages, supplier payments and other commitments on the date cash is expected to leave. Include only items relevant to the chosen account scope.
Calculate closing cash
Opening cash plus expected receipts minus expected payments equals closing cash. Carry that closing figure into the following week’s opening balance.
Mark uncertainty
Give uncertain receipts a status or scenario. An unsigned sale should not silently become the same kind of forecast item as a confirmed payment.
Illustrative forecast, GBP
| Week | Opening | Receipts | Payments | Closing |
|---|---|---|---|---|
| 1 | £12,000 | £5,000 | £8,000 | £9,000 |
| 2 | £9,000 | £3,000 | £7,000 | £5,000 |
| 3 | £5,000 | £6,000 | £4,000 | £7,000 |
Calculate by cash date, not invoice date
Suppose Commitments has an Excel table called Plan with CashDate, Amount and Direction columns. Amount contains positive values; Direction is either In or Out. If B1 contains the week start, weekly receipts can use:
=SUMIFS(Plan[Amount],Plan[CashDate],">="&B$1,Plan[CashDate],"<"&B$1+7,Plan[Direction],"In")
Use the same expression with "Out" for payments. Add a currency criterion if Plan contains more than GBP. Microsoft documents the argument order in SUMIFS. Keep every criteria range aligned to the same table.
These formulas summarise your assumptions; BankSync does not generate future invoices or guarantee collection dates. Use conditional formatting to highlight a closing balance below your chosen operating floor, then investigate the underlying commitments.
Test the week that matters
In the example, delay the £3,000 receipt from week 2 to week 3. Week 2 closing cash falls from £5,000 to £2,000; week 3 returns to £7,000 if everything else stays unchanged. The eventual total is the same, but the temporary cash position is very different.
Keep a base case and a delayed-receipt case. Record which invoice moved and why. That makes the conversation concrete: chase a payment, change a spending date where possible, or reassess a commitment. A colourful chart without those assumptions cannot explain the risk.
Roll the model forward every week
At the next cutoff, reconcile actual bank movements and closing balances. Explain the difference between forecast and actual: timing, amount, missing commitment or duplicate. Preserve the previous forecast if you want to learn how accurate your assumptions were.
Drop the completed week, append a new thirteenth week, and update only the assumptions that changed. Check bank consent, feed history and the workbook's latest received date before calling the model current. The first sync can only supply history the bank makes available.
For supporting purchase evidence, build the linked Transactions and Receipts workflow. Keep tax classification and filing decisions with the appropriate professional workflow.
Reviewed: 1 November 2026. Product references: UK bank setup, transaction handling, and Excel setup.