Please answer question 1 with excel output and explain how you used solver to get the correct answers. Will upvote for correct solutions

Additional Case Study: Coastal States Chemicals

to accompany MODULE B: Linear Programming

In November, Bill Stock, general manager for the Louisiana Division of Coastal States Chemicals and Fertilizers, received a letter from Fred McNair of the Cajun Pipeline Company which notified Coastal States that priorities had been established for the allocation of natural gas. The letter stated that Cajun Pipeline, the primary supplier of natural gas to Coastal States, might be instructed to curtail natural gas supplies to its industrial and commercial customers by as much as 40 percent during the ensuing winter months. Moreover, Cajun Pipeline had the approval of the Federal Power Commission (FPC) to curtail such supplies. Possible curtailment was attributed to the priorities established for the use of natural gas:

First priority: Residential and commercial heating

Second priority: Commercial and industrial users whereby natural gas is used as a source of raw material

Third priority: Commercial and industrial users whereby natural gas is used as boiler fuel

Almost all of Coastal States uses of natural gas were in the second and third priorities. Hence, its plants were certainly subject to brown-outs, or natural gas curtailments. The occurrence and severity of the brown-outs depended on a number of complex factors. First of all, Cajun Pipeline was part of an interstate transmission network that delivered natural gas to residential and commercial buildings on the Atlantic Coast and in northeastern regions of the United States. Hence, the severity of the forthcoming winter in these regions would have a direct impact on the use of natural gas.

Secondly, the demand for natural gas was soaring because it was the cleanest and most efficient fuel. There were almost no environmental problems in burning natural gas. Moreover, maintenance problems due to fuel-fouling in fireboxes and boilers were negligible with natural gas systems. Also, burners were much easier to operate with natural gas as compared to the use of oil or the stoking operation when coal was used as fuel.

Finally, the supply of natural gas was dwindling. The traditionally depressed price of natural gas had discouraged new exploration for gas wells; hence, shortages appeared imminent.

Stock and his staff at Coastal States had been aware of the possibility of shortages of natural gas and had been investigating ways of converting to fuel oil or coal as a substitute for natural gas. Their plans, however, were still in the developmental stages. Coastal States required an immediate contingency plan to minimize the effect of a natural gas curtailment on its multiplant operations. The obvious question was, what operations should be curtailed and to what extent to minimize the adverse effect upon profits? Coastal States had the approval from the FPC and Cajun Pipeline to specify which of its plants would bear the burden of the curtailment if such cutbacks were necessary. McNair, of Cajun Pipeline, replied, "Its your pie: we dont care how you divide it if we make it smaller."

The Model Six plants of Coastal States Louisiana Division were to share in the "pie." They were all located in the massive Baton Rouge-Geismar-Gramercy industrial complex along the Mississippi River between Baton Rouge and New Orleans. Products produced at those plants which required significant amounts of natural gas were phosphoric acid, urea, ammonium phosphate, ammonium nitrate, chlorine, caustic soda, vinyl chloride monomer, and hydrofluoric acid. Stock called a meeting of members of his technical staff to discuss a contingency plan for allocation of natural gas among the products if a curtailment developed. The objective was to minimize the impact on profits.

After detailed discussion, the meeting was adjourned. Two weeks later, the meeting reconvened. At this session, the data in the accompanying table were presented. Coastal States contract with Cajun Pipeline specified a maximum natural gas consumption of 36,000 cu ft x 10 per day for all of the six member plants. With these data, the technical staff proceeded to develop a model that would specify changes in production rates in response to a natural gas curtailment. (Curtailments are based on contracted consumption and not current consumption.)

Discussion Questions

1. Develop a contingency model and specify the production rates for each product for:

a. a 20% natural gas curtailment and

b. a 40% natural gas curtailment

2. Explain which of the products in the table should require the most emphasis with regard to energy conservation.

3. What problems do you foresee if production rates are not reduced in a planned and orderly manner?

4. What impact will the natural gas shortage have on company profits?

$ per Capacity (Tons ton per day) 60 400 80 250 90 300 Maximum Production Rate (Percent of Capital) 80 80 90 Natural gas Consumption (1,000 CU FT per ton) 5.5 7.0 8.0 100 300 100 10.0 Product Phosphoric acid Urea Ammonium phosphate Ammonium nitrate Chlorine Caustic soda Vinyl chloride monomer Hydroflouric acid 800 60 15.0 50 50 65 1,000 60 16.0 500 60 12.0 70 400 80 11.0