Learn how to evaluate Customer Lifetime Value (CLV)

Task

Calculate the lifetime value of an Internet customer.

A local Internet Service Provider is trying to calculate the lifetime value of each Internet customer. Use the following equations to calculate the CLV.

CLV=mLT-AC

LT=1/Churn

Churn= disconnects/total customers

m=marginal revenue marginal cost

AC is what is costs to acquire a single customer. This could be calculated as marketing costs/sales + install costs.

The customer segments break down as follows:

Speed	Revenue	# of Customers
50Mbps	$50	2400
150Mbps	$65	1800
250Mbps	$80	900
500Mbps	$100	600
1 Gig	$150	300

Here is the other information you will need:

Marginal Cost of servicing a customer per month	$45
Annual Marketing Costs	$110,000
Annual Sales Commissions	$30,000
Annual Employee Growth Bonuses	$30,000
Install Costs Labor and materials (per install)	$150
Equipment Costs (per install)	$250
Total number of Sales per year	1300
Total number of Disconnects Per Year	700

Tips and helps

Pretend that this is a snapshot of the customer base at the year end. Do not add the sales for the year or the disconnects for the year into the customer base--they are already all included in that number.

I have given you the total sales of 1300 just so you can calculate the marketing acquisition cost.

In the marketing acquisition cost, you need to divide the total amount of money spent on marketing and sales by the number of sales you achieved. You also need to include the cost of the install for every new sale

The thing that students struggle with the most is calculating the monthly profit (m) per customer. You need to lay this out in a Google sheet or Excel sheet, and then find the average margin for your total customer base. You also need to convert this number to an annual basis, since your lifetime is going to be calculated in years. Everything needs to be in the same time base for this formula to work.

I suggest laying out your spreadsheet like this:

Speed	Monthly Revenue	Marginal Cost	Gross Margin	# Customers	Total Margin
50Mbps		$45
150Mbps		$45
250Mbps		$45
500Mbps		$45
1 Gig		$45
Total
Average Margin Per Customer per month
Average Margin Per Customer per Year

I only need help on the starred questions below. Thank you

Question 1 ANSWERED ALREADY

The total number of customers is: answered

6000

1300

7300

4000

Question 2 ANSWERED ALREADY

Your total amount of monthly profit across all customer tiers is

Group of answer choices

$144,000

$196,000

$136,000

$254,000

Question 3: ANSWERED ALREADY

The Average Margin Per Customer per month is

Question 4: answered

Average Margin Per Customer per year is

Question 5: ANSWERED ALREADY

To calculate Churn, we simply divide total disconnects by...

Group of answer choices

Total number of customers

Annual profit

Sales

The CEOs bonus

***Question 6: NEED ANSWER

What is the Churn Rate? (expressed as 0.XX)

****Question 7: NEED ANSWER

Since lifetime (LT) Is the inverse of Churn, LT is ___ years.

(This represents how many years on average a customer stays with us.)

***Question 8: NEED ANSWER

Total acquisition cost for a new customer is____

** Question 9: NEED ANSWER

CLV=(M*LT)-AC

The customer lifetime value for this Internet provider is____