Groovy XM, a satellite radio and streaming services offers three subscription plans. They are the Music for $14 per month, Entertainment for $19 per month, and Platinum $30 per month. They expect that the plans will sell in the mix ratio of 9:5:2 respectively. The variable costs associated with each plan are $4, $6, $9, respectively. Can make an impact in this market they estimate that their promotions will cost $5 million.

a. How many plans of each type do they need to sell in order to breakeven? (calculate using the QBE model) please calculate in excel and please account for future costs.

b. Assume that they could get a total of 1,650,000 customers with the marketing budget mentioned above, and they can achieve retention rates in the three segments of 80%, 73%, and 65%, respectively. Using a monthly discount rate of 4%, please calculate the CLV for each segment. (Hint: M = total contribution for each segment from part a. For simplicity, assume that AC = FC/total number of customers) (Note: Just use the same AC for each segment) Please Calculate in excel showing formula and equation.

c. Suppose that next year they have $3,000,000 in their marketing budget that they can spend on one of the three segments. Help them decide by calculating what the retention rate target should be for each segment if the entire $3,000,000 is spent on that segment.(Hint: Rearrange the formula in terms of r, and calculate the new M. To find the size of each segment, use the sales mix, e.g. Music would be 9/16 of the total number of customers.) Please Calculate in excel showing formula and equation.

d. If they instead spend that $3,000,000 on advertising to 40,000 households in their highest CLV segment, how many new customers would they need to acquire to breakeven? (Hint: Use the CLV from part b). Can you please calculate all values using excel and show work on how results were obtained