Desperately Seeking Solver

In this article, we consider how to automate the manipulation of inputs so that outputs will equal a certain value. Maybe this is the management accountant’s perfect Excel functionality..? By Liam Bastick, Managing Director (and Excel MVP) with SumProduct Pty Ltd.

Query

I am trying to use Goal Seek to get a key spreadsheet output to equal a certain value. I can manage this if I hard code the required output. However, is there any way I can set this output to a value stored in another cell?

Advice

It is commonplace in modelling for users to ascertain what value an input must have in order to achieve a desired outcome. Consider the following example, taken from the attached Excel file (as all of the examples here have been):

Here, we have been asked to calculate the internal rate of return on these cash flows (i.e. compute the discount rate such that the Net Present Value of these cash flows will equal zero). I have considered IRR before (see Irreverent IRR) and it will probably not surprise you that I have deliberately chosen a set of values where Excel’s IRR and XIRR functions fall over:

• IRR will not work as the periods are nor equidistant;
• XIRR gives an incorrect answer (0.00%). If this were correct, then the sum of the undiscounted cash flows must equal zero. They do not.

Previously, I have suggested a more robust technique is to construct a long-hand NPV calculation and use Excel’s Goal Seek functionality to derive the discount rate that will make the NPV zero.

Goal Seek (ALT + T + G, or else go to the ‘Data’ tab on the Ribbon, then in the ‘Data Tools’ group, select ‘What-If Analysis’ and then choose ‘Goal Seek…’) requires three inputs:

The ‘Set cell’ value is the NPV output here, ‘To value’ is the desired outcome (e.g. zero) and ‘By changing cell’ defines the variable input (e.g. discount rate). Our reader’s problem here is that they want the ‘To value’ to refer to a cell value rather than a typed-in (“hard coded”) number.

I have a very simple response: you can’t.

So what can you do instead..?

Introducing Solver

Excel includes a (hidden) tool called Solver that uses techniques from the operations research to find optimal solutions for all kind of decision problems. It is not situated in the standard loadset: it has to be loaded from add-ins.

In any version of Excel, a simple way to access add-ins is to use the keystroke shortcut ALT + T + I:

Checking the Solver add-in will add Solver to the ‘Data’ tab of the Ribbon:

Solver is often used to optimise / minimise outputs. Consider the following example:

Imagine you run a company with four products: A, B, C and D. Your intention is to maximise company profits, but you only have 1,000kg of raw material necessary for these four products. If this is all there is to the problem, then it would be simple – only produce Product D. However, imagine you had the following operational constraints:

• For every kilogram of Product B produced, you have to manufacture two kilograms of Product A;
• You must produce at least 50kg of Product B; and
• Product C is a by-product of Products A and D: the total weight of Products A and D must equal the number of kilograms of Product C produced.(/li>

The above graphic shows the optimal solution; the question is, how did I derive it?

To recreate the solution, open the Solver dialog box:

The “objective” here would be the output, i.e. total profit (cell I18 in the example) and the aim is to maximise it (note the other two alternatives of minimisation or trying to generate a particular output value). This is achieved by selecting which cells may be varied (here the kg produced), subject to the constraints specified. Constraints are simple to include – merely click the ‘Add’ button:

In Excel 2010 and later versions, Solver explicitly allows one of three Solving methods:

• Simplex Method: this method is used for solving linear problems (i.e. where the relationship between variables could be charted using a straight line). Our example above is one such instance;
• GRG Nonlinear: this is used for solving smooth nonlinear problems; and
• Evolutionary Solver: this approach uses genetic algorithms to find its solutions. While the Simplex and GRG solvers are used for linear and smooth nonlinear problems, the Evolutionary Solver can be used for any Excel formulas or functions, even when they are not linear or smooth nonlinear. Spreadsheet functions such as IF and VLOOKUP fall into this category.

In summary, Solver is a more powerful variant of Goal Seek, allowing forecasters to derive inputs to achieve specific goals and objectives. However, upon first inspection, Solver still does not appear to allow the value to be set to be a cell reference.

Therefore, you need to employ a simple trick.

Using Solver with a reference

In this example, I will return to the NPV example:

In this instance, I could have used Goal Seek as before, but instead I got out the heavy artillery:

In this example, the discount rate (Solver_Rate) is cell G15. You should notice two little tricks though: I have not used the NPV (cell H25) as the output, but a dummy value of cell I19 (the first period’s cash flow). This allows us to select a very useful constraint: that the NPV (cell H25) equals the required value (cell H28). It should also be noted that compounding discount rates is clearly a non-linear calculation technique so Simplex should not be used as a Solving Method.

This answers our reader’s question and has allowed me to show you a neat trick, but I am not entirely convinced of this solution. I cannot help feeling we are cracking a walnut with a thermonuclear warhead and besides, you still have to activate the Solver – this method will not update values automatically as inputs change.

So is there another way..?

VBA approach

Well, yes there is, although it involves VBA, which regular readers will note is a method I try to fall back on when all else fails. This could be argued (perhaps!) as one such instance.

In this example, there are two inputs, initial cash flow and required NPV (cells G15 and H27 respectively). Typing a value in either cell will change the discount rate in cell G13 so that the NPV (cell H25) equals the required value.

This was achieved using a macro.

As in previous articles (e.g. see Automating a Table of Contents), I have included the macro code by right-clicking on the relevant worksheet tab and select ‘View Code’, viz.

This will launch the Visual Basic Editor:

Not all of the above panes may be visible, but in the right hand pane paste in the following code (as shown in the graphic above):

It is very straightforward: if cell G15 (row 15, column 7) or H27 (row 27, column 8) is edited the macro is invoked and changes the discount rate in cell G13 such that the NPV in cell H25 equals the required output specified in cell H27.

As with the other examples (and mentioned earlier), I have provided an attached Excel file to demonstrate how this might work. As long as macros are enabled, this will change the discount rate without calling either Goal Seek or Solver.

Word to the Wise

It is worth experimenting with all three techniques to understand their limitations. If you choose to use the macro solution, do remember to save the Excel file as a macro enabled workbook!