Linear Programming Models: Graphical and Computer Methods
Hooknem
GBA334_Assignment_Problem_Using_QM.pdf
Assignment Problem Using QM In this example, we will solve an assignment problem using linear programming with Excel QM. The hospital administrator at St. Charles General Hospital must appoint head nurses to four newly established departments: urology, cardiology, orthopedic, and obstetrics. In anticipation of this staffing problem, she has hired four nurses: Hawkins, Condriac, Bardot, and Hoolihan. Each nurse has been ranked on a cost scale of 0 to 100 for each department; a 0 implies that she would be perfectly suited for the task; a 100 implies that the nurse is not at all suited to head the unit. The table below represents all possible assignments and nurse ratings. Which nurse should be assigned to which unit?
Nurse Urology Cardiology Orthopedics Obstetrics
Hawkins 28 18 15 75
Condriac 32 48 23 38
Bardot 51 36 24 36
Hoolihan 25 38 55 12
To figure this out, let’s open Excel QM and solve our problem. Click on the Excel QM tab Alphabetical Assignment.
In the Spreadsheet Initialization window, be sure to identify that we have four assignments and we want to minimize (recall that a value closer to zero is ideal).
Click OK. A spreadsheet will display.
Enter the data shown into the table.
That is it. Once you have the data entered correctly, click the Data tab and then Solver.
A Solver Parameters window will appear.
Click Solve and then OK in the Solver Results window. Our results are shown below.
Data
COSTS Uro Cardio Ortho OB Hawkins 28 18 15 75
Condriac 32 48 23 38
Bardot 51 36 24 36
Hoolihan 25 38 55 12
Assignments
Shipments Uro Cardio Ortho OB Row Total
Hawkins 1 1
Condriac 1 1
Bardot 1 1
Hoolihan 1 1
Column Total 1 1 1 1 4
Total Cost 86
Assignment Rating
Hawkins to cardiology 18 Condriac to urology 32 Bardot to orthopedics 24 Hoolihan to obstetrics 12
Total “cost scale” 86
Click here to download the completed spreadsheet table so you can compare it to yours. This concludes our tutorial on assignment problem using linear programming with Excel QM.
