Assignment problem with Excel

Use the solver in Excel to find the assignment of persons to tasks that minimizes the total cost.

Formulate the problem

To formulate this assignment problem, answer the following three questions.

  • What are the decisions to be made? For this problem, we need Excel to find out which person to assign to which task (Yes=1, No=0). For example, if we assign Person 1 to Task 1, cell C10 equals 1. If not, cell C10 equals 0. (in yellow)
  • What are the constraints on these decisions? Each person can only do one task (Supply=1). Each task only needs one person (Demand=1). (in light blue)
  • What is the overall measure of performance for these decisions? The overall measure of performance is the total cost of the assignment, so the objective is to minimize this quantity. (in dark blue)

LP41

Name the following ranges:

Range Name Cells
Cost C4:E6
Assignment C10:E12
PersonsAssigned C14:E14
Demand C16:E16
TasksAssigned G10:G12
Supply I10:I12
TotalCost I16

And insert the following functions:

LP42

Solve the model

Enter the solver parameters:

LP43

The optimal solution:

LP44

Publicités