solved Customer Equity Model – An Interactive ExerciseThis spread sheet and

Customer Equity Model – An Interactive ExerciseThis spread sheet and associated exercise is designed to give you some insight into the customer equity approach to marketing management. The spreadsheet is an example of a customer equity type of model. Marketing managers are increasingly combining art and intuition with quantitative models. The Customer Equity Model allows you to change assumptions and estimate the impact on a firm, company, or product’s customer equity. The learning objectives of this exercise are as follows:To introduce you to customer equity conceptsTo introduce you to an example of a quantitative marketing model To explore how such a model can help you to make decisions about how to allocate marketing resources.To describe strategic options available to marketing managers.To achieve these objectives I have designed the following activities so that you can first understand the spread sheet and then use it to make some marketing decisions. In class, we will be reviewing the model by discussing some of the following “big picture” questions:1.In the real world, how would a marketing manager determine the values for some of the variables in the model (cells B4:B15)? 2.What are the advantages of the Customer Equity Model?3.What are the disadvantages of the Customer Equity Model?4.What other thoughts do you have about the Customer Equity Model?5.What have you learned about marketing from this experience?Follow the instructions on the following pages to get a better feel for how the Customer Equity Model works. Think about the question in each section and write down your responses. Please respond to both the questions above and those below in each section (questions 1-4).Getting to Know the Customer Equity ModelYour first assignment is simply to understand the variables that you can change in the model. You are able to change the numbers in green and immediately see the change in customer equity in purple. read the comments – which define each of the variables that you can change. By running your cursor over cells A4 – A15 and A43, a definition of each element will appear in a yellow box. Next, look to the right of each green number to learn a little more about the assumptions used to build the model. Let’s try playing with the model a bit. Start with the numbers listed below (which were included when you originally downloaded the model). These numbers may be reflective of what a small e-tailer might face. Let’s assume these numbers represent data for “Monterrey Little SportE-Bookstore” (MLEB) a fictional online book retailer who specializes in books about sports. Looking through the spread sheet note the following are the default numbers (see below):DEFAULT SPREADSHEET VALUESVariables in the Model Which May Be Changed1. Acquisition costs per customer$75 2. Average revenues from a new customer in first year $150 3. Contribution margin 40%4. Number of new customers in Year 110005. Number of new customers added due to marketing efforts30006. Customer retention rate60%7. Retention/maintenance costs for current customers$10 8. Annual revenue growth from customers 20%9. Cost savings (change in contribution) for retained customers1%10. Referrals from current customers20%11. Price premium from current customers0%12. Discount rate for present value calculation3%Customer Equity: NPV of Customer Contribution $320,910Look at the level of customer equity (in purple) – cell B32 and above.What about the contribution level over the years (see row 61). In what year does the firm first have positivecontribution? (Note that fixed costs are not included, so there may be other costs, too.) What would this say about the company’s cash flow? Review other numbers and note anything you find interesting and why.Now let’s play some “what if?” games with our model. Make the following changes to the numbers and note the changes in customer equity. First, let’s look at an acquisition focused strategy and assume that MLEB wants to investigate the possibility of increasing its online advertising at a number of sites. The more aggressive promotional campaign increases acquisition costs per customer, but also the number of new customers per year. Based on past experience, the owner of MLEB assumes that acquisition costs will increase to $82 per customer and the number of new customers per year to 4000. Change acquisition costs per customer (cell B4) to $82 and cell B8 to 4000.Using the Customer Equity Model as your evaluation tool, would this be a good strategy compared to current practice? What would customer equity be if you were able to increase the annual number of new customers to 4500 (cell B8) with only the $7 increase in customer acquisition costs (cell B4 from 75 to 82)? What if this increase resulted in new customers being only 3500 (cell B8)?Using your intuition, do you think that this strategy should change any other variables in our model? Note that right now we are not changing any other variables – but what variables could change?Is there any other analysis you would recommend?The owner of MLEB was also considering an alternative acquisition-oriented strategy with a program that rewarded current customers that referred new customers to the store. Each time a current customer referred a new customer, the current customer received a $10 credit. The owner assumed that this would increase acquisition costs by $3 (this included advertising to announce the program, the cost of rewarding customers, and of course not all new customers would come from this method). He also thought this would increase referrals percentage to 40%. First, change all numbers back to the default spreadsheet values shown in number 2 above. Next, change acquisition costs (cell B4) to $78 and change referrals from current customers (cell B13) to 40%. Using customer equity as your evaluation tool, would this be a good strategy compared to current practice?Using your intuition, do you think that this strategy should change any other variables in our model?Is there any other analysis you would recommend?The owner of MLEB also thought about marketing strategies that emphasized customer retention. He thought that if he increased the customer service staff and developed an individualized and customized e-mail newsletter for each customer, that customers would be more loyal to MLEB. One plan was more aggressive – had higher costs more but also increased customer retention to a greater degree. First, change all numbers back to the default spreadsheet values shown in number 2 above.Plan A changes retention and maintenance costs for current customers (B10) to $20 and customer retention rate (B9) to 80%. Plan B (less aggressive) changes retention and maintenance costs for current customers (B10) to $15 and customer retention rate (B9) to 70%. Using customer equity as your evaluation tool, would you recommend either strategy be used in place of current practice?Using your intuition, do you think that this strategy should change any other variables in our model?Is there any other analysis you would recommend?A third approach might be to work with current customers to more aggressively encourage add-on sales. The owner of MLEB had ideas about a series of e-mail delivered promotions that would encourage customers to increase the frequency of their shopping visits to MLEB and the amount they spent each time. The marketing manager thought he had a plan that would increase retention/maintenance costs to $20 per customer, but would grow customers annual purchases more quickly (at a rate of 35% per year). On the downside, he was worried that the more frequent messages might have a detrimental effect on customer retention. First, change all numbers back to the default spreadsheet values shown in number 2 above. Change the retention/maintenance costs (increase cell B10 to $20) and increase current customer annual purchases more quickly (increase cell B11 to 35%). On the other hand, the more frequent “selling” messages to current customers might have a detrimental effect on customer retention – so we reduce that to 55% (cell B9). Using customer equity as your evaluation tool, would this be a good strategy compared to current practice?Using your intuition, do you think that this strategy should change any other variables in our model?Is there any other analysis you would recommend?NOTE: Question 3 is not designed to demonstrate the superiority of one type of strategy (acquisition, retention, or add-on sales). Clearly this depends upon the other numbers in the spreadsheet. Also, the spreadsheet should not be your only guide and a sensitivity analysis must be conducted, too. There should be enough marketing research to have confidence in the assumptions you make. Next, let’s plug in some number that might more accurately reflect a business marketer’s situation. Here assume we have an electronics wholesaler (Cartagena Wholesale Electronics Supply – CWES) that sells products to many electronics manufacturers. The marketing strategy relies primarily on personal selling to acquire new customers and telemarketing to maintain the customers. Numbers for this type of firm might look more like the following:1. Acquisition costs per customer$1,800 2. Average revenues from a new customer in first year $10,000 3. Contribution margin 20%4. Number of new customers in Year 12005. Number of new customers added due to marketing efforts256. Customer retention rate80%7. Retention/maintenance costs for current customers$400 8. Annual revenue growth from customers 8%9. Cost savings (change in contribution) for retained customers1%10. Referrals from current customers10%11. Price premium from current customers0%12. Discount rate for present value calculation3%Change the spreadsheet to reflect the numbers for CWES listed above (if you do it correctly you should have Customer Equity = $1,251,714). Assume that these numbers are a pretty accurate estimate of the firm’s current strategy. Develop three different strategies – One that emphasizes acquisition of new customers, One that focuses on increasing customer retention, and Finally one that promotes add-on sales.For each strategy, describe the types of actions you propose (think about what a marketing strategy entails – 4 P’s and the target market). Make some reasonable assumptions about how your changes would impact variables in the model. I realize that this is a bit of a “guess” since you do not necessarily understand this market – but give it a try. Use common sense and what you have learned as a marketing major. If you were hired by CWES, how would you improve your estimates of these numbers? Change the numbers in the spread sheet to reflect your new estimates. What were the changes in customer equity? What other analysis would you recommend?