Case Study: Coastal States Chemicals and Fertilizers
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 manufactured at those plants that required significant amounts of natural gas were phosphoric acid, urea, ammonium phosphate, ammonium nitrate, chlorine, caustic soda, vinyl chloride monomer, and hydrofluoric acid.
Bill 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 Table 4.3 were presented (see page 159).
Coastal States' contract with Cajan Pipeline specified a maximum natural gas consumption of 36,000,000 cubic feet per day for all 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.) You have been given the Solver output reports from this model. The output is based on a 20% and a 40% natural gas curtailment along with the sensitivity reports for each.
Bill Stock would like to know what the impact of a larger curtailment would be. If the shortages are greater than expected, what are the impacts to Coastal States?
Discussion Questions
1. Rework the model and specify the production rates for each product for the following scenarios:
A. 25% natural gas curtailment
B. 45% natural gas curtailment
2. What impact will the natural gas shortage have on company profits?
3. Develop the sensitivity report for the 25% 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 availability constraint.
B. Interpret the shadow prices for the two constraints that limit the maximum phosphoric acid and chlorine that Coastal can produce.
C. 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 decrease on the production values? On the total profit?
D. 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?
E. Bill Stock thinks he can persuade Coastal's Mississippi Division to give him 1,000,000 cubic feet of its allotment of natural gas from Cajan Pipeline.
However, due to the Mississippi Division's pricing contract with Cajan Pipeline, this additional amount of natural 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 45% natural gas curtailment model. In addition, interpret the reduced cost for caustic soda.
QSO520 Final Case Study
You are looking to open up a cupcake shop in a high-traffic tourist area. In order to get your business open, you will need investors to provide you with $250,000 dollars. You are going to be making a pitch to a local bank for a portion of the money. T
he business environment you are looking to operate in is one in which there is a heavy amount of seasonal business. However, there is not enough non-seasonal business to support long-term growth. Based on this information, before you submit your business plan to the bank for consideration, you will need to perform a simulation analysis to determine the optimal model for your business.
You have made the following assumptions: Your equipment will allow you to only produce 50 batches of cupcakes per day. You have determined that the daily demand will follow the distribution shown in the following table:
|
Daily Demand
|
Probability
|
|
20
|
0.08
|
|
25
|
0.12
|
|
35
|
0.25
|
|
40
|
0.20
|
|
45
|
0.20
|
|
50
|
0.15
|
You will need $45,000 per month for your business to remain solvent. You are going to develop a business plan for the bank based on your top selling item: the bacon chocolate cupcake.
There are 12 cupcakes in every batch for a total of 600 cupcakes made per day. Each batch of bacon chocolate cupcakes costs $45 dollars to make and the entire batch can be sold for $100.
You are able to sell any unsold batches for $25 the next day. As part of your analysis, you will use Monte Carlo simulation and Scenario Manager in MS Excel to perform a simulation on your data. Refer to the examples in Chapter 10, pages 454-462, for additional reference.
Assignment Deliverables, Part 1
1. Use a Monte Carlo simulation in MS Excel to simulate 1 month (26 days) of operation to calculate monthly profit.Replicate this simulation for 156 days, 312 days, 624 days, and 936 days to calculate average monthly profit.
2. You are looking to expand production to see if you can increase profits by baking 55,60, 65, and 70 batches per day. On a separate tab of the same spreadsheet, use Scenario Manager to create a scenario summary for each batch.
Assignment Deliverables, Part 2
Write a 2- to 3-page paper summarizing your findings along with a recommendation either to move forward with a formal business plan or to re-evaluate the business model.Be sure to address the following:
1. Based on your calculations, will the business hit the revenue goal of $45,000 per month and be sustainable?
2. Which batch quantity would recommend and why?
Attachment:- coastal_states.rar