Transcript
70 ? Test Item File
Chapter Four ? 71
Excel Problems
1. An investment company currently has $1 million dollar available for investment in five different stocks. The company wants to maximize the interest earned over the next year. The five investment possibilities along with the expected interest earned are shown below. To manage risk, the investment firm wishes to have at least 35% of the investment in stocks A and B. Furthermore, no more than 15% of the investment may be in stock E.
Investment Expected Interest Earned (%)
Stock A 7
Stock B 9
Stock C 8
Stock D 10
Stock E 11
Sensitivity Report
Adjustable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$4 Stock A 0 -0.02 0.07 0.02 1E+30
$C$4 Stock B 350000 0 0.09 0.01 0.02
$D$4 Stock C 0 -0.02 0.08 0.02 1E+30
$E$4 Stock D 500000 0 0.1 0.01 0.01
$F$4 Stock E 150000 0 0.11 1E+30 0.01
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$G$7 Constraint 1 1000000 0.1 1000000 1E+30 500000
$G$8 Constraint 2 350000 -0.01 350000 500000 350000
$G$9 Constraint 3 150000 0.01 150000 500000 150000
Use the Sensitivity Report to answer the following questions:
a. What is the optimal total expected interest earned for next year?
b. What is the dollar amount that should be invested in each stock?
c. Which constraints are binding? Which constraints are not binding?
d. Is the solution to the problem unique or are there alternate optimal solutions?
e. Does the optimal solution call for investing the entire $1,000,000.00?
Answers:
Formulation:
Max 0.07A + 0.09B + 0.08C + 0.10D + 0.11E
Subject to:
A + B + C + D + E ? 1,000,000 (1)
A + B ? 350,000 (2)
E ? 150,000 (3)
A, B, C, D, E ? 0
a. $98,000.
b. A = $0, B = $350,000, C = $0, D = $500,000, and E = $150,000.
c. All three constraints are binding.
d. The solution is unique.
e. Yes
2. Refer to problem 1 and its associated Sensitivity Report.
a. What would be the impact on the optimal allocation if the expected interest earned on stock A decreases to 6%?
b. What would be the impact on the optimal allocation if the expected interest earned on stock A increases to 10%?
c. What should the minimal expected interest earned for stock C be before it would be desirable to invest in this particular stock?
d. What would be the impact on the optimal allocation and the objective function value if the expected interest earned on stock B decreases by 1%?
Answers:
a. Current optimal solution would remain the same. Currently, stock A is not in the solution.
b. The current optimal solution would change as the allowable increase for the objective function coefficient of stock A is 2%.
c. Expected interest must be at least 10% before it would be desirable to invest in stock C.
d. The current optimal solution would remain the same as the 1% change is within the allowable objective function coefficient decrease. However, the objective function value would decrease to $94,500.
3. Refer to problem 1 and its associated Sensitivity Report.
a. Suppose that the amount of money available for investment increases by $50,000. What impact would this have on the current optimal objective function value?
b. Suppose that total investment in stocks A and B must be at least 40% of the total amount available for investment (i.e., $400,000). What impact would this have on the current optimal objective function value?
c. Suppose that the total investment in stocks A and be must be at least 30% of the total amount available for investment. What impact would this have on the current optimal objective function value?
d. Assume that no more than 30% of the investment may be in stock E. What impact would this have on the current optimal objective function value?
Answers:
a. The shadow price for this constraint (constraint 1) is $0.1. Therefore, the objective function would improve by $50,000 x 0.1 = $5,000. The optimal objective function value would be $98,000 + $5,000 = $103,000.
b. The shadow price for constraint 2 is -$0.01 with an allowable increase of $500,000. Therefore, the current objective function value would decrease by $50,000 x 0.01 = $500.00. The new objective function value would be $97,500.
c. The shadow price for constraint 2 is -$0.01 with an allowable decrease of $350,000. Therefore, the current objective function value would improve by $50,000 x 0.01 = $500.00. The new objective function value would be $98,500.
d. Increasing the RHS of constraint 3 by $150,000 is within the allowable increase range. The shadow price of constraint 3 is $0.01. Therefore, the current objective function value would improve by $150,000 x 0.01 = $1500. The new objective function value would be $99,500.
4. An insurance company has three secretaries, A, B, and C that each is capable of processing four different types of insurance claims. The amount of time required by each secretary to process a particular type of a claim is summarized in the following table.
Processing Time in Hours
Claim Type
1 2 3 4
A 2 3 2 4
Secretary B 4 5 3 1
C 3 2 1 5
On a typical week, the insurance firm has 5 type 1 claims, 4 type 2 claims, 2 type 3 claims, and 3 type 4 claims. Each secretary works a maximum of 40 hours per week. The office manager wants to know how many of each type of an insurance claim should be processed by each secretary to minimize the total processing time.
Sensitivity Report
Adjustable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$C$13 A1 5 0 2 1 1E+30
$D$13 A2 0 1 3 1E+30 1
$E$13 A3 0 1 2 1E+30 1
$F$13 A4 0 3 4 1E+30 3
$C$14 B1 0 2 4 1E+30 2
$D$14 B2 0 3 5 1E+30 3
$E$14 B3 0 2 3 1E+30 2
$F$14 B4 3 0 1 3 1E+30
$C$15 C1 0 1 3 1E+30 1
$D$15 C2 4 0 2 1 1E+30
$E$15 C3 2 0 1 1 1E+30
$F$15 C4 0 4 5 1E+30 4
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$G$13 A constraint 10 0 40 1E+30 30
$G$14 B constraint 3 0 40 1E+30 37
$G$15 C constraint 10 0 40 1E+30 30
$C$16 Claim Type 1 5 2 5 15 5
$D$16 Claim Type 2 4 2 4 15 4
$E$16 Claim Type 3 2 1 2 30 2
$F$16 Claim Type 4 3 1 3 37 3
Use the Sensitivity Report to answer the following questions:
a. What is the total minimal processing time in hours?
b. Suppose that secretary A can process a type 2 claim in two hours rather than three hours. How would this impact the current optimal solution?
c. Assume that secretary C can process a type four claim in 6 hours rather than 5 hours. How would this impact the current optimal solution?
d. Currently, secretary C is not processing any type four claims. Suppose that we force secretary four to process one type 4 claim. What impact would this have on the optimal solution?
Answers:
Formulation:
Let Xij = no. of claims processed by by secretary i of claim type j
Min: 2XA1 + 3XA2 + 2XA3 + 4XA4 + 4XB1 + 5XB2 + 3XB3 + 1XB4 +
3XC1 + 2XC2 + 1XC3 + 5XC4
Subject to:
Secretary Constraints:
2XA1 + 3XA2 + 2XA3 + 4XA4 ? 40
4XB1 + 5XB2 + 3XB3 + 1XB4 ? 40
3XC1 + 2XC2 + 1XC3 + 5XC4 ? 40
Claim Type Constraints:
XA1 + XB1 + XC1 = 5
XA2 + XB2 + XC2 = 4
XA3 + XB3 + XC3 = 2
XA4 + XB4 + XC4 = 3
All Xij ? 0
a. Optimal solution = (5 x 2) + (4 x 2) + (2 x 1) + (3 x 1) = 23 hours.
b. The allowable decrease for variable A2 is 1. Since this is within the allowable range, the current optimal solution will not change.
c. Note that currently decision variable A4 is not in the optimal solution. So, “worsening” (i.e., increasing) the coefficient value of this variable will have no impact on the current solution.
d. The reduced cost for variable C4 is 4. This means that objective function value will increase (i.e., worsen) from 23 hours to 27 hours.
5. Refer to problem 4 and its associated Sensitivity Report.
a. Which constraints are binding?
b. Suppose that secretary A has a total of 45 weekly hours. What impact would this have on the current optimal solution?
c. What is the total number of unused weekly hours for secretary B?
d. Suppose that the number of type 4 claims increases to 4? What impact would this have on the current optimal solution?
Answers:
a. All the “claim Type” constraints are binding.
b. Shadow price for secretary A is zero, thus implying that additional hours will have no impact on the current optimal solution.
c. 37 unused weekly hours.
d. The shadow price for type 4 claim is 1. Also, note that increasing the number of claims to 4 is within the allowable increase range. Therefore, the current objective function value will increase by one hour to 24 hours.
6. Refer to problem 4 and its associated Sensitivity Report.
a. Suppose that the number of type 1 claims increases to 6 and the number of type 2 claims decreases to 3. What impact would this simultaneous change has on the current optimal objective function value?
b. What is the impact on the current solution and the objective function value if secretary A takes 3 hours to process claim 1 and secretary B takes 5 hours to process claim 1?
Answers:
a. Using the 100% rule, the sum of the ratios is 1/15 + 1/4 = 0.3167 which is less than 1, therefore, the information in the Sensitivity Report is still valid. The shadow price of type 1 claim constraint is 2. So, increasing the number of type 1 claims from 5 to 6 will increase the total processing time by 2 hours. Moreover, the shadow price of type 2 claim constraint is 2. So, decreasing the number of type 2 claims by 1 will reduce the total processing time by 2 hours. Thus, the total net effect on the current objective function value resulting from these simultaneous changes is zero.
b. According to the 100% rule, the sum of the ratios is 1/1 + 1/infinity = 1. Since the sum of the ratios does not exceed 1, the current optimal solution will not change. However, the total processing time will increase by 5 hours to 28 hours.
7. A company can ship its product from any of its three factories, F1, F2, and F3, to any of its retail outlets, R1, R2, and R3. The capacity, demand, and shipping cost information is provided as follows:
Demand (units) Capacity (units)
R1: 300 F1: 250
R2: 500 F2: 350
R3: 200 F3: 400
Shipping Cost/unit ($)
R1 R2 R3
F1 1 3 2
F2 3 4 2
F3 2 2 3
The company wants to come up with an optimal shipping strategy that will allow it to minimize its total shipping cost.
Sensitivity Report
Adjustable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$9 X1 250 0 1 1 1E+30
$C$9 X2 0 1 3 1E+30 1
$D$9 X3 0 2 2 1E+30 2
$B$10 X4 50 0 3 1 1
$C$10 X5 100 0 4 1 1
$D$10 X6 200 0 2 2 1E+30
$B$11 X7 0 1 2 1E+30 1
$C$11 X8 400 0 2 1 1E+30
$D$11 X9 0 3 3 1E+30 3
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$E$9 F1 250 -2 250 50 0
$E$10 F2 350 0 350 0 1E+30
$E$11 F3 400 -2 400 100 0
$B$12 R1 300 3 300 0 50
$C$12 R2 500 4 500 0 100
$D$12 R3 200 2 200 0 200
Use the Sensitivity Report to answer the following questions.
a. Will any of the retail outlets experience any shortages in meeting their demand requirements if we implement the optimal solution?
b. Will any of the factories have any remaining quantities of the product if we implement the optimal solution?
c. What is the total minimal shipping cost?
Answers:
Formulation:
Let X1=no. of units shipped from F1 to R1
X2= no of units shipped from F1 to R2
X3=no. of units shipped from F1 to R3
X4=no. of units shipped from F2 to R1
X3=no. of units shipped from F2 to R2
X3=no. of units shipped from F2 to R3
X3=no. of units shipped from F3 to R1
X3=no. of units shipped from F3 to R2
X3=no. of units shipped from F3 to R3
Min: 1X1+3X2+2X3+3X4+4X5+2X6+2X7+2X8+3X9
Subject to:
X1+X2+X3=250
X4+X5+X6=350
X7+X8+X9=400
X1+X4+X7=300
X2+X5+X8=500
X3+X6+X9=200
All X's ? 0
a. None of the retail outlets will experience any shortages as all three demand constraints
for R1, R2, and R3 are binding.
b. None of the factories will have any remaining quantities of the product as all the supply constraints are binding.
c. Total minimal shipping cost is $2000.
8. Refer to problem 4 and its associated Sensitivity Report.
a. Suppose that the shipping cost per unit for variable X1 (i.e., route F1-R1) increases to $2.00. What impact will this have on the current optimal solution and the objective function value?
b. Suppose that the shipping cost per unit for variable X6 (i.e., route F2-R3) increases by $2.00 to $5.00. What impact will this have on the current optimal solution and the objective function value?
c. Suppose that the factory constraints and the retail outlet constraints become “?”. What would the optimal solution and the objective function coefficient be? (Hint: the answer to this question is not given in the Sensitivity Report)
Answers:
a. The current optimal solution will remain the same as the increase is within the allowable range. However, the objective function value will increase by $250 to $2250.
b. The allowable increase for variable X6 is $2.00. Since the increase to $5.00 exceeds the maximum allowable increase, the current solution will no longer remain the same. The problem must be resolved with this new change.
c. Since the objective function is to minimize total shipping cost, the optimal solution along with the objective function will all assume a value of zero.
9. A real estate developer is planning to build an office complex. Currently, there are three office sizes under consideration: small, medium, and large. Small offices can be rented for $600 per month, medium offices can be rented for $750 per month, and large offices can be rented for $1000 per month. Each small office requires 600 square feet, each medium office requires 800 square feet, and each large office requires 1000 square feet. The current plot of land available to the developer is 100,000 square feet. The developer wants to ensure that the office complex has at least 3 units of each office size. Moreover, zoning restrictions limit the total number of offices to 50. Use the Sensitivity Report to answer the following questions:
Sensitivity Report
Adjustable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$4 Optimal Values Small 3 0 600 400 1E+30
$C$4 Optimal Values Medium 3 0 750 250 1E+30
$D$4 Optimal Values Large 44 0 1000 1E+30 250
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$E$8 Square footage 48200 0 100000 1E+30 51800
$E$9 Minimum no. of small 3 -400 3 41 3
$E$10 Minimum no. of medium 3 -250 3 41 3
$E$11 Minimum no. of large 44 0 3 41 1E+30
$E$12 Total no. of offices 50 1000 50 51.8 41
a. How many small, medium, and large offices should the developer build?
b. What is the total optimal monthly revenue?
c. Which constraints are binding? Which constraints are non-binding?
d. How much square footage would remain unused if the developer implements the optimal solution?
Answers:
Formulation:
Let X1 = no. of small offices to build
X2 = no. of medium offices to build
X3 = no. of large offices to build
Max: 600X1 + 750X2 + 1000X3
Subject to:
600X1 + 800X2 + 1000X3 ? 100,000 (square footage)
X1? 3 (minimum number of small offices)
X2? 3 (minimum number of medium offices)
X3 ? 3 (minimum number of large offices)
X1 + X2 + X3 ? 50 (maximum number of total offices)
X1, X2, and X3 ? 0
a. X1 = 3; X2 = 3; X3 = 44.
b. $48,050.00
c. Binding constraints are: minimum number of small offices constraint, minimum number of medium offices constraint, and the maximum number of total offices constraint. The other remaining two constraints are non binding.
d. 51800 square feet.
10. Refer to problem 9 and its associated Sensitivity Report.
a. What would be the impact on the optimal allocation of offices and the objective function value if small offices can be rented for $800 per month rather than $600 per month?
b. What would be the impact on the optimal allocation of offices and the objective function value if medium offices can be rented for $650 per month rather than $750 per month?
c. What would be the impact on the optimal allocation of offices if medium offices can be rented for $1100.00 rather than $750 per month?
d. Is the solution to the problem unique or are there alternate optimal solutions?
Answers:
a. The optimal allocation of offices would remain the same as this increase is within the allowable range. However, the objective function value would increase by $600 to $48,650.00.
b. The optimal allocation of offices would remain the same as this decrease is within the allowable range. However, the objective function value would decline by $300 to $47,750.00.
c. This rental increase exceeds the maximum allowable value. Therefore, the current solution optimal would change. Problem must be resolved.
d. The solution to this problem is unique.
11. Refer to problem 9 and its associated Sensitivity Report.
a. Suppose that the square footage available to the developer increases to 110,000 square feet. What impact would this have on the optimal objective function value?
b. Suppose that the minimal number of small officers that the developer needs to build must be at least 5 offices. What impact would this have on the optimal objective function value?
c. Suppose that the monthly rental of small offices increases to $650, and that the monthly rental of medium offices increases to $800. What impact will this have on the current optimal solution and the objective function value?
d. Suppose that the total number of offices increases to a maximum of 55, and the minimum number of medium offices increases to 5. What impact would this have on the current optimal objective function value?
Answers:
a. This increase would have no impact on the objective function value. Note that this constraint is not binding and has a shadow price of zero.
b. The shadow price associated with this constraint is $-400 and the increase is within the allowable range. Therefore, the current optimal objective function value will decrease by $800 to $47,250.00.
c. According to the 100% rule, the sum of the ratios is 50/400 + 50/250 = 0.325. Since the sum of the ratios does not exceed 1, the current optimal solution will not change. However, the objective function value will increase by $300 (3 x $50 + 3 x $50) to $48,350.
d. According to the 100% rule, the sum of the ratios is 5/51.8 + 2/41 = 0.055 < 1. Therefore, the information provided in the Sensitivity Report is still valid. The current objective function value would increase by $4500 (5 x $1000 + 2 x $-250) to $52,550.
