Question

Imagine you are consulting for an Internet service provider (ISP) that has hired you to research the impact of customer retention on their bottom line. Assume that their current monthly turnover is 3%, they have 1000 subscribers, they have set monthly subscriber fees at $25, and they estimate that their cost to serve customers is $10. Further, while they report that they do not have an estimate for customer acquisition cost, you do a little number crunching and find that they are spending $10 advertising for each new customer, they are offering their employees a $5 incentive to sell the service and they provide free installation software that costs $10. You also feel that a 5% interest rate is a reasonable prediction. Using this information, calculate the following:

a.) Annual turnover rate

b.) Annual customer turnover number

c.) Margin per month

d.) Break Even Value

e.) Customer Lifetime Value

Answer 1

Monthly Subscriber Turnover = 3 % = churn rate/ month

Therefore retention rate = 1- churn rate = 97%

Total Number of Subscribers = 1000

a) Annual Turnover Rate =36 %

b) Therefore, Annual Turnover = 12 * 3 = 36 % of 1000 = 360 Subscribers

Monthly Subscription Fee = $25

Cost to Serve the customer = $ 10

c) Margin per month = $25 - $10 = $15

Long term interest rates = 5 %

Calculation of customer acquisition cost:-

Advertising spend / customer(A) = $ 10

Incentive / customer conversion(B) = $ 5

Cost of free installation / customer( C ) = $ 10

Total acquisition cost per customer = A +B +C =$ 25

d) Since the number of customers leaving each month is 30.The amounr spent to gain themback is 25*30 =$ 750. So, in order to break even we need to make up for the cost of acquisition.

Average contribution per customer = $15

Hence, total minimum customer = $ 750 / 15 = 50 Customers

e ) Customer Lifetime Value = Annual Profit per customer * customer retention rate – initial acquisition cost

Formula Sheet for the above excel is :-

The above calculation has been done for a horizon of 5 years.

For an infinite time horizon, the CLV is given by

Where,

m = Margin per customer

r = Retention Rate

d = discount rate

AC = Cost of acquisition

Putting the values of the various variables in the above equation we have

CLV = 15/(1- 0.64+0.05) – 25 = 15/0.31 -25 = 48.38 - 25 = $ 23.38.