Excel models are often designed with parameters in the beginning of the spreadsheet (the upper left part) and calculations and results in another part. The nice thing about spreadsheets is that we can change one or more parameters and observe the effect on the results. In a budget we can for instance perform a sensitivity analysis (what-if analysis) by changing the price of a product, the number of units sold, etc., to see how the cash flow changes.
The goal seek function and the solver are excellent tools when we want to perform more advanced sensitivity analyses. The goal seek function is designed for plain problems where we want to find the parameter value that gives a specified result. The solver is suitable for more complex problems and allows us for instance to change several parameters and specify restrictions when setting a result to a specified value.
4.1 Goal seek
For sensitivity analyses, the goal seek function in Excel can be very useful. An example of such a sensitivity analysis is when we ask: How many units need to be sold to make a profit of £200,000?
We find the goal seek function under the tab Data and What-If Analysis in the group Data Tools. When we choose Goal Seek, the dialogue box in figure 4.1 appears. In the Set cell text box we may enter a cell address (such as B8) where we request the value specified in the To value text box. (This means that cell B8 must contain a formula.) In the By changing cell text box we write a cell address whose content we want to change so that the content of cell B8 reaches the requested value. In this case cell B8 must contain a formula that calculates a value based on the value in cell B5, either directly or indirectly.
Figure 4.1 Goal Seek.
In figure 4.1 we ask Excel to change the value in cell B5 so that the result in cell B8 changes to 100. When we click OK, the Goal Seek Status dialogue box appears and gives the result of the goal seek procedure.
Example 4.1
The manufacturer Gilbert Inc. sells a product at a price of £50 per unit. Variable unit cost is £20 and fixed cost is £100,000 per year. These values are written in the cells B4:B6 in figure 4.2.
Figure 4.2 Sensitivity analysis with Goal Seek.
In the spreadsheet in figure 4.2 we have calculated the revenue in cell B9 (as “=B3*B4”), variable cost in cell B10 (as “=–B3*B5”), contribution margin in cell B11 (as “=B9+B10”), and profit in cell B13 (as “=B11+B12”). We see that if Number of units in cell B3 changes, the profit in cell B13 will also change.
We want to find how many units Gilbert Inc. needs to sell to obtain a profit of £100,000. First we choose Data, What-If Analysis, and Goal Seek so that the dialogue box in figure 4.2 appears. In this case we ask Excel to change the number of units in cell B3 so that the profit in cell B13 becomes 100,000. When we click OK, the number of units will be set to 6667 which results in a profit of £100,000.
The solver is a library function in Excel. In principle it works like the goal seek function, but it is far more advanced. The solver is able to change the content of a number of specified cells so that the result from a formula in another cell gives a requested value. In addition it is possible to specify constraints, so that the solver always works inside a restricted area when solving a problem. To open the dialogue box for the solver, choose the tab Data and Solver in the group Analysis.
If you don’t find the solver on the ribbon, you need to install it. To do so, choose the tab File and Options in the backstage view. Then choose Add-Ins in the Option dialogue box that appears as illustrated in figure 1.3. At the bottom of the dialogue box you will now see a pull down box where you choose Excel Add-Ins. Then click Go. In the next dialogue box that appears, tag Solver Add-in and click OK. Then the solver appears as an option on the ribbon.
When we choose Data and Solver on the ribbon, the dialogue box in figure 4.3 appears. The cell addresses in the text boxes belong to example 4.2.
Figure 4.3 The Solver dialogue box.
We can use the solver to find the optimal value for a cell that contains a formula. This value must be calculated directly or indirectly from the contents of other cells. Figure 4.3 illustrates that the solver defines a number of cells that can be changed (under By Changing Variable Cells), and under which constraints (under Subject to the Constraints), to optimize the value in a specified cell (to the right of Set Objective). This value can be minimized, maximized or set to a given value.
To declare the constraints we click on the text box under Subject to the Constraints and then on Add so that the dialogue box in figure 4.4 appears. Here we can specify the cell addresses that contain values and formulas that define constraints. In figure 4.4 we tell the solver that the value in cell B7 should be less than or equal to the value in cell D7.
Each time we click Add, we add a new constraint to the model. When we are done with defining constraints, we click OK.
The dialogue box in figure 4.3 also offers more advanced procedures under Selecting a Solving Method and Options. You may explore these possibilities on your own.
Figure 4.4 The Constraint dialogue box.
When all text boxes in figure 4.3 are filled with cell addresses, we click Solve. Then the Solver Results dialogue box appears, and normally it tells us that the solver has found a solution. If we click OK, the optimal solution will be shown in the spreadsheet.
Find the maximum value for x+y under the following constraints:
The problem is presented in a spreadsheet in figure 4.5. Here cell E4 contain the formula “=B4+C4”, and the cells B7 and B8 the formulas “=2*C4–B4” and “=3*B4–C4” respectively.
Figure 4.5 Optimization problem.
The settings for the solver are shown in figure 4.3. The solution of the problem is x + y = 5 when x = 2 and y = 3.
Problems
4-1. Solve the following equation by Goal Seek in Excel.
4-2. Use the solver and find the maximum value for the expression 20x1 + 15x2 given the following restrictions:
