How to Make a Financial Plan in Excel Before Opening a New Restaurant: Step by Step Guide

Contents

• Intro: Profit Consideration & Start with an Excel
• 1.0 – Menu Items and Sales Channels
• 2.1 – Sales Projections
• 2.2 – Pricing Assumptions 
• 2.3 – Revenue Assumptions
• 3.0 – Costs Assumptions
• 4.0 – Reporting
• Final Thoughts

You decided to establish pathways for your dream: Opening your own restaurant.

You are super excited to bring the concepts down into a tangible space and get things rolling.

What’s next?

Accounting probably wasn’t one of the things attracting you. But it’s no secret that you need to know if your restaurant makes financial sense or not.

Because knowing numbers really help to foresee your financial situation before you hop on this cut-throat industry.

It is not hard as it sounds and you don’t need master in accounting and finance. You just need some guidance to do it.

This blog post outlines step-by-step what you need to know about the financials of restaurant ownership and walk you through how to set it up in Excel. 

Simple Truth: Profit!

Let’s switch to bird’s eye view.

Your child loves your delicious homemade pancakes and you’ve been asked to make some for the School Bake Sale.

Now, what would be your first consideration of pancake sales?

Profits, right?

Probably you would sell your pancakes at a reasonable price to cover your “cost of goods sold”. The sum cost of flour, baking powder, white sugar etc… Because it should be WORTH to get your hands dirty and raise some money for profit.

In case your cost to produce a single pancake is $1.50 and these pancakes are sold for $3, this works great.

What about selling for $2? Still makes sense? Well, depends. You can dig deeper and make assumptions on costs or daily sales if 0.5 dollars cover your efforts.

Here is where the feasibility study comes into play. Let’s focus on some real-life examples and highlight basics in Excel.

Starting out your Restaurant Financial Plan Spreadsheet:

There are two main concepts that you probably already know what it is: Revenue and Costs.

Before considering parameters like labor cost or cash flow, first, let’s visualize the general structure of the spreadsheet:

In four simple steps, we will show how to make your feasibility financial plan for your restaurant.

So, let’s start with the basics: your menu items.

1.0 – MENU ITEMS and SALES CHANNELS

First of all, you can group your items as Food and Beverages to organize the overall look and simplify the calculation of food cost. And input your menu items according to these 2 groups.

In case you offer more than one sales channel for the customers who prefer eating somewhere else, let’s consider to give two other options: Take-Away and Online Order.

Put your menu items in your new sales channels as well.

• Main Courses – Take Away
• Main Courses – Online Order

These will be summed up for your sales projections:

If you need a tool to calculate your projections, our Food Cost Calculator in Excel may help you.

2.1 – SALES PROJECTIONS

Sales projections make your restaurant’s ROI* easy-to-understand and bring clarity about how a profitable business is managed right. When you estimate your sales volume over a period of time, you can use that information for future sales forecasts.

ROI (Return on Investment): “Return on investment (ROI) is a ratio between net profit (over a period) and cost of investment (resulting from an investment of some resources at a point in time). A high ROI means the investment’s gains compare favorably to its cost.”

Wikipedia

Needless to say, inputting total sales volume assumptions per day gives you more accurate results but it may be hard to define it from the very beginning. So, keep it weekly for now and write down the sales volumes of each week.

Now according to your menu items and sales channels, you will see something similar to this:

Here, you’ll notice that we input sales by unit NOT the money you make each week.

Because sales volume does not quantify the monetary value without pricing (which we will touch shortly). Look at this:

If you make sales assumptions only based on sales revenue it won’t be an accurate estimation. It won’t let you control your price changes.

Now, let’s go one step further and expand your sales volume on a monthly basis.

Multiply weekly sales by four:

But, things never stay the same: Consider the increase in your sales volume. You may attract some customers to your restaurant even in one week. So keeping it weekly / monthly / quarterly / yearly would work the best.

As you see, sales projections are increased by 2% per month here:

We’ve formulated future assumptions including all time periods but you may enter yours manually:

Now, you should also carefully consider pricing on your products.

2.2 – PRICING

If you’re looking to improve your company profits and don’t know where to begin, focus on the pricing. Run scenarios on different pricing strategies for yourself and see what it leads to.

For example, the customers online would be glad if they pay lower than the Sit-Down service. So, pricing for different sales channels may drum up your business.

Let’s put the unit price for each sales channel:

In fact, no one feels like “making enough money”, but even covering your costs may lead to get some profit and perhaps grow your business. So take your time to think about pricing. You don’t want to flush your money away.

As in previous sales volume examples, pricing will not remain the same over time so let’s prepare a table for increases on a weekly, monthly, quarterly or yearly basis.

In case the yearly price increase is set to 5%, the following period results will be adjusted as:

Now it is time to multiply volumes and pricing and come up with the revenue we will make with our restaurant. The best part!

2.3 – REVENUE

We all want predictable revenue, right? This obviously takes time and lots of trial-error.

