Let's break down the problem step by step and **formulate a balanced transportation model** to minimize the cost. We'll also outline how to set this up in Excel Solver. --- ## **Step 1: Define Decision Variables** Let \( x_{ij} \) be the number of tons of steel type \( j \) produced at plant \( i \). - \( i \) = 1, 2, 3 (plants) - \( j \) = 1, 2, 3 (steel types) So, you will have 9 variables: \( x_{11}, x_{12}, x_{13}, x_{21}, x_{22}, x_{23}, x_{31}, x_{32}, x_{33} \). --- ## **Step 2: Objective Function (Minimize Cost)** The cost coefficients are taken from the table. The total cost is: \[ \text{Minimize} \quad Z = 60x_{11} + 40x_{12} + 28x_{13} + 50x_{21} + 30x_{22} + 30x_{23} + 43x_{31} + 20x_{32} + 20x_{33} \] --- ## **Step 3: Constraints** #### **A. Demand Constraints (Requirement for Each Steel Type):** Each type of steel must meet 100 tons: \[ x_{11} + x_{21} + x_{31} = 100 \quad \text{(Steel 1)} \] \[ x_{12} + x_{22} + x_{32} = 100 \quad \text{(Steel 2)} \] \[ x_{13} + x_{23} + x_{33} = 100 \quad \text{(Steel 3)} \] #### **B. Supply Constraints (Production Capacity at Each Plant):** Each plant is open 40 hours/week = 2400 minutes/week. Each plant's total minutes used cannot exceed 2400: - **Plant 1:** \( 20(x_{11} + x_{12} + x_{13}) \leq 2400 \) - **Plant 2:** \( 16(x_{21} + x_{22} + x_{23}) \leq 2400 \) - **Plant 3:** \( 15(x_{31} + x_{32} + x_{33}) \leq 2400 \) #### **C. Non-negativity:** \[ x_{ij} \geq 0 \quad \text{for all } i, j \] --- ## **Step 4: Excel Solver Setup** ### **A. Decision Variable Table** | | Steel 1 | Steel 2 | Steel 3 | Row Sum (for time constraint) | |---------|---------|---------|---------|-------------------------------| | Plant 1 | x11 | x12 | x13 | =SUM(B2:D2) | | Plant 2 | x21 | x22 | x23 | =SUM(B3:D3) | | Plant 3 | x31 | x32 | x33 | =SUM(B4:D4) | ### **B. Cost Table** | | Steel 1 | Steel 2 | Steel 3 | |---------|---------|---------|---------| | Plant 1 | 60 | 40 | 28 | | Plant 2 | 50 | 30 | 30 | | Plant 3 | 43 | 20 | 20 | ### **C. Formulas** - **Total cost:** `=SUMPRODUCT(decision_variable_range, cost_table_range)` - **Demand constraints:** For each steel type (column), sum across plants and set equal to 100. - **Time constraints:** For each plant, sum all produced tons, multiply by respective time per ton, and set ≤ 2400. ### **D. Solver Parameters** - **Set Objective:** Total cost cell (Min) - **By Changing Variable Cells:** All \( x_{ij} \) cells - **Subject to Constraints:** - Row sums × time per ton ≤ 2400 (for each plant) - Column sums = 100 (for each steel type) - All \( x_{ij} \geq 0 \) --- ## **Step 5: Final Linear Program** \[ \begin{align*} \text{Minimize} \quad & 60x_{11} + 40x_{12} + 28x_{13} + 50x_{21} + 30x_{22} + 30x_{23} + 43x_{31} + 20x_{32} + 20x_{33} \\ \text{Subject to:} \quad & x_{11} + x_{21} + x_{31} = 100 \\ & x_{12} + x_{22} + x_{32} = 100 \\ & x_{13} + x_{23} + x_{33} = 100 \\ & 20(x_{11} + x_{12} + x_{13}) \leq 2400 \\ & 16(x_{21} + x_{22} + x_{23}) \leq 2400 \\ & 15(x_{31} + x_{32} + x_{33}) \leq 2400 \\ & x_{ij} \geq 0 \end{align*} \] --- ## **Step 6: Solve in Excel** 1. Input the data and constraints as described above. 2. Use **Excel Solver** to find the optimal values for \( x_{ij} \). 3. Solver will give you the allocation (how many tons of each steel type each plant should produce) and the minimum total cost. --- ### **If you want the numerical solution:** If you set this up in Excel Solver, you will get the optimal allocation. Here's a quick calculation to see if any plant's capacity is a limiting factor: - **Plant 1:** 2400 / 20 = 120 tons max - **Plant 2:** 2400 / 16 = 150 tons max - **Plant 3:** 2400 / 15 = 160 tons max Total capacity: 430 tons (more than 300 tons needed, so feasible). --- ## **Summary Table for Excel** | | Steel 1 | Steel 2 | Steel 3 | Max Tons (Time Constraint) | |---------|---------|---------|---------|---------------------------| | Plant 1 | ? | ? | ? | 120 | | Plant 2 | ? | ? | ? | 150 | | Plant 3 | ? | ? | ? | 160 | - Fill in the question marks with Solver's solution. --- ## **Final Answer** - **Formulation:** As above. - **Solution:** Use Excel Solver to fill in the optimal values for \( x_{ij} \) and calculate the minimum total cost. If you need the actual solution values and don't have Excel, let me know and I can solve it step-by-step here!

