Management Science for Operations

P a g e 1 | 24
LGMT 525 Management Science for Operations
Transportation & Network Analysis Models
Assignment 1
Complete using Microsoft Office Excel Office 365
P a g e 2 | 24
Assignment Introduction & Overview
The primary focus of this assignment is the introduction of the following core concepts:
Facility location analysis
Transshipment
Maximal flow
Shortest route
The assignment consists of a set of tasks to learn and demonstrate understanding of how to implement and utilize the
concepts.
Concept and Task Organization
The assignment is designed to introduce you to a new concept and progress to basic understanding and application. The
iterative structure of the assignment facilitates the progression. In addition, the assignment includes the creation and
presentation of analysis products suitable for C-suite presentations. The best analysis in the world is for not if the analyst
cannot communicate an analysis and recommendation in a clear, concise, and professional manner.
You are expected to review each task’s elements to understand the requirements. If you are uncertain of the
requirements contact your instructor for clarification.
The tasks are presented in a detailed format. The Detailed form is akin to a cookbook with steps demonstrating how to
conduct the tasks using Excel. The task elements constitute a complete description of the requirements. Instructions for
how to do something will only be presented once. Subsequent requests to perform a similar task includes an implicit
statement to utilize all previous instructions and follow the requirements specified when originally taught.
Please note, the example data, charts, and products within the instructions may not exactly match your results in
numeric value or column headers.
Assignment Files & How to Submit Work
To complete the assignment, download a copy of the file named “M05_LGMT525_Assignment.xlsx” and store the file on
your computer. The file link is located on the assignment page with the Module section in Canvas. Upload your
completed file via Canvas. The naming convention for submitted files is as follows,
LastNameFirstInitial_OriginalFileName. For example: “GibsonJ_M05_LGMT525_Assignment.xlsx”
Grading
Adherence to the course requirements and instructions is important to ensure you earn the highest grade. Each task
element within the Summary Form is a graded specification; not following the exact specification of a task will result in a
point(s) deduction. More generally, not following the instructions contained in the course or assignment instructions will
result in a point(s) deduction. When specified, the use of Excel formulas (standard or custom) is required.
P a g e 3 | 24
Facility Location Analysis: Introduction
This portion of the assignment will utilize the following worksheets within the module assignment workbook.
Task 1_FacLoc Birmingham
Task 1
The following are instructions for conducting a facility analysis in Excel
This section will introduce how to construct a facility analysis. The section reviews the model formulation steps,
determination of intermediate calculations, the construction of a Data and Solver table, and how to write results
statements.
Note: A facility analysis can only evaluate one proposed facility per analysis. To evaluate two or more proposed facilities,
a facility location analysis must be completed for each proposed location. The results of each analysis are compared to
determine the recommended proposed location.
1. Open the assignment Excel workbook to the worksheet “Task 1_FacLoc Birmingham”
2. Read the “Instructions.” The instructions provide you an overview of the components required in the task.
3. Review the Problem Statement and Table provided in the “Variables” section.
4. Complete the following steps within the LP Model Formulation Steps.
The information provided in the problem statement and table(s) is used to define the decision variables, objective function,
and constraints. In the following steps, we will define this information and type the answers into the green boxes using one
variable or equation per row.
Model Formulation Steps
a. Define the decision variables
i. The decision variables represent the known and unknown information needed to construction the
location analysis model. For this problem, the following DVs apply:
1. Proposed location
2. Number of units shipped from production plant to warehouse, Xij
3. Cost of one unit from source i to destination j
a. Production cost + shipping cost from production plant i to destination j
P a g e 4 | 24
b. Define the objective function
i. The objective function is the function we want to maximize or minimize. In this case, we want to minimize
production and shipping costs.
1. The objective function decision variables are:
a. The production and shipping costs
b. The number of units shipped from a production plant to a warehouse
2. Use Excel’s sumproduct function to solve.
c. Define the constraints
i. The function constraints are limits place on the value of a decision variable. These limits define the
boundaries for the objective function.
1. Supply at source i, each production plant
2. Demand at destination j, each warehouse
Presentation of Results for the Model Formulation Steps
5. Complete the following steps within the Intermediate Calculations.
a. The data used in modeling is often provided by a previous analysis or supporting calculations. Identify the scenario
variables and provided data to determine if intermediate calculations are required for the model.
b. Provide a type of calculation description and demonstration for each type of calculation required to construct the
model.
i. Type of calculation – written description of derived variable how the original variables and data were
modified.
ii. Demonstrate a calculation for each type of calculation.
Presentation of Intermediate Calculations
P a g e 5 | 24
Information Populating the Data Table
6. Populate the Data Table within the Model section.
a. Specify the type of data in the table: cost, cij
b. Destination title: warehouse.
c. Names for each destination.
d. Source title: Production Plant.
e. Names for each source.
f. Source supply : obtain from the source constraints.
g. Destination demand: obtain from the destination constraints.
h. The source-destination cost matrix: values obtained from decision variables and intermediate calculations.
Completed Data Table
7. Construct the Solver Table within the Model section
a. Specify the type of data in the table: shipments, Xij
b. Destination title and destination names.
c. Source title and source names.
d. Sum Source supply.
e. Sum the shipments sent from each source, production plant (row).
i. Use Excel sum function to sum the shipments sent from each source (sum each row).
ii. The sum functions must be in each supply cell for Solver to work.
iii. The initial values are equal to 0 because the shipment matrix is empty.
f. Sum the shipments received at each destination, warehouse (column).
i. Use Excel sum function to sum the shipments received at each destination (sum each column).
ii. The sum functions must be in each demand cell for Solver to work.
iii. The initial values are equal to 0 because the shipment matrix is empty.
g. The source to destination shipment matrix is left blank. This section will be populated by Excel Solver.
h. Total Cost: use Excel sumproduct function using the cost and shipment matrices.
P a g e 6 | 24
Completed Solver Table
Using Excel Solver to Find the Objective Value
We will use Excel’s Solver to find the objective value.
8. Open the Solver Parameters window
a. Set Objective: Total Cost
b. To: Minimization
c. By Changing Variable Cells: the cells of the shipment matrix.
d. Subject to the Constraints:
i. The solver table demand cells >= the Data table demand cells
ii. The solver table supply cells <= the Data table supply cells
e. Check the box for “Make unconstrained variables non-negative.”
f. Select Solve
The Solver Table Results
Result Statements
The following are examples of how to write result statements for the objective function, from the source (production plant) point of
view, and from the destination (warehouse) point of view. The goal of each statement is a concise and information rich sentence.
We are reporting facts, not writing Shakespearian sonnets.
9. Objective function:
a. There is only one statement for the objective function.
b. Example: The current warehouse demand for 46,000 units is met by current and proposed production plants at a
total cost of $3,741,000.
10. Source (production plant) point of view:
a. There should be one statement for each source.
b. Example: The Cincinnati plant ships 10,000 units to Detroit, 1,000 units to New York, and 4,000 units to Los Angeles
for a total of 15,000 units.
c. Complete the (3) remaining source statements.
11. Destination (Warehouse) point of view:
a. There should be one statement for destination.
b. Example: The Detroit warehouse receives their total demand of 10,000 units from Cincinnati.
c. Complete the (3) remaining destination statements.
P a g e 7 | 24
Interpreting the Results
In this section, we combine the factual result statements with qualitative knowledge of the problem and knowledge from your
professional experience.
12. Answer the question(s) in this section.
a. Answers should be typed into the respective green highlighted boxes. The wrap text and increasing the row height
can be used to ensure the entire answer is displayed.
13. Task 1 is complete.
P a g e 8 | 24
Facility Location Analysis: Seattle
This portion of the assignment will utilize the following worksheets within the module assignment workbook.
Task 2_FacLoc Seattle
Task 2
The following are instructions for conducting a facility analysis in Excel
In this section, we will practice conducting a facility location analysis. The problem from Task 1 continues with an
examination of the Seattle location.
1. Open the assignment Excel workbook to the worksheet “Task 2_FacLoc Seattle”
2. Read the “Instructions.” The instructions provide you an overview of the components required in the task.
3. Review the Problem Statement and Table provided in the “Variables” section.
4. Hint: the number of green answer cells is the exact number needed.
5. Complete the LP Model Formulation Steps.
6. Complete the following steps within the Intermediate Calculations.
7. Populate the Data Table within the Model section.
8. Construct the Solver Table within the Model section
9. Use Excel Solver to complete the Solver Table.
10. Complete the Result Statements
11. Answer the question(s) in this section.
a. Answers should be typed into the respective green highlighted boxes. The wrap text and increasing the row height
can be used to ensure the entire answer is displayed.
12. Task 2 is complete.
P a g e 9 | 24
Transshipment: Regional Distribution
This portion of the assignment will utilize the following worksheets within the module assignment workbook.
Task 3_TS Regional
Task 3
The following are instructions for conducting a transshipment analysis in Excel
This section will introduce how to construct a transshipment point analysis model.
1. Open the assignment Excel workbook to the worksheet “Task 3_TS Regional”
2. Complete the following steps within the LP Model Formulation Steps.
a. Hint: the number of green answer cells is the exact number needed.
b. Define the decision variables
i. The decision variables are the number of units shipped between node pairings, Xij. For example:
ii. X13 = the number of units shipped from Toronto (node 1) to Chicago (node 3).
1. Complete for all 10 nodes.
iii. C13 = $4 – the cost to ship one unit from Toronto (node 1) to Chicago (node 3).
1. Complete for all 10 nodes.
c. Define the objective function
i. The objective is to minimize cost
ii. The number of units and cost associated with shipping between node pairings defines the objective
function.
iii. For example: Min Cost = C13*X13 + C14*14 + ….. + C47*X47
d. Define the constraints
i. The transshipment problem has three broad categories of constraints:
ii. Supply – how many units are available for shipment at the source. For example:
1. X13 + X14 <= 800
iii. Flow continuity – the number of units flowing into a transshipment point must equal the number flowing
out. For example:
1. X14 + X24 – X45 – X46 – X47 = 0
a. Hint: helps to reference the Transshipment Network figure when constructing these
constraint equations.
iv. Demand – the number of units required at the destination.
1. X35 + X45 = 450
P a g e 10 | 24
Model Formulation Steps
3. Complete the following steps within the Intermediate Calculations.
From this point forward, the solving the transshipment problem is the same as a linear programming problem. Hint: this would be a
good point to review the LP instructions.
4. Construct the objective table.
a. Hint: the exact number of color-coded cells is provided
5. Construct the constraints table.
a. Hint: the exact number of color-coded cells is provided
6. Input the Solver Parameters window information
7. Interpret the results using the Objective and Constraints tables to answer the questions.
a. Answers should be typed into the respective green highlighted boxes. The wrap text and increasing the row height
can be used to ensure the entire answer is displayed.
8. Task 3 is complete.
P a g e 11 | 24
Transshipment: Regional Flight Catering
This portion of the assignment will utilize the following worksheets within the module assignment workbook.
Task 4_TS Catering
Task 4
The following are instructions for conducting a transshipment analysis in Excel
This section is practice for a transshipment point analysis model.
1. Open the assignment Excel workbook to the worksheet “Task 4_TS Catering”
2. Complete the following steps within the LP Model Formulation Steps.
3. Complete the following steps within the Intermediate Calculations.
From this point forward, the solving the transshipment problem is the same as a linear programming problem. Hint: this would be a
good point to review the LP instructions.
4. Construct the objective table.
a. Hint: the exact number of color-coded cells is provided
5. Construct the constraints table.
a. Hint: the exact number of color-coded cells is provided
6. Input the Solver Parameters window information
7. Interpret the results using the Objective and Constraints tables to answer the questions.
a. Answers should be typed into the respective green highlighted boxes. The wrap text and increasing the row height
can be used to ensure the entire answer is displayed.
8. Task 4 is complete.
P a g e 12 | 24
Maximal-Flow
This portion of the assignment will utilize the following worksheets within the module assignment workbook.
Task 5_MaxFlow
Task 5
The following are instructions for conducting a maximal-flow analysis in Excel
1. Open the assignment Excel workbook to the worksheet “Task 5_MaxFlow”
2. Read the “Instructions.” The instructions provide you an overview of the components required in the task.
3. Review the Problem Statement and Figure provided in the “Variables” section.
4. Complete the following steps within the LP Model Formulation Steps.
a. Hint: the number of green answer cells is the exact number needed.
b. Define the decision variables
i. The decision variables are the number of units flowing into and out of a node.
ii. For example: Node 1
1. Complete for all nodes.
c. Define the objective function
i. The objective is to maximize the flow from the end point to the start point.
ii. For example: Maximize flow = X61
d. Define the constraints
i. The transshipment problem has two categories of constraints:
ii. Capacity limits – how many units can flow between two nodes.
1. Using the Node 1 to Node 2 arc, there are two capacity limits.
a. Capacity or Flow Limit from Node 1 to Node 2, X12 <= 3
b. Limit from Node 2 to Node 1, X21 <= 1
c. Capacity limits of zero (0) do not need to be represented in the define the constraints
section. However, be aware the limit exists.
d. Place all of the capacity limits for each Node in a single row
Capacity and Flow Continuity Equations
iii. Flow continuity – the number of units flowing into a Node must equal the number flowing out.
1. Node 5 flow continuity
2. Flow into Node 5: X35
3. Flow out of Node 5: X53, X56
4. Balance the flow equation: X35 = X53 + X56
5. Solve the flow equation for zero: X35 – (X53 + X56) = 0
a. Hint: helps to reference the Network Capacity figure when constructing these constraint
equations.
P a g e 13 | 24
Model Formulation Steps
5. Complete the following steps within the Intermediate Calculations.
a. A demonstration is provide for Node 5.
b. You must select an example node other than Node 5.
Intermediate Calculations Example
From this point forward, the maximal-flow model problem is the similar to solving a linear programming problem. Hint: this would be
a good point to review the LP and Naming Arrays instructions.
6. Constructing the maximal flow model using LPP principles.
Note: the “x” will be replaced by the Task X, in this case “5.” The “x” will remain for easy reference when using in future
problems. Hint, Task 6.
P a g e 14 | 24
Maximal Flow Model Formulation
a. from_x
i. The from_x represents the node # the flow is “out of.”
ii. Name the from_x array as “from_5”
iii. Start with the first node, node 1, represent each node connection in a separate row.
iv. Repeat for all “from” node pairings.
v. Note: the connection from Node 1 to Node 6 (final node) is not represented. This is accounted for by the
objective function.
b. to_x
i. The to_x represents the node # the flow is “into.”
ii. Name the to_x array as “to_5”
iii. Based on the node # in “from_x” insert the corresponding “to” node #
iv. Repeat for all “to” node pairings.
v. Note: the connection from Node 6 to Node 1 (final node) is not represented. This is accounted for by the
objective function.
c. flow_x
i. The flow_x is left blank and will be change by Excel’s Solver when computing the optimal solution.
1. Note: the numbers in the figure are representative only for demonstrating the slack and surplus
calculation.
ii. The final flow_x values represent the optimal flows from Node i to Node j, Xij
iii. Name the “flow_x” array as “from_5”
d. capacity_x
i. The capacity_x represents the flow capacity from Node i to Node j, Xij
ii. Name the “capacity_x” array as “capacity_5”
iii. Insert the flow capacity for the Node 1 and Node 2 pairing.
iv. Repeat for all node pairings.
e. Slack or Surplus
i. Represents the difference between the capacity_x and flow_x
ii. Use the excel formula for each row: =capacity_x – flow_x
f. Nodes
i. List the node numbers. In this example, nodes 1 – 6.
P a g e 15 | 24
g. netFlow_x
i. Determines the net flow for each node.
1. Note: the numbers in the figure are representative only.
ii. Name the “netFlow_x” array (defined by the bordered box, nodes 2 to 5) as “netFlow_5”
iii. The following formula is used in each row cell.
1. =SUMIF(from_5,H93,flow_5)-SUMIF(to_5,H93,flow_5)
a. The “from_5”, “flow_5”, and “to_5” are the named arrays created.
b. The H93 represents the row and cell number for the node # in the “Nodes” column
before the “netFlow_x” column.
h. supplyDemand_x
i. Maintains an even supply and demand for the nodes to ensure flow through the system.
ii. Each cell is defined by a zero (0).
iii. Name the “supplyDemand_x” array (defined by the bordered box) as “supplyDemand_5”
i. maxFlow_x
i. Name the adjacent green cell “maxFlow_5”
ii. Set maxFlow_x equal to the first row of the netFlow_x column. The cell above the bordered box.
7. Open the Solver Parameters window, select Data > Solver (in the Analyze section).
a. Set Objective: maxFlow_5
b. To: select the type of LPP. In this case, Max for maximization.
c. By Changing Variable Cells: flow_5
d. Subject to the Constraints: add the constraints.
i. Flow_5 <= capacity_5 ensure the flow cannot exceed the arc capacity.
ii. netFlow_5 = supplyDemand_5 ensures the flow continuity
e. Make unconstrained variables non-negative.
f. Use the Simplex LP method
g. Solve
Maximal Flow Solver Parameters
P a g e 16 | 24
8. Interpret the results using the maxFlow_x value, flow_x column, and slack values to answer the questions.
a. “maxFlow_x” answers the objective function concern how many units can flow through the network in a given
period of time.
b. “flow_x” identifies the route utilized to flow the units through the network. Node pairings with a zero (0) value are
not used.
c. “Slack” identifies the surplus capacity in the network.
d. Type answers into the green box next to the question.
9. Task 5 is complete.
P a g e 17 | 24
Maximal-Flow: Sorting Facility
This portion of the assignment will utilize the following worksheets within the module assignment workbook.
Task 6_MaxFlowSort
Task 6
The following are instructions for conducting a maximal-flow analysis in Excel
This section is practice for a maximal flow analysis model.
1. Open the assignment Excel workbook to the worksheet “Task 6_MaxFlowSort”
2. Read the “Instructions.” The instructions provide you an overview of the components required in the task.
3. Review the Problem Statement and Figure provided in the “Variables” section.
4. Complete the following steps within the LP Model Formulation Steps.
5. Complete the following steps within the Intermediate Calculations.
6. Constructing the maximal flow model using LPP principles.
7. Use Excel’s Solver
8. Interpret the results using the maxFlow_x value, flow_x column, and slack values to answer the questions.
9. Task 6 is complete.
P a g e 18 | 24
Shortest Route
This portion of the assignment will utilize the following worksheets within the module assignment workbook.
Task 7_ShortRoute
Task 7
The following are instructions for conducting a shortest route analysis in Excel
1. Open the assignment Excel workbook to the worksheet “Task 7_ShortRoute”
2. Read the “Instructions.” The instructions provide you an overview of the components required in the task.
3. Review the Problem Statement and Figure provided in the “Variables” section.
4. Complete the following steps within the LP Model Formulation Steps.
a. Hint: the number of green answer cells is the exact number needed.
b. Define the decision variables
i. The decision variables are all of the possible node pairings, Xij
1. For example, Node 4 possible pairings include: X42, X45, X46
ii. Distance between all of the possible node pairings, dij
1. The distances must be specified for both directions even though they are the same (unlike a
maximal flow).
a. For example, the distance for d24 = 200 and the distance for d42 = 200
2. There should be a distance for every node pairing in step 4.b.i
iii. Exceptions:
1. No pairings back to the start node (node 1).
2. No pairings from the end node to a previous node (node 6)
c. Define the objective function
i. The objective is to minimize the distance from the start to the end of the network.
ii. For example: Minimize distance = d12*X12 + …… + d56*X56
d. Define the constraints (Flow Continuity for Shortest Route problems)
i. The shortest route constraints are divided between the start/end nodes and remaining network nodes.
Except for the start and finish nodes, the flow into a Node must equal the flow out of a Node.
1. Hint: helps to reference the Network Capacity figure when constructing these constraint
equations.
ii. Start and End node constraints equal one (1).
1. For example, Node 1 (start): X12 + X13 = 1
iii. Remaining network nodes, flow into equals the flow out. Or rearranged to equal zero (0).
1. For example, Node 2: X12 + X32 – (X23 + X24 + X25) = 0
iv. Each node is presented in its own row.
P a g e 19 | 24
Distance and Flow Continuity Equations
Model Formulation Steps
5. Complete the following steps within the Intermediate Calculations.
a. A demonstration of flow is provided for Node 2.
b. You must select an example node other than Node 2.
P a g e 20 | 24
Intermediate Calculations Example
From this point forward, the maximal-flow model problem is the similar to solving a linear programming problem. Hint: this would be
a good point to review the LP and Naming Arrays instructions.
6. Constructing the maximal flow model using LPP principles.
Note: the “x” will be replaced by the Task X, in this case “7.” The “x” will remain for easy reference when using in future
problems. Hint, Task 8.
Shortest Route Model Formulation
a. from_x
i. The from_x represents the node # the flow is “out of.”
ii. Name the from_x array as “from_7”
iii. Start with the first node, node 1, represent each node connection in a separate row.
iv. Repeat for all “from” node pairings.
b. to_x
i. The to_x represents the node # the flow is “into.”
ii. Name the to_x array as “to_7”
iii. Based on the node # in “from_x” insert the corresponding “to” node #
iv. Repeat for all “to” node pairings.
c. distance_x
i. The distance_x represents the distance from Node i to Node j, dij
ii. Name the “distance_x” array as “distance_7”
iii. Insert the route distance for the Node 1 and Node 2 pairing.
iv. Repeat for all node pairings.
d. onRoute_x
i. The onRoute_x is left blank and will be change by Excel’s Solver when computing the optimal solution.
ii. The final onRoute_x values represent the optimal route from Node i to Node j, Xij
iii. Name the “onRoute_x” array as “onRoute_7”
P a g e 21 | 24
e. Nodes
i. List the node numbers. In this example, nodes 1 – 6.
f. netFlow_x
i. Determines the net flow for each node.
ii. Name the “netFlow_x” array (defined by the bordered box, nodes 1 to 6) as “netFlow_7”
iii. The following formula is used for:
1. Node 1: =SUMIF(from_7,H112,onRoute_7)
a. The H112 represents the row and cell number for the node # in the “Nodes” column
before the “netFlow_x” column.
2. Nodes 2 – 5: =SUMIF(from_7,H113,onRoute_7)-SUMIF(to_7,H113,onRoute_7)
a. The “from_7”, “onRoute_7”, and “to_7” are the named arrays created.
3. Node 6: =-SUMIF(to_7,H117,onRoute_7)
g. supplyDemand_x
i. Maintains an even supply and demand for the nodes to ensure flow through the system.
ii. Name the “supplyDemand_x” array (defined by the bordered box) as “supplyDemand_7”
iii. Start node (node 1) = 1
iv. Nodes between the start and end (nodes 2 – 5) = 0
v. End node (node 6) = -1
h. minDist_x
i. Name the adjacent green cell “minDist_7”
ii. Set minDist_x equal to the sumproduct of distance_x and onRoute_x
7. Open the Solver Parameters window, select Data > Solver (in the Analyze section).
a. Set Objective: minDist_7
b. To: select the type of LPP. In this case, Min for minimization.
c. By Changing Variable Cells: onRoute_7
d. Subject to the Constraints: add the constraints.
i. netFlow_7 = supplyDemand_7 ensures the flow continuity
e. Make unconstrained variables non-negative.
f. Use the Simplex LP method
g. Solve
P a g e 22 | 24
Maximal Flow Solver Parameters
8. Interpret the results using the minDist_x value, distance_x, and onRoute_x column to answer the questions.
a. “minDistance_x” answers the objective function concern the shortest distance through the network.
b. “onRoute_x” identifies the route utilized to flow through the network. Node pairings with a zero (0) value are not
used.
c. Type answers into the green box next to the question.
9. Task 7 is complete.
P a g e 23 | 24
Shortest Route: Flight Route
This portion of the assignment will utilize the following worksheets within the module assignment workbook.
Task 8_ShortRouteFlight
Task 8
The following are instructions for conducting a shortest route analysis in Excel
1. Open the assignment Excel workbook to the worksheet “Task 8_ShortRouteFlight”
2. Read the “Instructions.” The instructions provide you an overview of the components required in the task.
3. Review the Problem Statement and Figure provided in the “Variables” section.
4. Complete the following steps within the LP Model Formulation Steps.
5. Complete the following steps within the Intermediate Calculations.
From this point forward, the maximal-flow model problem is the similar to solving a linear programming problem. Hint: this would be
a good point to review the LP and Naming Arrays instructions.
6. Constructing the maximal flow model using LPP principles.
7. Use Excel’s Solver
8. Interpret the results using the minDist_x value, distance_x, and onRoute_x column to answer the questions.
9. Task 8 is complete.
P a g e 24 | 24
The End of the Assignment
“What kind of books do planets like to read? Comet books.”