Financial Model for a 48 Seat Burger Restaurant in Excel

Would you like to open a burger restaurant and do you need a financial model for it?

Then you are exactly right here.

What is a financial model?

A financial model represents the most important economic relationships of a company. In a restaurant, for example, sales per customer, customers per day, prices, product range, seats, amount of cooks and waiters, passersby-frequency, financing, profit margin, liquidity, capacity and so on.

What´s the purpose of a financial model?

The purpose of a financial model is to build a sandpit in which you can test different scenarios. Creating a financial model is recommended both when setting up a restaurant and later during ongoing operations.

How can restaurant founders profit from a financial model?

As a founder, you can try out different framework conditions in the sandpit without any risk. If you plan to open a restaurant, you should use this advantage in any case. If you calculate different concepts and variants of a restaurant, you also get a deeper understanding of the success factors.

To find the important success factors, you can start with the 4 Ps of marketing:

  • Product,
  • Price,
  • Place and
  • Promotion.

Then you can dive deeper into more specific thoughts:

  • The size of the restaurant – the number of seats.
  • The complexity of the recipes and the resulting workload and personnel requirements
  • The pricing policy – high or low prices.
  • The scope of the menu and the resulting amount of work.
  • The pedestrian frequency of the location – high frequency and higher rental costs or low frequency and cheaper rent.

How to use a financial model

The way you work with a financial model also depends on whether you are a founder of a new business or an operator of an existent restaurant.

As a founder, you have to make many assumptions that are sometimes very difficult to assess. Therefore, be careful with your estimates. On the other hand, as a founder, you have to have the courage to make these estimates. To begin with, you simply start with numbers that make sense, even if you don’t know if they actually apply. However, the good thing is that you can constantly revise and improve a financial model. Therefore, as a founder, you should take a lot of time to test your financial model from many different perspectives. In the beginning, anything is basically possible, and this endlessness can be intimidating. Please don’t let that put you off. Just dare!

The operator of an existing restaurant has to master different challenges. He already knows many data, such as sales, customer frequency, personnel, etc. But his goal is to improve his business and to make it more profitable. A financial model can be beneficial to accomplish this task. He enters his actual data into the model and can start to play in his sandpit. Then the operator begins to change some things, to discover their impact on the result.

How can you set up a financial model for a restaurant?

In short, these are the steps to create a financial model:

  1. Set an income goal
  2. Determine the opening days per week
  3. Get clear about your targeted customer. What will he or she consume and how much will he spend?
  4. How many customers can you get per day?
  5. Create recipes, calculate prices, and know your cost of goods (COG).
  6. Define the average costs of one employee (cook, waiter, etc.) and plan the personnel requirements.
  7. Estimate the monthly rent or take this cost position from an actual offer.
  8. Consider other fixed costs, which are on average 18 to 25% of sales.
  9. Bring all figures together and set up the monthly budget. Profit or loss will show you, how promising your plan is.

How to project restaurant sales

To estimate sales of a restaurant, you need following data:

  1. Average sales per customer (SPC)
  2. Amount of customers per day (CPD)
  3. Opening days per month (OPM)

Formula: $ SPC * CPD * OPM = Estimated sales per month

How to know the Cost of Goods for a restaurant

Cost of goods sold (COGS) is one of the most important cost in a restaurant. COGS is the money you have to pay for all ingredients you need to cook a meal or to prepare a beverage. For example, if you sell burger, you need buns, meat, spices, sauce, tomatoes and potatoes. COGS is measures in Dollar per portion and in percent of sales.

COGS Reference Values in % of Salesfromto
Food20%30%
Non-alcoholic beverages, wine and beer15%25%
Coffee and tea5%15%

How to estimate personnel costs for a restaurant

You can forecast the personnel costs of a restaurant in two steps. Step one is to determine the personnel requirements. This is the answer to the question, how many employees you need to handle the projected sales. Divide sales by sales per employee to get an estimate. The benchmark for sales per employee per month is between $5,000 and $7,000.

Formula: Projected sales / Sales per employee = Amount of required employees

Example: $15,000 Sales / $7,000 Sales per employee = 2.1 Employees

Step two is to get the average cost per employee and multiply it with the amount of employees.

