Excel 2024: Find Optimal Solutions with Solver

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 Fylstra runs Frontline Systems. His company wrote the Solver used in Excel. Frontline Systems 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 Add-in.

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 deal with only 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 in the figure below. 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/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 problem 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 to get more Sunday employees. I ended up with something that works: 38 employees and $2,584 of weekly payroll.

Of course, there is an easier way to solve this problem. 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 with 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 click Solve. In a few moments, Solver presents one optimal solution.

Solver finds a way to cover the amusement park staffing by using 30 employees instead of 38. The savings per week is $544 or more than $7000 over the course of the summer.

---

---

Notice the five stars below Employees Needed in the figure above. The schedule that Solver proposed meets your exact needs for five of the seven days. The by-product 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 gave 18 people 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 head count 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 move some workers from the Monday, Tuesday row to the Wednesday, Thursday row. I keep manually plugging in different combinations and come up with the solution shown below 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 absences 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 that are more complex than Solver can handle, check out the premium Excel solvers available from Frontline Systems: mrx.cl/solver77.

Thanks to Dan Fylstra and Frontline Systems for this example. Walter Moore illustrated the XL roller coaster.