Home health cash forecast
Cash by month for a home health agency paid by 30-day period: every period tested against its LUPA threshold, priced for a late notice of admission, billed after it closes and collected on each payer's measured clock — beside the old per-visit forecast, so the gap is visible in dollars and in months.
Built for Brightwater Home Health, an illustrative home health agency. The data is invented; the mechanics are not.
.xlsx · 7 sheets · 969 live formulas
540 inputs · 13 tie-outs · no macros
What the file finds
Three numbers the shortcut version never produces
- $42,331
- Promised by the old forecast by the end of August, not yet arrived
- $7,690
- Left on the table by LUPA periods
- $87,230
- Lowest closing cash in view
Cash!G35 · =D22
Exposure!C9 · =C7-C8
Cash!A35 · =MIN(C29:F29)
The mistake this model exists to catch
Cash tied to the visit or an invoice date. Under period payment the invoice date is neither when the work happened nor when the cash arrives.
The mistake this model exists to catch
Revenue recognized per visit, which reports a good month for a period that will be paid as a LUPA at a fraction of the rate — and a notice of admission filed late, which costs a slice of the payment for every day.
What you send
An export, and the rules of the business
- The 30-day periods from the EMR: payer, dates, the case-mix payment rate, the LUPA threshold, visits by discipline
- How each payer actually pays, measured from the remittance history
- Per-visit LUPA rates and the fully loaded cost of a visit by discipline, opening cash, and fixed overhead
Messy is fine. The paste grid on the right is what the export becomes: one row per period, every cell yellow because every cell is yours.
What comes back
- READMEread me first
- Inputs36 inputs
- Periods8 formulas
- Payment838 formulas
- Cash97 formulas
- Exposure12 formulas
- Checks14 formulas
What you send: the 30-day periods from the EMR
The paste grid. Twelve of the thirty-six periods shown.
| Period | Patient | Payer | Period start | First? | NOA filed | Period rate | LUPA thr. | SN | PT | OT | ST | HHA | MSW |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| BW-7001 | P-3104 | Medicare FFS | 06/05/2026 | Y | 06/08/2026 | $2,140 | 4 | 5 | 4 | 0 | 0 | 3 | 0 |
| BW-7002 | P-3110 | Humana Medicare Advantage | 06/06/2026 | Y | 06/09/2026 | $1,980 | 3 | 4 | 2 | 1 | 0 | 2 | 0 |
| BW-7003 | P-3097 | Medicare FFS | 06/08/2026 | N | 05/12/2026 | $2,410 | 5 | 6 | 5 | 2 | 0 | 4 | 1 |
| BW-7004 | P-3115 | UnitedHealthcare Medicare Advantage | 06/09/2026 | Y | 06/20/2026 | $2,630 | 4 | 5 | 6 | 0 | 0 | 3 | 0 |
| BW-7005 | P-3121 | Medicare FFS | 06/11/2026 | Y | 06/14/2026 | $1,720 | 2 | 1 | 0 | 0 | 0 | 0 | 0 |
| BW-7006 | P-3088 | Medicaid managed care | 06/12/2026 | N | 04/30/2026 | $2,050 | 3 | 3 | 3 | 0 | 0 | 2 | 0 |
| BW-7007 | P-3126 | Medicare FFS | 06/15/2026 | Y | 06/17/2026 | $3,120 | 6 | 7 | 6 | 3 | 1 | 4 | 1 |
| BW-7008 | P-3130 | Humana Medicare Advantage | 06/16/2026 | Y | 06/18/2026 | $2,270 | 4 | 4 | 3 | 0 | 0 | 2 | 0 |
| BW-7009 | P-3102 | Medicare FFS | 06/18/2026 | N | 05/22/2026 | $1,890 | 3 | 2 | 0 | 0 | 0 | 0 | 0 |
| BW-7010 | P-3134 | Medicare FFS | 06/19/2026 | Y | 06/22/2026 | $2,560 | 5 | 5 | 5 | 1 | 0 | 3 | 0 |
| BW-7011 | P-3139 | UnitedHealthcare Medicare Advantage | 06/22/2026 | Y | 06/24/2026 | $2,380 | 4 | 4 | 4 | 0 | 0 | 2 | 1 |
| BW-7012 | P-3141 | Medicare FFS | 06/23/2026 | Y | 07/06/2026 | $2,910 | 5 | 6 | 5 | 2 | 0 | 3 | 0 |
The workbook
Select any cell. The formula is the file's own.
These are excerpts of the sheets, with the values the file computes. Nothing on a calculation sheet is typed: the rates are looked up from Inputs, the months roll off one date, and every total has a second route to the same number.
Cash in the month it actually lands, against the old per-visit forecast
Receipts by payer, the old forecast beside them, then the roll-forward.
| Payer | Jul-26 | Aug-26 | Sep-26 | Oct-26 | Total | |
|---|---|---|---|---|---|---|
| Month key | 24,319 | 24,320 | 24,321 | 24,322 | ||
| Medicare FFS | $4,728 | $13,076 | $20,282 | $7,590 | $45,676 | |
| Humana Medicare Advantage | $0 | $1,980 | $4,750 | $4,650 | $11,380 | |
| UnitedHealthcare Medicare Advantage | $0 | $0 | $4,484 | $4,470 | $8,954 | |
| Medicaid managed care | $0 | $0 | $2,050 | $4,110 | $6,160 | |
| Receipts — all payers | $4,728 | $15,056 | $31,566 | $20,820 | $72,170 | |
| Still to land after the last month in view | $4,900 | |||||
| Periods landing this month | 3 | 8 | 14 | 9 | 36 | |
| WHAT THE OLD PER-VISIT FORECAST EXPECTED | ||||||
| Visits delivered (by period start month) | 167 | 100 | 0 | 0 | 267 | |
| Revenue the old forecast booked on those visits | $34,235 | $20,500 | $0 | $0 | $54,735 | |
| Cash the old forecast expected (visit + Old_Lag days) | $27,880 | $34,235 | $20,500 | $0 | $82,615 | |
| Old forecast — still to land after the last month in view | $0 | |||||
| Old forecast less what actually lands | $23,152 | $19,179 | ($11,066) | ($20,820) | $10,445 | |
| Cumulative — cash the old forecast has promised that has not arrived | $23,152 | $42,331 | $31,265 | $10,445 | ||
| CASH ROLL-FORWARD | ||||||
| Opening cash | $120,000 | $95,346 | $87,230 | $104,796 | ||
| Add: receipts | $4,728 | $15,056 | $31,566 | $20,820 | ||
| Less: cost of visits delivered | ($15,382) | ($9,172) | $0 | $0 | ||
| Less: fixed overhead | ($14,000) | ($14,000) | ($14,000) | ($14,000) | ||
| Closing cash | $95,346 | $87,230 | $104,796 | $111,616 | ||
| Below the minimum? | ok | SHORT | ok | ok |
In one picture
Same money. Different months.
The old forecast is not wrong about the quarter. It is wrong about August, and August is when payroll is due.
Table view
| Month | Period billing, each payer's clock | The old per-visit forecast | Difference |
|---|---|---|---|
| Jul-26 | $4,728 | $27,880 | $23,152 |
| Aug-26 | $15,056 | $34,235 | $19,179 |
| Sep-26 | $31,566 | $20,500 | ($11,066) |
| Oct-26 | $20,820 | $0 | ($20,820) |
The Checks sheet
Proved able to fail
The last sheet in the file is 13 tie-outs. Each reads OK on the recalculated file — which proves nothing on its own, because a check can be written so it cannot fail. So before this file shipped, a script typed a wrong number over 15 live formula cells, one at a time, and recalculated each copy.
15 of 15corruptions caught by a red check
The negative-control log
baseline: 13/13 checks OKcorrupt Payment @M7 col -> CAUGHT by C8 corrupt Payment @J10 col -> CAUGHT by C9 corrupt Payment @H10 col -> CAUGHT by C10 corrupt Payment @L9 col -> CAUGHT by C11 corrupt Payment @R7 col -> CAUGHT by C12 corrupt Payment @O7 col -> CAUGHT by C13 corrupt Payment @Q7 col -> CAUGHT by C6,C7 corrupt Payment @F9 col -> CAUGHT by C16 corrupt Cash Receipts — all payers col 4 -> CAUGHT by C14 corrupt Cash Closing cash col 5 -> CAUGHT by C14 corrupt Cash @D14 col -> CAUGHT by C7 corrupt Cash @D19 col -> CAUGHT by C15 corrupt Cash @D17 col -> CAUGHT by C18 corrupt Exposure @C9 col -> CAUGHT by C17 corrupt Exposure @C18 col -> CAUGHT by C17
| Tie-out | Result |
|---|---|
Receipts by month plus what lands later equals every expected payment The monthly grid did not drop, duplicate or misdate a period's cash. | OK |
Every period lands in exactly one cash month A payer typed differently from the Inputs table would look up to a zero lag and land in the wrong month; a broken date would land nowhere. This counts the periods that landed. | OK |
Expected payment re-derived from basis, rates and penalties The expected-payment column is rebuilt from its parts: LUPA periods at per-visit, the rest at the period rate less penalty. A number typed over any period's payment shows up here. | OK |
LUPA payments re-derived from the visit counts and the per-visit rates Visits by discipline on the paste grid, times the rate on Inputs, for every LUPA period — a second route to the same dollars. | OK |
LUPA flags agree with the thresholds on every period The flag column and the visit counts cannot disagree about how many periods fall short. | OK |
A penalty never exceeds its period's payment, and never applies to a LUPA The late-NOA reduction is capped at the payment and only applies to periods paid as periods. | OK |
Visit cost re-derived per discipline Visits by discipline on the paste grid times the cost on Inputs. A cost typed into a single period is caught. | OK |
Every period matched a payer's payment lag A payer name that does not match Inputs would collect on day zero — the most optimistic error a cash forecast can make. | OK |
Closing cash roll-forward agrees with a direct recompute from the periods Opening plus receipts less visits less overhead, month by month, equals the same figure rebuilt straight from the period list in one formula. | OK |
The old forecast's cash closes against its own revenue The comparison is honest: the old forecast is re-run in full, not caricatured. It differs from the actual only in WHEN the cash lands and in the periods it books at rates that will not be paid. | OK |
Visits per period re-added from the six discipline columns The visit total on Payment is re-summed straight off the paste grid. A typed-over total — which would also flip a LUPA decision — is caught. | OK |
The exposure figures re-derived from the Payment sheet The three dollar figures on Exposure are rebuilt from the period list in one formula, so a number typed over any of them is caught. | OK |
Visits in view, plus visits before and after it, equal the paste grid Every visit is counted exactly once: in a month in view, or before it, or after it. A typed-over cell in the monthly grid breaks the partition. | OK |
On your numbers
Have this built on your numbers
Forecast / budget vs. actual is included — like every other build type — on the one plan, at $1,950 a month with a 48 hours for reporting, 2–4 business days for models turnaround. A request like this one is the export above, the rules of the plan in plain language, and the question the model has to answer. The finished file comes back the same shape as this one: Inputs first, Checks last, and a README that explains itself.
How the money moves in home health and hospice agencies, and the other workbooks this business runs on: Home health & hospice.
The same care, on your numbers.
Every workbook is recalculated with a formula engine, its checks are proved able to fail, and a second pass re-derives the headline numbers before it ships.
Not ready? Get a free teardown of a sheet you already rely on.