| [Page 235 ( continued )] The assignment problem can be solved using the assignment modules in QM for Windows and Excel QM, and with Excel spreadsheets. We will solve our example of assigning ACC officials to game sites first by using Excel, followed by Excel QM, and then by using QM for Windows. Computer Solution with Excel As is the case with transportation problems, Excel can be used to solve assignment problems, but only as linear programming models. Exhibit 6.12 shows an Excel spreadsheet for our ACC basketball officials example. The objective function in cell C11 was developed by creating a mileage array in cells C16:F19 and multiplying it by the decision variables in cells C5:F8 . The model constraints for available teams (supply) are contained in the cells in column H, and the constraints, for teams of officials at the game sites (demand) are contained in the cells in row 10.
[Page 236] Exhibit 6.12. (This item is displayed on page 235 in the print version) 
Exhibit 6.13 shows the Solver Parameters screen for our example. Before clicking on "Solve," remember to invoke the Options screen and click on "Assume Linear Model" to solve as a linear programming model. The optimal solution is shown in Exhibit 6.14. A network diagram of the optimal solution is shown in Figure 6.5. Exhibit 6.13. 
Exhibit 6.14. 
Figure 6.5. Assignment network solution for ACC officials example 
[Page 237] Computer Solution with Excel QM Excel QM also includes a spreadsheet macro for assignment problems. It is very similar to the Excel QM macro demonstrated earlier in this chapter for the transportation problem, and the spreadsheet setup is very much like that of the Excel spreadsheet in Exhibit 6.14. The assignment macro is accessed (from the "QM" menu) and applied similarly in the transportation problem macro. It already includes all the cell and constraint formulas necessary to solve the problem. The solution to our ACC basketball example with Excel QM is shown in Exhibit 6.15. Exhibit 6.15. 
Computer Solution with QM for Windows The data input for our example is shown in Exhibit 6.16, followed by the solution in Exhibit 6.17. Exhibit 6.16. 
Exhibit 6.17. 
[Page 238] Management Science Application: Assigning Managers to Construction Projects The Nashville office of Heery International contracts with the state of Tennessee, various municipalities, and private firms for construction projects, such as hospitals , office buildings, state park facilities, university libraries, dorms and classroom buildings , and prisons. Projects average about $2 million, although they can vary in size from $50,000 up to $50 million. When assigning managers to projects, Heery's objective is to minimize the total cost while maintaining a balanced workload among its managers. At any one time, Heery will have approximately 7 managers (who live in different cities) that it must assign to up to 114 projects. Heery developed a modified version of the assignment model that is solved by using an Excel spreadsheet. The solution approach is robust enough to handle new projects, the termination of old projects, the resignation of managers, and the hiring of new managers over the passage of time. The objective function in Heery's model is to minimize project intensity (instead of cost). Project intensity is a logarithmic function of project cost and manager driving time to a project. This intensity function quantifies a manager's effort by assigning relative (dimensionless) values to the projects, based on a project's dollar value and the time required for the manager to drive to a project. As projects get larger (in dollar value), there is less difference between the intensities necessary to manage them. The model includes constraints that balance the monthly workload given to each manager. One set of constraints limits each manager's total intensity to a maximum amount each month for the next 12 months. Another set of constraints ensures fairness by specifying that each manager's total monthly intensity must be at least a specified amount. A final set of constraints ensures that each project has exactly one manager assigned to it. In some cases experienced managers are assigned to more important projects a priori manually in the spreadsheet. Also, continuity of previous assignments to ongoing projects is necessary. Further, some projects in an outlying area might require the same manager simply because of their proximity to each other. For example, three projects at Middle Tennessee State Universitystadium, bookstore, and libraryneeded to be managed by one person.  Because of the natural progression of construction projects over time, Heery updates the spreadsheet model each month at its Nashville office. The assignment model has saved Heery money by enabling it to reduce its number of managers, reduce travel costs for managers, and reduce the time required to prepare project assignments each month. It has also improved morale because workloads are perceived to be fairer. Source: L. J. LeBlanc, D. Randels, Jr., and T. K. Swann, "Heery International's Spreadsheet Optimization Model for Assigning Managers to Construction Projects," Interfaces 30, no. 6 (NovemberDecember 2000): 95106. |
|
