MA/OR 504PRACTICE PROBLEMS FOR MIDTERM EXAMPage | 1
- 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 / X1 = Number of liters of product A in total blend deliveredX2 = Number of liters of product B in total blend delivered
MIN: / 7 X1 + 3 X2
Subject to: / X1 + X2 = 1.5 * 50 (Total liters of mix)
X1 0.48 * 1.5 * 50 (X1 minimum)
X2 0.5 * 50 (X2 minimum)
.0.95 X1 + 0.78 X2 0.85 * 1.5 * 50 (85% alcohol minimum)
X1, X2 0
A / B / C / D / E
1 / Jacks' Distillery
2
3 / A / B
4 / Liters to use / Total Cost:
5 / Unit cost: / 10.5 / 4.5
6
7 / Constraints: / Supplied / Requirement
8 / Total Liters / 1 / 1 / 75
9 / A required / 1 / 36
10 / B required / 1 / 25
11 / 85% alcohol / 0.95 / 0.78 / 63.75
1a.Refer to the problem statement . What formula should be entered in cell E5 in the accompanying Excel spreadsheet to compute total cost?
a. / =B4*C4+B5*C5b. / =SUMPRODUCT(B4:C4,B5:C5)
c. / =SUM(B5:C5)
d. / =SUM(E8:E10)
ANS:B
1b.Refer to the problem statement. 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*C5b. / =SUMPRODUCT(B11:C11,$B$4:$C$4)
c. / =SUM(B5:C5)
d. / =SUM(E8:E10)
ANS:B
1c.Refer to the problem statement. Which cells should be changing cells in this problem?
a. / B4:C4b. / E5
c. / D8:D10
d. / E8:E10
ANS:A
1d.Refer to the problem statement. Which cells should be the constraint cells in this problem?
a. / B4:C4b. / E5
c. / D8:D11
d. / E8:E10
ANS:C
1e.Refer to the problem statement. Which of the following statements could represent a constraint in this problem?
a. / B4:C4 B5:C5b. / E5 0
c. / D8 = E8
d. / E8:E11 D8:D11
ANS:C
- 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 / Return / Years to Maturity / RatingA / 6.45% / 9 / 1-Excellent
B / 7.10% / 8 / 2-Very Good
C / 8.20% / 5 / 4-Fair
D / 9.00% / 8 / 3-Good
Let / X1 = Dollars invested in A
X2 = Dollars invested in B
X3 = Dollars invested in C
X4 = Dollars invested in D
MAX: / .0645 X1 + .071 X2 + .082 X3 + .09 X4
Subject to: / X1 + X2 + X3 + X4 300000
X1 75000
X2 75000
X3 75000
X4 75000
X1 + X2 + X4 100000
X3 + X4 75000
X1, X2, X3, X4 0
A / B / C / D
1 / Amount / Maximum
2 / Bond / Invested / 25.0% / Return
3 / A / $0 / $75,000 / 6.45%
4 / B / $0 / $75,000 / 7.10%
5 / C / $0 / $75,000 / 8.20%
6 / D / $0 / $75,000 / 9.00%
7 / Total Invested: / $0 / Total: / $0
8 / Total Available: / $300,000
E / F / G / H
1 / Years to / 7+ years? / Good or worse?
2 / Maturity / (1-yes, 0-no) / Rating / (1-yes, 0-no)
3 / 9 / 1 / 1-Excellent / 0
4 / 8 / 1 / 2-Very Good / 0
5 / 5 / 0 / 4-Fair / 1
6 / 8 / 1 / 3-Good / 1
7 / Total: / $0 / Total: / $0
8 / Required: / $100,000 / Allowed: / $75,000
2a.Refer to the problem statement. 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
2b.Refer to the problem statement. 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
2c.Refer to the problem statement. Which cells are changing cells in the accompanying Excel spreadsheet?
a. / B3:B6b. / B7:I7
c. / C7
d. / E7
ANS:A
3.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 / Required # of Nurses / Wage ($/hr)12 am 4 am / 20 / 15
4 am 8 am / 30 / 16
8 am 12 pm / 40 / 13
12 pm 4 pm / 50 / 13
4 pm 8 pm / 40 / 14
8 pm 12 am / 30 / 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 / Xi = number of nurses working in time period i; i = 1,6MIN: / 1X1 + 1X2 + 1X3 + 1X4 + 1X5 + 1X6
Subject to: / 1X1 + 1X2 30
1X2 + 1X3 40
1X3 + 1X4 50
1X4 + 1X5 40
1X5 + 1X6 30
1X1 + 1X6 20
Xi 0
A / B / C / D / E / F / G / H / I
1 / Nurse / Hiring
2
3 / Hours for each shift
4 / Mid / 4am / 8am / Noon / 4pm / 8pm / Nurses / Wages per
5 / Shift / 4am / 8am / Noon / 4pm / 8pm / Mid / Scheduled / Nurse
6 / 1 / $15
7 / 2 / $16
8 / 3 / $13
9 / 4 / $13
10 / 5 / $14
11 / 6 / $15
12 / Available: / Total Wages:
13 / Required:
ANS:
A / B / C / D / E / F / G / H / I1 / Nurse / Hiring
2
3 / Hours for each shift
4 / Mid / 4am / 8am / Noon / 4pm / 8pm / Nurses / Wages per
5 / Shift / 4am / 8am / Noon / 4pm / 8pm / Mid / Scheduled / Nurse
6 / 1 / 1 / 1 / 0 / 0 / 0 / 0 / $15
7 / 2 / 0 / 1 / 1 / 0 / 0 / 0 / $16
8 / 3 / 0 / 0 / 1 / 1 / 0 / 0 / $13
9 / 4 / 0 / 0 / 0 / 1 / 1 / 0 / $13
10 / 5 / 0 / 0 / 0 / 0 / 1 / 1 / $14
11 / 6 / 1 / 0 / 0 / 0 / 0 / 1 / $15
12 / Available: / Total Wages:
13 / Required: / 20 / 30 / 40 / 50 / 40 / 30
4.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.
HospitalNew Hope / County General / City East
Input Measures
Bed days unused (1000s) / 83.0 / 105.0 / 104.1
Supply expense ($1000s) / 123.8 / 162.3 / 154.0
Full-time staff / 225.0 / 200.0 / 231.0
Output Measures
Patient-days (1000s) / 105.0 / 71.0 / 82.7
Nurses qualified / 253.0 / 92.0 / 175.0
Assistants on staff / 125.0 / 45.0 / 65.0
Customer satisfaction / 98.0 / 88.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 / wi = weight assigned to output j, j = 1, ..., 4vi = weight assigned to input i, i = 1,...,3
MAX: / 82.7 w1 + 175.0 w2 + 65.0 w3 + 83.0 w4
Subject to:
105.1 w1 + 253.0 w2 + 125.0 w3 + 98.0 w4 83.0 v1 123.8 v2 225.0 v3 0
71.0 w1 + 92.0 w2 + 45.0 w3 + 88.0 w4 105.0 v1 162.3 v2 200 v3 0
82.7 w1 + 175.0 w2 + 65.0 w3 + 83.0 w4 104.1 v1 154.0 v2 231.0 v3 0
104.1 v1 + 154.0 v2 + 231.0 v3 = 1
w1, w2, w3, w4, v1, v2, v3 0
A / B / C / D / E
1 / Patient / Asst
2 / Days / Nurses / on / Cust
3 / Hospital / (1000s) / Qual. / Staff / Sat.
4 / New Hope
5 / Cnty. General
6 / City East
7
8 / Weights / 0 / 0 / 0 / 0
9
10 / UNIT / 3
11 / Output / 0.81
12 / Input / 1.0
F / G / H / I / J / K
1 / Bed-Days / Supply / Full
2 / Unused / Expense / Time / Wgt. / Wgt.
3 / (1000s) / ($1000s) / Staff / Output / Input / Diff
4 / 97% / 97% / 0.0000
5 / 83% / 87% / 0.0381
6 / 81% / 100% / 0.1877
7
8 / 0 / 0 / 0
9
10
11
12
ANS:
A / B / C / D / E1 / Patient / Asst
2 / Days / Nurses / on / Cust
3 / Hospital / (1000s) / Qual. / Staff / Sat.
4 / New Hope / 105.10 / 253.00 / 125.0 / 98
5 / Cnty. General / 71.00 / 92.00 / 45.0 / 88
6 / City East / 82.70 / 175.00 / 65.0 / 83
7
8 / Weights / 0.002009 / 0 / 0 / 0.00778
9
10 / UNIT / 3
11 / Output / 0.812259
12 / Input / 1
F / G / H / I / J / K
1 / Bed-Days / Supply / Full
2 / Unused / Expense / Time / Wgt. / Wgt.
3 / (1000s) / ($1000s) / Staff / Output / Input / Diff
4 / 83.00 / 123.80 / 225.00 / 97% / 97% / 0.0000
5 / 105.00 / 162.30 / 200.00 / 83% / 87% / 0.0381
6 / 104.10 / 154.00 / 231.00 / 81% / 100% / 0.1877
7
8 / 0 / 0 / 0.004329
9
10
11
12
5.The following questions correspond to the problem below and associated Solver sensitivity report.
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 / W1 = weight assigned to homeworkW2 = weight assigned to the project
W3 = weight assigned to the mid-term
W4 = weight assigned to the final
MAX: / 75W1 + 94W2 + 85W3 + 92W4
Subject to: / W1 + W2 + W3 + W4 = 1
W3 + W4 0.70
W2 + W4≤ 0.50
0.05 W1 0.25
0.05 W2 0.25
0.10 W3 0.40
0.10 W4 0.40
Adjustable Cells
Final / Reduced / Objective / Allowable / Allowable
Cell / Name / Value / Cost / Coefficient / Increase / Decrease
$F$5 / Mid Term to grade / 0.40 / 10.00 / 85 / 1E+30 / 10
$F$6 / Final to grade / 0.25 / 0.00 / 92 / 2 / 17
$F$7 / Project to grade / 0.25 / 2.00 / 94 / 1E+30 / 2
$F$8 / Homework to grade / 0.10 / 0.00 / 75 / 10 / 1E+30
Constraints
Final / Shadow / Constraint / Allowable / Allowable
Cell / Name / Value / Price / R.H. Side / Increase / Decrease
$E$14 / Both Exams Total / 0.65 / 0 / 0.7 / 1E+30 / 0.05
$E$15 / Final & Project Total / 0.5 / 17 / 0.5 / 0.05 / 0.15
$F$9 / 100% to grade / 1.00 / 75.00 / 1 / 0.15 / 0.05
5a.Refer to the problem statement. Constraint cell F9 corresponds to the constraint, W1 + W2 + W3 + W4 = 1, and has a shadow price of 75. Armed with this information, what can Robert request of his instructor regarding this constraint?
ANS:
Nothing. The constraint has the largest shadow price but enforces the total percentages to equal 1, thus nothing can be changed.
5b.Refer to the problem statement. Based on the Solver sensitivity report information, is there anything Robert can request of his instructor to improve his final grade?
ANS:
Robert can request an increase in the total weight allowed for the project and final exam combined since this has a positive shadow price.
5c.Refer to the problem statement. Based on the Solver sensitivity report information, Robert has been approved by his instructor to increase the total weight allowed for the project and final exam to 0.50 plus the allowable increase. When Robert re-solves his model, what will his new final grade score be?
ANS:
88.85 since shadow price of 17 and increase of 0.05 equates to 0.85.
6.An oil company wants to create lube oil, gasoline and diesel fuel at two refineries. There are two sources of crude oil. The following network representation depicts this problem.
Write out the LP formulation for this problem.
ANS:
MIN: / 15X13 + 13X14 + 9X23 + 11X24 + 4X35 + 7X36 + 8X37 + 3X45 + 9X46 + 6X47Subject to: / X13 X14 = 100
X23 X24 = 50
0.80X13 + 0.95X23 X35 X36 X37 = 0
0.85X14 + 0.85X24 X45 X46 X47 = 0
0.95X35 + 0.90X45 = 50
0.90X36 + 0.95X46 = 25
0.90X37 + 0.95X47 = 75
Xij 0
7.A small town wants to build some new recreational facilities. The proposed facilities include a swimming pool, recreation center, basketball court and baseball field. The town council wants to provide the facilities which will be used by the most people, but faces budget and land limitations. The town has $400,000 and 14 acres of land. The pool requires locker facilities which would be in the recreation center, so if the swimming pool is built the recreation center must also be built. Also the council has only enough flat land to build the basketball court or the baseball field. The daily usage and cost of the facilities (in $1,000) are shown below.
Variable / Facility / Usage / Cost ($1,000) / LandX1 / Swimming pool / 400 / 100 / 2
X2 / Recreation center / 500 / 200 / 3
X3 / Basketball court / 300 / 150 / 4
X4 / Baseball field / 200 / 100 / 5
Based on this ILP formulation of the problem and the indicated optimal solution what values should go in cells B5:G12 of the following Excel spreadsheet?
MAX: / 400 X1 + 500 X2 + 300 X3 + 200 X4Subject to: / 100 X1 + 200 X2 + 150 X3 + 100 X4 400 / budget
2 X1 + 3 X2 + 4 X3 + 5 X4 14 / land
X1 X2 0 / pool and recreation center
X3 + X4 1 / basketball and baseball
Xi = 0, 1
Solution: (X1, X2, X3, X4) = (1, 1, 0, 1)
A / B / C / D / E / F / G
1
2
3 / Facilities
4 / Pool / Rec center / Basketball / Baseball / Total usage:
5 / Select (0=no, 1=yes)
6 / Usage
7
8 / Resources / Used / Available
9 / Cost
10 / Land
11 / Pool & Rec center
12 / Basket or Baseball
ANS:
A / B / C / D / E / F / G1
2
3 / Facilities
4 / Pool / Rec center / Basketball / Baseball / Total usage:
5 / Select (0=no, 1=yes) / 1 / 1 / 0 / 1 / 1100
6 / Usage / 400 / 500 / 300 / 200
7
8 / Resources / Used / Available
9 / Cost / 100 / 200 / 150 / 100 / 400 / 400
10 / Land / 2 / 3 / 4 / 5 / 10 / 14
11 / Pool & Rec center / 1 / 1 / 0 / 0 / 0 / 0
12 / Basket or Baseball / 0 / 0 / 1 / 1 / 1 / 1
8.The following questions are based on the problem below.
A company wants to advertise on TV and radio. The company wants to produce about 6 TV ads and 12 radio ads. Each TV ad costs $20,000 and is viewed by 10 million people. Radio ads cost $10,000 and are heard by 7 million people. The company wants to reach about 140 million people, and spend about $200,000 for all the ads. The problem has been set up in the following Excel spreadsheet.
A / B / C / D / E1 / Problem Data / TV / Radio
2 / Cost / 20 / 10
3 / Coverage / 10 / 7
4
5 / Goal Constraints / TV / Radio / Cost / Coverage
6 / Actual Amount / 0 / 0
7 / +Under / 0 / 0 / 0 / 0
8 / Over / 0 / 0 / 0 / 0
9 / = Goal / 0 / 0 / 0 / 0
10 / Target Value / 6 / 12 / 200 / 140
11
12 / Percentage Deviation:
13 / Under / 1 / 1 / 1 / 1
14 / Over / 0 / 0 / 0 / 0
15
16 / Weights
17 / Under
18 / Over
19
20 / Objective / 0
8a.Refer to the problem statement. What formula goes in cell D6?
a. / =SUMPRODUCT(B2:B3,B6:B7)b. / =B2*C2+B6*C6
c. / =SUMPRODUCT(B2:C2,B10:C10)
d. / =SUMPRODUCT(B2:C2,B6:C6)
ANS:D
8b.Refer the problem statement. What formula goes in cell B9?
a. / =SUM(B6:B8)b. / =B6+B7-B8
c. / =B6-B7+B8
d. / =B10-B8
ANS:B
8c.Refer to the problem statement. Which of the following is a constraint specified to Solver for this model?
a. / $B$9:$E$9=$B$6:$E$6b. / $B$9:$E$9<$B$10:$E$10
c. / $B$9:$E$9=$B$10:$E$10
d. / $B$9:$E$9>$B$10:$E$10
ANS:C
8d.Refer the problem statement. Which cells are the variable cells in this model?
a. / $B$6:$C$6, $B$7:$E$8b. / $B$6:$C$6
c. / $B$9:$E$9
d. / $B$6:$E$8
ANS:A
8e.Refer to the problem statement. Which cell(s) is(are) the objective cell(s) in this model?
a. / $B$20b. / $D$6
c. / $E$6
d. / $B$13:$E$14, $B$9:$E$9
ANS:A
8f.Refer to the problem statement. If the company is very concerned about going over the $200,000 budget, which cell value should change and how should it change?
a. / D13, increaseb. / D13, decrease
c. / D14, increase
d. / D14, decrease
ANS:C