Description
Download the "Excel Investment Example" file from Canvas and go to the investment example tab. How much money can you take out of the fund in Year 3 without changing the allocation of fund types?
2. Using the worksheet containing the HU medical supply practice file titled "Excel Transshipment Example for Lecture 6", adding 50 more trucks to which western location (ROK, MAR, or PIT) would raise total costs the most? (we would use all of the available cars from the eastern location - NYC & PHL)
3. From Assignment 5's linear programming question. Solve the linear program model using solver. The Assignment 5 question is copied below:
A 24-hour pharmacy/drug store needs to plan for staffing but wants to cut costs to a minimum. The employees work 8-hour shifts. The pharmacy/drug store needs 12 staff between 8 am and noon, 10 staff between noon and 4 pm, 14 staff between 4 pm and 8 pm, 10 staff between 8 pm and 12 am, and only 6 staff between 12 am and 8 am. How should the drug store employees be scheduled? (Assumption: Schedule of staff is only on 4-hour intervals of midnight, 4am, 8am, noon, 4pm, and 8pm.)
Linear Program to use for your solver solution:
Decision variables represent different 8-hours shifts starting at 4am (x6), 8am (x1), noon(x2), 4pm(x3), 8pm(x4), and midnight (x5)Min Z = x1 + x2 + x3 + x4+ x5+ x6
Subject to (constraints)
x1 + x6 >= 12
x1 + x4 >= 10
X4 + x2 >= 14
X2 + x5 >= 10
x5 + x3 >= 6
x3 + x6 >= 6
All variables >= 0

Explanation & Answer

Attached.
Discussion
Student Name
Course
Date
Question 1
A
B
C
$0.00
$1,000,000.00
$1,100,000.00
$0.00
$0.00
$0.00
$610,000.00
1.1
0
0.6
0
0
0
1.03
$1,288,300.00
1
1
0
1
0
0
0
$1,000,000.00 =
$1,000,000.00
Year 1 End
0.02
0
0
1.03
-1
0
0
$0.00 =
$0.00
Year 2 End
0.02
1.1
-1
0
1.03
-1
0
$0.00 =
$0.00
Year 3 End
0.02
0
0.6
0
0
1.03
-1
$50,000.00 =
$50,000.00
Maximize
M1
M2
M3
M4
Subject to
LHS
Now
From Year 3 we have:
0.02A+0.6C+1.03M3=M4+50,0000.
where:
•
•
•
•
•
A represents a certain fund type.
C represents another fund type.
M3 represents the available funds in Year 3.
M4 represents the available funds in Year 4.
$50,000 is an additional allocation or spending amount.
From the Excel sheet, the values are:
•
•
•
A= 0
C=1,100,000
M3=0
Integrating these into the equation:
0.02(0) +0.6(1,100,000) +1.03(0) =M4+50,0000.
Simplifying each term:
0+660,000+0 = M4+50,0000
660,000 = M4+50,000
Solving for M4
Rearranging the equation to solve for M4:
M4 = 660,000−50,000
= final cash
RHS
M4 = 610,000
This means that $660,000 can be withdrawn from the fund in year 3 without affecting the
fund’s overall allocation.
For the next year (year 4):
$50,000 can be used for a cruise and $610,000 remains available for investment.
Question 2
NYC-HAR
Minimize
NYC-PIT
NYC-RMD PHL-HAR PHL-RMD HAR-ROK HAR-MAR HAR-PIT
RMD-MAR
RMD-PIT
150
0
0
0
300
100
50
0
100
200
68
264
129
109
118
212
214
203
162
135
Subject to
NYC
1
1
1
PHL
HAR
1
1
1
RMD
1
ROK
-1
-1
-1
1
-1
-1
1
MAR
PIT
1
1
1
1
1
1
$120,700.00
= total cost
LHS
RHS
150
=
150
200
>=
200
The initial transshipment cost is $120,700.00
a. To determine the cost when 50 more trucks are added to ROK, then
• NYC-HAR: 200 trucks
• PHL-RMD: 300 trucks
• HAR-ROK: 150 trucks
• HAR-MAR: 50 trucks
• RMD-MAR: 100 trucks
• RMD-PIT: 200 trucks
Multiply each flow by its corresponding cost:
• NYC-HAR: 200 trucks * $68/truck = $13,600
• PHL-RMD: 300 trucks * $118/truck = $35,400
• HAR-ROK: 150 trucks * $212/truck = $31,800
• HAR-MAR: 50 trucks $214/truck = $10,700
• RMD-MAR: 100 trucks * $162/truck = $16,200
• RMD-PIT: 200 trucks * $135/truck = $27,000
$13,600 + $35,400 + $31,800 + $10,700 + $16,200 + $27,000 = $134,700
NYC- NYC NYC PHL PHL HAR HAR HAR RMD- RM
HAR -PIT
- -PIT MAR
DRMD HAR RMD ROK MAR
PIT
200
0
0
0 300 150
50
0
100 200
Minimize
68
264
129
109
118
212
214
203
162
135
$134,700.00
= total cost
LHS
RHS
Subject to
NYC
1
1
1
PHL
1
HAR
1
1
1
RMD
-1
1
-1
-1
1
ROK
-1
-1
1
MAR
1
PIT
1
1
1
1
b. To determine the cost if we add 50 more trucks to MAR, then:
• NYC-HAR: 200 trucks
• PHL-RMD: 300 trucks
• HAR-ROK: 100 trucks
• HAR-MAR: 100 trucks
• RMD-MAR: 100 trucks
• RMD-PIT: 200 trucks
Multiply each flow by its corresponding cost:
• NYC-HAR: 200 trucks * $68/truck = $13,600
• PHL-RMD: 300 trucks * $118/truck = $35,400
• HAR-ROK: 1...