Certainly! Here's a comprehensive, step-by-step breakdown of solving the problem, including detailed calculations and the final optimal solution. --- ## **Step 1: Restate the problem** Steelco manufactures 3 types of steel (1, 2, 3) at 3 plants. Each week: - **Demand:** 100 tons of each type (total 300 tons). - **Time per ton at each plant:** | Plant | Time per ton (minutes) | |--------|------------------------| | 1 | 20 | | 2 | 16 | | 3 | 15 | - **Cost per ton at each plant:** | Plant | Steel 1 | Steel 2 | Steel 3 | |--------|---------|---------|---------| | 1 | \$60 | \$40 | \$28 | | 2 | \$50 | \$30 | \$30 | | 3 | \$43 | \$20 | \$20 | - **Capacity at each plant:** 40 hours/week = 2400 minutes/week. **Goal:** Minimize total cost while meeting demand and respecting capacity constraints. --- ## **Step 2: Decision Variables** Let: - \( x_{ij} \) = tons of steel type \( j \) produced at plant \( i \). Variables: | | Steel 1 | Steel 2 | Steel 3 | |-------------|---------|---------|---------| | Plant 1 | \( x_{11} \) | \( x_{12} \) | \( x_{13} \) | | Plant 2 | \( x_{21} \) | \( x_{22} \) | \( x_{23} \) | | Plant 3 | \( x_{31} \) | \( x_{32} \) | \( x_{33} \) | --- ## **Step 3: Objective Function** Minimize total cost: \[ Z = 60x_{11} + 40x_{12} + 28x_{13} + 50x_{21} + 30x_{22} + 30x_{23} + 43x_{31} + 20x_{32} + 20x_{33} \] --- ## **Step 4: Constraints** ### **Demand constraints:** Total steel of each type must be 100: \[ \begin{cases} x_{11} + x_{21} + x_{31} = 100 \quad \text{(Steel 1)} \\ x_{12} + x_{22} + x_{32} = 100 \quad \text{(Steel 2)} \\ x_{13} + x_{23} + x_{33} = 100 \quad \text{(Steel 3)} \\ \end{cases} \] ### **Capacity constraints:** Each plant's total production times must not exceed 2400 minutes: \[ \begin{cases} 20(x_{11} + x_{12} + x_{13}) \leq 2400 \Rightarrow x_{11} + x_{12} + x_{13} \leq 120 \\ 16(x_{21} + x_{22} + x_{23}) \leq 2400 \Rightarrow x_{21} + x_{22} + x_{23} \leq 150 \\ 15(x_{31} + x_{32} + x_{33}) \leq 2400 \Rightarrow x_{31} + x_{32} + x_{33} \leq 160 \\ \end{cases} \] ### **Non-negativity:** \[ x_{ij} \geq 0 \quad \forall i,j \] --- ## **Step 5: Feasible capacity check** Total production required: 300 tons (100 of each type). Sum of capacities: - Plant 1: 120 tons - Plant 2: 150 tons - Plant 3: 160 tons Total capacity: 120 + 150 + 160 = 430 tons, which exceeds demand, so feasible. --- ## **Step 6: Solving the problem** ### **Approach:** Due to the nature of costs, it's optimal to assign production starting with the cheapest options, respecting capacities and demand. ### **Step 7: Allocate production based on costs and capacities** #### **Step 7.1: Allocate Steel Type 3 (cheapest at Plant 3, \$20)** - Steel 3 demand: 100 tons. - Plant 3 capacity for total production: 160 tons. - Since Steel 3 is cheapest at Plant 3, assign as much as possible there: \[ x_{33} = \min(100, 160) = 100 \] Remaining capacity at Plant 3: \[ 160 - 100 = 60 \] Remaining demand for Steel 3: \[ 0 \quad (\text{demand satisfied}) \] --- #### **Step 7.2: Allocate Steel 2 (second cheapest at Plant 3, \$20), but Steel 2's cheapest is at Plant 3, but capacity already assigned.** - Steel 2 demand: 100 - Plant 3 capacity remaining: 60 - Assign as much as possible at Plant 3: \[ x_{32} = \min(100, 60) = 60 \] Remaining demand for Steel 2: \[ 100 - 60 = 40 \] Remaining capacity at Plant 3: \[ 60 - 60 = 0 \] - Remaining Steel 2 demand: 40 - Remaining capacity at Plant 3: 0 --- #### **Step 7.3: Allocate Steel 1** - Steel 1 demand: 100 - Plant 3 capacity: 0 (full) - Next cheapest at Plant 2: \$30 - Capacity at Plant 2: \[ 150 \] - Assign as much as possible at Plant 2: \[ x_{21} = \min(100, 150) = 100 \] Remaining demand for Steel 1: \[ 0 \] Remaining capacity at Plant 2: \[ 150 - 100 = 50 \] --- #### **Step 7.4: Allocate remaining Steel 2 demand (40) at Plant 2** - Capacity at Plant 2 remaining: 50 - Assign: \[ x_{22} = 40 \] Remaining capacity at Plant 2: \[ 50 - 40 = 10 \] --- #### **Step 7.5: Allocate remaining Steel 3 demand (0), done.** - Steel 3 demand is satisfied. --- #### **Step 7.6: Allocate remaining Steel 2 (40) at Plant 1** - Capacity at Plant 1: \[ 120 \] - Assign: \[ x_{12} = 40 \] Remaining capacity at Plant 1: \[ 120 - 40 = 80 \] --- #### **Step 7.7: Allocate Steel 1 at Plant 1** - Steel 1 demand: 0 left, already satisfied. - Remaining capacity at Plant 1: 80 - No further allocation needed. --- ### **Summary of allocations:** | | Steel 1 | Steel 2 | Steel 3 | Total at plant | Minutes used | |----------|---------|---------|---------|----------------|--------------| | Plant 1 | \( x_{11} \) | \( x_{12} \) | \( x_{13} \) | | | | | 0 | 40 | 0 | \( 0 + 40 + 0 = 40 \) | \( 20*0 + 16*40 + 15*0 = 640 \) | | Plant 2 | \( x_{21} \) | \( x_{22} \) | \( x_{23} \) | | | | | 100 | 40 | 0 | | | | | | | | Total: 150 | \( 16*150 = 2400 \) minutes | | Plant 3 | \( x_{31} \) | \( x_{32} \) | \( x_{33} \) | | | | | 0 | 60 | 100 | | | | | | | | Total: 160 | \( 15*(0+60+100) = 15*160 = 2400 \) minutes | --- ## **Step 8: Final check of capacities** - **Plant 1:** Total: 40 tons, time used: 640 min (within 2400). - **Plant 2:** Total: 150 tons, time used: 2400 min (full capacity). - **Plant 3:** Total: 160 tons, time used: 2400 min (full capacity). --- ## **Step 9: Calculate total cost** \[ \begin{aligned} Z &= 60 \times 0 + 40 \times 40 + 28 \times 0 \\ &\quad + 50 \times 100 + 30 \times 40 + 30 \times 0 \\ &\quad + 43 \times 0 + 20 \times 60 + 20 \times 100 \\ \end{aligned} \] Calculations: - Plant 1: \( 0 + 1600 + 0 = 1600 \) - Plant 2: \( 5000 + 1200 + 0 = 6200 \) - Plant 3: \( 0 + 1200 + 2000 = 3200 \) Total cost: \[ Z = 1600 + 6200 + 3200 = \boxed{\$10,200} \] --- ## **## **Final Answer:** | **Production Plan** | **Tons** | |---------------------|----------| | **Plant 1:** | \( x_{12} = 40 \) tons of Steel 2 | | **Plant 2:** | \( x_{21} = 100 \) tons of Steel 1, \( x_{22} = 40 \) tons of Steel 2 | | **Plant 3:** | \( x_{32} = 60 \) tons of Steel 2, \( x_{33} = 100 \) tons of Steel 3 | **Total minimum cost:** **\$10,200** --- ## **Summary:** - Assign Steel 3 entirely at Plant 3. - Assign Steel 2 primarily at Plant 3, then at Plant 2, and some at Plant 1. - Assign Steel 1 at Plant 2. - Capacities are respected, and total cost is minimized. --- **Note:** For precise optimal solutions, use Excel Solver with the formulation above. The above is an approximate optimal allocation based on cost minimization logic. If you'd like, I can help you set up the Excel sheet step-by-step to confirm this solution!