Excel Challenge

Challenge 1 Script

First, fill in the coverage column

FSI Insert is “Spot”

National Cinema through Network Radio, Nighttime is “National”

Regional Cinema through Spot Radio, Late/Fringe is “Spot”

We can start by finding our National Universe and DMA Population

Universe is found by the formula Impressions/Ratings *100

National Universe =C3/D3 * 100

DMA Population =C10/D10 * 100

Universe = Imp/Rating*100

Next, Rating in D2, D7, and D12 can be solved with the formula of GRPs/Average Frequency

Now, to fill in the missing rating column values, we will use Impressions/Universe *100

For D5 and D6, National Universe is used

For D10 and D12-15 DMA population is used

Use $ in order to lock the population column and drag the rest of the formula

Now, we have found all of our ratings values

Rating= GRP/Avg Freq

Rating= Imp/Universe*100

Next, we can fill in some GRP values using the Ratings values

For all missing GRP values, Ratings times Average frequency is the formula

We now have all GRP values

GRP= Rating*Avg Freq

Now that we have access to all GRP and Ratings, we can rearrange the previously used

formula

For all missing Average Frequency values, the formula GRPs/Rating will be used

Avg Freq= GRP/Rating

All that is left to fill now is impressions

Impressions is found by using the formula ratings times universe / 100

Use the $ again to lock the universe value and drag the formula

Imp= rating*universe/100

First, We can find the Unit Cost for almost every Media Vehicle by using a rearrangement of the

formula for CPP Unit cost = CPP * Rating

Starting with 60 Minutes, we can input this formula Then, copy and paste

Using a different rearrangement of that same formula we can find the Ratings

Starting with Chicago Fire we can use the formula Rating = Unit Cost / CPP copy and paste

We can find the remaining Rating value by using the formula

Rating = GRP/ #Units

Now using the original arrangement of that formula, starting with NBC we can use

CPP = Unit Cost/ Ratings Then, copy and paste

Now, to find some of the Impressions values,

We can rearrange the formula for CPM Starting at 60 Minutes, we can use the formula

Imp = Unit Cost / CPM Copy and Paste

Starting with NBC we can use

CPM = Unit Cost / Imp

Find GRPS

Start with 60 Minutes

GRP = Total Cost/ CPP Copy and paste

Units = GRP/Rating

Now that all Unit # is filled, we can calculate all total cost

Find the remaining Total Costs

Start at NCIS

Total Cost = GRP * CPP

Total Cost = Cost per unit * Units

GRP = Total Cost/ CPP

Units = GRP/Rating

Country Radio,

Imp = Total Imp / # units

CPM=Unit cost/ Imp

For Supergirl

Cost Per Unit = Total Cost/ Units

CPP = Unit Cost/ Ratings

Imp = Unit Cost / CPM

For all Total Impressions Total Imp = Imp * Units

Challenge 2 Script

First, Fill in all missing impressions values using the formula

Impressions= Clicks/CTR*100

Next, we can find the total cost values using 2 different formulas

First, locate the cells that have the Actions and CPA columns completed

Use the formula

Cost=CPA*Actions to find the total cost for those companies

Next, use a similar formula

Cost=CPC*Clicks in order to solve for the cost of the rest of the companies

Now, solve for clicks using the formula

Clicks= CPC*Cost

For actions, use the formula

Actions = Cost*CPA

Now that we have all of the data values, we can solve for CPA, CPC, and CTR

For CPA use the formula

Cost/Action

For CPC use the formula

Cost/Clicks

Finally, for CTR use the formula

Clicks/Imp *100

Start with the media mix.

Begin with percent syndication for vegetarian butcher

Find the value by the formula

$ spent on medium / total $ spent by brand*100

LOCK COLUMN B ($B6)

drag this formula

Now, apply this formula to other media mix cells

Find missing ad spend report cells with the formula

Start with $ mobile app for Garden Lites

$ spent on medium = media mix * total $ spent by brand / 100

LOCK COLUMN B ($B3)

Totals for share of voice are all 100

Find Morning Star Total $ by reversing the share of spending formula

$ Total Brand = share of spending * total category $

For all other $ brand spent on mediums for morning star

Formula

$ spent on medium = media mix * total $ spent by brand / 100

LOCK B5 - $ total brand spent - ($B$5)

Use the sum feature to get totals

For Totals in media mix

$ total spent on medium / total $ spent by all brands*100

LOCK B8 - $ total spent - ($B$8)

Share of Spending cells formula

Share of spending = $brand spent on medium / $ category spent on medium *100

Start with vegetarian butcher and copy and paste formula

LOCK ROW 8 (B$8)

Copy and paste