Let's break down and solve the **Transportation Problem** step by step. --- ### **Step 1: Problem Restatement** - **Supplies (Factories):** - Factory 1: 1900 units - Factory 2: 1420 units - Factory 3: 2050 units - **Demands (Warehouses):** - Warehouse 1: 900 units - Warehouse 2: 1500 units - Warehouse 3: 600 units - Warehouse 4: 800 units - Warehouse 5: 1100 units - **Cost Table:** (in $/unit) - As shown in the image (copy below for easy reference): | | WH1 | WH2 | WH3 | WH4 | WH5 | Supply | |------------|-----|-----|-----|-----|-----|--------| | Factory 1 | 7 | 10 | 8 | 12 | 14 | 1900 | | Factory 2 | 9 | 8 | 11 | 8 | 12 | 1420 | | Factory 3 | 10 | 9 | 12 | 11 | 7 | 2050 | | **Demand** | 900 |1500 | 600 | 800 |1100 | | --- ### **Step 2: Updated Demand Constraints** Each warehouse **should not receive less than its demand, and not more than 1.2 times its demand**. - Warehouse 1: 900 ≤ x ≤ 1080 - Warehouse 2: 1500 ≤ x ≤ 1800 - Warehouse 3: 600 ≤ x ≤ 720 - Warehouse 4: 800 ≤ x ≤ 960 - Warehouse 5: 1100 ≤ x ≤ 1320 --- ### **Step 3: Check Total Supply vs Total Demand** - **Total Supply:** 1900 + 1420 + 2050 = 5370 - **Total Minimum Demand:** 900 + 1500 + 600 + 800 + 1100 = 4900 - **Total Maximum Demand (120%):** 1080 + 1800 + 720 + 960 + 1320 = 5880 Since **Total Supply (5370) ≥ Total Minimum Demand (4900)** and **≤ Total Maximum Demand (5880)**, it is feasible. --- ### **Step 4: Set Up the Model (Variables)** Let \( x_{ij} \) be the units shipped from Factory \( i \) to Warehouse \( j \): - \( x_{11} \): Factory 1 to Warehouse 1 - \( x_{12} \): Factory 1 to Warehouse 2 - ... and so on. --- ### **Step 5: Write Constraints** #### **Supply Constraints** - Factory 1: \( x_{11} + x_{12} + x_{13} + x_{14} + x_{15} = 1900 \) - Factory 2: \( x_{21} + x_{22} + x_{23} + x_{24} + x_{25} = 1420 \) - Factory 3: \( x_{31} + x_{32} + x_{33} + x_{34} + x_{35} = 2050 \) #### **Demand Constraints** - Warehouse 1: \( 900 \leq x_{11} + x_{21} + x_{31} \leq 1080 \) - Warehouse 2: \( 1500 \leq x_{12} + x_{22} + x_{32} \leq 1800 \) - Warehouse 3: \( 600 \leq x_{13} + x_{23} + x_{33} \leq 720 \) - Warehouse 4: \( 800 \leq x_{14} + x_{24} + x_{34} \leq 960 \) - Warehouse 5: \( 1100 \leq x_{15} + x_{25} + x_{35} \leq 1320 \) #### **Non-negativity** - All \( x_{ij} \geq 0 \) --- ### **Step 6: Objective Function** Minimize total cost: \[ \text{Minimize } Z = 7x_{11} + 10x_{12} + 8x_{13} + 12x_{14} + 14x_{15} + 9x_{21} + 8x_{22} + 11x_{23} + 8x_{24} + 12x_{25} + 10x_{31} + 9x_{32} + 12x_{33} + 11x_{34} + 7x_{35} \] --- ### **Step 7: Solve (Using Excel Solver)** **How to set up in Excel:** 1. Fill in decision variable cells (e.g., C25:G27 in your template). 2. Set up formulas to sum each row (for supply) and each column (for demand). 3. Enter objective function formula (sumproduct of cost and decision variables). 4. Set up Solver: - Set objective to minimize the total cost cell. - By changing the decision variable cells. - Add constraints: - Row sums equal to supply. - Column sums between minimum and maximum demand. - All variables ≥ 0. --- ### **Step 8: (Sample) Initial Solution (By Inspection/Heuristic)** Let's try to assign as much as possible to the cheapest cost cells within constraints: #### **Warehouse 5 (cheapest: F3, $7)** - Factory 3 to Warehouse 5: can supply up to 1320 units (max demand), so assign 1320 units. - F3 remaining: 2050 - 1320 = 730 units. #### **Warehouse 2 (cheapest: F2, $8)** - Factory 2 to Warehouse 2: can supply up to 1420 units (supply limit), but max demand is 1800 units. - Assign all of F2's supply to Warehouse 2: 1420 units. - Warehouse 2 demand remaining: 1800 - 1420 = 380 units. #### **Warehouse 2 (next cheapest: F3, $9)** - Factory 3 to Warehouse 2: assign 380 units (remaining demand), F3 now has 730 - 380 = 350 units left. #### **Warehouse 4 (cheapest: F2, $8)** - Max warehouse demand: 960. F2 exhausted. - Assign F3 to Warehouse 4: up to 350 units remaining. - Assign 350 units to Warehouse 4 from F3, F3 now has 0 left. Now, let's check if all demands are met: - **Warehouse 1:** Needs 900-1080 units. - **Warehouse 2:** 1800 units (1420 from F2, 380 from F3). - **Warehouse 3:** 600-720 units. - **Warehouse 4:** 800-960 units (350 from F3). - **Warehouse 5:** 1320 units (from F3). But F3 is exhausted, and F1 hasn't been used yet. Now allocate from F1: - F1 to Warehouse 1: assign 900 units (within limits), F1 left: 1900 - 900 = 1000. - F1 to Warehouse 3: assign 600 units (within limits), F1 left: 1000 - 600 = 400. - F1 to Warehouse 4: assign 450 units (to meet min demand of 800, because 350 already from F3), F1 left: 400 - 450 = -50 (not possible). So, let's adjust: - F1 to Warehouse 4: can only send 400 units (all that's left). So Warehouse 4 receives 350 from F3 + 400 from F1 = 750, which is less than the minimum demand 800. So, let's try F2 to Warehouse 4, but F2 is already exhausted. **Thus, a manual solution is complex. This is why you need Solver.** --- ### **Step 9: Final Answer** **To get the exact optimal solution:** - **Enter the data in Excel as described above** - **Use Solver to minimize cost** - **Apply all constraints** - **Solver will provide the optimal shipping plan** --- ## **Summary of Steps** 1. **Write variables for each shipping lane** 2. **Set supply constraints for each factory** 3. **Set demand constraints (min and max) for each warehouse** 4. **Write the objective function (minimize total cost)** 5. **Input into Excel Solver and solve** --- ### **Final Answer** - **The most shipping cost-effective schedule and the resulting shipping cost can only be exactly determined by using Solver, given the constraints.** - **Follow the above steps in Excel to get the precise answer.** - **If you want, upload the Excel file after running Solver for a detailed breakdown of the optimal shipping schedule and cost!** --- **If you need help with the Solver setup or interpretation of the solution, let me know!**