Using the Excel Solver To

Optimize Your Marketing

Budget

The Excel Solver allows you to calculate how to allocate your resources to get an optimal result. It is such a useful and versatile tool that almost any manager can put it use effectively but not many are familiar with how to use it.

This article will provide a simple example of how an Internet marketing manager can use the Excel Solver to optimize the allocation of marketing budget funds among 4 pay-per-click networks to achieve the highest number of conversions (sales).

Solver Problem Summary

In summary, here’s what we are trying to do:

1) Maximize the total number of conversions from all 4 pay-per-click vendors.

2) Not spend more than $500 on each individual pay-per-click provider

3) Not spend more than $1,500 total

We are also provided with the average cost per click and average conversion rate (percentage of clicks that convert to sales) for each network. The information is placed on a spreadsheet as follows:

Click On the Image To See a Larger Version

Our Solver Optimization Task

We have a total monthly paid search budget of $1,500 that must be allocated between the 4 following paid search providers: Google AdWords Search Network, Google AdWords Content, Facebook paid search, and LinkedIn paid search. We want to keep our pay-per-click efforts diversified; we will spend no more than $500 per month any single pay-per-click providers, but no more than $1500 total.

We know our average cost-per-click and conversion rate for each of the 4 pay-per-click vendors. Conversion rate is the percentage of clicks that convert (buy and become customers).

Running the Problem on the Excel Solver

Once the Solver has been installed as an add-in (To add-in Solver: File / Options / Add-Ins / Manage / Excel Add-Ins / Go / Solver Add-In), you can access the Solver in Excel 2010 by: Data / Solver.

The following blank Solver dialogue box appears:

Click on Image To See Larger Version

The Solver dialogue box has the following 5 parameters that need to be set:

1) The Objective Cell – The is the target cell that we are either trying to maximize, minimize, or achieve a certain value.

2) Whether we want to minimize or maximize the target, or attempt to achieve a certain value in the Objective cell.

3) Decision Variables – A set of variables that will be changed by the Excel Solver in order to optimize the target cell.

4) Constraints – These are the limitations that the problem subjects the Solver to during its calculations

5) Solving Method to be used.
Here once again is the Excel spreadsheet input data that will be used:

Click On Image To See Larger Version

Objective:

Maximize the total number of conversions from all 4 pay-per-click vendors (green cell H10)

Decision Variables:

By varying the budget amounts allocated to each of the 4 pay-per-click networks (tan cells – C6 to F6)

Objective and Decision Variables Selected in Solver

Click on Image To See Larger Version

Choice of Solver Method

Click on Image To See Larger Version

a1x1 + a2x2 + … + anxn = objective

We can tell that we are dealing entirely with linear equations in this example because all equations which used to determine the objective (the final number of conversions) are first-order. These basic equations are as follows:

Number of Clicks = (Average Cost Per Click) * (Amount Spent)

Number of Conversions = (Average Conversion Rate) * (Number of Clicks)

The GRG Nonlinear method is used when the equation producing the objective is not linear but is smooth (continuous). Examples of smooth nonlinear functions are:

=1/C1, =Log(C1), and =C1^2

These functions have graphs that are curved (nonlinear), but have no breaks (smooth)

The Evolutionary method is used for non-smooth functions. These are functions whose graph is discontinuous at any point.

The use of the GRG Nonlinear and Evolutionary methods would require much more discussion than time permits in this article. We will therefore work exclusively with the Simplex LP method here.

Constraints

Constraints must be added separately. To add a new constraint, just click the Add button and select the cell that will have the constraints applied to it. You then select the type of constraint, for example, the constrained cell must be less than a certain number. Finally you specify the constraining value, for example, the number that the constrained variable must be less than.

When setting constraints, it is important to remember which variables cannot take a negative number and then specify this as a constraint. In this case, the Amount Spent for any of the pay-per-click networks cannot take a negative value.

The constraint highlighted in blue ( $C$4:$F$4 Greater Than or Equal To 0) illustrates this type of constraint in this example:

Click On Image To See Larger Version

Close-Up of Constraints Input Into Solver

Click On Image To See Larger Version

Solve

You are now ready to hit the Solve button and get the final result. Solver changes the Decision Variables to optimize the Objective Cell. When Solver changes the Decision Variables, all other variables which depend upon these Decision Variables will be recalculated. By definition, the Objective Cell must be a variable that is derived from the Decision Variables. The end result of running the Solver are as follows:

Click On Image To See Larger Version

Final, Optimized Result

We can see that the Excel Solver calculated the maximum possible number of conversions to be 392. Solver did this by setting the Decision Variables as follows:

Amount Spent – Google AdWords Search: $500

Amount Spent – Google AdWords Content: $500

Amount Spent – Facebook Paid Ads: $300

Amount Spent – LinkedIn Paid Ads: $500

Constraints Satisfied

The constraints on the upper limits of each Amount Spent, Total Amount Spent, and non-negative constraint for each Amount Spent have all been satisfied.

Available Reports

There are 3 available reports that can be attached to the Solver’s result. These 3 reports are Answer, Sensitivity, and Limits as seen in the Solver Completion dialogue box below:

Click On Image To See Larger Version

We will briefly evaluate only a part of 1 of those reports in this article: the Sensitivity Report.

The Sensitivity Report

Part of the Sensitivity Report is shown below:

Click On Image To See Larger Version

The Objective Function column shows the coefficients for each Amount Spent to produce the Total Number of Conversions. Since this is a first-order equation, the result looks like this:

0.2 * (Amount Spent on Google AdWords Search) +

0.2857143 * (Amount Spent on Google AdWords Content) +

0.1066667 * (Amount Spent on Facebook Paid Ads) +

0.2333333 * (Amount Spent on LinkedIn Paid Ads) =

= Total Number of Conversions

We can see from the Objective Coefficients that an amount spent on the Google AdWords Content Network would have the greatest effect on the Total Number of Conversions.

Summary

The Excel Solver at its basic level is a simple tool to use to determine the optimal allocation of your available resources.

If you would like to create a link to this blog article, here is the link to copy for your convenience:
Using Solver To Optimize Your Marketing Budget