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

This