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 $100, when the gross margin on the drill averages 30 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. X Open spreadsheet Questions 1. What is the value of a loyal customer (VLC) if the customer retention rate is 20 percent? Round your answer to the nearest cent. $ 2. What is the value of a loyal customer (VLC) if the customer retention rate increases to 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 150,000 customers? Round your answers to the nearest dollar 20 percent customer retention rate case: $ 80 percent customer retention rate case: $ 4. What do you conclude? If customer retention can be increased from 20 to 80 percent through better value chain performance, the economic payoff is quadrupled DⓇ Check My Work Reset Problem

Answer 1

Revenu per unit 'p' $100.00 Contribution margin to protit and Overheads 'CM' = = 30% Re purchase frequency -'RA' 0.5 l year V2C = PxCM X Rf & BLC Required 1 Retention rate = 2007 to calculate Buyer life cycle (BLC), we need defection rate which can be calculated from Retention rate. So Defection, rate 1-Retention Rate 1-20.1. = 0.8= 80%. BLC = 1 = 1.25 5 years 0.8 Defection Rate

So, VLC = Рxc1x Rf x В) с = $100 x 30) x 0.5x1.25 $18.75 Required 2 Retention rate 80% to calculate Buyer life cycle CBLC), we need defection rate which can be calarlated from retention rate. So, befection rate I- Retention rate 1- 80.1 = 0.2 = 20.1. 1 1 BLC = 5 years 0.2 Defection Rate So, VLC = PX CM X Rf X BLC $100 x 30). X0.5x5 = $75

Required 3 Total number of customers = 150000 Formula to calculate the 10% change in market Share = todel number of Customes VLC a) 16. charge in morket share at 20% retention rake Total number of customex & VLC (at 20%-) 50000 X $18.75 $ 2812500 b) 1. change in market share at 807. Yeterkion rate - Total number of customer X V2e Cat 80 ).) IS0000 x $ 75 $ 11250000 Required 4 If cushmer retention can be increased from 20 to so percent through better value chain perhomance the Economic payoff is quadrupled). $1250000 = 4 times ie quadrupled $2812500