Based on LeBlance et al XXXXXXXXXXA construction company has eight project managers and has 14 projects scheduled for the next 12 weeks. Each project must be assigned a project manager. The start and...

Based on LeBlance et al. (2000). A construction company has eight project managers and has 14 projects scheduled for the next 12 weeks. Each project must be assigned a project manager. The start and finish week for each project as well as the hours per week each project manager would need to spend on a project are given in file P15_37.xlsx. For example, project 1 starts at the beginning of week 4 and finishes at the end of week 10, for a duration of 7 weeks. Also note that if manager 2 is assigned to project 1, he will work 50 hours per week on the project. In assigning managers, the company has a policy of not allowing a manager to work more than 70 hours a week. Given this constraint and the fact that all projects must be done, the company wants to minimize the total number of weeks during which managers work more than 50 or less than 30 hours. (Note that, given the data for the problem, working fewer than 30 hours in a week means not working that week at all.) How would you assign managers to projects?

 (Hint: This problem is conceptually fairly simple, but the bookkeeping is difficult. Here is one possibility. Let the changing cells be a column of indexes of the managers assigned to the various projects. For example, the changing cell for project 1 is 4 if we assign manager 4 to project 1. Based on the values in these changing cells, use a lookup function to find the number of hours used by each project. For example, again assuming manager 4 is assigned to project 1, this lookup should return 38 for project 1.

Now create a table with weeks along the top and projects along the side. Each entry in the table should indicate how many hours are spent on each project each week. IF functions work here. Finally, create one more table with weeks along the top and managers along the side and use SUMIF functions, based on the data in the previous table, to calculate the number of hours each manager is working each week. As you can probably guess, you’ll need to use Evolutionary Solver if you set it up this way. Also, you might have to let Evolutionary Solver run for a long time. This is not an easy problem!)