Suppose Starbucks is considering an investment of $40 million in their 4,500 company-owned North American stores to improve service quality, and thereby, customer retention. How many customers per store would have to be "converted" from Gold to Platinum for the investment to achieve breakeven after five years? (For simplicity, assume all customer "conversions" apply throughout the entire five-year period.)

Objective: Calculate the break-even point in part B using the following formula:

investment amount / (CLV of gold - CLV of platinum)

**The image is clearly visible when using a laptop or iPad screen only**

X V B C D E F G H Starbucks Question Part A: Customer Category Silver Gold Platinum Customer Category Silver Gold Platinum Visits per month 2.6 3.9 7.3 Visits per month 2.6 3.9 7.3 Visits per year =C5*12 =D5*12 =E5*12 Visits per year 31.2 46.8 87.6 Revenue per visit 3.25 3.76 4.29 Revenue per visit 3.25 3.76 4.29 Annual retention probability 0.3 0.5 0.75 Annual retention probability 0.3 0.5 0.75 Expected revenue in year 1 =C6*C7 =D6*D7 =E6*E7 Expected revenue in year 1 101.4 175.968 375.804 Expected revenue in year 2 =C10*C8 =D10*D8 =[10*E8 Expected revenue in year 2 30.42 87.984 281.853 Expected revenue in year 3 =C11*C8 =D11*D8 =E11*E8 Expected revenue in year 3 9.126 43.992 211.38975 Expected revenue in year 4 -C12*C8 =D12*D8 =E12*E8 Expected revenue in year 4 2.7378 21.996 158.5423125 Expected revenue in year 5 =C13*C8 =D13*D8 =E13*E8 Expected revenue in year 5 0.82134 10.998 118.906734375 Expected profit from customer in year 1 =C10*0.45 =D10*0.45 =E10*0.45 Expected profit from customer in year 1 45.63 79.1856 169.1118 Expected profit from customer in year 2 =C11*0.45 =D11*0.45 =E11*0.45 Expected profit from customer in year 2 13.689 39.5928 126.83385 Expected profit from customer in year 3 =C12*0.45 =D12*0.45 =E12*0.45 Expected profit from customer in year 3 4.1067 19.7964 95.1253875 Expected profit from customer in year 4 =C13*0.45 =D13*0.45 =E13*0.45 Expected profit from customer in year 4 1.23201 9.8982 71.344040625 Expected profit from customer in year 5 =C14*0.45 =D14*0.45 =E14*0.45 Expected profit from customer in year 5 0.369603 4.9491 53.50803046875 PV of profit =NPV(10%,C16:C20) =NPV(10%,D16:D20) =NPV(10%,E16:E20) PV of profit 56.9514396060875 129.415165233373 411.981691986544 Less: Acquisition cost 10 10 10 Less: Acquisition cost 10 10 10 CLV: =C21-C23 =D21-D23 =E21-E23 CLV: 46.9514396060875 119.415165233373 401.981691986544 Part B: Investment: 40000000 # of stores: 4500 CLV of gold: =D25 CLV of platinum: =E25 BEP = Investment / (CLV of gold - CLV of platinum) =C28/(C30-C31) Approximately 31 customers per store How many customers per store would have would have to get to be "converted" from gold to platinum? =C33/4500 "converted"