Business model of a restaurant in Excel - basic model

Asen Gyczew
A free video tutorial from Asen Gyczew
Expert in performance improvement, turnaround and startups
4.4 instructor rating • 53 courses • 100,989 students

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:31 of on-demand video • Updated July 2021

  • 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] Recent model the axle it is very important too. At some point you transfer your ideas about your concept into excellent 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 that into Excel. So when you want to set up a restaurant you have not only to face the monthly recurring costs for food especially labor and friend but also invest a huge amount of money into the place that your innovation is also finished etc.. Roughly this is what you will be facing in terms of categories. So equipment design in the beginning. Also buying furniture which have to be remodeled every four or five years. Computer cash until system pools or sort of combines and other stuff like uniforms for the employees and the purchase of the place or rent over the place when we're talking about the monthly clearing expenses when it comes to month other monthly recurring costs. You obviously have all the third party services like bookkeeping and depending on the stock utilities food and drinks obviously cleaning costs personal et cetera. 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're using and how you do it. We're going to be here is just showing the profit and loss account on how you estimate the profits of the business will not be going that much into detail when it comes to the frozen cash. Before we go to the or just a quick warm up in terms of the model and we're 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. So we saw restaurants where we just look at the number of years we will be producing per day. And the number of days a month will be 30 days by the number of meals a day. Then after this we get number of transaction when we assume some sort of average transaction value. We are able to get revenues of our small restaurant and then we should assume some sort of a gross margin which will help us generate the total gross margin. So how much we earned after the the cost of food. So revenues minus the cost of food gives us our gross margin. And then we have a bunch of other costs the cost of marketing franchising fees are the 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 around labor and obviously the headquarters if we have them allocate some. And this should get the operating profit of the business or if you stop it before the Depression Zeeshan it will be done. Now let's go to Exel and I will show you how to calculate it in there as well. So this is our model and Exel. In this case were going to be just assuming they were one restaurant and were going and trying to estimate how much we go to and from a quick warning we used to call us to tell us some things so basic if you have Whyte's then we have a formula. So if its here Whyte's then it means that there is a format or something is automatically being calculated. And then for assumptions we use to close so for unknown assumptions we used the blue one and the 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 we have this permit principle. So whenever there is something being calculated then that Vibro is impacting it are usually just below it. So if we want to look at them sold then they depend or they are calculated using the salt per day and number of days. So they are fans here. The structure of the exit is a bit different and I show you the power point just to make it simple. So we start with the revenues as I said we calculate them using the meal sold which actually depending on milk sold per day and sales day as you can see here this is basically the one on that resume per day multiply by 40 days for the time being we assume that youve got the same number of days over. And then to get the revenues as we said we have to assume some sort of average ticket average value of the transaction and so we 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 look at the gross margin all the food cost for simplicity I chosen the food cost. So we sold the average meal for 15. And actually the cost per unit is six. So here we actually calculate how much was the cost of the food we get stuff you need sold from here. So we don't repeat its money. Its enough for me to purchase this 100 and then its recalculated to 300. So if I change it to 200. This is what I should get. This is recalculated in both places here and here. So we go back to the so we get the food cost which is 18. And then we calculate the gross margin gross margin is that there's a difference between the average ticket which we have put here which is fed into this cell. And then also the cost per unit which was set 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 didn't divide into fixed and variable costs we just put all the operational costs below and what we can find here as the royalties. So if it is something you take as franchising you would have to pay here a percentage of the value then also rent and labor marketing within others. We can go to a different level of the audience. So if I press to want to hear I'm actually seeing aggregated data. So if you're all of a sudden disappeared so I just see them you sold an average ticket. And same goes for years I don't actually see how this is generated. I just see the bodies and if I go and do this I press this order to hear above. Get the deal. So for example royalties on Colquitt as a percentage of revenues. And this is zero because this is for the time being just on a restaurant in the Kansas caucuses the number of square metres I have and some sort of price per square meters I got. And then I should be doing the rounds. Sorry guys I'd say it should be one hundred the size. And then this is 20. So the rent is basically calculational rent per square meter. And also the size. And then what happens is I calculate the labor. So labor we calculate basically assuming a number of fees and some sort of cost effective by fte we mean a full time employee Clo equivalent. So it's basically somebody who gives me one and then 70 hours per month it doesn't matter whether it's my somebody who employs the contract I basically should calculate all of them in one bucket and then obviously marketing activities and other costs which are here dividing in some categories and at the end we've got the remaining of others which is a 2 percent assumes on revenues. And this is how we get the operational costs when we take away from the gross margin operation because we gotta be the shoes here is 27 minus five gives us the 20 to be just on level. If it's let's see a new one in the beginning you have to assume also in the building costs all these supplements So here is the investment needed. And then I have the cash drawer here. So this is how much cash I'm making from this restaurant. So the first month 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 said just put some numbers here. But if it's above 7 percent then it probably makes sense to invest it if would if it would be let's say around zero then obviously doesn't make sense 15 20 something which is really good and not that easy to achieve. The best I think so far on the stock level would be making 40 percent but this requires actually a very good locations or like a very good scale. So this will be introduced in 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 fall of the sold meals per day and this will be recalculate it's automatic so I changed it to things that you see though be just all level went down. The good thing about this mall is actually that you can with not that many assumption 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 just a few parameters that impact the whole flow. 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 and I'll be more than happy to answer and explain you the details you haven't.