How to Model Churn

[Scrap #1] In this tutorial will show how to model churn in a financial model and apply it to a cash flow forecast. As an example, it will use a cohort approach, where we first make simple assumptions as to the users of a service, we will measure the churn and then see how the churn changes over time and how it influences the lifetime value of a user. The model divide churn into two parts 1st-month churn where we will expect a higher number of users to drop off (due to end of free trial or product dissatisfaction) and then the second part of churn where with a lower number where you see a higher level of customer loyalty or retention. This will then trickle into standard cash flows projections with revenues and direct costs driven by users. The model will be able to show how churn affects your lifetime value of the user and also how higher churn results overall lower user profitability. You will be able to run sensitivity analysis. The model will use a bottom up approach, where you assume an x number of adds - see effect of churn on total numbers. ten-year model.

[Scrap #2] Before we start calculating the revenues, let's start with the customer acquisition, churn and retention model. We will assume that we are dealing with subscription model such as saas (software as a service) or simply a service that provides subscribers with service (e.g. Spotify). We will divide the customers into two cohorts: free and premium (paid). We will make some assumptions around the customer behaviour - i.e. their propensity (likelihood) that they will upgrade to a higher service or that they will add some add ons (for instance Amazon prime). For each subscriber we will model different churn and retention rates. We will assume that the initial churn will be greater for premium customers (as they will drop off when their free trial will change). However, the retention may be greater for premium as the loyalty may be greater plus for example lack of ads and higher quality create more stickiness. The model will be provided with a calultore to see how churn affects your profitability and user lifetime value. We will also look at how churn affects your customer acquisition model. For more info on how to analyse churn in Excel, check out my post.

[Scrap #3] Customer life cycle and lifetime value will be greatly influenced by your churn assumption - you will see it in the model and in the calculator attached. Although the customer acquisition cost will remain fix, you can see how the lower lifetime revenue from subscription influences profitability or drives it even below. The model will also have an ability to increase your cost of acquisition costs. This is something that you should be particularly concerned when running sensitivity analysis. As you grow the company and the market becomes more competitive or saturated because of needing to reach and convert customers less likely to be familiar with your product. This is where it is important to also model upsale metric to see if you can offset, amortise or absorb the higher costs with lower acquisition costs and less revenue but with some special deals that will make that channel more profitable. This is where the cohort analysis will play an important role and keeping track of those metrics should become core of your cost of acquisition model. Then lifetime in the calculator is based on the formula of taking customer and then taking two level churn applying revenue and cost.

[Scrap #4] Revenue model is mostly run of the subscription fees generated by premium customers. The model will assume that free subscribers as well as paid subscribers will be generated by add ons. Some of the free subscribers will also upgrade to the premium plan. You will see there is a formula for a discounted pricing which is applied to percentage of free customers as well as premium customers that come from promotion. Subsctiption business model is used in saas, streaming services or for instance hosting service providers (like web sites). This is just an example but hopefully this model will give you enough idea how to play it. In terms of calculating the revenues, the once the customer acquisition model is done you will see that the Excel model clearly sepeated unit pricing for each cohort of users (users split into new and discounted) and then multiplies those users by a given unit price. Model then follows the direct revenues with direct costs i.e. costs that are associated with customer acquisition and directly serving the customers. Other costs are calculated as percentage of revenues and those can be costs such as % revenue share. You also have direct per user cost.

[Scrap #5] The model shows how to calculate and predict churn. I will focus on just one cohort but you will see that the spreadsheet takes two cohorts and models churn and retention separately and then merges them together. Churn happens when a user leave a service, for instance they unsubscribe or cancel their free period. Retention is the 'other side' of churn and the model shows it in more detail in churn example. There are a couple of ways to calculate and model churn but here we will assume that the highest churn happens in the first month and then it is followed by a steady rate afterwards. The spreadsheet takes the customers acquired in their first months and then it turns it into a negative number multiplied by a percentage rate. So for example you add 100 customers, 20 are churned e.g. unsubscribed so you are left with 80. Then this 80 is added to the next month and from to this number we apply a steady rate, much smaller, 5%. Then you churned 4 users, or in other words you retained 74 after [3 months]. Then you apply monthly churn again. The model assumes increased customer loyalty with time. 

[Scrap #6] After the direct revneus and costs calculation you can calculate direct margin or customer contribution. Then you need to add fixed costs and capex and see how you can use the numbers to predict the churn, calculate future costs and revenues and forecast the cash flow. Here the model uses twelve-month actuals and then follow them with 60 months of forecast. That way you can see how the revenues trend and whether your future prediction and scenarios assumptions don't depart too much from the reality. The fixed costs breaks the model into two parts: salaries and operating costs such as office space or other general expenses. In our example, you will notice that the model makes assumptions in regards to the number of employees and then multiplies them by an average salary to calculate the total costs. You could also split those cost per functions. Office costs are driven as the function of employees. You can also add some fixed expenses such as legal or various licence fees which would not be directly linked to the headcount. That along with direct costs and revenues gives you profit. Finally, capex assumes development spent such as software development our example assumes steady.

[Scrap #7] Finally, make sure you complement the model with graphs for ease of its visualisation. We first start with putting everything into an easy to grasp yearly summary. In our case we used SUMIFs Excel to aggregate numbers yearly but you could also modify it to for example show a quarterly view. Having aggregated numbers allow you to get a bigger picture view as well as it will allow you to easily re-use the numbers for creating graphs. Since we are dealing with subscriber model, make sure to clearly indicate the main metrics above of your summary, so you clearly show the number of customers, new additions, churn and so on. You may omit some details knowing that you can always add them if required. Our graphs will focs on the customer acquisition numbers so we need to clearly show how the numbers grow and churn. Here you can dig into more detail to show the free and premium cohorts and related churn. We used the [bar] chart in our example but you can think of using others. Finally, we want to clearly show revenue model summary, so show revenues from paid customers with breakdown of paid, discounts, upgrades and addons.

[Scrap #8] The model also contains sepereate sheet that takes or assumptions from the model spreadsheet and presents and visualise how they affect customer metrics. First, we start with customer acquisition unit analysis. We will use 100 customers for simplicity and rounding. The lifetime value calculator starts with the number and then deducts from it the initial churn we mentioned. Then based on the ongoing churn (second level) it calculates the lifecycle or duration of an average user. This gives you direct revenue. We then need to deduct customer acquisition and other direct costs. The customer acquisition cost is a of off investment and then the ongoing costs are calulcated on a percentage basis which we will calculate plus add any fixed revenue (i.e. subscriber rent) to calculate total. Here you can see how churn affected lifetime revenue and therefore lower or increase top line and therefore the profitability. The customer revenue model calculator splits the revenue based on the assumption used. It uses an average assumptions but you will see that the average total revenue links to the revenue from the customer lifecycle model. We used a [bar chart] to better illustrate the split. Add customer lifetime model cash flow chart.

[Scrap #9] Now when we have the model completed and summary written, you can see how changing the cash flow forecast alters with our changes to the assumptions. For simplicity, I will change the assumptions on the same sheet as the cash flow model spreadsheet, but have a look at my [tutorial on cash flows] to see how you can use a separate sheet to set up an assumptions page and have an ability to quickly change the assumptions and monitor how it affects the cash flows. Download the model sheet and see how changing one of the inputs affects the profitability. It's best to start with one assumptions and note how it affects the cash flow, then restore the assumption back to its original and then change another input such as customer acquisition costs. You will see that I have added some basic assumptions around the financial metrics return. You can read more about them in the cash flow model tutorial but for the purpose of this exercise, the lower overall cashflow or net present value, comparatively to your base, indicates worse performance. After you have run it a few times you should see the most sensitive inputs of your model.

[Scrap #10] This model has introduced you to the basics of customer subscription model. We started with looking at customer acquisition calculations and simple way of modelling new customers. Here we introduced a split between free and premium customers. We then looked at modelling and predicting churn in our model. Our example used two-level churn with the first level higher driven of initial customers and then a steady monthly churn. We then used a lower but steady churn for the remaining customers assuming that they retention or loyalty will be higher and will increase with time. We then looked at the modelling direct customer revenue. Here we used some assumptions around discounts and upgrades [as well as add-ons]. We took the number of paid customers and then multiply them by a monthly fee in a given fee to calculate the revenue. On the negative side we looked at the costs. Here the biggest direct driver was the customer acquisition calculated of added customers and monthly percentage and fixed per customer cost. For better understanding, we used customer calculators. With fixed costs and capex we got to cash flow. We then summarised and visualised the results and used sensitivity analysis to compare scenarios.