Customer Spend and Retention Pattern: Revenue Modeling (Excel XLSX)

This is a great bottom-up modeling style to use in order to more accurately forecast expected revenues. When modeling the retention rate of customers as well as the amount they may spend over the time that they are a customer, normally an average fixed amount is applied.

However, you may have a lot of data and research that shows a very specific pattern of spend and retention. If that is the case, this model will make it easy to apply those specific patterns to the amount of new customers added per month.

The first part of this is customer retention, which means the length of time an average customer remains a customer. It usually applies to SaaS or freemium businesses, but it could also apply to direct sales, ecommerce, and general retail.

This model lets the user define the average lifespan dynamically over 60 months. For example, if new customers often fall off in greater numbers within the first few months, but the remaining customers end up staying for a longer period of time, that has to be accounted for on a granular level by month.

You can do it with this template. Example: In month five of the model, 100 new customers are added. 70% remain after month 2, 40% remain after month 3, 35% remain after month 4, 30% remain after month 5. You can see that change in the rate of customer loss is often specific to various industries and can't be accounted for with a regular average loss per month that is split evenly over the average total life of a customer (what is normally done).

The second part of this is the actual spend per customer. The dynamic nature of this is applicable to nearly any business. The template allows the user to define the expected money spent per customer over their expected life.

Do customers spend a lot of money right away? Do they spend more as time goes on? Do they spend a little, then a lot, and then very little?

All of these general patterns can't be accounted for in traditional modeling and need a more granular approach if you know your customer spending patterns.

The model lets the user define both the spending and retention patterns for up to 60 months as well as a dynamic calculation for how much expected revenue there will be based on a defined monthly addition of new customers who are all subject to the defined spend/retention patterns.

If you have a function that defines the spend/retention over time, you can apply that to time ‘n' based on the month number being your input variable for the curve.