The Problem: Busy Media Planners on entertainment accounts were spending excessive amounts of time to calculate weekly spends of advertising campaigns leading to delays in deliverables and frustration from clients
The Solution: Created a calculator template that automatically performed media math functions allowing Media Planners to simply input provided spends by media type.
BACKGROUND:
As part of client requests, Media Planners would need to submit a document detailing how much was being spent each week of a media campaign. The process involved taking spending information from media schedules and plans and inputting into a monthly deliverable. Unfortunately, the task was not a straightforward “Copy & Paste” job as certain media math principles needed to be applied depending on the media type and how it was purchased.
Gross = Vendor Net/0.85
Client Net = (0.85 + % of commission fee) x Gross
Certain media types such as Television were represented in Gross Spend on their buys while other media types such as Digital were represented in Vendor Net Spend. The final deliverable had to be in Client Net.
A user flow of the entire process of the exercise
Potential for problems:
“Having to convert from gross to net takes a lot of time and sometimes I forget to net the number down and have to redo them”
“There should be a way we can do this automatically, I’m scared of messing one of the schedules up when I’m doing my calculations on the side”
“When I get the document, I’m not sure how the planner arrives at the numbers so I basically redo all the calculations which takes even more time”
“Everyone approaches it a bit differently, so when I check someone’s work I have to figure out how they did it. I’m surprised there isn’t a standard method”
IDEATION & SOLUTION:
It became apparent that the inconsistency in calculation methods was the main driver in dragging out the process. Since each media planner had a different approach, the manager in charge of the double-check would essentially redo the math to ensure all calculations were correct. The constant rehashing of the media math delayed the submission to the client leading to more frustration. Furthermore, the additional time spent on this deliverable took time away from other deliverables.
Recognizing the need for a standardized approach as well as a way to show the work, I began work on an excel sheet to help managers see the mathematical approach and also verify all bottom line totals were correct when netted down.
THE CALCULATOR TEMPLATE:
The calculator template was created in order to streamline the process and alleviate the process of calculation errors, time-intensive labor, redundant work, and inconsistent approaches. The template was built within Microsoft Excel and would convert Gross or Vendor Net to Client Net where needed and would total the media types at the bottom making this task an easy “Copy & Paste” job.
THE NEW PROCESS:
Several steps were eliminated allowing for faster completion time
Feedback:
“Love it! I like that I can see the gross and the net numbers so if it looks off I can immediately tell without having to redo all the math again.”
“This helps me so much! It also saves me a lot of time so I can focus on other deliverables that we need to get out regularly.”
Iterations:
Made general updates to formulas and decreased formula complexity to allow for faster calculations
Updated layout and input methodology as campaign schedules changed format
Conclusion:
After showing the template to planners and managers and demonstrating its efficiency, the document was integrated as a standard tool within the team. In general, the average task completion time was reduced by about half. The template was later found to be useful for other processes and exercises. Ultimately, I learned it is better to take initiative to address process problems than to idly continue with inefficient practices.