Excel Solver is a heavy-duty tool. Let me show you when to use Excel Solver, and how
to use Excel Solver. Once you are considering to use Excel Solver in your spreadsheet, you
have probably already tried other What-if analysis tools like scenario manager, goal
seek, and data table, all of which I have covered in previous videos. Those “low intensity
tools” work fine in a lot of fairly simple cases, but sometimes you need heavier machinery
like Solver, for example if you are trying to model various constraints that potentially
conflict with each other like those listed here in column C!
In this Excel spreadsheet, we have some input variables here at the top, a profit and loss
forecast right below it, and related profitability ratios at the bottom. Most of the cells in
the profit and loss forecast as well as the profitability ratios section are linked to
the input variables through formulas. This is very important for Excel Solver to work:
your input variables must directly or indirectly feed into the target cell.
What’s currently in column B is the base case scenario for a new product introduction:
$1 million in expected revenues, $200,000 in expected operating margin. This base case
was discussed with senior management. They were supportive of the product itself, but
guess what: instead of $200K they want half a million dollars of operating margin for
this NPI, and a contribution margin in the range of 45% to 55% of revenue. Let’s see
if the NPI team can make that happen. The NPI team has gathered ideas and input from
marketing, manufacturing and sourcing that might just make this project work. Let’s
use Excel Solver to apply the constraints, and see if a solution comes out!
If we want to use Excel Solver, we first need to verify that it is activated! If you go
to the Data tab in Excel, and don’t see Excel Solver on the far right, then you need
to enable the Excel Solver add-in. Go to File – Options – Add-ins – Manage (at the
bottom of the screen) – Excel Add-ins – Go. Tick the box next to the Solver Add-in, click
OK, and Excel Solver appears on the far right of our Data tab.
Click on Solver, and let’s review the “Solver Parameters” screen. Some people call Excel
Solver an advanced version of goal seek, which I think is underestimating the power of the
tool. Excel Solver can help you find a solution to a problem subject to multiple constraints.
The objective we want to set is for Operating Margin in cell B11 to be a value of $500,000
by changing variable cells B2 through B4: volume, price, and variable cost per unit.
By clicking “Add” on the right, we can start adding the constraints that were gathered.
There is potential volume upside versus the base case, however Cell B2 (volume) needs
to be lower than or equal to 120 thousand units. We can add the next constraint right
here by clicking add: cell B3 (price) must be lower than or equal to $12 per unit. For
cell B4 (variable cost per unit) the manufacturing and sourcing teams have committed to taking
50 cents to a dollar of cost out by renegotiating raw material prices, and redesigning the product
for manufacturability. We add two constraints in Solver: lower than or equal to $5.50 per
unit, and greater than or equal to $5 per unit. Excel Solver does not allow you to set
a constraint “between $5 and $5.50”, so you have to set the lower and upper bounds
separately. For the moment, we are going to skip the constraint in cell C10, as Excel
Solver has a really hard time with discontinuous functions such as IF. Let’s close the Excel
Solver screen, and highlight cell C10 in yellow, so we don’t forget about it later on. If
you go back to the Solver screen, you see that the constraints we have already input
are still there. We can add the profitability ratio constraints from senior management.
Cell B14 (contribution margin) has to be 45% or higher, and cell B14 has to be 55% or lower.
Once you click OK, you will see all constraints in a list. The strange thing is that price,
variable cost per unit, and volume are named as such, while CM% is referred to as cell
B14. Let’s fix that by closing the Excel Solver screen, clicking on cell B14, and naming
it CMpercentage here on the top left. Now go back to Excel Solver, and CM% is listed
with its constraints. Below the constraint box, we have the option ticked to make unconstrained
variables non-negative. Let’s leave that in place. Now we can click “Solve” here
at the bottom, and find out whether Excel Solver can calculate a solution for us to
get to the required operating margin of $500,000, given the constraints listed.
YES!!! Solver found a solution, all constraints and optimality conditions are satisfied. The
solution that Excel Solver came up with is displayed in column B of the spreadsheet.
This is a solution, not necessarily the optimal solution, but it got us what we asked for:
an estimated $500,000 of Operating Margin in cell B11. Be aware that Excel Solver seems
to take different paths to an outcome if it so pleases. There are cases where each time
you run the Solver a different solution pops up without changing any of the data or doing
anything differently. This is what the message at the bottom refers to: a local optimal solution
versus a global optimal solution. A locally optimal solution is one where there are no
other feasible solutions "in the vicinity" with better objective function values. A global
optimal solution is one where there are no other feasible solutions with better objective
function values period. Let’s work with what we have in front of us!
Now don’t rush things by clicking OK straight away. We want to save this set of input variables
as a scenario. This is how we can combine the benefits of using scenario analysis with
the benefits of using Solver later on in the analysis. Click “save scenario”, let’s
name it “$500K OM v1”, and click OK. We click on Answer Report, as we want to find
out how Excel Solver arrived at the solution. Now we can accept the Excel Solver results,
by clicking OK in this dialog box as well. A tab has been added to the workbook called
“Answer Report 1”, which tells you that the Objective Cell of operating margin has
changed from $200,000 to $500,000, shows how the Variable Cells have changed, and shows
how the constraints have impacted the solution. Before we review this in more detail, let’s
do some formatting to clean up the Answer Report.
In the constraints section, there are lines that have a status “Binding”. For these
cells, the value that Solver generated is at the limit, the constraint is binding, there
is no slack, no space to maneuver. Other lines have a status called “Not Binding”. For
these cells, the values are not at the limits that we specified, the constraint is not binding,
there is some slack, there is room to maneuver if you so choose.
The “Answer Report” is important, you want to make sure nothing outrageous happened
when Solver was running, and you want to know how much slack is available for certain constraints.
Excel Solver just does the calculation for you, it’s up to human eyes to sanity-check
the outcome of that calculation. Now let’s deal with the comment in cell
C10: marketing recommends that if we aim for a total revenue from the NPI of more than
$1.2 million, then an incremental $50,000 in advertising budget in the fixed cost line
is needed. Let’s add a formula for that in cell B10. If the revenue in B7 exceeds
$1.2 million, then put a value of $250,000 in B10. If not, then put a value of $200,000
in B10. The formula recognizes straight away that revenue has exceeded $1.2 million after
the Solver run, so $250,000 of fixed cost is needed. The fixed costs in the model are
really a type of step fixed cost, or “fixed to a certain point”! The fixed cost increases
(or “jumps”) when the activity of a business exceeds a certain level, but thereafter remains
fixed at this new higher level. If we had added this formula earlier on, when the starting
point for Solver was the base case revenue of $1 million, then Solver would have had
trouble handling the sudden discontinuous jump up of $50,000 in fixed cost when revenue
starts to exceed $1.2 million, as Solver basically tests various solutions in iterations.
Now that we have added additional fixed cost, our Operating Margin in B11 has dropped by
$50,000 to $450,000, so let’s run Solver again to get it back up to $500,000. Solver
has found a solution, the new values are displayed in column B. By changing the input variables,
Solver has generated additional contribution margin in cell B9, that can cover the additional
fixed cost in B10. Let’s save that scenario as “$500K OM v2”, and once again request
an answer report. The answer report is called “Answer Report 2”, which shows how the
target value of operating margin went from $450K to $500K (objective cell section), by
changing variable cells, which may alter the state of each constraint being binding or
non-binding. Now is the time to combine the benefits of
using scenario analysis with the benefits of using Solver. Data – What-if analysis
– Scenario Manager – Summary – report type Scenario Summary. A tab called “Scenario
Summary 3” is added. In the earlier video on how to use scenario manager in Excel, we
created scenario summaries 1 and 2, this scenario manager 3 is the current snapshot of all scenarios
side-by-side. The current values for volume, price, variable cost per unit, and operating
margin, are in column D. They are equal to the values in column J, which shows the “$500K
OM v2” scenario that we have just stored. The base case that we started this video with
is in column E. The three scenarios from the video on scenario manager are in columns F,
G and H. “$500K OM v1” scenario that we stored earlier is in column I. It no longer
adds up to $500,000 of operating margin, due to the step-wise increase in base cost. I
love having the scenarios side-by-side. If it turns out that we want to go back to a
different scenario, we can simply restore it! Data – What-if analysis – Scenario
Manager – take your pick from the six scenarios available.
The new scenario in the NPI tab is ready for review, by the NPI team as well as senior
management. One more check of the assumptions, and the relationship between them, before
hopefully the green light is given and the NPI is launched! And if any new constraints
or changes in existing constraints come out of that business review, start up our heavy-duty
Excel Solver tool once again, add the constraints,
and see if a new and improved solution is available!