A weekly cash forecast lists money in and money out, but the number a business owner needs is what is left in the bank each week, and when it is lowest. A running SUM with one end of its range locked carries every earlier week into the balance, and MIN with INDEX and MATCH then finds the worst week and names it.
You do the books for a landscaping company heading into its quiet winter months. The forecast in A2:C9 lists each week's expected receipts and payments, and the bank balance going in is in G1. In D2, write a formula for the closing balance at the end of that week, and copy it down to D9. Then in G2, find the lowest closing balance in the forecast, and in G3, return the week it happens, so you know how large an overdraft to arrange and when it has to be in place.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Week commencing | Receipts | Payments | Closing balance | Opening balance | 12400 | |
| 2 | 11/16/2026 | 9800 | 8600 | Lowest balance | |||
| 3 | 11/23/2026 | 7200 | 8900 | Week of lowest | |||
| 4 | 11/30/2026 | 6100 | 11450 | ||||
| 5 | 12/7/2026 | 5400 | 8200 | ||||
| 6 | 12/14/2026 | 4800 | 8300 | ||||
| 7 | 12/21/2026 | 3200 | 9650 | ||||
| 8 | 12/28/2026 | 2100 | 4300 | ||||
| 9 | 1/4/2027 | 11500 | 7900 |
A bank balance remembers. What is in the account at the end of the week of 12/21 depends on every week before it, so the formula in D2 has to keep reaching back to the first week however far down it is copied. $B$2:B2 does that: the locked top stays on row 2 and the unlocked bottom moves with the formula, so by D7 it reads $B$2:B7 and adds six weeks of receipts. Subtracting the same running total of payments and adding the opening balance in $G$1 gives the closing balance for every row from one formula. The tempting shortcut, =$G$1+B2-C2 copied down, looks right in the first row and then quietly forgets the past: every week starts again from $12,400, the worst week shows $5,950 still in the bank, and nobody arranges an overdraft. The real forecast goes overdrawn in the week of 12/21 and bottoms out at -$8,400 a week later. MIN(D2:D9) finds that low point, and INDEX(A2:A9,MATCH(G2,D2:D9,0)) finds where it sits in column D and returns the date in the same row. Notice that the first negative week and the lowest week are not the same: the overdraft has to be in place by 12/21, but it has to be big enough for 12/28.