How to Create a Subscription Model with Churn Calculation

This step-by-step tutorial will take you through an example of using Excel to create a Subscription Model with Churn Calculation. First, it will show you how to forecast customer acquisition and growth. Then, it will introduce you to 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 Excel spreadsheet and examples used.

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 in Excel or Google Sheets and show how the users will grow over time. For simplicity, start with simple month-on-month customer growth. Later, when you have the model working, you can experiment with more advanced growth curves.

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 a set number of months.

customer acquisition model excel example

Step 1. An Example of a Customer Acquisition Model in Excel. Click here to download the full file.

If your subscription model uses different payment plans, consider splitting your customer numbers to reflect it. For example, free customers will most likely behave differently to the 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

Churn rate is a measure of the number of customers or users that 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 the 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 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, take the acquired customers’ numbers and multiply them 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, take the sign-up fee and multiply it by the number of acquired customers in that period. Then for recurring payments, take the number of customers from a period and multiply them by the applicable subscription fee.

customer revenue model example spreadsheet

Step 3. An Example of Customer Revenue Model. Click here to download the full template in Excel.

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 Direct Customer Costs Calculations

Add direct customer cost 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 Direct Customer Cost: any expenditures related to signing up a user, for example, customer acquisition cost,
  • Recurring Direct Customer Expense – payments directly associated with providing service to your customers, for instance, revenue share, hosting or licencing fees.

You can further divide the recurring cost by splitting them into a percentage and fixed per user spend. 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.

My Subscription Model Template includes a few examples of how to estimate direct 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.

customer direct cost estimate spreadsheet

Step 4. A Spreadsheet with a Calculation of Customer Direct Cost. Click here to download the full Excel file.

You can read more on how to analyse customer acquisition costs in my tutorial on How to Start a Consulting Business from Scratch.

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 on average you can expect a user to use your service. To calculate the customer lifetime value, take that figure and multiply it 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 how to calculate customer lifecycle. I have created a separate calculator, which will 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.

customer lifetime value calculator

Step 5a. A Customer Lifetime Value Calculator. Click here to download the Excel spreadsheet.

I have also 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.

customer life cycle graph illustration

Step 5b. A monthly Illustration of the Customer Life Cycle based on a cohort of 100 new users.

To learn more about how to analyse customer churn, visit my tutorial on How to Use Cohort Analysis to Calculate Retention and Churn Rate in Excel.

Step 6: Add General and Development Expenditures

Create a subscription cash flow forecast by adding general operating and capital expenditure 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 belong to the operating expenses depending on the applicable accounting rules.

subscription cash flow forecast spreadsheet

Step 6. An Example of a Subscription Cash Flow Forecast. Click here to download the full Excel template.

Finally, to forecast subscription cash flow, take the customer revenue less direct 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 if you need to show the numbers quarterly or monthly.

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

subscription cash flow forecast summary

Step 7. A Subscription Cash Flow Forecast Summary with a List of Assumptions. Click here to download the full Excel template.

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 then calculates Net Present Values and IRR using 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 Excel.

Step 8: Stress-Test Your Subscription Model

List the main assumptions next to the subscription model summary, so you can see how changing one input affects the overall cash flows. Pay attention to measures such as 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 your overall equity returns.

equity returns calculation excel example

Step 8. An Example of Equity Return Calculation. Click here to download the full subscription model in Excel.

Use the customer lifetime calculator to see how changing monthly fees or churn number affects customer revenues. You can also see how a change in the customer acquisition cost affects their overall 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.

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

Summary: How to Build a Subscription Model in Excel

Here is the summary of the steps to create a Subscription Model in 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 at a steady pace over a period of 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 spend, 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 the overall 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 easily see the impact of each input. For example, check how changes in pricing and costs affect the overall cash flow and equity returns.

Download: Subscription Model Excel Template

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

  • Subscription Cash Flow Model – spreadsheet contains a calculation of 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 used.
  • 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.

 

Get in Touch

challengejp_data_analystHi, my name is Jacek, and I love Excel. I hope you’ve enjoyed reading this tutorial as much as I enjoyed writing it! If you have any questions about the subscription models in particular or financial analysis in general, don’t hesitate to get in touch.

If you would like to book my One-to-One Financial Modelling Crash Course, click here. For more Financial Modelling Tutorials, visit this page. If you are looking for help with financial modelling or cash flow forecasting, see my Financial Modelling and Forecasting Consulting services here.

 

Related Posts:

How to Use Cohort Analysis to Calculate Retention and Churn Rate in Excel – This tutorial will guide you through taking a simple data file and converting it into a meaningful cohort analysis and calculation of users’ churn and retention rates.

How to Build a Cash Flow Forecast in Excel – This step-by-step tutorial will show you how to use a financial model to forecast cash flow revenues. It also contains additional sheets to help you with sensitivity analysis, scenarios and presenting the model to potential investors.

Financial Model with Debt Funding Excel Template – This is a slightly more advanced cash-flow modelling tutorial, which will show you how you can finance a subscription model business using a mix of debt and equity.

How to Create a Project Finance Model in Excel – In this tutorial, you will learn how to consolidate a series of project cash flows into one forecast. It also includes debt and a return on equity investment calculation.

How to Start a Consulting Business from Scratch – This post will give you a few tips on your first steps when starting a new business. You will also be able to download a free budget spreadsheet that will help you analyse and forecast customer revenues and expenses.

Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.

, ,
Menu