Pharma Pipeline Model

The model structures two independent assets: a lead program in Phase II and a follow‑on in Phase I. Each has its own phase duration, patient enrollment, cost per patient by phase, probability of success (PoS) at each stage, peak penetration, annual price, and loss‑of‑exclusivity year.

• A 15‑year explicit forecast horizon captures the full pre‑LOE commercial life and post‑LOE erosion. The Pipeline_Timeline sheet tracks phase start/end years and binary phase‑active flags, which feed the R&D cost build and determine when revenue begins.
• Cumulative PoS is computed by multiplying phase‑specific probabilities, reflecting the compounding risk of clinical development.

R&D costs are built bottom‑up per asset: clinical trial costs equal enrolled patients times cost per patient, allocated across active phase years, plus pre‑clinical costs and CRO management fees.

• During development, operating losses combine R&D, general and administrative expenses, and a commercial SG&A ramp that starts 12–18 months before the earliest expected approval.
• The Income Statement consolidates both assets, showing zero revenue pre‑approval and a launch‑year ramp thereafter.
• The Cash Flow statement uses the indirect method, adding back non‑cash items like stock‑based compensation and depreciation, and incorporating equity raises and venture‑debt draws to derive closing cash and runway.

The rNPV_Valuation sheet computes risk‑adjusted free cash flow per asset by multiplying unlevered FCF by cumulative PoS in each year, then discounts at a biotech‑appropriate rate (e.g., 15%) and sums across the explicit period.

• A post‑LOE terminal value captures erosion after exclusivity.
• The sum of both asset rNPVs is reduced by net debt and divided by fully diluted shares—basic shares plus option pool and warrant overhang—to arrive at equity value per share.
• This approach ensures that the probability of clinical success and the time value of money are both reflected in the final valuation, rather than applying full revenue potential without risk adjustment.

This model helps investors and analysts evaluate capital allocation to clinical‑stage companies by testing how changes in phase success rates, enrollment costs, pricing, or financing affect rNPV.

• The Checks sheet validates that the balance sheet balances, cash never goes negative, cumulative PoS stays between 0% and 100%, and revenue appears only after approval.
• Common pitfalls—such as ignoring the regulatory review gap, applying corporate WACC instead of a biotech discount rate, or forgetting future dilution—are explicitly addressed in the structure.
• The public download is a values‑only preview; it does not include live formulas or automatic recalculation.