Problem 1:
A manager must decide on the mix of products A and B to produce for the coming week, in order to maximize weekly profits. For each unit of Product A, it requires processing times of 1 minute for molding and 2 minutes for painting. For each unit of Product B, it requires 1 minute for molding, 1 minute for painting, and 1 minute for cutting. Based on the preliminary staff and machine schedules, there will be 300 minutes available for molding, 400 minutes for painting, and 250 minutes for cutting for the coming week. The manager also estimates that the profit margins will be $3 per unit of A and $2 per unit of B.
Solve this linear program using Excel Solver and show the sensitivity report, with the calculations as I have shown for the min-max range for the shadow prices.
Problem 2:
A refinery procures two types of crude oil: Type A and Type B, and processing either type into finished fuel products. Type A costs $70 per barrel to procure and costs $1 to process per barrel, while Type B costs $50 per barrel and costs $1.6 per barrel to process.
The refinery produces four end products: Gasoline which sells for $150 per barrel, kerosene which sells for $100 per barrel, fuel oil which sells for $85 per barrel, and residual oil which sells for $10 per barrel.
Demand for gasoline is 24,000 barrels per day, kerosene is 2000 barrels per day, fuel oil is 6000 barrels per day.
The refinery suffers from yield loss and for this reason Type A only yields 80% gasoline, 5% kerosene, and 10% fuel oil, while the remaining 5% is residual oil. Type B only yields 44% gasoline, 10% kerosene, and 36% fuel oil, while the remaining 10% is residual oil.
Develop a sourcing plan for the refinery to maximize profits/day. Profit = Revenue – raw material cost – processing cost. In other words, solve this linear program using Excel Solver and show the sensitivity report.
Problem 3:
A company makes two products (X and Y) using two machines (A and B). Each unit of X that is produced requires 50 minutes processing time on machine A and 30 minutes processing time on machine B. Each unit of Y that is produced requires 24 minutes processing time on machine A and 33 minutes processing time on machine B.
Available processing time on machine A is forecast to be 3400 minutes and on machine B is forecast to be 3000 minutes.
The demand for X in the current week is forecast to be 35 units and for Y is forecast to be 45 units.
Company policy is to maximize the combined sum of the units of X and the units of Y produced each week (left over inventory is acceptable)