Companion files · The Private Markets Career Guides
A Practitioner's Guide to Healthcare Finance: Hospital Revenue and Payer Mix, Contractual Allowances, Cost per Case, Labor Productivity, Budgeting, Capital Planning and Bond Covenants.
These are the companion files of the book. Five workbooks follow Callowbeck Community Hospital, the fictional community hospital of the case, across two calendar years by month: revenue from gross charges to net by payer, labor and productivity, the budget and its variances, liquidity and the covenants, and the three options of the year-two decision. Each keeps the case’s figures in blue input cells that you overwrite with your own, guards every division against an empty divisor, and ends with a Checks tab. A toolkit holds the check-lists of Appendix B in editable form.
The archive holds the five workbooks, the toolkit and 00_START_HERE.md. No macros, no circular references, no external links, no password. The workbooks recalculate in Excel, LibreOffice or Google Sheets.
Chapters 2, 3 and 6
Gross patient revenue and each deduction by payer and year, net patient service revenue, the payer mix of net revenue, and adjusted patient days computed month by month. Tabs: Inputs, Gross to Net, Payer Mix, Volumes. A Checks tab puts the figures printed in the book beside the cells that compute them.
Download workbook 126 KBChapter 8
Paid, worked and paid time off hours by staff group and month, FTE on monthly divisors, a unit’s worked hours per patient day against its target, and contract nursing at its billed rate. Tabs: Inputs, FTE, Productivity, Contract Labor. A Checks tab puts the figures printed in the book beside the cells that compute them.
Download workbook 227 KBChapters 10 and 11
The fixed budget and the flexible budget at actual adjusted patient days, with revenue and expense variances split into volume, rate and spending, each marked favorable or unfavorable. Tabs: Inputs, Revenue Variance, Expense Variance. A Checks tab puts the figures printed in the book beside the cells that compute them.
Download workbook 317 KBChapters 5, 13 and 14
Days in accounts receivable, EBIDA, the cash rolled forward to the balance sheet, days cash on hand and debt service coverage, set against the covenant levels of the indenture. Tabs: Inputs, Receivables, EBIDA, Days Cash, Coverage, Covenants. A Checks tab puts the figures printed in the book beside the cells that compute them.
Download workbook 424 KBChapter 16
Three options for 2026 to 2028, one Inputs tab each, projected under each combination, with a sensitivity tab that scales every assumed effect. All projections. Tabs: Inputs a, Inputs b, Inputs c, Projection, Sensitivity. A Checks tab puts the figures printed in the book beside the cells that compute them.
Download workbook 538 KBAppendix B
the first 90 days in the job (chapters 1 and 12); the monthly close and the net revenue reconciliation (chapters 2 and 12); the review of receivables and denials (chapter 5); the annual budget cycle (chapters 10 and 11); the productivity review of a department (chapter 8); a capital request to the finance committee (chapter 16); covenant monitoring and continuing disclosure (chapters 13 and 14); preparing the annual audit and the Medicare cost report (chapter 15).
Download the toolkit42 KBRead first
What each file holds, which workbook answers which chapter, and the two inputs to change first.
Download the read-me6 KB| Gross to net | $221,026,053 - $109,407,895 - $2,873,339 = $108,744,819: commercial net revenue in 2025, the final denial write-offs on their own line |
| One definition | 3,744 x $70,488,224 / $35,822,592 = 7,367: adjusted patient days for January 2025, computed month by month |
| The covenant | $52,399,369 / (($270,080,782 - $15,420,000) / 365) = 75.1: days cash on hand at the end of 2025, against a covenant of 75 |
The files are provided as they are, without warranty of any kind. The model is illustrative: Callowbeck Community Hospital, the city of Rendlemoor, its people and its partners are fictional; the volumes, charges, payment ratios, hours, rates, covenant levels and effects are the book’s assumptions, not market norms or the record of any real hospital. The workbooks compute; they decide nothing about compliance, reimbursement or tax status, and nothing here promises any financial result. Not legal, tax, reimbursement or investment advice.