Blog · Territory, capacity and quota planning · Healthcare
Build a clinic operating workbook from aggregated visits, net revenue, staffing costs and collections. Work through the profit and cash schedules, then test a change in collection timing.
A medical clinic financial model links completed visits, expected net revenue, staffing and collection timing to monthly profit and cash. Start with aggregated business exports, keep earned revenue separate from receipts, and prove the result through the receivables and bank reconciliations.
This example is an illustrative US outpatient business. It is an operating planning worksheet using finance-approved assumptions, with no patient records or clinical inputs.
Use a monthly completed-visit summary, an aggregated net-revenue report, payroll and contractor totals, operating expenses, collections and opening balance-sheet figures. The budget owner should confirm which expenses vary with visits and which remain fixed within the planned volume range.
Keep month, site and visit category consistent across files. Count completed visits once; scheduled or canceled appointments do not belong in the completed-volume column. Avoid including names, diagnoses, individual account details or other patient-level information.
The finance team supplies expected net earned revenue per completed visit for planning. That is different from a posted charge and different from cash collected this month. Actual results should use the corresponding revenue amounts prepared by finance, rather than a fresh estimate derived from receipts.
The FP&A overview covers the wider budget process. Keep this workbook focused on the clinic's operating drivers; a startup financial model handles a new business's broader funding assumptions.
Net earned revenue = Completed visits × Expected net revenue per visit
Operating result = Net earned revenue − Variable delivery costs − Staffing − Other operating expenses
Enter this table in A1. Columns B, C and D contain the planning inputs; E, F and G are calculated. The two categories are business reporting groups, with no clinical meaning assigned.
| Visit category | Completed visits | Net revenue per visit (USD) | Variable cost per visit (USD) | Net revenue (USD) | Variable cost (USD) | Contribution (USD) |
|---|---|---|---|---|---|---|
| Category A | 1,200 | 125 | 35 | 150,000 | 42,000 | 108,000 |
| Category B | 800 | 150 | 40 | 120,000 | 32,000 | 88,000 |
| Total | 2,000 | 270,000 | 74,000 | 196,000 |
The spreadsheet formulas, copied down from row 2 to row 3, are:
E2 = B2*C2
F2 = B2*D2
G2 = E2-F2
For the total revenue check, use:
=SUMPRODUCT(B2:B3,C2:C3)
SUMPRODUCT multiplies corresponding values and sums the results. The check returns $270,000, matching the sum of the revenue column without relying on an average reimbursement assumption across different visit groups.
Assume staffing costs of $135,000 and other operating expenses of $25,000. Then operating result is 270,000 − 74,000 − 135,000 − 25,000 = $36,000. This simplified example excludes depreciation, interest and taxes; it does not describe final net income.
Keep staffing separate from the variable cost column. If contractor payments already sit in variable delivery costs, do not include them again in staffing. For a broader comparison of operating measures, see healthcare financial analysis.
Assume 70% of current-month net revenue is collected this month, alongside $80,000 against opening receivables. All operating expenses are paid in the month. There are no other receivable adjustments, prepayments or payable movements in this simplified schedule.
| Cash or receivable movement | Amount (USD) | Calculation |
|---|---|---|
| Opening cash | 60,000 | Reconciled bank balance |
| Opening receivables | 90,000 | Finance balance |
| Current-month collections | 189,000 | 270,000 × 70% |
| Collections of opening receivables | 80,000 | Receipt schedule |
| Total receipts | 269,000 | 189,000 + 80,000 |
| Operating payments | 234,000 | 74,000 + 135,000 + 25,000 |
| Equipment payments | 10,000 | Capital spending schedule |
| Closing cash | 85,000 | 60,000 + 269,000 − 234,000 − 10,000 |
| Closing receivables | 91,000 | 90,000 + 270,000 − 269,000 |
The operating result of $36,000 becomes a $25,000 cash increase after the $1,000 increase in receivables and $10,000 equipment payment. Therefore 36,000 − 1,000 − 10,000 = 25,000, which agrees with closing cash less opening cash.
Your actual receivable bridge may need separately identified adjustments. Use the finance team's signed movements rather than forcing any unexplained difference into collections. The days sales outstanding guide explains a related collection measure; its revenue and receivable definitions must fit the balances being compared.
Hold visits, earned revenue and every cost assumption constant. Reduce current-month collections from 70% to 60%, while retaining the same receipts from opening receivables.
| Metric | Base case | Delayed collections |
|---|---|---|
| Current-month collection share | 70% | 60% |
| Current-month receipts (USD) | 189,000 | 162,000 |
| Total receipts (USD) | 269,000 | 242,000 |
| Operating result (USD) | 36,000 | 36,000 |
| Closing receivables (USD) | 91,000 | 118,000 |
| Closing cash (USD) | 85,000 | 58,000 |
The $27,000 delay moves cash into receivables without changing earned revenue or the operating result. All displayed dollar amounts are exact. This comparison isolates timing; it does not assume the delayed amount is uncollectible.
Keep actual and budget amounts in separate columns. For an aggregated actual-revenue sheet with month in A, site in B and revenue in C, put the selected month in J2 and site in K2:
=SUMIFS(Actuals!C2:C1000,Actuals!A2:A1000,J2,Actuals!B2:B1000,K2)
SUMIFS supports multiple matching criteria. Use the same method for expense and collection summaries, then check their totals against finance's reports. Covirage is not affiliated with Microsoft.
Upload your aggregated operating and budget workbook or Google Sheet; Covirage maps the columns, and its tools compute and check the figures. The external AI model explains the differences with every figure traced to its rows. Create a free Covirage account and bring a sample of your own aggregated business data.
See Covirage for healthcare for the supporting industry context.
Use finance-approved net revenue for the operating plan. Gross charges can differ from the amount expected to be earned after the adjustments reflected in the clinic's accounting. Keep the assumptions and actual revenue definition consistent, and maintain collections as a separate cash schedule.
Yes. Revenue may be earned before it is collected, and equipment payments or debt principal can use cash without being current operating expenses. Reconcile the operating result to receivable movements and other cash uses instead of treating profit as the bank balance change.
This operating analysis uses aggregated monthly visit counts, revenue, expense and collection totals. Patient names, diagnoses and individual clinical records do not belong in the workbook. Use business categories that provide enough detail to explain revenue and staffing differences without identifying patients.