Supply chain management-optimization

1

SCM 421 Supply Chain Analytics Handout #16

Network Optimization

Agenda for today: 1. Attendance and questions from last time (5 min) 2. A transportation problem (70 min)

Homework: Turn in your work for the problem listed at the end of this handout. Agenda for the next class: 1. Sensitivity Analysis

Problem 1: The Transportation Problem (Based on the transportation problem in Dantzig G. B., Linear Programming and Extensions, Princeton University Press, Princeton, New Jersey, 1963)

Acme Inc. has two plants, one each in Seattle, Atlanta and Chicago. Their capacities are 300,000 units, 350,000 units and 650,000 units respectively. Acme needs to ship units from these plants to 10 markets. The demand at each market is listed in table 1. The per unit transportation cost between each source and destination is given in table 2. The demand at any market can be satisfied from any plant. How can you minimize the transportation cost?

Market Demand 1 Atlanta 200,000 2 Columbia 100,000 3 Dayton 50,0000 4 Fremont 50,000 5 Hanover 25,000 6 Indianapolis 200,000 7 Kensington 100,000 8 Manchester 30,000 9 New York 300,000 10 Raleigh 220,000

Table 1: Demand data

2

Table 2: Transportation cost

Let’s solve this in Excel. Towards this objective, first recognize

a. What is the objective function? Write in words.

b. What are your decision variables? How many of them are there? Write in words.

c. What are the supply constraints? Write in words. How many supply constraints are there?

d. What are the demand constraints? Write in words. How many demand constraints are there?

e. Now, let us set this up in Excel. First, we will determine the overall cost.

f. Then we define all supply constraints and demand constraints.

g. Next, let us use Solver to get the optimal solution. What is the minimum cost? (Remember to select the “min” option in the Solver window).

Market Seattle Atlanta Chicago Atlanta 5.21$ 0.20$ 2.30$ Columbia 2.30$ 5.20$ 2.60$ Dayton 2.65$ 2.30$ 1.20$ Fremont 2.50$ 2.50$ 10.58$ Hanover 8.14$ 5.00$ 7.00$ Indianapolis 8.58$ 6.52$ 3.20$ Kensington 2.50$ 8.01$ 15.24$ Manchester 10.25$ 12.25$ 12.45$ New York 15.21$ 10.25$ 11.65$ Raleigh 14.36$ 5.65$ 9.87$

Plant Transportation Cost / Unit

3

Binary Integer Programming: Binary Integer programming is used to model situations where your decision is Yes/No (1/0). Let’s extend the above example to understand the working of binary variables.

Problem 2: Suppose Acme has the following opportunity. It can close any of the three existing plants and get a revenue of $ 3,250,000, $ 4,000,000, $ 2,000,000 from the Seattle, Atlanta and Chicago plants respectively. In exchange of one or more of these plants, it can open new plants at Pittsburgh and/or Phoenix at the expense of 4,400,000 and 3,900,000 respectively. The two plants will have capacities of 200,000 units each. Should the firm close/open plants? If yes, what is the optimal allocations with the new set of optimal plants? Before running the numbers, think about it for 5 minutes and write down your opinion about which plants (if any) to close. What would need to happen for you to close a plant and open a different one?

Homework: A boutique owner is planning to open new stores using a total capital of $200,000. She has identified 10 possible locations. Further, she has estimates of the investment required at each location and the expected profit from each location (using a simulation model, perhaps). The expected profit is in terms of the NPV and includes the investment. The data is given in Spreadsheet ‘Location Selection’. Which set of locations should she select? Clearly, the decision for every location is Yes/No or 1/0. These decision variables are highlighted in yellow. The objective function, in terms of the decision variables, is expressed using sumproduct function.

The monetary constraint also is expressed using the sumproduct function. What is the optimal “set” of locations where she should focus?

How would you include the constraints for the following situations?

a) Location 1 must have a store. b) Either location 1 or 3 must have a store. c) Locations 3 and 4 both cannot have a store.

Please include the following in your submission:

1. A screenshot of your solver setup dialog and a screenshot of the excel model with the optimal solution and relevant formulae, for the basic model.

2. Screenshots of the modifications you made to the excel model for each constraint (screenshot of the solver setup is not required).