Q2: 25 points

Raptor Consulting has three (3) projects to complete this year. They have four (4) project teams currently available. Each project team can at most do one project. Based on the mix of skills and experience on each of the teams, Raptor has developed the following estimates on time taken by each team (in days) to complete each project.

Teams

Project

Team 1

Team 2

Team 3

Team 4

Project A

50

30

55

27

Project B

35

28

25

35

Project C

33

37

30

23

Determine the optimal assignment of teams to projects in order minimize the total days taken to complete all the projects.

Please answer items A-E below

A. Formulate the problem as an assignment problem. Use worksheet tab "Q2 SS Model" to develop your formulation. 5 pts

Use worksheet tab "Q2 SS Model" to develop your formulation

B. Solve the problem using Excel Solver. 5 pts

Use worksheet tab "Q2 SS Model"

C. What is the optimal assignment of teams to projects. 5 pts

D. What is the minimum number of days needed to complete the three projects. 5 pts

Q2: 25 points

Raptor Consulting has three (3) projects to complete this year. They have four (4) project teams currently available. Each project team can at most do one project. Based on the mix of skills and experience on each of the teams, Raptor has developed the following estimates on time taken by each team (in days) to complete each project.

Teams

Project

Team 1

Team 2

Team 3

Team 4

Project A

50

30

55

27

Project B

35

28

25

35

Project C

33

37

30

23

Determine the optimal assignment of teams to projects in order minimize the total days taken to complete all the projects.

Please answer items A-E below

A. Formulate the problem as an assignment problem. Use worksheet tab "Q2 SS Model" to develop your formulation. 5 pts

Use worksheet tab "Q2 SS Model" to develop your formulation

B. Solve the problem using Excel Solver. 5 pts

Use worksheet tab "Q2 SS Model"

C. What is the optimal assignment of teams to projects. 5 pts

D. What is the minimum number of days needed to complete the three projects. 5 pts

E. Suppose that at most only one of Teams 2 and 3 can be assigned to a project. Write below the additional constraint that you need to add. 5 pts

Note: No need to solve the model with this additional constraint.