In theory, optimisation is pretty simple: you maximise or minimise a specific value. For example, marketers could try to maximise conversion rates or minimise the cost per ad impression. Optimising the performance of one type of media is still relatively easy, but it is much more challenging to optimise a media plan that is based on several different media and their various constraints.
In the following example, I demonstrate how you can use Solver to tackle a situation that many marketers may face.
Your budget is €100,000 and your task is to create a media plan that provides the best-possible results using ten different types of media. (I use a rather simplistic example, for ease of explanation.)
I assume that you are already familiar with the costs of using different media, and their performance. In my example, the media costs, conversion rates and maximum number of impressions are fictitious.
First, create a table and fill this with information on various media, and on what parameters and constraints you would like to take into account. This is usually the most tedious part of the task, since precise data are not available, and you need to consider the constraints of different media.
Once you have filled in all the necessary information, we can start using Solver. The default is that you cannot see Solver on your tool bar, but you can enable it in ‘Excel Options’, as follows:
1. Click the Microsoft Office Button, and then click Excel Options.
2. Click Add-Ins, and then, in the Manage box, select Excel Add-ins.
3. Click Go.
4. In the Add-Ins available box, select the Solver Add-in check box, and then click OK.
5. After you load the Solver Add-in, Solver is available in the Analysis group on the Data tab.
There are three different solving methods available in Solver: Simplex LP, Evolutionary and GRC Nonlinear. The Simplex LP method works well in situations where you are solving linear problems. The Evolutionary and GRC Nonlinear methods are good for nonlinear problems. So, with Simplex LP, you can solve situations where you are using +/− signs, SUM and AVERAGE formulas, and the SUMPRODUCT formula, where only one range contains decisions. As our example is linear, we are using the Simplex LP method to solve it. It is also the fastest solving method in Excel.
In our media plan, we want to maximise results. Therefore, we are maximising the value in cell N13, and changing the values of selected impressions of different media in cells D6 to M6. The constraints are that the selected number of impressions cannot be higher than the listed maximum impressions in the media, and the budget is the same as the total cost of the media plan.
When you have filled in the constraints for Solver, Excel will produce for you the best-possible media plan.
If your constraints are a little more complicated – for example, you would like to use the IF-function to optimise your media plan, then the Evolutionary solving method can solve it. With the Evolutionary Solver, your formula choices expand to just about anything you wish, including these useful nonlinear functions:
• logical checks: IF, COUNTIF and SUMIF
• statistical functions: MIN, MAX, MEDIAN, LARGE, NORMDIST, BINOMDIST and so on
• lookup functions: VLOOKUP, HLOOKUP, OFFSET, MATCH and INDEX.
Using the Evolutionary method, we could, for example, take into account the costs of producing ads for different media based on whether Solver decides to use that media.
As a solving method, Evolutionary is a little different than Simplex LP. The latter does not necessarily always provide the same solution; it will provide the best solution that it finds in the time available, as defined in the Solver options.
With Excel Solver, you could solve problems with as many as 100 to 200 decision variables and constraints. OpenSolver (http://opensolver.org/) is a good option if you need a more heavy-weight solving tool.
31 Oct 2016