Join Dennis Taylor for an in-depth discussion in this video Using Solver, part of Excel 2016 Essential Training.
- In the previous movie which you might have seen, called Goal Seek, we were able to change the result of a formula by changing one of the formula's components; in other words, one of the cells that contributes to that formula. A more sophisticated version of Goal Seek is called Solver, and ultimately, it's very sophisticated. We've got a worksheet here, and there's a total here, and the total is based on what's happening in a lot of different cells. Let's imagine that we want a different total here. We're the optimistic sort, maybe, and we're envisioning that this first quarter profit really should be, or could be, 265,000.
The feature called Solver will allow us to come up with a new total based on not just one cell changing, but maybe many cells. If you were to review this worksheet, you would see there are formulas in column C, February entries here, that are based on entries in column B, and the column D entries are based on entries in column C, so ultimately, the data that we see here is based on what's happening in column B. These two numbers are significant, as are these three down here.
So, if we want to get a different total here, we can have the Solver feature actually allow these numbers to change. We can also do it in such a way that they can only change within certain limits, so it sounds somewhat complex and ultimately, it is. In order to use Solver, we go to the Data tab, and it will appear off to the right if you have it installed. It's what we call an Add-in, and in order to get to Add-ins, you go to the File tab in the ribbon, down to Options, and then choose Add-ins.
And in this list, and it may vary in appearance, you will see, eventually, Solver Add-in. And then down below, Manage Excel Add-ins, click Go. Now you only need to do this once and it doesn't take very long, typically. Click Go, you'll see a list here. There's the Solver Add-in. Click OK. Now if the Data tab is active, as it is in the background there, you're about to see this appear in the upper right. I'll click OK. It'll take a few seconds, and there it is, Solver.
Large dialog box here. We are about to set this total here. Now, I've repeated this total also in cell A2. A2 is simply picking up the data from E14, so we can use either one. I'm using E14 here. That's good enough. We want to set that cell to a Max, a Minimum, or a Value Of, and I'm going to put in 265,000. We want that new total to be 265,000. By changing variable cells.
I'm going to click in here. Let's say we're going to change these two cells right here. That's B5 and B6, our Sales Revenue, our Shipping Revenue, comma, and also the Cost of Goods. Now I could do the other two, Freight and Miscellaneous, but I don't want this to take too long. Here are the cells that we're going to change, but, subject to these constraints. In other words, we're going to add some Constraints. The first constraint is that that cell B5, and we can click on the panel where we see Cell Reference, then click cell B5.
We can allow this to grow, but keep it less than or equal to. We've got some other options out here, but make this be less than or equal to 139,000. Let's say that we've analyzed sales and other factors in the market, and we've recognized that possibly, based on what we're assuming, that the Sales figure here, this projection of 137,000, could be larger, but we do want to realistic about it. It can get bigger, but not that much bigger, so we put a cap on it of 139,000.
Let's add another Constraint. Let's going to Shipping Revenue. Let's say, again, optimistically, this could be larger, but no more than 28,000, so put a Constraint out there, a cap of 28,000, and let's add another Constraint. And earlier we had also included the cell B10. This is Cost of Goods, and once again, taking the optimistic approach, we're saying, we hope this drops, but this time we'll change the arrow to be greater than or equal to.
Let's say that we can allow this to drop, but certainly not below 75,000, so the amount that's going to be used here must be 75,000 or more. Okay, we've got our Constraints in place, so here's what we're saying. We want this total, the 258,760, right now it is, we want that go to 265,000. Subject to the Constraints of cell B5 growing but no more than 139,000, B6 growing but no more than 28,000, and B10 shrinking, but certainly at 75,000 or above.
Let's click Solve. Solver found a solution, and we see our numbers in the background, 265,000. By the way, I've tried this any number of different times and sometimes, we get a message, Solver could not find a solution, and the answer in the background might be something, like in this example, but it might be 264,997. I think in those cases, you'd say, "well, close enough." We can keep the Solver solution, we can Save the Scenario. Let's Return to the Solver Parameters Dialog box, and also click OK.
Now, the original number over in cell B5 was 136,000 and something. I could have copied those ahead of times but these numbers have changed. We don't necessarily know how. We could have analyzed that if we wished. We could click OK here. We're back to here. Now, where I can't claim any kind of expertise is under Options. This is ultimately a sophisticated mathematical modeling tool. If you click Options, you'll see a dialog box with GRG Nonlinear.
I'm not sure what that means. You'll see Evolutionary. There're all kinds of other features to potentially explore. So, ultimately, this is, as I said, a sophisticated mathematical modeling tool, and if you do want more information on this, as I close this here, off to the right I've depicted a web address you might want to go to, www.solver.com. Solver is a classic Add-in purchased by Microsoft many years ago, and that will give you more information on how this feature works, but I think you saw in the example here the potential of this, and I believe, ultimately, you can have something like 32 different cells that could be changing here that you could feed into this total that you might want.
You will not see Solver here, on the Data tab, until you install it, as we did earlier, but I think you can see the potential in a great feature, very sophisticated.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros