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?

Answer rating: 100% (QA)

To create a Customer Lifetime Value CLV model in Excel based on the provided information follow these steps Step 1 Set up the Excel spreadsheet with t... View the full answer