Introduction to Solver
September 27, 2017 - by Bill Jelen
Solver has been a free add-in since the days of Lotus 1-2-3
Excel was not the first spreadsheet program. Lotus 1-2-3 was not the first spreadsheet program. The first spreadsheet program was VisiCalc in 1979. Developed by Dan Bricklin and Bob Frankston, VisiCalc was published by Dan Fylstra. Today, Dan runs Frontline Systems. His company wrote the Solver used in Excel. It has also developed a whole suite of analytics software that works with Excel.
If you have Excel, you have Solver. It may not be enabled, but you have it. To enable Solver in Excel, press Alt + T followed by I. Add a checkmark next to Solver.
To successfully use Solver you have to build a worksheet model that has three elements:
- There has to be a single Goal cell. This is a cell that you either want to minimize, maximize, or set to a particular value.
- There can be many input cells. This is one fundamental improvement over Goal Seek, which can only deal with one input cell.
- There can be constraints.
Your goal is to build the scheduling requirements for an amusement park. Each employee will work five straight days and then have two days off. There are seven different possible ways to schedule someone for five straight days and two off days. These are shown as text in A4:A10. The blue cells in B4:B10 are the input cells. This is where you specify how many people you have working each schedule.
The Goal cell is total Payroll per Week, shown in B17. This is straight math: Total People from B11 times $68 salary per person per day. You will ask Solver to find a way to minimize the weekly payroll.
The red box shows values that will not change. This is how many people you need working the park on each day of the week. You need at least 30 people on the busy weekend days – but as few as 12 on Monday and Tuesday. The orange cells use SUMPRODUCT to calculate how many people will be scheduled each day based on the inputs in the blue cells.
The icons in row 15 indicate whether you need more people, or fewer people or whether you have exactly the right number of people.
First, I tried to solve this without Solver. I went with 4 employees each day. That was great, but I did not have enough people on Sunday. So, I started increasing schedules that would give me more Sunday employees. I ended up with something that works: 38 employees and $2,584 of weekly payroll.
Click the Solver icon on the Data tab. Tell Solver that you are trying to set the payroll in B17 to the minimum. The input cells are B4:B10.
Constraints fall into obvious and not-so-obvious categories.
The first obvious constraint is that D12:J12 has to be >= D14:J14.
But, if you tried to run Solver now, you would get bizarre results where you have fractional numbers of people and possibly a negative number of people working certain schedules.
While it seems obvious to you that you can’t hire 0.39 people, you need to add constraints to tell Solver that B4:B10 are >= 0 and that B4:B10 are integers.
Choose Simplex LP as the solving method and choose Solve. In a few moments, Solver presents one optimal solution.
Solver found a way to cover the amusement park staffing using 30 employees instead of 38. The savings per week is $544 – or more than $7,000 over the course of the summer.
Notice the five stars below Employees Needed. The schedule that Solver proposed meets your exact needs for five of the seven days. The byproduct is that you will have more employees on Wednesday and Thursday than you really need.
I can understand how Solver came up with this solution. You need a lot of people on Saturday, Sunday, and Friday. One way to get people there on those day is to give them Monday and Tuesday off. That is why Solver put 18 people with Monday and Tuesday off.
But just because Solver came up with an optimal solution does not mean that there are not other equally optimal solutions.
When I was just guessing at the staffing, I didn’t really have a good strategy.
Now that Solver has given me one of the optimal solutions, I can put on my logic hat. Having 28 college-age employees on Wednesday and Thursday when you only need 15 or 18 employees is going to lead to trouble. There won’t be enough to do. Plus, with exactly the right headcount on five days, you will have to call in someone for overtime if someone else calls in sick.
I trust Solver that I need to have 30 people to make this work. But I bet that I can rearrange those people to even out the schedule and provide a small buffer on other days.
For example, giving someone Wednesday and Thursday off also ensures that the person is at work Friday, Saturday, and Sunday. So, I manually moved some workers from the Monday, Tuesday row to the Wednesday Thursday row. I kept manually plugging in different combinations and came up with this solution which has the same payroll expense as Solver but better intangibles. The overstaff situation now exists on four days instead of two. That means you can handle call-offs on Monday through Thursday without having to call in someone from their weekend.
Is it bad that I was able to come up with a better solution than Solver? No. The fact is that I would not have been able to get to this solution without using Solver. Once Solver gave me a model that minimized costs, I was able to use logic about intangibles to keep the same payroll.
If you need to solve problems more complex than Solver can handle, check out the premium Excel solvers available from Frontline Systems: http://mrx.cl/solver77.
Thanks to Dan Fylstra and Frontline Systems for this example. Walter Moore illustrated the XL roller coaster.
Watch Video
- Solver has been a free add-in since the days of Lotus 1-2-3
- Solver is a product of Visicorp founder Dan Fylstra
- Solver in your Excel is a smaller version of heavy-duty solvers
- Learn more about pro solvers: http://mrx.cl/solver77
- To install Solver, type Alt + T then I. Check Solver.
- Solver will be found on the right side of the Data tab
- You want to have an objective cell that you are trying to minimize or maximize.
- You can specify multiple input cells.
- You can specify constraints, inluding some that you would not expect:
- No half-people: Use INT for Integer
- Solver will find an optimal solution, but there might be others that are ties
- Once you get the Solver solution, you might be able to tweak it.
Video Transcript
Learn Excel from MrExcel podcast, episode 2036 - Intro to Solver!
Alright, I'm podcasting this entire book, click the “i” on the top-right hand corner to get to the playlist, where you can play all the videos!
Welcome back to MrExcel netcast, I’m Bill Jelen. We talked about some What-If analysis recently, like Goal Seek, you know, with one input cell that you're changing, but what if you have something more complex? There's a great tool called Solver, Solver’s been around for a long time, I guarantee if you have Excel and you're running on Windows, you have Solver, it's just probably not turned on. So to turn it on, you have to go to Alt T and then I, so T for Tom, I for ice cream, and check this box for Solver, click OK, and after a couple seconds, you'll have a Solver tab out here on the right-hand side. Alright, and we're going to set up a model here that solver might be able to solve, we have an amusement park, we're trying to go out how many employees to schedule. Everyone works five consecutive days, so there's really seven possible schedules where you're off, Sunday Monday, Monday Tuesday, Tuesday Wednesday. We have to figure out how many employees to put on each of those schedules.
And so just simple little math here, doing some SUMPRODUCTs, number of employees times Sunday to figure out how many people were there on Sunday, Monday, Tuesday, Wednesday. And what we've learned through operating this amusement park is we need a lot of people on Saturday and Sunday. 30 people on Saturday and Sunday, during the week Monday, Tuesday, kind of slow, 12 staff will be able to do it. Alright so, just by coming along here and just screwing around, you know, trying to figure out the right numbers, you can just keep plugging things in, but with seven different choices, it would take forever, alright so.
Now in Solver, what we have is, we have a series of input cells, and in the free version of Solver I think you can have, is it a hundred? I don't know, there's some number, and if you have to go beyond that, there's a Premium Solver that you can get from Frontline Systems. Alright, so we have some input cells, we have some constraint cells, and then you have to bring it all down to a final number. So in my case, I'm trying to minimize payroll per week, so that green number is what I want to try and optimize, alright, so here's what we're going to do!
Solver, here's the objective cell, it’s the green cell, and I want to set that to a minimum value, figure out the staffing that gets me the minimum value, by changing these blue cells. And then here are the constraints, alright, so the first constraint is that the schedule total has to be >= the red section, and we can do all that as a single constraint. Watch how cool this is, all of these cells have to be >= these corresponding cells here, awesome, click Add, alright, but then there's other things that you wouldn't think of. For example, Solver at this point might decide that it's best to have 17 people on this schedule, 43 people on the schedule, and -7 people on this schedule. Alright, so we have to tell Solver that these input cells have to be an integer, click Add. And also, we can't have someone not show up, and they'll give us their salary back, right? So we're going to say these cells have to be >=0, click Add, we go back now, we have our three constraints there.
There's three different ways to solve, and this one it follows linear math, so we can just go Simplex LP. If this one doesn't work, then by all means try the other two, I've had cases where the Simplex says it can't find a solution, and one of the other two work. Frontline Systems has great tutorials on Solver, I'm just trying to get you through your first one here today, I don't proclaim to be a Solver expert. Once I had a Solver that wouldn't work, and I sent a note into Frontline Systems, and wow, I got this awesome 5-page letter back, right, from Dan Fylstra himself, the president of Solver! And it started out: “Dear Bill, great to hear from you!” And then went on for 4.9 pages, that was all pretty much completely above my head, alright. But you know, I know enough about Solver to get through this, alright, so we're going to click here on Solve, it found a solution, “All Constraints and optimality conditions are satisfied.” I'm going to keep that, I can create some reports, don't need to do that right now. Oh, I can actually save a scenario, I made fun of scenarios yesterday, maybe Solver would be able to create a new scenario for me, so we'll click OK.
Alright, and sure enough it's saved us money, we wrote 2584 before, and now it got us down to 2040. So we need a lot of people off on Monday and Tuesday, alright, some people, 2 people off on Wednesday Thursday, and then Friday Saturday. Well, this is awesome, I never would have just randomly come up with this set of answers, alright, but does that mean it's the best answer? Well, it means that it's the minimum payroll, but I can probably come up with a different set of answers that would still have this minimum payroll. There's other ways to do that, that might be a slightly better schedule. Like for example, right now we have 28 people on Wednesday and Thursday, when we only need 15 and 18, that's a lot of people. Think about who works at amusement parks, these are college kids on home for break, this is going to be trouble if we have that many extra people. And on Monday Tuesday, we’re dead even, exactly where we want to be. So that means if anyone I'm going call off sick, now we're going to have to, you know, call someone in and pay them time and a half, because they've already worked five other days.
Alright, so just with some simple little math here, if I would take 8 away from Monday Tuesday, and make it 10, and take those 8 and add them to Wednesday Thursday, alright. Now I have a Solver solution with the exact same answer, 2040, they got the right number of people. I just balance out the schedule, and now we have 8 extra, 8 extra, 3 extra, and 2 extra, and exactly what we need on the weekend which are, you know, the full staff scenario. To me, this is slightly better than what Solver came up with, does that mean that solver failed? No, absolutely not, because I never would have gotten this close without Solver. Once Solver gave me the answer, yeah, I was able to tweak it a little bit and get there, alright. Tip #37, “40 Greatest Excel Tips of All Time”, getting near the end of that first 40, great little introduction to Solver. The guide to all of the podcasts in this series is here, “MrExcel XL - 40 Greatest Excel Tips of All Time”, you can have the e-book for just $10, print book for $25, click the “i” on the top-right hand corner!
Alright, recap: Solver, if you're in Windows versions of Excel, Lotus 1-2-3, it's there, it's created by Visicorp founder Dan Fylstra. It's a free version of the heavy-duty solvers, here's a link to go check out the heavy-duty solvers, that'll be down in the YouTube comments. It's probable they're just not installed, Alt T I, check mark Solver, look on the right side of the Data tab to find Solver. Alright, you have to have an objective cell that you're trying to minimize or maximize or set to a value, one range of input cells. Specify constraints, including something wouldn’t expect, like I had to say “No half-people” and “No negative people”. Solver will find the optimal solution, but there might be others that are ties and you might be able to tweak it to get a better solution.
Alright, there you have it, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Download File
Download the sample file here: Podcast2036.xlsx
Title Photo: Photo-Mix / Pixabay
