Question

Excel Online Activity: VLC Assume that a customer in the small contractor target market segment buys an electric drill on average every two years (or every 0.5 year) for $50, when the gross margin on the drill averages 50 percent. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. Х Open spreadsheet Questions 1. What is the value of a loyal customer (VLC) if the customer retention rate is 60 percent? Round your answer to the nearest cent. $ 2. What is the value of a loyal customer (VLC) if the customer retention rate increases 80 percent? Round your answer to the nearest cent. $ 3. What is a 1 percent change in market share worth to the manufacturer if it represents 100,000 customers? Round your answers to the nearest dollar. 60 percent customer retention rate case: $ 80 percent customer retention rate case: $ 4. What do you conclude? If customer retention can be increased from 60 to 80 percent through better value chain performance, the economic payoff is

Answer 1

A B C D E F G H 3 4 1 Value of a Loyal Customer 2 3 Revenue per unit {R} Percent contribution margin to profit and overhead {CM} 5 Repurchase frequency (purchase/year) {RF} 6 Customer retention rate 7 Number of customers representing 1 percent market share 8 Customer defection rate $50.00 50% 0.5 0.6 100,000 0.4 6 Formulas 1 - customer retention rate ca LD Buyer's life cycle (BLC} 2.5 1/customer defection rate VLC Value of a 1 percent change in market share $31.25 $3,125,000 R*CM*RF*BLC Number of customers representing 1% market share*VLC 929998 0.8 Customer retention rate Customer defection rate 0.2 1 - customer retention rate Buyer's life cycle (BLC} 5 1/customer defection rate VLC Value of a 1 percent change in market share $62.50 $6,250,000 R*CM*RF*BLC Nun of customers representing 1% market share*VLC 21 22

с D E F Formulas 1 - customer retention rate 1/customer defection rate А B 1 Value of a Loyal Customer 2 3 Revenue per unit {R} 50 4 Percent contribution margin to profit and overhead {CM} 0.5 5 Repurchase frequency (purchase/year) {RF} 0.5 6 Customer retention rate 0.6 7 Number of customers representing 1 percent market share 100000 8 Customer defection rate =1-B6 9 10 Buyer's life cycle {BLC} =1/B8 11 12 VLC =B3*34*35*B10 13 Value of a 1 percent change in market share =B7*B12 14 15 Customer retention rate 0.8 16 Customer defection rate =1-B15 17 18 Buyer's life cycle (BLC) =1/B16 19 20 VLC =B3*34*85*B18 21 Value of a 1 percent change in market share =B7*B20 22 R*CM*RF*BLC Number of customers representing 1% market share*VLC 1 - customer retention rate 1/customer defection rate R*CM*RF*BLC Number of customers representing 1% market share*VLC

4. If customer retention can be increased from 60% to 80% through better value chain performance, the economic payoff is doubled. (6,250,000 / 3,125,000 = 2 times i.e doubled)