Scenario Modeling & Optimization

It truly is amazing what can be done with skilled Excel programming. As in: saving hundreds of thousands of dollars without compromising the deliverable. With a math problem this complex and big media contracts on the line, gut feelings and educated guesses just don’t cut it.

Crystal Ball, Or Monte Carlo Simulation

A mid sized media company had a hunch that they could save a bundle by purchasing cheap, national ads instead of higher priced ads targeted to a DMA.

Each ad purchased is either targeted or national, and is bought through a different channel (entertainment, finance, sports, travel, and more) each at a different national and targeted price. The media company is responsible for delivering a minimum number of impressions per DMA and per channel.  With their ad serving technology, the media company can tell where nationally bought ads are served, and back fill unused inventory at a loss. The amount of the loss for any back filled ad depends on the channel and if the inventory is targeted or not.

Even with the belief that an opportunity existed to increase margins by buying at least some inventory nationally instead of buying to match the delivery requirements exactly, the optimization challenge seemed insurmountable. How much inventory should the company buy, nationally and targeted, across each channel, to fulfill the delivery requirements and maximize profits? How much leftover inventory would be expected in an optimal solution? There are many variables to consider. Getting it wrong meant losing the client and losing the company’s proverbial shirt.

Keep Your Shirt On

Slate Analytics developed a Monte Carlo Simulation in Excel to model the costs of ad inventory across channels and DMAs, determine what percentage of nationally bought inventory could be distributed within each DMA, and integrate the requirements of any client’s media buy. The model, powered by Visual Basic for Applications (VBA), would consider the interactions of thousands of combinations of various media plans and determine an optimal solution. By overhauling their buying strategy and deliberately introducing delivery inefficiency, the media company could actually save between 15% and 50% of the cost of a given media plan.

With media contracts for hundreds of thousands of dollars, the opportunity was truly amazing. The company could plug in the client’s delivery requirements and prices quoted by the media channels, begin the simulation, and have a solution for exactly what to buy before returning from the water cooler.