Revenue Model Example: Forecasting in Excel - YouTube

Hey everyone, we are back with another video and today we're learning how to build a revenue model  

in excel, and this will be geared towards people that don't have any prior experience building  

revenue models, maybe you're an entrepreneur trying to raise some money with investors or get a loan, 

but if you have no prior background this is the video for you. My name is Brandon Crossley, I am  

the CEO of Poindexter, and Poindexter helps people  do exactly what we're talking about in this video;  

create revenue models and financial statements  for the process of raising money from investors,  

or getting loans, and so we've helped thousands  of businesses do this so we have some experience  

in this space, and our goal for this video is to  show you how revenue models are built from scratch  

using some basic principles that you can then use  to apply to a wide array of different scenarios.  

We're not going to be able to cover every  sort of business model in this video,  

but hopefully, after you watch you will have the  skills to be able to ask the right questions and  

tackle more difficult scenarios as you encounter  them. We'll start by covering what a revenue model  

is, and quite simply it's just the process of  defining how the business will make money. When we  

talk about revenue modeling we're looking at the  success of the revenue model that we've defined  

over some specified time frame, and we do that by  taking some core variables that are important to  

the success of the revenue model, for instance in  this case we're looking at the number of customers,  

the average price they would pay, and then  multiplying those two items together to  

determine what revenue would be to build out  a very simple revenue model. This would be  

far more simple than anything you're going to  encounter in the real world; yours is likely  

going to be much more complex than this, so this is  the base level knowledge that you have, so you now  

understand, at least to some degree, what a revenue  model is. In a moment we're going to get into  

some of the tips and techniques that you can use  to add some real world complexity into your model  

without getting too convoluted. Before we do I  wanted to cover a simple graphic that I found in  

researching this video that will help distill the  revenue model down into five different components  

here, or five different questions that you can  ask yourself. It was made by the University of  

Tilburg if I get my sources correct, from quora at  least, and it allows us to ask five questions that  

will help clearly define, or more clearly define,  what our revenue model looks like before we go  

about modeling it in excel. So when we look  at the question who pays, we might think of  

the typical scenario of an end customer just being  the person that pays you and they get some product  

or service. That's not always the case, so for an  advertiser, or if we have an advertising business  

model, and we have a website where we're selling,  advertising the customer is actually not the user  

that comes to your site, it's the advertiser  that's paying you for access to your users.  

So that will change dramatically the  dynamics that are at play as far as  

how you go about attracting advertisers, and  setting up relationships and transacting with them  

and what sales cycles might look like, so there's a  lot of implications at play just simply by asking  

who pays for the product or service, and that goes  the same for the rest of these. So what is paid,  

for what is paid, how it's paid, and how much is  paid, so this is pretty comprehensive as far as  

just looking at the revenue model itself. I'm not  going to dive into each of these individually,  

but if you're interested in learning more, I did  find a helpful blog post by lucidity that goes  

into detail on each of these sections. I'll put a  link to the blog post in the description below if  

you'd like to take a look at that. But one of the  things that isn't considered by this revenue model  

is what is the feeding mechanism into this  revenue model? So, when we talk about revenue  

modeling in excel we're actually going to take a  step back and look at how do we fill this revenue  

model once we have all the structure in place for  the answers to the questions that we have here? 

What does that look like? And that brings us to  the next step which is our example model in excel,  

and right now it's blank because the best place  to start is with simple concepts, and then we  

can build upon those simple concepts into more  sophisticated concepts as we get further into this.  

so, right now all we have is a table that has a  heading of month one through month twelve, and that  

could be real dates, but right now we're just going to  use those because it doesn't matter, and I'm going to  

label our first section here, over on the left-hand  side, called leads, and this is that lead generation  

or customer acquisition side of things that I  had mentioned a few moments ago that the revenue  

model itself doesn't include, but when we're  modeling things out in excel we definitely need,  

otherwise there's nothing to put into the revenue  model. So, we're going to just identify a couple  

customer acquisition channels here, so  I'm going to say TV ads is our next one  

after google, then let's do billboards so we  have some variability. So, we have some real world  

advertising, online advertising, and I'm going  to assign some very basic forecasts here, so  

I'm going to start with just random numbers, and  500 let's say, and then I'm just going to apply a  

growth rate to each of these. So, I'm going to say  that each of these is going to grow by let's say,  

not ten, two percent per month. I'm just going  to drag that out, good, and then apply it to the  

other two channels, and there we go. So, now we have  a forecast from each of our customer acquisition  

channels, and next we'll just sum these up just  so we have a nice little total here at the bottom.  

