File Home Insert Formulas Data Review View Help Tell me what you want to do Comments Cut Calibri 12 AA == 2 Wrap Text General LE ro Paste Copy Format Painter BLU Dab A Merge & Center $% 958 98 Insert Delete farmat Conditional Format Cell Formatting Table Styles Tables > Autosum Clear Sart & Find & Filter Select Editing Undo Clipboard Font Alignment Number Cells J6 H 1 K L M N 0 28 $ A B C D E F F 1 FILTERING FOR POSITIVE CUSTOMER LIFETIME VALUE (CLV) Advertising Lifetime Gross Customer Customer Channel Margin Per Acquisition Lifetime 2 Customer Cost Value (CLV) 3 Television $ 28 $ 30$ (2) 4 Magazines $ 28 $ 40$ (12) 5 Facebook $ 28 $ 15$ 13 6 YouTube $ 28 $ 30$ (2) 7 Google AdWords $ 35$ (7) 8 Local Radio $ 28 $ 25$ 3 9 10 AD BUDGET ALLOCATION (Note: The ad budget divided equally between channels with positive CLV) Advertising Allocation of Channel the Ad Budget 11 12 Television 13 Magazines 14 Facebook 15 YouTube 16 Google AdWords 17 Local Radio 12 TOTAI A CLV + $ X Excel template - Saved File Home Insert Formulas Data Review View Help Tell me what you want to do Comments Calibri - 12 AA == 29 Wrap Text ILX AutoSum 27 o MO X Cut [3 Format Painter Ciphon General $ - % 848 Paste BIV Dab Morge & Center A Insert Delete Format Conditional Format Cell Harmatting as Table Styles Clear Sort & Find & Filter Select Fant Alienment Number J6 A H I ) K L M N 0 A B C D E F TU U DUDOLI ALLUCATIVI JOCTC au wuugel LIVIUCU Equally WCUWECILIACIS WICHT POSILIVELLY Advertising Allocation of Channel the Ad Budget 11 12 Television 13 Magazines 14 Facebook 15 YouTube 16 Google AdWords 17 Local Radio 16 TOTAL $ 19 20 RETURN ON MARKETING INVESTMENT (ROMI) Advertising Attributable Campaign ROMI 21 Channel Revenue Cost 22 Television $ $ 0 23 Magazines $ - $ $ 0 24 Facebook $ - $ 0 25 YouTube $ - $ 0 26 Google AdWords $ $ 0 27 Local Radio $ $ 0 28 Average ROMI of Campaign 0 - Instructions 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 $28. Your promotions team plans to evenly divide their $1.5 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 1. 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 local radio O Magazines and Google AdWords Television and YouTube O Television and magazines O Facebook and Google AdWords .IL --I-... Lille tracking return on marketing investment (ROMI) of these campaigns and would like an estimate ahead of time. Questions 1. 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 local radio O Magazines and Google AdWords O Television and YouTube Television and magazines O Facebook and Google AdWords 2. 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 $25. How does this change the CLV? OCLV goes to zero (or reaches the breakeven point) CLV turns positive OCLV remains negative 3. Your promotions team plans to evenly divide their $1,500,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 $25). Divide the $1,500,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,500,000 ad campaign in each city? 09% O 26% O 37% 49% O 87%