July 18, 2016
Excel Solver is a fantastic tool, but most examples only cover the simple marketing use cases. Leaving the advanced usages to your imagination with no clear path forward. In this post, we’ll cover the next steps of enhancing your models and then cover how to automate the process.
The post focuses on the more technical aspects of Excel but will remain fully accessible. We’ll skip some of the finer details to better establish why and how to set up. At the very least you’ll build a strong grasp of the reasoning and initial set up. It may even lead you down the rabbit hole if you are so inclined.
This read will be a decent length journey so don’t forget to bring a towel.
Basics And A Refresher
Are you comfortable with solving for a new budget allocation based on a max spend? If not, you can check out two previous posts that cover reallocation during budget cuts.
We’ll cover the same initial ground and you may feel more comfortable after reading or watching these articles and videos.
The Next Steps
In the next two sections, we’ll cover how to include additional constraints as well as a simple way to automate the Solver. By upgrading our constraints, we can create better models and in turn create better solutions. Similarly, by automating the Solver, we can take advantage of the tool’s power to solve multiple problems for multiple scenarios.
Dealing With Constraints
Budgets are not always exclusively based on performance. Product launches, platform allocation minimums, and other variables will control how much you can spend in a given area. While these simple examples create an optimized budget for total spend, we can extend this thinking with additional solver requirements.
Setting up a simple table with a few of our favorite formulas will allow us to easily adjust those constraints and modify our output based on changing inputs. For this example, we will work with a selection of campaigns targeting Search and Display as well as the US and Canada.
Setting Up The Limits
To get started we can set up the Solver just as we would in the basic example. The only difference is a new set of tables to handle our new limits.
When building the table, we need to decide which buckets and metrics matter. In this case, we must allocate based on platform and country. We then need a minimum and maximum spend for each group.
After establishing the outline, we need to fill in the numbers. The minimum and maximums can be typed in directly and are completely your choice. But we need a limiter too, something dynamic so we don’t have to readjust it ourselves.
Populating The Data
We can utilize a simple sumIf() formula to calculate the sums of each group. While the Solver is running, these cells will update and be compared to our minimum or maximums. Once you have your sumifs() created, go back to the Solver set up and put in the additional limits. Notably allocated spend should be greater than minimum spend but less that maximum spend or max potential spend.
At this point, we’re done. You can try different scenarios with minimum and maximum budgets or add additional factors. Perhaps you want to weigh assisted conversions or the estimated effect of a brand lift. You could even dive deeper down to the ad group level for insights on segmentation and performance restrictions for top performers.
These additional fields and options greatly increase the power of the solver but what if you want to solve a problem multiple times?
Using Macros To Find Multiple Solutions
The slowest part of the Solver is the setup and then the tedium of the execution. Lucky for us, creating a Solver macro is one of the easiest things you can do.
You can even use the macro recorder! If you’ve used it before you may have noticed the mess of code it spits out. This can be discouraging to say the least. In the case of Solver, the recorded code is clear, concise, and easily modified.
There are two ways to approach this section. You may follow along step by step with the data you used in the first example. This is the easiest approach as we’ll use a simple example for illustrative purposes. The second is to follow along with the instructions but use them to extend one of you own solver set ups.
Record the Macro
Simple enough. Record the macro! Open the macro for editing and we’ll have a few more steps.
Adding A Few Minor Touches
First, we want to make sure the plugin is available. If you don’t do this step you may run into errors when you change the parameters.
Next, we need to add an additional two lines of the code. This line will act as the user hitting OK on the menus. Insert SolverSolve userFinish:= True after SolverSolve and then insert SolverFinish KeepFinal:=1 after that.
These will remove the pop-up confirmations and speed up the runtime. You don’t want to have to click OK each time for 20 consecutive runs.
Let’s Run Multiple Times
We could be content with the process so far. With the macro in place, we can change any parameter we want and solve again at the push of a button. It sounds minor but it’s enormously convenient compared to going through the ribbon.
However, it is still too much clicking if we need multiple solutions. Lucky again, VBA makes it simple to run code multiple times. We’ll use a for loop. Without diving into too much detail, the loop runs the contained code until it hits a defined limit.
Let’s say we want to go back to our previous example but solve the budget at $5,000 intervals. How many conversions can you expect at each level and how does it impact the CPA? First, we need to create a simple table to hold the output of each solution. Create a column of budget totals. These numbers can be whatever you want and the macro will be solving for these specific numbers.
The extra columns aren’t necessary but I’d advise using them. These will give you a grasp of how performance changes and at what rates. You may set these up before or after running the solver.
Now that the setup is out of the way, let’s establish the logic and create the loop.
We need to run the solver for each value, select the output, and paste into the appropriate cell. Our table starts a $B$18 and we can use that as a reference point. To access the next cell by offsetting by an additional row each time. The first solution will be based on $B$18, the next solution will be based on the row below it, and the second solution will be based on the value two rows below it and so on.
Along the way, we’ll need to collect the Solver output and paste it in a more permanent location.
A VBA loop has a start and stop point. There are seven values we need to solve for which means we need to run the code seven times.
We’ll start at zero, which may seem counterintuitive but we don’t want to offset any cells on the first run.
We’ll start the loop above the Solver code and end the loop afterwards. The syntax is simple.
For i = start_value to end_value
Solver Code to run (What you recorded)
Every time the code runs it’ll increment i up to the next value, up until it hits 6 and the code stops.
Changing The Cell References While Looping
When you initially configure the Solver, it targets specific cells. Now that we are looping we want to change our cell references. If we don’t we’ll just get the same answer six times.
To do this we select our initial cell $B$18 and offset by one row each time it runs, conveniently equal to i. If you look at the code below you’ll notice the parameters established in SolverAdd. There is the initial cells, the type of comparison, and FormulaText:= for the constraints. Right now we have it set up so that $K$10 must be less than or equal to (Relation:=1), to $B$18.
We’ll now change the reference to $B$18 to reference the offset cell instead. Similarly, to the worksheet formula we’ll use .Offset(0,i).Address to reference the location of the desired cell.
Now we need to include one more step to collect the conversion totals for column C. We’ll take the same approach, starting with $C$18 and offsetting by one each time. The only difference is that this time, we’ll set the value equal to the value of the solved conversions.
Once that line is placed we are almost ready to go.
To avoid any potential issues with lingering data, we want to reset the Solver each time it runs. Simply insert SolverReset at the start of the loop to clear the old solution and the code below will take care of the setup and execution.
Run the macro!
You now have a better Solver and your own macro. You can apply these techniques together or separately to your own accounts. It may seem like a lot to handle initially but once you have a firm grasp of the basics you can be increasingly creative with your solutions without much else in the way of additional effort.