(Answered)-***PLEASE ANSWER IN EXCEL/USING SOLVER*** ***PLEASE ANSWER IN - (2025 Updated Original AI-Free Solution
Question
***PLEASE ANSWER IN EXCEL/USING?SOLVER***
***PLEASE ANSWER IN EXCEL/USING?SOLVER***
***PLEASE ANSWER IN EXCEL/USING?SOLVER***
1. A transshipment model has 2 sources, 5 intermediate nodes and 20 destinations. The unit shipping costs from the sources to the intermediate nodes and from these to the destinations are provided in the two tables by Sheet1a of the Assignment2.xlsx file downloadable from Blackboard. The two tables also provide the supplies and demands for each source and destination node. Please,
(i) Find the solution that minimizes the total shipping cost.
(ii) Explain why one of the two sources shadow price is zero and the other one is not (Hint: you may want to run Solver twice again after changing each of the two supply parameters). Suppose now that source 2 has a 12,000 supply.
(iii) Find the new solution and explain what happened to the new 2,000 excess supply. Starting from the first, balanced problem, add to the network a second product type whose unit shipping costs, supplies and demands are provided by Sheet1b of Assignment2.xlsx.
(IV)?Write a mathematical?model?that minimizes the total shipping cost, given that every route connecting an intermediate node and a destination has a combined capacity of 2500 units and that at most 14,000 combined units must transit through intermediate node 3."
You do not have to write all the variables and constraints, a few representative of each type and a brief description are enough. So, no Excel needed here.?
2. (v) find how many cars per minute can flow in and out of Manhattan in the extremely simplified representation of its southbound traffic provided by Sheet2 in Assignment2.xlsx, where the numbers next to the routes represent the maximum capacity, in vehicles per minute, each road can tolerate.
(vi) A bike lane takes away about 10% of a road capacity. Where do you think the Department of Transportation should consider creating one? Why?
(vii) What road should the DOT consider to expand its capacity? Why?
3. A taxi app matches customers? ride requests to drivers by minimizing the response time, and it does so by choosing for each new request among all available drivers the one within the shortest distance to the pick-up location. However, in a big city with a high frequency of requests, such greedy algorithm is not always optimal at minimizing the total response time of all assignments. Therefore, the app has developed a new algorithm that collects all the customer requests that arrive in a minute, compares them with all the available drivers at that time by distance using current GPS data, and makes an optimal one-to-one assignment that minimizes the total distance traveled by drivers to the requested pick-up locations. Sheet 3 of Assignment2.xlsx shows all the requests arrived in a minute, from first to last, and the distance to all the drivers available in the area. Please,
(viii) find the assignment and total distance that drivers would have had to travel to reach their assigned pick-up location if the greedy algorithm was used.
Also, (ix) find the total distance traveled with the new algorithm and compare the two results. What do you notice and why? Finally, some customers also express a preference for a particular car type or driver?s rating, and if the app is able to make that assignment it is like cutting the total distance by 1 mile in the objective function.
Please, (x) find the new optimal assignment.
***PLEASE ANSWER IN EXCEL/USING?SOLVER***
***PLEASE ANSWER IN EXCEL/USING?SOLVER***
***PLEASE ANSWER IN EXCEL/USING?SOLVER***
1
2
A
5.20
3.10
B
4.10
5.70
C
4.30
2.50
supply
20000
10000
A
B
C
demand
1
2.10
2.00
1.30
2500
2
2.50
1.30
1.70
2500
3
1.70
0.70
1.50
2000
4
1.40
0.90
1.30
2000
Product
5
1.30
0.80
1.10
2000
6
1.00
1.40
1.20
2000
7
0.70
1.90
1.30
2000
8
1.90
2.80
1.60
2000
9
1.80
3.70
1.90
1500
10
0.80
3.50
1.80
1500
Product 1
11
0.90
2.70
1.30
1500
12
1.40
2.10
0.90
1000
13
1.60
1.60
1.90
1000
14
2.60
0.60
1.50
1000
15
2.30
0.70
2.10
1000
16
3.10
2.00
2.30
1000
17
2.10
2.50
1.70
1000
18
3.90
2.10
1.80
1000
19
4.10
1.20
1.70
1000
20
2.70
1.40
1.50
500
1
2
A
6.00
3.00
B
4.00
5.00
C
3.00
4.00
supply
3000
2000
A
B
C
demand
1
2.00
2.00
1.50
200
2
2.00
1.00
2.00
200
3
2.00
1.00
1.00
200
4
2.00
1.00
2.00
300
5
1.00
1.00
2.50
300
6
1.00
2.00
2.00
300
7
1.00
2.00
2.00
300
8
1.50
2.00
2.00
300
9
1.50
3.00
2.00
300
Product 2
10
1.50
3.00
2.00
300
11
1.00
3.00
2.00
300
12
1.00
3.00
1.00
300
13
2.00
3.00
1.50
300
14
2.00
1.50
1.00
300
15
3.00
1.00
2.00
300
16
3.00
1.00
1.00
200
17
2.00
1.00
1.00
200
18
4.00
1.00
1.00
200
19
3.00
1.00
1.00
100
20
2.00
2.00
2.00
100
ID
customer requests
preference
1
2 SUV
3
4 SUV, high rating
5
6 black
7 black, high rating
8
9
10 high rating
ID
drivers available
type
1 SUV, high rating
2 SUV high rating
3 SUV
4 SUV
5 SUV
6 black, high rating
7 black
8 black
9 high rating
10
11
12
13
distance
1
2
3
4
5
6
7
8
9
10
1
2.1
2.0
0.3
0.7
1.0
1.1
3.0
3.5
4.0
0.8
2
1.1
1.6
1.0
1.7
0.8
0.7
2.0
2.4
2.7
1.4
3
1.0
0.5
0.3
0.8
1.4
1.7
2.5
2.0
2.3
2.0
4
1.8
2.4
2.1
1.6
1.9
2.4
1.4
1.1
0.5
0.0
5
3.0
4.1
4.7
3.0
1.6
0.3
0.7
1.0
1.1
1.2
6
2.4
2.0
1.5
1.2
0.2
1.5
1.7
2.4
2.0
1.6
7
0.4
0.1
1.1
1.3
1.2
2.2
2.5
2.4
1.7
2.1
8
0.1
0.4
1.1
1.6
1.0
1.7
1.9
2.2
1.5
1.6
9
0.2
0.6
0.1
0.4
1.5
1.9
2.2
2.7
4.1
1.6
10
4.1
3.5
2.8
2.0
1.0
1.0
0.5
0.2
0.1
2.0
11
3.9
4.3
2.5
2.1
1.5
1.1
1.4
1.0
0.6
0.2
12
1.0
1.0
1.5
1.4
1.2
1.7
3.0
2.4
2.0
1.6
13
2.0
2.3
1.3
1.0
1.2
1.6
0.2
0.4
0.8
1.1