This article will guide you to calculate the problem of division of work in Excel.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
Assignment problem
Use solvers in Excel to assign people to tasks to reduce total costs.
Model building
The model we will deal with looks like this in Excel.
Person: Person
Mission: Task
1. To model this Assignment Problem, we first answer the following three questions.
a. What decisions will be made? For this, we need to figure out which task to assign (Yes=1, No=0). For example, if we assign Person 1 to Task 1, cell C10 equals 1. Otherwise, cell C10 equals 0.
b. What are the constraints on these decisions? Each person can only do one task (Supply=1). Each mission requires only one person (Demand = 1).
c.What are the overall performance metrics for these decisions? The overall performance metric is the total cost of the task, so the goal is to minimize this number.
2. To make the model easier to understand
3. Add the Function as follows
Explanation: The SUM functions calculate the number of tasks assigned to a person and the number of people assigned to a task.
Trial and error
For example, if we assign Person 1 for Task 1, Person 2 for task 2 and Person 3 for Task 3, the Task is assigned by Supply and Person is assigned by Demand. This solution has a total cost of 147.
Solve the model
To find the optimal solution, follow the steps below.
1. On the Data table, Solver
Enter solver parameters. The result should match the figure below.
2. Enter TotalCost for Target.
3. Click Min.
4. Enter Assignment for Changing Variable Cells.
5. Click Add.
6. Click Add
7. Click Add
8. Select ‘Make Unconstrained Variables Non-Negative’ ‘ and select ‘Simplex LP’.
9. Finally press Solve
Result:
Optimal solution:
Conclusion: it is optimal to assign Person 1 for Task 2, Person 2 for Task 3 and Person 3 for Task 1. This solution gives a minimum cost of 129. All are satisfied.
