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

Accountants without a succession plan are hurting not only themselves but their clients as well. Here are seven ways to see your practice continues after you retire—some of them are better than others.What Are Your...
In my last article, I discussed the model of value pricing and the benefits this billing structure offers you and your clients. However, in order to set up the right value pricing for your client, you need to know what...
Remember the old joke about the devil showing a guy around Hell? There were great parties, swimming pools, and sumptuous food. The guy liked what he saw, lived a bad life and went to Hell when he died. Upon arrival the devil...

Already a member? log in here.

Upcoming CPE Webinars

Sep 9
In this session we'll discuss the types of technologies and their uses in a small accounting firm office.
Sep 10
Transfer your knowledge and experience to prepare your team for the challenges and opportunities of an accounting career.
Sep 11
This webcast will include discussions of commonly-applicable Clarified Auditing Standards for audits of non-public, non-governmental entities.
Sep 24
In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.