Case costing by CPT and payer
Every case at a surgery center costed on its own: the facility fee that payer actually pays for that CPT, the standard supplies, the implant that case consumed and the OR time it took — then the same cases the way most centers model them, so the difference is visible in dollars.
Built for Larkspur Surgery Center, an illustrative ambulatory surgery center. The data is invented; the mechanics are not.
.xlsx · 7 sheets · 1,106 live formulas
385 inputs · 14 tie-outs · no macros
What the file finds
Three numbers the shortcut version never produces
- 6 of 40
- Cases that lose money
- ($3,699)
- Lost on those cases
- 5
- Loss-making cases the average hides
By CPT and payer!A55 · =COUNTIF(Costing!$M$6:$M$45,"LOSS")&" of 40"
By CPT and payer!C55 · =SUMIF(Costing!$K$6:$K$45,"<0")
Averages!A31 · =COUNTIFS(Costing!$M$6:$M$45,"LOSS",Costing!…
The mistake this model exists to catch
Revenue modeled as cases times an average rate. The same knee replacement pays $9,150 under one contract and $21,500 under another; an average puts money on Medicare cases that Medicare does not pay.
The mistake this model exists to catch
Implants averaged into supply cost. A $6,900 implant belongs to the case that used it. Spread across the service line, every case looks profitable; costed to the case, the ones that lose money have names and dates.
What you send
An export, and the rules of the business
- The case log: one row per case, with the CPT, payer, surgeon, actual OR minutes and the implant invoice
- Contracted facility fees by CPT and by payer
- Standard supplies and OR minutes per procedure, and what a minute of a running room costs
Messy is fine. The paste grid on the right is what the export becomes: one row per case, every cell yellow because every cell is yours.
What comes back
- READMEread me first
- Inputs24 formulas
- Cases3 formulas
- Costing731 formulas
- By CPT and payer223 formulas
- Averages110 formulas
- Checks15 formulas
What you send: the case log and the implant invoices
The paste grid. Twelve of the forty cases shown.
| Case ID | Date | CPT | Payer | Surgeon | OR minutes | Implant cost |
|---|---|---|---|---|---|---|
| LS-2041 | 04/01/2026 | 29881 | Blue Cross PPO | Dr. Okonkwo | 52 | $0 |
| LS-2042 | 04/02/2026 | 27447 | Medicare | Dr. Halvorsen | 138 | $6,900 |
| LS-2043 | 04/02/2026 | 64483 | UnitedHealthcare | Dr. Reyes | 18 | $0 |
| LS-2044 | 04/06/2026 | 66984 | Medicare | Dr. Bhatt | 24 | $165 |
| LS-2045 | 04/07/2026 | 29827 | Blue Cross PPO | Dr. Okonkwo | 92 | $1,150 |
| LS-2046 | 04/08/2026 | 47562 | UnitedHealthcare | Dr. Reyes | 78 | $0 |
| LS-2047 | 04/09/2026 | 66984 | Blue Cross PPO | Dr. Bhatt | 26 | $165 |
| LS-2048 | 04/13/2026 | 29827 | Medicare | Dr. Okonkwo | 95 | $1,720 |
| LS-2049 | 04/14/2026 | 27447 | Blue Cross PPO | Dr. Halvorsen | 128 | $5,050 |
| LS-2050 | 04/15/2026 | 64483 | Medicare | Dr. Reyes | 20 | $0 |
| LS-2051 | 04/16/2026 | 29881 | Workers' Comp | Dr. Okonkwo | 58 | $0 |
| LS-2052 | 04/20/2026 | 66984 | Medicare | Dr. Bhatt | 27 | $620 |
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.
Contribution per case, by CPT and by payer
The losing cells are the answer to which contracts to renegotiate.
| Procedure | CPT | Medicare | Blue Cross PPO | UnitedHealthcare | Workers' Comp | All payers |
|---|---|---|---|---|---|---|
| Knee arthroscopy with meniscectomy | 29881 | $766 | $2,956 | $2,655 | $4,223 | $2,387 |
| Arthroscopic rotator cuff repair | 29827 | ($394) | $3,788 | $3,039 | $5,940 | $2,628 |
| Total knee arthroplasty | 27447 | ($722) | $9,388 | $7,472 | $13,071 | $5,308 |
| Lumbar epidural steroid injection | 64483 | $107 | $742 | $588 | $942 | $512 |
| Cataract removal with lens implant | 66984 | $113 | $1,042 | $884 | $1,359 | $629 |
| Laparoscopic cholecystectomy | 47562 | $380 | $4,132 | $3,450 | $5,800 | $2,932 |
In one picture
The cases that lose money have names
Averaged into a service line, every case looks like the average. Costed on its own, the losses are specific cases under specific contracts — the list a renegotiation starts from.
Table view
| Case | Procedure · payer | Contribution |
|---|---|---|
| LS-2067 | Total knee arthroplasty · Workers' Comp | $13,071 |
| LS-2049 | Total knee arthroplasty · Blue Cross PPO | $9,488 |
| LS-2074 | Total knee arthroplasty · Blue Cross PPO | $9,289 |
| LS-2055 | Total knee arthroplasty · UnitedHealthcare | $7,472 |
| LS-2058 | Arthroscopic rotator cuff repair · Workers' Comp | $5,940 |
| LS-2078 | Laparoscopic cholecystectomy · Workers' Comp | $5,800 |
| LS-2051 | Knee arthroscopy with meniscectomy · Workers' Comp | $4,223 |
| LS-2059 | Laparoscopic cholecystectomy · Blue Cross PPO | $4,132 |
| LS-2045 | Arthroscopic rotator cuff repair · Blue Cross PPO | $3,822 |
| LS-2077 | Arthroscopic rotator cuff repair · Blue Cross PPO | $3,755 |
| LS-2072 | Laparoscopic cholecystectomy · UnitedHealthcare | $3,466 |
| LS-2046 | Laparoscopic cholecystectomy · UnitedHealthcare | $3,433 |
| LS-2064 | Arthroscopic rotator cuff repair · UnitedHealthcare | $3,039 |
| LS-2041 | Knee arthroscopy with meniscectomy · Blue Cross PPO | $2,972 |
| LS-2068 | Knee arthroscopy with meniscectomy · Blue Cross PPO | $2,939 |
| LS-2061 | Knee arthroscopy with meniscectomy · UnitedHealthcare | $2,655 |
| LS-2079 | Cataract removal with lens implant · Workers' Comp | $1,359 |
| LS-2065 | Cataract removal with lens implant · Blue Cross PPO | $1,059 |
| LS-2047 | Cataract removal with lens implant · Blue Cross PPO | $1,026 |
| LS-2069 | Lumbar epidural steroid injection · Workers' Comp | $942 |
| LS-2057 | Cataract removal with lens implant · UnitedHealthcare | $893 |
| LS-2071 | Cataract removal with lens implant · UnitedHealthcare | $876 |
| LS-2066 | Laparoscopic cholecystectomy · Medicare | $817 |
| LS-2054 | Knee arthroscopy with meniscectomy · Medicare | $783 |
| LS-2073 | Knee arthroscopy with meniscectomy · Medicare | $750 |
| LS-2056 | Lumbar epidural steroid injection · Blue Cross PPO | $742 |
| LS-2043 | Lumbar epidural steroid injection · UnitedHealthcare | $588 |
| LS-2075 | Lumbar epidural steroid injection · UnitedHealthcare | $588 |
| LS-2080 | Total knee arthroplasty · Medicare | $422 |
| LS-2062 | Cataract removal with lens implant · Medicare | $256 |
| LS-2044 | Cataract removal with lens implant · Medicare | $239 |
| LS-2076 | Cataract removal with lens implant · Medicare | $223 |
| LS-2050 | Lumbar epidural steroid injection · Medicare | $115 |
| LS-2063 | Lumbar epidural steroid injection · Medicare | $99 |
| LS-2053 | Laparoscopic cholecystectomy · Medicare | ($57) |
| LS-2052 | Cataract removal with lens implant · Medicare | ($265) |
| LS-2048 | Arthroscopic rotator cuff repair · Medicare | ($317) |
| LS-2070 | Arthroscopic rotator cuff repair · Medicare | ($471) |
| LS-2042 | Total knee arthroplasty · Medicare | ($1,177) |
| LS-2060 | Total knee arthroplasty · Medicare | ($1,410) |
The Checks sheet
Proved able to fail
The last sheet in the file is 14 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 13 live formula cells, one at a time, and recalculated each copy.
13 of 13corruptions caught by a red check
The negative-control log
baseline: 14/14 checks OKcorrupt Costing @K7 col -> CAUGHT by C13,C18 corrupt Costing @F7 col -> CAUGHT by C9 corrupt Costing @J7 col -> CAUGHT by C14 corrupt Costing @M7 col -> CAUGHT by C16 corrupt Costing @P7 col -> CAUGHT by C18 corrupt Costing @H7 col -> CAUGHT by C10 corrupt Costing @I7 col -> CAUGHT by C10 corrupt Costing @G7 col -> CAUGHT by C11 corrupt By CPT and payer @C9 col -> CAUGHT by C6,C9 corrupt By CPT and payer @C31 col -> CAUGHT by C7 corrupt Averages @E9 col -> CAUGHT by C19 corrupt Averages @E20 col -> CAUGHT by C18 corrupt Averages @I9 col -> CAUGHT by C12
| Tie-out | Result |
|---|---|
Every case lands in the grid exactly once The six-by-four count grid totals the case list. A CPT or payer typed differently from the Inputs tables would fall out of every cell, and this would say so. | OK |
Contribution in the grid equals contribution on the case list The bucketing did not drop or double-count a case. | OK |
Revenue in the grid equals the facility fees on the case list Same test on the revenue grid. | OK |
Facility fees re-derived from the case counts and the contract table How many cases in each cell, times what the contract pays for that cell, equals the fees on the case list. A fee typed over on the Costing sheet is caught. | OK |
Minutes and implants on the costing sheet equal the case log The two columns copied off the paste grid still agree with it. A number typed over either one on Costing shows up here. | OK |
Standard supplies re-derived from the procedure table Cases per CPT times the standard supply cost on Inputs. A supply figure typed over on the Costing sheet is caught. | OK |
What the average fee invents is re-multiplied from its two columns The headline on the Averages sheet is rebuilt from overstatement per case times Medicare cases. | OK |
Contribution re-derived from fee, supplies, implant and OR cost The contribution column is rebuilt from its four ingredients. A number typed over any case's contribution shows up here. | OK |
OR cost re-derived from minutes and the rate Every case's room cost is minutes times the one rate on Inputs — a rate typed into a single case is caught. | OK |
Every case matched a contract fee and a procedure A CPT or payer that does not match the Inputs tables looks up to nothing and would price the case at zero. This counts those. | OK |
LOSS flags agree with the numbers The flag column and the contribution column cannot disagree about how many cases lose money. | OK |
Implants only on procedures that have one An implant invoice against a knee scope or an injection is a data error on the case log, not a cost. Caught before it is costed. | OK |
Averaging the implants moves cost between cases without changing the total The averaged re-costing must spend exactly the same implant dollars, just spread out. If it does not, the average was computed wrongly. | OK |
The average fee reproduces total revenue Cases times the average fee per CPT equals actual revenue in total. That is precisely why the shortcut survives: nothing in the annual number gives it away. | OK |
On your numbers
Have this built on your numbers
Pricing / unit economics 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 ambulatory surgery centers, and the other workbooks this business runs on: Ambulatory surgery centers.
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.