This problem allows you to review and manipulate data to see how resulting calculations affect business decisions. It also illustrates how decisions based on one calculation often feed into additional calculations and decisions.

For this problem, you will use the following to work in the spreadsheet and then answer the questions. Fields highlighted yellow are for entering values as instructed in the following questions. Feel free to click on the Excel Online link now to open the spreadsheet $$ it will open in a separate browser tab. Your work will save automatically in the Microsoft Excel Online spreadsheet.

If you need help navigating Excel or knowing how to complete the tasks related to this problem, please click here to access Microsoft Support.

Open spreadsheet

Scenario

As the marketing analyst for Better Beans Coffee Company, you recently made a recommendation to management about the best two cities for opening new stores. Management accepted your recommendations and is building stores in those cities now. Your next task is to recommend the optimal advertising mix to your promotions team. This is the mix that will be used to promote each new store when it opens. Using historical data, you have already calculated customer acquisition cost for each of the company$$s six advertising channels. After some further calculation, you have estimated that lifetime gross margin per customer averages $$24.$

Your promotions team plans to evenly divide their $$1\mathrm{.}8$ million ad budget for each market only among the ad channels with a positive customer lifetime value $($CLV$).$ They$$ll be closely tracking return on marketing investment $($ROMI$)$ of these campaigns and would like an estimate ahead of time.

Questions

Refer to the Filtering for Positive Customer Lifetime Value $($CLV$)$ data table. Without making any changes to the table, what two advertising channels have positive CLV$?$

Facebook and Google AdWords

Magazines and Google AdWords

Facebook and local radio

Television and YouTube

Television and magazines

After some further analysis, you find that the customer acquisition cost for YouTube is different than you originally calculated. Still referencing the Filtering for Positive Customer Lifetime Value $($CLV$)$ data table, now change the customer acquisition cost of YouTube to $$20.$ How does this change the CLV$?$

CLV remains negative

CLV turns positive

CLV goes to zero $($or reaches the breakeven point$)$

Your promotions team plans to evenly divide their $$1,800,000$ ad budget for each market only among the ad channels with a positive customer lifetime value $($CLV$).$ Refer to the Filtering for Positive CLV data table and identify the ad channels you found to have positive CLV $($after updating the YouTube customer acquisition cost to $$20).$ Divide the $$1,800,000$ ad budget evenly between the ad channels with positive CLV $$ input the values in the Ad Budget Allocation data table. Now that you have allocated the advertising budget equally between the ad channels with positive CLV$,$ what is the average return on marketing investment $($ROMI$)$ for the $$1,800,000$ ad campaign in each city?

$6\%$

$24\%$

$33\%$

$40\%$

$60\%$