ana steel presentation (1)
TRANSCRIPT
ANA Steel CorporationMANAGING LOGISTICS VIA INTEGER LINEAR PROGRAMMINGBYALEX MIDENCENIKOLE CUNNINGHAMASHLEY MAGEE
MENTOR: RAIDA ABUIZAM, PH.D.
Learning Objectives
Use network diagrams to represent the problem graphically. The graphic presentation will be used in developing the spreadsheet model.
Take care of fixed costs by using binary variables (0,1). Because fixed costs are not a linear function, the use of binary variables will convert a non-linear model into a linear model.
Develop a spreadsheet model to minimize total cost without having to formulate the problem algebraically.
Case Overview
ANA Steel Corporation is a growing plate steel manufacturing company. The company wants to expand to four production and warehouse locations throughout the United States due to customer demand.
The CEO of ANA Steel Corp. would like to know the most cost efficient way to get the product from the plants to the customers. The possible plant locations are in Alabama, Indiana, Ohio and Missouri.
Case Overview Continued
Table 1: Plant Information
State Fixed Cost/ Year
Production Capacity tons/
YearAlabama $96,000,000 3,000,000Indiana $80,000,000 2,500,000Ohio $33,600,000 1,050,000Missouri $24,000,000 700,000
Case Overview Continued
ANA Steel Corporation determined that there are four potential warehouse locations to be used as distribution channels. When deciding warehouse locations, we researched fixed costs along with their storage capacities as shown in Table 2. The four prospective warehouse locations are Texas, Georgia, Illinois, and California. Table 2: Warehouse Information
State Fixed Cost/ Year Storage Capacity (tons/yr)
Texas $563,250 1,345,000Georgia $450,600 1,336,000Illinois $450,600 2,236,000
California $585,780 2,246,800
Case Overview Continued
ANA had found the estimated yearly demand for their product and listed their outcomes in Table 3. Table 3: Customer Demand
State Yearly Demand (tons)Texas 92,800
California 518,400Georgia 817,600Illinois 351,200
South Carolina 584,000Pennsylvania 584,000
Michigan 525,000Tennessee 116,800
Iowa 525,600Arkansas 525,600
Case Overview Continued
ANA projected transportation costs from each plant to warehouse and from each warehouse to customer. Their findings are recorded in tables 4 and 5, respectively. Table 4: Transportation costs per ton from plants to warehouses
Texas Georgia Illinois CaliforniaAlabama $1,651 $231 $832 $2,671Indiana $2,019 $1,136 $2,442 $1,969
Ohio $1,093 $875 $2,672 $2,119Missouri $1,235 $2,025 $990 $781
Case Overview Continued
Table 5: Transportation cost per ton from warehouse to customers
Texas California
Georgia
Illinois South Carolina
Pennsylvania
Michigan Tennessee
Iowa Arkansas
Texas $251 $2,427 $1,576
$1,503
$1,757 $2,453 $2,143 $1,353 $1,473 $848
Georgia $1,505 $3,452 $225 $1,031
$306 $1,209 $1,370 $332 $1,359 $744
Illinois $1,533 $3,145 $1,383
$307 $1,373 $1,206 $699 $859 $315 $804
California
$2,198 $605 $3,652
$2,886
$3,720 $3,788 $3,333 $3,244 $2,556 $2,751
Statement of Problem
Which plants to keep open and which warehouses to ship plate steel from in order to meet customer demand in the most cost efficient manner.
Network Diagram
IN
OH
MO
TX
AR
IA
TN
MI
PA
SC
IL
GA
CA
TX
GA
IL
CA
AL
Plants Warehouses
Customers
Requirement SolutionSpreadsheet Model
Development
Record all of the inputs into the spreadsheet:WarehousesTexas Georgia Illinois California Plants Capacity Fixed cost
Plants Alabama $1,651 $231 $832 $2,671 3,000,000 $96,000,000Indiana $2,019 $1,136 $2,442 $1,969 2,500,000 $80,000,000Ohio $1,093 $875 $2,672 $2,119 1,050,000 $33,600,000Missouri $1,235 $2,025 $990 $781 700,000 $24,000,000
7,250,000
WarehousesTexas Georgia Illinois California Demand
Texas $251 $1,505 $1,533 $2,198 92,800 California $2,427 $3,452 $3,145 $605 518,400 Georgia $1,576 $225 $1,383 $3,652 817,600 Illinois $1,503 $1,031 $307 $2,886 351,200
Customers South Carolina $1,757 $306 $1,373 $3,720 584,000 Pennsylvania $2,453 $1,209 $1,206 $3,788 584,000 Michigan $2,143 $1,370 $699 $3,333 525,600 Tennessee $1,353 $332 $859 $3,244 116,800 Iowa $1,473 $1,359 $315 $2,556 116,800 Arkansas $848 $744 $804 $2,751 525,600
4,232,800Texas Georgia Illinois California
Fixed Cost $563,250 $450,600 $450,600 $585,780
WHSE Capacity 1,345,000 1,336,000 2,236,000 2,246,800 7,163,800
Spreadsheet Model Development
The model should display each of the following: The quantity in tons that should be shipped from warehouses to customers. Fixed costs in US dollars of the operating plants and warehouses chosen to
be kept open Shipping costs from plants to warehouses and subsequently from
warehouses to customers Quantity in tons received by each open warehouse should equal the
quantity shipped out of each warehouse. No excess quantities should be kept at any warehouse
The quantity in tons that should be shipped from plants to warehouses Total amount shipped to customers from open warehouses should equal
customers’ demand
Solution
ANA linear programming model.xlsx
Texas Georgia Illinois California Plants Capacity Fixed cost Use Plant?Alabama $1,651 $231 $832 $2,671 3,000,000 $96,000,000 1Indiana $2,019 $1,136 $2,442 $1,969 2,500,000 $80,000,000 0Ohio $1,093 $875 $2,672 $2,119 1,050,000 $33,600,000 1Missouri $1,235 $2,025 $990 $781 700,000 $24,000,000 1
7,250,000Texas Georgia Illinois California Out of PLNTS Plant Capacity
Alabama - 1,336,000 1,664,000 - 3,000,000 <= 3,000,000Indiana - - - - 0 <= 0Ohio 532,800 - - - 532,800 <= 1,050,000Missouri - - 181,600 518,400 700,000 <= 700,000Into WHSE 532,800 1,336,000 1,845,600 518,400
<= <= <= <=WHSE Capacity 1,345,000 1,336,000 2,236,000 2,246,800
Texas Georgia Illinois CaliforniaTexas $251 $1,505 $1,533 $2,198California $2,427 $3,452 $3,145 $605Georgia $1,576 $225 $1,383 $3,652Illinois $1,503 $1,031 $307 $2,886South Carolina $1,757 $306 $1,373 $3,720Pennsylvania $2,453 $1,209 $1,206 $3,788Michigan $2,143 $1,370 $699 $3,333Tennessee $1,353 $332 $859 $3,244
Iowa $1,473 $1,359 $315 $2,556Arkansas $848 $744 $804 $2,751
Texas Georgia Illinois CaliforniaFixed Cost $563,250 $450,600 $450,600 $585,780
WHSE Capacity 1,345,000 1,336,000 2,236,000 2,246,800 7,163,800
Use WHSE? 1 1 1 1Out to CUST Demand
Texas 92,800 - - - 92,800 >= 92,800 California - - - 518,400 518,400 >= 518,400 Georgia - 817,600 - - 817,600 >= 817,600 Illinois - - 351,200 - 351,200 >= 351,200 South Carolina - 518,400 65,600 - 584,000 >= 584,000 Pennsylvania - - 584,000 - 584,000 >= 584,000 Michigan - - 525,600 - 525,600 >= 525,600 Tennessee - - 116,800 - 116,800 >= 116,800 Iowa - - 116,800 - 116,800 >= 116,800 Arkansas 440,000 - 85,600 - 525,600 >= 525,600 Going from WHSE to CUST 532,800 1,336,000 1,845,600 518,400 4,232,800
= = = =Coming into WHSE 532,800 1,336,000 1,845,600 518,400
CostsFixed costs PLNTS 153,600,000.00$ Fixed costs WHSE 2,050,230.00$ Transport PLNTS to WHSE 2,858,211,600.00$ Transport WHSE to CUST 2,527,951,840.00$
Total Cost 5,541,813,670.00$
Warehouses
Plan
ts
Warehouses
Cust
omer
s
Solution ContinuedSolution Network Diagram
This model solved for:
AL
IN
OH
MO
TX
GA
IL
CA
TX
CA
GA
IL
SC
PA
MI
TN
IA
AR
Plants Warehouses
Customers
1,336,000
1,664,000
532,800
181,600
518,400
92,800
440,000
817,600
518,400
351,200
65,600
584,000
525,600
116,800
116,800
85,600
518,400
Interpretation of Solver Solution
In generating a solution for minimal costs, Solver determined that only three of the four plants needs to be open for manufacturing. The binary variables (0,1) informed us NOT to open the Indiana plant and to proceed using each of the four warehouses. Also, instead of each warehouse distributing to each customer, it is actually more economical for each warehouse to ship product to a select amount of customers. This led to the Illinois warehouse distributing to the most customers.
ThankYou
Resources
http://www.freightcenter.com/freight/rail-freight.aspx http://www.alibaba.com/showroom/steel-plate-price-per-ton.html http://worldfreightrates.com/freight# http://www.flatbedsource.com/truckload-market-price-index/ http://minerals.usgs.gov/minerals/pubs/commodity/iron_&_steel/mcs-2015-feste.pdf https://www.steel.org/~/media/Files/AISI/Reports/AISI_Profile_14_FINAL.pdf http://www.statista.com/statistics/268683/us-steel-demand-since-2008/