Excel Solver is a fantastic tool, but most examples only cover simple marketing use cases. In this post, we’ll cover the next steps for enhancing your models and how to further automate the process for multiple runs.
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 setup.
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 premise here. Feel free to skim those to build a stronger foundation if you feel the need or get stuck along the way.
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 enhancing our constraints setup, 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 without doing all the setup and execution ourselves.
Dealing With Constraints
Budgets are not exclusively based on performance. Product launches, platform allocation minimums, and other variables will control how much you can spend in a given area. The examples in previous posts created an optimized budget for total spend. We can extend this with additional solver requirements that account for marketing needs and limits outside of total spend.
The following example will factor in campaign type and geographic region. 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. Our budget leaves some flexibility for performance by using both a minimum and maximum spend for each group.
After creating the table outline, we need to fill in the numbers. The minimum and maximums can be typed in directly and are completely your choice. Once we have our chosen constraints, we’ll need to build a few more to support our model.
Populating The Data
We can utilize a simple sumIf() formula to calculate the sums of campaign metrics in 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 tool set up and add these additional limits as constraints. Allocated spend should be greater than minimum spend but less than maximum spend or max potential spend.
At this point, we’re done with the setup! You can now analyze different scenarios with minimum and maximum budgets or add additional optimizations metrics as needed. Perhaps you want to weigh assisted conversions or the estimated effect of a brand lift. Or you could add bid increase estimates to factor in lost impression share to rank.
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
Running a model multiple times is tedious without automation. Thankfully, creating a Solver macro is one of the easier automation tasks. You can even use the macro recorder so there is almost no coding knowledge needed.
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 your own solver setups.
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 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.
The Logic
We need to run the solver for each value, select the output, and paste into the appropriate cell. Our table starts at $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.
Looping
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 afterward. The syntax is simple.
For i = start_value to end_value
Solver Code to run (What you recorded)
Next i
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 move through a series of cells to update our model.
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 cell ranges, 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 copy the conversion totals and record the results. We will place them in column C, right next to our spend targets.
We’ll use 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. If we skip this the results will be overwritten each run and we’ll only have the solved conversions for the last spend input.
To avoid any potential issues with lingering data, we want to reset the Solver each time it runs. Insert SolverReset at the start of the loop to clear the old solution and start fresh with the new data each time.
Run It
You can now run the macro to solve across a range of budgets and see how it impacts your campaigns.
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 incurring additional effort.
Original post date: 07/18/16
