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

Im trying to set this up in Excel, This is what I have so far but I am not too sure what to do after this. Any help? Thanks in advance.

File Home Insert Page Layout Formulas Data Review View Help Share Comments Calibri 11 AA a Wrap Text General PA Normal Bad Good AutoSum Fill X Cut LO Copy Format Painter 07 Paste B 1 U A 19 $ % Calculation 20 Check Cell Insert Delete Format Merge & Center v Analyze Solver Sensitivity Conditional Format as Neutral Formatting Table Clear Sort & Find & Filter Select Data Clipboard Font Alignment Number Styles Cells Editing Analysis Sensitivity Solver D8 X fr H 1 J L M N o P O R S T U V w Y Z AA AB 6 1 gig B D E F Monthly Marginal Gross customer Total 1 Speed Rev cost Margin s Margin 2 50 Mbps 50 45 5 2400 3 150mbps 65 45 20 1800 4 250mbps 80 45 35 900 5 500mbps 100 45 55 600 150 45 105 300 7 total 445 45 6000 Average margin per cust. 8 Per month Average Margin Per 9 Customer per Year 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35