Microsoft Excel 2016 BIBLE (2016)

IN THIS CHAPTER

1. Performing what-if analysis — in reverse

2. Single-cell goal seeking

3. Introducing Solver

4. Looking at Solver examples

The preceding chapter discussed what-if analysis — the process of changing input cells to observe the results on other dependent cells. This chapter looks at that process from the opposite perspective: finding the value of one or more input cells that produce a desired result in a formula cell.

What-If Analysis, in Reverse

Consider the following what-if question: “What is the total profit if sales increase by 20%?” If you set up your worksheet model properly, you can change the value in one or more cells to see what happens to the profit cell. The examples in this chapter take the opposite approach. If you know what a formula result should be, Excel can tell you the values that you need to enter in one or more input cells to produce that result. In other words, you can ask a question such as “How much do sales need to increase to produce a profit of $1.2 million?” Excel provides two tools that are relevant:

· Goal Seek: Determines the value that you need to enter in a single input cell to produce a result that you want in a dependent (formula) cell.

· Solver: Determines the values that you need to enter in multiple input cells to produce a result that you want. Moreover, because you can specify certain constraints to the problem, you gain significant problem-solving ability.

Single-Cell Goal Seeking

Single-cell goal seeking is a rather simple concept. Excel determines what value in an input cell produces a desired result in a formula cell. The following example shows you how single-cell goal seeking works.

A goal-seeking example

Figure 36.1 shows the mortgage loan worksheet used in the preceding chapter. This worksheet has four input cells (C4:C7) and four formula cells (C10:C13). Originally, this worksheet was used for a what-if analysis example. This example demonstrates the opposite approach. Rather than supply different input cell values to look at the calculated formulas, this example lets Excel determine one of the input values that will produce the desired result.

Figure 36.1 This worksheet is a good demonstration of goal seeking.

This workbook is available on this book's website at www.wiley.com/go/excel2016bible. The file is named mortgage loan.xlsx.

Assume that you're in the market for a new home and you know that you can afford an $1,800 monthly mortgage payment. You also know that a lender can issue a 30-year fixed-rate mortgage loan for 6.50%, based on an 80% loan-to-value (that is, a 20% down payment). The question is: “What is the maximum purchase price I can handle?” In other words, what value in cell C4 (purchase price) causes the formula in cell C11 (monthly payment) to result in $1,800? In this simple example, you could plug values into cell C4 until C11 displays $1,800. With more complex models, Excel can usually determine the answer much more efficiently.

To answer the question posed in the preceding paragraph, first set up the input cells to match what you already know. Specifically:

· Enter 20% into cell C5 (the down payment percent).

· Enter 360 into cell C6 (the loan term, in months).

· Enter 6.5% into cell C7 (the annual interest rate).

Next, choose Data Forecast What-If Analysis Goal Seek. The Goal Seek dialog box appears. Completing this dialog box is similar to forming a sentence. You want to set cell C11 to 1800 by changing cell C4. Enter this information in the dialog box either by typing the cell references or by pointing with the mouse (see Figure 36.2). Click OK to begin the goal-seeking process.

Figure 36.2 The Goal Seek dialog box.

In less than a second, Excel displays the Goal Seek Status box, shown in Figure 36.3, which shows the target value and the value that Excel calculated. In this case, Excel found an exact value. The worksheet now displays the found value in cell C4 ($355,974). As a result of this value, the monthly payment amount is $1,800. At this point, you have two options:

Figure 36.3 Goal Seek has found a solution.

· Click OK to replace the original value with the found value. After doing so, you can use Undo to return to the original value.

· Click Cancel to restore your worksheet to the form that it had before you chose Goal Seek.

More about goal seeking

Excel can't always find a value that produces the result that you're seeking. Sometimes a solution simply doesn't exist. In such a case, the Goal Seek Status box informs you of that fact.

Other times, however, Excel may report that it can't find a solution, but you're pretty sure that one exists. If that's the case, you can try the following options:

· Change the current value of the By Changing Cell field in the Goal Seek dialog box (refer to Figure 36.2) to a value that is closer to the solution, and then reissue the command.

· Adjust the Maximum iterations setting on the Formulas tab of the Excel Options dialog box (choose File Options). Increasing the number of iterations (or calculations) makes Excel try more possible solutions.

