Financial forecasting model combining graphs and pharmaceutical visuals.

Financial Model, Health Care, Life Science, Pharmacies & Drugstores

10-year Financial Projections, 5-year Financial Projections, Accounts Payable, Accounts Receivable, Amortization, Budget, Budgeting, Business Valuation, Cash Flow Analysis, Cash Flow Projections, Chart, Dashboard, DCF Model, Debt Schedule, Discount Rate, Dividends, Excel, Financial Analysis, Financial Debt, Financial Model, Financial Modeling, Financial Planning, Financial Projections, Financial Ratios, Financial Reporting, Financial Statements, Financing, Forecast, Forecasting, Income Statement, Return on Invested Capital (ROIC), Terminal Value, Three Statement Model | Three Financial Statements, Valuation, WACC (Weighted Average Cost of Capital)

Objective : The main objective of this model is to develop a 10 year Forecast for the Business, both in terms of market/business FRC and in terms of financials FRC. Moreover to provide a Valuation for the Business by using a Discounted Cash Flow Model.

Business Forecast : The model supports a forecast concerning 3 distinct therapeutic areas in which the company may operate, each consisting of up to 3 segments (e.g. could refer to line of therapies, or any other segment as defined). The first 3 years are forecasted on a monthly basis, while from year 4 to year 10 the FRC is annual.

*As a 1st step the market is forecasted by tacking into account the factors: Prevalence Rate, Diagnosis Rate, Treatment Rate, as well as, the break-down to each segment of the market.

** Following this, the assumed Market Share evolution per product is inputted for each segment. The model supports 10 company products and 5 competitive products. Tacking into account also the Compliance Rate, and the Units per Patient per Month, the Sales Volume in units/packs are calculated per product.

Finally, by inputting the Gross Prices per product and the Gross to Net Discounts, we come up with the net revenue per month/year.

Financial Forecast : Using as an initial input the Net Revenue per year calculated in the Business Forecast, a 10 year Financial Forecast is performed for the 3 main Financial Statements: Income Statement, Balance Sheet & Cash Flow Statement. Moreover, one can see the main Financial Ratios in the relevant worksheet.

Business Valuation : A Business Valuation is performed in the relevant worksheet using a Discounted Cash Flow model to calculate the intrinsic value of the Company.

It is assumed that the Company’s Shares are not trading in the Stock Exchange, and therefore we compare the Intrinsic Price of the Company to the existing Book Value.

The required Return on Equity is calculated through the Capital Asset Pricing Model (CAPM). The WACC of the firm is then calculated taking into account also the cost of Dept.

Suggested Work Flow:

Step_ 1 Model Settings w/s: Fill in the required info in the “Model Setup” section.

Step_ 2 Revenue_ Therap. Area_1/ 2/ 3 w/s: These are the w/s for the Business Forecast. Complete the data required in these 3 w/s in the input cells from top to bottom, concerning “Market Sizing”, “Trending per Segment”, “Sales Volume – Units Sold”, and “Conversion to Revenue”.

Step_ 3 FIN Assumptions & Calculations w/s: In this w/s you may input the assumptions for the Income Statement and the Balance Sheet. The rest of the calculations are made automatically.

Step_ 4 Financial Statements w/s: This w/s contains the 10 year Forecast for the 3 main Financial Statements. All calculations are performed automatically. Review the results in this w/s, and make any necessary adjustments from w/s “FIN Assumptions & Calculations” (Step_3), or from the Business Forecast (should you need to adjust the Revenue).

Step_ 5 Business Valuation w/s: Here you may perform a Valuation for your Business by using a Discounted Cash Flow method:

*Input the required data to estimate the Required Return on Equity (%) (Cost of Equity), and the WACC of the Firm.

**By using the Discounted Free Cash Flow to Firm method, the Valuation for the Business is performed automatically. All you need to input is the Terminal Growth Rate, and number of shares outstanding in order to end up with an Intrinsic Value per Share for the Business.

***You may also review here the Fundamental Financials per Share.

Financial Ratios w/s: Here you may see the values of the main Financial Ratios: Profitability & Return, Liquidity, Leverage, Asset Utilization.

Exec. Summary w/s: In the Business and Financials Executive Summary w/s you may review the key data of your 10 year Forecast, Business and Financial aspects respectively, and relevant charts, that may help you have a better understanding of your Business Forecast, and perhaps decide on fine-tuning adjustments needed.

Reviews

A very comprehensive model for Pharmaceutical companies

By ClaireS on August 10, 2024 at 16:18 (Verified Purchase)

A 1st class model for Pharmaceutical Companies. A very comprehensive 3 statement financial model. Moreover, I like the Business forecasting part, from prevalence to revenue. Very professional.

Thank you for your feedback.

185 of 322 people found this review helpful.

You must log in to submit a review.