Okay, and I'm going to add a border to  the top there, because I'm anal-retentive.  

And there we go. So now we have all of our leads  that are summed up here at the bottom we know each  

channel, and how many are coming from each channel.  So, pretty simple right now, we can get much more  

complex and sophisticated about how we set that  up for even arriving at each of these numbers, but  

that's what we're going to get into next.  So, now we have all of our forecasts for  

our customer channels, let's get into  what we have for our revenue model. So,  

one of our revenue models, or  one of our revenue streams rather,  

which a revenue stream is simply just one  part of your revenue model. You might make  

money in multiple different ways, but all of those  ways taken together is your revenue model. So,  

this product one, right now we're just going to  treat as our whole revenue stream, so we're gonna  

only sell one product. It's gonna have a price  associated with it, and we'll say it's 19.99.

Nope, okay that's what I get for doing this live.  And then i'm just going to paste that all the way  

down, cool, so if we're going to do the most basic  possible revenue model, all we have to do is say  

what is revenue from this? So, we have our price, and  we have how many people are going to purchase it,  

or how many leads we have assuming we convert 100 percent  of them, and boom we can calculate revenue based on  

the price and how many people are paying. And that  is all we would need to do if our revenue  

model is that basic, but in the real world, things  tend to be a little more complex than that. So,  

we're not going to get all the way down to revenue  just yet. There's a couple more steps along the way.  

The first of which is conversion. So,  we're going to have a conversion step.  

So, how many people are going to end up  paying, because usually, we'll start with  

a percentage here, usually not 100 percent of our leads  are going to pay. So, maybe we are really good at  

converting our leads, and then we're going to put  80 percent here just for the sake of argument.  

Maybe we have really targeted  advertising or something like that.  

If you have this good of  conversion, then congrats to you.  

So, now that we have a conversion rate we can say  how many actually convert to new customers.

And we'll just simply take our 80 percent, multiply  it by the number of leads, and there we go.  

Now we have how many new customers  we actually have that we can say  

are purchasing our product. And, so we can even  take this number, and then just multiply it by  

the price, and again arrive at revenue, but what  if, for instance, we didn't want to rely simply on  

each new month that we have in front of  us, the customers that we're generating that are  

new customers each month for revenue? What if we  wanted to have a reliance on some of the previous  

customers we had acquired back in month one when  we're in month six for instance? Because relying  

on new customers every month to drive your revenue  is going to get very costly, because you're reliant  

completely on the cost of customer acquisition  and it's much cheaper to rely on customers you  

had already done business with. It's much cheaper  to get revenue from people that, hopefully, you've  

treated well, that have liked your service or  business and want to do business with you again.  

So how would we go about modeling that scenario  out? Well, first we'd want to start with what is the  

number of customers we have at the beginning  of the month, so we'll say "Beginning Customers."  

For a new business let's say we won't have any,  but if you have some you can just input that  

right there, and then we'll also have some at the  ending, at the end of the month, "Ending Customers."  

And, that would simply be a matter of  just taking the beginning customers,  

adding the new customers, and there's our  ending customers. And to carry this over, so  

the key here, and this is going to apply  to a lot of scenarios, like this could be...  

I won't get ahead of myself. What we're going to  want to do is carry this ending value for "Ending  

Customers" over to the beginning value for the next  month. And so that way it will build upon itself,  

see, so now I'm just going to drag this along  here, and drag that along here, and there we go.  

So, now we have our customer base, ending customers  for the month, constantly growing being fed by  

the new customers that we're acquiring each  month. Isn't that cool? I think so but, I'm also  

a little stranger than the average person, so wow  that's pretty great. So, now what we can do  

is we can say that we not only are acquiring  new customers that are paying us each month,  

but maybe some percentage of the customers that  we have acquired previously are going to pay us,  

come back and you know purchase some new shoes  or whatever you're selling. That's  

pretty sophisticated stuff, so let's start to model  out what that looks like, so we'll say that revenue  

we'll say that revenue, we're gonna split  this into two, so revenue from new customers,

and then revenue from

from existing customers. Oh my gosh I can't  type tonight, that's what I get for doing this so late.  

Alright, so now we can take our new customers,  we'll say that we have new customers here,  

and they buy our 19.99 product, and  that gives us our new customer revenue.  

Let's turn that to dollars  just so it's a currency amount.  

And then we just do the same for, actually for the  existing customers we're going to want to say that,  

let's make this, let's drag this down a  little bit. I should have planned this out a  

little better, but this is what we get for doing  things on the fly here. So, we're going to have  

some percentage of customers returning  customers that come back each month, for instance.

