Place an Order

1.Paper details

Academic level

Type of paper

Select...

Discipline

Select or type...

Topic

Paper instructions

***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*** EXAMPLE IS ATTACHEDMicrosoft Excel 15.0 Answer Report
Worksheet: [transportation.xlsx]Sheet1
Report Created: 4/16/2016 12:10:09 PM
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.016 Seconds.
Iterations: 3 Subproblems: 0
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative

Objective Cell (Min)
Cell
Name
$H$2 obj fun

Original Value
$1,940.00

Final Value
$1,940.00

Variable Cells
Cell
Name
$D$9 S destination nodes
$E$9 S
$F$9 S

Original Value
0
60
40

Final Value
Integer
10000000000 Contin
20 Contin
5 Contin

Constraints
Cell
Name
$C$11 tot demand
$C$12 constraints
$C$13 constraints
$C$14 constraints
$C$15 constraints
$D$9 S destination nodes
$E$9 S
$F$9 S

Cell Value
constraints

Formula
$C$11&amp;lt;=$B$11
$C$12&amp;lt;=$B$12
$C$13&amp;lt;=$B$13
$C$14&amp;lt;=$B$14
$C$15&amp;lt;=$B$15
10000000000 $D$9&amp;lt;=1000
20 $E$9&amp;lt;=1000
5 $F$9&amp;lt;=1000

Status
Not Binding
Not Binding
Binding
Not Binding
Binding
Not Binding
Not Binding
Not Binding

Slack
50
50
0
50
0
800
800
1000

Microsoft Excel 15.0 Sensitivity Report
Worksheet: [transportation.xlsx]Sheet1
Report Created: 4/16/2016 12:10:09 PM

Variable Cells
Cell
Name
$D$9 S destination nodes
$E$9 S
$F$9 S

Final
Reduced Objective Allowable Allowable
Value
Cost
Coefficient Increase Decrease
10000000000
0
75
25
5
20
0
50
25
12.5
5
-2.5
35
2.5 1.000E+030

Constraints
Cell
$C$11
$C$12
$C$13
$C$14
$C$15

Name
tot demand
constraints
constraints
constraints
constraints

Final
Value
constraints

Shadow Constraint Allowable Allowable
Price
R.H. Side
Increase Decrease
0
450 1.000E+030
50
0
250 1.000E+030
50
12.5
800
100
100
0
450 1.000E+030
50
25
600
50
200

2
3
1
60
40
2
10
0
S
0
0
$/unit
destination nodes
parameters
1
2
3
1
10
15
12
2
8
16
20
S
10000000000
20
5
tot demand
80
70
40
SC1
100
?
SC2
80
?
SCS
0
?
DC1
50
?
DC2
70
?
DC3
40
?
DCD
20
?

constraints

origin
nodes

decision vars

1
0
50
0

D
0
20
0

obj fun
$1,940.00

D
0
0
1000
0
100
80
10
80
70
40
0

tot supply
100
80
10
0
sup/dem
balance

In the transportation problem represented by the network model on the top right of this page, a total of 180 units
source/origin nodes on the left, and a total of 180 units worth of demand is required to be delivered to the three d
therefore zero, and the problem is said to be balanced. The decision to be made is how much to ship on each of th
transportation cost, so six decision variables are needed, one for each route. Variables are named by the source an
number of units to be shipped from source 1 to destination 3. The total cost comes from the unit transportation co
arrow, and the objective function is the sumproduct of the six variables and these six unit costs.
There are two sets of constraints. The supply constraints guarantee that the total outflow from each source does n
demand constraints guarantee that the total inflow to each destination is no less than its demand, or else some of
words, since the totall supply and demand are balanced, we want to make sure that no supplier ships more than th
requested. Equal signs in the constraints would work as well, but this unequal signs allow the problem to be adapte
balanced.
If total supply and demand are unbalanced, some of constraints cannot be met and the model becomes infeasible.
red table above where the total demand is 190 and the total supply is 180, here not all of the demand constraints c
client will not receive all the units they have requested. The way to make the model feasible again is to add a dum
routes that connect this to all the destination nodes. The 10 unit dummy supply is created to meet the extra deman
units that will be delivered to the clients represents a unit that we were not able to delivered because in short of su
because they may cause a loss of revenue, a late fee, a contract violation, or a loss of goodwill and future sales with
transportation cost in our model. In the case above, since client 1 is a very important customer and we do not wan
transportation cost from the dummy source node to client 1 to a very large number to make sure that _?_?1 rem
If the problem is unbalanced on the supply side, we add a dummy destination node D that requests all the excess s
costs for each of the new arches created to the new node can be set to zero, because the &amp;quot;non-units&amp;quot; shipped to th
the source nodes as leftover inventory with no transportation cost (a unit inventory holding cost may be added inst
source is not needed, but it can be left in the model with a supply of 0.
In the optimal solution, there are always some decision variables with a zero value. This means that no units are sh
In the Sensitivity Report generated, we can look at their reduced costs of the routes and see by how much we shou
competitive again. Shadow prices instead, tell us for example how much we would save on transportation costs if w

page, a total of 180 units worth of supply is available to be shipped from the two
e delivered to the three destination nodes on the right. The supply/demand balance is
much to ship on each of the six possible routes in order to minimize the total
e named by the source and destination number code, so for example x 13 represents the
the unit transportation costs that are given in the network model on top of each arch&#039;s
costs.
from each source does not exceed its supply. E.g., _21+_22
+
+_23?80. The
demand, or else some of it will be left unsatisfied. E.g., _13+_23?40
+
. In other
upplier ships more than they have and all clients are delivered at least what they
the problem to be adapted to the case where total demand and supply are not

model becomes infeasible. An example is when the parameters are those given in the
the demand constraints can be satisfied because there is not enough supply and some
ble again is to add a dummy source node S, with a dummy supply of 10 units, and new
d to meet the extra demand that we are not able to satisfy, and any of this dummy
ered because in short of supply. These &amp;quot;non-deliveries&amp;quot; have a cost for suppliers
dwill and future sales with the customer, which may be estimated and used as a unit
omer and we do not want to miss on any delivery to them, we artificially set the unit
ake sure that __1 remains 0 in the optimal solution that Solver finds.
at requests all the excess supply that will not be delivered. Here, the unit transportation
&amp;quot;non-units&amp;quot; shipped to the dummy destination are not actually shipped, but remain in
ng cost may be added instead). Since there is no excess demand any more, the dummy

