Community Pharmacy Model
Healthcare Financial Model (Free Excel Download)
Model pharmacy performance from prescriptions, front-store sales, reimbursement, generic mix, inventory turns, labor, locations, and operating cash flow.
professionals from Deloitte
Used by professionals from
About this model
This model helps you assess a community pharmacy or a group of locations. It brings prescription volume, reimbursement, clinical services, and front-of-store sales together with the staffing and operating costs needed to run the business.
Use it to test store growth, prescription demand, pricing, and service mix before making an investment or operating decision. The summary shows the resulting profit, cash flow, and value.
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 Community Pharmacy Model
- Pharmacy inputs: Year-1 pharmacies, new pharmacies per year, scripts per pharmacy, pharmacists per pharmacy, average script value
- Utilisation: Year-1 utilisation with an annual ramp and a practical ceiling
- Ancillary: clinical encounters per pharmacy and average clinical fee, front-of-store revenue per script and front-of-store gross margin, price escalation
- Cost structure: pharmacist and technician comp and wage with benefits and wage growth, facilities, marketing, technology and SG&A as a percent of gross profit, depreciation, tax
- Capital and working capital: maintenance capex, pharmacy build-out cost per store, NWC, base-year revenue
- Valuation: WACC, terminal growth, net debt, shares outstanding
- Dashboard with pharmacies, scripts, utilisation, revenue per pharmacy and per script, EBITDA margin, EV, per share, and revenue mix
- Pharmacy roll-forward (opening + new = closing) with a capped utilisation ramp driving effective scripts per pharmacy and total scripts
Community Pharmacy Financial Model: How This Template Works
This pharmacy financial model template projects the economics of a multi-location community pharmacy group over seven years. It links store growth and prescription demand to dispensing mix, clinical services, and front-of-store sales, then flows through operating costs, cash flow, and a discounted valuation so you can test how reimbursement pressure and service mix affect profit and enterprise value.
What Drives Pharmacy and Prescription Growth
The model begins with a pharmacy roll-forward: opening stores plus new pharmacies equal closing stores. Each year, the group targets a fixed number of new locations, but a pipeline factor tapers that target as the store count approaches a market-saturation ceiling.
- This reflects the finite number of viable community pharmacy sites. Closing pharmacies then drive the script-volume build, because each store supports a mature number of prescriptions annually.
- A utilisation ramp scales that mature script book from an initial level upward to a practical ceiling, recognising that new or acquired stores take years to build patient relationships. Total scripts, the product of closing pharmacies and effective scripts per pharmacy, is the core volume driver feeding revenue and cost lines.
How the Dispensing Mix Shapes Revenue and Gross Profit
Prescription revenue is built tier by tier across generic, branded, specialty, and compounded-and-DME categories. Total scripts are split by each tier's share and multiplied by an average script value and a per-tier price index.
- A specialty script carries a price index well above one, while a generic sits far below, so the blended revenue per script emerges directly from the mix. The cost to deliver is the drug cost, calculated as each tier's revenue times one minus that tier's gross margin.
- High-volume generics earn a wide spread, while branded and specialty drugs are largely pass-through. That structural asymmetry means the blended gross margin before PBM fees lands in the mid-twenties, and it is a key lever for understanding profitability.
The PBM DIR-Fee Clawback and Its Effect on Margins
A defining feature of community pharmacy economics is the pharmacy benefit manager's ability to claw back direct and indirect remuneration fees months after the point of sale. The model prices this explicitly and separately from per-tier dispensing margins.
- The DIR fee starts at a percentage of prescription revenue and escalates linearly each year, and it is netted only against prescription revenue, never against front-of-store or clinical-services revenue. This clawback sits between drug cost of goods and gross profit, so it is additive to the tiered margin structure and can be flexed independently of volume or mix.
- Layering the DIR fee onto dispensing economics is why the blended gross margin settles in the low twenties and why the EBITDA margin compresses modestly across the horizon as the fee escalates faster than the cost-stack ramp.
Operating Costs, Cash Flow, and Valuation Outputs
Pharmacist and technician labour are headcount-driven and represent the dominant operating cost, loaded for benefits and escalated at a wage-growth rate. Remaining overhead—facilities, marketing, technology, and corporate SG&A—is set as a percentage of gross profit rather than revenue, because most dispensing revenue is pass-through drug cost.
- That structure lets utilisation, front-store attach, and clinical services lift gross profit while labour grows only with headcount and inflation. The cash flow statement then adds depreciation, subtracts maintenance and store build-out capital expenditure, and accounts for working capital tied up in drug inventory and PBM receivables.
- Unlevered free cash flow is discounted at a WACC, and a Gordon-growth terminal value produces enterprise value, equity value, and value per share, alongside a dashboard summarising pharmacies, scripts, revenue, EBITDA, and valuation multiples.
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 pharmacy financial model?+
A pharmacy financial model captures the seven-year operating economics and intrinsic value of a multi-location community and independent pharmacy group that runs retail dispensing alongside front-of-store retail and clinical services. It rolls a pharmacy count forward, converts a capacity-utilisation ramp into total scripts, prices scripts across a four-tier dispensing mix at a blended average script value and price index, runs the thin-margin drug-cost and labour stack to EBITDA, and discounts an unlevered free-cash-flow stream to enterprise value, equity value, and value per share.
How is pharmacy revenue built?+
Revenue is driven by the pharmacy estate and its utilisation: total scripts equal closing pharmacies times scripts per pharmacy times a utilisation factor that ramps to a ceiling, and prescription revenue splits those scripts across a generic, branded, specialty and compounded-and-DME mix, each priced at a blended average script value times a per-tier price index. Store-driven clinical services and script-driven front-of-store and OTC retail layer on to total revenue.
Why is the dispensing mix so important?+
High-volume generics carry a small price index but a wide gross margin, while branded and specialty drugs carry a large price index but a near-pass-through margin, so both the blended revenue per script and the blended gross margin fall out of the mix. The model makes the per-tier indices and gross margins explicit so an analyst can flex the mix and reimbursement and watch revenue per script, gross profit and EBITDA move together.
Why an unlevered DCF instead of an EBITDA multiple?+
A pharmacy group still builds out each store and ties up drug inventory and slow-paying PBM receivables, so EBITDA overstates cash. The model bridges to unlevered free cash flow, NOPAT plus depreciation, less maintenance and build-out capex, less the change in working capital, and discounts it at a WACC, then adds a Gordon-growth terminal value. The implied EV/EBITDA falls out as a sanity check rather than as the valuation input.
What is a pharmacy dispensing-mix model?+
It is a model that builds prescription revenue tier by tier - total scripts times each tier share (generic, branded, specialty, compounded-and-DME) times a per-tier average script value and price index - so that the blended revenue per script and blended gross margin both fall out of the mix rather than being assumed directly. A shift toward specialty lifts revenue per script but compresses gross margin; a shift toward generics does the opposite.
How is the utilisation ramp structured?+
The model takes a Year 1 utilisation input (the share of mature-capacity scripts a pharmacy actually fills in its first year), adds a fixed number of percentage points per year, and caps it at a practical ceiling. Total scripts equals closing pharmacies times effective scripts per pharmacy (scripts per pharmacy times utilisation), so the ramp is the dominant volume driver in the early years of a de novo or tuck-in pipeline.
Why is overhead sized off gross profit rather than revenue?+
The majority of prescription revenue is pass-through drug cost that the pharmacy collects from a PBM or insurer and remits to the wholesaler. Sizing overhead as a percentage of gross profit reflects the actual cost of running the store and multi-site platform relative to what the business retains after drug cost, and it keeps EBITDA margin benchmarks consistent with how pharmacy operators and acquirers actually report them.
How does the working-capital charge work in the FCF bridge?+
A dispensing business ties up drug inventory held before dispensing and carries PBM and third-party receivables that can settle on a 15-30 day cycle. The model expresses net working capital as a percentage of revenue growth, so as the pharmacy group adds stores or grows script volume the resulting inventory and receivables build-up is captured as a cash outflow in each year.
What are the headline model outputs?+
The model defaults to 25 pharmacies in Year 1 growing to 43 by Year 7, with total scripts rising from approximately 1.72 million to 3.27 million. Revenue grows from approximately $150.9 million to $330.6 million. Blended gross margin runs approximately 26%, EBITDA margin ranges from 8.9% to 9.2%, enterprise value is approximately $139.8 million, equity value per share is approximately $18.15, and the implied EV/EBITDA is approximately 10.5x. All inputs are editable on the Assumptions sheet.
Have more financial modelling questions? Contact us
Related templates
Dental Practice
Five-year service-mix model: visits, production, collections, comp, and margins.
Hospital Operating Model
Operating model for hospital systems including inpatient/outpatient volumes, payer mix, DRG reimbursement, and staffing cost drivers.
Franchise Unit Economics Model
Economics template for franchise systems modeling unit-level P&L, franchise fees, royalties, and multi-unit growth scenarios.
Pet Care Roll-Up Model
5-year veterinary-clinic roll-up: M&A pace, per-clinic economics, platform EBITDA, sponsor IRR and exit multiple arbitrage.
