Here is how you can use the Solver add-in in Excel to determine the best solutions for your calculations…..
(Note: Video version above, text version below)
Text version (click on the images for a better view):
In a previous post we looked at Goal Seek to see how Excel can be used to come up with a solution based on a simple set of criteria. Excel’s Solver provides a more elaborate structure for those more complex circumstances.
If you don’t have the Solver add-in installed go to File=>Options=>Add-Ins and at the bottom click Go next to Manage Excel Add-ins:
Make sure Solver is checked and click OK. Excel will install the Solver option in your Data tab on the ribbon:
Here is our scenario: We have two product lines. Each one produces an item with a material and labor cost. Each also has a Max Labor that indicates the most labor that can be used by that line in a day. Our goal is to use both production lines in a quantity that generates the most profit for our manufacturing plant. I’ve included in Column D the formulas for Column C (Column B’s formulas are similar). You can see how the selling price, profit and total labor are calculated:
We want Excel to tell us what the right quantity is for each line to give us the maximum overall profit.
When we click on the Solver button we get this pop-up:
In the Set Objective area, we want to enter cell $B$12, which is the Max Profit cell. We then have 3 choices: do we want a Max, Min, or Value Of: and fill in the blank with a value? Our choice will be Max, since we want to maximize our profits (and who wouldn’t!). We are then asked what cells will be changed to accomplish this? That would be cells $B$5:$C$5, the Qty/Day cells. Our parameters so far should look like this:
Now we need to add our constraints. For our example, we have four constraints:
Cell B5 must be an integer – we don’t want to produce any partial products
Cell C5 should also be an integer.
Cell B8 – Total Labor for Product A – cannot exceed 3,000, which is in cell B4.
Cell C8 also cannot exceed 3,200, which is in cell C4.
We accomplish the entry of these four constraints by clicking on the Add button and entering the appropriate information. Here is an example of the entry of two of them:
Click Add after the first 3, but click on OK after the last one. You should then see the following:
You can now see your Objective, the cells with the variables and the constraints. Click on Solve, and Excel will generate the answer:
Excel has determined that the most profitable use of these production lines would be 107 for A and 100 for B. Note that the Total Labor for A is 2,996, which is 4 less than the Max Labor, while for B it is 3,200, which hit the limit.
Next, click on Answer under Reports and click OK. Excel will create an additional tab with a recap of the solution:
And that’s Solver! It can be used for very complex calculations. The key is setting up your structure properly in the beginning. Good luck and Happy Excelling!