1-4

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. N 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 50,000 customers? Round your answers to the nearest dollar 60 percent customer retention rate case: s 80 percent customer retention rate case: s 4. What do you condlude? If customer retention can be increased from 60 to 80 percent through better value chain performance, the economic payoff is XExcel template Saved File Tell me what you want to do Home Insert Data Review View Help X Cut A A 2Wrap Text Arial 10 Copy Paste BIUDab A Merge & Center Format Painter Clipboard Undo Font Allignment Number B9 A B D C E G Value of a Loyal Customer 1 2 Revenue per unit Percent contribution margin to profit and overhead Repurchase frequency (purchases/year) Customer retention rate Number of customers representing 1 percent market share $50.00 4 50% 0.5 6 0.6 50,000 7 Formulas Buyer's life cycle #N/A 10 11 VLC Value of a 1 percent change in market share #NA #N/A 12 13 14 Customer retention rate 0.8 15 16 Buyer's life cycle #N/A 17 18 VLC Value of a 1 percent change in market share UN/A 19 #N/A 20 21 22 23 24 25 26 27 Sheet1 M Activity #2: VLC Assume that a customer in the small contractor target market segment buys an electric drill on average every two yea (or every 0.5 year) for $50, when the gross margin on the drill averages 50 percent. The data has been collected in th Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions belca X 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 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 50,000 customers? Round your answers to the nearest dollar. 60 percent customer retention rate case: s fr A B C D Value of a Loyal Customer 1 Revenue per unit Percent contribution margin to profit and overhead Repurchase frequency (purchaseslyear) Customer retention rate Number of customers representing 1 percent market share $50.00 50% 4 0.5 0.6 50,000 Formulas #N/A Buyer's life cycle 10 VLC Value of a 1 percent change in market share 11 #N/A 12 #N/A 13 14 Customer retention rate 0.8 15 16 Buyer's life cycle #N/A 17 18 VLC Value of a 1 percent change in market share #N/A #N/A 20 21 22 23 23 56 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. N 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 50,000 customers? Round your answers to the nearest dollar 60 percent customer retention rate case: s 80 percent customer retention rate case: s 4. What do you condlude? If customer retention can be increased from 60 to 80 percent through better value chain performance, the economic payoff is XExcel template Saved File Tell me what you want to do Home Insert Data Review View Help X Cut A A 2Wrap Text Arial 10 Copy Paste BIUDab A Merge & Center Format Painter Clipboard Undo Font Allignment Number B9 A B D C E G Value of a Loyal Customer 1 2 Revenue per unit Percent contribution margin to profit and overhead Repurchase frequency (purchases/year) Customer retention rate Number of customers representing 1 percent market share $50.00 4 50% 0.5 6 0.6 50,000 7 Formulas Buyer's life cycle #N/A 10 11 VLC Value of a 1 percent change in market share #NA #N/A 12 13 14 Customer retention rate 0.8 15 16 Buyer's life cycle #N/A 17 18 VLC Value of a 1 percent change in market share UN/A 19 #N/A 20 21 22 23 24 25 26 27 Sheet1 M Activity #2: VLC Assume that a customer in the small contractor target market segment buys an electric drill on average every two yea (or every 0.5 year) for $50, when the gross margin on the drill averages 50 percent. The data has been collected in th Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions belca X 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 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 50,000 customers? Round your answers to the nearest dollar. 60 percent customer retention rate case: s fr A B C D Value of a Loyal Customer 1 Revenue per unit Percent contribution margin to profit and overhead Repurchase frequency (purchaseslyear) Customer retention rate Number of customers representing 1 percent market share $50.00 50% 4 0.5 0.6 50,000 Formulas #N/A Buyer's life cycle 10 VLC Value of a 1 percent change in market share 11 #N/A 12 #N/A 13 14 Customer retention rate 0.8 15 16 Buyer's life cycle #N/A 17 18 VLC Value of a 1 percent change in market share #N/A #N/A 20 21 22 23 23 56