Frontline Systems, Excel Solver, optimization software, Solver Excel, simulation software
Solver.com
From Frontline Systems, developers of the Excel Solver.

Solver tutorials

Learn to use optimization for resource allocation, and Monte Carlo simulation for risk analysis of your models.


 

Office Assignment
A company wants to assign 14 employees to 10 offices. Each employee has a preference for a certain office.
How should the company assign employees to the offices to maximize the preference of all employees?
Preferences (1=first, 10=last choice)        
  Office 1 Office 2 Office 3 Office 4 Office 5 Office 6 Office 7 Office 8 Office 9 Office 10
Employee 1 3 2 1 4 6 5 8 9 10 7
Employee 2 5 3 2 6 1 7 9 8 4 10
Employee 3 10 8 1 9 7 4 3 6 2 5
Employee 4 7 3 2 9 5 4 8 6 1 10
Employee 5 1 3 6 8 5 2 9 10 7 4
Employee 6 4 9 1 5 6 8 2 7 10 3
Employee 7 2 1 10 9 5 3 6 8 4 7
Employee 8 6 5 1 3 2 4 7 8 9 10
Employee 9 8 9 10 5 4 3 2 1 6 7
Employee 10 9 10 3 2 5 4 1 7 8 6
Employee 11 7 3 5 2 9 8 1 10 4 6
Employee 12 6 5 1 9 10 2 3 4 7 8
Employee 13 6 8 10 9 1 2 3 4 5 7
Employee 14 6 3 5 9 1 2 10 4 8 7
Assignments Office 1 Office 2 Office 3 Office 4 Office 5 Office 6 Office 7 Office 8 Office 9 Office 10 Total   Preference
Employee 1 0 0 0 0 0 0 0 0 0 0 0 0
Employee 2 0 0 0 0 0 0 0 0 0 0 0 0
Employee 3 0 0 0 0 0 0 0 0 0 0 0 0
Employee 4 0 0 0 0 0 0 0 0 0 0 0 0
Employee 5 0 0 0 0 0 0 0 0 0 0 0 0
Employee 6 0 0 0 0 0 0 0 0 0 0 0 0
Employee 7 0 0 0 0 0 0 0 0 0 0 0 0
Employee 8 0 0 0 0 0 0 0 0 0 0 0 0
Employee 9 0 0 0 0 0 0 0 0 0 0 0 0
Employee 10 0 0 0 0 0 0 0 0 0 0 0 0
Employee 11 0 0 0 0 0 0 0 0 0 0 0 0
Employee 12 0 0 0 0 0 0 0 0 0 0 0 0
Employee 13 0 0 0 0 0 0 0 0 0 0 0 0
Employee 14 0 0 0 0 0 0 0 0 0 0 0 0
Total 0 0 0 0 0 0 0 0 0 0 0
   
Required 1 1 1 1 2 1 2 2 2 1      
Problem                              
A company wants to assign 14 employees to 10 offices. There are four offices that require 2 people. Each
employee has given their preference. A 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                              
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.                            

To Learn More:

For instant access to our white papers, example models, full-text User Guides, and to download free 15-day trial versions of our software products whenever you're ready, register now with no obligation.
User type
Email address
Name
First Last
Company
University
Phone

Trial version passwords are sent to the above email address. Our Privacy Policy protects you.