Question 1 Network Flow Optimization (8 points) (Excel Analytic Solver needed for Q1.2 and Q1.3)

You have four projects that you need to staff for the next two weeks. You also have four candidates available who are able to work on the projects. You have made the following project quality scores on how well each candidate will do if he/she is assigned to the projects. The time (hours) required to complete each project is indicated below:

	Project Quality Scores
Candidate	A	B	C	D
1	90	80	25	50
2	60	70	50	65
3	70	40	80	85
4	65	55	60	75
Time Required (hours)	70	50	85	35

Question 1.1. Assuming that each candidate can be assigned to at most one project, write out the mathematical formulation of the optimization problem for finding the candidate-to-project assignment that maximizes the total project quality score (Define your decision variables, show the objective function and all constraints) (2 points).

Question 1.2. Create a spreadsheet that implements the above model. Label the worksheet Q1.2 in your Excel Workbook (3 points).

Question 1.3. What is the highest total project quality score that you can achieve in the above problem? (1 point).

Question 1.4. Suppose that each candidate has 80 working hours available in the next two weeks. More than one candidate can work on one project and one candidate can work on more than one project. Write out the mathematical formulation of the optimization problem for maximizing the total project quality score (2 points).