(Answered)-***PLEASE ANSWER IN EXCEL/USING SOLVER*** ***PLEASE ANSWER IN - (2025 Updated Original AI-Free Solution

Discipline:

Type of Paper:

Academic Level: Undergrad. (yrs 3-4)

Paper Format: APA

Pages: 5 Words: 1375

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