1. Determine which Media Categories you want to use:
Traditional
- National television
- National sports
- National cable television
- National radio
- National magazines
- National newspapers
- Internet
- Spot television
- Spot cable television
- Spot radio
- Local magazines
- Local outdoor
- Direct mail
- Impact sports
- Impact specials
- Banner Ads
- Miscellaneous
Impact
- Interactive
- Local print
- Outdoor
- Product placement
Digital
- Banner ads
- Social media
- Streaming video
- Streaming audio
- Search Engine Marketing (SEM)
- Search Engine Optimization (SEM)
- Mobile advertising
2. Determine the specific media vehicles you want use within each category (see Simmons data)
3. Make rough dollar allocation decisions (percentages) by media category based on a solid rationale, first based on CPP allocation, and then for fixed cost
4. Create a media planning spreadsheet to fine tune your budget allocations
Steps in creating a media planning spreadsheet
1. Start with a total budget entry, usually on the upper left of your spreadsheet. This should be the total budget of your campaign, as you are allocating the whole budget to your media buy. Confirm that this is correct.
2. Subtract any amount you are holding back for contingencies (e.g., crisis situations).
3. Start with the IMPACT MEDIA section, the first section of the spreadsheet.
- List all the media categories of your impact media in the italicized media categories column.
- Add any extra row lines for additional media categories you want to include. Do not forget to copy the formulas over.
- Fill out how many units per media category you would like to purchase under the “unit alloc.” column.
- Fill out the cost per unit of each media category, based on the media prices provided
- You not need to fill the “allocations” column. Excel should calculate this for you.
- Fill out the notes with any relevant information. For example, if you are purchasing 12 units of billboard space, will you have one billboard displayed all year, or three billboards displayed over four months?
4. Next, we will move onto the DIGITAL MEDIA, FIXED COST section.
- Repeat steps 3a to 3f, but using digital media purchases. You do not need to fill the “allocations” row.
5. Third, we will move to the DIGITAL MEDIA, CPM COST section
- Add any extra row lines for additional media categories. Do not forget to copy the formula over.
- Fill out how many units per media category you would like to purchase.
- Fill out the CPM (cost per mille), based on the media prices provided to you.
- Fill out the number of impressions you want. This should be the number of people you want to see your digital advertisement, in increments of 1,000. You can calculate the number of impression for a digital media outlet based on the projected weighted (000) projection on Simmons.
- You do not need to fill the allocations column. Excel should calculate this for you.
- Fill out the notes section with any relevant information. For digital media, this will likely be the site or app that you are placing the advertisement in, as well as where you are allocating your units.
- At this point, you should check the “remainder” box in the top right of the spreadsheet. This number must be below your total budget allocation. Because you are expected to use all of your money, all of your remainder money will be spent on traditional media.
7. Finally, we will move to TRADITIONAL MEDIA, the last section of the spreadsheet.
- Add or change any rows you need. Do not forget to copy the formula over.
- Fill out the CPP of each media vehicle, based on the media prices provided to you.
- Fill out the “% allocation.” The cumulative total of the column of “% allocation” (highlighted yellow in the spreadsheet) should be 100.
- You do not need to fill the “allocations” or “GRPS” columns. Excel should calculate this for you.
8. Check the pie chart at the bottom of the spreadsheet. If you added rows to the traditional media section, you may have to add additional rows here as well. All of the calculations for this pie chart should be automatic, based on the information you have already provided.
9. Calculate the sum total for each section. You should have four sum totals in all.
10. Adjust your media plan as necessary. Do not forget that you will have to summarize and provide rationales for all the decisions you have made in your media spreadsheet. When you submit your media plan, include your spreadsheet.
Protip
Make sure that you have media buys for all the creative pieces you are producing. You should not have a radio script if you have no radio advertising buy. Similarly, if you buy lots of billboards, we will expect a mock-up of at least one in your creative plan.