Build the model in Excel with Solver

Problem

You can make three types of bags, Standard, Deluxe, and New. Each bag requires four types of labor, cutting, sewing, finishing, and inspection. Profit Contribution from each bag is constant and described in the table below:

Standard	Deluxe	New
10	13	14

The unit production requirements are given in the following table (how much of each labor it takes to make 1 bag):

	standard	Deluxe	New
Cutting	0.8	1	0.8
Sewing	0.5	5/6	1
Finishing	1	2/3	1
Inspection	0.1	0.25	0.25

Your current amount of hours for each labor type is given in the table below:

Cutting	Sewing	Finishing	Inspection
800	700	750	200

Furthermore, you can reassign workers from one task to another, but they are not as productive when they shift jobs. You can ignore any switching costs other than those in the table below. You can split workers among tasks in any fraction you wish.

	Cutting	Sewing	Finishing	Inspection
Cutting	1	0.8	0.6	0.5
Sewing	0.8	1	0.6	0.5
Finishing	0.7	0.7	1	0.9
Inspection	0.6	0.6	0.9	1

To read the table, switching 1 hour from Finishing to Sewing will result in 0.7 hours of Sewing. In other words, you lose 30% when you shift from Finishing to Sewing.

Questions

1. What is the optimal production of bags and hours to transfer to new activities?

2. How did you built your model in the solver (provide screenshots of constraints + formulas)

Note:

- Restrict bags produced to integer values, but allow continuous shifting of labor.

- You must create new decision (choice) variables for the movement of labor from one sector to another. There will be 12 of these variables (one for each entry in the table not equal to 1). The 12 comes from (4 choose 2) X 2. 4 choose 2 tells us that there are 6 possible pairs of labor types. And we have to consider labor moving into or out of the resource. Thus it is 2 X 6 = 12. If all this choose stuff is confusing, just look at the table and see that you have 12 ways you might want to move labor from the 12 non-zero coefficients.

- You have the same objective function as in the problem without transferable labor.

- The constraints should be modified to include the new variables.

- Name the variables whatever you want, but I would suggest using subscripts indicating where you took labor from and where you are sending it to. Thus would be how much labor you want to transfer from Cutting to Finishing, while would be how much labor you want to transfer from Finishing to Cutting.

Could someone help me with the formulas to get the transferable labor in Excel?