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