skip to Main Content

Pharmacy Financial Model

Rated 0 out of 5
(be the first to review)

The Pharmacy Financial Model allows users to create financial projections, and assess potential risks and benefits of opening a pharmacy.

$99.95

Learn more about License Types

BUY NOW BUY NOW
Someka 30 days refund icon

30-Days
Return

Someka one time payment Icon

One-Time
Payment

Someka direct download icon

Instant
Download

Payments are processed by our partner company Paddle.com

PRODUCT DESCRIPTION

Running an independent pharmacy is an exciting and challenging task, which requires many different types of expertise. The ability to adequately plan and manage finances is not the least. Often it is what makes a difference between business success and failure.
Whether you are intending to buy an existing pharmacy business, or start a drug store from the scratch, a sound financial plan needs to be in place even before you spend a single dime. Here comes our Pharmacy Financial Model template to be a roadmap.

Pharmacy-Financial-Model-Someka-S16

You may need to prepare a formal business plan in order to secure a business loan or attract a co-investor, or maybe you may just want to know how profitable your investment is going to be. Either way, you must employ a coherent financial model to predict sales and costs, and make proper capital rationing.

Even for a newcomer, our Pharmacy Financial Model template is intuitively understandable, while providing professional-level financial planning features. It allows a user to produce detailed, clear, and professionally looking pro forma statements and charts for the pharmacy’s business plan.

Pharmacy Financial Model Template Features:

In the upper part of the Dashboard, there is a navigation pane allowing a user to easily navigate across the template.

Model Config:

In the Model Config section, you have to:

Pharmacy-Financial-Model-Someka-S01

  • Set a length of the Projection Period. You can select 2 to 10 years by picking the respective value from the drop-down list;
  • Enter the Start Date of the project;
  • Enter a code of the currency in which all monetary amounts will be displayed;
  • Enter the Discount Rate that will be applied to the calculation of the Net Present Value (NPV) of the project. If you are unsure what Discount Rate should be, enter the interest rate that usually applies to long-term saving accounts. However, if you are not interested in producing a formal business plan or investor pitch deck, you can simply ignore this setting.
  • Enter Income Tax Rate that will affect net profit after taxation.

Here you also can show or hide instructions.

Medication Categories:

In the table of Medication Categories, enter a list of categories and related assumptions as follows:

Pharmacy-Financial-Model-Someka-S04

Mark-up percentage shows how much you are going to add to the hard cost of goods in order to get regular selling price before discounts;
Credit Period is a number of days showing how long it is going to take on average to pay suppliers for purchased goods;
Days of Inventory is a number of days showing how fast each category of goods can be sold, in other words, how many days of sales your inventory is going to cover;
• Finally, Shortage & Expiration Allowance shows which percentage of your inventory is going to be lost due to various reasons, rather than sold.

Sales Trends & Channels:

In the Sales Channels table, you set assumptions related to how you generate sales revenue. For example, you may choose to make prescription dispensing to health care plans’ customers, which decision will affect both your revenue and customer credit period.

Pharmacy-Financial-Model-Someka-S03

Reimbursement Level shows which percentage of nominal selling prices you probably will be able to collect, after applying discounts, rebates, merchant fee for card payments, and allowing for a cost that is not reimbursed by health care plans;
Credit Period is a number of days showing how long it is going, on average, to collect revenue after pointing of sale. It equals zero for cash payments, and a few days for card payments. However, it will take much longer to get paid from a health care plan;
• For each sales channel set a share of that channel in total revenue. Sure enough, they must sum up to 100%.

The Sales Trend table allows you to set long-term trends in order to account for how improving customer awareness and loyalty will affect your overall sales in long run. If you do not expect your sales to change over the years, you can leave this table blank.

Next, go to the Sales & Inventory section. Here you produce your sales projection.

Revenue & Cost Model Inputs:

Sales & Inventory Section:

Select medication categories in the Sales Forecast table. For each category, enter projected Average Daily Revenue.
To account for seasonal changes, use the Seasonal Sales Trend table, which allows you to enter, for each category, and each month of a year, percentages of average sales that can be expected in the respective month due to seasonal fluctuations of demand.

Pharmacy-Financial-Model-Someka-S05

If you do not expect changes in sales over a year, you can leave this blank. If you expect selling prices to increase over time (for example, due to inflation), enter the respective increase rate along with its frequency into the Price Increase fields.

Do not use this to account for changes in quantities of goods sold; use Sales Trend (in the Dashboard section) instead.
Projections of sales revenue and cost of sales will be calculated automatically. If you expect inflation-driven changes of purchase prices to happen over time, enter respective parameters into Cost Increase fields.

Projections of sales revenue will take into account:Pharmacy-Financial-Model-Someka-S06

  • Average daily revenue;
  • A number of days in a month;
  • Long-term sales trend, if any;
  • Seasonal fluctuations of sales (Seasonal Sales Trends), if any;
  • Reimbursement percentages in each of the sales channels, along with channels’ shares.
  • Inflation-driven increase in prices over time.

