Please please help me, running out of time and need some help. Thank you!

Marketing Analytics: Selecting A Communications Channel Instructions: Read the problem below and refer to the spreadsheet provided. The spreadsheet values highlighted in yellow can be manipulated in order to determine possible outcomes and answer the questions. You must answer the follow-up questions (a-e) correctly to receive full credit. Problem: Helen Troy, owner of three Sound Haus stereo equipment stores, is deciding what message channel (advertising medium) to use to promote her newest store. Her current promotion blend includes direct-mail ads that are effective for reaching her current customers. She also has knowledgeable salespeople who work well with consumers once they"re in the store. However, a key objective in opening a new store is to attract new customers. Her best prospects are professionals in the 25 44 age range with incomes over $38,000 a year. But only some of the people in this group are audiophiles who want the topof-the-line brands she carries. Troy has decided to use local advertising to reach new customers. Troy narrowed her choice to two advertising media: Pandora, a music streaming service that offers localized advertising services, and a biweekly magazine that focuses on entertainment in her city. Many of the magazine's readers are out-of-town visitors interested in concerts, plays, and restaurants. They usually buy stereo equipment at home. But the magazine's audience research shows that many local professionals do subscribe to the magazine. Troy doesn't think that the objective can be achieved with a single ad. However, she believes that ads in six issues will generate good local awareness with her target market. In addition, the magazine's color format will let her present the prestige image she wants to convey in an ad. She thinks that will help convert aware prospects to buyers. Specialists at a local advertising agency will prepare a highimpact ad for $2,000, and then Troy will pay for the magazine space. Pandora can target an audience similar to Troy's own target market. She knows repeated ads will be needed to be sure that most of her target audience is exposed to her ads. Troy thinks it will take daily ads for several months to create adequate awareness among her target market. Pandora will provide an announcer and prepare a recording of Troy's ad for a one-time fee of $200. All she has to do is tell them what the message content for the ad should say. d. The agency will not guarantee that its custom-produced Pandora ad will reach Troy's objective-making 80 percent of the prospects aware of the new store. Troy wants to see how lower levels of awareness would affect the advertising cost per buyer and the cost per aware prospect. Use the spreadsheet to model what would happen if awareness was only 70% (assuming the original number of insertions). What would the Pandora cost per buyer be? $8.65 $10.42 $13.68 $14.59 $17.06 e. Continuing the previous exercise, what would the cost per buyer be if awareness was only 50% ? $8.65 $10.42 $13.68 $14.59 $17.06 a. Based on the data displayed on the initial spreadsheet, which message channel (advertising medium) would you recommend to Troy? Pandora Magazine Both have roughly the same cost per buyer b. The agency that offered to prepare Troy's magazine ad will prepare a fully produced Pandora ad-including a musical jingle - for $2,500. The agency claims that its musical ad will have much more impact than the ad Pandora will create. The agency says its ad should produce the same results as the Pandora ad with 20 percent fewer insertions. If the agency claim is correct, would it be wise for Troy to pay the agency to produce the ad? Yes, the agency ad is more cost effective No, the agency ad will be less cost effective The cost per buyer is roughly the same regardless of who makes the ad c. Troy wonders if it would make sense to run the ads longer in order to get even higher awareness. She estimates that the percent of prospects aware can increase from 80% to 95% if she pays for 30% more insertions. Would this be a good decision? Yes, if she uses Pandora Yes, if she uses the magazine Yes, it will be better no matter which option she pursues No, it will be worse no matter which option she pursues Both Pandora and the magazine gave Troy reports summarizing recent audience research. She decides that comparing the two media in a spreadsheet will help her make a better decision. Spreadsheet The spreadsheet values outlined in yellow can be changed in order to determine possible outcomes. You can find the initial values in the corresponding blue cells in columns E and F. Start by entering the initial values into columns B and C. Then review the questions below and adjust the values in columns B and C to determine the correct answers