Build an Excel spreadsheet to estimate how much a prospective customer is worth, depending on whether that customer is offered a rebate. What do you learn from the updated spreadsheet? Explain the logic behind your findings and conclusions. On the basis of these results, what sales and marketing strategies would you recommend for SyPhone?

Hint: Suppose you have 100 prospective customers in each of the four segments. Compute how much it will cost to manage the pre-sale process, how many customers will be won, and the fixed costs associated with these new customers. Finally, include the lifetime values you computed previously (use the 35% discount factor figures). Divide the final figure by 100 to compute how much a prospective customer is worth.

Segments / Segment description	Number of customers	Gross margins	Marketing costs, next period	Customer lifetime value
Large accounts	500	$63,000	$5,000	$77,299
Large accounts, rebate	2000	$36,000	$4,000	$52,099
Small accounts	5000	$10,800	$1,500	$11,730
Small accounts, rebate	7500	$7,200	$1,000	$9,197
Lost customers	0	$0	$0	$0
Number Of Customers Per Segment
Simulations of number of customers per segment, over 5 periods.
Segments / Periods	N+1	N+2	N+3	N+4	N+5
Large accounts	500	275	151	83	46
Large accounts, rebate	2000	1550	1190	908	689
Small accounts	5000	2900	1682	976	566
Small accounts, rebate	7500	5350	3803	2696	1907
Lost customers	0	4925	8174	10338	11793
Customer Base's Lifetime Value
Customer base's lifetime value (discount rate 35%) and discounted net margins, over 5 periods.
Margins and costs / Periods	N+1	N+2	N+3	N+4	N+5
Gross margins	$211,500,000	$142,965,000	$97,915,950	$67,860,689	$47,525,715
Marketing costs	$25,500,000	$25,500,000	$17,275,000	$11,842,250	$8,205,518
Net margins	$186,000,000	$117,465,000	$80,640,950	$56,018,439	$39,320,197
Discount factor (35%)	0.74	0.55	0.41	0.30	0.22
Discounted net margins	$137,777,778	$64,452,675	$32,775,878	$16,865,372	$8,768,935
Discounted net margins (cumulated)	$137,777,778	$202,230,453	$235,006,330	$251,871,702	$260,640,637
Retention Rate (Large accounts)
Retention rate of a customer currently in the "Large accounts" segment.
Segments / Periods	N+1	N+2	N+3	N+4	N+5
Large accounts	1.00	0.55	0.30	0.17	0.09
Large accounts, rebate	n/a	0.10	0.13	0.13	0.11
Small accounts	n/a	0.00	0.00	0.00	0.00
Small accounts, rebate	n/a	0.00	0.00	0.00	0.00
Lost customers	n/a	0.35	0.57	0.71	0.80
Retention Rate (Large accounts, rebate)
Retention rate of a customer currently in the "Large accounts, rebate" segment.
Segments / Periods	N+1	N+2	N+3	N+4	N+5
Large accounts	n/a	0.00	0.00	0.00	0.00
Large accounts, rebate	1.00	0.75	0.56	0.42	0.32
Small accounts	n/a	0.00	0.00	0.00	0.00
Small accounts, rebate	n/a	0.00	0.00	0.00	0.00
Lost customers	n/a	0.25	0.44	0.58	0.68
Retention Rate (Small accounts)
Retention rate of a customer currently in the "Small accounts" segment.
Segments / Periods	N+1	N+2	N+3	N+4	N+5
Large accounts	n/a	0.00	0.00	0.00	0.00
Large accounts, rebate	n/a	0.00	0.00	0.00	0.00
Small accounts	1.00	0.58	0.34	0.20	0.11
Small accounts, rebate	n/a	0.02	0.03	0.02	0.02
Lost customers	n/a	0.40	0.64	0.78	0.87
Retention Rate (Small accounts, rebate)
Retention rate of a customer currently in the "Small accounts, rebate" segment.
Segments / Periods	N+1	N+2	N+3	N+4	N+5
Large accounts	n/a	0.00	0.00	0.00	0.00
Large accounts, rebate	n/a	0.00	0.00	0.00	0.00
Small accounts	n/a	0.00	0.00	0.00	0.00
Small accounts, rebate	1.00	0.70	0.49	0.34	0.24
Lost customers	n/a	0.30	0.51	0.66	0.76