In my Excel model I changed the first receipt month from 3 to 4. The receipt stayed in month 3. Which formula should I inspect?
propfessor.Companion exampleThe short answer
Inspect the first IF test in the receipt formulas. It must compare the month in row 11 with $B$4. If the formula compares with a fixed 3, changing B4 cannot move that receipt. Repair the reference and check it across the month columns, rather than typing over the result.
F$11 = $B$4Change the month. Check the reference.
Work through it
- For month 4, inspect Practice!F12: the first test should be F$11=$B$4.
- Keep the separate month-6 receipt term intact. The working formula is =IF(F$11=$B$4,$B$5,0)+IF(F$11=6,$B$6,0).
- With B4 set to 4, verify F12 = £600,000, B19 = £750,000 and B18 = £200,000 in the supplied workbook.
| First receipt month | Peak cash shortfall | Final cash surplus |
|---|---|---|
| 3 | £500,000 | £200,000 |
| 4 | £750,000 | £200,000 |
| 6 | £1,000,000 | £200,000 |
Original six-month model: £1.2m total receipts, £1m costs, no opening cash. Excludes financing, interest, tax, fees and intramonth timing. Shortfall is not a lender’s debt requirement.
You can locate a broken input link, then verify the repair in the output.
What if the formula already points to B4?
Check that the input is a number and that calculation is current. In desktop Excel, inspect the calculation options; Automatic is the normal default. Also check for other hard-coded cells in the chain.
Authored follow-up · No question credit used