· Double-check your logic and make sure that the formula cell does, indeed, depend upon the specified changing cell.

Note

Like all computer programs, Excel has limited precision. To demonstrate this limitation, enter =A1^2 into cell A2. Then use the Goal Seek dialog box to find the value in cell A1 (which is empty) that makes the formula return 16. Excel comes up with a value of 4.00002269, which is close to the square root of 16 but certainly not exact. You can adjust the precision for goal seeking on the Formulas tab of the Excel Options dialog box. (Make the Maximum Change value smaller.)

Note

In some cases, multiple values of the input cell produce the same desired result. For example, the formula =A1^2 returns 16 if cell A1 contains either –4 or +4. If you use goal seeking when multiple solutions are possible, Excel gives you the solution that is closest to the current value.

Introducing Solver

The Excel Goal Seek feature is a useful tool, but it clearly has limitations. It can solve for only one adjustable cell, and it returns only a single solution. Excel's powerful Solver tool extends this concept by enabling you to do the following:

· Specify multiple adjustable cells.

· Specify constraints on the values that the adjustable cells can have.

· Generate a solution that maximizes or minimizes a particular worksheet cell.

· Generate multiple solutions to a problem.

Although goal seeking is a relatively simple operation, using Solver can be much more complicated. In fact, Solver is probably one of the most difficult (and potentially frustrating) features in Excel. I'm the first to admit that Solver isn't for everyone. In fact, most Excel users have no use for this feature. However, many users find that having this much power is worth spending the extra time to learn about it.

Appropriate problems for Solver

Problems that are appropriate for Solver fall into a relatively narrow range. They typically involve situations that meet the following criteria:

· A target cell depends on other cells and formulas. Typically, you want to maximize or minimize this target cell or set it equal to some value.

· The target cell depends on a group of cells (called changing cells) that Solver can adjust to affect the target cell.

· The solution must adhere to certain limitations, or constraints.

After you set up your worksheet appropriately, you can use Solver to adjust the changing cells and produce the result that you want in your target cell — and simultaneously meet all the constraints that you defined.

No Solver Command?

You access Solver by choosing Data Analyze Solver. If this command isn't available, you need to install the Solver add-in. It's a simple process:

1. Choose File Options. The Excel Options dialog box appears.

2. Select the Add-Ins tab.

3. At the bottom of the dialog box, select Excel Add-Ins from the Manage drop-down list and then click Go. The Add-Ins dialog box appears.

4. Place a check mark next to Solver Add-In, and then click OK.

After you perform these steps, the Solver add-in loads whenever you start Excel.

A simple Solver example

I start with a simple example to introduce Solver and then present some increasingly complex examples to demonstrate what this feature can do.

Figure 36.4 shows a worksheet that is set up to calculate the profit for three products. Column B shows the number of units of each product, Column C shows the profit per unit for each product, and Column D contains formulas that calculate the total profit for each product by multiplying the units by the profit per unit.

Figure 36.4 Use Solver to determine the number of units to maximize the total profit.

This workbook, named three products.xlsx, is available on this book's website at www.wiley.com/go/excel2016bible.

You don't need an MBA to realize that the greatest profit comes from Product C. Therefore, to maximize total profit, the logical solution is to produce only Product C. If things were really this simple, you wouldn't need tools such as Solver. As in most situations, this company has some constraints that must be met:

· The combined production capacity is 300 total units per day.

· The company needs 50 units of Product A to fill an existing order.

· The company needs 40 units of Product B to fill an anticipated order.

· Because the market for Product C is relatively limited, the company doesn't want to produce more than 40 units of this product.

These four constraints make the problem more realistic and a bit more challenging. In fact, it's a perfect problem for Solver.

I go into more detail in a moment, but here's the basic procedure for using Solver:

1. Set up the worksheet with values and formulas. Make sure that you format cells logically; for example, if you can't produce partial units of your products, format those cells to contain numbers with no decimal values.

2. Choose Data Analyze Solver. The Solver Parameters dialog box appears.

3. Specify the target cell (also known as the objective).

4. Specify the range that contains the changing cells.

5. Specify the constraints.

6. Change the Solver options, if necessary.

7. Click Solve, and let Solver go to work.

To start Solver to tackle this example, choose Data Analyze Solver. The Solver Parameters dialog box appears. Figure 36.5 shows this dialog box, set up to solve the problem.

