either statsolver or mike bayville…
who ever is able to finish it by tomorrow
this is the text book An Introduction to Management Science: Quantitative Approaches to Decision Making, Revised, 13th edition, Anderson, Sweeney, Williams, Camm and Martin (Cengage Learning, 2012), ISBN: 9781111532222.
Excel Solver can be used to solve linear programs.
1. p. 126 7
2. p. 134 17
3. p. 138 21 (a)(d)
4. p. 140 25
5. p. 143 29
PAGE
QAS 19 Assignment 3
5.
(
p. 143 29)
b.
Let
W =gallons of white wine
R = gallons of rose wine
F = gallons of fruit juice
+
–
+
–
(
0
+
–
(
–
+
0
Max 
1.5W 
+ 
1.0R 
2.0F 

s.t. 
W 

10000 

R 
8000 

0 .5W 
– 
0 .5R 
0.5F 
(  0  
–0.2W 
0.8R 
0.2F 

–0.3W 
0.7R 
0.3F 
0  
–0.2W 
0.2R 
0.8F 

W, R, F 0
>
>
<
p><
p><
p><
p
>
>
<
p>
Solution
1
a) Introducing slack variables, S1, S
2
, and S3 the given linear programming problem can be written in standard form as
Max
3A +
4
B +
0
S1+ 0S2+ 0S3
Subject to
1A + 2B + S1 =
8
1A + 2B + S2 = 12
2A + 1B + S3 =
16
A, B, S1, S2, S3 ≥ 0
b) The feasible region is the shaded region
O
C
D
F
G
in the following graph.
The coordinates of the feasible region and the corresponding objective function value is shown in the following Table
Extreme point
Coordinates (A, B)
Objective
function
A
B
3A + 4B
O 00
0
C
8
0
24
D
(20/3) = 6.67
(8/3) = 2.67
(92/3) = 3
0.6
7
F 25
26
G0
4 16Since the objective function is maximum at D, the point D is optimal.
Thus, the optimal solution is A = 20/3 and B = 8/3 and the optimal value of the objective function is 92/3 = 30.67.
c) At the optimal solution A = 20/3 and B = 8/3,
1A + 2B + S1 = 8 implies (20/3) + 2(8/3) + S1 = 8, that is (4/3) + S1 = 8 or S1 = 20/3.
1A + 2B + S2 = 12 implies (20/3) + 2(8/3) + S2 = 12, that is 12 + S2 = 12 or S2 = 0.
2A + 1B + S3 = 16 implies 2(20/3) + (8/3) + S3 = 16, that is 16 + S3 = 16 or S3 = 0
Thus, the values of the slack variables S1 is 20/3 and S2 and S3 are 0 at the optimal solution.
Solution 2
a)
The decision variables are
X
= Number of units purchased in the stock fund
Y
= Number of units purchased in the money market fund
Since each unit invested in the stock fund has a risk index of 8, and each unit invested in the money market fund has a risk index of 3; the total risk index is 8 X + 3Y
Thus, an objective function that will minimize the total risk index for the portfolio is
Minimize 8 X + 3 Y
Since each unit of stock fund costs $
50
and each unit of money market fund costs $
100
, the constraint corresponding to the available funds is
50 X + 100 Y ≤ 1,200,000
Since the annual income from stock fund is ($50)(10%) = $5 and the annual income from the money market fund is ($100)(4%) = $4, the constraint corresponding to the annual income is
5 X + 4 Y ≥ 60,000
Since at least $300,000 to be invested in the money market, at least ($300,000/$100) = 3,000 units must be invested in the money market. Thus, the constraint corresponding to the minimum units in money market is
Y ≥ 3,000
Finally, the nonnegativity constraints, X, Y ≥ 0.
Thus, the linear programming problem is
Min 8 X + 3 Y
Subject to
50 X + 100 Y ≤ 1,200,000
(Funds available)
5 X + 4 Y ≥ 60,000
(Annual income)
Y ≥ 3,000
(Minimum units in money market)
X, Y ≥ 0
The Excel Solver output of the linear programming problem is shown below:
Data
Results
X
Y
LHS
Slack/Surplus
Objective
83
sign
RHS
62000
Constraint 1
50
100 <1
20000
0
1200000
0
Constraint 2
5
4
>
60000
60000
0
Constraint 3
0
1
>
3000
10000
7000
ResultsX
Y
Variables
4000
10000
Objective
62000
Therefore, the optimal solution is X = 4,000 and Y = 10,000 and the minimum total risk index = 62,000.
Thus, Innis should purchase 4000 units in the stock fund and 10000 units in the money market fund to minimize the total risk index for the portfolio.
b) The annual income corresponding to the optimal investment strategy is
Annual Income = 5 X + 4 Y = 5(4000) + 4(10000)
= 20000 + 40000
= 60000
c) If the client desires to maximize the annual return, he should invest everything in the stock fund.
Solution 3
The decision variables are
X = Number of gallons of regular gasoline produced
Y = Number of gallons of premium gasoline produced
Since the proﬁt contributions are $
0.3
0 per gallon for regular gasoline and $0.50 per gallon for premium gasoline; the total proﬁt contribution is 0.30 X + 0.50 Y.
Thus, the objective function is
Maximize 0.30 X + 0.50 Y
Since each gallon of regular gasoline contains 0.3 gallons of grade A crude oil and each gallon of premium gasoline contains 0.6 gallons of grade A crude oil, the constraint corresponding to the available Grade A crude oil is
0.30 X + 0.60 Y ≤ 18,000
The constraint corresponding to the production capacity is
X + Y ≤ 50,000
The constraint corresponding to the demand for the premium gasoline is
Y ≤ 20,000
Finally, the nonnegativity constraints, X, Y ≥ 0.
Thus, the linear programming model is
Max 0.30 X + 0.50 Y
Subject to
0.30 X + 0.60 Y ≤ 18,000
(Availability of Grade A crude oil)
X + Y ≤ 50,000
(Production Capacity)
Y ≤ 20,000
(Demand for Premium)
X, Y ≥ 0
b) The Excel Solver output of the linear programming problem is shown below:
Data
Results
X
Y
LHS
Slack/Surplus
Objectivesign
RHS
0.3
<
1
800
0
0
1
1
<
50000
0
0
1
<
10000
10000
Results
X
Y
Variables10000
Objective
0.3 
0.5 
17000 

