How to Create a Subscription Model with Churn Calculation

This step-by-step tutorial will take you through an example of using Microsoft Excel to create a Subscription Model with Churn Calculation. First, it shows how to forecast customer acquisition and growth. Then, it introduces estimating customer churn and lifetime value and their impact on cash flow and equity returns.

Download my Subscription Model with Churn Calculation Template to follow the spreadsheet and examples used.

You can also watch the video version of the tutorial at the end of this post.

Step 1: Start with a Customer Acquisition Forecast

The customer numbers will be the fundamental part of your subscription model, so start with a customer acquisition forecast. Open a spreadsheet and show how the users will grow over time. For simplicity, start with simple month-on-month customer growth. Later, you can experiment with more advanced growth curves when the model works.

Break down the monthly customers into the following categories:

• Acquired Customers: new subscribers or users that joined the service in a given period,
• Churned Customers: subscribers or users that have unsubscribed or left the service,
• Upgrades/Downgrades: subscribers or users that have switched between your plans (for instance, when your subscription model assumes more than one payment package).

In my Subscription Model Template, I started with a hundred customers and then increased the number by a fixed monthly percentage. I also added a growth period assumption, so the customers’ increase flattens after several months.

If your subscription model uses different payment plans, consider splitting your customer numbers to reflect it. For example, free customers will likely behave differently than paid or premium users. Hence, it’s worth looking at them as two separate groups, or cohorts, to track their distinct behaviour.

Step 2: Calculate and Predict Customer Churn

The churn rate measures the number of customers or users who left your service. To calculate the churn rate, add up the number of users that left or unsubscribed in a given period. Then, divide that figure by the total number of users at the beginning of that period.

My Subscription Model Template tracks customer behaviour using two metrics:

• Sign-Up Retention Rate: this is a measure that reflects the proportion of customers that stayed or renewed their service after their sign-up or a trial period,
• Monthly Churn Rate: calculated as the number of customers who left or unsubscribed from the service compared to the overall customer number.

Although both metrics correspond to customer behaviour, you should track the pre and post-sign-up behaviour separately. For instance, you will often see a significant drop in customer numbers at the end of a free trial. However, the users who renewed and paid for the service should consequently have a lower and more stable churn rate.

To predict the number of retained customers, multiply the acquired customers’ numbers by the sign-up retention rate. To forecast the monthly customer churn, take the churn rate assumption and multiply it by the number of users at the start of the month.

Step 3: Forecast Customer Subscription Revenues

Use your customer acquisition model to calculate subscription revenues. When forecasting customer revenues, calculate sign-up and subscription revenues separately. The split will allow you to analyse one-off and recurring income and explain any anomalies in your cash flow projection.

To calculate the subscription revenue, multiply the sign-up fee by the number of acquired customers in that period. Then, for recurring payments, multiply the number of customers from a period by the applicable subscription fee.

You can also add a Price Increase Index to account for changes in average revenue per user over time.

My Subscription Model Template lists the fee assumptions monthly. That way, you can see what input applies to a given period and increase or decrease it over time to adjust for inflation or pricing changes.

Step 4: Add Variable Customer Costs Calculations

Add variable customer costs to your subscription model. The calculations should be linked to the user numbers and reflect the cost of providing them with service. Divide the customer expenses into two sections:

• One-Off Variable Customer Cost: any expenditures related to signing up a user, for example, customer acquisition cost,
• Recurring Variable Customer Expense – payments directly associated with providing service to your customers, for instance, revenue share, hosting or licencing fees.

You can divide the recurring costs into a percentage and fixed per-user spending. The percentage-based expenses will include such categories as a revenue share or royalty payments; you will calculate them by taking a proportion of revenues. The per-user cost calculation will multiply the number of customers by a fixed cost assumption in a given period.

Finally, don’t forget to include a cost increase index to account for inflation.

My Subscription Model Template includes a few examples of estimating variable customer costs. The spreadsheet takes the number of sign-ups and multiplies them by customer acquisition cost assumption to calculate the direct marketing spend. Then, it takes the number of users in a period and multiplies it by a fixed $ monthly payment to estimate other recurring costs.

