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 customers 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