Ron Garcia felt good about his first week as a management trainee at Mexicana Wire Winding, Inc. He had not yet developed any technical knowledge about the manufacturing process, but he had toured the entire facility, located in the suburbs of Mexico City, and had met many people in various areas of the operation.

Mexicana, a subsidiary of Westover Wire Works, a Texas firm, is a medium-sized producer of wire windings that are used in making electrical transformers. Jose Arroyo, the production control manager, described the windings to Garcia as being of standardized design. Garcia's tour of the plant followed the manufacturing sequence for the windings: drawing, extrusion, winding, inspection, and packaging. After inspection, good product is packaged and sent to finished product storage; a defective product is stored separately until it can be reworked.

On March 8, Vivian Espania, Mexicana's general manager, stopped by Garcia's office and asked him to attend a staff meeting at 1:00 p.m.

At the meeting:

"Let's get started with the business at hand," Vivian said, opening the meeting. "You all have met Ron Garcia, our new management trainee. Ron studied operations management in his MBA program in northeastern Oklahoma so I think he is competent enough to help us with a problem we have been discussing for a long time that we have failed to resolve. I'm sure that each of you on my staff will give Ron your full cooperation."

Vivian turned to Jose Arroyo, production manager. "Jose, why don't you describe the problem we are facing?" "Well," Jose said, "business is very good right now. We are booking more orders than we can fill. We will have some new equipment on line within the next several months, which will take care of our capacity problems, but that won't help us in April. I have located some retired employees who used to work in the drawing department, and I am planning to bring them in as temporary employees in April to increase capacity there. Because we are planning to refinance some of our long-term debt, Vivian wants our profits to look as good as possible in April. I'm having a hard time figuring out which orders to run and which to back order so that I can make the bottom line look as good as possible. Can you help me with this?"

Ron was surprised and apprehensive to receive such an important, high-profile assignment so early in his career. Recovering quickly, he said, "Give me your data and let me work with it for a day or two."

Sometime shortly after the meeting, Ron was given the following information.

April Orders

Product W0075C 1,400 units
Product W0033C 250 units
Product W0005X 1,510 units
Product W0007X 1,116 units

Note: Vivian Espania has given her word to a key customer that MWW will manufacture 600 units of product W0007X and 150 units of product W0075C for him during April.

In the following table, the costs of material, labor, and overhead in addition to the selling price are given. These should be used to determine how much PROFIT will be made for each unit of the product that is sold.

Product	Material	Labor	Overhead	Selling Price
W0075C	$33.00	$ 9.90	$23.10	$100.00
W0033C	25.00	7.50	17.50	80.00
W0005X	35.00	10.50	24.50	130.00
W0007X	75.00	11.25	63.75	175.00

Selected Operating Data:

Average output per month = 2,400 units

Average machine utilization = 63%

Average percentage of production set to rework department = 5% (mostly from Winding Department)

Average no. of rejected units awaiting rework = 850 (mostly from Winding Department)

Plant Capacity (Hours): Drawing=4,000; Extrusion=4,200; Winding=2,000; Packaging=2,300

Note: Inspection capacity is not a problem; workers can work overtime, as necessary, to accommodate any schedule.

Bill of Labor (Hours/Unit)

Product	Drawing	Extrusion	Winding	Packaging
W0075C	1.0	1.0	1.0	1.0
W0033C	2.0	1.0	3.0	0.0
W0005X	0.0	4.0	0.0	3.0
W0007X	1.0	1.0	0.0	2.0

For this exercise, you are to solve the linear programming problem using Excel’s Solver and then write a report to management. The (professional) management report is to be submitted for grading. Please read the following section carefully.

Solve the problem.

-Using Solver in Excel and the linear programming formulation given below, solve the problem(s). Note: the final optimal solution with its data and the final expected outcomes MUST be included in the report. (Copying the Answer Reports as a “picture” is a good way to take what is in Excel and insert it into Word. After the linear programming model’s solution is given, please clearly state/identify what is expected for the scenario.

Analyze the results.

-Perform a sensitivity analysis for this problem. You may develop tables to identify important aspects of the sensitivity analysis BUT you must clearly explain to management how the expected outcomes could change if some of the parameters (objective function coefficients and RHS values) varied. Note: you must prepare a table or tables similar to the reports provided in Excel for its sensitivity analysis BUT do not merely copy the sensitivity analysis tables produced by Excel into the report without providing any “ANALYSIS”. Develop tables that have only the pertinent information included in them.

Using this information, what does the information tell you about the decision situation? What strategy do you recommend and why? Are there any qualitative aspects that should be taken into consideration?

Implement the results.

-Using all the information developed in the preceding steps, describe how your recommendations would be implemented. How will MWW know your recommendations were successful when May arrives?

Note: The focus of this assignment is to produce a report to management that provides a sensitivity analysis for this situation. Because of this, the formulation for this problem is given below.

Mexicana Wire Works (MWW) Problem Formulation

Let:

W0075C = The number of W0075C parts to produce

W00335C = The number of W0033C parts to produce

W0005X = The number of W0005X parts to produce

W0007X = The number of W0007X parts to produce

P = Profit

Objective Function:

Maximize P = 34 (100-(33+9.90+23.10) W0075C + 30 (80-(25+7.50+17.50) W0033C +

60 (130-(35+10.50+24.50) W0005X + 25 (175-(75+11.25+63.75) W0007X

Subject to:

1 W0075C £ 1,400 April Orders-W0075C

1 W0033C £ 250 April Orders-W0033C

1 W0005X £ 1,510 April Orders-W0005X

1 W0007X £ 1,116 April Orders-W0007X

1 W0075C + 2 W0033C + 0 W0005X + 1 W0007X £ 4,000 Drawing Capacity

1 W0075C + 1 W0033C + 4 W0005X + 1 W0007X £ 4,200 Extrusion Capacity

1 W0075C + 3 W0033C + 0 W0005X + 0 W0007X £ 2,000 Winding Capacity

1 W0075C + 0 W0033C + 3 W0005X + 2 W0007X £ 2,300 Packaging Capacity

1 W0075C ³ 150 Key Customer Guarantee-W0075C

1 W0007X ³ 600 Key Customer Guarantee-W0007X

And W0075C, W0033C, W0005X, W0007X ³ 0