Question

A certain newspaper has an annual subscription price of $338, and annual variable costs per subscriber are $300. Current retention spending is $8 a year per customer resulting in an attrition rate of 23% per year. The marketing department estimates that if the newspaper were to increase its retention spending to $12 a year, the attrition rate would be reduced to 15%. What would be the difference in the CLV if the spending is increased and a discount rate of 13% is used?

Please post the Excel formula

Answer 1

Sl.No	Descriptions	Existing	Proposal
i	Annual subscription price (Given in question)	338.00	338.00
ii	Annual variable costs (Given in question)	300.00	300.00
iii	Gross profit contribution (i-ii)	38.00	38.00
iv	Retention Cost (Given in question)	8.00	12.00
v	Net profit contribution (iii-iv)	30.00	26.00
vi	Percentage of total customers retained (Given in question)	77%	85%
vii	Net profit contribution of retained customers only (v*vi)	23.10	22.10
viii	Discount rate (Given in question)	13%	13%
ix	Discounted Net profit contribution per retained customer [vii/(1+viii)]	20.44	19.56

The difference in the CLV if the spending is increased is $0.88 per customer