How to Make Hotel Financial Plan? Ultimate Financial Plan in Excel!

Contents:

• Intro: Profit Consideration
• 1. Assumptions
• 1.1 Trendings and Pricing
• 1.2 Other Revenue Channels
• 1.3 Price Increase
• 2. Costs
• 2.1 Variable Costs
• 2.2 Fixed Costs
• 2.3 Startup Costs
• 3. Reports
• 3.1 Financial statements
• 3.2 Breakeven Analysis
• 3.3 Profits Trend Report
• 3.4 Sales Units Breakdown
• 3.5 NPV & IRR Calculator
• Final Thoughts

So, you decided to establish or just wondered what would happen if you open your own Hotel.

Maybe you still have doubts about it?… Or just want to know the main process of how to make an accurate financial feasibility study before opening.

That’s great.

In this article, we will be trying to give answers to these three main sections below:

You must have thought about all the items above before. However, listing them item by item and quantifying them will clarify the whole process even on paper.

 

Hotel Profitability:

There is one single common word that shapes the structure and future of each enterprise.

“PROFIT”. Right?

However, making profit can’t be the only determinant.

As you may guess, the feasibility studies are the milestones of investments. It doesn’t just have to be done but also it has to be done properly. It should be able to evaluate whether it makes sense to take that particular action or not. Let us elaborate…

In that case, (avoiding the value of money through the years) with basic math;

Why would I put my money in that kind of investment if I am going to only get the invested amount in 8 years?

If the yearly interest rates are higher than at least 1%, why would I take risks and get the same amount of money when I could have it while it is risk-free?

That is why a feasibility report should also indicate matters:

• Breakeven Point (where the cumulative profit meets the initial investment cost),
• Net present value of your project in that time’s circumstances or
• Internal Rate of Return to check the yearly financial expansion of my project.

This blog post outlines step-by-step how to start a hotel business and and walk you through how to set up the financials of ownership in Excel.

We will be dividing this feasibility study under 3 categories for a better understanding.

1. Assumptions
2. Costs
3. Reports

So let’s start with the Assumptions sections.

1.Assumptions

In this section, you have to think about all the factors which could affect all the income channels of the Hotel.

Not just the nightly rate or how many rooms there are but also other factors like the occupancy rate due to seasonal trending, the average discount for each month due to less demand in some particular months, other revenue channels except rooms, yearly price increase, etc.

So let’s start to list those factors which influence those assumptions...

1.1 Trending and Pricing

Pricing is a delicate issue in this matter and it may get affected by several factors. For this section, we are going to talk about:

1) Maximum Occupancy Rate
2) Seasonal Trending
3) Average Discounts

But first, list down all of your Accommodation Units, their nightly rate, their number of units available and the number of persons to stay. That will give you a general perspective about what you can offer to the customers.

Now we can discuss the trending which will affect this table.

Let’s start with the Maximum Occupancy Rate.

Maximum Occupancy Rate:

Once you establish your hotel you will have to get known to be able to reach your Maximum Occupancy. So you will not be able to give all your rooms every day. You will need advertisements, publicity, references to increase your brand’s quality.

Actually, you may think it like a Product Life Cycle:

Since you are just in the introduction part you must also make assumptions about the Yearly Maximum Occupancy Rates. So the question to be asked should be

“How many people do I have to expect each year compared to my maximum capacity?”

We may expect these kinds of results for the Maximum Occupancy Rate in its first five years.

Be careful about making your revenue assumption to have a positive correlation with your Maximum Occupancy Rate. Maximum Occupancy Rate is a factor you can not avoid.

Seasonal Trending:

Seasons have significant effects on many sectors. This is actually one of them. People usually tend to go on a trip in summer so, they start to search for nice hotels where they are about to stay. And as always in summer (besides the advanced hotel reservations), the prices skyrockets !!

This is what seasonal trending means.

So because the prices you are going to set each month will vary, you need to include the Seasonal Trending Occupancy Rate factor in your assumptions to make your predictions much stronger.

Average discount:

As we talked about in the previous factor, due to Seasonal Trending, you may have fewer customers compared to trending seasons. That is the reason you may want to lower your prices to attract customers.

So, in this case, it will be logical to input an Average Discount factor data for each less demanded months to increase the market segment of the hotel. After all, that is what we seek. Lower Price, more customers to attract, more publicity, more reputation, more money...

1.2 Other Revenue Channels

Accommodations will be your major income channel, however, most probably it is not going to be the only one. You will have to look for other channels which will generate income for your own good.

Facilities like Bars, Stores or SPA are usually not included in the daily price of the hotel. You can think about creating these kinds of facilities to generate extra income for your net profit.

1.3 Price Increase

Once your brand starts to get stronger, you may increase the nightly rates of accommodations and/or the services/products in other facilities.

Due to the price increases over the periods, your static prices may not give you the best results. Because you will be calculating each year’s assumptions with the first year’s price level.

