PLEASE SHOW EXCEL FORMULAS STEP BY STEP

Case Study: Agri Chem Corporation

In June 2006, Harry Sinclair, General Manager of the Texas Division of Agri Chem Corporation, received notification from Ben Elliot of Enerco that natural gas supplies were being rapidly depleted. In the event of a shortage, Enerco, the main producer and distributor of natural gas in the Gulf South region, would allocate gas to its customers under the following provisions established by the Federal Power

Commission

• First Priority: Residential and commercial heating and cooling
• Second Priority: Commercial and industrial firms that use natural gas as a source of raw material
• Third Priority: Industrial firms that use natural gas as a boiler fuel

Elliot related to Sinclair that most of Agri Chem's uses were in the second and third priority classifications. Hence, Agri Chem would probably be subjected to "rolling brownouts", temporary and periodic curtailments of natural gas supplies. Enerco planned to monitor its pipeline pressures and order reductions to maintain minimum levels. Elliot preferred that Enerco's customers initiate the reduction process to minimize the effect on their industrial processes. Enerco was authorized, however, to curtail supplies unilaterally if pipeline pressure fell below minimum levels.

The natural gas shortage was created by the unprecedented heat waves during recent summers. Electrical generating plants were operating at capacity to supply electricity to operate air conditioning and refrigeration units. Although long-range plans called for these utility companies to convert to coal, oil, or nuclear fuel, natural gas remained the dominant boiler fuel.

CURTAILMENT PLAN

Agri Chem's problem was to determine which of its complexes would be least affected by a gas curtailment. Its Texas Division is located in the greater Houston area; plants are located in the suburbs of Deer Park and Battleground. Both of these areas would be included in the curtailment region in the event of a brownout. Except for Agri Chem's ammonia operations, all gas purchased was used as boiler fuel. In its ammonia plant, gas was used as a source of raw materials. (The manufacture of ammonia uses natural gas in the steam reforming process.) In a detailed discussion with Elliot, Sinclair learned that Enerco would not specify the products to be curtailed. The curtailment procedure would be based primarily on a customer's usage pattern. Hence, Agri Chem had the flexibility to absorb curtailments where they would have minimum impact on profits.

Based on this information, Sinclair called a staff meeting to discuss a contingency plan for allocation of natural gas among the firm's products if curtailments became a reality. The specific objective was to minimize the impact on profits/overhead contribution. After a week of study, the information in Table 1 was presented to Sinclair.

Agri Chem's contract with Enerco specified a maximum of 90,000

10 cubic feet per day for its

complexes. However, curtailments are projected to be based on current usage, not on contractual maximums. Enerco projects curtailments in the range of 20 to 40 percent.

Which of Agri Chem's complexes would be least affected by a gas curtailment?

Formulate the LP model and solve it using Excel Solver.

Tips:

1. There are two cases with this question. Case 1: 20% curtailment, Case 2: 40% curtailment. You should solve the problem for each case separately and then compare the results. The only difference between two cases formulation is "gas usage constraint".
2. Number of decision variables: 8
3. Obiective function: maximizing profit
4. Number of constraints: 9
5. Optimal total profit: 20% curtailment: $487,192.00, 40% curtailment: $428,075.56

Table 1. Financial and Operational Data at Agri Chem