Your own personal Problem SOLVER

The business world is full of “what-if”s.  What if one of your suppliers raises its prices significantly?  What if your boss demands that you spend at least 20% of your advertising dollars on sports programming?  What if the corporate office suddenly shrinks your overall budget by 25%?  With scarce resources, companies (and individual consumers, as well) are always looking for ways to get the most bang for their buck.  Making matters more complex and difficult to navigate, often times decisions involve careful manipulation of several equally (or almost equally) important levers.  For example, you want to minimize your costs, but the cheapest vendor is not as effective or dependent as the more expensive ones.  And even if you had one clear choice, you never want to put all of your eggs in one basket, depending too heavily on one supplier.  Or maybe you’re asked to minimize your costs on marketing and advertising, but must reach a minimum number of potential customers.

One way to solve these types of problems is to build a spreadsheet, linking all of the important variables and calculations.  You could then give the user (you or someone else) the option to change those variables, viewing the results of different scenarios as changes are made.

The problems with this method should be obvious.  Not only is it tedious, requiring the user to manually change the numbers in order to see what happens, but it is also incomplete.  Insofar as these problems involve more than a few moving parts, it’s nearly impossible to manually account for and analyze every conceivable combination or possibility.

Luckily, Microsoft Excel provides a nifty tool to do all of this work for you.  You can think of it as your own personal problem Solver available at your command.  What more could you ask for?

First things first.  What is Solver?  Solver is an add-in tool that solves problems that involve many variable cells.  It is used most often when the user is trying to find the maximum or minimum value of a target cell by changing a range of variables within any number of constraints.  I know that’s a lot to digest, but bear with me.  This will be easier to understand with an example, but first let’s make sure you have the tool installed.  You’ll know the tool is installed if you:

• In Office 2007, go to the “Data” Ribbon and see a “Solver” button (usually on the far right)
• In Office 2003, click on the “Tools” Menu/dropdown and see “Solver”

If you do not have “Solver” installed, that’s okay.  Most users don’t.   But it’s easy to install it.  Simply:

• In Office 2007, click the Microsoft Office Icon (upper left). Then Select Excel Options/Add-In and hit the “Go” Button.  You’ll then see several add-in options, including “Solver”.  Click the checkbox and “Ok”, then “Yes” to install and confirm.
• In Ofice 2003, click “Add-Ins” on the “Tools” Menu, then select the “Solver Add-in” Checkbox.  Click “Ok” and “Yes” to continue and confirm.

Okay, now that we have Solver installed, let’s move on.  Again,  I think we can best understand this through an example.

Table 1: Intern's Report

Let’s say you are the Director of Television Advertising for a product that targets men and women ages 31-40.  I’m not sure what product that would be, but humor me.  You’re given a list of television programs to target and an overall budget of $5M.  But you’re asked to spend as little as you can and told your only (initial) requirement is that you reach 200M customers.  With instructions in hand, you assign your intern the task of getting pricing and (target audience) reach for each of the programs.  After he gets your favorite Starbucks drink, of course.  Two days later, the ambitious youngster provides the table we need.  (See Table 1: Intern’s Report)

Armed with this information, we can give our Problem SOLVER what it needs to give us what we need, namely the least spend to reach our desired goal.  With our only requirement that we reach at least 200M viewers and spend as little as possible, the answer is reached rather easily.  Before the SOLVER can go to work, though, you need to structure the worksheet in a manner that makes working through a solution easy. The SOLVER tool needs three elements to work properly:

1. A target cell, the cell you are trying to optimize (minimum or maximum value, typically)
2. The cells you want to change or manipulate to reach your optimal value
3. Any constraints that must be met while achieving numbers 1 and 2

In addition, you should set up the worksheet so that you can easily change values for the constraints, as well as easily calculate any necessary subtotals.  For example, having cells for Total Budget and Minimum Audience Reach that you can easily change is a lot easier than going into the SOLVER tool and manually changing the figures each time.  Some other ways to improve the efficiency of your worksheet include:

