solve question 3 with excel please

3. Develop the Sensitivity Report for the 20% natural gas curtailment model. Use this report to answer the following questions. Each question is independent of the others. (a) Interpret the shadow prices for the natural gas avail- ability constraint and for the two constraints that limit the maximum phosphoric acid and chlorine that Coastal can produce. (b) Brenda Lamb, Bill Stock's marketing manager, believes that due to increased competition she may have to decrease the unit profit contributions for all products by 3.5% each. What is the impact of this de- crease on the production values? On the total profit? (c) Jose Fernandez, Bill Stock's production manager, thinks that he can increase the maximum production rate for chlorine and vinyl chloride monomer to 80% of capacity. For all other products, he thinks he can increase the maximum production rate to 100% of capacity. What would be the impact of this change on the total profit? 252 CHAPTER 8 LINEAR PROGRAMMING SENSITIVITY ANALYSIS (d) Bill Stock thinks he can persuade Coastal's Mississippi Division to give him 1,000,000 cubic feet of its allot- ment of natural gas from Cajan Pipeline. However, due to the Mississippi Division's pricing contract with Cajan Pipeline, this additional amount of natu- ral gas will cost Stock an additional $1.50 per 1,000 cubic feet (over current costs). Should Stock pursue this option? If so, what is the impact of this additional gas on his total profit? What is the impact if Bill Stock can persuade the Mississippi Division to give him 3,000,000 cubic feet of its allotment of natural gas from Cajan Pipeline? 4. Redo question 3 using the Sensitivity Report for the 40% natural gas curtailment model. In addition, interpret the reduced cost for caustic soda. Source: Jerry Kinard, Western Carolina University, and Brian Kinard, University of North Carolina - Wilmington. TABLE 8.3 Contribution to Profit and Overhead CONTRIBUTION ($ PER TON) CAPACITY (TONS PER DAY) PRODUCT Phosphoric acid 60 400 Urea 80 250 Ammonium phosphate 90 300 Ammonium nitrate 100 300 50 800 Chlorine 50 1,000 Caustic soda 65 500 Vinyl chloride monomer 70 400 Hydrofluoric acid MAXIMUM PRODUCTION RATE (PERCENTAGE OF CAPACITY) 80 8 8 8 8 8 8 % 80 60 60 60 80 NATURAL GAS CONSUMPTION (1,000 CU. FT. PER TON) 5.5 7.0 8.0 10.0 15.0 16.0 12.0 11.0