Your company would like you to help create a model for calculating Customer Lifetime Value for a
Question:
Your company would like you to help create a model for calculating Customer Lifetime Value for a cohort. Using figure 2.5 as a starting point, help create a model in Excel that will calculate 10-year CLV based on the following: 1. Cost of acquisition is $100,000 2. The margin per customer is constant at $150/year 3. The discount rate is 5% 4. Annual Churn is 10% 5. Annual Activity Cost (ABC) is constant at $50,000 per year for the cohort The model should allow the company to make a change to any of the above variables in a single cell and CLV should automatically re-calculate. 1. Based on the above information, what is the CLV of the cohort? 2. Be prepared to demonstrate your model. One of the managers is very impressed with your model. They have been asked to budget a constant amount that should be spent annually on reducing churn from 10% to 5%. 3. What amount would you advise the manager is maximum that should be spent each year?
Using Microsoft Excel and Access 2016 for Accounting
ISBN: 978-1337109048
5th edition
Authors: Glenn Owen