Optimization Using Solver

April 2, 2010

Neha Jain, our Bangalore Advanced Analytics Trainer, teaches you how to solution an optimizing problem in Excel


Problem

Suppose a  company wants to assign 15 employees to 10 offices. There are four offices that require 2 people. Each employee has given their preference.   1 means first choice, 2 means second choice, etc. How should the company assign the people to the offices to optimize the preferences of the employees?

Solution

We can easily solve this problem using solver.

1) The variables are the assignments of the people to different offices. On worksheet Offices these are given the name Assignments.

2) There are the following logical constraints:

Assignments = binary

and the other constraints

Assignments_per_employee = 1                                                                                                                                                                  Total_employees = Required_employees

3) The objective is to optimize the preference of the employees. That means we have to minimize the sum of the total preferences given to the assigned offices, defined on the worksheet as Total_preference.

Remarks

When everybody wants a different office, there will be no problems. If all employees prefer the same office (more likely!), the problem gets more difficult and it might be necessary to give an employee 7th or 8th choice. It might be wise, in that case, to add a constraint to say that no assignment worse than 5th choice is given, for instance. This may cause the problem to be infeasible, i.e., there is no possible solution. If this happens, you will have to relax the constraint on the assignments, e.g. no worse than 6th or even 7th choice.                                                                                                                                                                                                                                                                              

Advertisements
%d bloggers like this: