In a previous post, I outlined how you can use the ‘Solver’ add-in to optimise decision on profit based on a single-item revenue and cost structure, and the optimal mix of inputs for six products to maximise profit. Whilst hearing about one of my bosses discussing allocating people into groups recently, I was reminded about numerous situations in which all of my previous jobs required some kind of task or staff allocation.

Throughout my professional career, I am always looking for ways to integrate my Economics and Commerce degrees into any facet of my jobs. I was able to analyse student progression on the margin, maximise educational learning based on classroom time constraints, and assist with departmental budget limitations to best satisfy student learning. But I never had to really consider any allocation or staffing maximisation, even though I thought about it from time to time when considering relief staffing.

To get us started, let’s do a brief review of how we can use Excel’s Solver. This Excel file can be downloaded here.

Example 1: Linear Programming Optimisation

Let’s say we have a 3-dimensional objective function:

P = 3x₁ + 2x₂ – x₃

With 3 constraints:

x₁ + 3x₂ + x₃ =< 9

2x₁ + 3x₂ – x₃ >= 2

3x₁ – 2x₂ + x₃ >= 5

x₁ >= 0, x₂ >= 0, x₃ >= 0

Instead of having to draw each individual function, which is possible, let’s let Excel do the heavy lifting. I’ll start by adding in the variables into Excel:

Next, I’ll create the objective function:

=3*B2+2*B3-B4

The last spreadsheet prep will be adding the constraints:

=B2+3*B3+B4

=2*B2+3*B3-B4

=3*B2-2*B3+B4

=B2

=B3

=B4

Now, I’ll kick up Solver, under the Data tab in the Ribbon:

When it’s all set up, click Solve:

Although cells B3:B4 show these values, we can take the assumption that they are close enough to 0 to be classified as 0.

Now that we’ve reviewed the process and understand how we can formulate maths problems using Solver, let’s transition to a typical kind of Transport problem.

Example 2: Transport Problem

In this particular problem, we are attempting to discover how many units of product to ship from each factory to each distribution centre to minimise the shipping costs. All orders, 6,000, need to be fulfilled and can’t exceed the available inventory at each factory.

This first transportation matrix shows the shipping cost between each factory and distribution centre, the inventory available at each factory (supply constraint), and the units demanded by each distribution centre (demand constraint).

This objective matrix will determine the optimal number of units shipped from each factory to each distribution centre, minimising total shipping costs, given the listed supply and demand constraints.

As we start to manually enter in shipping quantities, we can see the inventory moved, units received, and total shipping costs begin to be calculated.

Like that bike problem, both manual and solver methods, we need to figure out the optimal mix of shipments that will minimise the costs. Here are the parts that we need to break down:

1. Objective function,

=SUMPRODUCT(D5:G8,D16:G19)

Minimise $E$23

2. Decision Variables – the numbers we want to adjust to minimise the costs,

‘Example 2’!$D$16:$G$19

3. Constraints
   1. Decision variables need to be integer as we don’t want to be shipping part boxes,

$D$16:$G$19 = integer

2. Supply need to less than or equal to the inventory that the factory has available,

$I$16:$I$19 <= $I$5:$I$8

3. Demand needs to fulfilled for each distribution centre and since we want to be neither over nor under, we need to see this constraint to be a strict equality,

$D$21:$G$12 = $D$10:$G$10

Select the Simplex LP Solving Method then click Solve to observe the optimal mix of shipping:

We can see the split between the factories and distribution centres:

Boston should ship 225 to Miami, 250 to Seattle, and 525 to Baltimore. NYC should ship 1025 to Miami, and 975 to Dallas. Chicago should ship 2,500 to Seattle, and Oakland should also ship them 500.

That’s getting a bit advanced, but it is showing how we can utilise this add-in. Let’s start to look at some Assignment problems.

Example 3: Assignment Problem 1

Instead of assigning objects to values based on lowering costs, we can actually assign people to people. For instance, patients can be polled about their in-hospital carers and a model can assist in allocated people based on their satisfaction levels.

Like the previous problem, let’s create another matrix with 0 values in the satisfaction levels:

Let’s add sum cells at the bottom of each column in this lower matrix:

And rows too:

Lastly, let’s add the objective function. We want to divide by 4 as we are taking the sum of all patient’s satisfaction, hence we want to see the average of all patient’s satisfaction:

=SUMPRODUCT(B2:E5,B9:E12)/4

Now, to Solver. We set $B$16 as the Objective Function and we want to Maximise it:

We want to set the second matrix as the Decision Variables:

Finally, we want to add the constraints. For this, we want to make sure that all patients are assigned 1, and only 1, nurse:

And we want to make sure that all nurses are assigned 1,and only 1, patient:

Finally, make sure that the ‘Solving Method’ is set to Simplex LP, then click Solve:

We can observe the maximum amount of satisfaction has been reached and all nurses and patients have been assigned:

Now, for one last Assignment problem.

Example 4: Assignment Problem 2

There are some situations where you might need to allocate people into groups in an equitable or fair way, depending on the context and principles guiding that allocation process. Whether it’s a work-based grouping dynamic, school-based classroom allocation, or some other situation where this allocation process is required. To best serve those who are going to be allocated, the fairest way to approach this is to ask their top three preferences for each group.

For this situation, I have created some data where 20 students have provided their preferences for three different groups; 3 = most preferred, 1 = least preferred:

Like the other problems, I’ll create a duplicate matrix below and assign all the preferences to 0:

I’ll also add the sum cells to the bottom of the columns:

And the rows too:

Finally, we can create the objective function:

Let’s open Solver and handle all of that information:

Shown above, the Objective Function is to maximise happiness as we have asked for three preferences, 3 (most preferred) to 1 (least preferred). We want to be changing the preference (decision variable) cells, hence $D$27:$F$46. For each group, we only want to have a maximum of 15 people within each group, which is why we have $D$47:$F$47 <= 15. Lastly, we only want one student to be in one group, so we will set all rows eaual to 1; $G$27:$G$46 = 1. Now, to solve:

What happens if we allocate 45 people into 4 groups?

Duplicate the array and set all values to 0, and create the objective function:

Now, to create the setup in Solver:

Solved:

And what about 52 people into 4 groups? Due to Excel’s 200 variable limit, we will need to download and install Open Solver.

In this exploration of Excel Solver’s functionalities, we’ve dissected its application in linear programming, transport problems, and assignment problems. From optimising shipping quantities to handling nurse-patient assignments and equitable student group allocation, Solver demonstrates its versatility in handling complex problem-solving scenarios. Throughout these examples, we’ve leveraged the Simplex LP solving method to efficiently minimise costs, maximise satisfaction, and address equitable allocation challenges. The emphasis lies on practical, data-driven decision-making, aligning with the core principles of engineering problem-solving.

Excel Solver emerges as a pragmatic tool for business analysts and project managers grappling with optimisation puzzles. Whether optimising resources for a project or allocating individuals to groups based on preferences, Solver’s systematic approach simplifies the complexity inherent in decision support. This isn’t just about mathematical precision; it’s about a structured methodology for problem-solving. Solver, as illustrated in these scenarios, aligns with engineering principles, providing a reliable means to navigate the intricate landscape of real-world optimisation challenges. As we wrap up this exploration, it’s evident that Solver, with its methodical solving techniques, is a valuable asset for engineers and analysts seeking efficient solutions in a diverse array of optimisation problems.