However, leaving a gap where you could control the price increase over the periods will also give you the dynamicity to check each period’s revenue.

After including all these determinants, your presumptions will be closer to reality. You have all the data you need to calculate the monthly revenue of an accommodation type including Maximum occupancy Rate, Seasonal Trending and Average discount.

You can integrate all the data you have with the factors and determinants we mentioned to get the revenue of each income channel over the periods.

So, we talked about the factors which influence the assumptions. Now it is time to talk about the other side of the coin.

“The costs”.

2.Costs

You must have heard about the “Everything comes with a cost” expression. It is totally true. To be able to generate the income we plan in the assumptions part, we have to think about their cost, to evaluate whether it makes sense to manage every single one of them despite their costs.

But first, we need to differentiate our costs.

We will be dividing the costs under 3 categories for a better understanding.

1. Variable Costs
2. Fixed Costs
3. Startup Costs

So let’s carry on with the Costs sections.

2.1 Variable Costs

Variable costs are the costs which depend on directly to the number of supplied products. It depends on your sales volume level and/or production costs.

The Variable Costs column on the image above shows the percentage of Variable Cost of each Facility among its Sales Revenue. That is how you will be able to calculate your variable cost over each period.

You may also create a cost increase table just as we created one for the price increase in Price Increase section. That is how you can add the dynamicity of cost increase for each period.

Because eventually, your costs will increase somehow. It could be because of the rising product price, inflation, new taxes over some products…

Another table you could use Variable Cost is :

If you plan to work with Booking Engines and/or Travel Agents you could prepare a table like on the above, diversify your revenue share on each channel and specify their commission rate.

The commission rate your hotel will pay to those channels will also be a part of the Variable Cost.

2.2 Fixed Costs

Fixed Costs are the costs which you have to pay every month or period to be able to maintain your business. They are independent costs that won’t be affected by selling one more unit of your product or service. For instance salaries, cable tv, Internet, software fee, Uniform etc…

Create a table where you can observe your fixed costs.

As salaries are also fixed costs, if you have too many employees working under you, you may create a separate table for salaries to micromanage and observe all of your employees salary.

As we spoke in Variable Costs, the costs increase over periods.

In fixed costs cases there could be also an increase in salaries and other fixed costs over time. Employees are going to get their annual or semi-annual raises eventually but also some other fixed costs could get a raise.

That is the reason why you have to create another table to calculate the burden of the fixed cost increases over time. So basically, what you could do is, apply the same procedure we mentioned in the Price Increase section. The only difference will be, the costs are going to be calculated over time instead of prices.

When you prepare a table like that you will be also able to control the value of money and include small increases in costs to your analysis. That is going to make your analysis much more real.

You can also prepare the same kind of table for salary increases as well. So that you can see how much money your institution will spend on salaries each month including raises over time.

2.3 Startup Costs

Startup costs are the necessary costs you have to pay before running your establishment. They are non-recurring, one-time initial investment costs. In this case, it could be the cost of land you are going to acquire, legal fees, licenses and permits…

Since startup costs are a different kind of cost, you have to calculate it separately from the other costs.

And also it is a non-recurring cost so you don’t have to create a time table for each month because it is going to be paid only for one time. You can create your own table just like on the table below.

So far we successfully input our data. That is what we needed to analyze the whole process. Revenue and Cost Assumptions.

Now it is time to analyze those inputs.

3.Reports

It may differ from people to people about what kind of reports to look in a feasibility study, however, we are going to share the ones that we thought it is necessary to have.

We have divided the Reports part into 5 sections.

• Financial Statements
• Break-even Analysis
• Profits trend report
• Sales Units Breakdown
• NPV & IRR Calculator

So let’s start with our first report.

3.1 Financial statements

Financial statements are the financial records for a given period of time which the company/institution keeps for itself to check its activities and financial measure.

These records are also audited by the government for tax purposes. It provides data about the company’s financial performance, current situation and cash flows.

Financial statements include three different types of financial documents which are Income Statement, Balance Sheet and Cash Flow Statement.

3.1.1 Income Statement

Income Statement indicates the financial performance of a company/institution given a period of time. It is also known as the profit and loss statement.

Income Statement focuses on four main parts which are Revenue, Gains, Expenses and Losses.

An income statement starts with the total revenue and by deducting the expenses starting from the cost of goods sold it goes until Net Income is calculated.

So in summary, it is the path of Total Revenue becoming Net Profit.

3.1.2 Balance Sheet

The Balance sheet is constructed of three parts. These are Assets, Liabilities and Shareholders Equity.

It is a brief summary of what your hotel owns and owes with its capital invested by the Shareholders.

The formula which works behind this system is: Total Assets = Total Liabilities + Shareholder’s Equity.

The logic of this formula comes from a company must either pay or borrow for the assets it has.

In the Assets, you may find properties (tangible to intangible) or rights owned by the business which have a monetary value.

Liabilities part includes short and long term debts, current and deferred tax liabilities, long term notes, and other long term obligations to finance its existence.

