Group Case Study Analysis Managerial Report - A 6 – 9-page case study analysis in the form of a Managerial Report is due to the instructor and all Group Mentors (see the case analysis write-up requirements in Blackboard). Teamwork is required for the presentation and PowerPoint and case analysis writing.
The Group Case study is to be conducted using linear programming in Solver. Points will be deducted for those groups who do not provide a solution in solver.
Students will be assigned to groups or teams and as a group complete the case study assignment. Groups are to share the load equitably and build on the individual strengths of group members. The final deliverable will be a Paper (written in APA format) and a PowerPoint Presentation. Teamwork is important because the group case studies will be graded as one collective, agreed-upon work; in other words, each member of that team gets the same grade for presentations. Team member participation and contribution will be extremely important. Each team member is expected to participate in the presentation of the case study. Once the teamwork is completed, a peer review will be conducted, and you will receive individual credits based on each of your teammates' reviews.
To earn credit for the group case analysis, you and/or your team must submit deliverables by the assigned due date and your submission must include the Excel datasheet – after that point, neither assignment will be accepted (under any circumstances). Case submissions MUST be submitted through the designated Blackboard link and the Data Excel Analysis Sheet showing formulas used in Solver included.
Both a presentation grade as well as a grade for the case study analysis will be assigned and each group should plan to complete the case study analyzed in the group's presentation.
Please use the attached file as a reference I need something similar to it and also i need the Excel sheet for my work as well.
Managerial Report – Duke Energy Coal Allocation
Table of Contents 1. Introduction 3 2. Problem 1: Cost and Coal Allocation 3 2.1 Solution 3 3. Problem 2: Average Cost of Coal and Energy Efficiency 3 3.1 Solution 4 4. Problem 3: Additional Coal Purchasing Choice 4 4.1 Solution 5 Recommendation 5 5. Problem 4: Revised Procurement Plan 5 5.1 Solution 5 6. Problem 5: Selling Electricity Decision 6 6.1 Solution 6 7. Conclusion 6 References 7
A linear programming method known as the coal allocation model is used by Duke Energy, a significant energy supplier in the United States along with Latin America, to optimize the coal allocation to its generating plants. The goal of this model is to identify the most affordable procedure for acquiring and supplying coal to the producing units. We will examine the coal allocation issue in this paper and offer suggestions to reduce expenses and boost effectiveness (Hauenstein & Holz, 2021).
Prior to allocating the coal to the producing units, we must decide how much to buy from each mining company while taking into account the fixed-tonnage as well as variable-tonnage contracts. To accomplish this, we can use Excel's Solver and linear programming (Hauenstein & Holz, 2021).
The discovered ideal coal allocation as follows by using Solver to the linear programming model:
Purchased 200,000 tons of coal from Consol, Inc. for Miami Fort Unit 7, and 350,000 tons from RAG for Miami Fort Unit 5.
Purchase 275,000 tons of coal for Beckjord Unit 1 from American Coal Sales.
Get 200,000 tons of coal for East Bend Unit 2 from Addington Mining.
Purchase 80,000 tons of coal for Zimmer Unit 1 from American Coal Sales.
$5,367,000 is the total price to buy, deliver, and process the coal.
The average coal price in cents per million BTUs for each producing unit will then be calculated. This metric will make it easier for us to comprehend how much fuel each unit uses (Wang et al., 2019).
Solution: For each generating unit, the average coal price, expressed in cents per million BTUs, is as follows:
97.58 cents per million BTUs for Miami Fort Unit 5.
Miami 108.29 cents per million BTUs for Fort Unit 7.
Unit 1 of Beckjord: 97.58 cents per million BTUs
Unit 2 in East Bend: 91.75 cents for per million BTUs
97.58 cents per million BTUs for Zimmer Unit 1.
Here, also figure out the typical BTU output per pound of coal at each generating unit. This metric shows how energy-efficient the coal that each unit receives is (Wang et al., 2019).
Each generating unit receives an average of the following BTUs per pound of coal:
Unit 5 at Miami Fort produces 10,240 BTUs per pound.
Miami 10,000 BTUs per pound at Fort Unit 7.
1 Beckjord Unit: 10,240 BTUs per pound
Unit 2 in East Bend: 10,240 BTUs per pound
10,240 BTUs per pound for Zimmer Unit 1.
80,000 more tons of coal from American Coal Sales are available for $30 per ton, so we need to decide if Duke Energy should buy them (Zerizghi et al., 2022).
We might consider the general financial impact of buying the extra coal while assessing this choice. At a cost of $30 per ton, an extra 80,000 tons of coal will cost $2,400,000.
We might consider the greater monetary effect of buying the extra coal while assessing this choice. At a cost of $30 per ton, an extra 80,000 tons of coal will cost $2,400,000 (Zerizghi et al., 2022).
We now know that the coal from Cyprus Amax has 13,000 BTUs of energy per pound. We must review our purchase strategy.
The amended coal allocation is as follows after running the linear programming model once more and changing the energy amount of Cyprus Amax coal to 13,000 BTUs per pound:
Buy 100,000 tons of coal to Consol, Inc. and 150,000 tons mined coal by RAG for Miami Fort Units 5 and 7.
Get American Coal Sales to supply you with 275,000 tons burned coal for Beckjord Unit 1.
200,000 tons of coal to be purchased from Addington Mining and East Bend Unit 2.
For Zimmer Unit 1, purchase 80,000 tons of coal through American Coal Sales.
$5,705,000 will be spent on the coal in total for the delivery, processing, and purchase costs.
Duke Energy should change its procurement strategy as previously stated in light of the updated energy content (Xu et al., 2021).
Duke Energy has the chance to resell 50,000 megawatt-hours of electricity at a price of $30 per megawatt-hour over the system. The next step is to decide if Duke Energy should sell the extra electricity and, if so, which generating units should be used to do so (Gao et al., 2021).
In assessing this choice, we can take into account the additional revenue generated by exporting the electricity as well as the related production costs.
The research suggests that Duke Energy sells the extra electricity. Miami Fort Unit 5 and West Bend Unit 2 are the generators that ought to generate the extra electricity (Xu et al., 2021).
In conclusion, Duke Energy could optimize its coal acquisition and allocation, leading to cost savings and increased energy efficiency. This is done by using the coal allocation method through linear programming. The suggestions in this study can assist Duke Energy in making decisions and enhancing its operations in the coal allocation process.
Gao, R., Wu, F., Zou, Q., & Chen, J. (2021). Optimal dispatching of wind-PV-mine pumped storage power station: A case study in Lingxin Coal Mine in Ningxia Province, China. Energy, 243, 123061–123061. https://doi.org/10.1016/j.energy.2021.123061
Hauenstein, C., & Holz, F. (2021). The U.S. coal sector between shale gas and renewables: Last resort coal exports? Energy Policy, 149, 112097. https://doi.org/10.1016/j.enpol.2020.112097
Wang, D., Wan, K., Song, X., & Liu, Y. (2019). Provincial allocation of coal de-capacity targets in China in terms of cost, efficiency, and fairness. Energy Economics, 78, 109–128. https://doi.org/10.1016/j.eneco.2018.11.004
Xu, J., Lv, T., Hou, X., Deng, X., & Liu, F. (2021). Provincial allocation of renewable portfolio standard in China based on efficiency and fairness principles. Renewable Energy, 179, 1233–1245. https://doi.org/10.1016/j.renene.2021.07.101
Zerizghi, T., Guo, Q., Tian, L., Wei, R., & Zhao, C. (2022). An integrated approach to quantify ecological and human health risks of soil heavy metal contamination around coal mining area. Science of the Total Environment, 814, 152653. https://doi.org/10.1016/j.scitotenv.2021.152653