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

At long last, the Obama administration issued draft instructions along with revised draft tax forms that provide companies guidance on how to comply with the Affordable Care Act’s (ACA) employer mandate.The US Treasury...
Event Date: October 23, 2014, 2 pm ETAmber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.  By offering measurable results on...
Event Date: September 18, 2014, 2 pm ETIn this course, Amber Setter will shine the light on different types of leadership behavior- an integral part of everyone's career.  By examining good, bad, and ugly leadership...

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 18
In this course, Amber Setter will shine the light on different types of leadership behavior- an integral part of everyone's career.