means that no units are shipped on those routes because there are to expensive to run.
ee by how much we should decrease their unit transportation costs to make them
n transportation costs if we had one less unit of demand from each of our clients.

1
2
A
B
$/unit
parameters
1
2
A
B
tot demand

constraints

origin nodes

decision vars

A
0
0

A
10
8
0
SC1
SC2
ICA
ICB
DC1
DC2
DC3

B
0
0

B
12
20
0
0
0
0
0
0
0
0

0
0
0
0
destination nodes
1
2
4
2
5
3
70
70
?
100
?
80
=
0
=
0
?
70
?
70
?
40

obj fun
$0.00

0
0

des
3
1
3
40

tot supply
100
80
0
0
0
sup/dem
balance

The transshipment model allows for a more realistic
representation of logistic networks, since intermediate nodes
where units are not supplied nor demanded, but simply flow in
and out with a net balance of zero over a certain period of time,
can now be represented by nodes such as A and B in the example
on the left. Even if source nodes cannot ship directly to
destinations, but have to ship to intermediate nodes first, there
are a few more arches/routes to be created and therefore more
variables, unit cost parameters, and shipping costs in the
objective function. Supply constraints and demand constraints
are still there, but have to be updated with the new routes in
existence. Intermediate nodes have no supply or demand, but the
zero net balance has to be ensured by the creation of a new set
of intermediate constraints that makes the total inflow equal to
the total outflow. For node A, for example, the constraint to be
added is ?
1_21?
_+&amp;quot;
_ _&amp;quot; ? _2?=&amp;quot; &amp;quot; ?_?1+&amp;quot; &amp;quot; ?_?2+&amp;quot; &amp;quot; ?_?3,
which becomes 1
_1+&amp;quot;
2?_?_?_
&amp;quot; _2?_1?_2?_3 in
standard form. In case the problem is unbalanced, a dummy
node has to be created just like in the trasportation model. Other
constraints for special cases can always be added. For example if
route B3 allows for a maximum of 20 units to be shipped, the
constraint would be _? _?3?30. If some route is not used, the
corresponding decision variable can be eliminated. If a new route
connects directly source 2 to destination 1, the new decision
variable _21 and corresponding unit cost may be added. If at
least 10 units must transit through node B, the constraint
__1+&amp;quot;
__
&amp;quot; _2+&amp;quot; &amp;quot; _3?10 or the constraint 1
_1+&amp;quot; &amp;quot;
2
_2?10
may be added. Both would be reduntdant. Please run
Solver and see if adding any of this two last constraints would
bind the objective function. Also, try to give a managerial
meaning to the source and destination shadow prices and to the
ten reduced costs.

e nodes
ly flow in
d of time,
he example
o
rst, there
ore more
he
nstraints
utes in
and, but the
a new set
w equal to
int to be
&amp;quot; _3,
3 in
ummy
odel. Other
example if
ed, the
sed, the
new route
ecision
ded. If at
raint
_1+&amp;quot; &amp;quot;
Please run
s would
rial
and to the

Additional materials

orDrop files here

Drop files here

Paper format

Deadline

We'll send you the order for review by Mar 17, 2025 at 3:11 PM.

Pages

1,375 words

Spacing

Sources to be cited

Charts

PowerPoint slides

Writer category

Additional services

3 samples of works previously completed by your writer

Review 3 samples of work previously completed by the writer assigned to your order.

Get your paper checked by an expert proofreader: no grammatical and spelling mistakes, a perfect match with your requirements, and correspondence with the chosen academic level.

Personal 24/7 VIP manager responsible for tracking your order until final delivery, alongside personalized communication via call and text.

All papers are checked for plagiarism but if you'd like an official report alongside your paper please choose this option.

We will prepare a draft outlining structure and key points to be discussed in your paper and send send it before the final paper is complete.

Selection of related articles and books, cited in your paper

Get excerpts of the articles and e-books or the links to these excerpts cited in the paper.

Part-by-part delivery of your paper

Get your paper delivered step by step. For more information, please check our Progressive Delivery web page.

Available for orders with a deadline of 5 days and longer, and with the value of $200 and more.

Coupon code

2.Account

5 pages × $22.00

$110.00