I need you to help me with how solve LP model with step by step guide with what values I need to add in each cell in the excel sheet and identify the constraints and object function. Please provide images from excel sheet when you explain about the tables and using solver steps

The Background

Ron Smith felt good about his first week as a management trainee at MG Wire Winding. He had not yet developed technical knowledge about the manufacturing process, but he had toured the facility and met many people in various areas of the operation. MG Wire Winding, a Queensland company, is a medium-sized producer of wire windings used in making electrical transformers. Joseph Lowes, the production control manager, described the windings to Ron as being of standardized design.

Ron's tour of the plant, which is laid out by process type (SeeFigure 1), 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; defective product is stored separately until it can be reworked.

Figure 1 Plant Layout of MG Wire

Vivian Napoli, MG's general manager, stopped by Ron's office and asked him to attend a staff meeting."Let's get started with the business at hand", Vivian said, opening the meeting."Ron studied operations and supply chain management, so I think he can help us with a problem we have been discussing but without resolution."Vivian turned to Joseph Lowes, the production control manager."Joseph, why don't you describe the problem we are facing?"

"Well,"Joseph said,"business is very good right now. We are booking more orders than we can fill. We plan to purchase some new equipment, which will take care of our capacity problem, though we haven't decided the type of equipment to purchase, that is, it is not clear which department should get new equipment."

"Any plan for new equipment will take months to finalize and implement, so it won't help us next month. I have located some retired employees who used to work in the drawing department, and I'm going to bring them in as temporary employees next month to increase capacity there. Because we are planning to refinance some of our long-term debt, Vivian wants our profit to look as good as possible next month. 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 task so early in his career. Recording quickly, he said"Give me your data and let me work with it for a couple of days". Having studied business management particularly supply chain management, Ron knew that a good business report is essential for communication with the management team and was keen to good job.

Data

1. Next month orders

Product Units ordered

W0075C(units)

W0033C W0005X W0007X

1,400 250 1,510 1,116

Note: Vivian Napoli has given her word to a key customer that MG Wire will manufacture 600 units of product W0007X and 150 units of product W0075C for him next month.

2. Standard cost

Product Material Labour

W0075C $33.00 $9.90 W0033C $25.00 $7.50 W0005X $35.00 $10.50 W0007X $75.00 $11.25

Overhead

$23.10 $17.50 $24.50 $64.75

Selling Price

$100.00 $80.00 $130.00 $175.00

3. Selected operating data

Average output per month Average machine utilization Average percentage of production sent to Rework Department Average number of rejected units awaiting rework

4. Plant capacity (hours)

DrawingExtrusion

4,0004,200

2,400 units 63% 5% (mostly from Winding Department)

850 (mostly from Winding Department)

WindingPackaging

2,0002,300

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

5. Bill of resource (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