But if you’ve read this article so far, you have enough data to generate your revenue: Sales Volume and Pricing. It’s a simple multiplication of units and prices.

Here is your possible monthly revenue. You can expand this data according to your projection years:

Now, let’s take off our rose-tinted glasses for a moment and look at the cost of doing business:

3.0 – COSTS

Seeing that you made money on paper, but not actually having the cash would not be surprising if you ignore costs.

In this section, you are not going to read anything you don’t know but we’ll remind you of a few things about 3 types of costs:

Variable costs might be hard to define in the first few months. These are the costs that are affected by the change in the production quantity of a product.

Fixed costs are independent costs that won’t be affected by selling one more unit of your product or service.

Start-up costs are the initial investment costs you spend before your business is up and running. This may affect long term plans of the restaurant so it requires careful planning.

Next, you’ll figure out all the costs of your restaurant.

3.1 – VARIABLE COSTS

The most common variable cost of a restaurant is the food and beverages. Here, instead of calculating separately, add product unit cost (COGS) to your sales assumptions table:

Then, you can extend the time period monthly by considering unit product cost increase:

Next, multiply Unit Cost and Sales Volume to calculate your total COGS:

**FURTHER INFO: Gross Margin

Revenue and Cost of Goods Sold will give you the gross margin value of your restaurant. Gross margin shows whether your sales are sufficient to cover your costs or not and calculated as:

Gross Margin = (Total Revenue – Cost of Goods Sold)/Total Revenue x 100. 

It is hard to say “ideal” gross margin percentage because what is good for one industry may not good for another. That said, there is no average gross profit margin for a restaurant business, but when we look at the industry as a whole gross margin around 25% and 35% would be good for you.

Let’s continue with fixed costs:

3.2 – FIXED COSTS

You need to pay your fixed costs such as rent, electricity, salaries, maintenance regardless of your sales performance (that’s why they are called “fixed” costs).

So, no need to make calculations here, you can simply create a monthly fixed costs table for your cost predictions:

Clearly, from a financial standpoint, fixed costs seem easy-to-calculate but it is another important factor for the feasibility study.

**PRO-TIP:

In case your total salary costs, 

- exceeds 50% of your revenue OR 
- lower than 10% your revenue

Something may be wrong and needs reconsideration.

Try different scenarios to understand the drawbacks of what you might be doing. The more different cases you experiment, the more facts you’ll get.

Next, consider your startup costs.

3.3 – STARTUP COSTS

Startup costs are probably one of your main concerns about becoming an entrepreneur. You can throw as much money towards white elephants but you should try to evaluate costs, risks and potential benefits.

This is important to predict the time you’ll pass the zero line.

Now, list out all the costs and create a table similar to this:

Operating will be costly in the early stages. It takes time to grow your wings and cover all expenses to generate some profit.

Here you may be wondering the break-even point of your restaurant. In simple terms, how long does it take for your restaurant to make profits.

**FURTHER INFO: Break-Even Analysis

According to the industry standards, it may take 1,5-2 years on average but mostly depends on industry/company size. While it takes 5-10 years to reach the break-even point in large-sized companies, small businesses may cover their total costs even in 6 months.

Now you have all the information that hopefully makes sense of the big picture.

• Menu items and sales channels,
• Weekly sales volume
• Unit price for the products
• Revenue
• Costs

Let’s compile all the results that you find so far:

4.0 – REPORTING

In our EBITDA report by using the values above, we just started to make profits in the nineth month. But be careful, that’s not a cumulative profit. That shows a specific profit made only in that month:

In order to be successful with our restaurant business, we should cover the startup costs and previous months’ losses.

Let’s check our break-even analysis and cumulative profits as well:

Wow! With these prices and plan it takes almost four years to cover all the expenses and start making “real profit” from our restaurant. That’s a bit long.

Maybe we should increase the prices? Or sell more? Or make the same sales with fewer employees?

You may also check your gross profit and identify the profit earned on each product. Because it tells how you are using effectively your restaurant resources.

Here, it is all down to what your strategy should accomplish and how. Financial feasibility will only help to find some answers for your road-map. So, it’s time rolling up your sleeves to build well-performing business strategies.

In addition to these, keeping financial statements properly provides an overall financial snapshot of your restaurant:

FINAL THOUGHTS:

Financial feasibility study may determine the actions you take, answer some questions and identify whether your plan is likely to succeed.

The goal of this article is to provide a framework consolidating necessary financial aspects for those who have limited knowledge of accounting or finance.

You can plan all you want, but without doing nothing happens. So start to create your own financial plan. Or check out our template if you don’t have time to make one: Restaurant Financial Plan Excel Template

It doesn’t hold the secret formula for successful restaurant business but it gives a structured plan for every financial information you have and reports your restaurant financials accurately. At the end of the day, you can use that data to run your business effectively.

We hope this guide is useful for your own studies. If you think we’ve missed something, we will be very happy to hear from you.

If you’re about to get into restaurant business, we also recommend you to take a look at our Restaurant Excel Templates Bundle with special discount.

Cheers!