The data below show the results of a Starbucks segmentation study. Each column shows the values for a typical customer in each class (constant over the lifetime of the customer). The annual retention probability applies only after the first year. That is, no one defects until after a full year of patronizing Starbucks.

Visits/Month:

• Silver: 3.9
• Gold: 5.1
• Platinum: 7.2

Revenue per visit:

• Silver: $3.88
• Gold: $4.06
• Platinum: $4.62

Annual Retention Probability:

• Silver: 0.30
• Gold: 0.55
• Platinum: .070

a. Assume that Starbucks' gross margin is 40% and that its (one-time) customer acquisition cost is $100. Further assume that Starbucks uses a discount rate of 10%. What is the 5-year individual customer CLV for each class of customer? Show your work on an Excel spreadsheet.

b.Suppose Starbucks is considering an investment of $40 million in their 3500 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.) Show your work on an Excel spreadsheet