1. Determine which location, South Coast Plaza (SCP), Fashion Island (FI), or Laguna Hills (LH), provides the lowest transportation cost. Set up this problem as a linear programming (LP) problem and find the optimal solution using Excel’s Solver. Your spreadsheet file should have three worksheets in it named “SCP”, “FI”, and “LH”. Moreover, please make sure that all Solver parameters inputs (e.g., “Set objective cell”, “By Changing Variable cells”, and constraints) are all reflected in each corresponding Excel worksheet. Please view the Camtasia video file “Transportation Problem” as a guide (located in the “Readings & Resources” page of week 5).
2. Formulate this problem as a linear programming (LP) problem by identifying the decision variables (e.g., let X1 = no. of units shipped from A to B, etc.), objective function (e.g., Min $15X1 + $9X2 +….), and all relevant constraints for the South Coast Plaza (SCP) location only (e.g., the relevant supply and demand constraints) as per PowerPoint slides 33-38. You may embed/insert your LP formulation anywhere in the SCP worksheet.
3. Hint: You have 3 existing warehouses 1, 2 and 3 (your columns) with capacities/supply of 600, 340, and 200 units, respectively. These are your “supply” nodes/locations. You also have 2 existing demand nodes A and B (your rows) with demands of 400 and 500 units, respectively. The problem stated that we are evaluating opening a new store to the existing A and B locations (so we would be adding a 3rd row). The 3 new locations under consideration are “SCP”, “FI” and “LH”. Opening any of these new locations under consideration will have a demand of 500 units each. So, there will be three “3×3” setups (3 rows and 3 columns) that need to be inputted in Solver, whereby the only thing that will vary among the setups are the shipping costs per unit from each existing warehouse to each new location under consideration.