Optimization with Excel Solver
Solver is a Microsoft Excel add-in program you can use for optimization in what-if analysis.
According to O'Brien and Marakas, optimization analysis is a more complex extension of goal-seeking analysis. Instead of setting a specific target value for a variable, the goal is to find the optimum value for one or more target variables, under certain constraints. Then, one or more other variables are changed repeatedly, subject to the specified constraints, until you discover the best values for the target variables.
In Excel, you can use Solver to find an optimal value (maximum or minimum, or a certain value) for a formula in one cell called the objective cell, subject to certain constraints or limits, on the values of other formula cells on the worksheet.
This means that the Solver works with a group of cells called decision variables that are used in computing the formulas in the objective and constraint cells. Solver adjusts the values in the decision variable cells to satisfy the limits on constraint cells and produce the result you want for the objective cell.
You can use Solver to find optimal solutions for diverse problems such as −
Determining the monthly product mix for a drug manufacturing unit that maximizes the profitability.
Scheduling workforce in an organization.
Solving transportation problems.
Financial planning and budgeting.
Activating Solver Add-in
Before you proceed with finding solution for a problem with Solver, ensure that the Solver Add-in is activated in Excel as follows −
- Click the DATA tab on the Ribbon. The Solver command should appear in the Analysis group as shown below.
In case you do not find the Solver command, activate it as follows −
- Click the FILE tab.
- Click Options in the left pane. Excel Options dialog box appears.
- Click Add-Ins in the left pane.
- Select Excel Add-Ins in the Manage box and click Go.
The Add-Ins dialog box appears. Check Solver Add-in and click Ok. Now, you should be able to find the Solver command on the Ribbon under the DATA tab.
Solving Methods used by Solver
You can choose one of the following three solving methods that Excel Solver supports, based on the type of problem −
Used for linear problems. A Solver model is linear under the following conditions −
The target cell is computed by adding together the terms of the (changing cell)*(constant) form.
Each constraint satisfies the linear model requirement. This means that each constraint is evaluated by adding together the terms of the (changing cell)*(constant) form and comparing the sums to a constant.
Generalized Reduced Gradient (GRG) Nonlinear
Used for smooth nonlinear problems. If your target cell, any of your constraints, or both contain references to changing cells that are not of the (changing cell)*(constant) form, you have a nonlinear model.
Used for smooth nonlinear problems. If your target cell, any of your constraints, or both contain references to changing cells that are not of the (changing cell)*(constant) form, you have a nonlinear model.
Understanding Solver Evaluation
The Solver requires the following parameters −
- Decision Variable Cells
- Constraint Cells
- Objective Cells
- Solving Method
Solver evaluation is based on the following −
The values in the decision variable cells are restricted by the values in the constraint cells.
The calculation of the value in the objective cell includes the values in the decision variable cells.
Solver uses the chosen Solving Method to result in the optimal value in the objective cell.
Defining a Problem
Suppose you are analyzing the profits made by a company that manufactures and sells a certain product. You are asked to find the amount that can be spent on advertising in the next two quarters subject to a maximum of 20,000. The level of advertising in each quarter affects the following −
- The number of units sold, indirectly determining the amount of sales revenue.
- The associated expenses, and
- The profit.
You can proceed to define the problem as −
- Find Unit Cost.
- Find the advertising cost per Unit.
- Find Unit Price.
Next, set the cells for the required calculations as given below.
As you can observe, the calculations are done for Quarter1 and Quarter2 that are in consideration are −
No. of units available for sale in Quarter1 is 400 and in Quarter2 is 600 (cells – C7 and D7).
The initial values for advertising budget are set as 10000 per Quarter (Cells – C8 and D8).
No. of units sold is dependent on the advertising cost per unit and hence is budget for the quarter / Adv. Cost per unit. Note that we have used the Min function to take care to see that the no. of units sold in <= no. of units available. (Cells – C9 and D9).
Revenue is calculated as Unit Price * No. of Units sold (Cells – C10 and D10).
Expenses is calculated as Unit Cost * No. of Units Available + Adv. Cost for that quarter (Cells – C11 and D12).
Profit is Revenue – Expenses (Cells C12 and D12).
Total Profit is Profit in Quarter1 + Profit in Quarter2 (Cell – D3).
Next, you can set the parameters for Solver as given below −
As you can observe, the parameters for Solver are −
Objective cell is D3 that contains Total Profit, which you want to maximize.
Decision Variable cells are C8 and D8 that contain the budgets for the two quarters – Quarter1 and Quarter2.
There are three Constraint cells - C14, C15 and C16.
Cell C14 that contains total budget is to set the constraint of 20000 (cell D14).
Cell C15 that contains the no. of units sold in Quarter1 is to set the constraint of <= no. of units available in Quarter1 (cell D15).
Cell C16 that contains the no. of units sold in Quarter2 is to set the constraint of <= no. of units available in Quarter2 (cell D16).
Solving the Problem
The next step is to use Solver to find the solution as follows −
Step 1 − Go to DATA > Analysis > Solver on the Ribbon. The Solver Parameters dialog box appears.
Step 2 − In the Set Objective box, select the cell D3.
Step 3 − Select Max.
Step 4 − Select range C8:D8 in the By Changing Variable Cells box.
Step 5 − Next, click the Add button to add the three constraints that you have identified.
Step 6 − The Add Constraint dialog box appears. Set the constraint for total budget as given below and click Add.
Step 7 − Set the constraint for total no. of units sold in Quarter1 as given below and click Add.
Step 8 − Set the constraint for total no. of units sold in Quarter2 as given below and click OK.
The Solver Parameters dialog box appears with the three constraints added in box –Subject to the Constraints.
Step 9 − In the Select a Solving Method box, select Simplex LP.
Step 10 − Click the Solve button. The Solver Results dialog box appears. Select Keep Solver Solution and click OK.
The results will appear in your worksheet.
As you can observe, the optimal solution that produces maximum total profit, subject to the given constraints, is found to be the following −
- Total Profit – 30000.
- Adv. Budget for Quarter1 – 8000.
- Adv. Budget for Quarter2 – 12000.
Stepping through Solver Trial Solutions
You can step through the Solver trial solutions, looking at the iteration results.
Step 1 − Click the Options button in the Solver Parameters dialog box.
The Options dialog box appears.
Step 2 − Select the Show Iteration Results box and click OK.
Step 3 − The Solver Parameters dialog box appears. Click Solve.
Step 4 − The Show Trial Solution dialog box appears, displaying the message - Solver paused, current solution values displayed on worksheet.
As you can observe, the current iteration values are displayed in your working cells. You can either stop the Solver accepting the current results or continue with the Solver from finding solution in further steps.
Step 5 − Click Continue.
The Show Trial Solution dialog box appears at every step and finally after the optimal solution is found, Solver Results dialog box appears. Your worksheet is updated at every step, finally showing the result values.
Saving Solver Selections
You have the following saving options for the problems that you solve with Solver −
You can save the last selections in the Solver Parameters dialog box with a worksheet by saving the workbook.
Each worksheet in a workbook can have its own Solver selections, and all of them will be saved when you save the workbook.
You can also define more than one problem in a worksheet, each with its own Solver selections. In such a case, you can load and save problems individually with the Load/Save in the Solver Parameters dialog box.
Click the Load/Save button. The Load/Save dialog box appears.
To save a problem model, enter the reference for the first cell of a vertical range of empty cells in which you want to place the problem model. Click Save.
The problem model (the Solver Parameters set) appears starting at the cell that you have given as the reference.
To load a problem model, enter the reference for the entire range of cells that contains the problem model. Then, click on the Load button.
Optimization Modeling with Solver in Excel
Excel’s Solver tool lets you solve optimization-modeling problems, also commonly known as linear programming programs. With an optimization-modeling problem, you want to optimize an objective function but at the same time recognize that there are constraints, or limits. While this abstract definition sounds complicated, at least at the conceptual level, optimization modeling makes common sense once you provide a concrete example.
EasyRefresher: How Optimization Modeling Works
Suppose, for example, that you’re a residential real estate developer and contractor. You create and sell two products: building lots and houses. Suppose that you make $20,000 on each home you build and $15,000 on each building lot you develop and then sell. Your princi- pal financial objective is to maximize your profits, and this objective can be expressed as an objective function, or equation, that you want to maximize:
Of course, any objective function is limited by certain constraints. To continue with the fictional case of residential development, suppose that you have two principal limiting fac- tors: working capital and bulldozer capacity. Your working capital of $1,200,000 limits the number of lots and houses you can annually sell because every lot requires a $50,000 cash investment and every house requires a $25,000 cash investment. The fact that you have a single bulldozer available for only 3,000 hours each year also limits the number of lots and houses you can annually sell because every lot requires 80 hours of bulldozing and every house requires 200 hours of bulldozing. These two constraints can also be expressed as equations. For example, the working capital constraint can be expressed as follows:
This formula says the result of the formula $50,000 times the number of lots plus $25,000 times the number of houses must be less than or equal to the working capital limit of $1,200,000. The less than or equal to symbol is represented by the <= operator.
The bulldozer capacity constraint can be expressed as follows:
This formula says the result of the formula 80 times the number of lots plus 200 times the number of houses must be less than or equal to the bulldozer-hours limit of 3,000. Again, the less than or equal to symbol is represented by the <= operator.
Typically, you also have policy constraints when you work with an optimization-modeling problem. Suppose that as a matter of policy you want to maintain a certain level of activity both in developing lots and building houses. You might say, for example, that because you must maintain your team’s expertise in both raw land development and residential contracting that you want to develop at least 10 lots every year and build at least 5 houses. These two constraints also need to be expressed as equations. The minimum-number-of-lots policy constraint can be expressed as follows:
This formula says that you want to develop at least 10 building lots. Or, restated, this for- mula says that the lots variable must be greater than or equal to 10. The greater than or equal to symbol is represented by the >= operator.
The minimum-number-of-houses policy constraint can be expressed as follows:
This formula says that you want to build at least 5 houses. Or, restated, this formula says that the houses variable must be greater than or equal to 10. Again, the greater than or equal to symbol is represented by the >= operator.
With the information provided in the preceding paragraphs of this EasyRefresherTM, I’ve described your fictional optimization-modeling problem. You want to maximize your profits, which can be described using the following objective function:
but you can’t develop unlimited numbers of building lots or build unlimited numbers of houses. You are subject to the following constraints:
You can solve this equation in a variety of ways, including graphically, iteratively, or using a technique like simplex algebra. Or, you can provide the objective function and the con- straint equations to Excel and have it solve the problem, which is the solution technique described in the paragraphs that follow.
Solving an Optimization Problem
To use Excel’s Solver, first build a workbook that describes your optimization-modeling problem, including its objective function and any constraints, and then tell Solver to look for an optimal solution. As long as you understand the concepts of optimization modeling, as described in the preceding EasyRefresher, this process is simple.
Setting Up Your Workbook for Solver
You take three steps to set up a workbook for solver: provide guesses of the variables that optimize your objective function, supply the objective function, and then supply the con- straint functions. Figure 6-17 shows a workbook set up to solve the example problem dis- cussed in the EasyRefresher.If you want to experiment with Solver but don’t want to construct the worksheet shown in Figure 6-17 from scratch, remember that all the workbook examples in this book are included on the companion CD.
To build this or any optimization model workbook, follow these steps:
- Optionally, tell Excel to display the actual formulas rather than results.
ou don’t have to take this step, but because with optimization modeling you’re really more interested in what the formulas look like as opposed to the results they produce, you prob- ably want to tell Excel to display formulas rather than formula results. To do this, choose the Tools menu’s Options command, click the View tab, and select the Formulas check box.Figure 6-17 displays formulas instead of formula results.
- Provide starting guesses for the variables.
You need to provide starting guesses for the variables you’re trying to optimize. You can do this simply by entering values in cells, but I recommend you create a small schedule of variable names and variable guesses, as shown in Figure 6-17 in the worksheet range A1:B3.If you set up a worksheet range like that shown in Figure 6-17—and you really should— you’ll also want to name the cells that hold your guesses. In this case, you can do this by selecting the worksheet range that holds the variable names (Lots, Houses) and guesses— A2:B3 in Figure 6-17—and then by choosing the Insert menu’s Name command and then choosing the Name submenu’s Create command. When Excel displays the Create Names dialog box, select the Left Column check box and click OK.
- Describe the objective function.
In Figure 6-17, the worksheet describes the equation with the following formula located in cell B5:=15000*Lots+20000*HousesBecause the cells holding the variable guesses have been named Lots and Houses, the objective function uses these names in place of cell references. Note that the label in cell A5 identifies the equation, but you only need to enter the actual equation shown in cell B5.
- Describe each constraint.
In Figure 6-17, the constraints are described in the worksheet range B8:C11.To describe a single constraint, you enter the constraint equation in one cell and the lim- iting constant value in another cell. For example, the working capital constraint men- tioned in the earlier EasyRefresherTM says the formula $50,000 times the number of lots plus $25,000 times the number of houses must be less than or equal to $1,200,000 (the limiting constant).To describe this first constraint, you enter the following formula in cell B8:=Lots*50000+Houses*25000and you enter the constant value which limits this formula in cell C8:
To describe the second constraint—the one that quantifies the limit on bulldozer capac- ity—you enter the following formula in cell B9:
and you enter the constant value which limits this formula in cell C9:
To describe the third constraint—which comes from your minimum-number-of-lots policy constraint—you enter the following formula in cell B10:
and you enter the constant value which limits this formula in cell C10:
Finally, to describe the fourth constraint—which comes from your minimum-number- of-houses policy constraint—you enter the following formula in cell B11:
and you enter the constant value which limits this formula in cell C11:
Once you’ve completed the preceding steps, you’re ready to use Solver to look for an opti- mal solution to your objective function.
If you set up your workbooks similar to the one shown in Figure 6-17, you will find Solver easy to use. You simply follow these steps:
- Choose the Tools menu’s Solver command.
Excel displays the Solver Parameters dialog box (see Figure 6-18).If you don’t see the Solver command on your Tools menu, it means the Solver add-in isn’t yet installed. To install Solver, choose the Tools menu’s Add-Ins command. When Excel displays the Add-Ins dialog box, scroll down the Add-Ins Available list box until you see the Solver Add-In entry. Select the Solver Add-In check box, click OK, and then if Excel requests it, provide the Excel or Office installation CD.
- Identify the objective function.
Enter the address of the cell that holds your objective in the Set Target Cell box. For example, in Figure 6-17, cell B5 holds the objective function, so you would enter B5 in the Set Target Cell box.
- Describe how Solver should optimize the objective function.
Use the Equal To option buttons to specify how Solver optimizes the objective function. In the case of a profit function, for example, you want to maximize the function so you click the Max button. This is the case for the workbook shown in Figure 6-17. If your objective function described costs, you would instead want to minimize the function and so would click the Min button. You may also have situations in which you want to have the objective function return a specific value, and so in this special case you would click the Value Of button and then provide the specified value.
- Tell Solver which cells hold your variable guesses.
Use the By Changing Cells box to tell Excel where you’ve stored the variables used in the objective function and constraint equations. In Figure 6-17, for example, the work- book stores these variables in cells B2 and B3, so you could enter these two cell addresses in the By Changing Cells box. If you’ve named the variable cells, you can also type the cell names, as shown in Figure 6-18. Cell B2 is named Lots, and cell B3 is named Houses.
- Tell Solver you want to begin describing constraints.
Click the Add button. Excel displays the Add Constraint dialog box (see Figure 6-19).
- Describe the first constraint.
To add a constraint, use the Cell Reference box to identify the cell holding the first constraint’s equation, use the unnamed operator box to select an appropriate constraint operator, and then use the Constraint box to identify the cell holding the first constraint’s constant value. In the case of the workbook shown in Figure 6-17, for example, you might do this by clicking the Cell Reference box and then clicking cell B8, by selecting <= operator, and then by clicking the Constraint box and then clicking cell C8. Figure 6-19 shows how the Add Constraint dialog box should look to specify this constraint. Click Add to add the constraint. Then repeat this task to add more constraints.The unnamed drop-down list box provides five constraint operators: <= for indicat- ing less than or equal to, = for indicating equal to, >= for indicating greater than or equal to, int for indicating that variable must be integer, and bin for indicating that a variable must be binary (equal either to 1 or 0).
- Add any implicit integer constraints.
In many optimization-modeling problems, you’ll also have implicit integer constraints. What this means, for example, is that you can’t use decimal values as part of the optimal solution. For example, you might say that you must develop an integer number of building lots or build an integer number of houses. Or restated slightly, you might say that you can’t get to the end of the year and have one of your building lots only half-done or one of your houses only partially complete. To specify an integer constraint, use the Cell Reference box to identify the variable cell that must be integer and then select the int operator from the unnamed drop-down list box. Figure 6-20 shows how the Add Con- straint dialog box looks when you specify an integer constraint. Note that you don’t enter the word integer in the Constraint box. Excel does that.
- Add any binary constraints.
In a handful of optimization modeling problems, you may also have binary constraints. A binary constraint is one in which the variable must equal either 0 or 1. To specify a binary constraint, use the Cell Reference box to identify the variable cell that must be binary and then select the bin operator from the unnamed drop-down list box.
- Tell Excel you’re done adding constraints.
To leave the Add Constraint dialog box after you finish describing your last constraint, click OK. Excel closes the Add Constraint dialog box and returns you to the Solver Parameters dialog box. Any constraints you’ve added show in the Subject To Constraints list box.If you add a constraint and later want to delete it, display the Solver Parameters dia- log box, select the constraint, and then click the Delete button. If you add a constraint and later want to edit it, display the Solver Parameters dialog box, select the constraint, and then click the Change button. When Excel displays the Edit Constraint dialog box, use it to make your changes. The Edit Constraint dialog box works like the Add Con- straint dialog box.
- Tell Excel to look for a solution.
Click the Solve button to direct Excel to look for a solution to your optimization-mod- eling problem. Excel looks for a solution and then displays the Solver Results dialog box (see Figure 6-21).
This dialog box identifies the variable values that optimize your objective function and asks what you want to do with these values.
- To tell Excel to save its solution, click the Keep Solver Solution button and click OK.
- To tell Excel to discard its solution, click the Restore Original Values button and click OK.
- To tell Excel to save its solution as a scenario, click the Save Scenario button and then provide a scenario name when prompted.
Reviewing Solver Reports
The Solver Results dialog box gives you the option of generating several reports on the optimization modeling that Solver performs. To generate these reports, click the report or reports you want when Excel displays the Solver Results dialog box (see Figure 6-21).Excel does not allow Integer constraints in Sensitivity and Limit reports. You will need to return to the Solver Parameters dialog box (see Figure 6-18) and delete the inte- ger parameters for Houses and Lots to get these reports. These reports involve graphing several parameters in several equations. When you graph a complex equation, not all of the results will have integer values.
Understanding the Answer Report
The answer report, which Excel places on a separate worksheet, provides information about how close the optimal solution is to your original guesses and about which constraints bind, or limit, optimization. Figure 6-22 shows an example answer report. At the top of the re- port, Excel compares the original objection function formula result with the objection func- tion result provided by original variable values. In Figure 6-22, for example, Excel shows the original objective function value as 425000 and the final objective function value as 440000. The Solver in this case improves the objective function by 15000.
Beneath the comparison of the original and final values of the objective function’s formula results, Excel compares the original values and final values of the variables (see Figure 6-22). This information lets you see exactly by how much Excel adjusts the variables in order to optimize your objective function.
At the bottom of the answer report, Excel analyzes the constraints by calculating the for- mula results for the constraints and then comparing these formula results to the constraint constants. This sounds like busy-work at first blush, but this information is often very use- ful in two important ways: First, you can use the Status information to see which constraints are binding, or limiting. In Figure 6-22, the binding constraint is the bulldozer hours. Second, you can use the Slack information to see how close a given constraint comes to becoming binding. In Figure 6-22, the working capital constraint shows only 25000 of slack; in other words, you have only a 2% margin of error with your working capital ($25,000 / $1,200,000).
Understanding the Sensitivity Report
The sensitivity report, which Excel also places on a separate worksheet, shows reduced gradients for the variables and the Lagrange multipliers for the constraints (see Figure 6-23). A reduced gradient value shows how the objective function would change if the vari- able value increased by 1. The Lagrange multiplier shows how the objective function would change if the constraint constant increased by 1.Some reduced gradient values and some Lagrange multipliers will always show as 0. The reduced gradient equals 0 if the variable value can’t be increased, and the Lagrange multiplier shows as 0 if a constraint constant isn’t binding.
A closer inspection of the sensitivity report shown in Figure 6-23, for example, shows that the reduced gradient values for both the Lots and Houses variables equal 0. This indicates that neither value can be increased. The sensitivity report does show Lagrange multipliers for the working capital constraint and for the bulldozer-hours constraint. The Lagrange multiplier for the bulldozer-hours limit, 78.125, indicates that a 1 hour increase in the number of bull- dozing hours available increases the objective function (your profits) by 78.125.
If you’ve created a linear optimization model—and I’ll discuss linear models briefly in the next section, “Customizing Solver’s Operation”—your sensitivity reports include several ad- ditional pieces of information, including reduced costs, shadow prices, objective coefficients, and constraint right-hand side ranges.
Understanding the Limits Report
The limits report, which Excel places on still another worksheet, shows you how much your variable values can change but still stay within your constraints (see Figure 6-24). For each variable, the limits report shows the calculated optimal value, the lowest possible value that is allowable, and the highest possible value that is allowable. In Figure 6-24—and this would often be the case—the lower limit and upper limit values equal the optimal values. This shows that these variable values can be changed without affecting the optimal solution or violat- ing constraints. Note, however, that some optimization problems do allow you to change variable values while continuing to optimize the function and continuing to stay within the stated constraints. This happens when there are multiple sets of variable values that opti- mize the equation.
Customizing Solver’s Operation
The Solver Parameters dialog box provides an Options button that you can click to display the Solver Options dialog box (see Figure 6-25). The Solver Options dialog box lets you customize the way in which Solver works out your problem. The paragraphs that follow briefly describe each of the Solver options along with how and why you might change their settings.
Max Time and Iterations
A handful of these options are essentially self-descriptive. The Max Time box, for example, lets you specify how long Solver should work on a problem, and can it be set as high as 32,767 seconds (which is over nine hours). The Iterations box lets you specify how many iterations Solver should work on a problem, and it can be set as high as 32,767.
The Precision box lets you specify how precise Solver should be in checking a possible optimal solution against your constraints. A precision setting of 0.000001, the default set- ting, tells Excel that if a constraint formula value is within 0.000001 of the constraint con- stant, it meets the constraint. You can set the Precision box to any value from 0 to 1. To loosen your precision, use a larger Precision value. To tighten your precision, use a smaller Preci- sion value. As you boost your precision, predictably, Excel takes longer to reach a solution.
The Tolerance box lets you specify how precise Solver should be in making sure that any integer constraints are met. The default Tolerance setting of 5, or 5%, means that if an objective function variable is within 5% of an integer value—from 95% to 105%, in other words—Excel can consider it to be an integer. The Tolerance setting, by the way, applies only to optimization problems that use integer constraints. As you increase your precision, predictably, Excel takes longer to reach a solution.
The Convergence box lets you indicate when Excel should stop looking for a better solu- tion. You can set the Convergence value to any fractional value between 0 and 1. When the change in the objective function is less than the value shown in the Convergence box, Ex- cel stops looking for a better solution. The Convergence setting, by the way, applies only to nonlinear optimization-modeling problems. As you reduce the convergence setting (i.e., increase the precision), predictably, Excel takes longer to reach a solution.
Assume Linear Model
If the relationships in your optimization are linear, you can select the Assume Linear Model check box. By doing this, you simplify the calculations that Excel has to make and, thereby, speed things up.
If you want to tell Excel that your variables must be equal to or greater than 0 when you haven’t set a lower limit constraint, you can select the Assume Non-Negative check box. In effect, when you check this box, you tell Excel to create another, implicit set of constraints.
Use Automatic Scaling
You should select the Automatic Scaling check box when you’re working with variables and formula results that differ in magnitude. An example of this situation is when you’re solv- ing for a rate of return (a percentage) using a set of large dollar variables.
Show Iteration Results
You can select the Show Iteration Results check box to direct Excel to pause after each calculation iteration. After each calculation iteration, Excel displays a Show Trial Solution dialog box. You can save the trial solution by clicking the Save Scenario button. Or you can continue to work toward the solution by clicking the Continue button. To terminate the iterations, click the Stop button.
You use the Estimates option buttons—Tangent and Quadratic—to choose the approach that you want Excel to use to come up with the first trial solution. Select Tangent if you want Excel to extrapolate linearly from a tangent vector. Select Quadratic if you want Excel to extrapolate quadraticly—a technique which may yield better results for nonlinear optimization-modeling problems.
You use the Derivatives option buttons—Forward and Central—to specify the differencing used to estimate partial derivatives of the objective function and constraint function for- mulas. Typically, you can click the Forward button. However, if an optimization problem can’t be solved with Forward derivatives, you can click the Central button. Using differen- tials near the center of a target often takes more calculations to solve, but can be better with highly constrained problems such as airline ticket prices.
The Search option buttons—Newton and Conjugate—let you choose the algorithm Excel uses to find an optimal solution. If your personal computer has lots of free memory, click the Newton button to reduce the number of calculation iterations (albeit at the expense of using more memory). If your personal computer doesn’t have extra memory, click the Con- jugate button to allow more time so that your computer does’t use as much memory.Changing your Search setting to Conjugate should be necessary only on large, com- plicated optimization problems.
Save Model and Load Model
The Save Model and Load Model buttons let you save an optimization model description. To save a model—such as the equations that you set up for the scenario with houses, lots, working capital and bulldozers, click the Save Model button and then specify the empty worksheet range that Excel should use to save the model. To load a model, click the Load Model button and then specify the worksheet range holding the model.
Understanding Solver Error Messages
For most simple optimization problems, Excel rather quickly finds a solution to your problem and displays the Solver Results dialog box. In more complicated problems—unfortunately, those you’re likely to encounter in real life—Excel may encounter difficulties. In these cases, it may display one of the error messages described in the following paragraphs.
Solver has converged to the current solution
This message means that while Excel has found what it appears to be a solution, there may be a better solution. To direct Excel to look for a better solution, reduce the Convergence setting using the Solver Options dialog box, as described in the preceding section, “Cus- tomizing Solver’s Operations.”
Solver cannot improve the current solution
This message indicates that Excel has calculated a rough, appropriate solution, but there may be a better solution. To direct Excel to look for a better solution, adjust the Precision setting to a larger value using the Solver Options dialog box. Again, the preceding section explains how to do this.
Stop chosen when the maximum time limit was reached
This message indicates that Excel ran out of time. You can attempt to retry solving the solutionusingalargerMaxTimesetting.TospecifyalargerMaxTimevalue,usetheSolver Options dialog box.To save the work that Excel has already performed when you encounter either this message or the next one, click the Keep Solver Results button.
Stop chosen when the maximum iteration limit was reached
ThismessageindicatesthatExcelranoutofiterations.Youcanattempttoretrysolvingthe solution using a larger Iterations setting. To specify a larger Iterations value, use the Solver Options dialog box.
The Set Target Cell values do not converge
This message indicates that the objective function continues to increase or decrease even though all the constraints are already satisfied. In other words, with each iteration, Excel gets a better objective function value, but doesn’t appear any closer to a final objective function value. If you encounter this error, review your objective function and constraints to make sure that you’ve correctly described the optimization-modeling problem.
Solver could not find a feasible solution
This message probably indicates that your optimization-modeling problem has no answer. Alternatively, this error message may suggest that you’ve incorrectly described the objec- tive function or, perhaps more likely, one or more of the constraints. In the previous model, if your working capital was limited to $600,000, you would not have enough cash to work the minimum required number of lots and houses. There would be no feasible solution.
Conditions for Assume Linear Model are not satisfied
This message indicates you selected the Assume linear model check box, which appears on the Solver Options dialog box, but Excel, after reviewing the calculation results, concludes your model isn’t linear. If you see this message, first display the Solver Options dialog box and select the Use Automatic Scaling check box. Then attempt to solve your optimization model again. If you get the message error a second time, display the Solver Options box again, but this time clear the Assume Linear Model check box. Then attempt to solve your problem again.
Solver encountered an error value in a target or constraint cell
This message indicates that one of your formulas results in an error value or that you’ve incorrectly specified an integer or binary constraint. To address this Solver problem, you need to fix the incorrect formula.The earlier section, “Using Solver,” describes one way to correctly set up integer and binary constraints.
There is not enough memory available to solve the problem
This message, as you would suspect, indicates that Excel doesn’t have enough memory to successfully run Solver. To free up memory, try closing open documents and any other open programs. You may also want to add memory to your personal computer.
Filed Under: Finance, Using ExcelTagged With: business modelingSours: https://stephenlnelson.com/articles/optimization-modeling-solver-excel/
This article discusses using Solver, a Microsoft Excel add-in program you can use for what-if analysis, to determine an optimal product mix.
How can I determine the monthly product mix that maximizes profitability?
Companies often need to determine the quantity of each product to produce on a monthly basis. In its simplest form, the product mix problem involves how to determine the amount of each product that should be produced during a month to maximize profits. Product mix must usually adhere to the following constraints:
Product mix can’t use more resources than are available.
There is a limited demand for each product. We can’t produce more of a product during a month than demand dictates, because the excess production is wasted (for example, a perishable drug).
Let’s now solve the following example of the product mix problem. You can find the solution to this problem in the file Prodmix.xlsx, shown in Figure 27-1.
Let’s say we work for a drug company that produces six different products at their plant. Production of each product requires labor and raw material. Row 4 in Figure 27-1 shows the hours of labor needed to produce a pound of each product, and row 5 shows the pounds of raw material needed to produce a pound of each product. For example, producing a pound of Product 1 requires six hours of labor and 3.2 pounds of raw material. For each drug, the price per pound is given in row 6, the unit cost per pound is given in row 7, and the profit contribution per pound is given in row 9. For example, Product 2 sells for $11.00 per pound, incurs a unit cost of $5.70 per pound, and contributes $5.30 profit per pound. The month’s demand for each drug is given in row 8. For example, demand for Product 3 is 1041 pounds. This month, 4500 hours of labor and 1600 pounds of raw material are available. How can this company maximize its monthly profit?
If we knew nothing about Excel Solver, we would attack this problem by constructing a worksheet to track profit and resource usage associated with the product mix. Then we would use trial and error to vary the product mix to optimize profit without using more labor or raw material than is available, and without producing any drug in excess of demand. We use Solver in this process only at the trial-and-error stage. Essentially, Solver is an optimization engine that flawlessly performs the trial-and-error search.
A key to solving the product mix problem is to efficiently compute the resource usage and profit associated with any given product mix. An important tool that we can use to make this computation is the SUMPRODUCT function. The SUMPRODUCT function multiplies corresponding values in cell ranges and returns the sum of those values. Each cell range used in a SUMPRODUCT evaluation must have the same dimensions, which implies that you can use SUMPRODUCT with two rows or two columns, but not with one column and one row.
As an example of how we can use the SUMPRODUCT function in our product mix example, let’s try to compute our resource usage. Our labor usage is calculated by
(Labor used per pound of drug 1)*(Drug 1 pounds produced)+
(Labor used per pound of drug 2)*(Drug 2 pounds produced) + ...
(Labor used per pound of drug 6)*(Drug 6 pounds produced)
We could compute labor usage in a more tedious fashion as D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. Similarly, raw material usage could be computed as D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. However, entering these formulas in a worksheet for six products is time-consuming. Imagine how long it would take if you were working with a company that produced, for example, 50 products at their plant. A much easier way to compute labor and raw material usage is to copy from D14 to D15 the formula SUMPRODUCT($D$2:$I$2,D4:I4). This formula computes D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (which is our labor usage) but is much easier to enter! Notice that I use the $ sign with the range D2:I2 so that when I copy the formula I still capture the product mix from row 2. The formula in cell D15 computes raw material usage.
In a similar fashion, our profit is determined by
(Drug 1 profit per pound)*(Drug 1 pounds produced) +
(Drug 2 profit per pound)*(Drug 2 pounds produced) + ...
(Drug 6 profit per pound)*(Drug 6 pounds produced)
Profit is easily computed in cell D12 with the formula SUMPRODUCT(D9:I9,$D$2:$I$2).
We now can identify the three components of our product mix Solver model.
Target cell. Our goal is to maximize profit (computed in cell D12).
Changing cells. The number of pounds produced of each product (listed in the cell range D2:I2)
Constraints. We have the following constraints:
Do not use more labor or raw material than is available. That is, the values in cells D14:D15 (the resources used) must be less than or equal to the values in cells F14:F15 (the available resources).
Do not produce more of a drug than is in demand. That is, the values in the cells D2:I2 (pounds produced of each drug) must be less than or equal to the demand for each drug (listed in cells D8:I8).
We can’t produce a negative amount of any drug.
I’ll show you how to enter the target cell, changing cells, and constraints into Solver. Then all you need to do is click the Solve button to find a profit-maximizing product mix!
To begin, click the Data tab, and in the Analysis group, click Solver.
Note: As explained in Chapter 26, "An Introduction to Optimization with Excel Solver," Solver is installed by clicking the Microsoft Office Button, then Excel Options, followed by Add-Ins. In the Manage list, click Excel Add-ins, check the Solver Add-in box, and then click OK.
The Solver Parameters dialog box will appear, as shown in Figure 27-2.
Click the Set Target Cell box and then select our profit cell (cell D12). Click the By Changing Cells box and then point to the range D2:I2, which contains the pounds produced of each drug. The dialog box should now look Figure 27-3.
We’re now ready to add constraints to the model. Click the Add button. You’ll see the Add Constraint dialog box, shown in Figure 27-4.
To add the resource usage constraints, click the Cell Reference box, and then select the range D14:D15. Select <= from the middle list. Click the Constraint box, and then select the cell range F14:F15. The Add Constraint dialog box should now look like Figure 27-5.
We have now ensured that when Solver tries different values for the changing cells, only combinations that satisfy both D14<=F14 (labor used is less than or equal to labor available) and D15<=F15 (raw material used is less than or equal to raw material available) will be considered. Click Add to enter the demand constraints. Fill in the Add Constraint dialog box as shown in Figure 27-6.
Adding these constraints ensures that when Solver tries different combinations for the changing cell values, only combinations that satisfy the following parameters will be considered:
D2<=D8 (the amount produced of Drug 1 is less than or equal to the demand for Drug 1)
E2<=E8 (the amount of produced of Drug 2 is less than or equal to the demand for Drug 2)
F2<=F8 (the amount produced of Drug 3 made is less than or equal to the demand for Drug 3)
G2<=G8 (the amount produced of Drug 4 made is less than or equal to the demand for Drug 4)
H2<=H8 (the amount produced of Drug 5 made is less than or equal to the demand for Drug 5)
I2<=I8 (the amount produced of Drug 6 made is less than or equal to the demand for Drug 6)
Click OK in the Add Constraint dialog box. The Solver window should look like Figure 27-7.
We enter the constraint that changing cells must be non-negative in the Solver Options dialog box. Click the Options button in the Solver Parameters dialog box. Check the Assume Linear Model box and the Assume Non-Negative box, as shown in Figure 27-8 on the next page. Click OK.
Checking the Assume Non-Negative box ensures that Solver considers only combinations of changing cells in which each changing cell assumes a non-negative value. We checked the Assume Linear Model box because the product mix problem is a special type of Solver problem called a linear model. Essentially, a Solver model is linear under the following conditions:
The target cell is computed by adding together the terms of the form (changing cell)*(constant).
Each constraint satisfies the “linear model requirement.” This means that each constraint is evaluated by adding together the terms of the form (changing cell)*(constant) and comparing the sums to a constant.
Why is this Solver problem linear? Our target cell (profit) is computed as
(Drug 1 profit per pound)*(Drug 1 pounds produced) +
(Drug 2 profit per pound)*(Drug 2 pounds produced) + ...
(Drug 6 profit per pound)*(Drug 6 pounds produced)
This computation follows a pattern in which the target cell’s value is derived by adding together terms of the form (changing cell)*(constant).
Our labor constraint is evaluated by comparing the value derived from (Labor used per pound of Drug 1)*(Drug 1 pounds produced) + (Labor used per pound of Drug 2)*(Drug 2 pounds produced)+ …(Labor used per pound of Drug 6)*(Drug 6 pounds produced) to the labor available.
Therefore, the labor constraint is evaluated by adding together the terms of the form (changing cell)*(constant) and comparing the sums to a constant. Both the labor constraint and the raw material constraint satisfy the linear model requirement.
Our demand constraints take the form
(Drug 1 produced)<=(Drug 1 Demand)
(Drug 2 produced)<=(Drug 2 Demand)
(Drug 6 produced)<=(Drug 6 Demand)
Each demand constraint also satisfies the linear model requirement, because each is evaluated by adding together the terms of the form (changing cell)*(constant) and comparing the sums to a constant.
Having shown that our product mix model is a linear model, why should we care?
If a Solver model is linear and we select Assume Linear Model, Solver is guaranteed to find the optimal solution to the Solver model. If a Solver model is not linear, Solver may or may not find the optimal solution.
If a Solver model is linear and we select Assume Linear Model, Solver uses a very efficient algorithm (the simplex method) to find the model’s optimal solution. If a Solver model is linear and we do not select Assume Linear Model, Solver uses a very inefficient algorithm (the GRG2 method) and might have difficulty finding the model’s optimal solution.
After clicking OK in the Solver Options dialog box, we return to the main Solver dialog box, shown earlier in Figure 27-7. When we click Solve, Solver calculates an optimal solution (if one exists) for our product mix model. As I stated in Chapter 26, an optimal solution to the product mix model would be a set of changing cell values (pounds produced of each drug) that maximizes profit over the set of all feasible solutions. Again, a feasible solution is a set of changing cell values satisfying all constraints. The changing cell values shown in Figure 27-9 are a feasible solution because all production levels are non-negative, production levels do not exceed demand, and resource usage does not exceed available resources.
The changing cell values shown in Figure 27-10 on the next page represent an infeasible solution for the following reasons:
We produce more of Drug 5 than the demand for it.
We use more labor than what is available.
We use more raw material than what is available.
After clicking Solve, Solver quickly finds the optimal solution shown in Figure 27-11. You need to select Keep Solver Solution to preserve the optimal solution values in the worksheet.
Our drug company can maximize its monthly profit at a level of $6,625.20 by producing 596.67 pounds of Drug 4, 1084 pounds of Drug 5, and none of the other drugs! We can’t determine if we can achieve the maximum profit of $6,625.20 in other ways. All we can be sure of is that with our limited resources and demand, there is no way to make more than $6,627.20 this month.
Suppose that demand for each product must be met. (See the No Feasible Solution worksheet in the file Prodmix.xlsx.) We then have to change our demand constraints from D2:I2<=D8:I8 to D2:I2>=D8:I8. To do this, open Solver, select the D2:I2<=D8:I8 constraint, and then click Change. The Change Constraint dialog box, shown in Figure 27-12, appears.
Select >=, and then click OK. We’ve now ensured that Solver will consider changing only cell values that meet all demands. When you click Solve, you’ll see the message "Solver could not find a feasible solution." This message does not mean that we made a mistake in our model, but rather that with our limited resources, we can’t meet demand for all products. Solver is simply telling us that if we want to meet demand for each product, we need to add more labor, more raw materials, or more of both.
Let’s see what happens if we allow unlimited demand for each product and we allow negative quantities to be produced of each drug. (You can see this Solver problem on the Set Values Do Not Converge worksheet in the file Prodmix.xlsx.) To find the optimal solution for this situation, open Solver, click the Options button, and clear the Assume Non-Negative box. In the Solver Parameters dialog box, select the demand constraint D2:I2<=D8:I8 and then click Delete to remove the constraint. When you click Solve, Solver returns the message "Set Cell Values Do Not Converge." This message means that if the target cell is to be maximized (as in our example), there are feasible solutions with arbitrarily large target cell values. (If the target cell is to be minimized, the message "Set Cell Values Do Not Converge" means there are feasible solutions with arbitrarily small target cell values.) In our situation, by allowing negative production of a drug, we in effect “create” resources that can be used to produce arbitrarily large amounts of other drugs. Given our unlimited demand, this allows us to make unlimited profits. In a real situation, we can’t make an infinite amount of money. In short, if you see “Set Values Do Not Converge,” your model does have an error.
Suppose our drug company can purchase up to 500 hours of labor at $1 more per hour than current labor costs. How can we maximize profit?
At a chip manufacturing plant, four technicians (A, B, C, and D) produce three products (Products 1, 2, and 3). This month, the chip manufacturer can sell 80 units of Product 1, 50 units of Product 2, and at most 50 units of Product 3. Technician A can make only Products 1 and 3. Technician B can make only Products 1 and 2. Technician C can make only Product 3. Technician D can make only Product 2. For each unit produced, the products contribute the following profit: Product 1, $6; Product 2, $7; and Product 3, $10. The time (in hours) each technician needs to manufacture a product is as follows:
Each technician can work up to 120 hours per month. How can the chip manufacturer maximize its monthly profit? Assume a fractional number of units can be produced.
A computer manufacturing plant produces mice, keyboards, and video game joysticks. The per-unit profit, per-unit labor usage, monthly demand, and per-unit machine-time usage are given in the following table:
Each month, a total of 13,000 labor hours and 3000 hours of machine time are available. How can the manufacturer maximize its monthly profit contribution from the plant?
Resolve our drug example assuming that a minimum demand of 200 units for each drug must be met.
Jason makes diamond bracelets, necklaces, and earrings. He wants to work a maximum of 160 hours per month. He has 800 ounces of diamonds. The profit, labor time, and ounces of diamonds required to produce each product are given below. If demand for each product is unlimited, how can Jason maximize his profit?
Labor hours per unit
Ounces of diamonds per unit
Excel Solver Tutorial - Step by Step Product Mix Example In Excel
To define an optimization model in Excel you'll follow these essential steps:
- Organize the data for your problem in the spreadsheet in a logical manner.
- Choose a spreadsheet cell to hold the value of each decision variable in your model.
- Create a spreadsheet formula in a cell that calculates the objective function for your model.
- Create a formulas in cells to calculate the left hand sides of each constraint.
- Use the dialogs in Excel to tell the Solver about your decision variables, the objective, constraints, and desired bounds on constraints and variables.
- Run the Solver to find the optimal solution.
Within this overall structure, you have a great deal of flexibility in how you choose cells to hold your model's decision variables and constraints, and which formulas and built-in functions you use. In general, your goal should be to create a spreadsheet that communicates its purpose in a clear and understandable manner.
Creating an Excel Worksheet
Assuming that you have organized the data for the problem in Excel, the next step is to create a worksheet where the formulas for the objective function and the constraints are calculated. Because decision variables and constraints usually come in logical groups, you'll often want to use cell ranges in your spreadsheet to represent them.
In the worksheet below, we have reserved cells B4, C4, D4 and E4 to represent our decision variables X1, X2, X3, and X4 representing the number of pallets of each type of panel to produce. The Solver will determine the optimal values for these cells. (Click on the worksheet for a full-size image.)
Notice that the profit for each pallet of panels ($450, $1,150, $800 and $400) was entered in cells B5, C5, D5 and E5, respectively. This allows us to compute the objective in cell F5 as:
Formula for cell F5: =B5*B4+C5*C4+D5*D4+E5*E4
Formula for cell F5: =SUMPRODUCT(B5:E5,B4:E4)
In cells B8:E11, we've entered the amount of resources needed to produce a pallet of each type of panel. For example, the value 15 in cell C9 means that 15 hours of pressing is required to produce a pallet of Pacific style panels. These numbers come directly from the formulas for the constraints shown earlier. With these values in place, we can enter a formula in cell F8 to compute the total amount of glue used for any number of pallets produced:
Formula for cell F8: =SUMPRODUCT(B8:E8,$B$4:$E$4)
We can copy this formula to cells F9:F11 to compute the total amount of pressing, pine chips, and oaks chips used. (The dollar signs in $B$4:$E$4 specify that this cell range stays constant, while the cell range B8:E8 becomes B9:E9, B10:E10, and B11:E11 in the copied formulas.) The formulas in cells F8:F11 correspond to the left hand side values of the constraints.
In cells G8:G11, we've entered the available amount of each type of resource (corresponding to the right hand side values of the constraints). This allows us to express the constraints shown earlier as:
This is equivalent to the four constraints: F8<=G8, F9<=G9, F10<=G10, and F11<=G11. We can enter this set of constraints directly in the Solver dialogs along with the non-negativity conditions:
B4:E4 >= 0Click on the links below to see how this model can be solved using Excel's built-in Solver (or Premium Solver) or with FrontLine Systems' flagship Risk Solver Platform product.
< Back to: Product Mix Example
<< Back to: Tutorial StartNext: Using Excel's Solver >
Next: Using Risk Solver Platform >
Optimization model excel
Still Using Excel for Optimization Modeling? Stop.
Recently, I found myself in exactly this situation. As you might imagine, the golf was not entirely occupying my attention, and I found myself catching up on new social media content. In particular, I was drawn to a thread on my B-school group page discussing the woes of building optimization models in Excel.
Someone suggested my old classmate review a series of videos in which the author walks through the process of building an optimization model using Excel’s Premium Solver. Given my background in prescriptive analytics, I could not resist watching a few myself.
Before I knew it, the golf tournament was over, 60Minutes had less than a half hour left, and I was more convinced than ever that not only is Enterprise Optimizer® more dynamic than Exceland other programming-based systems, it’s easier to use.
The Downfall of Excel's Premium Solver
Each video I watched was focused on solving a simple set of decision variables within a single functional silo. One model solved for product mix. Another solved for shipment volume between plants and warehouses, another solved for production volume within a plant, and yet another solved for optimal procurement mix.
None of the models were integrated despite the fact that these critical planning decisions are most certainly inter-related in the real-world. Worse yet, each video stepped through an incredibly arduous equation-building process and resulted in a one-trick pony that very few business decision makers would be able or have the time to comprehend.
Most poignant was the fact that all of these examples took at least twelve minutes to build. I couldn’t help but consider:
- How long would a real-world model used to optimize one of these silo-based decisions take to build?
- How would the modeler communicate the equation-based rationale to key decision makers?
- How valid would the results be (e.g. optimal product mix) without accounting for all the inter-related constraints and marginal economic impacts?
Business Optimization Modeling Example
Enterprise Optimizer Does More than Excel ... in Half the Time
In the above video, I model a theoretical chemical company in River Logic’s code-free optimization modeling platform, Enterprise Optimizer®.
The model simultaneously optimizes a series of inter-related planning decisions:
- Product Mix – How much of which products to sell into each customer segment/market?
- Order Allocation – How much of each product to produce on each reactor?
- Procurement – How much of each material to purchase from each vendor?
Not only does the model solve for each set of decision variables simultaneously, the planned level of combinations are dynamically translated into a full set of audit-quality financials for the firm, and a proprietary solution output metric used to quantify the marginal economic value of binding constraints in true business terms.
Could it get any better, you ask? It does! I was able to use Enterprise Optimizer® drag-and-drop visual modeling language to build, populate, and solve the entire model in five minutes without writing a single equation!
And for even more information on the benefits of EO, you can download our Optimization product comparison below.
Solve problems with linear programming and Excel
Proving its value
Linear programming, as demonstrated by applying Excel's Solver feature, is a viable and cost-effective tool for analysing multi-variable financial and operational problems.
In the example, it was unclear at the outset what the optimal production quantity of each washing machine was given the stated objective of profit maximisation. An intuitive response might have been to focus all production on the washing machine that provides the greater profits per unit (ie, Arkel). However, because of the resource constraints in our example, following such an intuition would not have led to a situation where profits are maximised. Instead, relying on linear programming to analyse the business problem leads to a production mix that definitively maximises profits. While this example is simple, it is reflective of many more complex real-life scenarios in which accountants face situations that require them to fulfil a variety of business objectives while contending with practical constraints. Where required, the modelling can be scaled up to deal with more complicated business problems.
Limitations of linear programming
Linear programming is one of several optimisation techniques that can be employed to determine the most efficient way to use resources. While it is a powerful technique that can be applied to many business situations, it should only be used to solve optimisation problems that involve a single linear objective function and linear constraints that cannot be violated.
There may be situations where linear programming may not be the most appropriate optimisation technique to employ. For example, where optimisation problems involve multiple objectives, nonlinear objective functions and/or constraints, or soft constraints (that can be violated) rather than hard constraints (that cannot be violated), other more appropriate optimisation techniques such as multiple objective linear programming, goal programming, or nonlinear programming should be identified and employed instead.
Clarence Goh, CA (Singapore), Ph.D., is an assistant professor of accounting (practice) and director of professional development for the School of Accountancy at Singapore Management University. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at [email protected]
You will also be interested:
- The doomsday heist
- Great meme war veteran
- 2003 chevy cavalier windshield wipers
- Fortnite hitching pc
- Boat propellers ebay
- Enderman xbox one controller
- Rockwall isd salary
- Isuzu snowflake wheels