Special Lifetime Value Calculator Download and Instructions
The Special Lifetime Value Calculator is presented as an Excel spreadsheet.
Download the spreadsheet file,
open it in Excel or other spreadsheet program then follow the instructions below.
Using the Excel chart Table 1, modify the numbers shown with your own numbers. The resulting lifetime value of your customer segments will appear in Table 2.
This table should be computed for a customer segment which has similar purchasing habits. It could be seniors, or teenagers, married with children, for example. For business to business it could be SIC code groups with similar annual sales or numbers of employees.
- A. B. Orders per year and average order size. Estimate these from existing records.
- C. Determine from your database how many newly acquired customers continue to make purchases in the second and third years after acquisition.
- D. If you have a program that encourages customers to refer others who become customers, put the percentage of successful referrals here.
- D. Estimate the cost of fulfilling the products or services. These are all inclusive costs: cost of goods sold, costs of delivery, sales commissions, etc.
- F. Acquisition cost: Add together your annual expenditures to acquire new customers by TV, Radio, Print, Direct Mail, Sales Visits and Bonuses, etc. and divide that total by the number of new customers actually acquired in the year.
- G. H. I. Estimate the costs per customer of your marketing to them
- J. If you compensate customers for referring customers who actually become customers, put the average amount paid out per successful referral.
- K. This is the market rate of interest used by your company to borrow money. If you do not borrow money, use the existing market rate of interest charged by banks to business customers such as your company.
- L. Risk factor: there is always risk in business. 1.0 = No Risk at All. 2.0 = There are serious risks from competition, obsolescence, declining market, terrorism, etc.
- M. Consumers usually pay at the time of purchase. Business customers pay in 30, 60, 90, 120 days. Put your number in here.
- N. Segment size. The LTV is the same for any size segment.
Return to home page.