Assignment Instructions
Network Model
(WA Ch. 5 Problem 54 Logistics at General Ford)
Purpose
The purpose of this assignment is to apply LP to a network model and then communicate it to a decision maker with a memorandum. I have selected a logistics problem. This problem is the situation of an auto company with plants in Los Angeles and Detroit, a warehouse in Atlanta, and is trying to determine the least cost decision to ship cars to customers in Houston and Tampa. You are challenged to develop a logistics network model that minimizes cost for the shipping plan, and communicate your analysis and recommendation to the decision-maker.
Note: This problem has parts a), b), and c) – ONLY PART a) IS REQUIRED.
Instructions
1. A template has been provided with a structure for the decision situation
1.1. Input Information
1.1.1. A table of costs of shipping from and to each feasible path has been provided. Note the number codes that are used to identify Origins (From) and Destinations (To).
1.1.2. The only other input is the requirements for supply from the two plants and demand for the two customers.
1.2. Arc List and Decision Variables
1.2.1. The decision is the number of cars (units) that will flow from each origin to each destination. Note that for each cost in the From/To matrix, you will have a possible flow and a decision variable. You will have a possible flow for each From/To combination where there is a cost. The flow is From the Origin, To the Destination. To code each flow start with the first From Row and code each arc combination in that row, then do the next row, until all From/To arcs are complete.
1.3. Objective
1.3.1. The objective is to achieve a minimum cost shipping schedule. This is simply the arc cost multiplied by the number of units flowing across that arc. Prepare a single cell with a SUMPRODUCT function for this total. The solution as a check on your answer is $420,500.
1.4. Flow Balance Constraints
1.4.1. Origins – The Net Flows for the origin nodes will use the SUMIF function to look for all origins of the same number and the sum range of the flows. These must be less than or equal to the input supply
1.4.2. Destinations – the Net Flows for the destination nodes will use the SUMIF function to look for all destinations of the same number and the sum range of the flows. These must be equal to the input demand.
1.4.3. Warehouse – The Net Flows for the warehouse must be zero as anything flowing into the warehouse must leave the warehouse. Use two SUMIF functions in one formula that subtracts the sum of all origin flows from the sum of all destination flows.
Note: Examples of these Flow Balance Constraint calculations are provided throughout the lecture and examples from lecture provided on Blackboard.
2. Validate that your model calculates correctly by entering 1 unit in the various Decision Variables cells and confirming the correct output.
3. Prepare Solver setup
3.1. Set Objective: your Total Cost cell
3.2. To: Min
3.3. Changing Cells: the units in the flow cells
3.4. Define a <= constraint for the Origin Flow Balance Constraints.
3.5. Define an = constraint for the Demand Flow Balance Constraints.
3.6. Define an = constraint for the Warehouse Flow Balance Constraint.
3.7. Check the box Make Unconstrained Variables Non Negative (non-negativity)
3.8. Select Solving Method: Simplex LP
4. Solver Output
4.1. In the Solver Results dialog box, choose the sensitivity report, and choose to save the results. Any SolverTable analysis is not required for this assignment.
5. Memorandum to Communicate Decision Recommendation
5.1. The second part of your assignment is to communicate your recommendation to the VP of Operations at General Ford, Mr. Henry Ford.
5.2. Prepare a Word file in memo form to briefly communicate the results of your analysis and decision recommendation to Mr. Ford using the following outline:
5.2.1. Memo outline
To:
From:
Date:
Subject:
Problem Solving Approach
Results of the Analysis
Decision Recommendation
5.3. Mr. Ford appreciates good written communication that is concise, but complete. However, like many senior managers, he is able to understand a decision without knowing the details – that is why she has hired you! You should take care to consider the following in your memo:
5.3.1. Problem Solving Approach
This outlines the technique(s) used to solve the problem and their implementation. For Management Science techniques, you should explain which technique is being used and summarize the development of the model. The use of brief tables of information, lists, or simple diagrams is encouraged. This is an essential part of the memo and should contain enough information to explain the approach to the problem and give confidence to the reader in your skill, but be as brief as possible.
5.3.2. Results of the Analysis
This outlines the output of the problem solving approach. You should quantify the results in the form of the objective of the problem. Any unexpected observations or solution difficulties should be explained. The results are often brief statements of the solution and any insights of the analyst. This is an opportunity to demonstrate your understanding of the problem and analytical expertise.
5.3.3. Decision Recommendation
This section of the report answers the question(s) of the problem with a recommendation to the decision-maker. This recommendation should be in the form of advice to the decision-maker given your expertise. As such, advice is usually provided with any qualifying concerns that you may have.
6. Submission: Change the names of your Excel and Word files to begin with your first and last name (such as, General Ford – Network Model.xlxs and General Ford – Network Memo.docx). Then upload both files to the assignment item on Blackboard.
Assessment
A rubric has been provided for guidance on the assignment that will be used for scoring. The emphasis is on completing the model with good modeling guidelines, getting the correct solution in Solver, and communicating to the decision-maker in a well written memo.