1. Naming Cells and Ranges (see Cells E13 through E18 and Ranges E3:E8 and G3:G8)
2. Adding Custom Formats to make things a little clearer (see Cell E17); Notice how we can show “No Min” instead of zero.

So, as the attached sheet shows, we’ve added to the wonderful work our intern has done the following columns:

• Column E: Ads Placed for each of the programs [This is the area that SOLVER will be changing to find the optimal value; the values you place in here initially don’t really matter.]
• Column F: Total Advertising Cost for each Program (Cost per Ad [col. C] * Ads Placed [col. E])
• % of Total: Advertising Spending on each program as a percentage of the total Advertising Spending.
• Total Audience: Total Audience Reach purchased for each program (Audience per Ad [Col. D] * Ads Placed [Col. E])

Change the Constraints, then Watch Magic!

And we’ve added placeholders for the following constraints:

• Total Budget
• Minimum to Spend on Sports Programming (Rows 6 [Monday Night Football] and 7 [SportsCenter])
• Minimum Total Audience Reach
• Maximum Allowable Spend on any One Program
• Minimum Number of Ads to Place on Any One Program
• Maximum % Spend on Any One Program

Now, we just need to set up the SOLVER.  Open SOLVER and let’s first assign the Target Cell.  In our case, we want to minimize TotalAdvertisingSpend (Cell F9).  In the “by changing cells:” option, we want to select the range named “Ads_PlacedRange” (Range E3:E9).

That part is done.

We now want to assign the constraints.  We’ll do this in stages so you can get a better hang of how this tool works.  But we’ve set up a table, so that changing the constraints is simply a matter of changing the cells in the worksheet, instead of having to go into solver and manually changing the figures.  To make things simpler, we enter the following constraints into SOLVER, all referring to cells in the worksheet, instead of hard numbers:

• Ads_PlacedRange = integer (you can’t place a partial ad)
• Ads_PlacedRange >= 0 (you can’t have a negative number of ads; unless, of course, you advertise with one of those dumb reality shows!)
• Ads_PlacedRange >= MinAds (Cell E17; initially set to 0, so not a constraint)
• PercOfTotalRange <= MaxPercOfTotal (Cell E18; initially set to 100%, so no constraint)
• TotalAdvertisingSpend <= TotalBudget (can’t spend more than the budget, right?)
• TotalReach >= Sum of Total Reach must be at least MinimumAudience (cell E15)
• TotalSportsAdSpend >= SportsBudget (you must spend AT LEAST x Dols on Sports Programming; initially set to 0, so no constraint)

Now, in succession, do the following and see what SOLVER spits out:

• Change the MinimumAudience (E15) from 0 (No Min) to 200
• Click ‘SOLVER’ and “SOLVE”

You’ll see that all of the money was spent on Army Wives.  (A good show, by the way.  Yes, I said it!).  That’s because we are trying to minimize spending with only one constraint, audience reached.  Therefore, SOLVER picks the cheapest road to its desired destination.  If we add constraints, things change.  Now, let’s say we want to diversify our spending.

• Change maxPercOfTotal (E18) from 100% to 25%
• Click ‘SOLVER’ and “SOLVE”

Big, big difference, huh?  Now, let’s say your boss is a big sports fan and wants to spend at least $1M on sports programs.

• Change SportsBudget from 0 to $1M
• Click ‘SOLVER’ and “SOLVE”

More changes, right?  Finally, we want to make sure we advertise with every program.

• Change MinAds (Cell E17) from 0 to (No Min) to 1
• Click ‘SOLVER’ and “SOLVE”

I assume you’re getting the point, so I’ll stop there.  But just to see what happens when the constraints don’t allow SOLVER to come up with a solution, do the following:

• Increase the Sports Spending to $2M

And/Or

• Increase the minimum number of Ads to 5

See.  No Solution.  Which means you have to look to see why.  I’ll let you use your brain a little to figure out why those constraints, combined with the others are impossible to solve.

I hope that’s been helpful and, more importantly, that you’ll try to use this very powerful and helpful tool in your work and even in your life.

As always, if you have questions, drop me a line or post a comment.

2007 Version: Example: Solver

2003 Version: Example: Solver