In this tutorial, based on a case study from Netflix, you'll learn how to forecast subscription revenue for a software-as-a-service (SaaS) or other subscription-based business.

http://breakingintowallstreet.com/

/"Financial Modeling Training and Career Resources for Aspiring Investment Bankers/"

Table of contents:

1:16 Part 1: Key factors for a subscription revenue business

5:09 Part 2: Where to find the information you need

10:08 Part 3: How to put it together in Excel Add scenarios

15:32 Review and summary

Part 1: Key factors for a subscription revenue business

Key revenue drivers for subscription-based businesses include:

1) Existing Subscribers and Renewal Rate – MOST of your revenue depends on your existing subscriber base unless your business is growing at a rapid pace.

2) New subscribers and their renewal rates – How many new subscribers does the company add each year as a percentage of existing subscribers?

3) Monthly fees and price increases – How much will these increase over time? How much *can* the company increase fees before they drive away members?

Renewal rates often differ for existing and new subscribers, as new customers tend to cancel more quickly; however, if someone has been with the company for a few years, they are more likely to keep their subscription.

You should also look at different scenarios: what happens with higher growth, higher renewal rates and fee growth and what happens with lower growth, lower renewal rates and fee growth?

Part 2: Where to find the information you need

Some companies disclose these numbers in their filings, but Netflix does not. The company only tells us the net additions, revenues and average monthly fees for each business segment.

However, if you do the math yourself, you'll see that the churn rate or cancellation rate can't possibly be that high, given that net additions have historically been between 17 and 25 percent of subscribers.

With a 30% churn rate, the company would have to replenish its subscriber base by 50% with new subscribers every year – which is unlikely!

Additionally, industry sources such as Parks Associates note that the company's cancellation rate is relatively low at 9%.

For this reason, we chose a renewal rate of 94% for existing subscribers and 88% for new subscribers (the 91% rate in the middle corresponds to the 9% cancellation rate).

In the upside case we go 2% higher, in the downside case 2% lower and in the extreme downside case 2% lower.

Subscriber additions as a percentage of base subscribers will be higher than historical numbers but will decline over time. Monthly fee increases will be in between average historical increases.

Part 3: How to put it together in Excel Add Scenarios

Step 1: Set up the renewal tariff plan for new and existing

Step 2: Multiply the number of existing subscribers each year by the renewal rate

Step 3: Consider annual new additions as a percentage of base subscribers

Step 4: Apply the new or existing renewal price each year

Step 5: Add the total number of subscribers and calculate the annual average

Step 6: Increase the monthly fees and multiply them to determine the total revenue

What's next?

Once you have the basic timeline in place, you can review and refine your numbers to make sure all scenarios and capitalized annual growth rates (CAGR) make sense.

You can also consult other sources, such as stock analysis, and compare your views with consensus estimates for the company.

And then you can build the rest of the model by projecting expenses, working capital, capital expenditures, and other items needed to complete the balance sheet projections.

RESOURCES:

https://youtube-breakingintowallstreet-com.s3.amazonaws.com/105-18-Subscription-Revenue-Model.pdf

https://youtube-breakingintowallstreet-com.s3.amazonaws.com/105-18-Subscription-Revenue-Model-Excel.xlsx

https://youtube-breakingintowallstreet-com.s3.amazonaws.com/105-18-NFLX-Annual-Report-Extracts.pdf

https://youtube-breakingintowallstreet-com.s3.amazonaws.com/105-18-Industry-Churn-Rates.pdf

Please take the opportunity to connect with your friends and family and share this video with them if you find it useful.