Question

BADM 3963-Quantitative Methods Module 3 Excel Exercise for Chapter 7 For this Excel Exercise for Chapter 7, you will complete the following instructions. Develop an Excel worksheet and on this worksheet you will: 1. 2. 3. 4. S. Formulate and show the linear programming formulation for the following scenario. Develop a linear programming model in Excel. Solve the problem using Solver in Excel. Show an Answer Report and a Sensitivity Report in Excel. Submit the completed Excel worksheet on BlackBoard Scenario: Mexicana Wire Winding, Inc. 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. Let's get started with the business at hand," Vivian said, opening the meeting. "You all have met Ron Garcia new management trainee. Ron studied operations management in his MBA program in southern California so I think he is competent to help us with a problem we have been discussing for a long time without resolution. I'm 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 new equipment on line within the next several months, which will take care of our capacity problems, but that 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 refinance some of our long-term debt, Vivian wants our profits to look as good as possible in April. I'm time figuring out which orders to run and which to back order so that I can make the bottom line look as good a possible. Can you help me with this? Garcia 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 April Orders 1,400 units 250 units 1,510 units 1,116 units Note: Vivian Espania has given her word to a key cu Product W0075C Product W0033C Product WD005X Product W0007X er that we will manufacture 600 units of product W0007X and 150 units of product W0o7SC for him during April

Answer 1

1. Linear programming model is formulated mathematically as below:

Calculate for each product, unit profit = selling price - Material cost - Labor cost - Overhead cost

Let X1, X2, X3, X4 be the optimal product mix of these four products W0075C, W0033C, W0005X, W0007X resp.

Max 34X1+30X2+60X3+25X4

s.t.

X1 <= 1400

X2 <= 250

X3 <= 1510

X4 <= 1116

1X1+2X2+0X3+1X4 <= 4000

1X1+1X2+4X3+1X4 <= 4200

1X1+3X2+0X3+0X4 <= 2000

1X1+0X2+3X3+2X4 <= 2300

X1, X2, X3, X4 >= 0

2. Linear programming model in Excel is following:

If you do not know how to install solver, this might help -To use the Solver Add-in, you first need to load it in Excel. For Excel 2010 and later versions, go to File > Options. (For Excel 2007, click Microsoft Office Button , and then click Excel Options)

Click Add-Ins, and then in Manage box, select Excel Add-ins. In Add-Ins available box, select Solver Add-in check box, and then click OK.

Note that if Solver Add-in is not listed in Add-Ins available box, click Browse to locate the add-in. If you get prompted that Solver Add-in is not currently installed on your computer, click Yes to install it.

After loading Solver Add-in, the Solver command is available in Analysis group on Data tab in Excel.

Standard Cost Optimal SSolver Parameters Product Material Labor O/H Selling Price Unit Profit Orders Product Mix Profit W0075C W0033C W0005X W0007X 9.9 7.5 10.5 11.25 23.1 17.5 24.5 63.75 100 1400 250 1510 1116 Set Objective: 25 35 75 ⓔMax ○ Min . Value Of: 0 130 175 25 By Changing Variable Cells: SHS3 SHS6 Subject to the Constraints: SBS16:SES16 SBS15:SES15 SHS3:SHS6SGS3:SGS6 Bill of Labor Total Profit- 10 Drawing Extrusion Winding P W0075C W0033C W0005X W0007X 12 13 14 15 Capacity (hours) 4000 16 C 17 18 19 Delete Reset All 2300 Utilised0 Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the IPOPT Nonlinea nonlinear.Select the LP Simplex engine for linear Solver Problems. r engine for Solver Problems that are smooth 21 Solve 25

EXCEL FORMULAS:

	Standard Cost						Optimal
Product	Material	Labor	O/H	Selling Price	Unit Profit	Orders	Product Mix	Profit
W0075C	33	9.9	23.1	100	=E3-SUM(B3:D3)	1400		=F3*H3
W0033C	25	7.5	17.5	80	=E4-SUM(B4:D4)	250		=F4*H4
W0005X	35	10.5	24.5	130	=E5-SUM(B5:D5)	1510		=F5*H5
W0007X	75	11.25	63.75	175	=E6-SUM(B6:D6)	1116		=F6*H6
	Bill of Labor						Total Profit =	=SUM(I3:I6)
Product	Drawing	Extrusion	Winding	Packaging
W0075C	1	1	1	1
W0033C	2	1	3	0
W0005X	0	4	0	3
W0007X	1	1	0	2
Capacity (hours)	4000	4200	2000	2300
Capacity Utilised	=SUMPRODUCT(B11:B14,$H$3:$H$6)	=SUMPRODUCT(C11:C14,$H$3:$H$6)	=SUMPRODUCT(D11:D14,$H$3:$H$6)	=SUMPRODUCT(E11:E14,$H$3:$H$6)

3. Solution using Excel Solver is following:

Standard Cost Optima SSolver Parameters Product Material Labor O/H Selling Price Unit Profit Orders Product Mix Profit W0075C W00330 Woo05X W0007X 9.9 7.5 10.5 11.25 23.1 17.5 24.5 63.75 100 80 130 175 1400 250 1510 1116 1400 47,600 6,000 18,000 Set Objective: IS9 25 35 75 30 Max Min O Value Of: 25 By Changing Variable Cells: SHS3:SHS6 Subject to the Constraints: SBS16:SES16SBS15 SES15 Bill of Labor Total Profit- 71,600 10 Product W0075C W00330 W0005X W0007X Drawing Extrusion Winding P 13 14 15 с 16 C 17 18 19 Reset AlI hours)4000 4200 2000 2300 2300 Utilised 1800 2800 Make Unconstrained Variables Non-Negative Select a Solving Method: Simplex LP Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear.Select the LP Simplex engine for linear Solver Problems. 21 23 24. 25

Optimal Product mix

Product	Optimal Product Mix
W0075C	1400
W0033C	200
W0005X	300
W0007X	0

Optimal objective Value = Profit = $ 71,600

4. To generate Answer report and Sensitivity report, check these options in the window that appears after running Solver. Then click OK.

SSolver Results Solver found a solution. All constraints and optimality conditions are satisfied. Reports Answer Sensitivity Limits O Keep Solver Solution O Restore Original Values Return to Solver Parameters Dialog OK Canceled Reports Create the type of report that you specify, and place each report on a separate sheet in the workbook

Answer report:

Obiective Cell (Max) Cell Name Original Value Final Value SIS9 Total Profit Profit 71600 71600 Variable Cells Cell $H$3 W0075C Product Mix $H$4 W0033C Product Mix SH$5 W0005X Product Mix $H$6 W0007X Product Mix Original Value 1400 200 300 Name Final Value Integer 1400 Contin 200 Contin 300 Contin 0 Contin Constraints Cell $B$16 Capacity Utilised Drawing $C$16 Capacity Utilised Extrusion SD$16 Capacity Utilised Winding ŞE$16 Capacity Utilised Packaging $H$3 W0075C Product Mix $H$4 W0033C Product Mix SH$5 W0005X Product Mix $H$6 W0007X Product Mix Status Slack 2200 1400 Name Cell Value Formula 1800 $B$16<-$B$15 Not Binding 2800 $C$16--$C$15 Not Binding 2000 SD$16<-SD$15 Binding 2300 SE$16 SE$15 Binding 1400 $H$3 SG$3 Binding 200 SHSA-$G$4 Not Binding 50 300 $H$5-SG$5 Not Binding 1210 0 $H$6< $G$6 Not Binding 1116

Sensitivity report:

Variable Cells Final Reduced Objective Allowable Allowable Cell $H$3 W0075C Product Mix $H$4 W0033C Product Mix SH$5 W0005X Product Mix $H$6 W0007X Product Mix Name Value Cost Coefficient Increase Decrease 1400 200 300 34 30 60 25 1E+30 12 12 15 4 30 22.5 1E+30 -15 Constraints Final Shadow Constraint Allowable Allowable Cell $B$16 Capacity Utilised Drawing $C$16 Capacity Utilised Extrusion SD$16 Capacity Utilised Winding SE$16 Capacity Utilised Packagin Name Value Price R.H. Side Increase Decrease 1800 2800 2000 2300 4000 4200 2000 2300 1E+30 1E+30 150 1050 2200 1400 600 900 10 20