Question

Please complete all work in excel. Use excel to make the calculations (cells can be clicked on to view any formulas used) and be sure to identify your answer, including units.

1. There are four jobs to be done and four employees to do them. Each resource will take a specific time, translated to salaries, to do each job, as shown in the following table:

Employee
Job	A	B	C	D
1	$20	23	21	17
2	18	18	22	19
3	19	24	23	12
4	21	19	18	18

  

1. What is the result of the row reduction?  

2. What is the result of the column reduction?

3. What is the result of modifying the row and column reductions table?

4. What is the optimum assignment of jobs to employees?

5. What is the total cost for the optimum assignment of jobs to employees?

Answer 1

The given table is

a) Row Reduction:

For doing the row reduction, we identify the minimum value of each row as below.

Then, we subtract the row minimum from each cell of the corresponding rows as below.

Therefore, the result of the row reduction is

b) Column Reduction

For column reduction, we find the minimum value from each column and subtract from each cell in the column.

Since the minimum value for all the columns are zeroes, therefore there will be no change in the table after column reduction. The resulting table after column reduction is as below.

c)

Now, we find rows with exactly one zero and marked it. All other zeroes in the column of the marked zero are crossed out.

Now, we follow the same step for the columns also.  The result of modifying the row and column reductions table is as below.

d) In the above table, the number of assignments is 3, which is not equal to 4. Therefore, the above assignment is not optimal.

We will draw a set of horizontal and vertical lines to cover all the zeroes as below.

• We tick(✓) mark all the rows in which no assigned 0.
• Then, we examine Tick(✓) marked rows, If any 0 cell occurs in that row, then tick(✓) mark that column.
• We examine Tick(✓) marked columns, If any assigned 0 exists in that columns, then tick(✓) mark that row.
• We will repeat this process until no more rows or columns can be marked.
• We draw a straight line for each unmarked rows and marked columns.

We developed the new revised cost table as below.

• We select the smallest element among the cells not covered by any line (say k = 3)
• We subtract k = 3 from every element in the cell not covered by a line.
• Then, we add k = 3 to every elment in the intersection cell of two lines.

The resulting optimum assignment of jobs to employees is as below.

e)

The total cost for the optimum assignment of jobs to employees is 68