How to use Excel Solver to power your budgeting

It’s that time of the year again – time to set budgets and get ready for 2018. Not to mention budgeting for some successful holidays. How do you know what to spend on advertising efforts, and how do you know where to put that spend? This is where the use of Excel Solver can come in handy.

Where to start? Look to the past

First, let’s download the campaigns from the previous year to see how they performed (or from previous quarter, depending on what time frame you’re budgeting for). In this example we’ll be using a mock client that sells all holiday needs in North America. So, first things first, let’s download campaign data with Clicks, Impressions, Conversions, Cost, Revenue and IS (if you are a lead gen client, Revenue is not needed). You should have something that looks like this:

Some businesses have different goals than just direct revenue, which is great! If you develop a point system around different goals such as engaged users, assisted conversions, and regular conversions, you can change the conversion or revenue column to align with your goal and the solver that we’re creating here will work for your model.

Budgeting per location

In this particular situation, we have certain budgets per location – so we want to break those down. It could be by Network (for Search or Display), Product Type, Different Brands or anything else you segment by. These can then be added to your naming convention, followed by your budgeting sheet.

So, from here let’s add a few columns and then use text-to-columns to segment the categories listed within the campaign (Note: you can also use labels on campaigns here).

Gathering data for the solver

Once this is all figured out, it’s time to start working on the data we’ll need for the solver to use. The first column we add is the “Max Cost”, aka, if we wanted to completely max out the spend on a campaign, what would that number look like? In order to get this number, we use Cost/IS. This takes the cost we currently have and adds in the availability of this campaign if we were to raise bids or open budgets.

Next, we add a column called “Allocated Spend” but fill this column with zeros all the way down, as this is the column the solver will be adjusting for us. “Allocated Conversions”, “Allocated Revenue”, “CPC”, “Conv rate”, “Clicks” and “AOV” will be added as well.

Terms To Know:

• CPC = Cost/Click
• Conv Rate = Conversion/Click
• AOV = if(conversions>0,revenue/conversions,0)
• Allocated Clicks = Allocated Spend/CPC
• Allocated Conv = Allocated Clicks*Conv Rates
• Allocated Revenue = Allocated Conv*AOV

Add the totals to the bottom of each of the allocated columns as seen here, by summing up all the columns:

As stated, we want to set budgets for each country separately, so we need to add these columns before setting up the solver. First, just create a table with max and minimum spend:

Then, add the columns with SumIf calculations to find allocated spend per location. From there we’ll look at the potential spend available per location by using the formula seen on the following page:

The $A$4:$A$33 represents the Location column in the campaign lists – and the $J$4:$J$33 represents the Potential Spend column in the campaign data set. Make the change from “United States” to “Canada” and “Mexico” in the two rows below from there to gauge potential spend per location. We can then do the same for Allocated Spend, Allocated Conversions and Allocated Revenue, and then add an ROAS column that is simply Allocated Revenue/Allocated Spend:

Setting up the solver

Once all of this is in place, it’s time to solve your budgeting issues. In this scenario, we’re willing to spend $200,000: $100,000 in the US, $50,000 in CA and $50,000 in MX. Shown here is how you’d use the solver to go about setting budgets on different campaigns with the availability:

The solver setup is a fairly simple setup. The first number at the top of the page is the totals in the revenue column. The objective being to maximize revenue in this scenario. The variable cells includes the allocated spend cells across all of the campaigns, as these are the numbers the solver will be adjusting within the constraints.

The constraints assure that 1) we don’t pace higher than our budgeted amount per locations, 2) we don’t pace lower than our minimum amount per location and 3) the spend amount allocated to each campaign is within the realms of possibilities based on availability of traffic.

Finally, you get your results once you hit solve. This will tell you what your yearly or quarterly budgets and goals should look like per the previous year or quarters statistics at the campaign-level, location level and account-level.

Use Solver to hit ROAS goals

Let’s add one more step here. Let’s say you only want to spend up to $200,000 – but you also only want to spend if you finish within your ROAS goal. In this case, let’s say that goal is a 300% ROAS. All you have to do in this scenario is add one more constraint to the solver showing projected ROAS being equal or greater than 3 as seen in the constraints listed below:

Let’s run this one time and see how our suggestions look at the location-level.

As can be seen in the image, the solver shows that in order to hit the 300% ROAS and max out our revenue we should spend $184,906.80. Once you scroll back up you’ll see how that should be allocated across the different campaigns.

Using Solver for monthly budgeting and adjusting

These solver methods should help with your monthly, quarterly or yearly planning – but what about
mid month shifts? Solver can help you out there as well. We’re just going to have to make some additions to the sheet. First let’s add the Monthly Budget and Day of the Month to the top of the report as seen here:

From there, only Max Cost needs to be adjusted, because we’re now looking at data from past 7 days, but wanted to project out max cost for 24 days. In order to calculate that into the max cost the following formula should help, as long as your “Stats Through” number is updated:

From there the bottom of the portion will have quite a few changes – we’ll start with entering the monthly max using the monthly max at the top of the page and setting some percentages at the location level. Then, subtract the Monthly Max with the current spend in order to get the Max Spend the solver will be based off of. Finally, we added extra columns to show how the per-day trends should be changed for the rest of the month. The final product on the bottom portion should appear as so:

Run the solver and get results!

Once this is all setup – we can run the solver. Below shows the constraints that you could use (this includes a limit to assure you can target a 300% ROAS for the monthly totals):

The sheet here will be built now for you to pull numbers whenever you want to make changes throughout the month. You’d just have to change the number in the “Stats Through” at the top of the page. In conclusion – the Solver tool in Excel is an extremely powerful budgeting tool when it comes to Adwords. You can read through performance and availability side by side in order to develop a sound strategy on where to put your money, whether you’re looking into a fresh year, a fresh quarter, a fresh month or need to make some mid-month adjustments.