Using Excel Solver
Using Excel Solver, you can find the minimum or maximum value for a single cell, by modifying other cells that are directly or indirectly related to the target formula. The minimum or maximum value that is found by modifying other cells is called a target cell and the target formula resides in this cell. Constraints can be applied to restrict the values of the target cell using constrained cells. Solver adjusts the values in the specified adjustment cells. Solver commands are in a group called what-if analysis tools.
Let’s use Solver to calculate a maximum value of one cell by changing the value of another formula-related cell. The formulas in the spreadsheet must be related or no changes will occur. To start this Solver example, open a “what-if” spreadsheet used to calculate maximum profit based on certain spending levels such as advertising.
You must first set the target cell. Click Solver under the Tools pull-down menu. Enter the cell designation of the target cell in the Set target cell box. Since we are maximizing profit, choose the Max option. Enter the cell designation of the cell that will be changing in the By changing cells box. You can also designate a range of cells for this value.
Add a constraining value. The default value for the relationship is less than or equal to. Enter the constraining value in the box next to the Relationship box. Click OK to save your entries and click Solve to start the Solver.
You may want to change a value such as a your constraining value. Click Solver again and ensure the constraint formula is selected. Click Change and modify your constraint value. Click OK and then Solve to continue. Click Keep solver solution and then OK to retain the displayed results showing on the spreadsheet now.
You may want to save your solution by choosing Save Model in the Solver Options dialog box. You need to save each problem in your spreadsheet. Click Restore original values to get rid of the resulting values. Click OK to display the initial values of the cells.
Microsoft includes an Excel file named Solvsamp.xls with every hard installation of the application. The spreadsheet goes through a complete explanation of several interrelated examples using Microsoft Excel Solver.