Chapter 3—Modeling and Solving LP Problems in a Spreadsheet MULTIPLE CHOICE 1. An a. b. c. d.
LP LP problem with a feasible region will will have have an optimal optimal solution at some interior point. an optimal solution at some some extreme point. an optimal optimal solution only at the origin. an optimal optimal solution solution at two interior interior points.
ANS: B
PTS: 1
2. Microsoft Excel, Quattro Pro and Lotus 1-2-3 contain built-in optimizers called a. what-if engines. b. calculators. c. solvers. d. risk analyzers. ANS: C
PTS: 1
3. Which type of spreadsheet spreadsheet cell represents represents the objective objective function in an LP LP model? a. Objective cell b. Changing variable cell c. Constraint cell d. Constant cell ANS: A
PTS: 1
4. Which type of spreadsheet spreadsheet cell represents represents the decision decision variables in an LP model? a. Target or set cell b. Variable cell c. Constraint cell d. Constant cell ANS: B
PTS: 1
5. Which type of of spreadsheet cell represents represents the left hand hand sides (LHS) formulas in an LP LP model? a. Target or set cell b. Changing variable cell c. Constraint cell d. Constant cell ANS: C
PTS: 1
6. The constraints X 1 ≥ 0 and X 2 ≥ 0 are referred to as a. positivity constraints. b. optimality conditions. conditions. c. left hand sides. d. nonnegativity conditions. ANS: D
PTS: 1
7. In the Risk Solver Platform (RSP) dialog dialog box simple upper upper and lower bounds bounds for decision decision variables are specified by
a. b. c. d.
referring directly to the decision variable variable cells in the Constraints-Bound Constraints-Bound area. requiring the addition of of the bounds above and below below the variable cells. resolving the problem with the bounds added. incorporating the bounds bounds in the objective objective function.
ANS: A
PTS: 1
8. The built-in built-in Solver in Excel is found under under which tab tab on the ribbon? a. Tools b. Insert c. Data d. Window ANS: C
PTS: 1
9. Which tab in the Risk Solver Platform Platform (RSP) task pane is used to define define an optimization optimization problem? a. Guess b. Model c. Change d. Delete ANS: B
PTS: 1
10. Spreadsheet modeling is an acquired skill because a. there is generally only one correct way to build a model. b. the spreadsheet is free-form providing providing many modeling options. options. c. using Risk Solver Platform (RSP) requires requires lots lots of experience. experience. d. spreadsheets are not very easy to use. ANS: B
PTS: 1
11. Models which are setup in an intuitively appealing, logical layout layout tend to be the most most a. Reliable b. Modifiable c. Auditable d. Organized ANS: C
PTS: 1
12. The English-reading eye scans a. Right to left b. Bottom to top c. Left to Bottom d. Left to right ANS: D
PTS: 1
13. Numeric constants should be a. embedded in formulas. b. placed in individual cells cells c. placed in separate workbooks. workbooks. d. entered manually manually every time a model is solved. solved. ANS: B
PTS: 1
14. The "Analyze Without Solving" Solving" tool in Risk Solver Solver Platform (RSP) is useful for
a. b. c. d.
referring directly to the decision variable variable cells in the Constraints-Bound Constraints-Bound area. requiring the addition of of the bounds above and below below the variable cells. resolving the problem with the bounds added. incorporating the bounds bounds in the objective objective function.
ANS: A
PTS: 1
8. The built-in built-in Solver in Excel is found under under which tab tab on the ribbon? a. Tools b. Insert c. Data d. Window ANS: C
PTS: 1
9. Which tab in the Risk Solver Platform Platform (RSP) task pane is used to define define an optimization optimization problem? a. Guess b. Model c. Change d. Delete ANS: B
PTS: 1
10. Spreadsheet modeling is an acquired skill because a. there is generally only one correct way to build a model. b. the spreadsheet is free-form providing providing many modeling options. options. c. using Risk Solver Platform (RSP) requires requires lots lots of experience. experience. d. spreadsheets are not very easy to use. ANS: B
PTS: 1
11. Models which are setup in an intuitively appealing, logical layout layout tend to be the most most a. Reliable b. Modifiable c. Auditable d. Organized ANS: C
PTS: 1
12. The English-reading eye scans a. Right to left b. Bottom to top c. Left to Bottom d. Left to right ANS: D
PTS: 1
13. Numeric constants should be a. embedded in formulas. b. placed in individual cells cells c. placed in separate workbooks. workbooks. d. entered manually manually every time a model is solved. solved. ANS: B
PTS: 1
14. The "Analyze Without Solving" Solving" tool in Risk Solver Solver Platform (RSP) is useful for
a. b. c. d.
verifying the equations in a spreadsheet model. toggling between absolute absolute and relative cell referencing. referencing. executing the Excel spreadsheet layout Wizard. naming cells and cell ranges for easier easier modifiability. modifiability.
ANS: A
PTS: 1
15. The "Objective Value Value of" option in the Risk Solver Platform (RSP) task pane may be used to a. find a solution at a maximum value. b. find a solution at a minimum minimum value. c. find a solution for a specific objective function value. d. returns the best feasible solution. ANS: C
PTS: 1
16. The "Objective Sense" Sense" option in the Risk Solver Platform Platform (RSP) task pane may be used to a. return a heuristic heuristic solution to the problem. b. tell the Solver what value it should should seek for your optimization optimization objective. c. determine the value of the objective objective based on specified specified decision decision variable cells. d. always works correctly. ANS: B
PTS: 1
17. What action is required to make Risk Solver Platform Platform (RSP) solve solve a specified problem? a. Type go in cell A1. b. Click the "Optimize" button on on the RSP Ribbon, or the green arrow "Solve" in the the Task Pane. c. Click the Close button button in the RSP Parameters dialog dialog box. box. d. Click the Guess button in the the RSP Parameters dialog dialog box. box. ANS: B
PTS: 1
18. What does does the Excel =SUMPRODUCT(A1:A5,C6;C10) =SUMPRODUCT(A1:A5,C6;C10) command do? a. Sums each range and multiplies the sums. b. Sum each pair of cells and multiples each sum. sum. c. Multiplies the contents contents of cells containing containing the =SUM() =SUM() command. d. Multiplies each pair pair of cells and sums each product. product. ANS: D
PTS: 1
19. What command is used to add the contents of cells A1, A2 and A3? a. =A1+A2+A3 b. =ADD(A1:A3) c. =TOTAL(A1:A3) d. =PRODUCT(A1:A3) ANS: A
PTS: 1
20. Which command is equivalent equivalent to =SUMPRODUCT(A1:A3,B1:B3 =SUMPRODUCT(A1:A3,B1:B3)? )? a. =SUM(PRODUCT((A1:A3,B1:B3)) b. =PRODUCT(SUM((A1:A3,B1:B3) =PRODUCT(SUM((A1:A3,B1:B3))) c. =PRODUCT(A1+B1,A2+B2,A3+B3)) d. =A1*B1+A2*B2+A3*B3 ANS: D
PTS: 1
21. Problems which have only integer solutions are called a. discrete programming problems b. integer programming problems problems c. discrete programming problems d. infeasible programming problems ANS: B
PTS: 1
22. What is the significance significance of an absolute cell reference in Excel? a. The cell reference will not change if the the formula containing containing the reference is copied copied to another location b. The cell will always contain contain the absolute value of any number entered into it c. The cell reference changes if the formula containing containing the the reference is copied to another location d. It is the only formula formula used to to refer to a cell on another another spreadsheet ANS: A
PTS: 1
23. How many decision decision variables are there there in a transportation transportation problem which which has 5 supply supply points and and 4 demand points? a. 4 b. 5 c. 9 d. 20 ANS: D
PTS: 1
24. How many constraints constraints are there in a transportation problem which has 5 supply points points and 4 demand points? (ignore the non-negativity non-negativity constraints) constraints) a. 4 b. 5 c. 9 d. 20 ANS: C
PTS: 1
25. A heuristic solution is a. used by Risk Solver Solver Platform (RSP) when the the Guess button is used. b. guaranteed to produce an optimal optimal solution. c. used by Risk Solver Solver Platform (RSP) if Standard Standard GRG Nonlinear method method is selected. d. a rule-of-thumb for making decisions. ANS: D
PTS: 1
26. Scaling problems a. can cause Risk Risk Solver Solver Platform (RSP) to consider consider a linear problem as nonlinear. nonlinear. b. can cause problems in accuracy of solutions solutions returned. c. are caused by small numbers and large numbers used in the same problem. d. all of these. ANS: D
PTS: 1
27. Which of the following following describes describes Data Envelopment Analysis (DEA). a. DEA finds the most effective company among some set of companies. b. DEA determines if a company is converting converting inputs to outputs outputs as effectively as possible. possible. c. DEA determines how effective effective a company converts inputs to outputs compared to other
companies. d. DEA compares how effective a company converts inputs to outputs compared to a benchmark composite of all companies. ANS: C
PTS: 1
28. Data Envelopment Analysis (DEA) is an LP-based methodology in which weighted sums of inputs and outputs are calculated and a. the constraints capture the maximum effectiveness of each unit. b. the objective is to maximize every units output. c. the constraints ensure the sum of the weighted outputs is one. d. the objective for each unit is to maximize the weighted sum of its outputs. ANS: D
PTS: 1
29. Using Data Envelopment Analysis (DEA) for an inefficient unit, a more efficient composite unit can be found by a. Solving its DEA problem and retrieving the weights from the answer report. b. Solving its DEA problem and examining those units whose final value is non-zero. c. Solving its DEA problem and using the resulting shadow prices as composite weights. d. Solving its DEA problem and using the positive resulting shadow prices as composite weights. ANS: C
PTS: 1
Exhibit 3.1 The following questions are based on this problem and accompanying Excel windows. Jones Furniture Company produces beds and desks for college students. The production process requires carpentry and varnishing. Each bed requires 6 hours of carpentry and 4 hour of varnishing. Each desk requires 4 hours of carpentry and 8 hours of varnishing. There are 36 hours of carpentry time and 40 hours of varnishing time available. Beds generate $30 of profit and desks generate $40 of profit. Demand for desks is limited, so at most 8 will be produced. Let
X 1 = Number of Beds to produce X 2 = Number of Desks to produce
The LP model for the problem is MAX: Subject to:
30 X 1 + 40 X 2 6 X 1 + 4 X 2 ≤ 36 (carpentry) 4 X 1 + 8 X 2 ≤ 40 (varnishing) X 2 ≤ 8 (demand for desks) X 1 , X 2 ≥ 0 A
1 2 3 4 5 6 7 8
B
C
D
E
Jones Furniture Beds Number to make: Unit profit: Constraints: Carpentry
Desks Total Profit:
30
40 Used
6
4
Available 36
9 10
Varnishing Desk demand
4
8 1
40 8
30. Refer to Exhibit 3.1. What formula should be entered in cell E5 in the accompanying Excel spreadsheet to compute total profit? a. =B4*B5+C4*C5 b. =SUMPRODUCT(B8:C8,$B$4:$C$4) c. =SUM(B5:C5) d. =SUM(E8:E10) ANS: A
PTS: 1
31. Refer to Exhibit 3.1. What formula should be entered in cell D8 in the accompanying Excel spreadsheet to compute the amount of carpentry used? a. =B4*B5+C4*C5 b. =SUMPRODUCT(B8:C8,$B$4:$C$4) c. =SUM(B5:C5) d. =SUM(E8:E10) ANS: B
PTS: 1
32. Refer to Exhibit 3.1. Which cells should be changing cells in this problem? a. B4:C4 b. E5 c. D8:D10 d. E8:E10 ANS: A
PTS: 1
33. Refer to Exhibit 3.1. Which cells should be the constraint cells in this problem? a. B4:C4 b. E5 c. D8:D10 d. E8:E10 ANS: C
PTS: 1
34. Refer to Exhibit 3.1. Which of the following statements represent the carpentry, varnishing and limited demand for desks constraints? a. B4:C4 ≤ B5:C5 b. E5 ≤ 0 c. D8:D10 ≤ E8:E10 d. E8:E10 ≤ D8:D10 ANS: C
PTS: 1
Exhibit 3.2 The following questions are based on this problem and accompanying Excel windows. The Byte computer company produces two models of computers, Plain and Fancy. It wants to plan how many computers to produce next month to maximize profits. Producing these computers requires wiring, assembly and inspection time. Each computer produces a c ertain level of profits but faces only a limited demand. There are also a limited number of wiring, assembly and inspection hours available in each month. The data for this problem is summarized in the following table.
Computer Model
Profit per Model ($)
Plain Fancy
30 40
Maximum demand for product
Wiring Hours Required
Assembly Hours Required
Inspection Hours Required
80 90 Hours Available
.4 .5 50
.5 .4 50
.2 .3 22
Let
X 1 = Number of Plain computers to produce X 2 = Number of Fancy computers to produce
MAX: Subject to:
30 X 1 + 40 X 2 .4 X 1 + .5 X 2 ≤ 50 (wiring hours) .5 X 1 + .4 X 2 ≤ 50 (assembly hours) .2 X 1 + .2 X 2 ≤ 22 (inspection hours) X 1 ≤ 80 (Plain computers demand) X 2 ≤ 90 (Fancy computers demand) X 1 , X 2 ≥ 0 A
1 2 3 4 5 6 7 8 9 10 11 12
B C Byte Computer Company
Number to make: Unit profit:
Plain
Fancy
30
40
D
E
Total Profit:
Constraints: Wiring Assembly Inspection Plain Demand Fancy Demand
Used 0.4 0.5 0.2 1
0.5 0.4 0.3 1
Available 50 50 22 80 90
35. Refer to Exhibit 3.2. What formula should be entered in cell E5 in the accompanying Excel spreadsheet to compute total profit? a. =B4*C4+B5*C5 b. =SUMPRODUCT(B4:C4,B5:C5) c. =SUM(B5:C5) d. =SUM(E8:E10) ANS: B
PTS: 1
36. Refer to Exhibit 3.2. What formula should be entered in cell D8 in the accompanying Excel spreadsheet to compute the amount of wiring used? a. =B4*B5+C4*C5 b. =SUMPRODUCT(B8:C8,$B$4:$C$4) c. =SUM(B5:C5) d. =SUM(E8:E10) ANS: B
PTS: 1
37. Refer to Exhibit 3.2. Which cells should be changing cells in this problem? a. B4:C4 b. E5
c. D8:D10 d. E8:E10 ANS: A
PTS: 1
38. Refer to Exhibit 3.2. Which cells should be the constraint cells in this problem? a. B4:C4 b. E5 c. D8:D12 d. E8:E12 ANS: C
PTS: 1
39. Refer to Exhibit 3.2. Which of the following statements will represent the constraint for just assembly hours? a. B4:C4 ≤ B5:C5 b. D9 ≤ E9 c. D8:D10 ≤ E8:E10 d. E8:E10 ≤ D8:D10 ANS: B
PTS: 1
Exhibit 3.3 The following questions are based on this problem and accompanying Excel windows. Jack's distillery blends scotches for local bars and saloons. One of his customers has requested a special blend of scotch targeted as a bar scotch. The customer wants the blend to involve two scotch products, call them A and B. Product A is a higher quality scotch while product B is a cheaper brand. The customer wants to make the claim the blend is closer to high quality than the alternative. The customer wants 50 1500 ml bottles of the blend. Each bottle must contain at least 48% of Product A and at least 500 ml of B. The customer also specified that the blend have an alcohol content of at least 85%. Product A contains 95% alcohol while product B contains 78%. The blend is sold for $12.50 per bottle. Product A costs $7 per liter and product B costs $3 per liter. The company wants to determine the blend that will meet the customer's requirements and maximize profit. Let
X 1 = Number of liters of product A in total blend delivered X 2 = Number of liters of product B in total blend delivered
MIN: Subject to:
7 X 1 + 3 X 2 X 1 + X 2 = 1.5 * 50 (Total liters of mix) X 1 ≥ 0.48 * 1.5 * 50 (X 1 minimum) X 2 ≥ 0.5 * 50 (X 2 minimum) .0.95 X 1 + 0.78 X 2 ≥ 0.85 * 1.5 * 50 (85% alcohol minimum) X 1 , X 2 ≥ 0 A
1 2 3 4 5 6 7
B
C
D
E
Jacks' Distillery A Liters to use Unit cost: Constraints:
B Total Cost:
10.5
4.5 Supplied
Requirement
8 9 10 11
Total Liters A required B required 85% alcohol
1 1 0.95
1 1 0.78
75 36 25 63.75
40. Refer to Exhibit 3.3. What formula should be entered in cell E5 in the accompanying Excel spreadsheet to compute total cost? a. =B4*C4+B5*C5 b. =SUMPRODUCT(B4:C4,B5:C5) c. =SUM(B5:C5) d. =SUM(E8:E10) ANS: B
PTS: 1
41. Refer to Exhibit 3.3. What formula should be entered in cell D11 in the accompanying Excel spreadsheet to compute the total liters of alcohol supplied? a. =B4*B5+C4*C5 b. =SUMPRODUCT(B11:C11,$B$4:$C$4) c. =SUM(B5:C5) d. =SUM(E8:E10) ANS: B
PTS: 1
42. Refer to Exhibit 3.3. Which cells should be changing cells in this problem? a. B4:C4 b. E5 c. D8:D10 d. E8:E10 ANS: A
PTS: 1
43. Refer to Exhibit 3.3. Which cells should be the constraint cells in this problem? a. B4:C4 b. E5 c. D8:D11 d. E8:E10 ANS: C
PTS: 1
44. Refer to Exhibit 3.3. Which of the following statements could represent a constraint in this problem? a. B4:C4 ≤ B5:C5 b. E5 ≤ 0 c. D8 = E8 d. E8:E11 ≤ D8:D11 ANS: C
PTS: 1
Exhibit 3.4 The following questions are based on this problem and accompanying Excel windows.
A financial planner wants to design a portfolio of investments for a client. The client has $300,000 to invest and the planner has identified four investment options for the money. The following requirements have been placed on the planner. No more than 25% of the money in any one investment, at least one third should be invested in long-term bonds which mature in seven or more years, and no more than 25% of the total money should be invested in C or D since they are riskier investments. The planner has developed the following LP model based on the data in this table and the requirements of the client. The objective is to maximize the total return of the portfolio. Investment A B C D
Return 6.45% 7.10% 8.20% 9.00%
Years to Maturity 9 8 5 8
Let
X 1 = Dollars invested in A X 2 = Dollars invested in B X 3 = Dollars invested in C X 4 = Dollars invested in D
MAX: Subject to:
.0645 X 1 + .071 X 2 + .082 X 3 + .09 X 4 X 1 + X 2 + X 3 + X 4 ≤ 300000 X 1 ≤ 75000 X 2 ≤ 75000 X 3 ≤ 75000 X 4 ≤ 75000 X 1 + X 2 + X 4 ≥ 100000 X 3 + X 4 ≤ 75000 X 1 , X 2 , X 3 , X 4 ≥ 0 A
1 2 3 4 5 6 7 8 < < < < < < < < <
Bond A B C D Total Invested: Total Available:
1 2 3 4 5 6 7 8
E Years to Maturity 9 8 5 8 Total: Required:
B Amount Invested $0 $0 $0 $0 $0 $300,000 F 7+ years? (1-yes, 0-no) 1 1 0 1 $0 $100,000
C Maximum 25.0% $75,000 $75,000 $75,000 $75,000 Total:
G Rating 1-Excellent 2-Very Good 4-Fair 3-Good Total: Allowed:
Rating 1-Excellent 2-Very Good 4-Fair 3-Good
D Return 6.45% 7.10% 8.20% 9.00% $0
> > > > > > > > >
H Good or worse? (1-yes, 0-no) 0 0 1 1 $0 $75,000
45. Refer to Exhibit 3.4. What formula should be entered in cell B7 in the accompanying Excel spreadsheet to compute total dollars invested? a. =ADD(B3:B6) b. =SUM(B3:B6)
c. =TOTAL(B3:B6) d. =TALLY(B3:B6) ANS: B
PTS: 1
46. Refer to Exhibit 3.4. What formula should be entered in cell D7 in the accompanying Excel spreadsheet to compute the total return? a. =B7*SUM(D3:D6) b. =SUMPRODUCT(B3:B6,D3:D6) c. =SUM(B3:B6) d. =SUMPRODUCT(B3:E3,B6:E6) ANS: B
PTS: 1
47. Refer to Exhibit 3.4. Which cells are changing cells in the accompanying Excel spreadsheet? a. B3:B6 b. B7:I7 c. C7 d. E7 ANS: A
PTS: 1
Exhibit 3.5 The following questions are based on this problem and accompanying Excel windows. A company is planning production for the next 4 quarters. They want to minimize the cost of production. The production cost is stable but demand and production capacity vary from quarter to quarter. The maximum amount of inventory which can be held is 12,000 units and management wants to keep at least 3,000 units on hand. Quarterly inventory holding cost is 3% of the cost of production. The company estimates the number of units carried in inventory each month by averaging the beginning and ending inventory for each month. There are currently 5,000 units in inventory. The company wants to produce at no less than one half of its maximum capacity in any quarter. Quarter Unit Production Cost Units Demanded Maximum Production
1 $ 300 2,000 8,000
2 $ 300 9,000 7,000
3 $
300 12,000 8,000
Let
P i = number of units produced in quarter i, i = 1, ..., 4 B i = beginning inventory for quarter i
MIN:
300 P 1 + 300 P 2 + 300 P 3 + 300 P 4 + 9(B 1 + B 2 )/2 + 9(B 2 + B 3 )/2 + 9(B 3 + B 4 )/2 + 9(B 4 + B 5 )/2 4000 ≤ P 1 ≤ 8000 3500 ≤ P 2 ≤ 7000 4000 ≤ P 3 ≤ 8000 4500 ≤ P 4 ≤ 9000 3000 ≤ B 1 + P 1 − 2000 ≤ 12000 3000 ≤ B 2 + P 2 − 9000 ≤ 12000 3000 ≤ B 3 + P 3 − 12000 ≤ 12000 3000 ≤ B 4 + P 4 − 11000 ≤ 12000 B 2 = B 1 + P 1 − 2000
Subject to:
4 $
300 11,000 9,000
B 3 = B 2 + P 2 − 9000 B 4 = B 3 + P 3 − 12000 B 5 = B 4 + P 4 − 11000 P i , B i ≥ 0 A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
B
E
F
Beginning Inventory Units Produced Units Demanded Ending Inventory
1 5,000 8,000 2,000 11,000
D Quarter 2 11,000 7,000 9,000 9,000
3 9,000 8,000 12,000 5,000
4 5,000 9,000 11,000 3,000
Minimum Production Maximum Production
4,000 8,000
3,500 7,000
4,000 8,000
4,500 9,000
Minimum Inventory Maximum Inventory
3,000 12,000
3,000 12,000
3,000 12,000
3,000 12,000
Unit Production Cost Unit Carrying Cost
$300 $9.00
$300 $9.00
$300 $9.00
$300 $9.00
$2,400,000 $72,000
$2,100,000 $90,000
$2,400,000 $63,000
$2,700,000 $36,000
Quarterly Production Cost Quarterly Carrying Cost
3.0%
C
Total Cost
$9,861,000
48. Refer to Exhibit 3.5. What formula should be entered in cell C6 in the accompanying Excel spreadsheet to compute ending inventory? a. =C3-C4+C5 b. =C3+C4-C5 c. =C3-(C4-C5) d. =C5-C4-C3 ANS: B
PTS: 1
49. Refer to Exhibit 3.5. What formula should be entered in cell C18 in the accompanying Excel spreadsheet to compute the quarterly carrying costs? a. =C15*C3+C6 b. =C15*(C3+C6) c. =C15*C3/2 d. =C15*(C3+C6)/2 ANS: D
PTS: 1
50. Refer to Exhibit 3.5. Which cells are changing cells in the accompanying Excel spreadsheet? a. C4:F4 b. C9:F9 c. F20 d. C12:F12 ANS: A
PTS: 1
51. Refer to Exhibit 3.5. What formula could be entered in cell F20 in the accompanying Excel spreadsheet to compute the Total Cost for all four quarters?
a. b. c. d.
SUMPRODUCT($C$4:$F$4,C17:F17) SUM(C17:F17) + SUM(C18:F18) PRODUCT(SUM(C14:F15,C17:F18) SUMPRODUCT(C4:F4,C14:F14) + SUMPRODUCT(C6:F6,C15:F15)
ANS: B
PTS: 1
PROBLEM 52. You have been given the following linear programming model and Excel spreadsheet to solve this problem. What numbers should be entered into cells B5:C5 and B8:C10 to implement this model? MAX: Subject to:
2 X 1 + 7 X 2 5 X 1 + 9 X 2 ≤ 90 9 X 1 + 8 X 2 ≤ 144 X 2 ≤ 8 X 1 , X 2 ≥ 0 A
1 2 3 4 5 6 7 8 9 10
B
C
X1
X2
D
Number to make: Unit profit:
E
OBJ. FN. VALUE
Constraints: 1 2 3
Used
Available 90 144 8
D
E
ANS: A 1 2 3 4 5 6 7 8 9 10
B
C
X1
X2
Number to make: Unit profit:
OBJ. FN. VALUE 2
7
Constraints: 1 2 3
5 9 0
9 8 1
Used
Available 90 144 8
PTS: 1 53. You have been given the following linear programming model and Excel spreadsheet to solve this problem. What formulas should be entered into cells E5 and D8:D10 to implement this model? MAX: Subject to:
2 X 1 + 7 X 2 5 X 1 + 9 X 2 ≤ 90 9 X 1 + 8 X 2 ≤ 144 X 2 ≤ 8
X 1 , X 2 ≥ 0 A 1 2 3 4 5 6 7 8 9 10
Number to make: Unit profit: Constraints: 1 2 3
ANS: Cell E5 D8
B
C
X1
X2
D
E
OBJ. FN. VALUE 2
7 Used
5 9 0
9 8 1
Formula =SUMPRODUCT(B4:C4,B5:C5) =SUMPRODUCT($B$4:$C$4,B8:C8)
Available 90 144 8
Copied to D9:D10
PTS: 1 54. You have been given the following linear programming model and Excel spreadsheet to solve this problem. What cell references would you enter in the Risk Solver Platform (RSP) task pane for the following? Objective Cell: Variables Cells: Constraints Cells: MAX: Subject to:
8 X 1 + 5 X 2 3 X 1 + 5 X 2 = 54 11 X 1 + 10 X 2 ≤ 144 X 1 ≥12 X 1 , X 2 ≥ 0 A
1 2 3 4 5 6 7 8 9 10
Number to make: Unit profit: Constraints: 1 2 3
ANS: Objective Cell: $E$5 Variables Cells: $B$4:$C$4
B
C
X1
X2
D
E
OBJ. FN. VALUE 8
5
3 11 1
5 10 0
Used
Available 54 144 12
Constraints Cells: $B$4:$C$4 ≥ 0 $D$8 = $E$8 $D$9 ≤ $E$9 $D$10 ≥ $E$10 (or $B$4 ≥ $E$10) PTS: 1 55. You have been given the following linear programming model and Excel spreadsheet to solve this problem. What numbers should be entered into cells B5:C5 and B8:C10 to implement this model? MAX: Subject to:
4 X 1 + 3 X 2 6 X 1 + 7 X 2 ≤ 84 X 1 ≤ 10 X 2 ≤ 8 X 1 , X 2 ≥ 0 A
1 2 3 4 5 6 7 8 9 10
B
C
X1
X2
D
Number to make: Unit profit:
E
OBJ. FN. VALUE
Constraints: 1 2 3
Used
Available 84 10 8
D
E
ANS: A 1 2 3 4 5 6 7 8 9 10
Number to make: Unit profit: Constraints: 1 2 3
B
C
X1
X2
4
3
OBJ. FN. VALUE
Used 6 1 0
7 0 1
Available 84 10 8
PTS: 1 56. You have been given the following linear programming model and Excel spreadsheet to solve this problem. What formulas should be entered into cells E5 and D8:D10 to implement this model? MAX: Subject to:
4 X 1 + 3 X 2 6 X 1 + 7 X 2 ≤ 84 X 1 ≤ 10 X 2 ≤ 8 X 1 , X 2 ≥ 0
A 1 2 3 4 5 6 7 8 9 10
Number to make: Unit profit: Constraints: 1 2 3
ANS: Cell E5 D8
B
C
X1
X2
D
E
OBJ. FN. VALUE 4
3 Used
6 1 0
7 0 1
Formula =SUMPRODUCT(B4:C4,B5:C5) =SUMPRODUCT($B$4:$C$4,B8:C8)
Available 84 10 8
Copied to D9:D10
PTS: 1 57. You have been given the following linear programming model and Excel spreadsheet to solve this problem. What cell references would you enter in the Risk Solver Platform (RSP) task pane for the following? Objective Cell: Variables Cells: Constraints Cells: MAX: Subject to:
12 X 1 + 9 X 2 9 X 1 + 10.5 X 2 ≤ 126 X 1 ≥ 5 X 2 ≥ 6 X 1 , X 2 ≥ 0 A
1 2 3 4 5 6 7 8 9 10
B
C
X1
X2
Number to make: Unit profit:
12
9
Constraints: 1 2 3
9 1 0
10.5 0 1
ANS: Objective Cell: $E$5 Variables Cells: $B$4:$C$4 Constraints Cells:
D
E
OBJ. FN. VALUE
Used
Available 126 5 6
$B$4:$C$4 ≥ 0 $D$8 ≤ $E$8 $D$9:$D$10 ≥ $E$9:$E$10 PTS: 1 58. You have been given the following linear programming model and Excel spreadsheet to solve this problem. What numbers should be entered into cells B5:C5 and B8:C10 to implement this model? MIN: Subject to:
8 X 1 + 3 X 2 X 2 ≥ 8 8 X 1 + 5 X 2 ≥ 80 3 X 1 + 5 X 2 ≥ 60 X 1 , X 2 ≥ 0 A
1 2 3 4 5 6 7 8 9 10
B
C
X1
X2
D
Number to make: Unit profit:
E
OBJ. FN. VALUE
Constraints: 1 2 3
Used
Available 8 80 60
D
E
ANS: A 1 2 3 4 5 6 7 8 9 10
Number to make: Unit profit: Constraints: 1 2 3
B
C
X1
X2 OBJ. FN. VALUE
8
3 Used
0 8 3
1 5 5
Available 8 80 60
PTS: 1 59. You have been given the following linear programming model and Excel spreadsheet to solve this problem. What formulas should be entered into cells E5 and D8:D10 to implement this model? MIN: Subject to:
8 X 1 + 3 X 2 X 2 ≥ 8 8 X 1 + 5 X 2 ≥ 80 3 X 1 + 5 X 2 ≥ 60 X 1 , X 2 ≥ 0 A
B
C
D
E
1 2 3 4 5 6 7 8 9 10
X1 Number to make: Unit profit: Constraints: 1 2 3
ANS: Cell E5 D8
X2 OBJ. FN. VALUE
8
3
8 3
1 5 5
Used
Formula =SUMPRODUCT(B4:C4,B5:C5) =SUMPRODUCT($B$4:$C$4,B8:C8)
Available 8 80 60
Copied to D9:D10
PTS: 1 60. You have been given the following linear programming model and Excel spreadsheet to solve this problem. What cell references would you enter in the Risk Solver Platform (RSP) task pane for the following? Objective Cell: Variables Cells: Constraints Cells: MIN: Subject to:
8 X 1 + 3 X 2 X 2 ≥ 8 8 X 1 + 5 X 2 ≥ 80 3 X 1 + 5 X 2 ≥ 60 X 1 , X 2 ≥ 0 A
1 2 3 4 5 6 7 8 9 10
B
C
X1
X2
D
Number to make: Unit profit: Constraints: 1 2 3
ANS: Objective Cell: $E$5 Variables Cells: $B$4:$C$4 Constraints Cells: $B$4:$C$4 ≥ 0
E
OBJ. FN. VALUE
Used 8 3
1 5 5
Available 8 80 60
$D$8:$D$10 ≥ $E$8:$E$10 PTS: 1 61. A farmer is planning his spring planting. He has 20 acres on which he can plant a combination of Corn, Pumpkins and Beans. He wants to maximize his profit but there is a limited demand for each crop. Each crop also requires fertilizer and irrigation water which are in short supply. There are only 50 acre ft of irrigation available and only 8,000 pounds/acre of fertilizer available. The following table summarizes the data for the problem.
Crop Corn Pumpkin Beans
Profit per Acre ($) 2,100 900 1,050
Yield per Acre (lb) 21,000 10,000 3,500
Maximum Demand (lb) 200,000 180,000 80,000
Irrigation (acre ft) 2 3 1
Fertilizer (pounds/acre) 500 400 300
Formulate the LP for this problem. ANS: Let
MAX: Subject to:
X 1 = aces of corn X 2 = acres of pumpkin X 3 = acres of beans 2100X 1 + 900X 2 + 1050X 3 21X 1 ≤ 200 10X 2 ≤ 180 3.5X 3 ≤ 80 X 1 + X 2 + X 3 ≤ 20 2X 1 + 3X 2 + 1X 3 ≤ 50 5X 1 + 4X 2 + 3X 3 ≤ 80 X 1 , X 2 , X 3 ≥ 0
PTS: 1 62. A farmer is planning his spring planting. He has 20 acres on which he can plant a combination of Corn, Pumpkins and Beans. He wants to maximize his profit but there is a limited demand for each crop. Each crop also requires fertilizer and irrigation water which are in short supply. There are only 50 acre ft of irrigation available and only 8,000 pounds/acre of fertilizer available. The following table summarizes the data for the problem.
Crop Corn Pumpkin Beans
Profit per Acre ($) 2,100 900 1,050
Yield per Acre (lb) 21,000 10,000 3,500
Maximum Demand (lb) 200,000 180,000 80,000
Irrigation (acre ft) 2 3 1
Fertilizer (pounds/acre) 500 400 300
Enter the numbers in the appropriate cells of ranges B12:D12 and E8:F12 in the Excel spreadsheet to solve this problem based on the following formulation. Let
X 1 = aces of corn X 2 = acres of pumpkin X 3 = acres of beans
MAX: Subject to:
2100X 1 + 900X 2 + 1050X 3 21X 1 ≤ 200 10X 2 ≤ 180 3.5X 3 ≤ 80 X 1 + X 2 + X 3 ≤ 20 2X 1 + 3X 2 + 1X 3 ≤ 50 5X 1 + 4X 2 + 3X 3 ≤ 80 X 1 , X 2 , X 3 ≥ 0 A
1 2 3 4 5 6 7 8 9 10 11 12
B Farm
C Planning
D Problem
Corn
Pumpkin
Beans
E
Acres to plant Profit per acre
F
Total Profit:
Constraints: Corn demand Pumpkin demand Bean demand Water Fertilizer
Used
Available
E
F
ANS: A 1 2 3 4 5 6 7 8 9 10 11 12
Acres to plant Profit per acre Constraints: Corn demand Pumpkin demand Bean demand Water Fertilizer
B Farm
C Planning
D Problem
Corn
Pumpkin
Beans
2100
900
1050
Total Profit:
Used 21000 10000 2 500
3 400
3500 1 300
Available 200000 180000 80000 50 8000
PTS: 1 63. A farmer is planning his spring planting. He has 20 acres on which he can plant a combination of Corn, Pumpkins and Beans. He wants to maximize his profit but there is a limited demand for each crop. Each crop also requires fertilizer and irrigation water which are in short supply. The following table summarizes the data for the problem.
Crop Corn Pumpkin Beans
Profit per Acre ($) 2,100 900 1,050
Yield per Acre (lb) 21,000 10,000 3,500
Maximum Demand (lb) 200,000 180,000 80,000
Irrigation (acre ft) 2 3 1
Fertilizer (pounds/acre) 500 400 300
What are the key formulas for this Excel spreadsheet implementation of the following formulation?
Let
X 1 = aces of corn X 2 = acres of pumpkin X 3 = acres of beans
MAX: Subject to:
2100X 1 + 900X 2 + 1050X 3 21X 1 ≤ 200 10X 2 ≤ 180 3.5X 3 ≤ 80 2X 1 + 3X 2 + 1X 3 ≤ 50 5X 1 + 4X 2 + 3X 3 ≤ 80 X 1 , X 2 , X 3 ≥ 0 A
1 2 3 4 5 6 7 8 9 10 11 12
Acres to plant Profit per acre Constraints: Corn demand Pumpkin demand Bean demand Water Fertilizer
ANS: Cell F5 E8
B Farm
C Planning
D Problem
Corn
Pumpkin
Beans
E
F
Total Profit: 2100
900
1050 Used
21000 10000 2 500
3500 1 300
3 400
Formula =SUMPRODUCT(B4:D4,B5:D5) =SUMPRODUCT($B$4:$D$4,B8:D8)
Available 200000 180000 80000 50 8000
Copied to E:E12
PTS: 1 64. A hospital needs to determine how many nurses to hire to cover a 24 hour period. The nurses must work 8 consecutive hours but can start work at the start of 6 different shifts. They are paid different wages depending on when they start their shifts. The number of nurses required per 4-hour time period and their wages are shown in the following table. Time period 12 am − 4 am 4 am − 8 am 8 am − 12 pm 12 pm − 4 pm 4 pm − 8 pm 8 pm − 12 am
Required # of Nurses 20 30 40 50 40 30
Wage ($/hr) 15 16 13 13 14 15
Formulate the LP for this problem. ANS: Let
X i = number of nurses working in time period i; i = 1,6
MIN: Subject to:
1X 1 + 1X 2 + 1X 3 + 1X 4 + 1X 5 + 1X 6 1X 1 + 1X 2 ≥ 30
1X 2 + 1X 3 1X 3 + 1X 4 1X 4 + 1X 5 1X 5 + 1X 6 1X 1 + 1X 6 X i ≥ 0
≥ 40 ≥ 50 ≥ 40 ≥ 30 ≥ 20
PTS: 1 65. A hospital needs to determine how many nurses to hire to cover a 24 hour period. The nurses must work 8 consecutive hours but can start work at the start of 6 different shifts. They are paid different wages depending on when they start their shifts. The number of nurses required per 4-hour time period and their wages are shown in the following table. Time period 12 am − 4 am 4 am − 8 am 8 am − 12 pm 12 pm − 4 pm 4 pm − 8 pm 8 pm − 12 am
Required # of Nurses 20 30 40 50 40 30
Wage ($/hr) 15 16 13 13 14 15
Enter the numbers in the appropriate cells of ranges B6:G11 and B13:G13 in the Excel spreadsheet to solve this problem based on the following formulation. Let
X i = number of nurses working in time period i; i = 1,6
MIN: Subject to:
1X 1 + 1X 2 + 1X 3 + 1X 4 + 1X 5 + 1X 6 1X 1 + 1X 2 ≥ 30 1X 2 + 1X 3 ≥ 40 1X 3 + 1X 4 ≥ 50 1X 4 + 1X 5 ≥ 40 1X 5 + 1X 6 ≥ 30 1X 1 + 1X 6 ≥ 20 X i ≥ 0
A 1 2 3 4 5 6 7 8 9 10 11 12 13
Shift 1 2 3 4 5 6 Available: Required:
ANS:
B
Mid 4am
C
4am 8am
D Nurse
E Hiring
F
G
Hours for each shift 8am Noon 4pm Noon 4pm 8pm
8pm Mid
H
I
Nurses Scheduled
Wages per Nurse $15 $16 $13 $13 $14 $15
Total Wages:
A 1 2 3 4 5 6 7 8 9 10 11 12 13
B
Mid 4am 1 0 0 0 0 1
Shift 1 2 3 4 5 6 Available: Required:
C
4am 8am 1 1 0 0 0 0
D Nurse
E Hiring
F
G
Hours for each shift 8am Noon 4pm Noon 4pm 8pm 0 0 0 1 0 0 1 1 0 0 1 1 0 0 1 0 0 0
8pm Mid 0 0 0 0 1 1
H
I
Nurses Scheduled
Wages per Nurse $15 $16 $13 $13 $14 $15
Total Wages: 20
30
40
50
40
30
PTS: 1 66. A hospital needs to determine how many nurses to hire to cover a 24 hour period. The nurses must work 8 consecutive hours but can start work at the start of 6 different shifts. They are paid different wages depending on when they start their shifts. The number of nurses required per 4-hour time period and their wages are shown in the following table. Time period 12 am − 4 am 4 am − 8 am 8 am − 12 pm 12 pm − 4 pm 4 pm − 8 pm 8 pm − 12 am
Required # of Nurses 20 30 40 50 40 30
Wage ($/hr) 15 16 13 13 14 15
What are the key formulas for this Excel spreadsheet implementation of the following formulation? Let
X i = number of nurses working in time period i; i = 1,6
MIN: Subject to:
1X 1 + 1X 2 + 1X 3 + 1X 4 + 1X 5 + 1X 6 1X 1 + 1X 2 ≥ 30 1X 2 + 1X 3 ≥ 40 1X 3 + 1X 4 ≥ 50 1X 4 + 1X 5 ≥ 40 1X 5 + 1X 6 ≥ 30 1X 1 + 1X 6 ≥ 20 X i ≥ 0
A 1 2 3 4 5 6 7 8
Shift 1 2 3
B
Mid 4am
C
4am 8am
D Nurse
E Hiring
F
G
Hours for each shift 8am Noon 4pm Noon 4pm 8pm
8pm Mid
H
I
Nurses Scheduled
Wages per Nurse $15 $16 $13
9 10 11 12 13
4 5 6 Available: Required:
ANS: Cell I12 B12
$13 $14 $15 Total Wages:
Formula =SUMPRODUCT(H6:H11,I6:I11) =SUMPRODUCT(B6:B11,$H$6:$H$11)
Copied to C12:G12
PTS: 1 67. A hospital needs to determine how many nurses to hire to cover a 24 hour period. The nurses must work 8 consecutive hours but can start work at the start of 6 different shifts. They are paid different wages depending on when they start their shifts. The number of nurses required per 4-hour time period and their wages are shown in the following table. Time period 12 am − 4 am 4 am − 8 am 8 am − 12 pm 12 pm − 4 pm 4 pm − 8 pm 8 pm − 12 am
Required # of Nurses 20 30 40 50 40 30
Wage ($/hr) 15 16 13 13 14 15
What values would you enter in the Risk Solver Platform (RSP) task pane for the following cells for this Excel spreadsheet implementation of the formulation for this problem? Objective Cell: Variables Cells: Constraints Cells: Let
X i = number of nurses working in time period i; i = 1,6
MIN: Subject to:
1X 1 + 1X 2 + 1X 3 + 1X 4 + 1X 5 + 1X 6 1X 1 + 1X 2 ≥ 30 1X 2 + 1X 3 ≥ 40 1X 3 + 1X 4 ≥ 50 1X 4 + 1X 5 ≥ 40 1X 5 + 1X 6 ≥ 30 1X 1 + 1X 6 ≥ 20 X i ≥ 0
A 1 2 3 4 5 6
Shift 1
B
C
Mid 4am 1
4am 8am 1
D Nurse
E Hiring
F
G
Hours for each shift 8am Noon 4pm Noon 4pm 8pm 0 0 0
8pm Mid 0
H
I
Nurses Scheduled
Wages per Nurse $15
7 8 9 10 11 12 13
2 3 4 5 6 Available: Required:
0 0 0 0 1
1 0 0 0 0
1 1 0 0 0
0 1 1 0 0
0 0 1 1 0
0 0 0 1 1
$16 $13 $13 $14 $15 Total Wages:
20
30
40
50
40
30
ANS: Objective Cell: $I$12 Variables Cells: $H$6:$H$11 Constraints Cells: $H$6:$H$11 ≥ 0 $B$12:$G$12 ≥ $B$13:$G$13 PTS: 1 68. A company needs to purchase several new machines to meet its future production needs. It can purchase three different types of machines A, B, and C. Each machine A costs $80,000 and requires 2,000 square feet of floor space. Each machine B costs $50,000 and requires 3,000 square feet of floor space. Each machine C costs $40,000 and requires 5,000 square feet of floor space. The machines can produce 200, 250 and 350 units per day respectively. The plant can only afford $500,000 for all the machines and has at most 20,000 square feet of room for the machines. The company wants to buy as many machines as possible to maximize daily production. Formulate the LP for this problem. ANS: Let MAX: Subject to:
X i = number of machines of type i purchased 200X 1 + 250X 2 + 300X 3 2X 1 + 3X 2 + 5X 3 ≤ 20 80X 1 + 50X 2 + 40X 3 ≤ 500 X 1 , X 2 , X 3 ≥ 0
PTS: 1 69. A company needs to purchase several new machines to meet its future production needs. It can purchase three different types of machines A, B, and C. Each machine A costs $80,000 and requires 2,000 square feet of floor space. Each machine B costs $50,000 and requires 3,000 square feet of floor space. Each machine C costs $40,000 and requires 5,000 square feet of floor space. The machines can produce 200, 250 and 350 units per day respectively. The plant can only afford $500,000 for all the machines and has at most 20,000 square feet of room for the machines. The company wants to buy as many machines as possible to maximize daily production. Enter the numbers in the appropriate cells of range B5:F10 in the Excel spreadsheet to solve this problem based on the following formulation. Let
X i = number of machines of type i purchased
MAX:
200X 1 + 250X 2 + 300X 3
Subject to:
2X 1 + 3X 2 + 5X 3 ≤ 20 80X 1 + 50X 2 + 40X 3 ≤ 500 X 1 , X 2 , X 3 ≥ 0 A
1 2 3 4 5 6 7 8 9 10
B
Machine 1
C Capital Expansion Machine Types Machine 2
D
E
F
Machine 3
Number to buy Machine output
Total Output:
Requirements: Square feet Cost
Used
Available
E
F
ANS: A 1 2 3 4 5 6 7 8 9 10
B
Number to buy Machine output Requirements: Square feet Cost
C Capital Expansion
D
Machine 1
Machine Types Machine 2
Machine 3
200
250
300
Total Output:
Used 2,000 80,000
3,000 50,000
5,000 40,000
Available 20,000 500,000
PTS: 1 70. A company needs to purchase several new machines to meet its future production needs. It can purchase three different types of machines A, B, and C. Each machine A costs $80,000 and requires 2,000 square feet of floor space. Each machine B costs $50,000 and requires 3,000 square feet of floor space. Each machine C costs $40,000 and requires 5,000 square feet of floor space. The machines can produce 200, 250 and 350 units per day respectively. The plant can only afford $500,000 for all the machines and has at most 20,000 square feet of room for the machines. The company wants to buy as many machines as possible to maximize daily production. What are the key formulas for this Excel spreadsheet implementation of the following formulation? Let
X i = number of machines of type i purchased
MAX: Subject to:
200X 1 + 250X 2 + 300X 3 2X 1 + 3X 2 + 5X 3 ≤ 20 80X 1 + 50X 2 + 40X 3 ≤ 500 X 1 , X 2 , X 3 ≥ 0 A
1 2 3
Number to Buy
B Machine A
C Machine B
D Machine C
E
F
G
4 5 6 7 8
Production Possible
200
250
2 80
Hours Required 3 50
Resources Floor Space Req'd Assemble
ANS: Cell G4 E7
350
Total: Used
4 40
Formula =SUMPRODUCT(B2:D2,B4:D4) =SUMPRODUCT($B$2:$D$2,B7:D7)
Available: 20 500
Copied to E8
PTS: 1 71. A company needs to purchase several new machines to meet its future production needs. It can purchase three different types of machines A, B, and C. Each machine A costs $80,000 and requires 2,000 square feet of floor space. Each machine B costs $50,000 and requires 3,000 square feet of floor space. Each machine C costs $40,000 and requires 5,000 square feet of floor space. The machines can produce 200, 250 and 350 units per day respectively. The plant can only afford $500,000 for all the machines and has at most 20,000 square feet of room for the machines. The company wants to buy as many machines as possible to maximize daily production. What values would you enter in the Risk Solver Platform (RSP) task pane for the following cells for this Excel spreadsheet implementation of the formulation for this problem? Objective Cell: Variables Cells: Constraints Cells: Let
X i = number of machines of type i purchased
MAX: Subject to:
200X 1 + 250X 2 + 300X 3 2X 1 + 3X 2 + 5X 3 ≤ 20 80X 1 + 50X 2 + 40X 3 ≤ 500 X 1 , X 2 , X 3 ≥ 0 A
1 2 3 4 5 6 7 8 9 10
B
Machine 1 Number to buy Machine output Requirements: Square feet Cost
ANS: Objective Cell: $F$6 Variables Cells:
C Capital Expansion Machine Types Machine 2
D
E
F
Machine 3 Total Output:
200
250
300 Used
2,000 80,000
3,000 50,000
5,000 40,000
Available 20,000 500,000
$B$5:$D$5 Constraints Cells: $B$5:$D$5 ≥ 0 $E$9:$E$10 ≤ $F$9:$F$10 PTS: 1 72. State Farm Supply has just received an order for 10,000 pounds of chicken feed. The farmer has specified certain that the feed meet minimum requirements for Protein, Carbohydrate, Fat and Vitamins. State Farm can blend four different feeds to produce the required mix. The farmer would like to pay the lowest possible price for the feed. The data for the problem is summarized in the following table.
Nutrient Protein Carbohydrate Fat Vitamin Cost/1,000 lbs
Feed 1 15 20 20 1 $500
State Farm Supply Percent of Nutrient in: Feed 2 Feed 3 20 30 10 10 30 15 1.50 0.75 $600 $550
Feed 4 15 15 20 0.50 $450
Minimum Req'd Amt 18 12 20 1
Formulate the LP for this problem. ANS: Let MIN: Subject to:
X i = pounds of feed i used in mixture .5X 1 + .6X 2 + .55X 3 + .45X 4 .15X 1 + .20X 2 + .3X 3 + .15X 4 ≥ 1800 .20X 1 + .10X 2 + .1X 3 + .15X 4 ≥ 1200 .20X 1 + .30X 2 + .15X 3 + .20X 4 ≥ 2000 .01X 1 + .015X 2 + .0075X 3 + .005X 4 ≥ 100 1X 1 + 1X 2 + 1X 3 + 1X 4 = 10000 X i ≥ 0
PTS: 1 73. A paper mill has received an order for rolls of paper. The customer wants 400 12" wide rolls, 300 18" rolls and 200 24" rolls. The company has 40" wide rolls of paper which it can slit to the appropriate width. The company wants to minimize the number of rolls it must use to fill the order. Formulate the LP for this problem. ANS: Define the following cutting patterns.
Cutting pattern 1 2 3 4
Number of widths in roll 12" 18" 24" 3 0 0 1 1 0 1 0 1 0 2 0 400 300 200
Let
X i = number of rolls cut in pattern i
MIN: Subject to:
1X 1 + 1X 2 + 1X 3 + 1X 4 3X 1 + 1X 2 + 1X 3 ≥ 400 1X 2 + 2X 4 ≥ 300 1X 3 ≥ 200 X i ≥ 0
PTS: 1 74. Pete's Plastics manufactures plastic at plants in Miami, St. Louis and Cleveland. Pete needs to ship plastic to customers in Pittsburgh, Atlanta and Chicago. He wants to minimize the cost of shipping the plastic from his plants to his customers. The data for the problem is summarized in the following table.
Plant Miami St. Louis Cleveland Demand
Distance From Plants to Customers Pittsburgh Atlanta Chicago 1200 700 1300 700 550 300 125 675 350 40 60 20
Supply 30 40 50
Formulate the LP for this problem. ANS: Let MIN: Subject to:
X ij = tons shipped from plant i to customer j (i and j = 1, 2, 3) 1200X 11 + 700X 12 + 1300X 13 + 700X 21 + 550X 22 + 300X 23 + 125X 31 + 675X 32 + 350X 33 X 11 + X 12 + X 13 = 30 X 21 + X 22 + X 23 = 40 X 31 + X 32 + X 33 = 50 X 11 + X 21 + X 31 ≥ 40 X 12 + X 22 + X 32 ≥ 60 X 13 + X 23 + X 33 ≥ 20 X ij ≥ 0
PTS: 1 75. A financial planner wants to design a portfolio of investments for a client. The client has $400,000 to invest and the planner has identified four investment options for the money. The following requirements have been placed on the planner. No more than 30% of the money in any one investment, at least one half should be invested in long-term bonds which mature in six or more years, and no more than 40% of the total money should be invested in B or C since they are riskier investments. The planner has developed the following LP model based on the data in this table and the requirements of the client. The objective is to maximize the total return of the portfolio. Investment A B C D
Return 6.45% 8.5% 9.00% 7.75%
Years to Maturity 6 5 8 4
Rating 1-Excellent 3-Good 4-Fair 2-Very Good
Formulate the LP for this problem. ANS: Let
X 1 = Dollars invested in A X 2 = Dollars invested in B X 3 = Dollars invested in C X 4 = Dollars invested in D
MAX: Subject to:
.0645 X 1 + .085 X 2 + .090 X 3 + .0775 X 4 X 1 + X 2 + X 3 + X 4 ≤ 400000 X 1 ≤ 120000 X 2 ≤ 120000 X 3 ≤ 120000 X 4 ≤ 120000 X 1 + X 3 ≥ 200000 X 2 + X 3 ≤ 160000 X 1 , X 2 , X 3 , X 4 ≥ 0
PTS: 1 76. A financial planner wants to design a portfolio of investments for a client. The client has $400,000 to invest and the planner has identified four investment options for the money. The following requirements have been placed on the planner. No more than 30% of the money in any one investment, at least one half should be invested in long-term bonds which mature in six or more years, and no more than 40% of the total money should be invested in B or C since they are riskier investments. The planner has developed the following LP model based on the data in this table and the requirements of the client. The objective is to maximize the total return of the portfolio. Investment A B C D
Return 6.45% 8.5% 9.00% 7.75%
Years to Maturity 6 5 8 4
Rating 1-Excellent 3-Good 4-Fair 2-Very Good
Let
X 1 = Dollars invested in A X 2 = Dollars invested in B X 3 = Dollars invested in C X 4 = Dollars invested in D
MAX: Subject to:
.0645 X 1 + .085 X 2 + .090 X 3 + .0775 X 4 X 1 + X 2 + X 3 + X 4 ≤ 400000 X 1 ≤ 120000 X 2 ≤ 120000 X 3 ≤ 120000 X 4 ≤ 120000 X 1 + X 3 ≥ 200000 X 2 + X 3 ≤ 160000 X 1 , X 2 , X 3 , X 4 ≥ 0 A
1 2
Bond
B Amount Invested
C Maximum 30.0%
D Return
> > >
3 4 5 6 7 8 < < < < < < < < <
A B C D Total Invested: Total Available:
1 2 3 4 5 6 7 8
$0 $0 $0 $0 $0 $400,000
E Years to Maturity 6 5 8 4 Total: Required:
F 6+ years? (1-yes, 0-no) 1 0 1 0 $0 $200,000
$120,000 $120,000 $120,000 $120,000 Total:
6.45% 8.5% 9.00% 7.75% $0
G
H Good or worse? (1-yes, 0-no) 0 0 1 1 $0 $160,000
Rating 1-Excellent 3-Good 4-Fair 2-Very Good Total: Allowed:
> > > > > >
What values would you enter in the Risk Solver Platform (RSP) task pane for the following cells for this Excel spreadsheet implementation of this problem? Objective Cell: Variables Cells: Constraints Cells: ANS: Objective Cell: D7 Variables Cells: B3:B6 Constraints Cells: B3:B6 ≤ C3:C6 B3:B6 ≥ 0 B7 ≤ B8 F7 ≥ F8 H7 ≤ H8 PTS: 1 77. A financial planner wants to design a portfolio of investments for a client. The client has $400,000 to invest and the planner has identified four investment options for the money. The following requirements have been placed on the planner. No more than 30% of the money in any one investment, at least one half should be invested in long-term bonds which mature in six or more years, and no more than 40% of the total money should be invested in B or C since they are riskier investments. The planner has developed the following LP model based on the data in this table and the requirements of the client. The objective is to maximize the total return of the portfolio. Investment A B C D
Return 6.45% 8.5% 9.00% 7.75%
Years to Maturity 6 5 8 4
Rating 1-Excellent 3-Good 4-Fair 2-Very Good
Let
X 1 = Dollars invested in A X 2 = Dollars invested in B X 3 = Dollars invested in C X 4 = Dollars invested in D
MAX: Subject to:
.0645 X 1 + .085 X 2 + .090 X 3 + .0775 X 4 X 1 + X 2 + X 3 + X 4 ≤ 400000 X 1 ≤ 120000 X 2 ≤ 120000 X 3 ≤ 120000 X 4 ≤ 120000 X 1 + X 3 ≥ 200000 X 2 + X 3 ≤ 160000 X 1 , X 2 , X 3 , X 4 ≥ 0 A
1 2 3 4 5 6 7 8 < < < < < < < < <
Bond A B C D Total Invested: Total Available:
1 2 3 4 5 6 7 8
E Years to Maturity 6 5 8 4 Total: Required:
B Amount Invested $0 $0 $0 $0 $0 $400,000 F 6+ years? (1-yes, 0-no) 1 0 1 0 $0 $200,000
C Maximum 30.0% $120,000 $120,000 $120,000 $120,000 Total:
G Rating 1-Excellent 3-Good 4-Fair 2-Very Good Total: Allowed:
D Return 6.45% 8.5% 9.00% 7.75% $0
> > > > > > > > >
H Good or worse? (1-yes, 0-no) 0 0 1 1 $0 $160,000
What formulas are required for the following cells in the Excel spreadsheet implementation of the formulation? B7 D7 F7 H7 ANS: B7 =SUM(B3:B6) D7 =SUMPRODUCT($B$3:$B$6,D3:D6) F7 =SUMPRODUCT($B$3:$B$6,F3:F6) H7 =SUMPRODUCT($B$3:$B$6,H3:H6) PTS: 1
78. A company is planning production for the next 4 quarters. They want to minimize the cost of production. The production cost, demand and production capacity vary from quarter to quarter. The maximum amount of inventory which can be held is 100 units and management wants to keep at least 50 units on hand. Quarterly inventory holding cost is 4% of the cost of production. There are currently 50 units in inventory. The company wants to produce at no less than one half of its maximum capacity in any quarter. Quarter 1 $55 100 150 $2.2
Unit Production Cost Units Demanded Maximum Production Holding cost
2 $50 150 150 $2
3 $50 180 160 $2
4 $45 120 130 $1.8
Let
P i = number of units produced in quarter i, i = 1, ..., 4 B i = beginning inventory for quarter i
MIN:
55 P 1 + 50 P 2 + 50 P 3 + 45 P 4 + 2.2 (B 1 + B 2 )/2 + 2 (B 2 + B3 )/2 + 2 (B 3 + B 4 )/2 + 1.8 (B 4 + B 5 )/2 75 ≤ P 1 ≤ 150 75 ≤ P 2 ≤ 150 80 ≤ P 3 ≤ 160 65 ≤ P 4 ≤ 130 50 ≤ B 1 + P 1 − 100 ≤ 100 50 ≤ B 2 + P 2 − 150 ≤ 100 50 ≤ B 3 + P 3 − 180 ≤ 100 50 ≤ B 4 + P 4 − 120 ≤ 100 B 2 = B 1 + P 1 − 100 B 3 = B 2 + P 2 − 150 B 4 = B 3 + P 3 − 180 B 5 = B 4 + P 4 − 120 P i , B i ≥ 0
Subject to:
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
B
C
D
E
F
Quarter Beginning Inventory Units Produced Units Demanded Ending Inventory
1 50 120 100 70
2 70 150 150 70
3 70 160 180 50
4 50 120 120 50
Minimum Production Maximum Production
75 150
75 150
80 160
65 130
Minimum Inventory Maximum Inventory
50 100
50 100
50 100
50 100
Unit Production Cost Unit Carrying Cost
$55 $2.20
$50 $2.00
$50 $2.00
$45 $1.80
$6,600 $132
$7,500 $140
$8,000 $120
$5,400 $90
Monthly Production Cost Monthly Carrying Cost
4.0%
20
Total Cost
$27,982
What formulas are required for cells D3, D6, D8, D15, D17 and D18 in the Excel spreadsheet implementation of the formulation? ANS: D3 =C6 D6 =D3+D4-D5 D8 =D9/2 D15 =$B$15*D1 D17 =D14*D4) D18 =D15*(D3+D6)/2) PTS: 1 79. A grain store has six types of grain, each varying in cost, quality, and nutritional content. Periodically, excess inventory of these grains are consolidated into two local products, Feed-M-All and Supreme-Feed. Feed-M-All sells for $6.50 for a 10-pound bag while Supreme-Feed sells for $8.50 for a 10-pound bag. These feeds are advertised as having the following nutritional content: Grain Feed-M-All Supreme-Feed
Minimum Protein 16% 18%
Minimum Fat 18% 18%
Maximum Carbohydrates 10% 9%
The component grains have the following content characteristics: Grain
Cost/10 lbs
Quality
Protein
Fat
Carbohydrates
Pounds Avail.
A B C D E F
$4.75 $4.00 $3.75 $4.25 $4.50 $5.00
4 2 1 3 3 4
15% 20% 10% 15% 20% 25%
10% 20% 25% 20% 20% 15%
10% 8% 5% 10% 10% 12%
90 120 150 125 85 165
Targets for Feed-M-All are a cost of $ 4.35 per 10-pound bag, a quality rating of 2.25, along with the minimum percentages of protein and fat, and the maximum percentage of carbohydrates. Similar targets are set for Supreme-Feed with cost set at $ 4.60 and quality at 2.45. There must be at least a 70%-30% mix among these two local feeds. Formulate an LP model for this product mix problem. ANS: Let
X ij = amount of grain i in feed j where i = A, B, C, D, E, F and j = 1(Feed-M-All), 2(Supreme-Feed) Y j = total amount of feed j produced
MAX: $6.50Y 1 + $8.50Y 2 Subject to: Y 1 = X 11 + X 21 + X 31 + X 41 + X 51 + X 61 Y 2 = X 12 + X 22 + X 32 + X 42 + X 52 + X 62 X 11 + X 12 ≤ 90 X 21 + X 22 ≤ 120
Define Y j values Grain availability
X 31 + X 32 ≤ 150 X 41 + X 42 ≤ 125 X 51 + X 52 ≤ 85 X 61 + X 62 ≤ 165 220.5 ≤ Y 1 ≤ 514.5 220.5 ≤ Y 2 ≤ 514.5 4X 11 + 2X 21 + X 31 + 3X 41 + 3X 51 + 4X 61 ≥ 2.25Y 1 4X 12 + 2X 22 + X 32 + 3X 42 + 3X 52 + 4X 62 ≥ 2.45Y 2 4.75X 11 + 4X 21 + 3.75X 31 + 4.25X 41 + 4.5X 51 + 5X 61 ≤ 4.35Y 1 4.75X 12 + 4X 22 + 3.75X 32 + 4.25X 42 + 4.5X 52 + 5X 62 ≤ 4.60Y 2 10X 11 + 20X 21 + 10X 31 + 15X 41 + 20X 51 + 25X 61 ≥ 16Y1 10X 12 + 20X 22 + 10X 32 + 15X 42 + 20X 52 + 25X 62 ≥ 18Y2 10X 11 + 20X 21 + 25X 31 + 20X 41 + 20X 51 + 15X 61 ≥ 18Y1 10X 12 + 20X 22 + 25X 32 + 20X 42 + 20X 52 + 15X 62 ≥ 18Y2 10X 11 + 8X 21 + 5X 31 + 10X 41 + 10X 51 + 12X 61 ≤ 10Y 1 10X 12 + 8X 22 + 5X 32 + 10X 42 + 10X 52 + 12X 62 ≤ 9Y 2 X ij ≥ 0 for all i and j, Y j ≥ 0 for all j.
Mix requirements Quality targets Cost targets Protein targets Fat targets Carbohydrate targets
PTS: 1 80. Carlton construction is supplying building materials for a new mall construction project in Kansas. Their contract calls for a total of 250,000 tons of material to be delivered over a three-week period. Carlton's supply depot has access to three modes of transportation: a trucking fleet, railway delivery, and air cargo transport. Their contract calls for 120,000 tons delivered by the end of week one, 80% of the total delivered by the end of week two, and the entire amount delivered by the end of week three. Contracts in place with the transportation companies call for at least 45% of the total delivered be delivered by trucking, at least 40% of the total delivered be delivered by railway, and up to 15% of the total delivered be delivered by air cargo. Unfortunately, competing demands limit the availability of each mode of transportation each of the three weeks to the following levels (all in thousands of tons): Week 1 2 3 Costs ($ per 1000 tons)
Trucking Limits 45 50 55 $200
Railway Limits 60 55 45 $140
Air Cargo Limits 15 10 5 $400
Formulate an LP model for this logistics problem. ANS: Let
X ij = amount shipped by mode i in week j where i = 1(Truck), 2(Rail), 3(Air) and j = 1, 2, 3 WL ij = weekly limit of mode i in week j (as provided in above table)
MIN: 200(X 11 + X 12 + X 13 ) + 140(X 21 + X 22 + X 23 ) + 500(X 31 + X 32 + X 33 ) Subject to: Weekly limits by mode X ij ≤ WL ij for all i and j Total at end of three weeks X 11 + X 12 + X 13 + X 21 + X 22 + X 23 + X 31 + X 32 + X 33 ≥ 250 Total at end of two weeks X 11 + X 21 + X 31 + X 12 + X 22 + X 32 ≥ 200 Total at end of first week X 11 + X 21 + X 31 ≥ 120 Truck mix requirement X 11 + X 12 + X 13 ≥ 0.45*250 Rail mix requirement X 21 + X 22 + X 23 ≥ 0.40*250
Air mix limit
X 31 + X 32 + X 33 ≤ 0.15*250 X ij ≥ 0 for all i and j PTS: 1
81. Carlton construction is supplying building materials for a new mall construction project in Kansas. Their contract calls for a total of 250,000 tons of material to be delivered over a three-week period. Carlton's supply depot has access to three modes of transportation: a trucking fleet, railway delivery, and air cargo transport. Their contract calls for 120,000 tons delivered by the end of week one, 80% of the total delivered by the end of week two, and the entire amount delivered by the end of week three. Contracts in place with the transportation companies call for at least 45% of the total delivered be delivered by trucking, at least 40% of the total delivered be delivered by railway, and up to 15% of the total delivered be delivered by air cargo. Unfortunately, competing demands limit the availability of each mode of transportation each of the three weeks to the following levels (all in thousands of tons): Week 1 2 3 Costs ($ per 1000 tons)
Trucking Limits 45 50 55 $200
Railway Limits 60 55 45 $140
Air Cargo Limits 15 10 5 $400
The following is the LP model for this logistics problem. Let
X ij = amount shipped by mode i in week j where i = 1(Truck), 2(Rail), 3(Air) and j = 1, 2, 3 WL ij = weekly limit of mode i in week j (as provided in above table)
MIN: 200(X 11 + X 12 + X 13 ) + 140(X 21 + X 22 + X 23 ) + 500(X 31 + X 32 + X 33 ) Subject to: Weekly limits by mode X ij ≤ WL ij for all i and j Total at end of three weeks X 11 + X 12 + X 13 + X 21 + X 22 + X 23 + X 31 + X 32 + X 33 ≥ 250 Total at end of two weeks X 11 + X 21 + X 31 + X 12 + X 22 + X 32 ≥ 200 Total at end of first week X 11 + X 21 + X 31 ≥ 120 Truck mix requirement X 11 + X 12 + X 13 ≥ 0.45*250 Rail mix requirement X 21 + X 22 + X 23 ≥ 0.40*250 Air mix limit X 31 + X 32 + X 33 ≤ 0.15*250 X ij ≥ 0 for all i and j A 1 2 3 4 5 6 7 8 9 10 11 12 13 14
Costs Week 1 Week 2 Week 3 Shipped by Percentage Total Limit
Week 1 Week 2
B $200.00 by Truck 45 50 13 108 45% 108
Truck 45 50
C $140.00 by Rail 60 55 12 127 40% 100
Weekly Limits Rail 60 55
D $500.00 by Air 15 0 0 15 15% 37.5
Air 15 10
E
F
Totals 120 225 250
Required 120 200 250
Total Cost
$46,880.00
15
Week 3
55
45
5
What formula goes in cells F10, E3, E4, E5, and B6 of this Excel spreadsheet? ANS: F10 =SUMPRODUCT($B$1:$D$1,$B$6:$D$6) E3 =SUM($B$3:$D$3) E4 =SUM($B$4:$D$4) E5 =SUM($B$5:$D$5) B6 =SUM($B$3:$B$5) PTS: 1 82. Carlton construction is supplying building materials for a new mall construction project in Kansas. Their contract calls for a total of 250,000 tons of material to be delivered over a three-week period. Carlton's supply depot has access to three modes of transportation: a trucking fleet, railway delivery, and air cargo transport. Their contract calls for 120,000 tons delivered by the end of week one, 80% of the total delivered by the end of week two, and the entire amount delivered by the end of week three. Contracts in place with the transportation companies call for at least 45% of the total delivered be delivered by trucking, at least 40% of the total delivered be delivered by railway, and up to 15% of the total delivered be delivered by air cargo. Unfortunately, competing demands limit the availability of each mode of transportation each of the three weeks to the following levels (all in thousands of tons): Week 1 2 3 Costs ($ per 1000 tons)
Trucking Limits 45 50 55 $200
Railway Limits 60 55 45 $140
Air Cargo Limits 15 10 5 $400
The following is the LP model for this logistics problem. Let
X ij = amount shipped by mode i in week j where i = 1(Truck), 2(Rail), 3(Air) and j = 1, 2, 3 WL ij = weekly limit of mode i in week j (as provided in above table)
MIN: 200(X 11 + X 12 + X 13 ) + 140(X 21 + X 22 + X 23 ) + 500(X 31 + X 32 + X 33 ) Subject to: Weekly limits by mode X ij ≤ WL ij for all i and j Total at end of three weeks X 11 + X 12 + X 13 + X 21 + X 22 + X 23 + X 31 + X 32 + X 33 ≥ 250 Total at end of two weeks X 11 + X 21 + X 31 + X 12 + X 22 + X 32 ≥ 200 Total at end of first week X 11 + X 21 + X 31 ≥ 120 Truck mix requirement X 11 + X 12 + X 13 ≥ 0.45*250 Rail mix requirement X 21 + X 22 + X 23 ≥ 0.40*250 Air mix limit X 31 + X 32 + X 33 ≤ 0.15*250 X ij ≥ 0 for all i and j A 1 2 3 4 5
Costs Week 1 Week 2 Week 3
B $200.00 by Truck 45 50 13
C $140.00 by Rail 60 55 12
D $500.00 by Air 15 0 0
E
F
Totals 120 225 250
Required 120 200 250
6 7 8 9 10 11 12 13 14 15
Shipped by Percentage Total Limit
108 45% 108
127 40% 100
Truck 45 50 55
Weekly Limits Rail 60 55 45
15 15% 37.5 Total Cost
Week 1 Week 2 Week 3
$46,880.00
Air 15 10 5
What values would you enter in the Risk Solver Platform (RSP) task pane for the cells in this Excel spreadsheet implementation of this problem? Objective Cell: Variables Cells: Constraints Cells: ANS: Objective Cell: F10 Variables Cells: B3:D5 Constraints Cells: B3:D3 ≤ B13:D13 E3:E5 ≥ F3:F5 B6:C6 ≥ B8:C8 D6 ≤ D8 PTS: 1 83. Robert Hope received a welcome surprise in this management science class; the instructor has decided to let each person define the percentage contribution to their grade for each of the graded instruments used in the class. These instruments were: homework, an individual project, a mid-term exam, and a final exam. Robert's grades on these instruments were 75, 94, 85, and 92, respectively. However, the instructor complicated Robert's task somewhat by adding the following stipulations: • • • •
homework can account for up to 25% of the grade, but must be at least 5% of the grade; the project can account for up to 25% of the grade, but must be at least 5% of the grade; the mid-term and final must each account for between 10% and 40% of the grade but cannot account for more than 70% of the grade when the percentages are combined; and the project and final exam grades may not collectively constitute more than 50% of the grade.
Formulate an LP model for Robert to maximize his numerical grade. ANS: Let
W 1 = weight assigned to homework W 2 = weight assigned to the project W 3 = weight assigned to the mid-term W 4 = weight assigned to the final
MAX: Subject to:
75W 1 + 94W 2 + 85W 3 + 92W 4 W 1 + W 2 + W 3 + W 4 = 1 W 3 + W 4 ≤ 0.70 W 3 + W 4 ≥ 0.50 0.05 ≤ W 1 ≤ 0.25 0.05 ≤ W 2 ≤ 0.25 0.10 ≤ W 3 ≤ 0.40 0.10 ≤ W 4 ≤ 0.40
PTS: 1 84. Robert Hope received a welcome surprise in this management science class; the instructor has decided to let each person define the percentage contribution to their grade for each of the graded instruments used in the class. These instruments were: homework, an individual project, a mid-term exam, and a final exam. Robert's grades on these instruments were 75, 94, 85, and 92, respectively. However, the instructor complicated Robert's task somewhat by adding the following stipulations: • • • •
homework can account for up to 25% of the grade, but must be at least 5% of the grade; the project can account for up to 25% of the grade, but must be at least 5% of the grade; the mid-term and final must each account for between 10% and 40% of the grade but cannot account for more than 70% of the grade when the percentages are combined; and the project and final exam grades may not collectively constitute more than 50% of the grade.
The following LP model allows Robert to maximize his numerical grade. Let
W 1 = weight assigned to homework W 2 = weight assigned to the project W 3 = weight assigned to the mid-term W 4 = weight assigned to the final
MAX: Subject to:
75W 1 + 94W 2 + 85W 3 + 92W 4 W 1 + W 2 + W 3 + W 4 = 1 W 3 + W 4 ≤ 0.70 W 3 + W 4 ≥ 0.50 0.05 ≤ W 1 ≤ 0.25 0.05 ≤ W 2 ≤ 0.25 0.10 ≤ W 3 ≤ 0.40 0.10 ≤ W 4 ≤ 0.40 A
1 2 3 4 5 6 7 8 9 10 11 12 13
Mid Term Final Project Homework
Both Exams Final & Project
B
100%
C Percentage to grade 0.40 0.25 0.25 0.10 1.00 1.00
Grade Total 0.65 0.5
88.00 Limit 0.70 0.50
Grade 85 92 94 75
D
E Limits
Lower 0.10 0.10 0.05 0.05
Upper 0.40 0.40 0.25 0.25
What values would you enter in the Risk Solver Platform (RSP) task pane for the cells in this Excel spreadsheet implementation of this problem? Objective Cell: Variables Cells: Constraints Cells: ANS: Objective Cell: C10 Variables Cells: C3:C6 Constraints Cells: C3:C6 ≤ D3:D6 C3:C6 ≥ E3:E6 C7 = C8 B12 ≤ C12 B13 ≥ C13 PTS: 1 85. The hospital administrators at New Hope, County General, and City East recently received notice of an impending state inspection of their facilities. Under new guidelines established to improve the overall health care system, state inspectors will be assessing the efficiency of each hospital. The staff at New Hope has suggested a mutual assistance program in preparation for the inspections and have proposed using DEA as a means to assess the efficiency of each facility. The data collected thus far is summarized in the following table. All data reflects averages compiled over the past six months.
Input Measures Bed days unused (1000s) Supply expense ($1000s) Full-time staff Output Measures Patient-days (1000s) Nurses qualified Assistants on staff Customer satisfaction
New Hope
Hospital County General
City East
83.0 123.8 225.0
105.0 162.3 200.0
104.1 154.0 231.0
105.0 253.0 125.0 98.0
71.0 92.0 45.0 88.0
82.7 175.0 65.0 83.0
a.
Formulate a DEA LP model to evaluate the efficiency of City East.
b.
Implement a spreadsheet model for this problem and compute the DEA efficiency for each facility. Which facilities are efficient?
ANS: a. Let
MAX:
w i = weight assigned to output j, j = 1, ...,4 v i = weight assigned to input i, i = 1,...,3 82.7 w 1 + 175.0 w 2 + 65.0 w 3 + 83.0 w 4
Subject to: 105.1 w 1 + 253.0 w 2 + 125.0 w 3 + 98.0 w 4 − 83.0 v 1 − 123.8 v 2 − 225.0 v 3 ≤ 0 71.0 w 1 + 92.0 w 2 + 45.0 w 3 + 88.0 w 4 − 105.0 v 1 − 162.3 v 2 − 200 v 3 ≤ 0 82.7 w 1 + 175.0 w 2 + 65.0 w 3 + 83.0 w 4 − 104.1 v 1 − 154.0 v 2 − 231.0 v 3 ≤ 0 104.1 v 1 + 154.0 v 2 + 231.0 v 3 = 1 w 1 , w 2 , w 3 , w 4 , v 1 , v 2 , v 3 ≥ 0 b.
A 1 2 3 4 5 6 7 8 9 10 11 12 < < < < < < < < < < < < <
Hospital New Hope Cnty. General City East Weights UNIT Output Input
1 2 3 4 5 6 7 8 9 10 11 12
B Patient Days (1000s) 105.10 71.00 82.70 0.002009
C
Nurses Qual. 253.00 92.00 175.00
D Asst on Staff 125.0 45.0 65.0
E
Cust Sat. 98 88 83
0
0
0.00778
I
J
K
Wgt. Output 97% 83% 81%
Wgt. Input 97% 87% 100%
Diff 0.0000 −0.0381 −0.1877
3 0.812259 1
F Bed-Days Unused (1000s) 83.00 105.00 104.10
G Supply Expense ($1000s) 123.80 162.30 154.00
H Full Time Staff 225.00 200.00 231.00
0
0
0.004329
> > > > > > > > > > > > >
Results: Unit New Hope County General City East
DEA Efficiency 1.0000 0.9297 0.8123
New Hope is an efficient facility. PTS: 1 86. The hospital administrators at New Hope, County General, and City East recently received notice of an impending state inspection of their facilities. Under new guidelines established to improve the overall health care system, state inspectors will be assessing the efficiency of each hospital. The staff at New Hope has suggested a mutual assistance program in preparation for the inspections and have proposed using DEA as a means to assess the efficiency of each facility. The data collected thus far is summarized in the following table. All data reflects averages compiled over the past six months. Hospital
Input Measures Bed days unused (1000s) Supply expense ($1000s) Full-time staff Output Measures Patient-days (1000s) Nurses qualified Assistants on staff Customer satisfaction
New Hope
County General
City East
83.0 123.8 225.0
105.0 162.3 200.0
104.1 154.0 231.0
105.0 253.0 125.0 98.0
71.0 92.0 45.0 88.0
82.7 175.0 65.0 83.0
Based on the following formulation, is City East efficient? If not, what input and output values should they aspire to in order to become efficient? Let
w i = weight assigned to output j, j = 1, ..., 4 v i = weight assigned to input i, i = 1,...,3
MAX: 82.7 w 1 + 175.0 w 2 + 65.0 w 3 + 83.0 w 4 Subject to: 105.1 w 1 + 253.0 w 2 + 125.0 w 3 + 98.0 w 4 − 83.0 v 1 − 123.8 v 2 − 225.0 v 3 ≤ 0 71.0 w 1 + 92.0 w 2 + 45.0 w 3 + 88.0 w 4 − 105.0 v 1 − 162.3 v 2 − 200 v 3 ≤ 0 82.7 w 1 + 175.0 w 2 + 65.0 w 3 + 83.0 w 4 − 104.1 v 1 − 154.0 v 2 − 231.0 v 3 ≤ 0 104.1 v 1 + 154.0 v 2 + 231.0 v 3 = 1 w 1 , w 2 , w 3 , w 4 , v 1 , v 2 , v 3 ≥ 0 ANS: No, City East is not efficient. The following shows that 78.69% of New Hope input and outputs produces a composite unit with outputs greater than or equal to those of City East requiring less input than City East.
Unit New Hope County General City East Comp Vals < < < < < < < < <
Unit New Hope County General City East Comp Vals
--- Outputs --Patient Days (1000s) 105.10 71.00 82.70 82.7 --- Inputs --Bed-Days Unused (1000s) 83.00 105.00 104.10 65.3
Nurses Qual. 253.00 92.00 175.00
Asst on Staff 125.0 45.0 65.0
Cust Sat. 98 88 83
199.1
98.4
77.1
Supply Expense ($1000s) 123.80 162.30 154.00
Full Time Staff 225.00 200.00 231.00
97.4
177.0
> > > > > > > > >
Composite Weight 0.7869 0 0
Note, however, the drop in customer satisfaction. City East will not want to aspire to that particular level. These composite values will make City East efficient.
A 1 2 3 4 5 6 7 8 9 10 11 12 < < < < < < < < < < < < <
Hospital New Hope Cnty. General City East Weights
B Patient Days (1000s) 105.10 71.00 82.70 0.004279
UNIT Output Input
1 2 3 4 5 6 7 8 9 10 11 12
C
Nurses Qual. 253.00 92.00 199.0
D Asst on Staff 125.0 45.0 98.4
E
Cust Sat. 98 88 83
0
0
0.007784
3 1 1
F Bed-Days Unused (1000s) 83.00 105.00 65.3
G Supply Expense ($1000s) 123.80 162.30 97.4
H Full Time Staff 225.00 200.00 177
0
0
0.005649
> > > > > > > > > > > > >
I
J
K
Wgt. Output 121% 99% 100%
Wgt. Input 127% 113% 100%
Diff −0.0586 −0.1411 0.0000
PTS: 1 87. The hospital administrators at New Hope, County General, and City East recently received notice of an impending state inspection of their facilities. Under new guidelines established to improve the overall health care system, state inspectors will be assessing the efficiency of each hospital. The staff at New Hope has suggested a mutual assistance program in preparation for the inspections and have proposed using DEA as a means to assess the efficiency of each facility. The data collected thus far is summarized in the following table. All data reflects averages compiled over the past six months.
Input Measures Bed days unused (1000s) Supply expense ($1000s) Full-time staff Output Measures Patient-days (1000s) Nurses qualified Assistants on staff Customer satisfaction
New Hope
Hospital County General
City East
83.0 123.8 225.0
105.0 162.3 200.0
104.1 154.0 231.0
105.0 253.0 125.0 98.0
71.0 92.0 45.0 88.0
82.7 175.0 65.0 83.0
Enter the numbers in the appropriate cells of ranges B4:H6 in the Excel spreadsheet to solve this problem based on the following formulation. Let
w i = weight assigned to output j, j = 1, ..., 4 v i = weight assigned to input i, i = 1,...,3
MAX: 82.7 w 1 + 175.0 w 2 + 65.0 w 3 + 83.0 w 4 Subject to: 105.1 w 1 + 253.0 w 2 + 125.0 w 3 + 98.0 w 4 − 83.0 v 1 − 123.8 v 2 − 225.0 v 3 ≤ 0 71.0 w 1 + 92.0 w 2 + 45.0 w 3 + 88.0 w 4 − 105.0 v 1 − 162.3 v 2 − 200 v 3 ≤ 0 82.7 w 1 + 175.0 w 2 + 65.0 w 3 + 83.0 w 4 − 104.1 v 1 − 154.0 v 2 − 231.0 v 3 ≤ 0 104.1 v 1 + 154.0 v 2 + 231.0 v 3 = 1 w 1 , w 2 , w 3 , w 4 , v 1 , v 2 , v 3 ≥ 0 A 1 2 3 4 5 6 7 8 9 10 11 12 < < < < < < < < < < < < <
Hospital New Hope Cnty. General City East Weights UNIT Output Input
1 2 3 4 5 6 7 8 9 10 11 12
B Patient Days (1000s)
0
C
Nurses Qual.
D Asst on Staff
E
Cust Sat.
0
0
0
3 0.81 1.0
F Bed-Days Unused (1000s)
G Supply Expense ($1000s)
H Full Time Staff
0
0
0
> > > > > > > > > > > > >
I
J
K
Wgt. Output 97% 83% 81%
Wgt. Input 97% 87% 100%
Diff 0.0000 −0.0381 −0.1877
ANS: A 1 2 3 4 5 6 7 8 9 10 11 12 <
Hospital New Hope Cnty. General City East Weights UNIT Output Input
B Patient Days (1000s) 105.10 71.00 82.70 0.002009
C
Nurses Qual. 253.00 92.00 175.00
D Asst on Staff 125.0 45.0 65.0
E
Cust Sat. 98 88 83
0
0
0.00778
3 0.812259 1 F
G
H
I
J
> > > > > > > > > > > > > K
< < < < < < < < < < < <
1 2 3 4 5 6 7 8 9 10 11 12
Bed-Days Unused (1000s) 83.00 105.00 104.10
Supply Expense ($1000s) 123.80 162.30 154.00
Full Time Staff 225.00 200.00 231.00
0
0
0.004329
Wgt. Output 97% 83% 81%
Wgt. Input 97% 87% 100%
Diff 0.0000 −0.0381 −0.1877
PTS: 1 88. The hospital administrators at New Hope, County General, and City East recently received notice of an impending state inspection of their facilities. Under new guidelines established to improve the overall health care system, state inspectors will be assessing the efficiency of each hospital. The staff at New Hope has suggested a mutual assistance program in preparation for the inspections and have proposed using DEA as a means to assess the efficiency of each facility. The data collected thus far is summarized in the following table. All data reflects averages compiled over the past six months.
New Hope
Hospital County General
City East
83.0 123.8 225.0
105.0 162.3 200.0
104.1 154.0 231.0
105.0 253.0 125.0 98.0
71.0 92.0 45.0 88.0
82.7 175.0 65.0 83.0
Input Measures Bed days unused (1000s) Supply expense ($1000s) Full-time staff Output Measures Patient-days (1000s) Nurses qualified Assistants on staff satisfaction
What are the key formulas for this Excel spreadsheet implementation of the following formulation? Let
w i = weight assigned to output j, j = 1, ..., 4 v i = weight assigned to input i, i = 1,...,3
MAX: 82.7 w 1 + 175.0 w 2 + 65.0 w 3 + 83.0 w 4 Subject to: 105.1 w 1 + 253.0 w 2 + 125.0 w 3 + 98.0 w 4 − 83.0 v 1 − 123.8 v 2 − 225.0 v 3 ≤ 0 71.0 w 1 + 92.0 w 2 + 45.0 w 3 + 88.0 w 4 − 105.0 v 1 − 162.3 v 2 − 200 v 3 ≤ 0 82.7 w 1 + 175.0 w 2 + 65.0 w 3 + 83.0 w 4 − 104.1 v 1 − 154.0 v 2 − 231.0 v 3 ≤ 0 104.1 v 1 + 154.0 v 2 + 231.0 v 3 = 1 w 1 , w 2 , w 3 , w 4 , v 1 , v 2 , v 3 ≥ 0 A 1 2 3 4 5
Hospital New Hope Cnty. General
B Patient Days (1000s) 105.10 71.00
C
Nurses Qual. 253.00 92.00
D Asst on Staff 125.0 45.0
E
Cust Sat. 98 88
> > > > > >
6 7 8 9 10 11 12 < < < < < < < < < < < < <
City East
82.70
175.00
65.0
83
Weights
0.002009
0
0
0.00778
UNIT Output Input
1 2 3 4 5 6 7 8 9 10 11 12
3 0.812259 1
F Bed-Days Unused (1000s) 83.00 105.00 104.10
G Supply Expense ($1000s) 123.80 162.30 154.00
H Full Time Staff 225.00 200.00 231.00
0
0
0.004329
ANS: Cell I4 J4 K4 B11 B12
> > > > > > >
I
J
K
Wgt. Output 97% 83% 81%
Wgt. Input 97% 87% 100%
Diff 0.0000 −0.0381 −0.1877
Formula =SUMPRODUCT($B$8:$E$8,B4:E4) =SUMPRODUCT($F$8:$H$8,F4:H4) =I4-J4 =INDEX(I4:I6,B10,1) =INDEX(J4:J6, B10,1)
Copied to I5:I6 J5:J6 K5:K6
PTS: 1
PROJECT 89. Project 3.1
The Diet Problem: Ordering Meals from McDonald's
Based on: Robert A. Bosch, "Big Mac Attack: The Diet Problem revisited, Eating at McDonald's," OR/MS Today, August 1993, pp 30-31. Tina Simpson is a new fourth-grade teacher at Forest Ridge Elementary. The first teacher workshop for the upcoming school year is next Monday and by majority vote, McDonald's was selected as the food of choice. As the new person, Tina is tasked with developing the meal for the workshop. McDonald's has graciously offered to deliver whatever food Tina decides to order, along with a variety of condiments applicable to whatever is ordered. Rather than offer a menu choice, Tina has decided to simply order the same meal for each person in the workshop. To get started, Tina took a trip to McDonald's and obtained their published information on the nutritional content of their food. That data is summarized in the table below.
Menu Item Hamburger McLean Dlx
Price ($) 0.59 1.79
Calories 255 320
Protein Fat (grams) 12 9 22 10
Sodium (mg) 490 670
> > > >
Big Mac 1.65 Small Fries 0.68 McNuggets 1.56 Honey 0.00 Chef Salad 2.69 Garden Salad 1.96 Egg McMuffin 1.36 Wheaties 1.09 Yogurt Cone 0.63 Milk 0.56 Orange Juice 0.88 Grapefruit juice 0.68 Apple Juice 0.68 Prices recorded August, 1991 in Oberlin Ohio < < < < < < < < < < < < < < < < < < <
Vit A
500 220 270 45 170 50 280 90 105 110 80 80 90
25 3 20 0 17 4 18 2 4 9 1 1 0
26 12 15 0 9 2 11 1 1 2 0 0 0
890 110 580 0 400 70 710 220 80 130 0 0 5
Vit C Vit B1 Vit B2 Niacin Calcium Menu Item % U.S. Recommended Daily Allowance (RDA) Hamburger 4 4 20 10 20 10 McLean Dlx 10 10 25 20 35 15 Big Mac 6 2 30 25 35 25 Small Fries * 15 10 * 10 * McNuggets * * 8 8 40 * Honey * * * * * * Chef Salad 100 35 20 15 20 15 Garden Salad 90 35 6 6 2 4 Egg McMuffin 10 * 30 20 20 25 Wheaties 20 20 20 20 20 2 Yogurt Cone 2 * 2 10 2 10 Milk 10 4 8 30 * 30 Orange Juice * 120 10 * * * Grapefruit juice * 100 4 2 2 * Apple Juice * 2 2 * * * Prices recorded August, 1991 in Oberlin Ohio * Contains less than 2% of the U.S. RDA of these nutrients
> > > > > > > > > > > > > > Iron 15 20 20 2 6 * 8 8 15 20 * * * * 4
Tina wants the meal to be nutritionally complete. The National Research Council publishes their Recommended Daily Allowances. In this publication, they contend that a diet (in this case the meal) should provide at least 100 percent of the U.S. RDA of numerous nutrients. The specific amount of the RDA depends on such factors as age, weight and gender. In addition, the council recommends daily sodium and cholesterol intakes be kept to at most 2.4 grams of sodium and 300 milligrams of cholesterol. Further, at most 30 percent of the calories consumed should come from fat, and at most 10 percent from saturated fat. Each gram of fat contains 9 calories. Based on the above information, Tina wants to design a least-cost meal that provides at least 100% of the U.S. RDA of vitamins A, C, B 1 , B 2 , niacin, calcium, and iron; supplies at least 55 grams of protein; contains at most 3 grams of sodium; and contains at most 30 percent of its calories from fat. Only those foods list in the table above are available for the meal. Formulate the LP model for Tina's problem. Develop a spreadsheet model of the problem and use Excel Solver to determine the least-cost meal that meets all the stated requirements. What is the recommended meal? Is this meal reasonable? If not, modify the model to obtain what you believe to be a reasonable meal that meets the stated requirements. ANS: Answer not provided.