Assignment 2
• Please accumulate your Excel sheets in one Excel file and submit it. Do not submit multiple Excel files or zipped version of them.
1. A company can produce four kinds of drugs, A,B and C. The following table shows the required labors and drugs to make each kind of drugs. The company has the total of 10,000 hours of monthly labor. Make an Excel file that can find the optimal production plan that maximizes the monthly revenue. (15 points)
|
A
B
C
Labor (hour)
|
1
|
2
2
|
1
1
3
|
|
Price ($)
|
30
|
150
|
300
|
2. Use the network structure and unit cost information given in ‘2024-1 Final 3’ problem. Change the problem into ‘navigation’ problem and assume that you are going from Node 1 to Node 8.
(a) Find the route that incurs the minimum cost. (10 points)
(b) (Continued from (a)) Assume that at least one of Node 2 and 4 should be visited before you arrive at Node 8. Find the route that incurs the minimum cost. (5 points)
(c) (Continued from (a)) Assume that at most one of Node 3 and 5 can be visited before you arrive at Node 8. Find the route that incurs the minimum cost. (5 points)
3. A company produces products at three plants. These products can be shipped directly to the company’s two markets or it can first be shipped to the company’s two warehouses and then to the markets. Nodes 1, 2 and 3 represent the plants, nodes 4, 5 and 6 represent the warehouses, and nodes 7 and 8 represent the markets. The production capacity of each plant is 300 (Node 1), 250 (Node 2) and 350 (Node 3). The demand of each market is 400 (Node 7) and 420 (Node 8). The table shows the shipping cost per unit for each arc.
|
1
1
1
2
2
3
3
3
4
4
5
5
5
6
6
7
7
8
8
8
|
2
3
4
1
6
1
6
7
3
7
2
6
8
3
8
3
8
5
6
7
|
2
7
10
3
6
5
5
1
6
8
5
9
10
9
3
7
2
7
6
8
|
We also assume that at most 300 units of the product can be shipped between any two nodes. This is the common arc capacity. Assume that the demand in market 7 and 8 should be satisfied from the supply from plant 1, 2 and 3. Also assume all the production cost and the market prices across regions are the same and the company only needs to concern the shipping cost.
(a) Determine a logistics plan to minimize the total shipping cost. (10 points)
(b) (Continued from (a)) Assume that shrinkage occurs at Node 4. The shrinkage rate is 95%. Determine a logistics plan to minimize the total shipping cost. (5 points)
(c) (Continued from (a)) Assume that shrinkage occurs on all the routes. The shrinkage rate is 98%. Hence, for example, if 100 units are shipped through 1 → 4 → 7 → 8, the units arrived at Node 8 will be 100 × (0.99)3 . Assume that the shipping cost is applied to average units before the shrinkage. For example, if 100 units are shipped from 1 to 2, the shipping cost of 100 × 2, not 100 × 2 × 0.99 will be occurred. Determine a logistics plan to minimize the total shipping cost. (5 points)
4. An airline company is planning to run several daily commuter flights to and from Chicago, Dallas and Atlanta. Each potential flight has an estimated (Revenue - Variable cost) as in the Table below. The company owns three airplanes. There is afixed cost of $5,000 per plane per day that flies any flights. However, a plane that is not used does not incur this fixed cost. Any plane that arrives in a city after its last flight of the day has two options. It can sit overnight in that city, or, at a cost of $3,000, it can be flown empty to another city overnight. The company’s objective is to maximize its profit per day. Develop a network model for scheduling the airline’s flights, given its available aircraft, to maximize net profit from the flights. (20 points)
|
Origin
|
Destination
|
Depart
|
Arrive
|
Revenue - variable cost
|
|
Chicago
Chicago
Chicago
Chicago
Dallas
Dallas
Dallas
Dallas
Atlanta
Atlanta
Atlanta
Atlanta
|
Dallas
Dallas
Atlanta
Atlanta
Chicago
Chicago
Atlanta
Atlanta
Chicago
Chicago
Dallas
Dallas
|
6
12
9
15
9
18
9
15
9
6
12
15
|
9
15
12
18
12
21
12
18
12
9
15
18
|
16
13
25
12
8
15
20
21
19
21
21
10
|