Business model of a restaurant in Excel - basic model

A free video tutorial from Asen Gyczew
Expert in performance improvement, turnaround and startups
Rating: 4.5 out of 5Instructor rating
75 courses
221,249 students
Business model of a restaurant in Excel - basic model

Lecture description

Excel gives you great opportunities to calculate whether your idea makes sense or not. For this you will use the knowledge you have gathered in the previous lectures on your concept, location and your customers. Excel is obviously less emotion than you and the model you will create here can be used as a cornerstone of your business plans. It shows also to banks and potential investors what is your cash flow. We start with a simple model so you can understand it and learn how to use it. 

Learn more from the full course

Financial Modeling for Startups

Learn how to model a startup business in Excel and draw conclusions about your business

02:50:32 of on-demand video • Updated December 2023

Model your business in Excel
Manage your business based on numbers / KPIs
Set well priorities to value creating activities
Pick the right business model for your business idea
You will understand what is important in your business
You will know what KPI you should look at and what are the benchmarks
You will be able to write down your business model in the form of an Excel model and to draw conclusion out of it
English [Auto]
Russian model in exile. It is very important to at some point transfer, you know, your ideas about your concept into exile and to see what it actually makes economic sense or under what condition it would make sense. Therefore, we are going to be talking about this subject here. So how to translate it into exile? So when you want to set up a restaurant, you have not only to face the monthly recurring costs for food, especially labor, but also invest a huge amount of money into the place on the renovation is also on furniture, etc. Roughly, this is what you will be facing. Names of the categories. So equipment design in the beginning, also buying furniture, which have to be remodeled every four or five years. Computer cash until system was all sort of domestic appliances and other stuff like uniforms for the employees and say the purchase of the place or rent of the place when we are talking about the monthly recurring expenses when it comes to other monthly recurring costs. You obviously have all the third party service like bookkeeping and the spending on the stock, utilities, food and drinks. Obviously, cleaning costs, personal, etc. On top of that, you also have to invest a lot of cash into the stock. So it's from one week to two months, depending on the type of a concept you are using and how you do it. We are going to be huge just showing the the profit and loss account on how you estimate the profit of the business will not be going that much into detail when it comes to the frozen cash. Before we go to next, just a quick warm up in terms of the model, and we are going to be talking about three models. So in the basic one, this is what we're going to have. So let's start with a simple model. So we saw that with restaurants where we just look at the number of meals we will be producing per day and the number of days of monthly would be 30 days by the number of meals assumed for day. Then out of this, we get number of transaction when we assume some sort of average transaction value. We are able to get the revenues of our small restaurant and then we should assume some sort of a gross margin, which will help us generate the total gross margins on how much we earned after the cost of food. So revenues minus the cost of food gives us to gross margin. And then we have a bunch of other costs, like the cost of marketing, franchising fees, other variable costs and we get the so-called net margin. And then at the end, we deduct the so-called fixed costs. So in our case, it could be the rent, the labor and obviously the headquarters if we have them all get simple and this should get cost the operating profit of the business or if you stop it before the depression, your amortization, it will give you a bit dumb. Now let's go to Axel, and I will show you how to calculate it in in there as well. So this is our model in Excel. In this case, we're going to be just assuming they were one restaurant and we're going to try to estimate how much we're going to and from it. A quick warning we used to call us to tell us some things. So basically, if you have white, then we have a formula. So if it's here whites, then it means that there is a format or something is automatically being calculated. And then four assumptions we use to call us. So four unknown assumptions. We used the blue one and four known assumptions, like the number of days per month we can be using the yellow one. So as I check assumptions, I should also change the color. Here obviously means that I have assumed some average ticket in month number one and which is then fed into other cells here, as you can see. The other thing which is worth mentioning is that, uh, we have this pyramid principle. So whenever there is something being calculated, then there are that variables impacting it are usually just below it. So if we want to look at them, use salt, then they depends, or they are calculated using the use of per day and number of days. So they are found here. The structure of the X is a bit different and I show you in the pipelines just to make it a little bit simple. So we start with the revenues, as I said. We calculate them using the meal salt, which actually depending on meals sold per day and sales day, as you can see here. This is basically the 100 we assume per day multiplied by 50 days. For the time being, we assume that you've got the same number of days all over. And then to get the revenues, as we said, we have to assume some sort of average ticket average, uh, value of the transaction. So assume here 15. And then out of this, we get in in thousand forty five key of revenues per month. Now, as we said, we can either look at the gross margin or the food cost. For simplicity, I've chosen the food cost, so we sold the average meal for 15 and actually the the cost per unit is six. So here we actually calculate how much. What was the cost of the food we get? You need salt from here. So we don't repeat it manually. It's enough for me to produce one hundred and then it's recalculated to three hundred. So if I change it to 200, this is what I should get. Yeah, this is recalculated in both places here and here. So we go back to the one and this. So we get the food cost, which is 18, and then we calculate the gross margin, the gross margin is calculated, the difference between the average ticket, which we had put here, Andrew, which is fed into this cell and then also the cost per unit, which was found here and is taken here. So we get nine per unit, which gives us the twenty seven of 27 per month and this is our gross margin. So the revenue minus the food cost give us the gross margin and then we then divided to fixing the variable cost. We just put all the operational costs below, and what we can find here is the royalties. So if it is something you take as a franchising, you would have to pay you a percentage of the value, then also rent and labor marketing division orders. We can go to a different level of details, so if I press to want to hear what I'm actually saying aggregated data. So here all of a sudden this disappear. So I just see them. You sold an average ticket of VTi. And same goes for two years. I don't actually see how this is generated. I just see the revenues. And if I go in more, do this. I press this or the two here above, I get the data. So, for example, royalties are calculated as a percentage of revenues and here is zero, because this is for the time being, just on a restaurant, the rent is calculated as a number of square meters I have and some sort of price per square meters I've got and then actually should be the other way. Sorry, guys. So let's say it should be 100 the size and then this is, say, 20. So the rent is basically calculation of rent per square meter and also on the size. And then what happens is I calculate the labour. So Labour, we calculate basically by assuming number of fees and some sort of a cost of 50 by FTE, we mean a full time equivalent. So it's basically somebody who gives me one of the 70 hours per month. It doesn't matter whether it's my somebody who employs the contractor, I basically should calculate all of them in one bucket and then obviously marketing activities and other costs which are here divided in some categories. And at the end, we've got the remaining of others, which is a 2% assumed on revenues. And this is how we get to the operational costs. When we take away from the gross margin operational cost, we go to the bidder, which is here it is 27 minus five, gives us the 22 and beat to store level. If it's, let's say, a new one in the beginning, you have to assume also on the building costs of the establishment. So here is the investment needed and then I have the cash flow here. So this is how much cash I'm making from this restaurant. So the first month, let's say I'm a minus because I have to spend roughly almost two on this case and then I'm making some money here as well. He obviously, the margin is very, very big, which is not realistic because it just put some numbers here. But if it's above seven percent, then it probably makes sense to invest it. If if it would be, let's say, around zero, then it obviously doesn't make sense 15 20 something which is really good and not that easy to achieve. The best, I think, in terms of on the store level would be making 30 percent, but this requires actually either very good locations or like a very good scale. So this would be in short and the most simple model of our of our restaurant. I can play with it so I can, for example, increase or decrease the average ticket here. Or I can, you know, decrease the number of of the sold meals per day and this will be recalculated automatically. So I change the two things. I think you see the doing the bit the store level went down. The good thing about this model is actually that you can, with not that many assumptions, really see how it's impacting in a minute. I'm going to show you more complicated models, but please start playing with this one for beginning because they are like just a few parameters that impact the home little. The next one, they will be much more so it will be more difficult to manage it as the first model. So have a look at this. And if you have any questions, ask me. I'll be more than happy to answer and explaining the details you have to understand.