A large retailer is planning to open a new store to add to its two existing retail stores (A & B). Three locations in California are currently under consideration for the new store: South Coast Plaza (SCP), Fashion Island (FI), and Laguna Hills (LH).
Each of these new stores under consideration will have a demand of 500 units. The product (construction building toy sets) is currently supplied from three warehouses (1, 2, and 3).
Warehouses 1, 2 and 3 can each supply a total of 600, 340, and 200 units, respectively. The following two tables provide additional Information regarding supply/demand and shipping costs per unit reflected in $.
Shipping costs/unit
|
SCP |
FI |
LH |
| Warehouse 1 |
$4.00 |
$7.00 |
$5.00 |
| Warehouse 2 |
$11.00 |
$6.00 |
$5.00 |
| Warehouse 3 |
$5.00 |
$5.00 |
$6.00 |
|
A |
B |
Supply |
| Warehouse 1 |
$15.00 |
$9.00 |
600 |
| Warehouse 2 |
$10.00 |
$7.00 |
340 |
| Warehouse 3 |
$14.00 |
$18.00 |
200 |
| Demand |
400 |
500 |
|
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.
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).
You may embed/insert your LP formulation anywhere in the SCP worksheet.