In this blog, we discuss the following:
What is the Solver? Why Use It?
Excel has an endless amount of built-in components that can be essential tools for planning and strategizing. The feature we are going to examine is called the Solver. Solver is a Microsoft Excel add-in program you can use for what-if analysis. We use Solver to find an optimal (maximum or minimum) value for a formula in one cell — called the objective cell — subject to constraints, or limits, on the values of other formula cells on a worksheet. Excel Solver is an optimization tool that can be used to determine how the desired outcome can be achieved by changing the assumptions in a model. Similar to in our last blog, we went over Goal Seek. It allows you to work with related figures and determine how much only one value or variable would need to change in order for another to meet a goal.
The Solver and Goal Seek in Excel can be used for any situation. However, the situations for goal seek is that it can only change one variable at a time. Here are a few scenarios and questions about where we can find ourselves finding these tools useful.
How can we maximize our profit by slightly altering price and costs and taking into account specific constraints for costs and price; such that price cannot go above a certain level and costs cannot go below a certain point.
How can we distribute the remaining budget dollars among our different projects based on certain constraints?
How can we forecast to increase net income while staying at reasonable rates and hours?
Steps to Use Goal Seek in Excel
Here's a video on how to use Solver to increase net income in Excel.
Before even jumping to starting up a profit and loss data table, here's how to enable and load the Solver add-in to your Excel for the Mac and PC. Once you've added that in and created your table with a goal and constraints. The below steps and data are based on the video tutorial above, please refer to it for the formulas.
On the Data ribbon, head over to the far right to the Analyze box and click Solver
When the Parameters box appears, make “Set Objective” equal to the Net Profit (the objective is the cell we want to change)
Set “To Value Of” equal to the output you want to achieve which is $12,500 (type the number in, for more options you can click Max or Min)
Set “By Changing Variable Cells” equal to the assumption you want to solve for (the current hours and rates)
"Subject to the Constraints" is where you can click Add and the limitations
The "Add Constraint" pop up window should appear, for "Cell Reference" click on the current value of the hours (300) which should be less than or equal to (<=) the constraint hours (500)
Do the same for the rates and click OK
Press OK
Comments