## Solve Assignment Problem Using Solver Excel

**Unformatted text preview: **3/30/2015 Assignment Problem in Excel Easy Excel Tutorial Excel Easy #1 Excel tutorial on the net Excel Introduction Basics Functions Data Analysis VBA 300 Examples Ask us Assignment Problem Follow 5.2k Formulate the Model | Trial and Error | Solve the Model Use the solver in Excel to find the assignment of persons to tasks that minimizes the total cost. Formulate the Model The model we are going to solve looks as follows in Excel. http://www.exceleasy.com/examples/assignmentproblem.html 1/8 3/30/2015 Assignment Problem in Excel Easy Excel Tutorial Advertisement Excel to Tally Converter Versatile & fastest way to post any data from Excel to Tally (any ver) 1. To formulate this assignment problem, answer the following three questions. a. 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. b. What are the constraints on these decisions? Each person can only do one task (Supply=1). Each task only needs one person (Demand=1). c. 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. 2. To make the model easier to understand, name the following ranges. http://www.exceleasy.com/examples/assignmentproblem.html 2/8 3/30/2015 Assignment Problem in Excel Easy Excel Tutorial Range Name Cells Cost C4:E6 Assignment PersonsAssigned C10:E12 C14:E14 Demand TasksAssigned C16:E16 G10:G12 Supply I10:I12 TotalCost I16 3. Insert the following functions. Explanation: The SUM functions calculate the number of tasks assigned to a person and the number of persons assigned to a task. Total Cost equals the sumproduct of Cost and Assignment. Trial and Error With this formulation, it becomes easy to analyze any trial solution. For example, if we assign Person 1 to Task 1, Person 2 to task 2 and Person 3 to Task 3, Tasks Assigned equals Supply and Persons Assigned equals Demand. This solution has a total cost of 147. http://www.exceleasy.com/examples/assignmentproblem.html 3/8 3/30/2015 Assignment Problem in Excel Easy Excel Tutorial It is not necessary to use trial and error. We shall describe next how the Excel Solver can be used to quickly find the optimal solution. Advertisement Buy 1 & Get 1 75% Off! Fly to Mumbai Book now Solve the Model To find the optimal solution, execute the following steps. 1. On the Data tab, click Solver. http://www.exceleasy.com/examples/assignmentproblem.html 4/8 3/30/2015 Assignment Problem in Excel Easy Excel Tutorial Note: can't find the Solver button? Click here to load the Solver addin. Enter the solver parameters (read on). The result should be consistent with the picture below. You have the choice of typing the range names or clicking on the cells in the spreadsheet. 2. Enter TotalCost for the Objective. 3. Click Min. http://www.exceleasy.com/examples/assignmentproblem.html 5/8 3/30/2015 Assignment Problem in Excel Easy Excel Tutorial 4. Enter Assignment for the Changing Variable Cells. 5. Click Add to enter the following constraint. Note: binary variables are either 0 or 1. 6. Click Add to enter the following constraint. 7. Click Add to enter the following constraint. 8. Check 'Make Unconstrained Variables NonNegative' and select 'Simplex LP'. 9. Finally, click Solve. Result: http://www.exceleasy.com/examples/assignmentproblem.html 6/8 3/30/2015 Assignment Problemin Excel Easy Excel Tutorial The optimal solution: Conclusion: it is optimal to assign Person 1 to task 2, Person 2 to Task 3 and Person 3 to Task 1. This solution gives the minimum cost of 129. All constraints are satisfied. Advertisement http://www.exceleasy.com/examples/assignmentproblem.html 7/8 3/30/2015 Assignment Problem in Excel Easy Excel Tutorial Product forecasting statwizards.com Generate product forecasts in Excel using state-of-the-art techniques Excel to mobile app Optimal Control Software Free Excel Templates Bitrix24 - 100% Free CRM Professional Logo Design Free Excel Add-in Download Full Books Microsoft Excel 2010 Excel Help & Fin Models Do you like this free website? Please follow us on Google+ Follow 5.2k Go back to solver, use the side menu >> Go to Top: Assignment Problem | Go to Next Example: Shortest Path Problem Go Search Entire Site Go back to Solver Download Excel File assignmentproblem.xls Follow Excel Easy Copyright (c) 20102015 www.exceleasy.com. All rights reserved. excel 2010 tutorial | how to excel | microsoft excel 2010 | excel macro http://www.exceleasy.com/examples/assignmentproblem.html 8/8 ...

View Full Document

Formulate the Model | Trial and Error | Solve the Model

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

### Formulate the Model

The model we are going to solve looks as follows in Excel.

1. 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.

What are the constraints on these decisions? Each person can only do one task (Supply=1). Each task only needs one person (Demand=1).

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.

2. To make the model easier to understand, 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 |

3. Insert the following functions.

Explanation: The SUM functions calculate the number of tasks assigned to a person and the number of persons assigned to a task. Total Cost equals the sumproduct of Cost and Assignment.

### Trial and Error

With this formulation, it becomes easy to analyze any trial solution.

For example, if we assign Person 1 to Task 1, Person 2 to task 2 and Person 3 to Task 3, Tasks Assigned equals Supply and Persons Assigned equals Demand. This solution has a total cost of 147.

It is not necessary to use trial and error. We shall describe next how the **Excel Solver** can be used to quickly find the optimal solution.

### Solve the Model

To find the optimal solution, execute the following steps.

1. On the Data tab, in the Analyze group, click Solver.

Note: can't find the Solver button? Click here to load the Solver add-in.

Enter the solver parameters (read on). The result should be consistent with the picture below.

You have the choice of typing the range names or clicking on the cells in the spreadsheet.

2. Enter TotalCost for the Objective.

3. Click Min.

4. Enter Assignment for the Changing Variable Cells.

5. Click Add to enter the following constraint.

Note: binary variables are either 0 or 1.

6. Click Add to enter the following constraint.

7. Click Add to enter the following constraint.

8. Check 'Make Unconstrained Variables Non-Negative' and select 'Simplex LP'.

9. Finally, click Solve.

Result:

The optimal solution:

Conclusion: it is optimal to assign Person 1 to task 2, Person 2 to Task 3 and Person 3 to Task 1. This solution gives the minimum cost of 129. All constraints are satisfied.

## One thought on “Solve Assignment Problem Using Solver Excel”