{"text_format":"3","workbook":"xls","n_sheets":7,"sheets":["Summary","Transport1","Transport2","Transport3","Knapsack","Facility","Prodtran"],"not_rendered":{"defined_names":601},"read":"whole","date_system":"1900","formulas":"cached_only"}

{"sheet":"Summary","index":0,"n_rows":24,"n_cols":1,"rows_kept":24,"flags":["formulas_cached_only"]}
["Distribution/Logistics Examples"]
[]
["On each example worksheet, read the comments at the bottom of the sheet, then"]
["click Tools Solver... to examine the decision variables, constraints, and objective."]
["To find the optimal solution, click the Solve button."]
[]
["An important group of Solver applications is based on distribution or network models. "]
["The amount of money that companies save each year by applying linear programming "]
["towards their distribution problems is enormous."]
[" "]
["In this series we will look at a simple transportation problem in worksheet Transport1, then "]
["extend it to a 2-level multi-product model in worksheets Transport2 and Transport3. "]
[]
["We'll also examine a frequently encountered class of problems called 'knapsack' "]
["problems, in worksheet Knapsack.  As an example we will look at a truck that has to "]
["transport different kinds of gas. "]
[]
["In the Facility worksheet we will look at a facility location problem, where a company has "]
["to decide if it's profitable to close down one or more of their plants and save overall costs. "]
[]
["Finally, in the Prodtran worksheet, we will examine a combination production and "]
["transportation model where the number of products made in plants depends on choices"]
["made in distribution. This kind of combination is often possible, but many users prefer to"]
["split these models up into smaller ones to simplify the problem."]

{"sheet":"Transport1","index":1,"n_rows":41,"n_cols":8,"rows_kept":41,"flags":["formulas_cached_only"]}
["Transportation Problem 1"]
[]
["Minimize the costs of shipping goods from factories to customers, while not exceeding   "]
["the supply available from each factory and meeting the demand of each customer."]
[]
["Cost of shipping ($ per product)"]
[null,"Destinations"]
[null,"Customer 1","Customer 2","Customer 3","Customer 4","Customer 5"]
["Factory 1",{"v":1.75,"d":"$1.75"},{"v":2.25,"d":"$2.25"},{"v":1.5,"d":"$1.50"},{"v":2,"d":"$2.00"},{"v":1.5,"d":"$1.50"}]
["Factory 2",{"v":2,"d":"$2.00"},{"v":2.5,"d":"$2.50"},{"v":2.5,"d":"$2.50"},{"v":1.5,"d":"$1.50"},{"v":1,"d":"$1.00"}]
[]
["Number of products shipped"]
[null,"Customer 1","Customer 2","Customer 3","Customer 4","Customer 5","Total","Capacity"]
["Factory 1",0,0,0,0,0,0,{"v":60000,"d":"60,000"}]
["Factory 2",0,0,0,0,0,0,{"v":60000,"d":"60,000"}]
["Total",0,0,0,0,0]
["Demand",{"v":30000,"d":"30,000"},{"v":23000,"d":"23,000"},{"v":15000,"d":"15,000"},{"v":32000,"d":"32,000"},{"v":16000,"d":"16,000"}]
[]
["Total cost of shipping",null,{"v":0,"d":"$0"}]
[]
[]
["Problem"]
["A company wants to minimize the cost of shipping a product from 2 different factories to 5 different customers. "]
["Each factory has a limited supply and each customer a certain demand. How should the company distribute the "]
["product?"]
[]
["Solution"]
["1) The variables are the number of products to ship from each factory to the customers. These are given the "]
["name Products_shipped in worksheet Transport1. "]
["2) The logical constraint is"]
[null,"Products_shipped >= 0  via the Assume Non-Negative option"]
["The other two constraints are"]
[null,"Total_received >= Demand"]
[null,"Total_shipped <= Capacity"]
["3) The objective is to minimize cost. This is given the name Total_cost."]
[]
["Remarks"]
["This is a transportation problem in its simplest form. Still, this type of model is widely used to save many "]
["thousands of dollars each year."]
["In worksheet Transport2 we will consider a 2-level transportation, and in worksheet Transport3 we expand this to "]
["a multi-product, 2-level transportation problem."]