Figure 36.5 The Solver Parameters dialog box.

In this example, the target cell is D6 — the cell that calculates the total profit for three products.

1. Enter D6 into the Set Objective field of the Solver Parameters dialog box.

2. Because the objective is to maximize this cell, select the Max option button.

3. Specify the changing cells (which are in the range B3:B5) in the By Changing Variable Cells field. The next step is to specify the constraints on the problem. The constraints are added one at a time and appear in the Subject to the Constraints list.

4. To add a constraint, click the Add button. The Add Constraint dialog box, shown in Figure 36.6, appears. This dialog box has three parts: a Cell Reference, an operator, and a Constraint value.

Figure 36.6 The Add Constraint dialog box.

5. To set the first constraint (that the total production capacity is 300 units), enter B6 as the Cell Reference, choose equal (=) from the drop-down list of operators, and enter 300 as the Constraint value.

6. Click Add, and enter the remaining constraints. Table 37.1 summarizes the constraints for this problem.

Table 37.1 Constraints Summary

7. After you enter the last constraint, click OK to return to the Solver Parameters dialog box, which now lists the four constraints.

8. For the Solving Method, use Simplex LP.

9. Click the Solve button to start the solution process. You can watch the progress onscreen, and Excel soon announces that it has found a solution. The Solver Results dialog box is shown in Figure 36.7.

Figure 36.7 Solver displays this dialog box when it finds a solution to the problem.

At this point, you have the following options:

· Keep the solution that Solver found.

· Restore the original changing cell values.

· Create any or all of the three reports that describe what Solver did.

· Click the Save Scenario button to save the solution as a scenario so that Scenario Manager can use it.

See Chapter 35, “Performing Spreadsheet What-If Analysis,” for more on Scenario Manager

The Reports section of the Solver Results dialog box lets you select any or all of three optional reports. If you specify any report options, Excel creates each report on a new worksheet, with an appropriate name. Figure 36.8 shows an Answer Report. In the Constraints section of the report, three of the four constraints are binding, which means that these constraints were satisfied at their limit with no more room to change.

Figure 36.8 One of three reports that Solver can produce.

This simple example illustrates the way Solver works. The fact is, you could probably solve this particular problem manually by trial and error. That, of course, isn't always the case.

Caution

When you close the Solver Results dialog box (by clicking either OK or Cancel), the Undo stack is cleared. In other words, you can't undo any changes that Solver makes to your workbook.

More about Solver

Before presenting more complex examples, this section discusses the Solver Options dialog box. From this dialog box, you control many aspects of the solution process, as well as load and save model specifications in a worksheet range.

Usually, you want to save a model only when you're using more than one set of Solver parameters with your worksheet. This is because Excel saves the first Solver model automatically with your worksheet (using hidden names). If you save additional models, Excel stores the information in the form of formulas that correspond to the specifications. (The last cell in the saved range is an array formula that holds the options settings.)

It's not unusual for Solver to report that it can't find a solution, even when you know that one should exist. Often, you can change one or more of the Solver options and try again. When you click the Options button in the Solver Parameters dialog box, the Solver Options dialog box, shown in Figure 36.9, appears.

Figure 36.9 You can control many aspects of the way Solver solves a problem.

This list describes Solver's options:

· Constraint Precision: Specify how close the Cell Reference and Constraint formulas must be to satisfy a constraint. Excel may solve the problem more quickly if you specify less precision.

· Use Automatic Scaling: Use when the problem involves large differences in magnitude — when you attempt to maximize a percentage, for example, by varying cells that are very large.

· Show Iteration Results: Instruct Solver to pause and display the results after each iteration by selecting this check box.

· Ignore Integer Constraints: When this check box is selected, Solver ignores constraints that specify that a particular cell must be an integer. Using this option may allow Solver to find a solution that can't be found otherwise.

· Max Time: Specify the maximum amount of time (in seconds) that you want Solver to spend on a problem. If Solver reports that it exceeded the time limit, you can increase the amount of time that it spends searching for a solution.

· Iterations: Enter the maximum number of trial solutions that you want Solver to perform.

· Max Subproblems: Use this for complex problems. Specify the maximum number of subproblems that may be explored by the Evolutionary algorithm.

· Max Feasible Solutions: Use this for complex problems. Specify the maximum number of feasible solutions that may be explored by the Evolutionary algorithm.

