Healthcare Clinic Excel Template for Hospital Valuation

Healthcare Clinic Model Excel Template for Hospital Valuation ready for the pitch. Dive into the real story of how Medical Practice valuation Excel helped our client to prepare a successful business plan and start a medical practice.

Hospital Valuation Model for Healthcare: Important or Not

Forward budgeting is crucial for all types of businesses nowadays. But there are several critical factors that make it especially important for financial forecasting in healthcare:

Different Budget Forecasting Techniques

Nowadays, there are many types of financial models, and the use of each depends on the expectable outcome of planning. You can find more information about other financial modeling types here:

1. Leveraged buy out — Leveraged buy out model is beneficial for companies that want to borrow costs for making a big investment. In a public health service, this method helps investors to see how much time they need to meet acquisition costs.
2. Option pricing — Option pricing model allows businesses to calculate a potential value for a certain option at a specific time. Using this model in healthcare, we can estimate reasonable prices for certain products or services.
3. Sum-of-the-parts — Sum-of-the-parts model makes a break-up analysis of the company. Applying this model to healthcare valuation, we determine the value of different healthcare services or divisions.
4. Comparative company analysis — Comparative company analysis model can compare a company with similar businesses within the same industry. As an illustration, this model allows comparing the financial performance of several healthcare institutions.
5. Discounted cash flow — Discounted cash flow model can estimate the value of a business, predicting the sum of future cash flows.

In our case, we used the last type which was discounted cash flow for the hospital valuation model. The point is that the main purpose of our client was to understand whether her idea of a startup is profitable.

According to expectable outcomes of funding projection, we focused on a certain model type, defined the key expectations, and set up a range of goals to be achieved.

Let’s take a look at the specific case from my practice and explore the essentials of successful hospital financial model Excel.

Conveying a Business Idea of Medical Practice

In my day-to-day working practice, I consult the people, who need my assistance in:

They need it for their current or forthcoming projects. Once I worked with a doctor, who had more than 15 years of working experience behind her career in medical practice. One day the lady came up with an idea of establishing a specialized medical practice.

But the problem was to choose the service that would not have to compete with other general clinics. Having done some extensive research, the lady found out that adults with chronic diseases represent more than a half of market share.

In particular, 1 in 3 adults in her town suffered from cardiovascular diseases.About one-quarter of patients had diabetes type 2. The doctor wanted to focus on long-term care services, particularly on wound care. This type of healthcare delivery is important for people with chronic diseases.

So, the lady decided to focus on wound care services with the use of HBOT equipment, which is not yet known in her town. In hyperbaric oxygen therapy (HBOT), the use of pure oxygen speeds up the process of wound healing, what’s especially good for diabetics.

The Reasons for Requesting Financial Modelling Assistance

The lady acquired a vision of a strong marketing strategy for her medical practice launch. She has found a niche with a minimal number of competitors, clearly defined her target market, and selected a proper product, narrowing her focus to wound care and HBOT.

Despite her first-class education and a great background in management, she did not know what to begin with. Besides, the use of templates made things only worse, as none of them was applicable to her individual case. Finally, the lady requested professional financial modelling assistance, and I was there to help.

Setting Up Purposes and Forming a Medical Practice Business Plan

At the very beginning of our cooperation, the level of economic competence my client had surprised me to the upside. Together, we set up expectations and established the main purposes of our cooperation, such as the following:

• understand, whether the medical practice business idea is profitable
• calculate overall costs of specialized medical practice launch
• find out, whether signing on a hospital manager would be affordable
• evaluate expenses on salaries and wages of medical practice workers
• estimate expenses on equipment, suppliers, management, education, and advertising
• discover costs of debt and time needed to loan payback
• -calculate an average number of visits within a certain area (population)
• estimate prices for each procedure
• get the main medical practice financial performance indicators
• craft an effective strategy for medical practice business development

Hospital Financial Model Interactive Dashboard

Having planned everything, I started working on hospital financial dashboard, which became the cornerstone of a future model.

Before we begin creating a hospital valuation model, let’s consider some valuable aspects of the financial modeling process. First of all, it is necessary to define the key drivers and variables to rely on.

In our case, these variables consisted of:

• the addressable market population
• the market share.

At this image you can find Hospital Financial model population assumptions

In addition, it included:

• the number of visits per patient per year
• treatment prices
• overall hospital operating expenses.

Secondly, we should establish the links between:

• debt
• potential investments
• managing
• operating expenses

Our hospital valuation model had to be responsive to changing drivers, as we wanted to test several alternatives. For instance, we could look at healthcare business valuation statements with or without expenses on hospital management. All things considered, I started working on the healthcare business valuation model.

How to Forecast Hospital Financial Performance

Hospital Financial Model Defining Target Audience

As the client requested hospital valuation for a certain population, I began with defining the target market, separately for wound care and HBOT. To do it properly, I focused on the three following aspects, such as:

• Geographical location of the potential patients
• The estimated population growth
• The potential growing need for suggested services

Why?

With this in mind, the doctor provided me with the estimated population growth and the percentage of patients eligible for wound care.

Based on it, hospital financial metrics calculated the expectable patient growth for wound care services. It was a basis for the estimation of growth in the number of hyperbaric visits.

