4. (20 marks) Norwalk Chemical produces two types of glues (A and B) by blending three types of chemicals (I, II and III). The tables below show the sales prices (in dollars) per gallon of the glues and the costs (in dollars) per gallon of the chemicals. I II III 1.2 B 1.4 Sales price Cost 0.5 0.6 0.82 The available amounts of the chemicals, specifications of how much of the three chemicals should be used in A and B, and a special demand are shown below. G C2 C C C5 C6 C Cg C. C10 C1 There are 6,000 gallons of I available. There are 5,000 gallons of II available. There are 4,500 gallons of III available. At most 35% of A is I. At least 40% of Ais II. At most 10% of A is III. At least 25% of B is I. I in B is no more than III in B. At most 30% of B is III. A is at least 3,000 gallons due to special demand B is at least 4,500 gallons due to special demand The objective is to maximize the total profit. Let xij denote the number of gallons of chemical j (j = 1, II or III) used in the production of glue i (i = A or B). (a) Find the objective function. Express your answer in Excel-ready form. (b) Write mathematical descriptions for constraints Cz,Cs and C11. Express your answers with s in Excel-ready form. (c) Below is the sensitivity report generated with Excel with some terms omitted. 4. (e) (continued) Variable Cells Final Reduced Objective Coefficient Allowable Decrease Name Value Cost Allowable Increase 1.25 0.795454545 0 2.01 XAI XAII 1050 1650 0 Cell $C$20 $D$20 SE$20 $F$20 $G$20 $H$20 1E+30 1.69 XAIII 0 4.375 XBI M M2 3350 0 XBII 0 1E+30 1.060606061 1E+30 0.795454545 1E+30 1.060606061 XBUNI 2512.5 0 Constraints Final Shadow Allowable Constraint R.H. Side Allowable Decrease Cell Name Value Price C1 3562.5 0 6000 $C$24 $C$25 Increase 1E+30 2250 2437.5 1550 C2 5000 5000 2812.5 C3 1.91 M 2.01 $C$26 $C$27 1E+30 4500 0 1687.5 1050 C4 -1.27898E-13 450 CS -450 0 0 1E+30 450 C6 5.68434E-14 1.69 C7 -418.75 0 $C$28 $C$29 $C$30 $C$31 $C$32 $C$33 $C$34 0 450 300 0 1E+30 418.75 0 1392.857143 372.2222222 0 675 335 C8 0 2.01 C9 0 3.7 C10 -3000 -3000 0.4375 0 3000 2818.181818 1E+30 3875 C11 -8375 -4500 (1) Determine the values of M, M, and M3 in the sensitivity report. Justify your answers. (ii) How would the maximum profit change if the demand on A is changed to at least 3500 gallons? Justify your answer. (i) How would the maximum profit change if the available amounts of I, II are each increased by 1000 gallons? Justify your answer. (iv) If the selling price of A increased by $0.5 per unit, will the optimal solution be affected? Justify your answer. 4. (20 marks) Norwalk Chemical produces two types of glues (A and B) by blending three types of chemicals (I, II and III). The tables below show the sales prices (in dollars) per gallon of the glues and the costs (in dollars) per gallon of the chemicals. I II III 1.2 B 1.4 Sales price Cost 0.5 0.6 0.82 The available amounts of the chemicals, specifications of how much of the three chemicals should be used in A and B, and a special demand are shown below. G C2 C C C5 C6 C Cg C. C10 C1 There are 6,000 gallons of I available. There are 5,000 gallons of II available. There are 4,500 gallons of III available. At most 35% of A is I. At least 40% of Ais II. At most 10% of A is III. At least 25% of B is I. I in B is no more than III in B. At most 30% of B is III. A is at least 3,000 gallons due to special demand B is at least 4,500 gallons due to special demand The objective is to maximize the total profit. Let xij denote the number of gallons of chemical j (j = 1, II or III) used in the production of glue i (i = A or B). (a) Find the objective function. Express your answer in Excel-ready form. (b) Write mathematical descriptions for constraints Cz,Cs and C11. Express your answers with s in Excel-ready form. (c) Below is the sensitivity report generated with Excel with some terms omitted. 4. (e) (continued) Variable Cells Final Reduced Objective Coefficient Allowable Decrease Name Value Cost Allowable Increase 1.25 0.795454545 0 2.01 XAI XAII 1050 1650 0 Cell $C$20 $D$20 SE$20 $F$20 $G$20 $H$20 1E+30 1.69 XAIII 0 4.375 XBI M M2 3350 0 XBII 0 1E+30 1.060606061 1E+30 0.795454545 1E+30 1.060606061 XBUNI 2512.5 0 Constraints Final Shadow Allowable Constraint R.H. Side Allowable Decrease Cell Name Value Price C1 3562.5 0 6000 $C$24 $C$25 Increase 1E+30 2250 2437.5 1550 C2 5000 5000 2812.5 C3 1.91 M 2.01 $C$26 $C$27 1E+30 4500 0 1687.5 1050 C4 -1.27898E-13 450 CS -450 0 0 1E+30 450 C6 5.68434E-14 1.69 C7 -418.75 0 $C$28 $C$29 $C$30 $C$31 $C$32 $C$33 $C$34 0 450 300 0 1E+30 418.75 0 1392.857143 372.2222222 0 675 335 C8 0 2.01 C9 0 3.7 C10 -3000 -3000 0.4375 0 3000 2818.181818 1E+30 3875 C11 -8375 -4500 (1) Determine the values of M, M, and M3 in the sensitivity report. Justify your answers. (ii) How would the maximum profit change if the demand on A is changed to at least 3500 gallons? Justify your answer. (i) How would the maximum profit change if the available amounts of I, II are each increased by 1000 gallons? Justify your answer. (iv) If the selling price of A increased by $0.5 per unit, will the optimal solution be affected? Justify your