Pharma Primary Care Model – Business Planning & Financial Forecasting

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

5-year Financial Projections, Break-Even, Budgeting, Business Valuation, Cash Flow Analysis, Cash Flow Projections, DCF Model, Debt Service Coverage, Financial Analysis, Financial Model, Financial modelling, Financial Planning, Financial Projections, Financial Ratios, Financial Statements, Forecast, Forecasting, Free Cash Flows, Income Statement, NPV (Net Present Value), Sensitivity Analysis, Three Statement Model | Three Financial Statements, Uses and Sources of Funds, Valuation, WACC (Weighted Average Cost of Capital)

Objective: The main objective of this model is to develop a 5 year Forecast for the Business, both in terms of Revenue FRC and in terms of Financials 3-Statement Model FRC. Also to provide a Valuation for the Business by using a Discounted Cash Flow Model.

Moreover, the model includes:

* 2nd alternative valuation method (Analyst’s choice), focusing on a more short-mid term period (2,3, and 5 Years Valuation).

* Sensitivity Analysis for both valuation methods

* Break-Even Analysis

*Major Loan Repayment Schedule

*Investor Summary Dashboard

*Executive Summaries: Business/Financials

Business Forecast: The model supports a forecast concerning 3 distinct Therapeutic Areas.

The model supports 10 company products per therapeutic area. The Sales Volume in units/packs are forecasted per product for the 5 year forecast period.

Finally, by inputting the Gross Prices per product and the Gross to Net Discounts, we come up with the Νet Revenue per year.

Financial Forecast (3 Statement Model): Using as an initial input the Net Revenue per year calculated in the Revenue Forecast, a 5 year Financial Forecast is performed for the 3 main Financial Statements:

Income Statement (P&L), 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.

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. You may find also a Sensitivity Analysis concerning the Enterprise Value, using as variables WACC and Terminal Growth Rate (Perpetual Growth Rate).

Alternative Valuation – Analyst’s Choice: The difference with the previous valuation method is that the Enterprise Value is calculated as the sum of Total Assets at the end of Year 1 plus the Net Present Value of the DCFs for the anticipated period that the investor will hold the company’s shares (2, 3, or 5 Years). Thus the Terminal Value of the Company is not taken into account, based on the assumption that the investor intends to hold the company’s shares for a short-medium period of time.

You may find also a Sensitivity Analysis concerning the Enterprise Value, using as variables WACC and Number of years forecasted (NPV).

Break-Even Analysis: A break-even analysis is performed for each of the 5 forecast years, calculating the break-even volume & revenue, according to the forecast scenario.

Major Loan Repayment: A Loan Repayment Schedule is calculated for the major loan of the company, using as inputs the loan amount, the interest rate, the principal payment, and the moratorium period in years.

Suggested Work Flow:

Step_ 1 – Home – Model Settings w/s: Fill in the required info in the “Model Setup” section. Here you may input Financial Currency, 1st Forecast Year. There is also a Table of Contents from which you may visit the rest of the w/s through hyperlinks.

Step_ 2 – Product List w/s: Here you may input the names of the 3 Therapeutic Areas, as well as the Company’s product names in each area.

Step_ 3 – General Assumptions w/s: Here you may input the main assumptions regarding: Business Valuation, Major Loan, Working Capital Parameters. You may also view the Uses & Sources of Funds.

Step_ 4 – Revenue worksheets (3 distinct w/s, one for each Therapeutic Area): These are the w/s for the Revenue Forecast. Complete the data required in these 3 w/s in the input cells (in yellow) from top to bottom, concerning “Annual Volume Growth”, “Volume – Packs Sold”, and “Conversion to Revenue”.

Step_ 5 – Loan Repayment w/s: Here you may see the Major Loan Repayment Schedule, while entering the “principal payment” parameter.

Step_ 6 – FIN Assumptions & Calculations w/s: Here you may input the assumptions for the Income Statements and the Balance Sheet. The rest of the calculations are made automatically.

Step_ 7 – Financial Statements w/s: This w/s contains the 5 year Forecast for the 3 main Financial Statements. All calculations are performed automatically. Review the results, and make any necessary adjustments from the “FIN Assumptions & Calculations” w/s (Step_6), or from the Revenue Forecasts.

Step_ 8 – Business Valuation w/s: Here you may perform a Valuation for the Business by using a Discounted Cash Flow method.

*You may view the estimation of the Required Return on Equity (%) (Cost of Equity), and the WACC of the Firm (you have already entered the data in the General Assumptions w/s in step 3).

**By using the Discounted Free Cash Flow to Firm method, the Valuation for the Business is performed automatically in order to end up with an Intrinsic Value per Share for the Business. You may find also a sensitivity analysis for the Enterprise Value of the Business.

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

Step_ 9 – Valuation_ Analyst’s Choice w/s: In this w/s you may find an alternative Valuation Method, based on the assumption that the investor intends to hold the company’s shares for a short-medium period of time. The Enterprise Value is calculated as the sum of Total Assets at the end of Year 1 plus the Net Present Value of the DCFs for the anticipated period that the investor will hold the company’s shares (2, 3, or 5 Years).

Step_ 10 – Break-Even Analysis w/s: Here you may find a break-even analysis for each of the 5 forecast years, calculating the break-even volume & revenue, according to the FRC scenario.

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 5 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.

Investor Summary Dashboard w/s: In this w/s you may find several dashboards as illustrative summaries for the potential investor.

You must log in to submit a review.