0.6 
1 80 00 

50000 

20000  
40000 

17000 
Therefore, the optimal solution is X = 40,000 and Y = 10,000 and the minimum total proﬁt contribution = $17,000.
Thus, 40,000 gallons of regular gasoline and 10,000 gallons of premium gasoline should be produced to maximize the total proﬁt contribution.
c) From the Excel solver output, the slack variables for constraints 1, 2 and 3 are respectively S1 = 0, S2 = 0, and S3 = 10000.
The slack variable for constraint 1, S1 = 0 means that all available grade A crude oil is used.
The slack variable for constraint 2, S2 = 0 means that total production capacity is used.
The slack variable for constraint 3, S3 = 10000 means that Premium gasoline production is 10,000 gallons less than the maximum demand.
d) The binding constraints are those constraints whose slack variables are zero. Therefore, Grade A crude oil availability and Production capacity are the binding constraints
Solution 4
The decision variables are
X = Time allocated to regular customer service during the twoweek period
Y = Time allocated to new customer service during the twoweek period
Since technicians require an average of 50 minutes for each regular customer and 1 hour = 60 minutes for each new customer, a technician can handle 60/50= 1.2 regular customers and 60/60 = 1 new customer in one hour, so that the total number of customers contacted during the twoweek period is 1.2 X + Y.
Thus, the objective function is
Maximize 1.2 X + Y
Since a maximum of 80 hours of technician time is available over the twoweek planning period, the constraint corresponding to the available technician time is
X + Y ≤ 80
The constraint corresponding to the cash flow requirement (revenue) is
25
X + 8 Y ≥ 800
Since the technician time spent on new customer contacts must be at leas 60% of the time spend on regular customer contacts, the constraint corresponding to this requirement is
Y ≥ 0.60 X
Or
– 0.60 X + Y ≥ 0
Finally, the nonnegativity constraints, X, Y ≥ 0.
Thus, the linear programming model is
Max 1.2 X + Y
Subject to
X + Y ≤ 80
25 X + 8 Y ≥ 800
– 0.60 X + Y ≥ 0
X, Y ≥ 0
b) The Excel Solver output of the linear programming problem is shown below:
Data
Results
X
Y
LHS
Slack/Surplus
Objectivesign
RHS
1
1
<
80
0
8
>
Constraint 31
>
0
0
0
Results
X
Y
Variables
Objective
1.2 
1 
90 

80  
25  800 
1490 
690 
0.6 

50 
30 

90 
Therefore, the optimal solution is X = 50 and Y = 30 and the maximum number of customers contacted = 90.
Thus, HTC should allocate 50 hours to regular customer service and 30 hours to new customer service to maximize the total number of customers contacted during the twoweek period.
Solution 5
a) The feasible region is CBF
b) The coordinates of the point C are obtained by solving the equations 6X – 2Y = 3 and X+Y = 3. Solving these equations we get Point C (9/8, 21/8) = (
1.125
,
2.625
)
The coordinates of the point B are obtained by solving the equations 6X – 2Y = 3 and 2X+3Y = 6. Solving these equations we get Point B(3/2, 3) = (1.5, 3).
The coordinates of the point F are obtained by solving the equations 2X+3Y = 6 and X+Y = 3. Solving these equations we get Point F(3/5, 12/5) = (0.6,
2.4
).
The coordinates of the feasible region and the corresponding objective function value is shown in the following Table
Extreme point
Objective function
C B3
F0.6
Coordinates (X, Y) 

X  Y 
3X + 6Y 
1.125  2.625 
12.375 
1.5 
13.5 

2.4 
12.6 
Since the objective function is maximum at B, the point B is optimal.
c) The optimal solution is X = 1.5 and Y = 3 and the optimal objective function value is 13.5.
d) The binding constraints are 6X – 2Y ≤ 3 and 2X + 3Y ≤ 6.
e) Introducing slack variables S1, S2, and surplus variable S3, the linear program in standard form is
Max
3X + 6Y + 0S1+ 0S2 + 0S3
Subject to
6X – 2Y + S1 = 3
2X + 3Y+ S2 = 6
X + Y – S3 = 3
X, Y, S1, S2, S3 ≥ 0
f) At the optimal solution X = 1.5 and Y =3,
6X – 2Y + S1 = 3 implies 6(1.5) – 2(3) + S1 = 3, that is 3 + S1 = 3 or S1 = 0.
2X + 3Y+ S2 = 6 implies 2(1.5) + 3(3) + S2 = 6, that is 6 + S2 = 6 or S2 = 0.
X + Y – S3 = 3 implies 1.5 + 3 – S3 = 3, that is 4.5 – S3 = 3 or S3 = 1.5
Thus, the values of the slack variables S1 and S2 at the optimal solution are both 0 and the value of the surplus variable S3 at the optimal solution is 1.5.