Hospital Model
Healthcare Financial Model (Free Excel Download)
Forecast inpatient and outpatient volumes, payer mix, reimbursement, staffing, departmental costs, capex, and hospital operating margins.
professionals from Deloitte
Used by professionals from
About this model
This operating model projects five years of profitability and cash flow for a general acute care hospital system by forecasting inpatient and outpatient volumes, payer mix, DRG reimbursement rates, clinical staffing by service line, and operating expense scaling. Answer: can the hospital achieve 10–16% EBITDA margins and generate positive operating leverage as volumes grow and labour inflation stabilises?
The workbook builds revenue from three streams: inpatient (licensed beds × occupancy × length of stay × net revenue per discharge), outpatient (visit volumes × net revenue per visit), and ED (annual visits × revenue per visit). Payer mix drives reimbursement: Medicare and Medicaid at regulated DRG rates, commercial at contracted rates with 3–5% annual escalators. Operating expenses: salaries and wages (50–55% of revenue, highest category), supplies (15–20%), purchased services (8–12%), and facilities/insurance (4–6%). FTE scaling: 4.5–5.5 FTEs per adjusted occupied bed. Working capital: DSO 45–65 days, DIO 15–25 days.
Used by hospital systems, PE sponsors in healthcare acquisitions, lenders underwriting hospital debt, and policymakers modelling health system consolidation impact. The model captures service line profitability (high-margin OR procedures, low-margin inpatient medical), payer mix sensitivity (shift from commercial to Medicaid erodes margins by 200+ bps), and agency labour premiums (15% uplift vs. FTE base). Benchmarks: HCA Healthcare, Tenet, UHS - all showing 50–55% SWB ratio, 3–5% operating margin, 60–90+ days cash on hand. Days cash covenant is typically >60 days (liquidity buffer for supply chain and payroll timing).
What every model includes
Live formulas, no hardcoded values
Outputs are driven by live formulas, so the workbook updates from its assumptions instead of relying on hardcoded results.
All assumptions in one tab
Inputs are clearly marked in the Assumptions tab and separated from calculations, making it clear what to change and what to leave intact.
Statements always balancing
For integrated-statement models, the balance sheet, cash flow, and supporting schedules tie through properly.
Distinct schedules for clarity
Debt, working capital, taxes, and cash flow can get messy quickly. We group calculations in clear schedules, not across disconnected tabs.
No hidden macros or external links
There are no unexplained external workbook links or macros to undermine auditability or portability.
Changes flow through the model
Update a key driver and see the impact carry through the forecast, financing, and return outputs. We never use hardcoded numbers in formulas.
What's inside the Hospital Model
- Inpatient and outpatient revenue stream modelling
- Departmental P&L with overhead allocation logic
- Payer mix sensitivity including DRG reimbursements
- Dynamic staffing and FTE optimisation schedules
- Capex planning for facility expansion and medical equipment
- Inpatient and outpatient case volumes by service line
- Payer mix (Medicare, Medicaid, commercial insurance, self-pay)
- DRG reimbursement rates and case complexity weighting
Inside the Hospital Model: How Acute Care Economics Are Captured
This hospital model builds a general acute care facility's financials from operating drivers, not headline guesses. It translates beds, occupancy, length of stay, case mix and payor contracts into net revenue, staffing costs, capital spending and cash flow.
The aim is a transparent evaluation of how volume, payor mix and capacity decisions flow through to earnings and balance sheet strength.
Revenue Drivers and Payor Contracting
Inpatient revenue starts with available bed days, occupancy and patient days, then divides by average length of stay to get admissions. Case mix index and the Medicare base rate provide a second cross-check on intensity.
- Outpatient and emergency department streams use separate visit volumes and rate escalators, reflecting different growth pressures from ambulatory surgery centres and urgent care. A payor mix layer then converts gross charges into net patient revenue by applying contractual allowances and bad debt ratios for Medicare, Medicaid, commercial and self-pay tiers, each with its own collection period and rate escalation.
- Value-based capitation adds a per-member-per-month block with shared savings.
Cost Structure and Staffing Economics
Labour is built from the bottom up rather than as a fixed percentage of revenue. Total full-time equivalents come from adjusted occupied beds multiplied by a staffing ratio.
- Those FTEs split across nursing, physician, technical and administrative roles, each with its own salary and benefits burden. Productive nursing hours are separated from non-productive time such as paid leave and education.
- Agency labour is costed explicitly from agency FTEs, hours and an hourly rate that is materially higher than the in-house base, capturing post-pandemic staffing pressure. Medical supplies and pharmaceuticals are variable costs, with a 340B saving applied to outpatient drug spend.
Purchased services, facilities, malpractice, marketing and equipment rent round out operating expenses.
Capital Investment, Debt and Balance Sheet Mechanics
Capital spending combines maintenance capex as a percentage of net revenue, growth capex for new beds, and explicit equipment refresh outlays in later years. Depreciation follows separate lives for buildings and equipment.
- The balance sheet is anchored to an opening column with explicit cash, accounts receivable, inventory, payables, property, debt and equity inputs. Share capital is held constant at opening equity rather than acting as a plug, and retained earnings accumulate net income.
- Debt is split into three tranches with distinct balances, rates and terms; interest is calculated on the opening balance, which avoids circularity. Covenants track debt service coverage, leverage and fixed-charge coverage.
How the Model Is Used in Practice
The structure supports a disciplined underwriting process. A user changes occupancy, case mix, payor mix, wage inflation or bed expansion and sees the effect on net patient revenue, staffing cost, EBITDAR, EBITDA and cash flow.
- Working capital responds automatically because receivable days vary by payor and inventory and payables are tied to operating activity. Valuation uses a discounted cash flow with terminal value, while a KPI dashboard surfaces labour productivity, revenue cycle metrics, financial health and cost-of-capital checks.
- Fifteen validation checks test balance sheet integrity, occupancy caps, coverage ratios and margin ranges. The public file is a values-only preview, not a live calculating workbook.
Formatted to IB standards
Named theme colors repaint the whole workbook in one click, on top of an investment-banking structure with clear input, output, and cross-sheet reference styling - brand-ready, institutional-grade, and fully auditable.
Created by ex-finance professionals
Hey, I’m Alex and I created Finamodel.
Over my years in the finance industry I kept building the same models over and over again. Same structure, same assumptions, different logo. So I started building frameworks to turn them into clean, reusable templates.
Every model here is one I’d actually use for a client, and I personally vet each one before it goes up.
I’m not an expert in every industry, but I’ve built enough models to know what belongs in one. And when something is completely foreign to me, I reach out to my network for experts to work on our models with us.
Having a template library on hand cuts a first build from hours to minutes.
Need help finding your model? You’ll find me in the Finamodel app!
Frequently asked
What is a hospital financial model?+
It is a model that forecasts hospital revenue, costs, and profitability using clinical and operational drivers such as payer mix, patient volume, and departmental performance.
Who uses hospital financial models?+
Hospital administrators, healthcare investors, lenders, and consultants use them for capital budgeting, strategic planning, and performance monitoring.
What should a hospital model include?+
It should include inpatient and outpatient revenue, departmental P&L, payer mix assumptions, staffing matrices, and capex planning for equipment and facilities.
Does it handle DRG reimbursements?+
Yes. The model supports weighted DRG averages for inpatient services, multiplying base payment rates by the Case Mix Index and discharge volumes.
Can I model facility expansion?+
Yes. The capex module includes schedules for new wings, surgical suites, and diagnostic equipment with IRR and payback outputs based on projected patient throughput.
Have more financial modelling questions? Contact us
Related templates
Medical Device Financial Model
Revenue and profitability model for medical device companies including R&D, regulatory, manufacturing, and distribution.
Senior Living Facility Economics Model
Operating model for senior living communities with unit mix, occupancy, care levels, staffing, and profitability analysis.
Insurance Portfolio Risk and Return Model
Portfolio model for insurance investment holdings including asset allocation, duration matching, credit risk, and spread analysis.
Gym and Fitness Studio Operating Model
Operating model for fitness franchises and independent gyms including membership revenue, class scheduling, and facility utilization.
