Data Entry Area Lifetime Value Calculated Results. Do not enter any data here.
Modify these figures to enter your data This table will be computed automatically
Table 1 Table 2
Acquisition Second Third Acquisition Second Third
Code Variables Year Year Year Variables Year Year Year
A Orders per year 1.1 1.2 1.3 Retained Customers 200,000 121,200 86,537
B Average Order Size $90 $100 $120 Referred Customers 2,000 2,424 2,596
C Retention Rate 60.0% 70.0% 80.0% Total Customers 202,000 123,624 89,133
D Referral Rate 1.0% 2.0% 3.0% Retention Rate 60% 70% 80%
E Cost Percent 60% 58% 57% Referral Rate 1.0% 2.0% 3.0%
F Acquisition Cost Each $32 0 0 Orders per year 1.1 1.2 1.3
G Marketing Cost Each $3 $10 $10 Average Order Size $90 $100 $120
H Special Program Cost $2 $2 $2 Annual Revenue $19,998,000 $14,834,880 $13,904,733
I Database Cost $4 $4 $4
J Referral Incentives $15 $15 $15 Cost % of Revenue 60% 58% 57%
K Interest Rate 6.0% 6.0% 6.0% Product Costs $11,998,800 $8,604,230 $7,925,698
L Risk Factor 1.60 1.60 1.60 Acquisition Cost $6,400,000 0 0
M Days delay in payment 30 30 30 Marketing Cost $606,000 $1,236,240 $891,329
N Segment Size 200,000 NA NA Special Program Cost $404,000 $247,248 $178,266
Database Cost $808,000 $494,496 $356,532
If you have no data in a field, enter zero. Referral Incentives $30,000 $36,360 $38,942
Total Costs $20,246,800 $10,618,574 $9,390,766
For instructions click the link below:
Net Revenue ($248,800) $4,216,306 $4,513,967
Discount Rate 1.01 1.10 1.21
NPV Revenue ($246,933) $3,818,119 $3,729,625
Cum NPV Revenue ($246,933) $3,571,186 $7,300,811
Lifetime Value ($1.23) $17.86 $36.50
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.
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
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 ba
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, e
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.
ments will appear in Table 2.
ed with children, for example. For business to business it could be SIC code groups with similar annual sales or numbers of employees.
and Bonuses, etc. and divide that total by the number of new customers actually acquired in the year.
rate of interest charged by banks to business customers such as your company.
declining market, terrorism, etc.