Healthcare Clinic 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 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-todate exchange rate of the currency in which you want to display your financial report.
4
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
5
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 to be spent or a number of cost per visit each
6
Fixed Expenses
Go to the F_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
7
Wages
8
Start with Average Staff Numbers section. Give a name for each staff category you need. Non used you can erase/clear. Setup hire and fire dates for each category item. After,enter the Average Annual Base Salaries for each staff category Once you are ready to enter employees count, go the Staff Numbers section. Enter expected FTEs employed for each year of the forecast. If you need 1 FTE till the model end, simply enter 1 for each year. After, set the Annual raise per position for each year. Finally, set the bonus and taxes section for each staff category.
Development Expenses On this sheet, you can see a table about hospital-level capital expenses. You have to give a name of categories, choose payment delay, input amount and choose how this amount is going to be paid.
9
Dashboard - Debt
10
There you may add Initial Debt and set up repayment terms. The entered amount will be received at the very first month of the model. Ad repaid within debt period. Cost of Debt will be used to calculate the interest expenses.
Capitalization Table
11
Cap table require some manual manipulation to make accurate. The structure enables you to make the necessary modifications. The Cap Table model takes you through multiple rounds of financings Showing how issued shares to new investors and cost per share impact the investment received. After each round, you will see the ownership, %
Financial Statements Summary
The Summary sheet is a Summary of the key financial reports of the model. Moreover, you have the options to select the year to see detailed outputs.
12
Top Revenue
Here you can track how much money each category generates.
13
Top Expenses
Here you can track the biggest 4 expense categories ranked as a sum of 5 years and the "other" which is the rest of the expenses.
14
Break Even Analysis
15
Here you can track when your company is supposed to move to a whole new level. When its overall revenues become significantly bigger than expenses.
Valuation
The model can do valuation of distribution of current cash flow to Investors - Discounted Cash Flow method.
16
Financial Charts
Here you can visually track key financial performance over five years or 24 months.
17
Operational Charts
18
At this tab, you can track some internal operational indexes that can be useful for analysis of the internal activity of the hospital.
Formatting and color scheme Formatting and color scheme will unlock the door of productive function management! Colors show which table cells are open for editing and which are unavailable for the user. Yellow cells are all yours to edit, but the white and grey ones are not. The pages also have a certain color scheme: •
Blue – reports
•
Grey – calculations
•
Green – instructions (i.e. ‘read me first’)
•
White – cover page
Green tabs include Contents, Info, How To and Changelog sections. Contents describe each page and contain a link to it. If you want to go back to the table of contents, click Go to Table of Contents in the left. This way, you can easily navigate the file. Info is the tool general description. Here you can find whom to address in case you have questions. How to is a mini-guide on how to complete your restaurant financial model.
19
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