A common modelling problem faced by SaaS businesses is how to estimate Running Customers in forecast periods when the logic of the model is focussed solely on ARR, separated into the usual segments of New ARR, Churned ARR, Expansion ARR and Running ARR.
The goal is to transform these ARR outputs into Customer outputs, i.e. New Customers in Period, Churned Customers and Running Customers. Note that Expansion ARR has no impact on Customer numbers and is therefore excluded.
Here is a nifty solution!
The first step is to determine how to calculate New Customers in the period from New ARR in each period. This requires an Input for Annual Contract Value (ACV) which can vary in each period along with New ARR.
Now, producing the formula in the snip below will calculate the New Customers in Period.
Now we can move on to calculating the amount of Running Customers which has three separate pieces.
- The amount of Running Customers in the first period of the model. This is only applicable if you have existing ARR flowing into the model in the first period, else you can ignore.
The formula below calculates number of customers at t=0 based on initial ACV if the model starts with existing ARR, and excludes the New ARR in the period as this will be dealt with in the next piece.
- The next piece brings in the New Customers from the beginning of the model but needs a new variable to operate, Inverse ACV. As the name suggests this is calculated using 1/ACV. This now allows the use of the sumproduct function as in the snip below:
This formula calculates all the new customers from the beginning of the model to the current period, based on New ARR and the respective ACV when first purchasing and is added to the step 1.
- The final piece determines the amount of Churned Customers to date in the model. A new Input is required for the formula to operate, Inverse ACV Shifted, which is the Inverse ACV variable created above, but shifted along the Contract Length so that it sits in the appropriate period for Churned ARR.
The logic is that if the Contract Length is 12 months, and after this 12 months the Customer churns, the appropriate ACV for that Customer is not the ACV input for that period, but rather the ACV of the period 12 months ago when that Customer joined.
See formula below:
Once this variable is created, using the same sumproduct function as step 2, the formula below can be created and added to the previous steps:
The formula calculates churned customers by looking at Churned ARR and the respective ACV when first purchasing, which is shifted back by the contract length.
The overall formula should now look like this:
It is up to user discretion whether you want to roundup, round or roundown.
The last remaining element is Churned Customers. This can now be calculated as the difference between Running Customers in current period to New Customer in Period and previous period’s Running Customers as in the snip below:
You now have a fully functioning set of calculations to create Customer outputs from ARR outputs! It should look something like this:
Happy modelling and hope this is useful!
If there are any questions or alternative strategies please post below!