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)
Name the following ranges:
And insert the following functions:
Solve the model
Enter the solver parameters:
The optimal solution: