Homework #6 – TV Ratings
You are an analyst at a company that advertises on network TV. You want to explore the trends in network programming on the 5 major broadcast networks over the past few years and prepare an advertising budget for the current year. You have created a dataset that contains the shows that were on the air, their ratings and their viewership and a few other categories of information.
As you prepare your analysis, remember that you will be presenting this information to your boss, so make an effort to present the information clearly and concisely. You will be graded on ease of interpretation.
Use what you’ve learned and think about things like:
fonts and color coding,
formatting of numerical values,
column sizing,
arrangement of information in your sheet,
which totals / subtotals are appropriate to show or not
hiding / displaying field headers and +/- buttons in pivot tables
sizing and labeling charts clearly, etc.
Using Pivot Tables, sort and summarize this information in order to identify trends and get a perspective on each of the following – do each part (a-c) on a separate sheet / pivot table:
You first want to understand how the networks have performed as compared to each other in terms of both average ratings and average viewership across all of their shows and across all years in the dataset. (HINT: do not include the titles of the shows or individual years in the pivot table).
Show the networks (rows) in descending order based on average viewership (values) and also include the average ratings (values).
Prepare a Pivot Chart that displays this information. (HINT: a dual vertical axis chart will probably work best due to the difference in the range of values for each metric).
Now you are interested to see what sort of track record each network has in terms of developing new shows by looking at how many shows they’ve developed in the last 3 seasons that have been “cancelled in the first season” (status). (HINT: only include information for shows that have this status).
Include the network and show titles (rows – in this order), and count of the shows (values) for each year individually (columns).
Now let’s look at some specific shows and see how they are performing in terms of overall viewership.
We are only interested in shows that are active and are scripted (filters).
Display the viewership for each season (columns) for each network and show (rows) that meet the above criteria.
We want to create calculations that show the percentage change in viewership for each year for each show. (HINT: Add a second viewership selection in the Values section of the Field Settings, then in Value Field Setting select Show Values as “% difference from” . . . Season . . . previous.)
Because this isn’t a complete list of all shows or all years, we do not want to show sub-totals or grand totals on the table.
Place the % change columns together in the right hand columns by moving the ∑ Values item first in the column field. Center the % change information in their columns.
Change the column labels for the second viewership field to “% change vs. prior year”.
You have likely gotten some errors in the % change calculation where there are no values for the prior year. Clean these up – use PT Options (under PT Analyze) and indicate for it to show NA instead of the error message.
[PT Analyze – PT Options (far left on ribbon) – Layout and Format – check “for error values show”, then fill NA in the box behind it].
Now let’s highlight those years where the viewership is increasing (green) vs. decreasing (red). Think conditional formatting of the percentage change columns here. “NA” indicators should not be highlighted at all. You will have 3 conditional format options applied (red for decreasing, green for increasing, white background formatting when the cell value is equal to NA).
What sorts of trends do you see in the viewership of active shows? Are people watching fewer shows? Are more options available?
Let’s now turn our attention to developing an advertising rate card and budget. You have learned that the advertising costs are based on the ratings according to the schedule below. These costs reflect the price of one 30 second commercial.
| Over 0.9 | $ 400,000 |
| 0.8 – .89 | 350,000 |
| 0.7 – .79 | 300,000 |
| 0.6 – .69 | 250,000 |
| 0.5 – .59 | 200,000 |
| 0.4 – .49 | 175,000 |
| 0.3 – .39 | 150,000 |
| 0.2 – .29 | 125,000 |
| 0.1 – .19 | 100,000 |
| Below .1 | 80,000 |
On a new sheet – prepare a rate schedule that displays the advertising cost for each show that is on the schedule for next year (status = active & new shows) based on its most recent rating. (Some current season ratings aren’t available).
If the show has a rating in the current season, use that rating
If the show doesn’t have a current season rating, use the prior season rating.
HINT: For this step prepare a Pivot Table to isolate the ratings for each show for 2021/2022 and 2020/2021 (you don’t need to include the network information). You can then copy this information into another sheet [Copy – Paste Values]. Embed VLOOKUPs in an IF Statement to identify the cost to advertise on each show. This is your rate schedule.
Your market research department has compared the demographics of the 2021/2022 shows and has determined that the following shows are optimal selections for reaching your target audience. This step can be done on the same sheet as #2.
| A Million Little Things |
| Blue Bloods |
| Dancing with the Stars |
| Grey’s Anatomy |
| Chicago Med |
| Shark Tank |
| Station 19 |
| The Good Doctor |
| FBI |
| 60 Minutes |
List each of these shows, then using the rate schedule that you prepared in Step #2 as your lookup range, lookup the show title and return its advertising cost. Determine what the total cost will be for the current year if you advertise on each of these shows for 13 weeks. Assume each show will air once per week. Don’t forget to calculate the total cost.