{"sheet":"Transport2","index":2,"n_rows":72,"n_cols":8,"rows_kept":72,"flags":["formulas_cached_only"]}
["Transportation Problem 2 (2-stage-transport)"]
[]
["Minimize the costs of shipping goods from factories to warehouses and customers, and   "]
["warehouses to customers, while not exceeding the supply available from each factory or "]
["the capacity of each warehouse, and meeting the demand from each customer."]
[]
["Cost of shipping ($ per product)"]
[null,"Destinations"]
[null,"Warehouse 1","Warehouse 2","Warehouse 3","Warehouse 4"]
["Factory 1",{"v":0.5,"d":"$0.50"},{"v":0.5,"d":"$0.50"},{"v":1,"d":"$1.00"},{"v":0.2,"d":"$0.20"}]
["Factory 2",{"v":1.5,"d":"$1.50"},{"v":0.3,"d":"$0.30"},{"v":0.5,"d":"$0.50"},{"v":0.2,"d":"$0.20"}]
[]
[null,"Customer 1","Customer 2","Customer 3","Customer 4","Customer 5"]
["Factory 1",{"v":1.75,"d":"$1.75"},{"v":2.5,"d":"$2.50"},{"v":1.5,"d":"$1.50"},{"v":2,"d":"$2.00"},{"v":1.5,"d":"$1.50"}]
["Factory 2",{"v":2,"d":"$2.00"},{"v":2.5,"d":"$2.50"},{"v":2.5,"d":"$2.50"},{"v":1.5,"d":"$1.50"},{"v":1,"d":"$1.00"}]
[]
[null,"Customer 1","Customer 2","Customer 3","Customer 4","Customer 5"]
["Warehouse 1",{"v":1.5,"d":"$1.50"},{"v":1.5,"d":"$1.50"},{"v":0.5,"d":"$0.50"},{"v":1.5,"d":"$1.50"},{"v":3,"d":"$3.00"}]
["Warehouse 2",{"v":1,"d":"$1.00"},{"v":0.5,"d":"$0.50"},{"v":0.5,"d":"$0.50"},{"v":1,"d":"$1.00"},{"v":0.5,"d":"$0.50"}]
["Warehouse 3",{"v":1,"d":"$1.00"},{"v":1.5,"d":"$1.50"},{"v":2,"d":"$2.00"},{"v":2,"d":"$2.00"},{"v":0.5,"d":"$0.50"}]
["Warehouse 4",{"v":2.5,"d":"$2.50"},{"v":1.5,"d":"$1.50"},{"v":0.2,"d":"$0.20"},{"v":1.5,"d":"$1.50"},{"v":0.5,"d":"$0.50"}]
[]
["Number of products shipped"]
[null,"Warehouse 1","Warehouse 2","Warehouse 3","Warehouse 4","Total"]
["Factory 1",0,{"v":19999.999995932736,"d":"20,000"},0,{"v":14999.999999672582,"d":"15,000"},{"v":34999.99999560532,"d":"35,000"}]
["Factory 2",{"v":44999.999999999556,"d":"45,000"},0,{"v":10999.99993067349,"d":"11,000"},0,{"v":55999.999930673046,"d":"56,000"}]
["Total",{"v":44999.999999999556,"d":"45,000"},{"v":19999.999995932736,"d":"20,000"},{"v":10999.99993067349,"d":"11,000"},{"v":14999.999999672582,"d":"15,000"}]
["Capacity",{"v":45000,"d":"45,000"},{"v":20000,"d":"20,000"},{"v":30000,"d":"30,000"},{"v":15000,"d":"15,000"}]
[]
[null,"Customer 1","Customer 2","Customer 3","Customer 4","Customer 5","Total"]
["Factory 1",{"v":9999.999999992724,"d":"10,000"},0,0,{"v":15000.00007000304,"d":"15,000"},0,{"v":25000.000069995764,"d":"25,000"},"Factory"]
["Factory 2",0,0,0,0,0,0,"Capacity"]
[{"$nulls":3},"Total products shipped out of factory 1",null,null,{"v":60000.00006560108,"d":"60,000"},{"v":60000,"d":"60,000"}]
[{"$nulls":3},"Total products shipped out of factory 2",null,null,{"v":55999.999930673046,"d":"56,000"},{"v":60000,"d":"60,000"}]
[]
[null,"Customer 1","Customer 2","Customer 3","Customer 4","Customer 5","Total"]
["Warehouse 1",0,{"v":23000,"d":"23,000"},0,{"v":16999.999929997335,"d":"17,000"},{"v":5000.000068984534,"d":"5,000"},{"v":44999.99999898187,"d":"45,000"}]
["Warehouse 2",{"v":19999.99999593274,"d":"20,000"},0,0,0,0,{"v":19999.99999593274,"d":"20,000"}]
["Warehouse 3",0,0,0,0,{"v":10999.99993067349,"d":"11,000"},{"v":10999.99993067349,"d":"11,000"}]
["Warehouse 4",0,0,{"v":14999.999999678133,"d":"15,000"},0,0,{"v":14999.999999678133,"d":"15,000"}]
["Total",{"v":29999.999995925464,"d":"30,000"},{"v":23000,"d":"23,000"},{"v":14999.999999678133,"d":"15,000"},{"v":32000.000000000375,"d":"32,000"},{"v":15999.999999658023,"d":"16,000"}]
["Demands",{"v":30000,"d":"30,000"},{"v":23000,"d":"23,000"},{"v":15000,"d":"15,000"},{"v":32000,"d":"32,000"},{"v":16000,"d":"16,000"}]
[]
["Total cost of shipping",null,{"v":237000.000166385,"d":"$237,000"}]
[]
[]
["Problem"]
["A company has 2 factories, 4 warehouses and 5 customers. It wants to minimize the cost of shipping its "]
["product from the factories to the warehouses, the factories to the customers, and the warehouses to the "]
["customers. The number of products received by a warehouse from the factory should be the same as the"]
["number of products leaving the warehouse to the customers. How should the company distribute the products?"]
[]
["Solution"]
["1) The variables are the number of products to ship from the factories to the warehouses, the factories to the "]
["customers, and the warehouses to the customers. These are defined in worksheet Transport2 as "]
["Factory_to_warehouse, Factory_to_customer, Warehouse_customer. "]
["2) The logical constraints are all defined via the Assume Non-Negative option:"]
[null,"Factory_to_warehouse >= 0"]
[null,"Factory_to_customer >= 0"]
[null,"Warehouse_customer >= 0"]
["The other constraints are"]
[null,"Total_from_factory <= Factory_capacity"]
[null,"Total_to_customer >= Demand"]
[null,"Total_to_warehouse <= Warehouse_capacity"]
[null,"Total_to_warehouse = Total_from_warehouse"]
["3) The objective is to minimize cost, given by Total_cost."]
[]
["Remarks"]
["Please note that the last constraint must be an '=' , because otherwise products would start piling up at the "]
["warehouse. It would be possible to make this a multi-period model where storage at the warehouses would be "]
["possible and even desired, if transportation prices would fluctuate during the different time periods.  In "]
["worksheet Transport3 we will look at a multi-product situation."]

