ASGN:Goal Seek and Social Media Analytics
Scenario
Walt Disney Resorts & Parks (WDRP) has a new advertisement for broadcast TV and social media. As a new marketing intern at WDRP, it is your job to identify the most popular TV shows and social media sites on which to place the ad. Using the data on the “Prime Broadcast TV” and “Social Content Ratings” tabs of the provided data spreadsheet, answer the following questions:
Questions
Data Orientation
For the first two questions, use the “Prime Broadcast TV” tab of the spreadsheet
(1) For the weeks of 1/22, 1/29, and 7/30, what single TV show should WDRP have advertised on to reach the most viewers?
a. 1/22: <<Type your answer here>>
b. 1/29: <<Type your answer here>>
c. 7/30: <<Type your answer here>>
(2) WDRP may be able to negotiate a discount to advertise on ABC stations (which are also owned by Disney). Filter the Prime Broadcast TV list for just ABC shows (this should result in just 5 rows) and list the top-watched ABC shows.
a. <<Type your answers here>>
Goal Seek
(3) Prepare to run a goal seek by following these directions:
• Copy the 5 rows of ABC shows and paste them into the “Goal Seek” worksheet starting in cell B4.
• Use an appropriate function to calculate the “Total Cost to Advertise (no discount)” for all five shows in cell I10. (Hint: first calculate the “Cost to Advertise” for each show based on “Cost to advertise per Thousand Viewers”)
• Use an appropriate function to calculate the “Total Cost to Advertise (with discount)” in cell I12 (Hint: This should use the discount given in cell K7)
a. <<Insert a screenshot showing the results of you calculations here. This should include columns G, H, and I with rows 3 through 13. >>
(4) Given yearly advertising budget that averages $100,000 per week, use Goal Seek to calculate how big of a discount WDRP needs to ask for to afford to advertise on all 5 of their top 10 shows for these 3 weeks. Take a screenshot of the goal seek pop-up window after you’ve entered your inputs (but before running the goal seek), and paste it into part A. Then run the goal seek and write the result (the required discount) in part B.
a. <<Insert screenshot here>>
b. <<Type your percentage answer (to 2 decimal places) here>>
Social Media
For these next three questions, use the “Social Content Ratings” tab of the spreadsheet
Next, assume that advertisements are interacted with on social media at the same rate as the show on which they air. WDRP wants to get their ad trending on social media, and has data from Nielsen for the weeks of 1/29 and 9/10 (See “Social Content Ratings” tab of the spreadsheet).
(5) If WDRP wanted to only advertise on Disney-owned stations (ABC and the ESPNs), which non-sports-related show should WDRP advertise on to maximize their overall social media interaction? (Hint: Column D indicates which shows are Sports-related)
a. <<Type your answer here>>

(6) What does the answer to question number 5 (above) change to if WDRP wants to maximize their Twitter engagement by purchasing on a single non-sports-related show shown on a Disney-owned station?
a. <<Type your answer here>>

(7) Finally, if WDRP decided they want to maximize their Instagram engagement by purchasing across multiple shows, no matter who owns the network, would they do better by advertising on sports shows, or on non-sports shows? Create a chart or PivotChart showing how much of a difference exists between Instagram engagement on sports shows versus Instagram engagement on non-sports shows, for all networks.
a. <<Type your answer here>>
b. <<Insert your chart here>>