Answer these questions except sensitivity analysis based on the data given below.

The Background Ron Smith felt good about his rst 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 (See Figure I), followed the manufacturing sequence for the windings: drawing, extrusion, winding, inspection and packaging. After inspection, good product is packaged and sent to nished product storage; defective product is stored separately until it can be reworked. Finished Product Wire Drawing Storage Extrusion Rework Department Receiving and Raw Winding Material Storage - Packaglng Rejected Product Inspection Storage 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\Data 1. Next month orders Product Units ordered (units) W0075C 1,400 W0033C 250 W0005X 1,510 W0007X 1,116 Note: Vivian Napoli has given her word to a key customer that MG Wire will manufacture 600 units of product WOO07X and 150 units of product W0075C for him next month. 2. Standard cost Product Material Labour 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 $64.75 $175.00 3. Selected operating data Average output per month 2,400 units Average machine utilization 63% Average percentage of production sent to 5% (mostly from Winding Department) Rework Department Average number of rejected units awaiting 850 (mostly from Winding Department) rework 4. Plant capacity (hours) Drawing Extrusion Winding Packaging 4,000 4,200 2,000 2,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.00 Sensitivity analysis: If relevant, discuss the results from the sensitivity report, and identify relevant information that could be help with decision making in MG Wire. 0 Discussions and recommendations , including but not limited to: > > > Discuss MG Wire's options for next month. Comment on the need for temporary workers in the drawing department. Does this analysis help decide which department needs new equipment? If yes, please explain your answer. If not, please describe the appropriate analysis technique to answer the question about new equipment, and discuss what additional data is needed. Make recommendations with justications. A B C D E F G H 1 Microsoft Excel 16.74 Sensitivity Report 2 Worksheet: [Book9.xIsx]Sheet1 Report Created: 27/6/2023 10:37:04 am UI A 6 Variable Cells V Final Reduced Objective Allowable Allowable 00 Cell Name Value Cost Coefficient Increase Decrease $B$29 X1 1100 34 1E+30 14 10 $C$29 X2 250 0 30 1E+30 30 11 $D$29 X3 0 -42 60 42 1E+30 12 $E$29 X4 600 0 24 44 1E+30 13 14 Constraints 15 Final Shadow Constraint Allowable Allowable 16 Cell Name Value Price R.H. Side Increase Decrease 17 $F$19 Key Customer Order 150 LHS 1100 0 150 950 1E+30 18 $F$20 Key Customer Order 600 LHS 600 -44 600 475 75 19 $F$21 Plant Capacity (hrs) - Drawing LHS 2200 0 4000 1E+30 1800 20 $F$22 Plant Capacity (hrs) - Extrusion LHS 1950 0 4200 1E+30 2250 21 $F$23 Plant Capacity (hrs) - Winding LHS 1850 0 2000 1E+30 150 22 $F$24 Plant Capacity (hrs) - Packaging LHS 2300 34 2300 150 950 23 $F$25 Next Months Orders - W0075C LHS 1100 0 1400 1E+30 300 24 $F$26 Next Months Orders - WOO33C LHS 250 30 250 50 250 25 $F$27 Next Months Orders - WOOO5X LHS 0 0 1510 1E+30 1510 26 $F$28 Next Months Orders - WOO07X LHS 600 0 1116 1E+30 516A B C D E F G H 7 Profit Per Product 8 Product Material Labour Overhead Total Cost Selling Price Profit 9 X1 $33.00 $9.90 $23.10 $66.00 $100.00 $34.00 10 X2 $25.00 $7.50 $17.50 $50.00 $80.00 $30.00 11 X3 $35.00 $10.50 $24.50 $70.00 $130.00 $60.00 12 X4 $75.00 $11.25 $64.75 $151.00 $175.00 $24.00 13 14 15 Objective Function = 34X1+30X2+60X3+24X4 16 Objective Coefficient 34 30 60 24 17 Variables X1 X2 X3 X4 18 Constraints LHS RHS 19 Key Customer Order 150 1 0 0 0 1100 >= 150 20 Key Customer Order 600 0 0 0 1 600 >= 600 21 Plant Capacity (hrs) Drawing 1 2 0 1 2200