Question

Solve. Subcontractor Project A Project B Project C Westside 50 36 16 Federated 28 30 18 Goliath 35 32 20 Universal 25 25 14 How should the contractors be assigned so that totalmileage is minimized? Make sure that at least one contractor is assigned to a project, and no contractor is assigned to more than two projects. Also, keep in mind that Westside cannot be assigned to Project C. Moreover, Either Goliath or Universal needs to be assigned to work on Project C. Answer in Excel.

Answer 1

We tabulate the given data as shown below:

Since this is a minimization problem, and Westside cannot be assigned to Project C, we have allotted an arbitrary high value of 100 to this combination as shown in red such that this combination will never be feasible in a minimization problem. Either Goliath or Universal needs to be assigned to work on Project C. Hence, Federated also cannot be assigned to Project C.

The available and assigned nos. of projects are max. 2 nos to each Contractor. The constraints have to be entered in solver accordingly. The decision variables are binary as the project will either be assigned to a contractor and denoted by 1 or not assigned and denoted by 0.

Hence, we solve as follows. The solution is shown as per cells highlighted in Blue:

The above solution in the form of formulas and excel solver extract is shown below for better understanding and reference:

Hence, the assignment is:

Project A = Federated

Project B = Universal

Project C = Universal

-----------------------------------------------------------------------------------------------------------------------

In case of any doubt, please ask through the comment section before Upvote/downvote.

If you liked the answer, please give an upvote. This will be quite encouraging for me, thank-you!!!