{"sheet":"Transport3","index":3,"n_rows":108,"n_cols":9,"rows_kept":108,"flags":["formulas_cached_only"]}
["Transportation Problem 3 (2-stage-transport, multi-commodity)"]
[]
["Minimize the costs of shipping 3 different goods from factories to warehouses and customers, and   "]
["warehouses to customers, while not exceeding the supply available from each factory or "]
["the capacity of each warehouse, and meeting the demand from each customer."]
[]
["Cost of shipping ($ per product)"]
[null,null,"Destinations"]
[null,null,"Warehouse 1","Warehouse 2","Warehouse 3","Warehouse 4"]
["Factory 1","Product 1",{"v":0.5,"d":"$0.50"},{"v":0.5,"d":"$0.50"},{"v":1,"d":"$1.00"},{"v":0.2,"d":"$0.20"}]
[null,"Product 2",{"v":1,"d":"$1.00"},{"v":0.75,"d":"$0.75"},{"v":1.25,"d":"$1.25"},{"v":1.25,"d":"$1.25"}]
[null,"Product 3",{"v":0.75,"d":"$0.75"},{"v":1.25,"d":"$1.25"},{"v":1,"d":"$1.00"},{"v":0.8,"d":"$0.80"}]
["Factory 2","Product 1",{"v":1.5,"d":"$1.50"},{"v":0.3,"d":"$0.30"},{"v":0.5,"d":"$0.50"},{"v":0.2,"d":"$0.20"}]
[null,"Product 2",{"v":1.25,"d":"$1.25"},{"v":0.8,"d":"$0.80"},{"v":1,"d":"$1.00"},{"v":0.75,"d":"$0.75"}]
[null,"Product 3",{"v":1.4,"d":"$1.40"},{"v":0.9,"d":"$0.90"},{"v":0.95,"d":"$0.95"},{"v":1.1,"d":"$1.10"}]
[]
[null,null,"Customer 1","Customer 2","Customer 3","Customer 4","Customer 5"]
["Factory 1","Product 1",{"v":2.75,"d":"$2.75"},{"v":3.5,"d":"$3.50"},{"v":2.5,"d":"$2.50"},{"v":3,"d":"$3.00"},{"v":2.5,"d":"$2.50"}]
[null,"Product 2",{"v":2.5,"d":"$2.50"},{"v":3,"d":"$3.00"},{"v":2,"d":"$2.00"},{"v":2.75,"d":"$2.75"},{"v":2.6,"d":"$2.60"}]
[null,"Product 3",{"v":2.9,"d":"$2.90"},{"v":3,"d":"$3.00"},{"v":2.25,"d":"$2.25"},{"v":2.8,"d":"$2.80"},{"v":2.35,"d":"$2.35"}]
["Factory 2","Product 1",{"v":3,"d":"$3.00"},{"v":3.5,"d":"$3.50"},{"v":3.5,"d":"$3.50"},{"v":2.5,"d":"$2.50"},{"v":2,"d":"$2.00"}]
[null,"Product 2",{"v":2.25,"d":"$2.25"},{"v":2.95,"d":"$2.95"},{"v":2.2,"d":"$2.20"},{"v":2.5,"d":"$2.50"},{"v":2.1,"d":"$2.10"}]
[null,"Product 3",{"v":2.45,"d":"$2.45"},{"v":2.75,"d":"$2.75"},{"v":2.35,"d":"$2.35"},{"v":2.85,"d":"$2.85"},{"v":2.45,"d":"$2.45"}]
[]
[null,null,"Customer 1","Customer 2","Customer 3","Customer 4","Customer 5"]
["Warehouse 1","Product 1",{"v":1.5,"d":"$1.50"},{"v":0.8,"d":"$0.80"},{"v":0.5,"d":"$0.50"},{"v":1.5,"d":"$1.50"},{"v":3,"d":"$3.00"}]
[null,"Product 2",{"v":1,"d":"$1.00"},{"v":0.9,"d":"$0.90"},{"v":1.2,"d":"$1.20"},{"v":1.3,"d":"$1.30"},{"v":2.1,"d":"$2.10"}]
[null,"Product 3",{"v":1.25,"d":"$1.25"},{"v":0.7,"d":"$0.70"},{"v":1.1,"d":"$1.10"},{"v":0.8,"d":"$0.80"},{"v":1.6,"d":"$1.60"}]
["Warehouse 2","Product 1",{"v":1,"d":"$1.00"},{"v":0.5,"d":"$0.50"},{"v":0.5,"d":"$0.50"},{"v":1,"d":"$1.00"},{"v":0.5,"d":"$0.50"}]
[null,"Product 2",{"v":1.25,"d":"$1.25"},{"v":1,"d":"$1.00"},{"v":1,"d":"$1.00"},{"v":0.9,"d":"$0.90"},{"v":1.5,"d":"$1.50"}]
[null,"Product 3",{"v":1.1,"d":"$1.10"},{"v":1.1,"d":"$1.10"},{"v":0.9,"d":"$0.90"},{"v":1.4,"d":"$1.40"},{"v":1.75,"d":"$1.75"}]
["Warehouse 3","Product 1",{"v":1,"d":"$1.00"},{"v":1.5,"d":"$1.50"},{"v":2,"d":"$2.00"},{"v":2,"d":"$2.00"},{"v":0.5,"d":"$0.50"}]
[null,"Product 2",{"v":0.9,"d":"$0.90"},{"v":1.35,"d":"$1.35"},{"v":1.45,"d":"$1.45"},{"v":1.8,"d":"$1.80"},{"v":1,"d":"$1.00"}]
[null,"Product 3",{"v":1.25,"d":"$1.25"},{"v":1.2,"d":"$1.20"},{"v":1.75,"d":"$1.75"},{"v":1.7,"d":"$1.70"},{"v":0.85,"d":"$0.85"}]
["Warehouse 4","Product 1",{"v":2.5,"d":"$2.50"},{"v":1.5,"d":"$1.50"},{"v":0.6,"d":"$0.60"},{"v":1.5,"d":"$1.50"},{"v":0.5,"d":"$0.50"}]
[null,"Product 2",{"v":1.75,"d":"$1.75"},{"v":1.3,"d":"$1.30"},{"v":0.7,"d":"$0.70"},{"v":1.25,"d":"$1.25"},{"v":1.1,"d":"$1.10"}]
[null,"Product 3",{"v":1.5,"d":"$1.50"},{"v":1.1,"d":"$1.10"},{"v":1.5,"d":"$1.50"},{"v":1.1,"d":"$1.10"},{"v":0.9,"d":"$0.90"}]
[]
["Number of products shipped"]
[null,null,"Warehouse 1","Warehouse 2","Warehouse 3","Warehouse 4","Total"]
["Factory 1","Product 1",0,0,0,0,0]
[null,"Product 2",0,0,0,0,0]
[null,"Product 3",0,0,0,0,0]
["Factory 2","Product 1",0,0,0,0,0]
[null,"Product 2",0,0,0,0,0]
[null,"Product 3",0,0,0,0,0]
["Total","Product 1",0,0,0,0]
[null,"Product 2",0,0,0,0]
[null,"Product 3",0,0,0,0]
["Capacity","Product 1",{"v":35000,"d":"35,000"},{"v":20000,"d":"20,000"},{"v":30000,"d":"30,000"},{"v":15000,"d":"15,000"}]
[null,"Product 2",{"v":30000,"d":"30,000"},{"v":25000,"d":"25,000"},{"v":15000,"d":"15,000"},{"v":24000,"d":"24,000"}]
[null,"Product 3",{"v":20000,"d":"20,000"},{"v":20000,"d":"20,000"},{"v":25000,"d":"25,000"},{"v":20000,"d":"20,000"}]
[]
[null,null,"Customer 1","Customer 2","Customer 3","Customer 4","Customer 5","Total"]
["Factory 1","Product 1",0,0,0,0,0,0]
[null,"Product 2",0,0,0,0,0,0]
[null,"Product 3",0,0,0,0,0,0]
["Factory 2","Product 1",0,0,0,0,0,0]
[null,"Product 2",0,0,0,0,0,0]
[null,"Product 3",0,0,0,0,0,0]
[]
[{"$nulls":7},"Capacity"]
[null,null,"Total products shipped out of factory 1",null,null,"Product 1",0,{"v":90000,"d":"90,000"}]
[{"$nulls":5},"Product 2",0,{"v":100000,"d":"100,000"}]
[{"$nulls":5},"Product 3",0,{"v":80000,"d":"80,000"}]
[null,null,"Total products shipped out of factory 2",null,null,"Product 1",0,{"v":75000,"d":"75,000"}]
[{"$nulls":5},"Product 2",0,{"v":65000,"d":"65,000"}]
[{"$nulls":5},"Product 3",0,{"v":90000,"d":"90,000"}]
[]
[]
[null,null,"Customer 1","Customer 2","Customer 3","Customer 4","Customer 5","Total"]
["Warehouse 1","Product 1",0,0,0,0,0,0,0]
[null,"Product 2",0,0,0,0,0,0,0]
[null,"Product 3",0,0,0,0,0,0,0]
["Warehouse 2","Product 1",0,0,0,0,0,0,0]
[null,"Product 2",0,0,0,0,0,0,0]
[null,"Product 3",0,0,0,0,0,0,0]
["Warehouse 3","Product 1",0,0,0,0,0,0,0]
[null,"Product 2",0,0,0,0,0,0,0]
[null,"Product 3",0,0,0,0,0,0,0]
["Warehouse 4","Product 1",0,0,0,0,0,0,0]
[null,"Product 2",0,0,0,0,0,0,0]
[null,"Product 3",0,0,0,0,0,0,0]
["Total","Product 1",0,0,0,0,0]
[null,"Product 2",0,0,0,0,0]
[null,"Product 3",0,0,0,0,0]
["Demands","Product 1",{"v":30000,"d":"30,000"},{"v":23000,"d":"23,000"},{"v":15000,"d":"15,000"},{"v":32000,"d":"32,000"},{"v":16000,"d":"16,000"}]
[null,"Product 2",{"v":20000,"d":"20,000"},{"v":15000,"d":"15,000"},{"v":22000,"d":"22,000"},{"v":12000,"d":"12,000"},{"v":18000,"d":"18,000"}]
[null,"Product 3",{"v":25000,"d":"25,000"},{"v":22000,"d":"22,000"},{"v":16000,"d":"16,000"},{"v":20000,"d":"20,000"},{"v":25000,"d":"25,000"}]
[]
["Total cost of shipping",null,{"v":0,"d":"$0"}]
[]
[]
["Problem"]
["This model builds on model Transport2. Again, a company wants to minimize cost of shipping, but this time "]
["there are 3 products to distribute. How should the company distribute the products?"]
[]
["Solution"]
["The solution to the problem is identical to the one in Transport2. Notice that we have used the 'Insert Name "]
["Define' command to extend the model to a multiproduct problem. This way the variables and constraints are "]
["still the same as in Transport2. "]
[]
["Remarks"]
["Notice that this model delivers the same result as three separate models for the three products. There will be "]
["times however, that there are constraints that apply to more than one product. In that case it would not be "]
["desirable to have three different models and maybe even impossible. For an extension of this model, where the "]
["number of products made in the factories depends on the demand and distribution rather than being constant, "]
["see the worksheet Prodtran in this workbook."]