Formula: Average cost per employee * Employees = Personnel costs

Example: According to the U.S. Bureau of Labor Statistics Annual mean wage in the restaurant industry in 2017 was $25,970. But the standard wage does not include all costs. You can estimate them with an employee cost calculator. The annual employee cost in New Jersey would be $29,260 or $2,439 per month, to continue this example.

How much should you pay for rent?

The rent for a restaurant should be about 7 to 10% of sales. Consider the passers-by traffic in front of the premises, which has a huge impact on your sales. Passers-by can become a customer quite easily. But to get further customers through the doors you need a lot of marketing effort, which causes marketing costs. A stable stream of customers in front of your door is precious makes the plan of a startup more reliable.

Which other fixed costs arise in a restaurant?

To get a complete budget, you need to consider these costs:

  • Energy costs
  • Fees
  • Insurance costs
  • Operating costs
  • Marketing costs
  • Administration costs
  • Maintenance costs
  • Depreciation
  • Leasing costs
  • Interest

In total, the benchmark for these costs is 18 to 25 % of sales.

How can you forecast the profit of a restaurant?

You can calculate the profit in two steps. Step one is getting sales. Step two is deducting the costs. As a result you get profit or loss.

Step 1: Amount of sold products * Prices = Sales

i.e. 1000 burgers * $15 = $15,000 Sales

Step 2: Sales – Costs = Profit or Loss

Example:

Monthly Budget
+ Sales+ $20,000
– Cost of goods sold– $5,000
– Personnel costs– $5,400
– Rent– $1,400
– Other fixed costs– $4,000
= Profit= $4,200

Getting started with Rest-O-Sim

Before you get started with your financial model, I would like to introduce you to Rest-O-Sim. We developed this app especially for founders who want to express their restaurant idea quickly and easily in numbers. Rest-O-Sim is a straightforward financial model to simulate sales, customers, employees, costs, and profits. The data entry is made using sliders so that you can create your first simple budget in 5 minutes or less. Just try it out. It is available for free.

Click to create a budget in 5 minutes!

Financial model template for a burger restaurant

Below is a financial model for a 48-seat burger restaurant. This Excel template is part of the new book „Business Plan to Start a Restaurant“. For more info, click on the book!

  1. Profit Target

So that you steer your budget in the right direction right from the start, you should first define your goal. In this case, the founder is aiming for a monthly income of $ 4,000. The investments are expected to be around $ 150,000 and are expected to amortize within 36 months.

2. Location Potential

Before you decide on a location, you should assess the sales potential. You can win your customers primarily from passers-by and also from neighboring residents.

3. Sales Plan

Create a sales plan by defining important facts such as guests per day, opening days and sales per guest.

4. Sales distribution

With sales distribution, you can divide sales into different categories.

5. Capacity

In order to create a realistic business plan for your restaurant, you should calculate the capacity and utilization in the kitchen and service.

6. Recipes

Recipes are the basis for quality and profit. Take enough time to calculate every single item on your menu. The recipe template also takes into account the loss due to shrinkage, so that you can calculate a realistic COGS.

7. Contribution margins

Contribution margins correspond to your sales revenues minus the cost of goods. Based on the sales planning and the recipes, you get contribution margins which should cover all other costs and your profit.

8. Personnel requirements & costs

The model calculates the need for cooks and waiters based on your entries for sales, preparation time and service time.

9. Rent & lease

If the rent is more than 7-10% of sales, it becomes difficult to make a profit. You can also enter a target rent percentage in the model to calculate the target rent.

10. Investment & Depreciation

The depreciation takes into account the cost of wearing down a restaurant’s equipment.

11. Financing

When financing, you can also calculate the interest expense on loans.

12. Other fixed costs

The other fixed costs should be in the range of 18 to 25% of sales.

13. Income statement

The income statement starts with the contribution margins, subtracts the remaining costs and calculates the profit or loss.

14. Liquidity plan

In the liquidity plan, you can plan 12 months into the future so that you always have enough liquid funds.

This 14 sheet financial model for a restaurant is an Excel tool, that comes together with the book „Business Plan to Start a Restaurant“.

Click on the book to get more info!