And this will be the percentage, and let's say it's i don't know 15%.

And then we're just gonna drag that out, and  then now we can say that return customers

is going to be, we're going to, what we're going to  want to do here is if they're returning customers,  

we have to grab them from the beginning of the  month, right, because you can't have somebody  

returning if you've acquired them already that  month, so you know because that's...  

To illustrate a little more clearly, I guess  I should be on like one of these months  

so, if we have 1600 customers at the  beginning of the month and we're adding  

1600 more, we don't want to add them, we don't  want to include them in the number of customers  

that return, because we're already accounting  for the people that we're adding, so we want  

to take it from the beginning of the month if  that makes things more clear, so we'll take the  

customers at the beginning, multiply it  by our percentage of people coming back,

and there we go. Now we have a nice little model  there, and we'll just simply take our returning  

customers, multiply it by the price, and we now  have our revenue from return customers. Pretty neat.  

So, we've already gotten a pretty key concept here  is really just understanding the carryover of  

beginning customers and ending customers, because  this will help you solve a lot of problems.  

For instance, if we wanted to go back to our lead  generation here, let's say we had like a newsletter  

where you're constantly sending helpful  information out to people that are part  

of it, and you're getting new customers, or getting  new subscribers to your newsletter every month  

and maybe something you pitch to them on a  monthly basis, a percentage will convert, or they  

won't, right. So, a newsletter as a lead source would  be great, but you would want to model it out more  

similar to how we've done it here where there's a  beginning number of subscribers you're adding some,  

and then there's an ending number of subscribers.  And you can also, I mean, you can do that with a  

lot of different scenarios, so I'm not going to  ramble on about that. The only thing that's really  

missing from this is in reality we are not going  to have as clean of a model as this is, because  

it would be nice to have a number of customers  that is constantly growing over time, and never  

falls, but unfortunately, that's just not how  reality works. So, we're going to add some churn  

here. So, this is another core concept is that  some people are just never going to come back,  

and we're going to have to live with that, no  matter how good our service is, or our product, so  

we're going to add our churn percentage now. We'll  say it's 5% or something like that, which is very  

good if you're in a software as a service business  like we are. And then we will say that our churn  

is simply just, take that five percent, multiply  it by customers at the beginning of the month,  

and there we go. And again we're going to want  to take it from the beginning of the month  

because the people, we don't want  to include these new people again.  

They can't churn if we're getting them this  month, they can only turn in the following months.  

So, we want to make sure to take the  churn percentage from this beginning value here.  

Then all we have to do is make sure we  subtract the people that are churning here  

from our ending value so that we're not  forgetting them in our final calculation.  

We don't want to do that. So, let's try that  again and then subtract the churn field, and  

cascade that down the forecast, and we can see that  our number of customers at the end here kind of  

decreased marginally, which is expected to be  the case, and if we just select one of these fields  

we can see that we are now taking the number  of customers at the beginning of the month,  

adding new customers, and then subtracting  the customers that never come back, because  

they ate some bad chili or whatever. But, hopefully  this provides a solid foundation for you to start  

to understand how to go about modeling scenarios  in excel so that they represent, at least  

to some degree, the real world. So if we wanted to  make this even more complicated, or more robust  

let's say and add a different product we could  simply just copy the product that we have here,

we'll paste it down below, get rid of  the logo, make this product number two,

and then we're converting 80 percent to product  one, so we'll say twenty percent product two  

and there we go. I'll need  to update some of these numbers  

because they are not referencing correctly,  but the idea is quite similar. So, all we  

need to do is just repeat this process for a  similar type product to make a multi-tiered  

product model, which is quite nice. If we wanted to  get into modeling out some customer acquisition  

we can get more detailed about how to think about  what this process looks like. So, for google ads  

what are the steps that these leads take before  they become leads even? So, they need to see an ad  

they might need to click on it, they might need to  sign up for a newsletter, or a service of some kind.  

What would that process look like? And maybe some  good homework for you would be to, instead of  

the simple revenue model we have now, think about  these new customers, what if this wasn't a simple  

price that they paid once? What if this was  a yearly annual amount that they paid as a  

sort of subscription? How would we keep track of  the 1600 one year from now so that they can pay  

the annual subscription fee when the time comes,  when the subscription is due? So, I hope that this  

was helpful and provided at least some background  on how to go about doing this process. If you want  

any other examples of models or scenarios that  you are encountering and would like some help with,  

please let me know, I'm happy to do  some more videos on this topic if  

it is of interest. iIf you  found it helpful please like  

the video and subscribe to our channel for  more helpful content like this. Thank You!