IntroLGMT 525 Management Science for OperationsTransportation & Network AnalysisModule 4 AssignmentFile: M05_LGMT525_Assignment.xlsxWorksheetsIntroOverview of the assignment...

Can you help me solve the 8 tasks in this assignment. I have another file to attach that has instructions.


Intro LGMT 525 Management Science for Operations Transportation & Network Analysis Module 4 Assignment File: M05_LGMT525_Assignment.xlsx Worksheets IntroOverview of the assignment tasks. Honor CodeConfirmation of your agreement to follow the ERAU honor code. Failure to complete this step will result in a grade of '0' for the assignment. Task 1_FacLoc BirminghamIntroduction to facility location analysis Task 2_FacLoc SeattlePractice facility location analysis Task 3_TS Regional Introduction to transshipment analysis Task 4_TS CateringPractice transshipment analysis Task 5_MaxFlowIntroduction to maximum flow analysis Task 6_MaxFlowSortPractice maximum flow analysis Task 7_ShortRouteIntroduction to shortest route analysis Task 8_ShortRouteFlightPractice shortest route analysis Honor Code Embry-Riddle Aeronautical University Honor Code Type your First and Last Name in the box below to confirm that the work in this file is entirely your own and in adherence with the Honor Code First and Last Name Please type your Student ID # into the box below Student ID # Failure to complete this step will result in a grade of '0' for the assignment. Task 1_FacLoc Birmingham Facility Location Analysis: Birmingham Cell Color Legend NotesUser inputAuto calculateAnswerLocation Marker Constraint Sign<= less="" than="" or="" equal="" to="Equals" (add="" apostrophe="" first="" -="" '="-" to="" "tell"="" excel="" this="" is="" not="" an="" equation)="">=Greater than or equal to 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 produces components at three production plants. These plants supply inventory to regional warehouses. The company is considering constructing an additional production plant in either Birmingham or Seattle to expand capacity. Using the following information, determine how many units are shipped from each plant to each warehouse and the total cost of production and shipping. This analysis is for the proposed Birmingham location. Table 1 Demand and Supply Data WarehouseDemand (units/mo)Production PlantSupply (units/mo)Production cost ($/unit) Detroit10000Cincinnati15000$ 48.00 Dallas12000Salt Lake City6000$ 50.00 New York15000Pittsburgh14000$ 52.00 Los Angeles9000Birminghama11000$ 49.00 a Site of proposed production plant. Table 2 Shipping Costs From \ ToDetroitDallasNew YorkLos Angeles Cincinnati$ 25.00$ 55.00$ 40.00$ 60.00 Salt Lake City$ 35.00$ 30.00$ 50.00$ 40.00 Pittsburgh$ 36.00$ 45.00$ 26.00$ 66.00 Birminghama$ 35.00$ 30.00$ 41.00$ 50.00 a Site of proposed production plant. 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 Data Table Destination Title CostsSupply Source Title Demand Solver Table Warehouse Locations Shipments0000Supply Production Plants0 0 0 0 Demand Total Cost0.0 Result Statements Note: Write result statements for the problem objective, production point of view, warehouse point of view. Objective Production POV Warehouse POV Interpreting the Results Note: Please answer the following questions. Type the answer in the green box. QuestionsType answer in the box 1. Briefly describe a qualitative reason that supports selecting Birmingham as the new production plant location. End of Analysis Task 2_FacLoc Seattle Facility Location Analysis: Seattle Cell Color Legend NotesUser inputAuto calculateAnswerLocation Marker Constraint Sign<= less="" than="" or="" equal="" to="Equals" (add="" apostrophe="" first="" -="" '="-" to="" "tell"="" excel="" this="" is="" not="" an="" equation)="">=Greater than or equal to 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 produces components at three production plants. These plants supply inventory to regional warehouses. The company is considering constructing an additional production plant in either Birmingham or Seattle to expand capacity. Using the following information, determine how many units are shipped from each plant to each warehouse and the total cost of production and shipping. This analysis is for the proposed Seattle location. Table 1 Demand and Supply Data WarehouseDemand (units/mo)Production PlantSupply (units/mo)Production cost ($/unit) Detroit10000Cincinnati15000$ 48.00 Dallas12000Salt Lake City6000$ 50.00 New York15000Pittsburgh14000$ 52.00 Los Angeles9000Seattlea11000$ 53.00 a Site of proposed production plant. Table 2 Shipping Costs From \ ToDetroitDallasNew YorkLos Angeles Cincinnati$ 25.00$ 55.00$ 40.00$ 60.00 Salt Lake City$ 35.00$ 30.00$ 50.00$ 40.00 Pittsburgh$ 36.00$ 45.00$ 26.00$ 66.00 Seattlea$ 60.00$ 38.00$ 65.00$ 27.00 a Site of proposed production plant. 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 Data Table Warehouse Locations Production Plants Solver Table Warehouse Locations Production Plants Total Cost Result Statements Note: Write result statements for the problem objective, production point of view, warehouse point of view. Objective Production POV Warehouse POV Interpreting the Results Note: Please answer the following questions. Type the answer in the green box. QuestionsType answer in the box 1. Briefly describe a qualitative reason that supports selecting Birmingham as the new production plant location. End of Analysis Task 3_TS Regional Transshipment Problem: Regional Distribution Cell Color Legend NotesUser inputAuto calculateAnswerLocation Marker Constraint Sign<= less="" than="" or="" equal="" to="Equals" (add="" apostrophe="" first="" -="" '="-" 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 "X23" 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 \ To:ChicagoBuffaloNew York CityBostonSt LouisSupply Toronto$ 4.00$ 7.00---800 Detroit$ 5.00$ 7.00---700 Chicago--$ 6.00$ 4.00$ 5.00- Buffalo--$ 2.00$ 3.00$ 4.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 ReferenceConstraint Constraint VariablesTechnical CoefficientsLHS (units used)Constraint SignRHS (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. QuestionsType answer in the box 1. Briefly describe a qualitative reason that supports sending more units to one transshipment point versus another. End of Analysis Task 4_TS Catering Transshipment Problem: Regional Flight Catering Cell Color Legend NotesUser inputAuto calculateAnswerLocation Marker Constraint Sign<= less="" than="" or="" equal="" to="Equals" (add="" apostrophe="" first="" -="" '="-" 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 "X23" 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 flight catering company wants to minimize shipping cost from the production kitchen (Source) to the customer (Airports) via regional transshipment points. The production kitcher produces a certain number of meals (Supply) to meet the needs of airlines at each airport (Demand). Due to the high cost of intra-city shipping, the catering company wants to minimize the cost by strategically selecting transshipment points. Note: the cost of the meals are not included in this analysis. Figure 1 Representation of Transshipment Network Table 1 Transshipment Data From \ To:HobokenYonkersBrooklynNewarkTeterboroLaGuardiaJFK IntlSupply Flight Catering Center$ 2.00$ 3.00$ 4.00----16000 Hoboken---$ 2.00$ 2.50$ 5.00$ 6.00- Yonkers---$ 2.50$ 2.00$ 4.00$ 5.00- Brooklyn---$ 3.00$ 4.00$ 3.00$ 2.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 ReferenceConstraint Constraint VariablesTechnical CoefficientsLHS (units used)Constraint SignRHS (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. QuestionsType answer in the box 1. Did all transshipment points receive or ship units? 2. If No, which transshipment point did not receive or ship units? 3. Briefly describe a qualitative reason that supports question #1. End of Analysis Task 5_MaxFlow Maximal-Flow Problem Cell Color Legend NotesUser inputAuto calculateAnswerLocation Marker Constraint Sign<= less="" than="" or="" equal="" to="Equals" (add="" apostrophe="" first="" -="" '="-" 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 "X23" 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 town wants to determine how many vehicles can flow through their road system in a period of time. A representation of the road network is provided in Figure 1. The maximum flow from one node to another is indicated by the number closest to the origin node along the line to the destination node. Example: the maximum flow from node 3 to node 4 is 3. The maximum flow from node 4 to node 3 is 1. The capacity numbers provided in Figure 1 are in hundreds of cars per hour. What is the maximum flow through the network? Figure 1 Representation of a road network capacity 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 Instructions: 1. Label each column name in accordance with variable naming standards with an underscore number associated with the task number (e.g. columnName_5) 2. Define each column set as a named array. The array name must match the label in #1. 3. Label the objective function output and define with a named array. 4. Solve using Excel's Solver. The cell references and constraints within Solve must utilize the array names. Cell ReferenceConstraintCell ReferenceConstraint LHS (units used)Constraint SignRHS (constraint)Slack (-) or Surplus (+)LHS (units used)Constraint SignRHS (constraint) from_xto_xflow_xcapacity_xNodesnetFlow_xsupplyDemand_x Maximum FlowmaxFlow_x 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. QuestionsType answer in the box 1. Which from to node combination has the largest slack? 2. What is the slack value for question #1? 3. Describe a qualitative factor that could be changed to increase the flow in the system. End of Analysis Task 6_MaxFlowSort Maximal-Flow: Sorting Facility Cell Color Legend NotesUser inputAuto calculateAnswerLocation Marker Constraint Sign<= less="" than="" or="" equal="" to="Equals" (add="" apostrophe="" first="" -="" '="-" 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 "X23" 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 determine how many packages can flow through their sorting facility in a period of time