EXERCISE 1: CALCULATE CUSTOMER LIFETIME VALUE IN EXCEL

This activity will allow students to calculate average Customer Lifetime Value in a simpler example using Excel. Students will be given the following assumptions and asked to calculate CLV.

Open CLV Calculation.xlsx (under Exercise Materials).

SEE THAT THE INITIAL ASSUMPTIONS ARE AS FOLLOWS:

Annual revenue per average customer = $2,000 per annum

Product costs associated with the average customer's purchases = $500 per year

Customer Service Expenditure= $100 a year per customer

Annual retention rate (loyalty rate) = 80%

Average costs to acquire a new customer = $1,000

STEP 1: CALCULATE THE AVERAGE ANNUAL PROFIT PER CUSTOMER

Average annual profit per customer = Annual Revenue - (Product Cost +Service Cost)

Therefore, In this case, it is $2000 - $500 - $100 = $1,400.

STEP 2: TO CALCULATE THE CUSTOMER LIFETIME IN YEARS

FROM THE RETENTION RATE

Customer lifetime in years = 100% / (100% - Annual Retention Rate) = (1 / 1- Annual Retention Rate)

So in this example of an 80% loyalty rate, the Average Customer Lifetime Period = 100% / (100% -80%) = 100% / 20% = 5 years

STEP 3: TO CALCULATE LIFETIME VALUE (CLV)

CLV = $1,400 (profit) X 5 (years) - $1,000 (acquisition) = $6,000