Answered step by step
Verified Expert Solution
Question
1 Approved Answer
PLEASE SOLVE IN EXCEL USING SOLVER THANK YOU Skillings Industrial Chemicals, Inc., operates a refinery in southwestern Ohio near the Ohio River. The company's primary
PLEASE SOLVE IN EXCEL USING SOLVER THANK YOU
Skillings Industrial Chemicals, Inc., operates a refinery in southwestern Ohio near the Ohio River. The company's primary product is manufactured from a chemical process that requires the use of two raw materials - material A and material B. The production of 1 pound of the primary product requires the use of 1 pound of material A and 2 pounds of material B. The output of the chemical process is 1 pound of the primary product, 1 pound of liquid waste material, and 1 pound of solid waste by-product. The solid waste byproduct is given to a local fertilizer plant as payment for picking it up and disposing of it. The liquid waste material has no market value, so the refinery has been dumping it directly into the Ohio River. The company's manufacturing process is shown schematically in Figure 1. Figure 1: Manufacturing Process Government pollution guidelines established by the Environmental Protection Agency will no longer permit disposal of the liquid waste directly into the river. The refinery's research group has developed the following set of alternative uses for the liquid waste material 1. Produce a secondary product K by adding 1 pound of raw material A to every pound of liquid waste. 2. Produce a secondary product M by adding 1 pound of raw material B to every pound of liquid waste. 3. Specially treat the liquid waste so that it meets pollution standards before dumping it into the river. These three alternatives are depicted in Figure 2 Figure 2: Manufacturing Process The company's management knows that the secondary products will be low in quality and may not be very profitable. However, management also recognizes that the special treatment alternative will be a relatively expensive operation. The company's problem is to determine how to satisfy the pollution regulations and still maintain the highest possible profit. How should the liquid waste material be handled? Should Skillings produce product K, produce product M, use the special treatment, or employ some combination of the three alternatives? Last month 10,000 pounds of the company's primary product were produced. The accounting department has prepared a cost report showing the breakdown of fixed and variable expenses that were incurred during the month. In this cost analysis, the fixed-cost portion of the expenses is the same every month regardless of production level. Direct labor costs are expected to run $0.20 per pound for product K and $0.10 per pound for product M. The company's primary product sells for $5.70 per pound. Secondary products K and M sell for $0.85 and $0.65 per pound, respectively. The special treatment of the liquid waste will cost $0.25 per pound. A company accountant believes that product K is too expensive to manufacture and cannot be sold at a price that recovers its material and labor cost. The accountant's recommendation is to eliminate product K as an alternative. For the upcoming production period, 5000 pounds of raw material A and 7000 pounds of raw material B will be available. Develop an approach to the problem that will allow the company to determine how much primary product to produce, given the limitations on the amounts of the raw material available. Include recommendations as to how the company should dispose of the liquid waste to satisfy the environmental protection guidelines. How many pounds of product K should be produced? How many pounds of product M should be produced? How many pounds of liquid waste should be specially treated and dumped into the river? Include a discussion and analysis of the following in your report: 1. A cost analysis showing the profit contribution per pound for the primary product, product K, and product M 2. The optimal production quantities and waste disposal plan, including the projected profit 3. A discussion of the value of additional pounds of each raw material 4. A discussion of the sensitivity analysis of the objective function coefficients 5. Comments on the accountant's recommendation to eliminate product K as an alternative: Does the recommendation appear reasonable? What is your reaction to the recommendation? How would the optimal solution change if product K were eliminated? Skillings Industrial Chemicals, Inc., operates a refinery in southwestern Ohio near the Ohio River. The company's primary product is manufactured from a chemical process that requires the use of two raw materials - material A and material B. The production of 1 pound of the primary product requires the use of 1 pound of material A and 2 pounds of material B. The output of the chemical process is 1 pound of the primary product, 1 pound of liquid waste material, and 1 pound of solid waste by-product. The solid waste byproduct is given to a local fertilizer plant as payment for picking it up and disposing of it. The liquid waste material has no market value, so the refinery has been dumping it directly into the Ohio River. The company's manufacturing process is shown schematically in Figure 1. Figure 1: Manufacturing Process Government pollution guidelines established by the Environmental Protection Agency will no longer permit disposal of the liquid waste directly into the river. The refinery's research group has developed the following set of alternative uses for the liquid waste material 1. Produce a secondary product K by adding 1 pound of raw material A to every pound of liquid waste. 2. Produce a secondary product M by adding 1 pound of raw material B to every pound of liquid waste. 3. Specially treat the liquid waste so that it meets pollution standards before dumping it into the river. These three alternatives are depicted in Figure 2 Figure 2: Manufacturing Process The company's management knows that the secondary products will be low in quality and may not be very profitable. However, management also recognizes that the special treatment alternative will be a relatively expensive operation. The company's problem is to determine how to satisfy the pollution regulations and still maintain the highest possible profit. How should the liquid waste material be handled? Should Skillings produce product K, produce product M, use the special treatment, or employ some combination of the three alternatives? Last month 10,000 pounds of the company's primary product were produced. The accounting department has prepared a cost report showing the breakdown of fixed and variable expenses that were incurred during the month. In this cost analysis, the fixed-cost portion of the expenses is the same every month regardless of production level. Direct labor costs are expected to run $0.20 per pound for product K and $0.10 per pound for product M. The company's primary product sells for $5.70 per pound. Secondary products K and M sell for $0.85 and $0.65 per pound, respectively. The special treatment of the liquid waste will cost $0.25 per pound. A company accountant believes that product K is too expensive to manufacture and cannot be sold at a price that recovers its material and labor cost. The accountant's recommendation is to eliminate product K as an alternative. For the upcoming production period, 5000 pounds of raw material A and 7000 pounds of raw material B will be available. Develop an approach to the problem that will allow the company to determine how much primary product to produce, given the limitations on the amounts of the raw material available. Include recommendations as to how the company should dispose of the liquid waste to satisfy the environmental protection guidelines. How many pounds of product K should be produced? How many pounds of product M should be produced? How many pounds of liquid waste should be specially treated and dumped into the river? Include a discussion and analysis of the following in your report: 1. A cost analysis showing the profit contribution per pound for the primary product, product K, and product M 2. The optimal production quantities and waste disposal plan, including the projected profit 3. A discussion of the value of additional pounds of each raw material 4. A discussion of the sensitivity analysis of the objective function coefficients 5. Comments on the accountant's recommendation to eliminate product K as an alternative: Does the recommendation appear reasonable? What is your reaction to the recommendation? How would the optimal solution change if product K were eliminatedStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started