Using Excel Solver

What-if scenarios are useful business tools. They can help business-owners and other decision makers analyze the efects of their actions before they are implemented. Fortunately, Microsoft Excel offers decision makers a useful tool, called Solver, to help users explore what-if scenarios.

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.

You may like these other stories...

While reputational risk is the No. 1 nonfinancial concern among corporate directors, cybersecurity/IT risk is gaining steam. In fact, both private companies and organizations with more than $1 billion in revenue felt they...
We've all been there. Trying to make our work-lives more efficient, transfer knowledge to newer team members, and leverage our practices. Sometimes it works, and sometimes, well, the result is embarrassing at best.Here...
From May 20-23, the Association for Accounting Marketing (AAM) held its annual conference. Frequent contributor Sally Glick picked up some ideas that she will be sharing with us in the coming days, as she has done in...

Upcoming CPE Webinars

Jul 31
In this session Excel expert David Ringstrom helps beginners get up to speed in Microsoft Excel. However, even experienced Excel users will learn some new tricks, particularly when David discusses under-utilized aspects of Excel.
Aug 5
This webcast will focus on accounting and disclosure policies for various types of consolidations and business combinations.
Aug 20
In this session we'll review best practices for how to generate interest in your firm’s services.
Aug 21
Meet budgets and client expectations using project management skills geared toward the unique challenges faced by CPAs. Kristen Rampe will share how knowing the keys to structuring and executing a successful project can make the difference between success and repeated failures.