Question

I need to show how to calculate step by step!

Sephora wants to evaluate the effect of its loyalty program with customer lifetime value (CLV). The expected profits per customer is assumed to be $300 over the next five years, the chur rate is 15% before the program and 10% after Sephora launched the program. The development cost of the program is $200,000,000.

Fill in the CLV table below.

Calculate the ROI of the loyalty program.

With the program
	Year 1	Year 2	Year 3	Year 4	Year 5
Profits per customer	300	300	300	300	300
No. customers	1,000,000	900,000	810,000
Chur rate	10%	10%	10%	10%	10%
Expected profits	300,000,000	270,000,000	243,000,000
Discount rate	10%	10%	10%	10%	10%
Discount years	1	2	3	4	5
Present value of expected profits	272,727,273	223,140,496	182,569,497
Cumulative LTV	272,727,273	495,867,769	678,437,265

Without the program
	Year 1	Year 2	Year 3	Year 4	Year 5
Profits per customer	300	300	300	300	300
No. customers	1,000,000	850,000	722,500
Chur rate	15%	15%	15%	15%	15%
Expected profits	300,000,000	255,000,000	216,750,000
Discount rate	10%	10%	10%	10%	10%
Discount years	1	2	3	4	5
Present value of expected profits	272,727,273	210,743,802	162,847,483
Cumulative LTV	272,727,273	483,471,074	646,318,557

Answer 1