Customer Equity Exercise

CREATING CUSTOMER EQUITY SPREADSHEET AND MODEL Variables in the Model Which May Be Changed 1. Acquisition costs per customer $1.800 2. Average revenues from a new customer in first year $10.000 6 3. Contribution margin 20% 7 4. Number of new customers in Year 1 8 5. Number of new customers added due to marketing efforts 9 6. Customer retention rate 10 7. Retention/maintenance costs for current customers 11 8. Annual revenue growth from customers 12 9. Cost savings (change in contribution) for retained customers 13 10. Referrals from current customers 14 11. Price premium from current customers 15 12. Discount rate for present value calculation N 16 32 Customer Equity: NPV of Customer Contribution 33 45 46 Pro Forma Income Statement 47 48 Revenues 49 from new customers 50 from current customers 51 52 Total Revenues 53 54 Costs 55 acquisition costs (not part of contribution) 56 variable costs of sales [ie., revenue*(1-contribution)] 57 retention/maintenance costs (# of customers "maint costs) 58 59 Total Variable, Acquisition and Maintenance Costs 60 61 Customer contribution after variable, acquisition, and maintenance costs 62 Rate of contribution after variable, acquisition, and maintenance costs 63 64 Net Present Value of Customer Contribution 65 66 4. 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 - o One that emphasizes acquisition of new customers, o One that focuses on increasing customer retention, and o 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? Assumed to operate as a variable cost but not included as a variable cost for contribution margin purposes, constant over the time horizon Assumed constant for new customers over the time horizon Value for new customers, could gradually increase for retained customers (see below) Number of new customers Assumes constant growth "number" per year Assumed to be the same for all current customers from previous year Assumed constant over the full time horizon Customers are assumed to increase their purchases over time from cross-selling and generally preferring the business-rate does not change over time Assumed to increase annually at this rate for 5 years then level off Percentage of customers who refer a single customer. Assumed that the referrals will come from customers retained for more than one year. These customers are added without acquisition costs. Assumes a premium that is constant beginning in third year as a customer-ie., premium is constant following the third year a customer is retained $2.000.000,0 $250.000.0 $410.000,0 $398.000.0 $1.866.240 $1.845.711 $1.977.274 $401.200,0 $2.079.746 $2.000.000 $2.116.240 $2.255.711 $2.375.274 $2.480.948 $360.000,0 $45.000,0 $1.600.000 $1.690.330 80.000 74.000 $45.000,0 $1.946.738 77.168 $45.000,0 $45.000,0 $1.796.228 $1.876.040 75.600 76.400 1.997.440 377.834 2.068.906 2.040.000 1.809.330 306.910 15% 1.916.828 338.884 15% 412.040 -40.000 -2% 16% 17% 8 3 & 8 U 200 25 80% $400 8% 1% 10% 0% 3% $1.251.714 $1.251.714