Question

You have 13 employees you need to assign to 3 projects for the remainder of the summer (PROJECTS M, Q and W). Each project must have approximately equal numbers of people, skills, and average team rating (as defined below – not as defined by you! J). The table above shows each employees rating, their skills (A, B and/or C), their preference in which project they wish to be assigned (based upon location) and also the last project they worked on for you, if any (blank means they are newly assigned to you).

OBJECTIVE: Your goal is to assign each employee to a project such that the overall sum of their preferences is maximized.

-Each project team must have at least 4 people.

-Each project team must have at least 2 people with Skill A, at least 2 people with Skill B and at least 2 people with Skill C.

-Each project team must have a total preference sum that is at least 30% of the overall total that you maximize (note – this is NOT an average).

-Employees that worked on their last project together (see column “Last Project”) cannot work on a project together in this assignment. (e.g., Cather and Lawson must be assigned to different projects).

-Each project team must have average employee ratings of at least 80.6.   (note – this IS an average).

Summarize your assignments in words and show how they meet all requirements for project assignment.

I’d suggest doing the requirements modularly in order from A to E - if you can’t get a particular requirement to work, move on to the next one and let me know which ones you successfully implemented.

Please show all the solver constraints and all formulas for cells.

					PROJ M	PROJ Q	PROJ W
Rating	Name	Last Project	Skills
76	Anders		A		15	25	10
81	Brock	Alpha	A,B		13	13	24
70	Cather	Beta	B		5	5	40
91	Dalton		A,C		15	15	20
88	Easley	Alpha	B,C		5	24	21
84	Fuller	Alpha	A,B		28	12	10
62	Gillman		B		17	17	16
75	Harris	Rho	C		22	5	23
80	Ingram		C		12	33	5
94	Jurgens		A,B		27	18	5
83	Keplar	Rho	A,C		21	13	16
81	Lawson	Beta	B		33	8	9
90	Minders		A,C		15	17	18

Answer 1

Excel formulation

Solution