Transshipment Problem: Regional Distribution

Cell Color Legend

Notes User input Auto calculate Answer Location Marker

Constraint Sign

$<=$ Less than or equal to

$=$ Equals $($add apostrophe first $-\text{'}=-$ to "tell" Excel this is not an equation$)$

$>=$ Greater than or equal to

Node Annotation

Nodes are represented as Xij where i $=$ the origin, j $=$ destination

Example: the distance from node $2$ to node $3$ is represented as $"$X$23"$

Instructions:

Given the following problem statement:

$1.$ Define the variables in the model formulation steps.

$2.$ Determine and calculation intermediate values to support the model

$3.$ Construct and organize the model. Solve the model using Excel's "Solver."

$4.$ Interpret the model results and write result statements.

Variables

Problem Statement

"A company wants to minimize shipping cost from the production facilities $($Source$)$ to supply warehouses $($Destination$)$ via regional transshipment points. The production facilities produce a certain number of units $($Supply$).$ The destinations require a different number of units $($Demand$)$ based on forecasted sales.

Identify the routing pathways from, source to destination via transshipment points, to meet the demand while minimizing cost."

Figure $1$

Representation of Transshipment Network

Table $1$

Transshipment Data

From $\backslash$ To: Chicago Buffalo New York City Boston St Louis Supply

Toronto $$4\mathrm{.}00$ $$7\mathrm{.}00---800$

Detroit $$5\mathrm{.}00$ $$7\mathrm{.}00---700$

Chicago $--$ $$6\mathrm{.}00$ $$4\mathrm{.}00$ $$5\mathrm{.}00-$

Buffalo $--$ $$2\mathrm{.}00$ $$3\mathrm{.}00$ $$4\mathrm{.}00-$

Demand

Model Formulation Steps

Note: identify each of the following for this problem. Type answer into green cell

$1.$ Define the decision variables

$2.$ Define the objective function

$3.$ Define the constraints

Intermediate Calculations

Note: demonstrate one of each type of intermediate calculation

Type of calculation:

Demo:

Model

Note:

Objective Table

Decision Variables

Objective Function

Constraints Table

Cell Reference Constraint

Constraint Variables Technical Coefficients $"$LHS

$($units used$)"$ Constraint Sign $"$RHS

$($constraint$)"$ Slack $(-)$ or Surplus $(+)$

Result Statements

Note: Write result statements for the objective and constraints

Objective

Constraints

Interpreting the Results

Note: Please answer the following questions. Type the answer in the green box.

Questions Type answer in the box

$1.$ Briefly describe a qualitative reason that supports sending more units to one transshipment point versus another.

End of Analysis