Cross-selling is a major activity at financial institutions. One regional bank classifies its 100,000 individual customers into 4 groups: basic services (checking, savings accounts), lending (mortgages, loans), investment (mutual funds, bonds), and financial planning (retirement, trusts, comprehensive financial planning). Customers in each of the four categories generate net (of servicing costs) revenue of $100, $200, $300, and $1,000 per year respectively. Currently, the mix of customers is 70%, 10%, 15%, and 5% in the four types. Retention rates are 90% across the board. Retention costs (note: these are different from servicing costs) per account are $80, $120, $150, and $200 respectively per year. In order to improve performance, three major strategies are under consideration.

a. Increase net revenue per customer by 10% across the board

b. Change the customer mix to 60%, 15%, 18%, and 7%

c. Increase retention in one of the groups to 95%

1) What is the CLV in each of the current customer categories? (Assume a 10% discount rate)

2) What would be the increase in the banks weighted average CLV if each of the three suggested strategies were implemented separately? (assuming servicing costs are constant)

Show formulas within excel.