Question

Use the 'Solver' add in on excel to formulate the constraints

The Clarke Special Parts Company manufactures three products: A, B, and C. Three manufacturing centers are necessary for the production process. Product A only passes through Centers 1 and 2; Products B and C must pass through all three manufacturing centers. The time required in each center to produce one unit of each of the three products is noted as follows:

				X1	X2	X3	Z
			Profit
			Constraints
			Center 1
			Center 2
			Center 3

So a unit of Product A takes three hours at Center 1, two hours at Center 2, and zero hours at Center 3. Each center is on a 40-hour week. The time available for production must be decreased by the necessary cleanup time. Center 1 requires four hours of cleanup, Center 2 requires seven hours, and Center 3 requires five hours. It is estimated that the profit contribution is $60 per unit of Product A, $40 per unit of Product B, and $30 per unit of Product C. How many units of each of these special parts should the company produce to obtain the maximum profit?

Formulation, by steps:

1. Determine the type of problem—The problem only mentions profit, so it has to be a maximization problem.

2. Define the decision variables—The profit coefficients are attached to Products A, B, and C and have a weekly stated time horizon, so

X1 = number of units of Product A to produce per week

X2 = number of units of Product B to produce per week

X3 = number of units of Product C to produce per week

3. Formulate the objective function:

Maximize: Z = 60X1 + 40X2 + 30X3

4. Formulate the constraints—This problem illustrates that some arithmetic may be needed to derive model parameters. In this case, the right-hand-side b values need to be adjusted for the cleanup time. In a week, each department starts with 40 hours for production purposes. They then have to be decreased for the cleanup time, as stated in the problem sentences, “The time available for production must be decreased by the necessary cleanup time. Center 1 requires four hours of cleanup, Center 2 requires seven hours, and Center 3 requires five hours.” So, for Center 1 we have 36 hours (40 − 4), for Center 2 we have 33 hours (40 − 7), and so on to formulate the right-hand-side values in each constraint. This problem also illustrates the use of the left-hand-side strategy for formulating constraints. Note how the tabled values are the technology coefficients listed by columns in the constraints that follow:
Image

5. Interpret the results

Answer 1