And lastly, Shareholder’s Equity is the amount of money that your hotel has, after all liabilities are settled.

3.1.3 Cash Flow Statement

Cash Flow Statement indicates all the money entering and leaving a company from its ongoing operations and investments during a given time period.

It could be said that the Cash Flow Statement is the financial statement that helps investors and others determine the value of the hotel.

Contrary to Income Statement, Cash flow statement tracks the cash accounting.

*CASE STUDY:

A) You offer the customer to pay $100,000 in cash in advance in exchange for your products. 
B) You offer the customer to pay $110,000 with an option to pay with a deferred payment in 2 months.

In Case A, your hotel sold its products to the customers and you had an immediate receive of $100,000. So the Total Revenue is $100,000 and the money is in your hotel’s asset part as cash. 
In Case B, your hotel inputs this sale to its system as Account Receivable. So the Total revenue is $110,000 however you may not receive it until a later date. There is still a moral hazard risk (which is the risk when the buyer fails to pay the amount of money he/she is pledged to the receiver). Your hotel is in profit according to the income statement and it pays income tax on it but the money which should suppose to be transferred may come in more or fewer income figures.

Cash Flow Statement is divided into 3 main sections which are Cash Flows from Operations, Cash Flows from Investing and Cash Flows from Financing.

• If CAPEX increases from investing that means there is a reduction in cash however your hotel is making investments for its future business activities. A higher CAPEX figure indicates a cash outflow from your hotel.
• If there is a positive cash inflow in the investing section it means you sold some of your hotel’s property and/or equipment to generate cash.
• If the cash flows from financing is a positive figure it means more money is coming into your hotel than it outflows. However, if it is a negative figure we could say your company is paying its credits or distributing dividend payments.

Now, let’s take a look at how to make a Break-Even Analysis to make our study much stronger.

3.2 Breakeven Analysis

Breakeven analysis is a financial tool which shows the required number of products to be sold and the price level to be able to get the “zero profit” point.

Let us elaborate…

You opened your hotel.

You had startup costs so far and each period your hotel stays operational, it also generates fixed and variable costs as well.

The profits you will earn must cover startup and those costs mentioned at some point but WHEN?

The break-even point indicates the level where all your start-up costs are covered with your project’s cumulative profit.

Once it covers those costs and your project starts making real profits, your company or project will be above the break-even point level.

If you would like to get a simple break-even calculator, you may get our free break-even calculator Excel template from the link below:

Break-Even Analysis Excel Template

3.3 Profits Trend Report

This report shows you the EBITDA each month. You can actually see if you are on profit each month compared to your sales revenue.

*PRO TIP:
EBITDA is earnings before interest, tax, depreciation and amortization each month. All of your variable and fixed costs are deducted by your sales revenues and depreciation & amortization is added on it.

Depreciation & Amortization is an imaginary cost which depends on your equipment’s lifetime and price value. The more you use the equipment you bought to operate your business, the more likely it is going to get broken and it is a negative asset value that should be deducted from Income Statement. 

So because it is an imaginary, non-existent factor it is added up to show the real profits each month.

3.4 Sales Units Breakdown

It is important to check your monthly profit report however it is also important to check the properties of the services you are providing.

After all, your services are helping you to make profit so why wouldn’t you measure the performance?

Preparing a Sales Units Table would allow you to analyze your sales for each product and service you are providing. It may show you different opportunities to improve your strategy.

3.5 NPV & IRR Calculator

NPV (as known as Net Present Value) is the initial investment plus the difference between the present value of cash inflows and the present value of cash outflows over a period of time.

Present value means the current value of the project including the future cash inflows and outflows generated by business operations.

It helps us to calculate the value of your hotel including the future cash inflows it is going to generate. This is a method you may use to determine your hotel’s value.

The formula for NPV could be written as:

IRR (as known as the Internal Rate of Return) to estimate the probability of potential investments. It is the minimum discount rate to reveal which projects will bring a decent yield and be worth investing.

You may calculate IRR by summing up the present values of the cash flows and equal them to zero as the formula below points:

By knowing the minimum yield your hotel should bring, it will be much easier to analyze and know the financials of your Hotel.

*PRO TIP:

- Having Net Present Value and Internal Rate of Return in your reports will help you to optimize your capital budgeting much better. 

- It will help you to decide whether a project or an investment is worthwhile by looking at its value and minimum yield it should suppose to generate.

FINAL THOUGHTS:

Feasibility studies could help you quantify your assumptions and gives a numerical output to decide whether it makes sense to take that particular action or not.

The purpose of this article is to explain the financial structure of establishing a hotel and give you a general perspective on the process. So, now you have a general knowledge about how to make hotel financial plan.

Now you may start and create your own hotel feasibility study in Excel. Or check out our template if you don’t have time to make one: Hotel Financial Model Excel Template

Hope this guide helps you with your own feasibility studies. If you think we’ve missed to mention something, just get in touch with us!