Similarly, projections of the cost of sales will take into account:Pharmacy-Financial-Model-Someka-S07

  • Average daily revenue;
  • A number of days in a month;
  • Long-term sales trend, if any;
  • Seasonal fluctuations of sales (Seasonal Sales Trends), if any;
  • Projected markup rate;
  • Inflation-driven increase in cost over time.

Forecasts of inventory at cost, at the end of each month, will be calculated by multiplying the average daily cost of sales in the next month by the number of Days of Inventory. The difference between the opening and closing inventory, plus costs of sales, will give us the projected cost of purchased goods. If you set your purchase credit period to a non-zero value, your cash payments to suppliers will lag after the purchase of goods by a respective number of days.
Similarly, if you set a non-zero sales credit period, the collection of revenue will occur later than underlying sales.

Pharmacy-Financial-Model-Someka-S08

It will affect your cash flows; adequate consideration must be given to capital rationing in order to ensure your pharmacy business is not going to run short of cash.

After you have planned sales and inventory, go to the Fixed Costs section.

Fixed Cost Section:

In the Staff table, enter names of employee positions, along with their respective salaries. For each projected month, enter the expected numbers of employees.

Pharmacy-Financial-Model-Someka-S09
If you expect that salaries will increase over time, enter expected increase rates and frequencies into Salary Increase fields. Time-adjusted salaries will be calculated for each month of the projected period.
In the Other Monthly Expenses table, enter names of expense items into the list, and then enter expected monthly expense amounts for each projected month.

Pharmacy-Financial-Model-Someka-S10

Next, enter expected startup costs into the Startup Costs section.

Startup Section:

The usefulness of your financial plan will depend on the degree of accurateness in estimating startup costs. It is rather dangerous to underestimate them. There is the Account field there, you can indicate whether respective startup expenditure will create an asset (for example, property, equipment, software, or patents), or be accounted for as an expense.

Pharmacy-Financial-Model-Someka-S11

However, if you do not need to produce pro forma financial statements for the business plan, you can ignore this column altogether.

Miscellaneous Section:

If your purpose is simply to get a snapshot of projected revenues and costs, you can jump right to the reports. However, if you need a more comprehensive financial picture, go to the Miscellaneous section first. Here you can add figures related to the financing and fixed assets.

In the Debt & Equity Financing table, enter:

Pharmacy-Financial-Model-Someka-S12

  • Equity contribution – money injected into the business by owners (co-investors);
  • Equity repayments – amounts of initial investments withdrawn by owners (co-investors) at par, as well as payouts to the owners (co-investors) expected to be made out of net profit;
  • Debt Issuance – amounts of loans received from a bank or other lender;
  • Debt Repayment – payments in order to repay the principal amount of the loan;
  • Interest Paid – payments for the use of the loan.

Fixed assets are those you expect to be in use for more than one year. They include tangible assets, also known as property, plant, and equipment, and intangible assets (software, patents, and trademarks). Enter amounts you are going to spend for fixed assets, other than startup costs, to the Fixed Assets table.Pharmacy-Financial-Model-Someka-S13

For the sake of simplicity, we do not calculate depreciation and amortization; however, you can add them manually in this table.
You will instantly be able to see a snapshot of profit and cash flow in the tables below.

Pharmacy-Financial-Model-Someka-S14Pay attention to the End Cash Balance figures. If any are in red, it means your business is running out of cash in respective months. In this case, you will need to consider an additional equity contribution to be made, or a loan taken in order to balance cash. You also can play with the equity and debt figures above and immediately see an effect here.

Report Outputs:

Reports are available from the Dashboard section. Pharmacy Financial Model template contains the following reports:

  • Monthly and yearly pro forma financial statements, including Income statement, Balance sheet, and Cash flow statement;
  • Profit (EBITDA) report presenting breakdowns of income and operating expenses by line items;
  • Revenue Details report displaying revenues for each category of medication, and each sales channel;
  • Breakeven Analysis report giving you an estimation of the payback period;
  • Gross Profit Breakdown report displaying details of income, direct costs, and profit margins for each category of medication, and each sales channel;

Pharmacy-Financial-Model-Someka-S15

  • Gross Profit Report displaying a summary of gross margins for each category of medication, and each sales channel, for selected periods;
  • NPV and IRR calculations and a chart of cumulative discounted cash flow giving you an estimation of discounted payback period;
  • Inventory Balances report displaying the cost of inventory in stock, at the end of each reporting quarter.
  • Account Payable report that shows changes of accounts payable in each reporting quarter, broken down by categories of medication;
  • Account Receivable report shows quarterly changes of accounts receivable, for each sales channel.

Pharmacy Financial Model Features Summary:

  • Financial Feasibility Study for Pharmacy in Excel
  • Innovative Reporting System
  • Works both on Mac and Windows
  • No installation needed, ready to use.
  • Professional design and suitable for presentations
  • On Sheet Instructions
  • White Label
  • Print-Ready
  • Compatible with Excel 2010 and later versions
  • Includes VBA and Macros

This Pharmacy Financial Model Excel template is ready-to-use and provided as is. If you need customization on your reports or need more complex templates, please refer to our custom services.

USER REVIEWS

Reviews

There are no reviews yet.

Leave a customer review

Your email address will not be published. Required fields are marked *