Please make a Linear Program on Excel to determine the optimal mix.

Case: BADGER ENERGY LIMITED Mark Stephen was manager of the Production Fuels Department of Badger Energy Limited (BEL), a small utility in southeastern Ohio BEL had three steam electric power plants located in Athens Zanesville, and Strobesville-whose primary energy source was conl Fach month coal for those plante was purchased from a heterogeneous collection of vendors in Ohio Pensylvania, and West Vir, 1 m size for all father-md-son creations to large mung Cannes Stephen was responsible for the monthly cond-procurement process, including how much to purchase from each vendor and which specific plant (or plants) each vendor should supply. In October 2016, Stephen's mmediate task was to determine November's coal- procurement schedule. BEL had recently retained the services of a consulting firm to analyze aspects of its operations, including the coal procurement process. Stephen boped to use the opportunity of the consultants analysis to rethink the entire procurement process. Te also hoped the report would shed some light on two related issues that had been a course of controversy within the department Coal Compared with oil, natural gas, and nuclear energy. coal was a relatively cheap source of fuel during the 2010s. Coal is a combustible rock formed by the underground compression of partially decomposed plant matter over millions of years. There are four major types of coal. classified according to energy coctent: Lignite lowest energy coatest), sub bituminous, bituminous (most widely used as a fisel source), and anthracite (highest energy content). Coal's energy content or thermal value) is measured in British thermal units (Bras) (One Rru is the amount of heat needed to raise a jound of water one degree Fahrenheit) Petitions coal typically contains on the under of 15,000 Btu pet pound (Btw. b) There are three major determinants of the quality of coal One is foral moisture content There me two distinct types of more associated with coul. Freemoiste lies on the face of the cual. Its presence, winch depends pumanly on conditions in the mine and in transat, is an mportant parameter in the design of coal-handling and preparation equipment. Inherent moisture is trapped within the pores of the coal itself and is present even when the surface of the cappears chy. Both types of meste seduce my coulent. A second determinant of coal quality is ash content. Ash is the incombustible residue that remains after the coal is burned. Like moisture, a high ask coateat increases shipping, bandling. and preparation costs while reclucing thermal value. Additional equipment and expense is required periodically to remove ash from a coal-fired famace Frilure to do so adequately has a long-term impact on the life of a furnace 2 by the coal Te further control The third major determinant of quality is sufur content. When coal is burned, sulfur oxides are released, creating pollution and contributing to the corrosion of vital plant parts. Some sulfar can be removed sed prior to of piticks. During the 2010, the maximum level of sulfun oxide pollution was regulated by law Ench coal-fired plant was thus forced to restact the amount of sulfia in the coal ituned on the basis of the specific pollution-control equipment it was using BEL's Coal-Procurement Process Exchih, verdens mested in supplymig emne of BEL's cual-Gited power plants completed an offer sheet specifying the amount of coal they had to sell along with its quality and price Quality was expressed in in terms of Bulb and moisture, ash, and sulfur content. Vendors were asked to quote a per ton price, transportation included, for each power plant they were willing and able to supply. The Production Fuels department took all offers, adjusted them 1st performance (particularly the amount of coal available for purchase, which was often overstated and had to be adjusted downward), and summarized the results in a document called the offers exhat pont (Exhibit l). At the same time, each of the three coal-fired power plants submitted its requirements for the upcoming month. Corporate policy dictated that a plant have sufficient Btu's cn hand each month to satisfy 120% of expected demand. Exactly how many Bhus to order for the upcoming month depended on both the estimated ending investory of coal in the current month (stated in terms of Bras) and the expected demand daring the upcoming month Each plant also provided minimum acceptable quality standards for moisture, ash, and sulfar content. Each of those was stated in terms of a weighted average of all coal delivered to the plant in the month For example, 1.000 tons of coal with 2% sulfar content and 500 tons of coal with 1% sulfun content would produce an overall 1.67% self-content level this man er was not allowed to exceed the sulfur standard. The sulfu standards were set by law, masture and ash standards were left to the discretion of the individual plant managers, who were familiar with the costs associated with handling the increased levels of moisture and ash at their respective plants The Production Fuels Department was responsable for taking the offers edit report and the plant requireasents, summarized in the plant requirements edit report Exhibit 2). and arriving at an overall coal-procurement plan. Stephen, as manager of the department, had the flexibility to negotiate with both vendors and plent managers strike a better overall deal for the company For example, he could negotiate juice reductions d'or qumlily increases with vemits Sumatly, he could make plant pas aware of pusticulely restrictave quality requaements and negotiate to have them relaxed. Ultimately, Stephen n was responsible for approving the overall coal procurement plan. Recently, the Production Fuels Department had been struggling with two esses related to the coal-procurement process: long-term contracts and safety-stock levels. Long-Term Contracts Because of a wality's need to have a need some of fuel, long-loam contacts with coal vendors were a long-standing industry practice. A long-term contract with a vendor obligated the utility to buy a a minimum amount int of coal each month from that reador at the contract specified price. The balance of the utility's seeds was met by purchasing additional coal on the spot market Prior to 2003, BET had purchased approximately 65% of its coal on long-term contact. The energy crisis of the 1990s and resulting surge in demand for coal md coal prices band precipitated an upward trend in that figue. By 2016, BEL was purchasing 80% of its coal on long-term contract (vendors in late 2016 with whom BEL hnd long-term contracts and the contract amounts are mdicated in Exhibit) As the energy crisis eased, however, the availability of coal became less of a concem. Moreover by 1986 prices on the spot market were maning about $ per tonless the long-term contract prices. Many people in the Production Fiels department thought that the percentage of coal purchased implem contact should be reduced, prelaps back to the 65% level Stehen estimated that time to the 65% figuee would allow BEL to reduce the amount of coal purchased on long-term compact by 12,000 tons. If such a reduction were to be made it was not clear to Stephen which of the current long-term contracts should be redared and of Safety Stack of 20% Running out of coal forced a wility to purchase energy from a neighboring utility at a premium price In August, for example, BEL had sold 10 billion plus Bison Energency basis to a wally in western Pennsylvania for $20,000. A ash of such purchases by BEL in the 1990s bad daiven the company to zase its requued safety-stock level from 15% to 20% Since the safety stocked becametesse, however, none of BEL's plants had eser been forced to purchase outside nagy. In fact, over the past three years, actual monthly energy demand had rarely exceeded 110% of expected demand. Some BEL officials attributed this situation to improved forecasting techniques, while others thought it represented a leveling off of demand Whatever the reason, many at BEL were now pushing to reduce the safety-stock level back to 15% Stephen recognized that such a reduction would save BEL carrying costs on the coal needed to supply 5% of overall Blu demand. From Octob's coul- procurement numbers, Stephen estunated that the average cost of a balhou Btus at each plaut were as follows: Plaut Strobesville Zanesville Athens Average cost of 1 billion Blus $1.740 S1610 S1 625 He woodered if those were the appropriate costs to use, and if so, bow to balance the cost SAVIDRS against increased possibility of ranning out of coal Exhibit BADGER ENERGY LIMITED Offers Edit Report for November 2 22.500 12.210 6.296 14% 0.9% UUED 3.000 11,000 3.000 1.00 11 12 MocGillan (minimum of 8 000 tons) foster Hestes mot 20.000 tons Monghela Consol (minimum of 18,000 tons) Exhibit BADGER ENERGY LIMITED Plant Requirements Edit Report for November Brus (billions) Plant Maximum Allowable Weighted Average Moisture Ash Sulfus Strobenville Zanesville Athens 800! 500 600 7.00 70% 11% 18% 1.09 2.09 1.0% Number of Etusteat, waen added to October's expected ending imatory, would equal 120% od November's expected Case: BADGER ENERGY LIMITED Mark Stephen was manager of the Production Fuels Department of Badger Energy Limited (BEL), a small utility in southeastern Ohio BEL had three steam electric power plants located in Athens Zanesville, and Strobesville-whose primary energy source was conl Fach month coal for those plante was purchased from a heterogeneous collection of vendors in Ohio Pensylvania, and West Vir, 1 m size for all father-md-son creations to large mung Cannes Stephen was responsible for the monthly cond-procurement process, including how much to purchase from each vendor and which specific plant (or plants) each vendor should supply. In October 2016, Stephen's mmediate task was to determine November's coal- procurement schedule. BEL had recently retained the services of a consulting firm to analyze aspects of its operations, including the coal procurement process. Stephen boped to use the opportunity of the consultants analysis to rethink the entire procurement process. Te also hoped the report would shed some light on two related issues that had been a course of controversy within the department Coal Compared with oil, natural gas, and nuclear energy. coal was a relatively cheap source of fuel during the 2010s. Coal is a combustible rock formed by the underground compression of partially decomposed plant matter over millions of years. There are four major types of coal. classified according to energy coctent: Lignite lowest energy coatest), sub bituminous, bituminous (most widely used as a fisel source), and anthracite (highest energy content). Coal's energy content or thermal value) is measured in British thermal units (Bras) (One Rru is the amount of heat needed to raise a jound of water one degree Fahrenheit) Petitions coal typically contains on the under of 15,000 Btu pet pound (Btw. b) There are three major determinants of the quality of coal One is foral moisture content There me two distinct types of more associated with coul. Freemoiste lies on the face of the cual. Its presence, winch depends pumanly on conditions in the mine and in transat, is an mportant parameter in the design of coal-handling and preparation equipment. Inherent moisture is trapped within the pores of the coal itself and is present even when the surface of the cappears chy. Both types of meste seduce my coulent. A second determinant of coal quality is ash content. Ash is the incombustible residue that remains after the coal is burned. Like moisture, a high ask coateat increases shipping, bandling. and preparation costs while reclucing thermal value. Additional equipment and expense is required periodically to remove ash from a coal-fired famace Frilure to do so adequately has a long-term impact on the life of a furnace 2 by the coal Te further control The third major determinant of quality is sufur content. When coal is burned, sulfur oxides are released, creating pollution and contributing to the corrosion of vital plant parts. Some sulfar can be removed sed prior to of piticks. During the 2010, the maximum level of sulfun oxide pollution was regulated by law Ench coal-fired plant was thus forced to restact the amount of sulfia in the coal ituned on the basis of the specific pollution-control equipment it was using BEL's Coal-Procurement Process Exchih, verdens mested in supplymig emne of BEL's cual-Gited power plants completed an offer sheet specifying the amount of coal they had to sell along with its quality and price Quality was expressed in in terms of Bulb and moisture, ash, and sulfur content. Vendors were asked to quote a per ton price, transportation included, for each power plant they were willing and able to supply. The Production Fuels department took all offers, adjusted them 1st performance (particularly the amount of coal available for purchase, which was often overstated and had to be adjusted downward), and summarized the results in a document called the offers exhat pont (Exhibit l). At the same time, each of the three coal-fired power plants submitted its requirements for the upcoming month. Corporate policy dictated that a plant have sufficient Btu's cn hand each month to satisfy 120% of expected demand. Exactly how many Bhus to order for the upcoming month depended on both the estimated ending investory of coal in the current month (stated in terms of Bras) and the expected demand daring the upcoming month Each plant also provided minimum acceptable quality standards for moisture, ash, and sulfar content. Each of those was stated in terms of a weighted average of all coal delivered to the plant in the month For example, 1.000 tons of coal with 2% sulfar content and 500 tons of coal with 1% sulfun content would produce an overall 1.67% self-content level this man er was not allowed to exceed the sulfur standard. The sulfu standards were set by law, masture and ash standards were left to the discretion of the individual plant managers, who were familiar with the costs associated with handling the increased levels of moisture and ash at their respective plants The Production Fuels Department was responsable for taking the offers edit report and the plant requireasents, summarized in the plant requirements edit report Exhibit 2). and arriving at an overall coal-procurement plan. Stephen, as manager of the department, had the flexibility to negotiate with both vendors and plent managers strike a better overall deal for the company For example, he could negotiate juice reductions d'or qumlily increases with vemits Sumatly, he could make plant pas aware of pusticulely restrictave quality requaements and negotiate to have them relaxed. Ultimately, Stephen n was responsible for approving the overall coal procurement plan. Recently, the Production Fuels Department had been struggling with two esses related to the coal-procurement process: long-term contracts and safety-stock levels. Long-Term Contracts Because of a wality's need to have a need some of fuel, long-loam contacts with coal vendors were a long-standing industry practice. A long-term contract with a vendor obligated the utility to buy a a minimum amount int of coal each month from that reador at the contract specified price. The balance of the utility's seeds was met by purchasing additional coal on the spot market Prior to 2003, BET had purchased approximately 65% of its coal on long-term contact. The energy crisis of the 1990s and resulting surge in demand for coal md coal prices band precipitated an upward trend in that figue. By 2016, BEL was purchasing 80% of its coal on long-term contract (vendors in late 2016 with whom BEL hnd long-term contracts and the contract amounts are mdicated in Exhibit) As the energy crisis eased, however, the availability of coal became less of a concem. Moreover by 1986 prices on the spot market were maning about $ per tonless the long-term contract prices. Many people in the Production Fiels department thought that the percentage of coal purchased implem contact should be reduced, prelaps back to the 65% level Stehen estimated that time to the 65% figuee would allow BEL to reduce the amount of coal purchased on long-term compact by 12,000 tons. If such a reduction were to be made it was not clear to Stephen which of the current long-term contracts should be redared and of Safety Stack of 20% Running out of coal forced a wility to purchase energy from a neighboring utility at a premium price In August, for example, BEL had sold 10 billion plus Bison Energency basis to a wally in western Pennsylvania for $20,000. A ash of such purchases by BEL in the 1990s bad daiven the company to zase its requued safety-stock level from 15% to 20% Since the safety stocked becametesse, however, none of BEL's plants had eser been forced to purchase outside nagy. In fact, over the past three years, actual monthly energy demand had rarely exceeded 110% of expected demand. Some BEL officials attributed this situation to improved forecasting techniques, while others thought it represented a leveling off of demand Whatever the reason, many at BEL were now pushing to reduce the safety-stock level back to 15% Stephen recognized that such a reduction would save BEL carrying costs on the coal needed to supply 5% of overall Blu demand. From Octob's coul- procurement numbers, Stephen estunated that the average cost of a balhou Btus at each plaut were as follows: Plaut Strobesville Zanesville Athens Average cost of 1 billion Blus $1.740 S1610 S1 625 He woodered if those were the appropriate costs to use, and if so, bow to balance the cost SAVIDRS against increased possibility of ranning out of coal Exhibit BADGER ENERGY LIMITED Offers Edit Report for November 2 22.500 12.210 6.296 14% 0.9% UUED 3.000 11,000 3.000 1.00 11 12 MocGillan (minimum of 8 000 tons) foster Hestes mot 20.000 tons Monghela Consol (minimum of 18,000 tons) Exhibit BADGER ENERGY LIMITED Plant Requirements Edit Report for November Brus (billions) Plant Maximum Allowable Weighted Average Moisture Ash Sulfus Strobenville Zanesville Athens 800! 500 600 7.00 70% 11% 18% 1.09 2.09 1.0% Number of Etusteat, waen added to October's expected ending imatory, would equal 120% od November's expected