Medical Practice Business Plan Financial Model Excel Template by Henry Sheykin - Financial Models https://finmodelslab.com/ Last version published: 08/05/2019
COVER PAGE
The cover page is the name of your business project, company or Food Truck name. This name is put in a yellow cell and appears on every file page afterward.
2
Time Series Time Series Financial year end in the model - input the last month of the financial year. If you don't need to assume for the financial years, simply type in Dec. First Financial Year in the model - input the very first financial year you want to start from financial year. If you have entered Dec in the Financial year end in the model, then, your calendar year equals the financial year.
3
Dashboard - Currencies Input Currency - currency for the assumptions and inputs. Insert your currency sign $/€/¥/£ or type in USD/EUR/GBP or any other abbreviation. Output Currency - currency for the outputs and graphs. Insert your currency sign $/€/¥/£ or type in USD/EUR/GBP or any other abbreviation. Currency exchange rate - enter your up-to-date exchange rate of the currency in which you want to display your financial report.
4
Population inputs
At the Revenue tab, you may find the Starting Population and Patient Growth Rate. Change yellow cells for each year to see the impact on Forecasted Population at the end of the year.
5
Dashboard - Inputs At the dashboard, you may find the Patient base assumptions section. Change the Treatment, %, to define medical practice Addressable Market Population. After, Change Patients, % which leads the percent that center will capture annually. Changing this you will affect the number of potential patients in your medical practice. Finally, • define Annual Visits for each treatment. • set the Price per visit by years. Now, you may check very first output
6
Seasonality
Now you are ready to spread acquired patients within a year according to seasonality. Go to the seasonality tab and change Patients Acquisition Seasonality Assumptions section Now, lets setup visits seasonality Go to the next section which is the Visits Seasonality Assumptions. Here you may adjust visits for every month by changing the seasonality factor. All 5 treatments will be affected.
7
Variable Expenses
Enter in the list of variable expenses you want to forecast in the rows Variable expense. And type in the percentage of revenue (first 2 lines) to be spent or a cost per visit for each treatment.
8
Fixed Expenses
9
Go to the Fixed Expenses tab Enter in the list of fixed expenses (i.e. ""running the business"") you want to forecast in the rows Fixed expense. You have multiple options of when to start to accrue the expense and when to end accruing. You may define periodicity of the accrue for each expense. It could be One-time, daily, weekly, bi-weekly, monthly, quarterly and etc.. This gives you the flexibility to manage expenses.
Wages
10
Wages tab contains the placeholder list. A placeholder can be a person, or position, or a group of people. Each row specifies their hire and fire dates, annual salary and Full-Time Equivalent count. Besides, there are also annual salary raise, monthly bonuses, and tax rate. The wages tab easily allows you to calculate the expenses for the salaries of your personnel. Use 1 line per employee/category and simply type in the hire date, fire date, and annual salary. Next use the Number of Employees section to type in FTE count for each year. An increase of FTEs means that you are going to hire additional FTE's. In case of decrease FTEs means you fired some FTEs. New FTEs will be hired/fired on Jan of the relevant year. If there are raises or changes in salaries, simply type in the Annual Salary Raise appropriate figures. The program will automatically recalculate the changes made. Finally, there are Monthly Bonus and Tax Rate columns where you can enter the percentage of respective
Development Expenses
11
On this sheet, you can see a table about cliniclevel capital expenses. You have to name categories, choose payment delay, and input the cost. Capital costs or investments are the clinic development expenditures (i.e. equipment, decoration, furniture etc.) that are to be amortized Each expense item has a purchase date, payment delay time and amount of money spent. What is payment delay? Imagine that your equipment requires prepayment 3 months beforehand. Then you use the payment delay column to note this information. Now, if you put 0, it means You pay and actually buy the item in the same month. If you put plus 1/2/3, then you create a payment delay. You can find the information about CAPEX expenses in the Income Statement -
Dashboard - Debt
12
There are 3 debt lines: either choose one or go for all three. Put the amount of money, select launch date and repayment term, interest rate and debt type. There are two debt types: annuity (paid back in installments of the same size consisting of a loan repayment portion and an interest portion) and usual (the same amount of money is paid for the debt principal, and the interest goes down every month). In addition, there is also a grant or nonrepayable funding. Usually, it is given by a government department.
Capitalization Table
13
Capitalization Table
14
CapTable require some manual manipulation to be accurate. The structure enables necessary modifications.
you
to
make
the
The CapTable takes you through multiple rounds of financings. Showing how issued shares to new investors and cost per share impact the investment income. After each round, you will see the ownership, % changes. This is how the dilution works. We offer several funding rounds. In general, this method is for large companies but is also effective for stock corporations. The information to be given: shareholders’ names, investment round close date, and share price. The number of shares is distributed between shareholders. The second funding round is optional for initial and new investors. All the investments influence both cash flow and income statement.
Reports - Dashboard Here you can see 4 charts and a table. The left upper chart gives you a yearly revenue breakdown by treatments. The right upper one shows your EBITDA, yearly profitability and EBITDA percent. Left lower chart is for cash flow, which includes operating, investing and financing categories. Net Cash Flow is highlighted in green color. Right lower one depicts cumulative cash flow. The only difference from the ordinary cash flow is that it has an accumulative function starting from the first year and ending
15
Key Financial/Customer/SAAS Charts
Reports - Financial Statements Summary If you want to know more details about the financial state of your medical practice or plan, then you should open the Summary tab. The summary presents 3 core reports: - profit and loss - balance sheet- cash flow You can review them in different ways: by each month of each year or check out 5 years altogether in the left upper table.
16
Reports - Financial Charts
17
Financial charts allow you to see some key figures, such as Revenue Breakdown for 24 months and 5 years. You can see two options: the first one covers midweek and weekends&holidays levels, and the second one focuses on food and beverage numbers. Then, scroll down and see Operating Cash Flow and Cash Balance sheets. Operating cash flow stands for the cash inflow and outflow, i.e. how much money you get and spend. Cash balance signifies the amount of money accumulated. EBITDA, or Earnings before Interest, Taxes, Depreciation and Amortization, is the overall profitability index before
Reports - Top Revenue
18
Top Revenue is another useful category designed for your needs. As we have only five revenue sections, the numbers are easy to understand. All the charts give you a thoroughly made analysis. Top Revenue Summary enables you to choose a period to look at, e.g. 2018-2022, and shows the exact revenue numbers for each section. Top Revenue Depth gives you an opportunity to dive deeper into the information concerning every year of your financial model. Another cool feature is Revenue Bridge. You can literally go from the first year to the last year and make out
Reports - Top Expenses
19
Top Expenses also cover the same categories, as in Top Revenue, but they regard your spending and are divided into 5 groups: - biggest 4 expense categories ranked as a sum of 5 years and - "other" which is the rest of the expenses.
Reports - Break-Even Analysis
20
Additionally, you can use a break-even analysis feature. What does it mean? Break-even signifies the sales level with zero profitability. So, you get neither losses nor profits. Every dollar above this level makes your project profitable, and every dollar below it creates losses.
Reports - Operational Reports
Another tab is called Operational Charts. Here you can check medical practice productivity, that is medical practice statistics, OPEX productivity. Worker productivity calculates the revenue and operating expenses per worker.
21
Reports - Valuation Valuation tab. Calculate the cost of your company! The first thing that you see is the Cost of Capital. The puzzling numbers, such as risk-free rate, inflation premium, and risk premium, are available on public sources, for example, Bloomsburg. Growth after the end of the forecast is your personal prediction for the future of your project. Below, you can see the results of these calculations in free cash flow and discounted cash flow sections.
22
Thank you very much for your interest in my tools! If you enjoyed using my model, please let people know about your experience! Give 5-star feedback here & share this page across social networks If you have any suggestions or ideas of how to make this model more useful, don't hesitate, just send me a PM - Henry Sheykin