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...

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...
Success, for a practitioner in a busy CPA firm, requires the ability to handle multiple tasks effectively. To get everything done, CPAs typically track their agenda with a "to do" list or other open-item systems to...
Everyone loses clients. You've seen the statistics. Clients and heirs often change accountants, attorneys, and advisors after a death or divorce. That's understandable. What about ongoing relationships when the...

Upcoming CPE Webinars

Jul 24
In this presentation Excel expert David Ringstrom, CPA revisits the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both boost the integrity of your spreadsheets, but reduce maintenance as well.
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.