Empower Your Business with Sales Growth Revenue for IT

How to create outlook signature

welcome to another tutorial video this time around we're gonna be going through a revenue model for a consumer retail company we're using CEC entertainment otherwise known as chuck-e-cheese which is basically a kid's restaurant chain in the US they have video games they have arcades they have a lot of other things it was acquired by Apollo in a 1.3 billion dollar leveraged buyout and we're looking at it because it's an interesting example to learn this concept of how to build a revenue model so I have as always the excel file we're gonna be using here and I always like to start with why a certain topic is important and in this case building a revenue model it's a topic that could come up in case studies and interviews it could come up and Investment Banking private equity hedge funds they could give you something like this and an LBO case study they could give you a three statement model in case study they could even ask you normal interview questions about it and to show you exactly what I'm talking about here you can see here that I've listed the company's total revenue as well as the revenue growth rates the historical revenue growth rates underneath right before the deal took place now what we could do is we could go in and say you know what revenue is gonna grow at five percent next year and then four percent and based on that we're gonna take the old number multiplied by one plus five percent and we get two revenue numbers like that and you could do that and in many case studies that's all you're gonna do you will give them be given a percentage growth rate or you'll make an estimate on your own and calculate revenue like that but a slightly better approach actually a much better approach is to go up and think about what drives the company so for a retailer it would be the number of existing stores they have how much in sales each of them is generating on average and then how many new stores they're opening each year and how much in sales those stores on average are generating so we're gonna go through that and look at how you project some of those numbers and get to better grounded numbers for revenue here and in addition to being important in key studies interviews also in a lot of cases companies disclose a lot of data on the revenue side but not that much data on the expense side so it's often much easier to go into more detail on revenue projections than it is on expense projections unless you happen to get lucky and they give you a lot of information or you're working with them as a client and you have access to more detailed information now how do you actually set it up and what is it well the two main methods are to use units sold times the average selling price or the total market size times the company's percent market share and you'll sometimes use a combination of both of these you'll sometimes mix the two a bit but bottom line is that the method you use depends on first and foremost the available data the work and research you've done and then what the company actually discloses so for consumer retail in this example if you think about it it would be almost impossible to use something like total market size because it's a huge and fragmented market think about the restaurant market the kids restaurant market they're so big that it's almost impossible to establish also the company discloses a lot of information on average sales per store so if you go in and look at their annual reports they actually tell you how many stores have open historically they go through and tell you the average annual sales per store so they make it very easy to project the numbers like that and the goal with this type of model is to show what happens over the next three five ten years under a variety of different scenarios and assumptions so for example what happens if they only open ten stores instead of 15 stores what happens if sales per store only grow by two percent rather than three percent or 4 percent how does that impact revenue and how does that impact our returns if we're looking at this as an investment so how do you actually go about the process of building a revenue model it depends a bit on the industry I've listed a few examples here if you're working with something like a software company or a subscription company a software company that sells software on a subscription basis like Salesforce comm for example you look at the number of subscribers the average annual subscription the dollar amount for that the growth rate and the churn rate so how many people come in how many people leave each year if you're thinking about something like Airlines you might start with available seat miles you might calculate the segments flown the number of flights the average percent on each flight that's actually occupied and then the dollars per passenger and I have an example here for easy jet you can see some of the logic that basically what we're doing is looking at the available seat kilometers and then we're looking at the average sector length we're looking at the total number of seats that are flown so the total number of flights times the average passion countering each flight and then we're figuring out the seat revenue on each of those different segments and then just sort of multiplying everything through so that's how we get to revenue here I'm not going to go into detail because that's not our example but just to show you for a variety if you're looking at something like health care you might look at the pipeline of drugs or their products you might estimate the market size the launch date potential revenue and then of course for retail we would divide it into existing storage versus new stores assume an average sales per store or per square foot or square meter and then make assumptions for new stores open stores closed and how the sales figures change over time so what's the overall process here step one I'm going to divide this into a seven step process so step one is to get all the historical data that we actually need so in this case I've already done a lot of the work here and I've already filled in the information for us you can see that going back historically we have the total number of stores they separate into company owned versus franchise but it's mostly company owned so we're not going to pay too much attention to that they give the average annual sales per comparable store and then the number of stores that were actually comparable from year to year so we have a lot in that information and then if you keep going forward in the filings you can find all the information about the number of stores that were new the number of stories that were closed each year and so on and so forth so what I've done here to save us some time is I've already filled in a lot of this information and just go up here and zoom out so you can see this a bit better so I already have all the new stores open stores closed the growth and sales per store and so on and so forth I also have all the stats on the sales per store down here so that's step one and then step two we have to make assumptions for the number of stories that were opened and closed each year so in a lot of cases companies will actually tell you this in their filings or you can extrapolate based on historical trends it could also do your own channel checks you could speak with suppliers customers partners in the market you could look in equity research so there are a lot of ways you can come up with estimates for this but what I'm gonna do here is actually show you how the company points this out directly in their filings so we just set up a frame here so you can see this better and then if you go to their filings so filing from the year before this deal took place look at this they say here on page 39 that the growth line over the next four years to open approximately 50 to 60 new stores so they're giving you the number right there and you just have to make sure that you assume something in that range for your numbers to match up and then 12 to 15 they plan to open in 2013 which was just before the deal took place at a cost of approximately 2.7 million per store so they're giving you all their plans and stats upfront and so what we can do then based on that is go in and actually start filling in these numbers so we're gonna say 15 in 2014 because remember they said twelve to fifteen they may increase this over time because the company lately has been doing better they've been an uptick whereas in the past few years before this sales were falling and sales per store were also falling so we're gonna say 15 16 16 17 and 17 and if you just do a quick check of these numbers so let's just add these up over four years this comes to exactly 60 stores open so it's in line with their estimates although it is at the high end now one thing they didn't disclose is the number of stores they're planning to close that's the other side of this not every store is going to perform well and if you look back at this historically they've closed between two and six stores each year so we're gonna assume something in that range about the same as what they've closed in the past two years so I'm gonna say negative 5 negative 6 negative 6 negative 7 negative 7 and by negative of course we're really just doing that so that later on we can just add this up and have it be reflected in the total store account now for the growth and sales per comparable store and growth and sales per new store so this one is a little murkier because the numbers are all over the place here the safest thing to do in this scenario growth and sales per comparable store this rarely changes by a huge amount so we're just gonna sort of assume flat growth here they had one point three percent right before the deal took place we're gonna say 1 percent going forward each year to be conservative and because we really don't have any other insights into the data the numbers jump around a lot historically the growth and sales per new store again we're gonna mostly follow the sales per comparable store here because we have less faith in these numbers new stories tend to be a lot more volatile but for modeling purposes we like to use some more consistent numbers so I'll say 0.5 percent 0.5% 0.5 % 0.5% and 0.5% each year and so we have our basic assumptions here of course we're going to tweak and modify these later on so we're done with that step of the process that was step two and then actually step three we've already assumed the growth rate in sales per comparable store so we're done with that step four we're gonna calculate the ending stores per year so in other words they add a certain number stores and then they close down a certain number of stores what does it look like at the end of that process and then after that we're gonna make similar calculations for the sales / new store and sales / existing store so let's go up and take a look at this and what I'm gonna do here is once again set up a frame so for the beginning stores each year we're going to just link this to the ending store number from the year before and then for the number of new and acquired stores so for this one we're going to take the number up here that we're assuming and then I'm going to multiply by something called the sensitivity toggle now to show you how to do this I need to remove the frame but the idea here is that we want to be able to tweak these assumptions and we want to be able to toggle this and make it go up or down by a certain percentage so I'm going to take this number and multiply by one plus the toggle right here and we have that and then for the number of closed stores we're gonna do the exact same thing we're just going to take the number up here and then multiply by 1 plus the number or toggle as I call it that let's add these up and then let's calculate the growth rate and stores as well and then what we can do is just copy this over and we can see that at the end of the period they own 572 stores after 5 years whereas 3 or 4 years before that they owned about five hundred five hundred seven stores expanded by about thirty so it seems reasonable they could expand by around forty stores total net over these next five years now for these other numbers similar approach for the growth rates and sales per comparable store we're just going to take our numbers up here and then multiply by one plus our toggle sales per store toggle and then do the same thing here so we'll take the number up there and then multiply by that toggle as well and then we'll copy this over and then what we can do is just take the old number multiplied by one plus the growth rate and then do the same thing down here and then we can just copy these cross and so we're almost done the problem though is that we need to split this into segments and we need to split both the revenue itself into segments and then also the stores into different segments so how do we do that well let's go up here and the first problem is that although we know the total number of stores each year it's not as simple as just saying the ending stores - anything we added this year those are the comparable stores because you don't know they could be renovating them they could be changing them around in other ways so a better approach is to look at this as a percentage of the total ending stores so I've taken the comparable store figures that they have in their filings and I've done that and what I can do here is just take a simple average of all these over the past four years and then we can carry this forward like that and then what we can do is just multiply this percentage by the ending stores each year and that gives us the comparable store count each year how is this useful well now what we can do is calculate total sales by comparable stores and new stores so to do this let's take our comparable store dollar amount here and multiply by the number of comparable stores and we'll divide by units so we can convert that properly and then for new stores so what we're gonna do remember comparable stores is based on the ending stores up at the very top of this so what we can do here is just take our total ending stores subtract the comparable stores and then multiply by the sales for new store and divide by units again to convert it add these up and then we can copy this over so you have that and then really the final step here is to divide this revenue itself all the store revenue into segments and specifically they sell food and beverages and then they also have entertainment in merchandise the video games near arcades and the other high-end gadgets and toys that I was talking about before so this is important because the margins on these will be very different if you take a quick look below you can see that clearly the margins on entertainment and merchandise expenses there are cogs are around 3040 million a year on revenue around 400 over 4 million the margins are going to be much higher on those than they will be on the food and beverage segment so that's why it's important to split this up in this case now here they have a clear trend going in one direction that they're moving away from food and beverages and moving into these higher margin items so we're gonna continue that trend and say 45 percent forty four point five percent forty four percent forty three point five percent at forty three percent and again it's just based on the fact that historically it's fallen by around four percent so it makes sense than the future to be conservative it maybe falls by two percent percent of revenue from that over the next five years and then to finish this off we can just take one and subtract that number and then what we can do really is a final step in this model is but now we can link in the food and beverage sales and the entertainment a merchandise sales say go total store sales multiplied by those percentages if that and then franchising fees and royalties this is so small we're not going to worry about it too much but to get this you can just take an average and then you can just link to these numbers then the average number historically and add these up and then calculate the revenue growth rates like this so we have that and that's it that's really all we have to do to get our revenue model in place so as you can see not terribly complicated but the way we send up here is very specific because look at this now let's if I wanted to go in and let's say I wanted to say you know what let's say that we have five percent more stores per year that are open what happens as a result well you go down you can see that revenue is growing now more like three percent per year we end up with nine hundred fifty million at the end versus before we had only nine hundred forty four million so it makes a difference but not as much of a difference as you might think now if the stores per year went up by twenty percent then we have more of a difference now we have nine hundred sixty million in revenue growing by over three percent per year and so that's really the point of building in these types of toggles and if you kept going at the model you could look at operating expenses capital expenditures you could go and fill in a whole lot of other things but this is just the baseline this is just the starting point for you and of course after this that's what you would do you would go through and fill in the expenses you go to the other financial statements and start filling in your projections for those so just to recap what we did here revenue models are important because you want to ground your numbers in reality rather than just assuming a simple percentage growth rate if you can do so and if you have the time to do so now to do it you usually look at units sold time's average selling price or market size times percent market share the best method really depends on the industry the company what they disclose how do you build it well I have a few examples here which I went through before but usually you start with historical data then you make assumptions for the key driver so in this case the number of stores opened and closed and how the sales per store changes those are really the key drivers for a retail company like this you assume a growth rate for those and you calculate the ending stores each year you make sure that you build in sensitivity toggle so you can easily modify the assumptions then you make similar assumptions for sales for a new store and sales / existing store you split the revenue into those segments and then you split it further into food and beverages and entertainment and merchandise and then for the final step you could go back and check your numbers we did that a little bit already you could go in and look at equity research and see how your numbers there compare so you can go and do that on your own if you want to see what other people are saying about this company at this point in time I'm not going to cover it here but that would be the next step in this process and then of course you can go back and tweak your numbers as necessary so what next go and try this yourself go and pick a company you're interested in an industry that's relatively easy to analyze in terms of key drivers and project revenue based on what's in their filings and what they disclose it doesn't have to be super complicated most companies honestly are driven by fewer than five key factors at least if they're only in one or two business segments you should avoid conglomerates some so something like General Electric for example or Samsung would be a bad example because they do so many different things that it's tougher to project there avoid companies with a lot of business lines or industries that are more complex like oil and gas commercial banking and so on a few suggestions for you to try would be Airlines technology companies consumer retail companies industrial or manufacturing companies Healthcare is a little bit iffy if it's a very simple company with only one or two or a few products you can do it if it is Pfizer or our company with thousands of product lines and different drugs and development you probably want to avoid it so those are a few suggestions but go ahead get started with this yourself and use it for practice and key studies interviews modeling tests and whatever else may come up along the way you