skip to Main Content

Pharmacy Financial Model Excel Template

5.0 (3 reviews)

Financial model spreadsheet for medical stores. Feasibility study, forecasts & budget plans. Printable financial statements. Dynamic charts. All in Excel.

Learn more about License Types


Someka refunds icon

30-Days
Return

Someka one time payment icon

One-Time
Payment

Someka direct download icon

Instant
Download

TEMPLATE DESCRIPTION

Running an independent pharmacy is an exciting and challenging task, which requires many different types of expertise. So, the ability to adequately plan and manage finances is not the least. Here comes our Pharmacy Financial Model Excel template to be a roadmap.

Pharmacy-Financial-Model-Someka-S16

Furthermore, you may need to prepare a formal business plan in order to secure a business loan or attract investors. 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.

Our Pharmacy Financial Model template is intuitively understandable even for a newcomer,  despite providing professional-level financial planning features. In addition, it allows you to produce detailed and professionally looking pro forma statements and charts for a business plan.

Pharmacy Financial Model Excel Template Features:

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

Model Config:

Generally, 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;
  • Set the Discount Rate for 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 don’t want to produce a formal business plan, you can simply ignore this setting.
  • Enter Income Tax Rate that will affect net profit after taxation.

Moreover, you also can show or hide instructions here.

Medication Categories:

Firstly, in the table of Medication Categories, enter a list of categories and related assumptions:

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 a 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:

Generally, you set assumptions related to how you generate sales revenue in the Sales Channels table. 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 the 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%.

Furthermore, the Sales Trend table allows you to set long-term trends. If you do not expect your sales to change over the years, you can leave this table blank.

After that, go to the Sales & Inventory section of this Pharmacy Financial Model. Here you produce your sales projection.

Revenue & Cost Model Inputs in Pharmacy Financial Model Excel Template

Sales & Inventory Section:

Firstly, select medication categories in the Sales Forecast table. For each category, enter the 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. But, 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. Besides, the projections of sales revenue and cost of sales will be calculated automatically. So, if you expect inflation-driven changes in purchase prices to happen over time, enter respective parameters into the Cost Increase fields.

Projections of sales revenue: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.
  • Lastly, the inflation-driven increase in prices over time.

Projections of the cost of sales: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;
  • Lastly, the inflation-driven increase in cost over time.

To emphasize, the 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

In addition, it will affect your cash flow. So, adequate consideration must be given to capital rationing in this excel financial model 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 the 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 the Salary Increase fields. Time-adjusted salaries will be calculated for each month of the projected period.

In the Other Monthly Expenses table, enter the names of expense items into the list, and then enter expected monthly expense amounts for each projected month.

Pharmacy-Financial-Model-Someka-S10

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

Startup Section:

Particularly, the usefulness of your financial plan will depend on the degree of accuracy 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 of Pharmacy Financial Model Excel Template

Firstly, 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.

Then, 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).

Afterward, enter the 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. So, you will instantly be able to see a snapshot of profit and cash flow in the tables below.

Pharmacy-Financial-Model-Someka-S14In detail, please pay 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 in Pharmacy Financial Model Excel Template

Reports are available from the Dashboard section. In summary, the 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;
  • Finally, the Account Receivable report shows quarterly changes of accounts receivable, for each sales channel.

Pharmacy Financial Model Excel Template Features Summary:

  • Drugstore Financial Feasibility Plan 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

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

PRODUCT VIDEO

Watch the video below to see the template in action! Presentantation also includes usage notes, explanations and tips & tricks about the template.

FREQUENTLY ASKED QUESTIONS

USER REVIEWS & COMMENTS

3 reviews for Pharmacy Financial Model Excel Template

  1. Janina

    Hello for the useful clear data button and yellow instructions in my template thank you team

  2. Sizwe Holgersen

    This is a great template but it is hard to use then shown in the video tutorial. after a time I learned how to use it thank you

    • Someka

      Hi Sizwe,
      Thank you for your feedback. Remember we have a customer support team you can always contact for help! Have a nice day!

  3. Mohamed ALi

    nice

Leave a customer review

Only verified users who have downloaded and used the template may leave a review.

RELATED TEMPLATES