Question

CarpetPlus sells and installs floor covering for commercial buildings. Brad Sweeney, a CarpetPlus account executive, was just awarded the contract for five jobs. Brad must now assign a CarpetPlus installation crew to each of the five jobs. Because the commission Brad will earn depends on the profit CarpetPlus makes, Brad would like to determine an assignment that will minimize total installation costs. Currently, five installation crews are available for assignment. Each crew is identified by a color code, which aids in tracking of job progress on a large white board. The following table shows the costs (in hundreds of dollars) for each crew to complete each of the five jobs:
   Job

   Crew    1 2 3 4 5

   Red 30 44 38    47 31

   White 25 32 45 44 25

Blue 23 40 37 39 29

   Green 26 38 37    45 28

Brown 26 34    44    43    28

a. Develop a network representation of the problem. b. Formulate and solve a linear programming model to determine the minimum cost assignment

I need EXCEL part in steps. Thank you.

Answer 1

For solving this linear programming problem using Excel -> Solver, we will first setup Excel as shown below:

In this next step, we need to add Solver by going File -> Options ->Add-ins -> Excel Add-ins as shown below:

(This is assuming one job can be assigned to one crew only)

Now go to Data-> Solver to setup Solver with decision variables, constraints and objective function as shown below:

Now, click on Solver to get the optimal allocation of crew to job as optimal solution:

Hence, the optimal solution is to assign Job 1 to Brown, Job 2 to White, Job 3 to Red, Job 4 to Blue, Job 5 to Green.

Total minimum cost is 163

Please give thumbs up/ likes if you consider my answer useful. Thank you!