{"sheet":"Knapsack","index":4,"n_rows":54,"n_cols":8,"rows_kept":54,"flags":["formulas_cached_only"]}
["Partial Loading (Knapsack Problem)"]
["A fuel truck with 4 compartments needs to supply 3 different types of gas to a customer. "]
["When demand is not filled, the company loses $0.25 per gallon that is not delivered."]
["How should the truck be loaded to minimize loss?"]
[]
["Truck Specifications"]
[null,"Comp. 1","Comp. 2","Comp. 3","Comp. 4"]
["Size (gallons)",1200,800,1300,700]
["Loading of Compartments (1=yes, 0=no)"]
[null,"Comp. 1","Comp. 2","Comp. 3","Comp. 4"]
["Gas 1",0,0,0,0]
["Gas 2",0,0,0,0]
["Gas 3",0,0,0,0]
["Total",0,0,0,0]
["Amount (gallons)"]
[null,"Comp. 1","Comp. 2","Comp. 3","Comp. 4","Total","Demand","Loss"]
["Gas 1",0,0,0,0,0,1800,{"v":450,"d":"$450.00"}]
["Gas 2",0,0,0,0,0,1500,{"v":375,"d":"$375.00"}]
["Gas 3",0,0,0,0,0,1000,{"v":250,"d":"$250.00"}]
[{"$nulls":6},"Total Loss",{"v":1075,"d":"$1,075.00"}]
["Maximum Amount (gallons)"]
[null,"Comp. 1","Comp. 2","Comp. 3","Comp. 4"]
["Gas 1",0,0,0,0]
["Gas 2",0,0,0,0]
["Gas 3",0,0,0,0]
[]
[]
["Problem"]
["A fuel truck needs to supply 3 different kinds of gas to a customer. When demand is not filled the company "]
["loses $0.25 per gallon that is not delivered. The truck has 4 separate compartments of different size. How "]
["should the truck be loaded to minimize loss?"]
[]
["Solution"]
["1) The variables are the decisions to fill the compartments for each type of gas, and the amounts to be put in"]
["if the compartment is filled.  In worksheet Knapsack, these are given the name Gallons_loaded and "]
["Loading_decisions."]
["2) The logical constraints are"]
[null,"Gallons_loaded >= 0  via the Assume Non-Negative option"]
[null,"Loading_decisions = binary"]
["Since there can only be one kind of gas in any compartment we have"]
[null,"Total_decisions <= 1"]
["The size limitations of the truck give"]
[null,"Gallons_loaded <= Maximum_gallons"]
["We don't want to load more than needed. This gives"]
[null,"Total_gallons <= Demand"]
["3) The objective is to minimize the loss. This is given the name Total_loss."]
[]
["Remarks"]
["It is often possible to have different objectives in these types of problems. We might, for instance, want to "]
["minimize the wasted space in the truck in this example. Knapsack problems are characterized by a series of"]
["0-1 integer variables with a single capacity constraint. If someone goes camping and his backpack can hold "]
["only a certain amount of weight, what items should the camper bring?  He should try to optimize the value "]
["of the items while not exceeding the weight allowed by the backpack. There is a wide set of problems that "]
["fall into this category. "]

