calculate the CLV for an infinite timeline given estimates for profit margin, retention rate, and discount rate.

Transcribed Image Text:

A B C 1 Customer Lifetime Value Year 0 D2 10 E F 3 4 C5 G H I J K L M N 2 3 Average Acusition Cost -3 All areas in gray are known or estimated values for a time horizon of 5 years. Promotional, sales, and setup costs of aquiriring a new customer 4 5 Average Customer Revenue 30 6 Average Customer Costs 20 NW 37 25 7 Average Customer Profit 10 12 432 45 53 60 Expecte average per customer revenue (CRn) 30 35 40 Expecte average per customer cost (Cn) 15 18 20 Calculated expected per customer profit margin (CRn-Cn) 8 9 Customer Retention Rate 1 0.6 0.65 0.7 10 Cumulative Retention Rate 1 0.6 0.39 0.273 0.75 0.20475 Expected yearly customer retention rate (Rn) Calculated iteration of expected retention rates (Rn*Rn+1..._) 11 231567% Likely Customer Profit 10 7.2 5.85 4.914 4.095 Calculated expected profit (CRn-Cn)*(Rn*Rn+1..) 14 Discount Rate 1 1.1 1.21 1.33 1.46 1.61 Assuming a discount rate 10%, (1+d)^n CLV-Yearly -3 9.090909 5.950413 4.395192 3.356328 2.542673 Calculated expeted customer value ----(CRn-Cn)*(Rn*Rb+1...)/(1+d)^n 18 Cumulative CLV 6.090909 12.041322 16.436514 19.792842 22.335515 Calculated expected sum for CLV A B C 1 Customer Lifetime Value Year 0 D2 10 E F 3 4 C5 G H I J K L M N 2 3 Average Acusition Cost -3 All areas in gray are known or estimated values for a time horizon of 5 years. Promotional, sales, and setup costs of aquiriring a new customer 4 5 Average Customer Revenue 30 6 Average Customer Costs 20 NW 37 25 7 Average Customer Profit 10 12 432 45 53 60 Expecte average per customer revenue (CRn) 30 35 40 Expecte average per customer cost (Cn) 15 18 20 Calculated expected per customer profit margin (CRn-Cn) 8 9 Customer Retention Rate 1 0.6 0.65 0.7 10 Cumulative Retention Rate 1 0.6 0.39 0.273 0.75 0.20475 Expected yearly customer retention rate (Rn) Calculated iteration of expected retention rates (Rn*Rn+1..._) 11 231567% Likely Customer Profit 10 7.2 5.85 4.914 4.095 Calculated expected profit (CRn-Cn)*(Rn*Rn+1..) 14 Discount Rate 1 1.1 1.21 1.33 1.46 1.61 Assuming a discount rate 10%, (1+d)^n CLV-Yearly -3 9.090909 5.950413 4.395192 3.356328 2.542673 Calculated expeted customer value ----(CRn-Cn)*(Rn*Rb+1...)/(1+d)^n 18 Cumulative CLV 6.090909 12.041322 16.436514 19.792842 22.335515 Calculated expected sum for CLV

Answer rating: 100% (QA)

To calculate the Customer Lifetime Value CLV for an infinite timeline given estimates for profit mar... View the full answer