Note

The other two tabs in the Options dialog box contain additional options used by the GRG Nonlinear and Evolutionary algorithms.

Solver Examples

The remainder of this chapter consists of examples of using Solver for various types of problems.

Solving simultaneous linear equations

This example describes how to solve a set of three linear equations with three variables. Here's an example of a set of linear equations:

1. 4x + y – 2z = 0

2. 2x – 3y + 3z = 9

3. –6x – 2y + z = 0

The question that Solver will answer is this: What values of x, y, and z satisfy all three equations?

Figure 36.10 shows a workbook set up to solve this problem. This workbook has three named cells, which makes the formulas more readable:

Figure 36.10 Solver will attempt to solve this series of linear equations.

· x: C11

· y: C12

· z: C13

All three named cells are initialized to 1.0 (which certainly doesn't solve the equations).

This workbook, named linear equations.xlsx, is available on this book's website at www.wiley.com/go/excel2016bible.

The three equations are represented by formulas in the range B6:B8:

· B6: =(4*x)+(y)-(2*z)

· B7: =(2*x)-(3*y)+(3*z)

· B8: =-(6*x)-(2*y)+(z)

These formulas use the values in the x, y, and z named cells. The range C6:C8 contains the desired result for these three formulas.

Solver will adjust the values in x, y, and z — that is, the changing cells in C11:C13 — subject to these constraints:

B6=C6

B7=C7

B8=C8

Note

This problem doesn't have a target cell because it's not trying to maximize or minimize anything. However, the Solver Parameters dialog box insists that you specify a formula for the Set Objective field. Therefore, just enter a reference to any cell that has a formula.

Figure 36.11 shows the solution. The x (0.75), y (–2.0), and z (0.5) values satisfy all three equations.

Figure 36.11 Solver will attempt to solve this series of linear equations.

Note

A set of linear equations may have one solution, no solution, or an infinite number of solutions.

Minimizing shipping costs

This example involves finding alternative options for shipping materials, while keeping total shipping costs at a minimum (see Figure 36.12). A company has warehouses in Los Angeles, St. Louis, and Boston. Retail outlets throughout the United States place orders, which the company then ships from one of the warehouses. The company wants to meet the product needs of all six retail outlets from available inventory and keep total shipping charges as low as possible.

Figure 36.12 This worksheet determines the least expensive way to ship products from warehouses to retail outlets.

This workbook, named shipping costs.xlsx, is available on this book's website at www.wiley.com/go/excel2016bible.

This workbook is rather complicated, so I'll explain each part individually:

· Shipping Costs Table: This table, in range B2:E8, is a matrix that contains per-unit shipping costs from each warehouse to each retail outlet. The cost to ship a unit from Los Angeles to Denver, for example, is $58.

· Product needs of each retail store: This information appears in C12:C17. For example, Denver needs 150 units, Houston needs 225, and so on. C18 contains a formula that calculates the total needed.

· Number to ship from: Range D12:F17 holds the adjustable cells that Solver varies. All these cells are initialized with a value of 25 to give Solver a starting value. Column G contains formulas that sum the number of units the company needs to ship to each retail outlet.

· Warehouse inventory: Row 21 contains the amount of inventory at each warehouse, and row 22 contains formulas that subtract the amount shipped (row 18) from the inventory.

· Calculated shipping costs: Row 24 contains formulas that calculate the shipping costs. Cell D24 contains the following formula, which is copied to the two cells to the right of cell D24:

=SUMPRODUCT(C3:C8,D12:D17)

Cell G24 is the bottom line, the total shipping costs for all orders.

Solver fills in values in the range D12:F17 in such a way that minimizes shipping costs while still supplying each retail outlet with the desired number of units. In other words, the solution minimizes the value in cell G24 by adjusting the cells in D12:F17, subject to the following constraints:

· The number of units needed by each retail outlet must equal the number shipped. (In other words, all the orders are filled.) These constraints are represented by the following specifications:

·C12=G12 C14=G14 C16=G16

C13=G13 C15=G15 C17=G17

· The number of units remaining in each warehouse's inventory must not be negative. (In other words, they can't ship more than what's available.) This is represented by the following constraint specifications:

D22>=0 E22>=0 F22>=0

· The adjustable cells can't be negative because shipping a negative number of units makes no sense. The Solve Parameters has a handy option: Make Unconstrained Variables Non-Negative. Make sure this setting is enabled.

Note

Before you solve this problem with Solver, you may want to attempt to solve this problem manually by entering values in D12:F17 that minimize the shipping costs. And, of course, you need to make sure that all the constraints are met. Doing so may help you better appreciate Solver.

Setting up the problem is the difficult part. For example, you must enter nine constraints. When you have specified all the necessary information, click the Solve button to put Solver to work. Solver displays the solution shown in Figure 36.13.

Figure 36.13 The solution that was created by Solver.

The total shipping cost is $55,515, and all the constraints are met. Notice that shipments to Miami come from both St. Louis and Boston.

Learning More About Solver

Solver is a complex tool, and this chapter barely scratches the surface. If you'd like to learn more about Solver, I highly recommend the website for Frontline Systems (www.solver.com). Frontline Systems is the company that developed Solver for Excel. Its website has several tutorials and lots of helpful information, including a detailed manual that you can download. You can also find additional Solver products for Excel that can handle much more complex problems.

Allocating resources

The example in this section is a common type of problem that's ideal for Solver. Essentially, problems of this sort involve optimizing the volumes of individual production units that use varying amounts of fixed resources. Figure 36.14 shows a simplified example for a toy company.

This workbook is available on this book's website at www.wiley.com/go/excel2016bible. The file is named allocating resources.xlsx.

Figure 36.14 Using Solver to maximize profit when resources are limited.

This company makes five different toys, which use six different materials in varying amounts. For example, Toy A requires 3 units of blue paint, 2 units of white paint, 1 unit of plastic, 3 units of wood, and 1 unit of glue. Column G shows the current inventory of each type of material. Row 10 shows the unit profit for each toy.

The number of toys to make is shown in the range B11:F11. These are the values that Solver determines (the changing cells). The goal of this example is to determine how to allocate the resources to maximize the total profit (B13). In other words, Solver determines how many units of each toy to make. The constraints in this example are relatively simple:

· Ensure that production doesn't use more resources than are available. This can be accomplished by specifying that each cell in column I is greater than or equal to zero.

· Ensure that the quantities produced aren't negative. This can be accomplished by specifying the Make Unconstrained Variables Non-Negative option.

Figure 36.15 shows the results that are produced by Solver. It shows the product mix that generates $12,365 in profit and uses all resources in their entirety, except for glue.

Figure 36.15 Solver determined how to use the resources to maximize the total profit.

Optimizing an investment portfolio

This example demonstrates how to use Solver to help maximize the return on an investment portfolio. A portfolio consists of several investments, each of which has a different yield. In addition, you may have some constraints that involve reducing risk and diversification goals. Without such constraints, a portfolio problem becomes a no-brainer: put all your money in the investment with the highest yield.

This example involves a credit union (a financial institution that takes members' deposits and invests them in loans to other members, bank CDs, and other types of investments). The credit union distributes part of the return on these investments to the members in the form of dividends, or interest on their deposits.

This hypothetical credit union must adhere to some regulations regarding its investments, and the board of directors has imposed some other restrictions. These regulations and restrictions comprise the problem's constraints. Figure 36.16 shows a workbook set up for this problem.

Figure 36.16 This worksheet is set up to maximize a credit union's investments, given some constraints.

This workbook is available on this book's website at www.wiley.com/go/excel2016bible. The file is named investment portfolio.xlsx.

Allocating the $5 million portfolio is subject to these constraints:

· The amount that the credit union invests in new-car loans must be at least three times the amount that the credit union invests in used-car loans. (Used-car loans are riskier investments.) This constraint is represented as

C5>=C6*3

· Car loans should make up at least 15% of the portfolio. This constraint is represented as

D14>=.15

· Unsecured loans should make up no more than 25% of the portfolio. This constraint is represented as

E8<=.25

· At least 10% of the portfolio should be in bank CDs. This constraint is represented as

E9>=.10

· The total amount invested is $5,000,000.

· All investments should be positive or zero.

The changing cells are C5:C9, and the goal is to maximize the total yield in cell D12. Starting values of 1,000,000 have been entered in the changing cells. When you run Solver with these parameters, it produces the solution shown in Figure 36.17, which has a total yield of 9.25%.

Figure 36.17 The results of the portfolio optimization.