BUY WITH CONFIDENCE
DESCRIPTION
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.
Got a question about the product? Email us at support@flevy.com or ask the author directly by using the "Ask the Author a Question" form. If you cannot view the preview above this document description, go here to view the large preview instead.
Source: Best Practices in Customer Loyalty Excel: Customer Spend and Retention Pattern: Revenue Modeling Excel (XLSX) Spreadsheet, Jason Varner | SmartHelping
OVERVIEW
ABOUT THE AUTHOR
Additional documents from author: 136
I graduated in 2011 with a Bachelors degree in Accounting. From there, I worked at a few small businesses doing financial reporting and some bookkeeping. After a few years of that, I started doing freelance financial consulting work on Elance and Upwork.
After over 400 jobs completed with a 100% success rate, I now run my own modeling/consulting practice and continue to build new financial models every few weeks.
[read more]
Ask the Author a Question
This document is available as part of the following discounted bundle(s):
Save %!
Financial Models for Service-based Businesses
This bundle contains 13 total documents. See the additional documents to the right.
Customer Loyalty Customer Experience Net Promoter Score Customer Satisfaction Employee Management Relationship Marketing Digital Transformation Business Transformation Cyber Security Mobile Strategy Ideation Service Design Creativity Innovation Management
![]() |
Download our FREE Digital Transformation Templates
Download our free compilation of 50+ Digital Transformation slides and templates. DX concepts covered include Digital Leadership, Digital Maturity, Digital Value Chain, Customer Experience, Customer Journey, RPA, etc. |