B C D E F G H K L M N O P CREATING CUSTOMER EQUITY SPREADSHEET AND MODEL Variables in the Model Which May Be Changed 1. Acquisition costs per customer $75 Assumed to operate as a variable cost but not included as a variable cost for contribution margin purposes, constant over the time horizon 2. Average revenues from a new customer in first year $150 Assumed constant for new customers over the time horizon 3. Contribution margin 40% Value for new customers, could gradually increase for retained customers (see below) 4. Number of new customers in Year 1 1000 Number of new customers 8 5. Number of new customers added due to marketing efforts 3000 Assumes constant growth "number" per year 6. Customer retention r 60% Assumed to be the same for all current customers from previous year 10 7. Retention/maintenance costs for current customers $10 Assumed constant over the full time horizon 8. Annual revenue growth from customers 20% Customers are assumed to increase their purchases over time from cross-selling and generally preferring the business -- rate does not change over time 12 9. Cost savings (change in contribution) for retained customers 1% Assumed to increase annually at this rate for 5 years then level off 13 10. Referrals from current customers 20% 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. 14 11. Price premium from current customers 0% 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 15 12. Discount rate for present value calculation 3% 16 32 Customer Equity: NPV of Customer Contribution $320,910 33 45 46 Pro Forma Income Statement 47 48 Revenues 49 from new customers $ 150,000.0 $450,000.0 $468,000.0 $514,800.0 $545,040.0 50 from current customers $129,600 $482, 112 $751,473 $985,848 Total Revenues $150,000 $579,600 $950, 112 $1,266,273 $1,530,888 53 54 Costs 55 acquisition costs (not part of contribution) $75,000.0 $225,000.0 $225,000.0 $225,000.0 $225,000.0 56 variable costs of sales [ie., revenue*(1-contribution)] $90,000 $400,464 $620,473 $809,882 $965,685 57 retention/maintenance costs (# of customers *maint costs) 10,000 36,000 52,800 66,000 75,936 58 59 Total Variable, Acquisition and Maintenance Costs 175,000 661,464 898,273 1, 100,882 1,266,621 60 61 Customer contribution after variable, acquisition, and maintenance costs -25,000 81,864 51,839 165,391 264,266 62 Rate of contribution after variable, acquisition, and maintenance costs 17% -14% 5% 13% 17% 63 64 Net Present Value of Customer Contribution $320,910 65 66 67 68 Joe Cannon 2003 all rights reserved 69 70 Sheet1 Sheet2 Sheet3 +