Learn More: You can read more about analysing customer acquisition costs in my tutorial on How to Create a Marketing Investment Plan.

Step 5: Use a Customer Lifetime Value Calculator

Use your churn and subscription revenue assumptions to calculate a customer lifecycle and customer lifetime value. The customer lifecycle tells you how many months you can expect a user to use your service on average. To calculate the customer lifetime value, multiply that figure by the subscription revenue assumption and see how much an average user will contribute to your cash flow.

Download my Subscription Model Template to see an example of calculating customer lifecycle. I have created a separate calculator to help you analyse a customer’s lifetime value. The calculator takes a user’s lifetime income and then deducts direct costs to calculate their net contribution.

I have added a simple illustration to show how your current retention and churn assumptions affect your users’ lifetime. My example takes a cohort of one hundred new subscribers and displays how the number of customers decreases over time. The graph shows how a high churn will result in a faster drop-down of customers, affecting customers’ lifetime value.

Learn More: To read more about analysing customer churn, visit my tutorial on How to Use Cohort Analysis to Calculate Retention and Churn Rate in Microsoft Excel.

Step 6: Add General and Development Expenditures

Create a subscription cash flow forecast by adding general operating and capital expenditures to your customer revenue model. Start with monthly headcount projections and multiply them by the average staff cost assumption. Then, add any other expenses directly linked to the number of employees. Finally, add other operating expenditures such as rent, general marketing, bills, and other recurring payments.

Include any costs that you would spend on developing your product or software. My Subscription Model Template puts the research and development cost in the capital expenditures line. Still, they may also be operating expenses depending on the applicable accounting rules.

Finally, add salary and cost increase index to account for inflation.

To forecast subscription cash flow, take the customer revenue minus variable cost, subtract staff and operating expenditure, and then deduct any development spend or CAPEX. As a result, you will see a monthly projected inflow or outflow of cash. Add the numbers together on a cumulative basis to find the lowest figure, which will be your forecast cash or equity requirement.

Step 7: Summarise Your Subscription Model

Aggregate the monthly subscription cash flow into an annual view. When creating a summary spreadsheet, use a SUMIFS formula. That way, you can easily tweak the table to show quarterly or monthly numbers.

Add some graphs showing the relationship between subscriber numbers and main financial metrics. For example, show how customer number increases contribute to customer revenues and cash flows. Or the breakdown of subscribers’ revenue between different payment plans.

Finally, add a simple calculation of equity returns to see how your cash flows perform under different assumptions. Our example uses exit estimation based on an EBITDA multiplier and calculates Net Present Values and IRR using Microsoft Excel’s NPV and IRR formulas.

To learn more about exit valuation and equity returns, visit my tutorial: How to Build a Cash Flow Forecast in Microsoft Excel.

Step 8: Stress-Test Your Subscription Model

List the main assumptions next to the subscription model’s summary so you can see how changing one input affects the overall cash flows. Pay attention to cumulative or peak cash flow, equity requirement and overall returns.

Testing the model will allow you to find its weak spots. It will also help you address any performance issues that may occur in the future. For example, you should check how your churn rate changes affect your customer revenues or how a difference in the customer acquisition costs affects the equity returns.

Use the customer lifetime calculator to see how changing a monthly fee or churn assumption affects customer revenues. You can also see how a customer acquisition cost change affects their net contribution. Ensure that your customer revenue model returns healthy margins, as you still need to leave room to cover general operating expenses and CAPEX.

Learn More: To read more about sensitivity analysis and scenario planning, check out my tutorial on How to Build a Cash Flow Forecast in Microsoft Excel.

Summary: How to Build a Subscription Model

Here is the summary of the steps to create a Subscription Model in Microsoft Excel, forecast cash flow, churn and calculate a User Lifetime Value:

