Speaker 1: Welcome to another tutorial video. This time around we're going to be looking at how you project revenue for a subscription business otherwise known as a software as a service or SaaS business. Now normally in these tutorial videos I like to address questions that come in and give brief answers to them. This time around though we're not going to answer a question, instead I'm going to be sharing a part of an upcoming case study that I'm working on based on Netflix. The idea in the case study is to analyze the company and think about how it could raise funding via debt, equity, or a convertible bond and then make a recommendation on which one is best. We're going to look at a small part of that case study in this lesson. What I'm going to be focused on here is first off the key drivers of a subscription revenue business. Then we're going to go over where to find the required information when you have to set up a revenue model like this. And then in part three I'll show you how to put everything together, the information and those key drivers, add in some scenarios, and then build up everything in Excel and get to the company's revenue in one of their business segments. So let's start with part one and look at the key drivers of a subscription revenue model. There are really three key drivers here. You could always make it more complicated, but when you get right down to it, it comes down to three main factors in our opinion. First off, you have the existing subscribers and their renewal rate. Now for a subscription business, most of the revenue is going to be coming from those existing subscribers. No matter how big the company is or how quickly it's growing, unless it's growing by a huge percentage each year, doubling subscribers or something like that, the majority of the revenue is going to come from here. So in our Excel model, for example, we have around 3.4 billion of streaming revenue as of the end of the most recent fiscal year as of the time of this case study. We have about 39 million subscribers. The vast majority of those subscribers are going to stay subscribed and so they're going to keep generating revenue for us because they're going to keep paying their monthly fees. The second key driver is the number of new subscribers and their renewal rates. So a certain number of new people will sign up each year and then they may renew or they may cancel their subscription and you have to think about that as well. And then key driver number three is the monthly fees and the pricing increases each year. So Netflix attract a lot of attention when it raises prices from $7.99 a month to $9.99 a month and they can keep raising prices to some extent, but past a certain point, people are going to start leaving. They may not go for it, they may sign up for competing services, there are a lot of alternatives out there. So you have to think about the pricing increases and how that is going to play into the subscription revenue as well. A little bit more on the key drivers before we move into the information gathering part. One thing to keep in mind here is that the renewal rates for existing and new subscribers are going to be different in a lot of cases. And you see that in our model because here, if you look at how we've set this up, we have one area for the existing subscriber renewal rates and then one area for the new subscriber renewal rates. And the existing subscriber rates are higher. We have around 94% falling to 93% in the base case for existing subscribers and then 88% falling to 87.5% for new subscribers. This simply reflects the fact that when someone is new to the service, he or she is more likely to cancel in most cases. Whereas if the person has been around for a while, say two years, three years, four years, they're probably going to stick around or at least the likelihood of them sticking around is higher. So that's why the renewal rates tend to be higher for the existing subscribers that have been there for at least a year, two years, three years, or more than that. Now after the first year, of course, that new subscriber rate will start to approach or equal the existing subscriber rate. And in our model, we're going to assume that they're basically the same after that first year. With the subscriber additions, you generally want to project these as a percentage of the total existing subscribers because you want to avoid nonsensical growth rates. What I mean by that is if the company, as is the case here, has 39 million subscribers, you don't want to just blindly project some growth rate or some lump sum number based on that. You want to look at historically what percentage of new subscribers they've added as a percent of the subscribers at the end of the year or the beginning of the year or whatever it is. And then you want to use those numbers or use those numbers to inform your estimates going forward. So you generally want to look at this as a percent of the total number of subscribers at the beginning or end of the year or average during the year or something in that vein. And then finally here, you also want to look at different outcomes. You want to look at what happens when there's a higher growth rate, when there's a higher renewal rate, when there's higher fee growth, and then when there's a lower growth rate or a lower renewal rate and lower fee growth as well. So that's a little bit about some of the key drivers here. Let's move on to the next part, which is gathering the information. Now Netflix famously does not disclose its churn rate or its renewal rate. The renewal rate and the churn rate are sort of the opposite of one another. Different people will define them differently. But at a basic level, you're looking at what percentage of customers cancel or stay subscribed year after year. If you're wondering how they can possibly have a business that's completely dependent on this metric and then not disclose it, you're not alone. They actually got in trouble once and actually went through a whole lawsuit because they didn't disclose this figure. What they do disclose are the following. For each of their segments, they do give the net additions, so the number of new members that join minus the old members that left. They tell you the members at the end of each period, and then they tell you the revenue for each segment. They also give you the average monthly revenue per member, but we can calculate that ourselves. So that's all they're really giving us here. As a result, we're going to have to do some of our own detective work and homework to get to these cancellation rates or renewal rates. If you think about it, the churn rate, the cancellation rate, can't really be that high because the net additions here as a percent of base subscribers were around 17% to 25% historically. Let's say, for example, that we made a guess and we said that maybe the company's losing 30% of its existing subscribers each year. If you think about what that means, though, let's say, for example, that in the first year here, they lost 30%. In the beginning or over the course of the second year, they had 17.9 million subscribers sticking around. They have net additions of 6.3. We'd need to get around 15.5 million brand new subscribers just to get the net additions here of 6.3 million. Now, it's not completely out of the realm of possibility, but if you think about the numbers here, that is a rate of over 50%. It's between 50% and 60%. So we'd be saying that they have to get between 50 and 60% of their current subscriber base as new subscribers each year. Now, while it's possible, it's quite unlikely, especially in a mature and saturated market like the US. So we don't think the churn rate can possibly be that high. If you look at industry sources for more on this, you can actually find a report here from this company, Parks Associates, and they peg the Netflix cancellation rate is actually quite low, around 9% right here versus around 50% for Hulu Plus and around 25 or 30% for Amazon Prime Instant Video. So Netflix has the lowest cancellation rate according to them. Now, of course, we don't know if this is really accurate or not, but it's the best number we have now. And it's certainly lower than our initial guess of 30%, which we know is probably not going to be plausible in this case. As a result of this, in our baseline scenario, we're going to start at a 94% renewal rate for the existing subscribers and then an 88% rate for the new one. And if you think about the math here, 94% and 81%, these are the renewal rates. So the cancellation rates are 6% or 12%. We know that this company, Parks Associates, is saying the cancellation rate is around 9%, which is right in the middle of these numbers. So that's a bit about how we came up with some rough estimates for these. And then in the other cases, we are basically 2% higher in the upside case, 2% lower in the downside case, and then 2% lower than that in the extreme downside case. So we go from 94% to 96% and then down to 92% and 90%. And then we decrease this over time because as the subscriber base grows, generally the cancellation rate is going to go up and the renewal rate is going to go down by at least a little bit simply because there are so many more members now. For the subscriber additions here, if you're wondering how we got to these numbers, 19%, 21%, 18%, 16%. For the most part, we're trying to stay above the historical numbers for net additions as a percent of base subscribers. And the reason is that, first off, these are net numbers. So they include not only new subscribers, but also all the ones that canceled and left. So we generally want our numbers here, at least in the base and upside cases, to be a bit higher than that. And then finally, for the monthly fees here, historically they've increased their fees by anywhere from 1% up to 4%. So we sort of take those two extremes and use them in our estimates. In the upside case, we have fees going up by 4% down to 2.5%. And then in the extreme downside case, the fees are going up by 1% down to 0.5%. So in that case, essentially we just don't gain many members and we also have very little pricing power. Whereas in the upside case, we gain a lot more members and we have significantly more pricing power so we can increase our prices by something higher than the rate of inflation each year. So let's put it all together in Excel now and go to part three of this process. First let's set up the renewal rate schedule for new versus existing customers. And we have this right here, subscriber renewal rates. For the 2014 customers, these are the ones that are around right now. So we're just going to use the existing subscriber renewal rate for all these, 94% going to 93.5% and then 93% right here. And then for the one right below this, for the 2015 customers, at first these are going to be new, but then it switches and then these are now the existing customers once they've been around for at least a year. And then that same pattern continues for the rest of these. We start with the new subscriber renewal rate and then we turn them into existing subscribers after some time passes. Step two, we need to multiply by the existing subscribers at the end of the most recent fiscal year by that renewal rate each year. We're doing this first because this part is probably the simplest part of the entire model. We just take our existing subscribers and then we multiply it by the rate right here for the 2014 year end customers and copy this across. So we have that. And then in step three, we have to factor in the new additions each year as a percent of the base subscribers. So we are going to look at the additions in 2015 first and we're going to take the number of subscribers at the end of 2014 and then we're going to multiply by the subscriber additions as a percent of the base subscribers right there. So we have that. And to get this fully working, we can actually copy across this formula. Each 65, we want to anchor the 65 part so that doesn't shift around. We also want to anchor the 32 part in G32 so that doesn't go down as we move across. So we have that. Now in step four, we need to apply either the new or the existing renewal rate each year. Now, this shouldn't be too difficult because we already have our schedule here set up. So we can take the 2015 subscribers, the new ones that we got this year, and then in 2016, we're going to multiply by our selected subscriber renewal rate, 88% because these are new subscribers. The 2015 subscribers are still new in 2016, it's their first year. But then after that, as we move across, now they're existing subscribers. So they get that higher rate of 93.5%. And then we can do the same thing down here for the 2016 subscribers. And then do the same thing for the 2017 subscribers. Copy that across. And then the same thing for the 2018 subscribers down here. You can see how this sort of looks like a waterfall, which is why some people call this a waterfall schedule for revenue for a subscription company like this. Now at this stage, we need to sum the total subscribers and take the yearly average. We actually have already summed the total subscribers. So really all we need to do here is just take the average. We need to take the average because when we calculate the revenue, we want to base it on the monthly fees over the course of the year times the average number of subscribers over the course of the year. Because if they added 7.4 million people, they didn't add all those people at the very start of the year. They added them over time, over January, February, March, through June, through September, through December. So let's do this and take the average between these ending numbers and then copy this across. So we have that. And then in step six, we're going to grow the monthly fees and then multiply to get the total revenue here. So let's take our monthly fees and then multiply by one plus our selected growth rate down here. We have that copied across. And then for the total revenue, we'll take our average annual subscribers, multiply by those monthly fees. And then we also have to remember to multiply by the months in the year because these are monthly figures. And we can copy that across. Also copy across the growth rate. And so we have our revenue model set up. And if you want to see what it looks like in the other scenarios, we can actually go in and change it now. Upside case is a lot higher. The downside case is significantly lower. And the extreme downside case still has growth, but it's even lower, a CAGR of only 6%, which is very, very low for a higher growth company like Netflix. So that's a bit about how the numbers differ in the different cases. Now at this point, you might be wondering what you could do next at this stage in a model. Well, first off, you'd have to go in and check and refine your numbers. We just entered some rough numbers here, but you would go back and look at other sources, other industry research, see if your scenarios make sense. See if the capitalized annual growth rates make sense as well. You'd also want to look at equity research, see what consensus estimates of the company say, and see how your numbers stack up to those. And then you'd have to go and finish the rest of the model. So you'd factor in the expenses for the segment, working capital, capital expenditures. You'd fill in the balance sheet, the cash flow statement, and everything else like that to build a full model. So let's do a quick recap and summary now. The key drivers of a subscription revenue business are the existing subscribers and the renewal rate there, the new subscribers each year and their renewal rate, and then the monthly fees and how those change over time. To find the required information, you can look at the company's filings and Netflix gives you some, but not all the information you need. You can also look at industry research to find the rest as we did here. And then to put it together in Excel, you want to start with the number of subscribers and specifically you want to start with the renewal rates and your forecast and projections for those. Once you've figured out how the existing subscriber count changes and then the new subscriber count each year and how that changes over time, then you can get to the average annual subscribers. You can determine how much the monthly fees increase by each year by taking the monthly fee growth rate down here and increasing those fees each time. And then you can multiply those fees by the average annual subscribers and the number of months in the year to get to your total revenue at the top. So that's it for this tutorial. I hope it answers some questions about how to project revenue for a subscription software or services company like we did here and gives you some ideas as you approach your own case studies and modeling tests where you have to do this. Thank you.