Using the worksheet containing the HU medical supply practice

User Generated

ororyng

Health Medical

Intr to Mod Optimization Dec Mak

Harrisburg University of Science and Technology

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

User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

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...

Related Tags