Because these two variables were interdependent. The more patients requested wound care services — the more started to use HBOT in treatment.

In such way, the hospital valuation model in excel calculated the annual number of patients. They request wound care or HBOT, taking into account the changes in population.

Patients count & revenue calculations

Hospital Financial Model Creating Pricing Strategy

As mentioned above, our client has a solid working experience in healthcare. That is why she knows that certain procedures are more popular than the other. Thanks to this, she estimated the average price for each procedure per one patient visit.

Hospital Financial Model Making Revenue Assumptions

The annual revenue was for the hospital financial model excel estimated based on the average expected number of annual visits and the price of each procedure.

In result, we received revenue assumptions, calculated separately for wound care and hyperbaric oxygen therapy.

Hospital Financial Model Salaries and Wages Assumptions

To get this part of work done, the client provided me with standard requirements for wages and salaries. Additionally, I asked the client to provide the whole list of personnel members, who would be working in her specialized team hospital.

Important to realize, members of medical personnel might change over the years. Due to this, I took into account the staff changes which might happen in the next 5 years. Another key point is the estimated salary growth and personal tax rates.

These indicators often are not taken into account. So, having considered the changes in professional staff, the need for salary growth, and the expenses on tax rates, hospital business valuation model provided calculated salaries for each of the following 5 years.

Hospital Financial Model Calculating Capital Expenditure

The first thing to remember, the capital expenses of launching a startup are the most significant. Due to this, we included a purchase schedule in our healthcare business valuation model. Additionally, the client may adjust the payment offset for the purchased items.

Hospital Financial Model Fixed Expenses Assumptions

Great flexibility to forecast up to 15 expense line items. Each line has the launch date — when this expense should start accrue.

End date — when to stop accruing. Periodicity — one-time, weekly, monthly and more to forecast expense more precisely. And finally, amount to be spent with the chosen periodicity.

Hospital Financial Model Variable Expenses Assumptions

• As per % of total revenue
• As per one Hyberbatic visit
• As per one Wound Care visit

With this flexibility, the client may assume for variable expenses driven by specific revenue stream or/and by total revenue.

Outputs of the Hospital Financial Model Excel

Hospital Financial Model Dashboard

Available at the front working datasheet (Dashboard) of the healthcare business valuation excel model and ready for instant correction by the model user.

Another batch of input indicators available at the hospital financial Dashboard comprised of core financials:

• debt and equity amounts
• cost of debt
• core revenue / expense positions

Needless to say, there are a variety of charts and graphs available for visual presentation purposes. Here you can see the dashboard.

Hospital Financial Model Breakeven Analysis

Setting a Break Even point is quite helpful because it helps your investors look into long-term profits. Breakeven Analysis calculation by years with detailed graph.

Hospital Financial Model Financial and Operational Charts

Hospital Financial Model Top 5 expenses report

The client wanted to see the big picture of the future expenses. So, hospital valuation statements included detailed information on annual expenses.

In particular, this information consisted of expenditures on education, equipment, suppliers, advertising, management and medical director fee. In result, the client saw the overall annual expenses on wound care, HBOT, and hospital management.

Hospital Financial Model Valuation

On the Valuation tab, the net present value (NPV) of future cash flows is calculated for investment purposes. In addition, here in this section, the discount factor (WACC) is calculated. Once we had agreed with the client, we made the business valuation using this technique. Besides, the hospital valuation model provides several other options for healthcare business valuation.

Hospital Financial Model Capital

The Capital datasheet showed a straightforward view on interest charged over the period and the debt outstanding, as well as the calculation of dividends for payouts.

Hospital Financial Model Financials Summary

There are three possible types of financial reports present at the end of the hospital financial model Excel. It can create separate datasheets for each, as they are for each financial year.

Detailed overview of the annual financial statements and monthly overview for the chosen year:

Hospital Financial Model Forecasted Financial Statements

Finally, a powerful excel chart tool helps in providing representative deliverables in Revenues and Expenses as well. Hospital Financial Model xls Income Statement Report

3 financial statements detailed by month for the next 5 years:

Benefits of Using Medical Practice Valuation in Healthcare

1. No hard-coded assumptions — First and foremost, we did not hard-code any assumptions. It is very helpful both for the client and financial modeler. In fact, all the variables could be edited on the hospital financial dashboard or at the expense/wages sections. Thanks to it, we could test different alternatives and make any edits literally on the fly.
2. Flexible Management fee — Besides, management fee was switchable, so the end user of the medical practice valuation model could see the revenue and expenses both with and without the expenditures on management.
3. Flexible model launch date — Besides it, the action period of budget modeling was editable, just like the currency.
4. Debt cost and repayment — Last but not least, healthcare business valuation model considered the cost and the period of debt. It allowed to estimate the time needed to meet acquisition costs.

In result, the user received maximally accurate financial statements summary. Thus, our hospital financial metrics was flexible and editable. It provided the client with a great user experience and confidence in the profitability of her business idea.

Do you want to purchase a unified version of the clinic financial model?

Then click on the button below

Also, don’t forget to watch the video review of hospital financial model xls to gain a better vision of the main building blocks of budget forecasting. We appreciate your interest and opinion.

Feel free to express it in the comments below.