problem set

QSO 635 Module Seven 1 Optimal transportation of goods in a supply chain is essential because it is important that:  The total transportation cost is minimized  The demand at warehouses is satisfied  The capacity at production facilities is not exceeded Read this article that describes in detail the role of transportation in a supply chain. Consider the following example that demonstrates optimization of transportation. There are production facilities in Battle C reek, Cherry Creek, and Dee Creek with annual capacities of 500 units, 400 units, and 600 units, respectively. The annual demands at warehouses in Worchester, Dorchester, and Rochester are 300 units, 700 units, and 400 units, respectively. The table below gives the unit transportation costs between the production facilities and the warehouses. Worchester Dorchester Rochester Battle Creek $20/unit $30/unit $13/unit Cherry Creek $10/unit $5/unit $17/unit Dee Creek $15/unit $12/unit $45/unit How much of the demand at each of the warehouses must be met by each of the production facilities? This problem can be modeled as a linear programming model as follows: Decision Variables Xbw = # of units to be transported from Battle Creek to Worchester Xcw = # o f units to be transported from Cherry Creek to Worchester Xdw = # of units to be transported from Dee Creek to Worchester Xbd = # of units to be transported from Battle Creek to Dorchester Xcd = # of units to be transported from Cherry Creek to Dorchester Xdd = # of units to be transported from Dee Creek to Dorchester 2 QSO 635 Module Seven Xbr = # of units to be transported from Battle Creek to Rochester Xcr = # of units to be transported from Cherry Creek to Rochester Xdr = # of units to be transported from Dee Creek to Rochest er Objective Function Minimize total annual transportation cost ($): = 20*Xbw + 10*Xcw + 15*Xdw + 30*Xbd + 5*Xcd + 12*Xdd + 13*Xbr + 17*Xcr + 45*Xdr Constraints Demand Constraints Xbw + Xcw + Xdw ≥ 300 (demand at Worchester) Xbd + Xcd + Xdd ≥ 700 (demand at Dorchester) Xbr + Xcr + Xdr ≥ 400 (demand at Rochester) Capacity Constraints Xbw + Xbd + Xbr ≤ 500 (capacity at Battle Creek) Xcw + Xcd + Xcr ≤ 400 (capacity at Cherry Creek) Xdw + Xdd + Xdr ≤ 600 (capacity at Dee Creek) Non -Negativity Constraints Xbw, Xcw, Xdw, Xbd, Xcd, Xdd, Xbr, Xcr, and Xdr are ≥ 0 Integer Constraints Xbw, Xcw, Xdw, Xbd, Xcd, Xdd, Xbr, Xcr, and Xdr are integers The above model can be solved using the Mi crosoft Excel Solver tool. Refer to the tutorial on how to use the Solver tool (click on each image for a better display). QSO 635 Module Seven 3 Using Excel Solver Microsoft Excel contains a tool called Solver that can be used to find the op timal solutions to linear programming problems. The Excel spreadsheet set up for the solver is given below.

For an explanation of steps to set up the spreadsheet and use Solver to find the optimal solution, view the document Linear Programming with Excel S olver document found in the Resources folder . After setting up the spreadsheet, Excel So lver is used to find the optimal solution to the problem. The Excel Solver dialog box set up for the example problem is given below. The steps for installing and using the Excel Solver are given in the Linear Programming with Excel Solver document. Excel Solver finds the optimal solution to the problem, which is displayed in the spreadshe et as well as presented through an Answer Report as shown below. For an explanation of the 4 QSO 635 Module Seven steps for producing the Answer Report for the example problem view the Linear Programming with Excel Solver document. The answer report above provides the same optimal solution as was obtained through the graphical method. It gives the slack for each constraint. Slack is the unutilized resource. Slacks for cowhide and production time constraints are zero, implying that all of the cowhide and production time available are being utilized. It can also be seen that of the production cap acity of 500 for baseballs, 360 is being utilized and 140 is the unutilized capacity. Similarly, for soft balls there is an unutilized production capacity of 200. Note: In some cases, you may receive the following error message when generating the Answer R eport, “Solver: an unexpected internal error occurred or the available memory was exhausted”. If that happens, you can view the optimal solution in the spreadsheet. After solving the problem by Excel Solver, the spreadsheet numbers change to the optimal so lution.