1. Start with Customer Acquisition Forecast: Set up a spreadsheet that shows monthly projections of your subscribers’ growth. For example, start with an initial number of sign-ups and then grow them steadily over time.
2. Include Customer Churn Calculations: Split the customer behaviour metrics into a signup retention rate and an occurring monthly churn. The first metric applies to the customers in their initial period of subscription. The latter shows user churn after that period.
3. Forecast Customer Revenues: Split into one-off sign-up fees and recurring subscriptions. You can also apply different pricing to basic and premium subscribers, which drives the incoming cash flow accordingly.
4. Add Direct Cost Projections: Link your cost assumptions with the subscribers’ forecast. For example, to calculate marketing spending, multiply the cost per acquisition assumption by the number of new customers.
5. Use the Customer Lifetime Value: Calculate the contribution of each subscriber to the overall cash flow. Test the impact of churn, revenue and cost assumptions on user profitability.
6. Add General and Development Expenditures: Complete your subscription cash flow forecast with staff costs, office, and other general expenses. Also, include the costs associated with the company setup, development and R&D.
7. Summarise the Cash Flows: Create an annual view of your financial model so it’s easy to sanity check it and follow the link between the user numbers and the financial output.
8. Stress Test the Subscription Model: List the main assumptions next to the summary to see each input’s impact easily. For example, check how changes in pricing and costs affect the overall cash flow and equity returns.

Download: Subscription Model Template

Click on the button below to download my Subscription Model Template. The spreadsheet allows you to forecast customer growth, calculate subscription revenues, and estimate user churn. The file contains the following Excel sheets:

• Subscription Cash Flow Model – spreadsheet calculates user growth, churn and subscription revenues. It also estimates direct expenditures such as customer acquisition costs or revenue share. It then adds staff costs, general OPEX and capital spend to forecast total subscription cash flow.
• CLV Calculator – this sheet calculates customer lifetime value using subscribers’ revenue, churn, and cost inputs. It also illustrates how customer lifecycle changes depending on the churn and retention assumptions.
• Subscription Model Summary – aggregates the subscription model and cash flow numbers into annual views. It also contains equity IRR and NPV calculations. It lists the model’s main assumptions to the right of the summary table, allowing for a convenient stress test.
• Balance Sheet – this spreadsheet lists the main forecast assets and liabilities based on the numbers from the subscription cash flow. It estimates asset value based on the model’s depreciation inputs. It uses simple working capital assumptions to calculate the receivables and payables.

The file is in Excel format, but you can convert it to Google Sheets using the instructions here [external link].

Video: How to Use the Subscription Model Template

For more details and a step-by-step explanation of using and creating the Subscription Model Template, watch my video tutorial below:

 

Get in Touch

Hi, my name is Jacek, and I love spreadsheets! I hope you’ve enjoyed reading this tutorial as much as I did writing it. If you have any questions about creating a Subscription Model in particular or financial analysis in general, don’t hesitate to get in touch.

Explore my other tutorials to learn more about financial modelling or data analysis. If you need further support, find out about my One-to-One Training and Financial Modelling Services.

Please note that this tutorial is provided for general informational and educational purposes only and is not a substitute for professional advice.

Learn More

Calculating Retention and Churn Rates with Cohort Analysis – This tutorial will guide you through taking a simple data file, converting it into a meaningful cohort analysis, and calculating users’ churn and retention rates.

Marketing Investment Plan – Learn more about how long-term user value and churn affect overall returns on investment.

How to Build a Cash Flow Forecast in Microsoft Excel – Explore using a financial model to forecast cash flow revenues, conduct sensitivity analysis scenarios and present the model to potential investors.

Merger and Acquisition Model – A tutorial on using a financial model to consolidate two companies to increase revenues and an overall market reach.

Monthly Budget Forecast – Learn more about building a company’s projections of costs and revenues.

Telecom Investment Model – Explore another example of estimating the customer lifetime value and its impact on a cash flow forecast.

Financial Model with Debt Funding – A more advanced cash-flow modelling tutorial on financing a business plan using a mix of debt and equity.

How to Create a Project Finance Model – Learn to consolidate a series of project cash flows into one forecast.

Real Estate Investment Model – Explore building a cash flow forecast in Microsoft Excel to calculate revenue growth and debt payments.

Capital Investment Plan – Another example of using market reach calculations to forecast customer growth, revenues and costs.

Starting a Consulting Business from Scratch – Tips for starting a new business with a spreadsheet to help you analyse customer revenues and expenses.