{"sheet":"Facility","index":5,"n_rows":52,"n_cols":8,"rows_kept":52,"flags":["formulas_cached_only"]}
["Facility Location"]
["A company currently ships its product from 5 plants to 4 warehouses. It is considering closing "]
["one or more plants to reduce cost. What plant(s) should the company close, in order to"]
["minimize transportation and fixed costs?"]
[]
["Transportation Costs (per 1000 products)"]
[null,"Plant 1","Plant 2","Plant 3","Plant 4","Plant 5"]
["Warehouse 1",{"v":4000,"d":"$4,000"},{"v":2000,"d":"$2,000"},{"v":3000,"d":"$3,000"},{"v":2500,"d":"$2,500"},{"v":4500,"d":"$4,500"}]
["Warehouse 2",{"v":2500,"d":"$2,500"},{"v":2600,"d":"$2,600"},{"v":3400,"d":"$3,400"},{"v":3000,"d":"$3,000"},{"v":4000,"d":"$4,000"}]
["Warehouse 3",{"v":1200,"d":"$1,200"},{"v":1800,"d":"$1,800"},{"v":2600,"d":"$2,600"},{"v":4100,"d":"$4,100"},{"v":3000,"d":"$3,000"}]
["Warehouse 4",{"v":2200,"d":"$2,200"},{"v":2600,"d":"$2,600"},{"v":3100,"d":"$3,100"},{"v":3700,"d":"$3,700"},{"v":3200,"d":"$3,200"}]
[]
["Open/close decision variables"]
[null,"Plant 1","Plant 2","Plant 3","Plant 4","Plant 5"]
["Decision",0,0,0,0,0]
[]
["Number of products to ship (per 1000)"]
[null,"Plant 1","Plant 2","Plant 3","Plant 4","Plant 5","Total","Demand"]
["Warehouse 1",0,0,0,0,0,0,15]
["Warehouse 2",0,0,0,0,0,0,18]
["Warehouse 3",0,0,0,0,0,0,14]
["Warehouse 4",0,0,0,0,0,0,20]
["Total",0,0,0,0,0]
["Capacity",0,0,0,0,0]
["Distr. Cost",{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"}]
["Fixed Cost",{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"}]
["Total Cost",{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"},{"v":0,"d":"$0"}]
[]
[]
["Problem"]
["A company currently ships products from 5 plants to 4 warehouses. The company is considering the option of  "]
["closing down one or more plants. This would increase distribution cost but perhaps lower overall cost. What "]
["plants, if any, should the company close?"]
[]
["Solution"]
["1) The variables are the decisions to open or close the plants, and the number of products that should be "]
["shipped from the plants that are open to the warehouses.  In worksheet Facility these are given the names "]
["Open_or_close and Products_shipped."]
["2) The logical constraints are"]
[null,"Products_shipped >= 0  via the Assume Non-Negative option"]
[null,"Open_or_close = binary"]
["The products made can not exceed the capacity of the plants and the number shipped should meet the "]
["demand.  This gives"]
[null,"Products_made <= Capacity"]
[null,"Total_shipped >= Demand"]
["3) The objective is to minimize cost. This is given the name Total_cost on the worksheet."]
[]
["Remarks"]
["It is often possible to increase the capacity of a plant. This could be worked into the model with additional 0-1 "]
["or binary integer variables. The Solver would find out if it would be profitable to extend the capacity of a plant. "]
["It could also be interesting to see if it would be profitable to open another warehouse. An example of this can "]
["be found, in somewhat modified form, in the capacity planning model in the Finance Examples workbook."]

{"sheet":"Prodtran","index":6,"n_rows":133,"n_cols":8,"rows_kept":133,"flags":["formulas_cached_only"]}
["Production Transportation Problem (2-stage-transport, multi-commodity)"]
[]
["Minimize the costs of producing 3 different goods, and shipping them from factories to warehouses and   "]
["customers, and warehouses to customers, while not exceeding the supply available from each factory or "]
["the capacity of each warehouse, and meeting the demand from each customer."]
[]
["Cost to make products"]
[null,"Product 1","Product 2","Product 3"]
["Factory 1",{"v":4,"d":"$4"},{"v":5,"d":"$5"},{"v":3,"d":"$3"}]
["Factory 2",{"v":2,"d":"$2"},{"v":8,"d":"$8"},{"v":6,"d":"$6"}]
[]
[null,"Product 1","Product 2","Product 3","Cost"]
["Factory 1",0,0,0,{"v":0,"d":"$0"}]
["Factory 2",0,0,0,{"v":0,"d":"$0"}]
[{"$nulls":3},"Total Cost",{"v":0,"d":"$0"}]
[]
["Cost of shipping ($ per product)"]
[null,null,"Destinations"]
[null,null,"Warehouse 1","Warehouse 2","Warehouse 3","Warehouse 4"]
["Factory 1","Product 1",{"v":0.5,"d":"$0.50"},{"v":0.5,"d":"$0.50"},{"v":1,"d":"$1.00"},{"v":0.2,"d":"$0.20"}]
[null,"Product 2",{"v":1,"d":"$1.00"},{"v":0.75,"d":"$0.75"},{"v":1.25,"d":"$1.25"},{"v":1.25,"d":"$1.25"}]
[null,"Product 3",{"v":0.75,"d":"$0.75"},{"v":1.25,"d":"$1.25"},{"v":1,"d":"$1.00"},{"v":0.8,"d":"$0.80"}]
["Factory 2","Product 1",{"v":1.5,"d":"$1.50"},{"v":0.3,"d":"$0.30"},{"v":0.5,"d":"$0.50"},{"v":0.2,"d":"$0.20"}]
[null,"Product 2",{"v":1.25,"d":"$1.25"},{"v":0.8,"d":"$0.80"},{"v":1,"d":"$1.00"},{"v":0.75,"d":"$0.75"}]
[null,"Product 3",{"v":1.4,"d":"$1.40"},{"v":0.9,"d":"$0.90"},{"v":0.95,"d":"$0.95"},{"v":1.1,"d":"$1.10"}]
[]
[null,null,"Customer 1","Customer 2","Customer 3","Customer 4","Customer 5"]
["Factory 1","Product 1",{"v":2.75,"d":"$2.75"},{"v":3.5,"d":"$3.50"},{"v":2.5,"d":"$2.50"},{"v":3,"d":"$3.00"},{"v":2.5,"d":"$2.50"}]
[null,"Product 2",{"v":2.5,"d":"$2.50"},{"v":3,"d":"$3.00"},{"v":2,"d":"$2.00"},{"v":2.75,"d":"$2.75"},{"v":2.6,"d":"$2.60"}]
[null,"Product 3",{"v":2.9,"d":"$2.90"},{"v":3,"d":"$3.00"},{"v":2.25,"d":"$2.25"},{"v":2.8,"d":"$2.80"},{"v":2.35,"d":"$2.35"}]
["Factory 2","Product 1",{"v":3,"d":"$3.00"},{"v":3.5,"d":"$3.50"},{"v":3.5,"d":"$3.50"},{"v":2.5,"d":"$2.50"},{"v":2,"d":"$2.00"}]
[null,"Product 2",{"v":2.25,"d":"$2.25"},{"v":2.95,"d":"$2.95"},{"v":2.2,"d":"$2.20"},{"v":2.5,"d":"$2.50"},{"v":2.1,"d":"$2.10"}]
[null,"Product 3",{"v":2.45,"d":"$2.45"},{"v":2.75,"d":"$2.75"},{"v":2.35,"d":"$2.35"},{"v":2.85,"d":"$2.85"},{"v":2.45,"d":"$2.45"}]
[]
[null,null,"Customer 1","Customer 2","Customer 3","Customer 4","Customer 5"]
["Warehouse 1","Product 1",{"v":1.5,"d":"$1.50"},{"v":0.8,"d":"$0.80"},{"v":0.5,"d":"$0.50"},{"v":1.5,"d":"$1.50"},{"v":3,"d":"$3.00"}]
[null,"Product 2",{"v":1,"d":"$1.00"},{"v":0.9,"d":"$0.90"},{"v":1.2,"d":"$1.20"},{"v":1.3,"d":"$1.30"},{"v":2.1,"d":"$2.10"}]
[null,"Product 3",{"v":1.25,"d":"$1.25"},{"v":0.7,"d":"$0.70"},{"v":1.1,"d":"$1.10"},{"v":0.8,"d":"$0.80"},{"v":1.6,"d":"$1.60"}]
["Warehouse 2","Product 1",{"v":1,"d":"$1.00"},{"v":0.5,"d":"$0.50"},{"v":0.5,"d":"$0.50"},{"v":1,"d":"$1.00"},{"v":0.5,"d":"$0.50"}]
[null,"Product 2",{"v":1.25,"d":"$1.25"},{"v":1,"d":"$1.00"},{"v":1,"d":"$1.00"},{"v":0.9,"d":"$0.90"},{"v":1.5,"d":"$1.50"}]
[null,"Product 3",{"v":1.1,"d":"$1.10"},{"v":1.1,"d":"$1.10"},{"v":0.9,"d":"$0.90"},{"v":1.4,"d":"$1.40"},{"v":1.75,"d":"$1.75"}]
["Warehouse 3","Product 1",{"v":1,"d":"$1.00"},{"v":1.5,"d":"$1.50"},{"v":2,"d":"$2.00"},{"v":2,"d":"$2.00"},{"v":0.5,"d":"$0.50"}]
[null,"Product 2",{"v":0.9,"d":"$0.90"},{"v":1.35,"d":"$1.35"},{"v":1.45,"d":"$1.45"},{"v":1.8,"d":"$1.80"},{"v":1,"d":"$1.00"}]
[null,"Product 3",{"v":1.25,"d":"$1.25"},{"v":1.2,"d":"$1.20"},{"v":1.75,"d":"$1.75"},{"v":1.7,"d":"$1.70"},{"v":0.85,"d":"$0.85"}]
["Warehouse 4","Product 1",{"v":2.5,"d":"$2.50"},{"v":1.5,"d":"$1.50"},{"v":0.6,"d":"$0.60"},{"v":1.5,"d":"$1.50"},{"v":0.5,"d":"$0.50"}]
[null,"Product 2",{"v":1.75,"d":"$1.75"},{"v":1.3,"d":"$1.30"},{"v":0.7,"d":"$0.70"},{"v":1.25,"d":"$1.25"},{"v":1.1,"d":"$1.10"}]
[null,"Product 3",{"v":1.5,"d":"$1.50"},{"v":1.1,"d":"$1.10"},{"v":1.5,"d":"$1.50"},{"v":1.1,"d":"$1.10"},{"v":0.9,"d":"$0.90"}]
[]
["Number of products shipped"]
[null,null,"Warehouse 1","Warehouse 2","Warehouse 3","Warehouse 4","Total"]
["Factory 1","Product 1",0,0,0,0,0]
[null,"Product 2",0,0,0,0,0]
[null,"Product 3",0,0,0,0,0]
["Factory 2","Product 1",0,0,0,0,0]
[null,"Product 2",0,0,0,0,0]
[null,"Product 3",0,0,0,0,0]
["Total","Product 1",0,0,0,0]
[null,"Product 2",0,0,0,0]
[null,"Product 3",0,0,0,0]
["Capacity","Product 1",{"v":35000,"d":"35,000"},{"v":20000,"d":"20,000"},{"v":30000,"d":"30,000"},{"v":15000,"d":"15,000"}]
[null,"Product 2",{"v":30000,"d":"30,000"},{"v":25000,"d":"25,000"},{"v":15000,"d":"15,000"},{"v":24000,"d":"24,000"}]
[null,"Product 3",{"v":20000,"d":"20,000"},{"v":20000,"d":"20,000"},{"v":25000,"d":"25,000"},{"v":20000,"d":"20,000"}]
[]
[null,null,"Customer 1","Customer 2","Customer 3","Customer 4","Customer 5","Total"]
["Factory 1","Product 1",0,0,0,0,0,0]
[null,"Product 2",0,0,0,0,0,0]
[null,"Product 3",0,0,0,0,0,0]
["Factory 2","Product 1",0,0,0,0,0,0]
[null,"Product 2",0,0,0,0,0,0]
[null,"Product 3",0,0,0,0,0,0]
[]
[{"$nulls":7},"Capacity"]
[null,null,"Total products shipped out of factory 1",null,null,"Product 1",0,0]
[{"$nulls":5},"Product 2",0,0]
[{"$nulls":5},"Product 3",0,0]
[null,null,"Total products shipped out of factory 2",null,null,"Product 1",0,0]
[{"$nulls":5},"Product 2",0,0]
[{"$nulls":5},"Product 3",0,0]
[]
[null,null,"Customer 1","Customer 2","Customer 3","Customer 4","Customer 5","Total"]
["Warehouse 1","Product 1",0,0,0,0,0,0]
[null,"Product 2",0,0,0,0,0,0]
[null,"Product 3",0,0,0,0,0,0]
["Warehouse 2","Product 1",0,0,0,0,0,0]
[null,"Product 2",0,0,0,0,0,0]
[null,"Product 3",0,0,0,0,0,0]
["Warehouse 3","Product 1",0,0,0,0,0,0]
[null,"Product 2",0,0,0,0,0,0]
[null,"Product 3",0,0,0,0,0,0]
["Warehouse 4","Product 1",0,0,0,0,0,0]
[null,"Product 2",0,0,0,0,0,0]
[null,"Product 3",0,0,0,0,0,0]
["Total","Product 1",0,0,0,0,0]
[null,"Product 2",0,0,0,0,0]
[null,"Product 3",0,0,0,0,0]
["Demands","Product 1",{"v":30000,"d":"30,000"},{"v":23000,"d":"23,000"},{"v":15000,"d":"15,000"},{"v":32000,"d":"32,000"},{"v":16000,"d":"16,000"}]
[null,"Product 2",{"v":20000,"d":"20,000"},{"v":15000,"d":"15,000"},{"v":22000,"d":"22,000"},{"v":12000,"d":"12,000"},{"v":18000,"d":"18,000"}]
[null,"Product 3",{"v":25000,"d":"25,000"},{"v":22000,"d":"22,000"},{"v":16000,"d":"16,000"},{"v":20000,"d":"20,000"},{"v":25000,"d":"25,000"}]
[]
["Total cost of shipping",null,{"v":0,"d":"$0"}]
["Total cost of production",null,{"v":0,"d":"$0"}]
[null,"Total Cost",{"v":0,"d":"$0"}]
[]
[]
["Problem "]
["A company wants to minimize the cost of shipping three different products from factories to warehouses and customers "]
["and from warehouses to customers. The production of each product at each plant depends on the distribution. How many "]
["products should each factory produce and how should the products be distributed in order to minimize total cost while "]
["meeting demand?"]
[]
["Solution"]
["Notice that this is an extension of the transportation model as seen in the Transport3 worksheet. This time the factories do "]
["not produce a fixed amount. The amounts produced are now variables."]
["1) The variables are the number of products to make in the factories, the number of products to ship from factories to "]
["warehouses, factories to customers, and warehouses to customers. In worksheet Prodtran these are given the names "]
["Products_made, Factory_to_warehouse, Factory_to_customer, and Warehouse_to_customer. "]
["2) The logical constraints are all defined via the Assume Non-Negative option:"]
[null,"Products_made >= 0"]
[null,"Factory_to_warehouse >= 0"]
[null,"Factory_to_customer >= 0"]
[null,"Warehouse_to_customer >= 0"]
["The other constraints are"]
[null,"Total_from_factory <= Factory_capacity"]
[null,"Total_to_customer >= Demand"]
[null,"Total_to_warehouse <= Warehouse_capacity"]
[null,"Total_to_warehouse = Total_from_warehouse"]
["3) The objective is to minimize cost.  This is defined in the worksheet as Total_cost."]
[]
["Remarks"]
["This is one of the more complex models in this series of examples.  If the number of products, factories and warehouses "]
["becomes large, the number of variables in a model like this one becomes very large.  Also bear in mind the degree of "]
["coordination between business units that may be needed in order to implement the optimal solution.  For these reasons, "]
["some users prefer to split problems like this one into a set of smaller, simpler models. "]