Solutions to Lp Problems
Furnco manufactures desks and chairs. Each desk uses 4 units of wood, and each chair uses 3 units of wood. A desk contributes $40 to profit, and a chair contributes $25. Marketing restrictions require that the number of chairs produced be at least twice the number of desks produced. There are 20 units of wood available. Using the graph below, determine a production plan that maximizes Furnco’s profit. a) Draw isoprofit lines where the total profit equals 125, 150, 175, and 200.
Produce 2 desks and 4 chairs. c) What is the optimal total profit? Point C is (2, 4), where the total profit is $180. 2. A farmer in Iowa owns 45 acres of land. She is going to plant each acre with wheat or corn. Each acre planted with wheat yields $200 profit; each with corn yields $300 profit. The labor and fertilizer used for each acre are given in the table below. 100 workers and 120 tons of fertilizer are available. | |Wheat |Corn | |Labor |3 workers |2 workers | |Fertilizer |2 tons |4 tons |
Determine the planting scheme that maximizes total profit. There are four critical points: Point A (at the origin), Point B (where the fertilizer constraint crosses the non-negativity constraint on wheat), Point C (where the fertilizer constraint line crosses the labor constraint), and Point D, where the labor constraint crosses the non-negativity constraint on corn). The best solution is at Point C (20, 20). Plant 20 acres of wheat and 20 acres of corn. d) What is the optimal total profit? The optimal total profit is $10,000. 3. A bank is attempting to determine where its assets should be invested during the current year.
At present, $500,000 is available for investment in bonds, home loans, auto loans, and personal loans. The annual rates of return on each type of investment are known to be the following: bonds, 10%; home loans, 16%; auto loans, 13%; and personal loans, 20%. To ensure that the bank’s portfolio is not too risky, the bank’s investment manager has placed the following three restrictions on the bank’s portfolio: • The amount invested in personal loans cannot exceed the amount invested in bonds. • The amount invested in home loans cannot exceed the amount invested in auto loans. No more than 25% of the total amount invested may be in personal loans. Below are various elements of the Excel model used to solve the problem: the spreadsheet model, the Solver parameters, the Solver options, the answer report and the sensitivity report.
From cell E8 in the answer report, the optimal portfolio will return 14. 5% ($73,750). c) What would be the improvement in the return on investment if the limit on the total amount invested in personal loans were increased to 30%? We look at cell E20 in the Sensitivity Report, and see that the shadow price is 1%. That means that for every unit of increase in this constraint’s right-hand side, we will realize a 1% improvement in the objective function. If we change the right-hand side of this constraint from 25% to 30% (a change that is within the allowable increase shown in cell G20 of the Sensitivity Report), then the objective function will increase by 0. 05 * 0. 01 = 0. 0005.
Our portfolio return would go from 0. 1475 to 0. 1480 (from 14. 75% to 14. 80%). d) If the return on bonds increases from 10% to 13%, what will happen to the optimal allocation of funds? Cell G9 in the Sensitivity Report indicates that the bond return would have to increase by at least 4. 5% before the optimal investment mix would change. Since this is only a 3% increase, the portfolio would not change (although it would become more profitable). 4. Sunco Oil manufactures three types of gasoline (gas 1, 2, and 3). Each type is produced by blending three types of crude oil (crude 1, crude 2, and crude 3).
Sunco can purchase up to 5,000 barrels of each type of crude daily. The selling price per barrel of gasoline and the purchase price per barrel of crude oil are given in Table 4. 1 below. | |Selling Price per Barrel | |Purchase Price per Barrel | |Gas 1 |$70 |Crude 1 |$45 | |Gas 2 |$60 |Crude 2 |$35 | |Gas 3 |$50 |Crude 3 |$25 |
Table 4. 1 The three types of gasoline differ in their octane rating and their sulfur content. The crude oil blended to form gas 1 must have an average octane rating of at least 10 and contain at most 1% sulfur. The crude oil blended to form gas 2 must have an average octane rating of at least 8 and contain at most 2% sulfur. The crude oil blended to form gas 3 must have an average octane rating of at least 6 and contain at most 1% sulfur. The octane rating and sulfur content of the three types of crude oil are given in Table 4. 2 below. | |Octane Rating |Sulfur Content |Crude 1 |12 |0. 5% | |Crude 2 |6 |2. 0% | |Crude 3 |8 |3. 0% | Table 4. 2 It costs $4 to transform 1 barrel of oil into 1 barrel of gasoline, and Sunco’s refinery can produce up to 14,000 barrels of gasoline daily. Sunco’s customers require the following amounts of each gasoline: gas 1, 3000 barrels per day; gas 2, 2000 barrels per day; gas 3, 1000 barrels per day. The company considers it an obligation to meet these demands.
Sunco also has the option of advertising to stimulate demand for its products. Each dollar spent daily in advertising a particular type of gas increases the daily demand for that type of gas by 10 barrels. For example, if Sunco decides to spend $20 daily in advertising gas 2, the daily demand for gas 2 will increase by 200 barrels. Below are the answer report and the sensitivity report from a Solver optimal solution to this problem. [pic] [pic] [pic] [pic] [pic] a) What is the optimal amount of profit for Sunco? Cell E8 of the Answer Report indicates that the optimal profit is $287,750. ) How much of Crude Oil 2 should Sunco purchase?
Cell D40 of the Answer Report indicates that Sunco should purchase 5,000 barrels of Crude Oil 2. c) How much of the Crude Oil 2 will be used to make Gas 3? Cell D18 of the Answer Report indicates that none of the Crude Oil 2 will be used to make Gas 3. d) The marketing director insists that Sunco needs to spend $500 advertising Gas 1. What will this do to the net profit? According to the Sensitivity Report.
500 barrels of Crude Oil 2 are available on the spot market. How much should Sunco offer per barrel for this commodity? According to the Sensitivity Report (cell E36), Sunco should offer no more than $20. 90 per barrel. f) Assuming that the seller agrees to Sunco’s price, how many barrels of Crude Oil 2 should they buy? Cell G36 of the Sensitivity Report indicates that Sunco would be interested in as much as 400 barrels at a price of no more than $20. 90. g) 500 barrels of Crude Oil 3 are available on the spot market. How much should Sunco offer per barrel for this commodity?