# The Healthcare Finance Director: companion files

Julian R. Sterling

## What is here

- HCF_1_Revenue_and_Payer_Mix.xlsx (Chapters 2, 3 and 6; Revenue and Payer Mix workbook of Appendix A): tabs Inputs, Gross to Net, Payer Mix, Volumes, then Checks. Gross patient revenue and each deduction by payer and year, net patient service revenue ($253,610,245 in 2024, $258,137,981 in 2025; commercial insurance $108,744,819 in 2025), the payer mix of net revenue (Medicare Advantage 11.7 percent in 2025), and adjusted patient days month by month (83,880 and 84,964).
- HCF_2_Labor_and_Productivity.xlsx (Chapter 8; Labor and Productivity workbook): tabs Inputs, FTE, Productivity, Contract Labor, then Checks. Paid, worked and paid time off hours by staff group and month, FTE on the case's monthly divisors, 4 West's worked hours per patient day against its target (10.05 in 2025), and contract nursing at its billed rate (98,200 hours in 2025, 47.2 FTE, $11,391,200).
- HCF_3_Budget_Variance.xlsx (Chapters 10 and 11; Budget Variance workbook): tabs Inputs, Revenue Variance, Expense Variance, then Checks. The 2025 budget, the flexible budget at actual adjusted patient days ($266,405,000 of expenses), and the revenue and expense variances split into volume, rate and spending (revenue volume variance -$1,877,040; spending variance $3,675,782), each marked favorable or unfavorable.
- HCF_4_Liquidity_and_Covenants.xlsx (Chapters 5, 13 and 14; Liquidity and Covenants workbook): tabs Inputs, Receivables, EBIDA, Days Cash, Coverage, Covenants, then Checks. Days in accounts receivable (54.0 at the end of 2025), EBIDA, the cash rolled forward to the balance sheet, days cash on hand (99.3 and 75.1) and debt service coverage (2.66 and 1.60) against the covenant levels of the case's indenture.
- HCF_5_Options.xlsx (Chapter 16; Options workbook): tabs Inputs a, Inputs b, Inputs c, Projection, Sensitivity, then Checks. One Inputs tab per option, the baseline and the scenario switches at the top of the Projection tab, operating income, margin, coverage and days cash for 2026 to 2028 under each combination (days cash 78.5 in 2026 and 72.3 in 2028 for the decision of the case, 58.3 in 2028 with no action), and a Sensitivity tab that scales every assumed effect (70.8 days in 2027 with the effects halved). All projections, never results.
- HCF_Finance_Director_Toolkit.docx: the 8 check-lists of Appendix B, editable (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)).

## How to use the workbooks

Each tab opens with a note that names the chapter it serves and says what to type. The workbooks have no separate read-me tab: this file and the notes carry the instructions. Input cells are blue on a light blue fill, and they sit on the Inputs tab of each workbook (in the Options workbook, on the three Inputs tabs, with the shared baseline and the scenario switches at the top of the Projection tab and the effect factor at the top of the Sensitivity tab). In the grids (payers, months, staff groups, years) the case's figures are typed in place: overwrite them with your own, and clear the case before the first real use, because a file that still holds a fictional hospital's figures next to your own is worse than no file. A single input shows the case's figure, an empty yellow cell for yours, and a Used column that takes your figure when you type one; every formula reads the Used column. Black type is a formula, green type a link to another tab. Every cell that divides stays empty, never an error, until its divisor is entered. Rebuild the case first: the Checks tab of each workbook compares the live cells with the figures printed in the book, then runs the structural checks of Appendix A, and reads ALL CHECKS OK while the case figures are in place. Each of the 12 exercises of Appendix A is done in the workbook it names.

The arithmetic is plain: add, subtract, multiply, divide and round. Ratios are rounded to two decimals, days to one decimal, shares to one decimal of a percent, dollars to the dollar; a year is the sum of its months.

Two inputs to try first:

1. In HCF_4_Liquidity_and_Covenants.xlsx, on Inputs, type 1.40 in the yellow cell of the coverage covenant: the Covenants tab reads the 2025 cushion against the new level at once; clear it and the case's 1.25 comes back.
2. In HCF_5_Options.xlsx, on Sensitivity, type 25 percent in the yellow cell: every assumed effect of options a and b falls to a quarter, and the lowest days cash on hand among the scenarios moves; clear it and the case's halved effects come back.

To use a workbook on your own hospital, fill the inputs from the general ledger, the payroll register, the budget file, the audited statements and your own indenture, in the order Appendix A gives, and keep a copy with the case for comparison. The workbooks are separate files with no link between them: where one workbook needs a figure of another (the 2025 agency hours and denial write-offs in the Options workbook, the adjusted patient days in the Budget Variance workbook), it is typed, with its source beside it. The workbooks compute; they decide nothing about compliance, reimbursement or tax status.

## A word of caution

Callowbeck Community Hospital, the city of Rendlemoor, Corlenby County, Pennistow Surgery Center, Brisswick Medical Group, the Velmarsh Health Facilities Authority and the hospital's people are fictional. Volumes, charges, payment ratios, rates of pay, hours, interest rates, covenant levels and effects are teaching assumptions, not market norms, targets or recommendations, and nothing here promises a margin or any financial result. How a real hospital bills, reserves, reports and files, what its indenture allows and whether its bonds can be refinanced are questions for its counsel, its bond counsel, its municipal advisor and its auditors. No real hospital, health system, insurer, health plan, rating agency, software vendor, staffing agency, purchasing group, firm or bank is described or recommended. Nothing here is legal, tax, reimbursement or investment advice.
