Chapter 66: Determining Customer Value

Overview

• A credit-card company currently has an 80 percent retention rate. How will the company’s profitability improve if the retention rate increases to 90 percent or higher?

• A long-distance phone company gives the competition’s customers an incentive to switch. How large an incentive should they give?

Many companies undervalue their customers. When valuing a customer, a company should look at the net present value (NPV) of the long-term profits that the company earns from the customer. (For detailed information about net present value, see Chapter 7, “Evaluating Investments by Using Net Present Value Criteria.”) Failure to look at the long-term value of a customer often causes a company to make poor decisions. For example, a company might cut its customer service staff by 10 percent to save $1 million, but the resulting decrease in service quality might cause them to lose much more than $1 million in “customer value,” which would, of course, result in the company being less profitable. The following two examples show how to compute customer value.

• A credit-card company currently has an 80 percent retention rate. How will the company’s profitability improve if the retention rate increases to 90 percent or higher?

• Our example is based on a discussion in Frederick Reichheld’s excellent book The Loyalty Effect (Harvard Business School Press, 2001). You can find the sample data we’ll use in the file Loyalty.xlsx, shown in Figure 66-1 on the next page. Reichheld estimates the profitability of a credit-card customer based on the number of years the customer has held a card. For example, during the first year a customer has the credit card, the card-holder generates –$40 profit, which is the result of customer acquisition costs and the cost of setting up the customer’s account. During each successive year, the profit generated by the customer increases until a customer who has owned a card for 20 or more years generates $161 per year in profits.

  
  Figure 66-1: Value of a credit-card customer

• The credit-card company wants to determine how the value of a customer depends on the company’s retention rate. Currently, the company has an 80 percent retention rate, which means that at the end of each year, 20 percent (1–0.80) of all customers do not renew their card. (We refer to the 20 percent of customers who don’t renew as the annual churn rate.) The credit-card company wants to determine the long-term value of a customer for retention rates of 80 percent, 85 percent, 90 percent, 95 percent, and 99 percent.

• To determine the long-term value of a customer, we start with a cohort of, for example, 100 customers. (A cohort is a group of individuals having a statistical factor in common. The size 100 is arbitrary here, but round numbers make it easier to follow the analysis.) Then we determine how many of these customers are still around each year with the formula (Customers around for year t+1)=(Retention rate)*(Customers around for year t). We assume that customers “quit” only at the end of each year. Then we use the NPV function to determine the total NPV (assuming a 15 percent discount rate) generated by our original cohort of 100 customers. The 15 percent discount rate implies that $1 earned one year from now is worth the same as $1.00/$1.15 of profit earned now. Dividing this number by the number of customers in our original cohort (100) gives us the value of an individual customer.

• I first assign the names in the cell range B6:B7 to the cell range C6:C7. Then I enter our number of original customers (100) in cell C9. Copying from cell C10 to the range C11:C38 the formula retention_rate*C9 generates the number of customers present for each year. For example, we will have 80 customers present in year 2.

• I compute the profit earned each year by multiplying the number of remaining customers by each customer’s profit. To make this calculation, copy from cell D9 to D10:D38 the formula C9*B9. In cell E4, I compute the average NPV generated by an individual customer with the formula (1+Interest_rate)*NPV(Interest_rate,D9:D38)/100. We are assuming cash flows at the beginning of the year and a 15 percent annual discount rate. The portion of the formula that reads NPV(Interest_rate,D9:D38) computes the average NPV generated by an individual customer assuming end-of-year cash flows. Multiplying by (1+Interest_rate) converts the end-of-year cash flow NPV to a beginning-of-year NPV.

• With an 80 percent retention rate, we find that the average customer is worth $141.72. To determine how the value of an individual customer varies with a change in annual retention rate, I use a one-way data table. I enter the relevant annual retention rates in the cell range F9:F13. In cell G8, I enter the formula we want the data table to calculate (NPV per customer) with the formula =E4. Next, I select the table range (F8:G13) and then choose Data Table from the What-If Analysis command on the Data tab. After entering a Column Input Cell of C6, I obtain the profit calculations shown in Figure 66-1. Notice that increasing our retention rate from 80 percent to 90 percent nearly doubles the value of each customer, which strongly argues for being “nice” to these customers and against pinching pennies on activities related to customer service. Understanding the value of a customer gives most companies a crucial lever that can be used to increase their profitability.

• A long-distance phone company gives the competition’s customers an incentive to switch. How large an incentive should they give?

• Let’s say that we work for a phone company in which the average long-distance customer spends $400 per year and the company generates a 10 percent profit margin on each dollar spent. At the end of each year, 50 percent of our company’s customers switch to the competition, and without any incentives, 30 percent of the competition’s customers switch to our company. We’re considering giving the competition’s customers a one-time incentive to switch companies. How large an incentive can we give and still break even?

• The key to analyzing this problem (which you can find in the file Phoneloyalty.xlsx, shown in Figure 66-2 on the next page) is to look at the NPV for two situations:

  • Situation 1: 100 customers begin with the competition.

  • Situation 2: We pay the 100 customers who are with the competition a certain amount to switch to us.

  
  Figure 66-2: Phone incentive analysis

• Following through each situation for a period of time (for example, 20 years), we can use the Excel Goal Seek command to determine the dollar amount x paid to a person switching to our company that makes us indifferent between the following two situations:

  • Situation 1: We have just paid 100 non-loyal customers $x each to switch to us.

  • Situation 2: The market consists of 100 non-loyal customers.

• We assume that our analysis begins on June 30, 2004, and that customers switch companies, at most, once per year. I assigned the range names in cells A2:A6 to cells B2:B6. The key step in our analysis is to realize that (year t+1 customers with us)=.3*(year t competitor customers)+.5*(year t our customers). Similarly (year t+1 customers with competition)=.7*(year t competition customers)+.5*(year t our customers).

• Next I enter 100 in cell D9 (customers with us) and 0 in cell E9 (customers with the competition). This customer alignment corresponds to the situation right after we offer our incentive to 100 customers. We’re assuming that customers who receive the incentive must stay with us for at least one year. Copying from D10 to D11:D28 the formula (1–probleave)*D9+probcome*E9 generates the number of customers we have during each year (years 2012–2019 are hidden in Figure 66-2). Copying from E10 to E11:E28 the formula probleave*D9+(1–probcome)*E9 gives us the number of customers with the competition during each year.

• In cell F9, I generate the profit earned during our first year with the formula D9* annrevenue*profitmargin–switch_fee*100. Note that I have subtracted the cost of paying the 100 customers with the competition to switch. Copying from F10 to F11:F28 the formula D10*annrevenue*profitmargin generates our profit during later years. In cell D5, I compute the NPV of the profits associated with the incentive by using the formula XNPV(0.1,F9:F28,B9:B28). (See Chapter 7 for a discussion of the XNPV function.)

• In a similar fashion, in the cell range G8:I28 I generate the profits earned each year from 100 customers who were originally with the competition. On June 30, 2004, 30 of these 100 customers will have switched to us (even without incentives). In cell F2, I compute the difference between our NPV with the incentive and our NPV without the incentive.

• Finally I use Goal Seek to vary the size of the incentive (cell B2) to set F2 equal to 0. The Goal Seek dialog box is shown in Figure 66-3. We find that an incentive of $34.22 makes the NPV of the two situations identical. Therefore, we could give incentives of up to $34.21 for a customer to switch and still have increased our profitability.

  
  Figure 66-3: Goal Seek settings to determine the maximum incentive that increases profitability