Transcript
30 ? Test Item File
Chapter Two ? 29
Excel Problems
1. Consider the following linear programming problem.
Maximize 6X1 + 4X2
subject to:
X1 + 2X2 £ 16
3X1 + 2X2 £ 24
X1 ? 2
X1, X2 ? 0
Use Solver to find the optimal values of X1 and X2.
2. Consider the following linear programming problem.
Maximize 5X1 + 3X2
Subject to:
X1 + X2 ? 20
X1 ? 5
X2 ? 10
X1, X2 ? 0
Use Solver to find the optimal values of X1 and X2.
3. Consider the following linear programming problem.
Minimize 3X1 + 2X2
Subject to:
X1 + X2 ? 10
X1 + X2 ? 20
X2 ? 10
X1 ? 18
X1, X2 ? 0
Use Solver to find the optimal values of X1 and X2.
4. Consider the following linear programming problem.
Minimize 6X1 + 3X2
Subject to:
2X1 + 4X2 ? 16
4X1 + 3X2 ? 24
X1, X2 ? 0
Use Solver to find the optimal values of X1 and X2.
5. A computer retail store sells two types of flat screen monitors: 17 inches and 19 inches, with a profit contribution of $300 and $250, respectively. The monitors are ordered each week from an outside supplier. As an added feature, the retail store installs on each monitor a privacy filter that narrows the viewing angle so that only persons sitting directly in front of the monitor are able to see on-screen data. Each 19” monitor consumes about 30 minutes of installation time, while each 17” monitor requires about 10 minutes of installation time. The retail store has approximately 40 hours of labor time available each week. The total combined demand for both monitors is at least 40 monitors each week. How many units of each monitor should the retail store order each week to maximize its weekly profits and meet its weekly demand?
6. Creatine and protein are common supplements in most bodybuilding products. Bodyworks, a nutrition health store, makes a powder supplement that combines creatine and protein from two ingredients (X1 and X2). Ingredient X1 provides 20 grams of protein and 5 grams of creatine per pound. Ingredient X2 provides 15 grams of protein and 3 grams of creatine per pound. Ingredients X1 and X2 cost Bodyworks $5 and $7 per pound, respectively. Bodyworks wants its supplement to contain at least 30 grams of protein and 10 grams of creatine per pound, and be produced at the least cost.
7. A furniture store produces beds and desks for college students. The production process requires assembly and painting. Each bed requires 6 hours of assembly and 4 hours of painting. Each desk requires 4 hours of assembly and 8 hours of painting. There are 40 hours of assembly time and 45 hours of painting time available each week. Each bed generates $35 of profit and each desk generates $45 of profit. As a result of a labor strike, the furniture store is limited to producing at most 8 beds each week. Determine how many beds and desks should be produced each week to maximize weekly profits.
8. An ice cream shop sells single scoop ice cream cones that come in three flavors: chocolate only, vanilla only, and chocolate-vanilla twist. The cones are prepackaged and sold to a supermarket daily. The ingredients used along with the minimum demand of each flavor are shown as follows:
Ice Cream Flavor
Chocolate Vanilla Chocolate-Vanilla
Ingredient:
Chocolate 4 oz. 0 oz. 3 oz.
Vanilla 0 oz. 4 oz. 2 oz.
Min daily demand: 20 scoops 15 scoops 10 scoops
Each day, 40 pounds of chocolate, and 38 pounds of vanilla are supplied to the ice cream shop from an outside vendor. The chocolate, vanilla, and chocolate-vanilla twist each yield a profit of $2.00, $2.50, and $3.00 per cone, respectively. How many chocolate, vanilla, and chocolate-vanilla twist cones must prepackage daily to maximize daily profits?
9. A company manufactures four products A, B, C, and D that must go through assembly, polishing, and packing before being shipped to a wholesaler. For each product, the time required for these operations is shown below (in minutes) as is the profit per unit sold.
Product Assembly Polish Pack Profit ($)
A 2 3 2 1.50
B 4 2 3 2.50
C 3 3 2 3.00
D 7 4 5 4.50
The company estimates that each year they have 1667 hours of assembly time, 833 hours of polishing time and 1000 hours of packing time available. How many of each product should the company make per year to maximize its yearly profit?
10. Suppose that a farmer has 5 acres of land that can be planted with either wheat, corn or a combination of the two. To ensure a healthy crop, a fertilizer and an insecticide must be applied at the beginning of the season before harvesting. The farmer currently has 100 pounds of the fertilizer and 150 pounds of the insecticide at the beginning of the season. Each acre of wheat planted requires 10 pounds of the fertilizer and 12 pounds of the insecticide. Each acre of corn planted requires 13 pounds of the fertilizer and 11 pounds of the insecticide. Each acre of wheat harvested yields a profit of $600, while each acre of corn harvested yields $750 in profit. What is the optimal allocation for the crop that maximizes the farmer’s profit?
11. A carpenter makes tables and chairs. Each table can be sold for a profit of $50 and each chair for a profit of $30. The carpenter works a maximum of 40 hours per week and spends 5 hours to make a table and 2 hours to make a chair. Customer demand requires that he makes at least twice as many chairs as tables. The carpenter stores the finished products in his garage, and there is room for a maximum of 6 furniture pieces each week. Determine the carpenter’s optimal production mix?
12. A bank is attempting to determine where its assets should be allocated in order to maximize its annual return. At present, $750,000 is available for investment in three types of mutual funds: A, B, and C. The annual rate of return on each type of fund is as follows: fund A, 15%; fund B, 12%; fund C; 13%. The bank’s manager has placed the following restrictions on the bank’s portfolio:
No more than 20% of the total amount invested may be in fund A
The amount invested in fund B cannot exceed the amount invested in fund C
Determine the optimal allocation that maximizes the bank’s annual return.
13. A warehouse stocks five different products, A, B, C, and D. The warehouse has a total of 100,000 square feet of floor space available to accommodate all the products that it inventories. The monthly profit per square foot for each product is shown as follows:
Product Profit per square foot
A $4.50
B $3.00
C $2.75
D $3.75
Each product must have at least 10,000 ft2, and no single product can have more than 25% of the total warehouse space. The warehouse manager wants to know the floor space that should be allocated to each product to maximize profit.
14. A company that is introducing a new product would like to generate maximum market exposure. The marketing department currently has $100,000 of advertising budget for the year and is considering placing ads in three media: radio, television, and newspapers. The cost per ad and the exposure rating is shown as follows:
Cost/ad Exposure/ad
Radio $10,000 30,000 individuals
Television $25,000 50,000 individuals
Newspaper $5000 20,000 individuals
The marketing department would like to place twice as many radio ads as television ads. They also would like to place at least 4 ads in each advertising media. What is the optimal allocation to each advertising medium to maximize audience exposure?
15. A meat packing store produces a dog food mixture that is sold to pet retail outlets in bags of 10 pounds each. The food mixture contains the ingredients turkey and beef. The cost per pound of each of these ingredients is as follows:
Ingredient Cost/pound
Turkey $2.00
Beef $5.50
Each bag must contain at least 5 pounds of turkey. Moreover, the ratio of turkey to beef must be at least 2 to 1. What is the optimal mixture of the ingredients that will minimize total cost?
