Let's break down the solution step-by-step as requested: --- ## **Step 1: Two-Stage Stochastic Optimization Model** ### **Stage 1: Decision Variables (First Stage)** Let: - \( x_1 \): Number of Cavanagh edge hats to produce - \( x_2 \): Number of Ribbon edge hats to produce - \( x_3 \): Number of Folded edge hats to produce - \( x_4 \): Number of Raw edge hats to produce ### **Stage 2: Recourse Actions (Second Stage)** Demand in each scenario may be less than what is produced, so only the minimum of produced and demanded hats will be sold (excess production isn't sold). Let: - \( d_{is} \): Demand for hat type \(i\) in scenario \(s\) - \( p_s \): Probability of scenario \(s\) ### **Parameters** - Yarn constraint: \(2x_1 + 1.6x_2 + 1.2x_3 + 1x_4 \leq 650\) - Non-negativity: \(x_i \geq 0\) - Folded edge constraint: \(x_3 \leq 650\) ### **Objective: Expected Revenue** Let \( r_i \) be the revenue per hat type: - Cavanagh: $60, Ribbon: $45, Folded: $35, Raw edge: $25 Expected revenue is: \[ \text{Maximize} \sum_{s} p_s \left[ r_1 \min(x_1, d_{1s}) + r_2 \min(x_2, d_{2s}) + r_3 \min(x_3, d_{3s}) + r_4 \min(x_4, d_{4s}) \right] \] Where for each scenario \(s\): - \(d_{is}\) = demand for hat type \(i\) in scenario \(s\) --- ## **Step 2: Mathematical Model** ### **Variables:** - \(x_1, x_2, x_3, x_4\): Number of each hat type to produce ### **Objective:** \[ \text{Maximize} \quad 0.45 \left[ 60 \min(x_1, 60) + 45 \min(x_2, 180) + 35 \min(x_3, 250) + 25 \min(x_4, 360) \right] \\ + 0.30 \left[ 60 \min(x_1, 45) + 45 \min(x_2, 140) + 35 \min(x_3, 190) + 25 \min(x_4, 280) \right] \\ + 0.25 \left[ 60 \min(x_1, 28) + 45 \min(x_2, 100) + 35 \min(x_3, 140) + 25 \min(x_4, 250) \right] \] ### **Subject to:** \[ 2x_1 + 1.6x_2 + 1.2x_3 + x_4 \leq 650 \\ x_3 \leq 650 \\ x_i \geq 0 \quad \text{for } i=1,2,3,4 \] --- ## **Step 3: Excel Formulation** ### **Excel Steps:** 1. **Input Variables**: Cells for \(x_1, x_2, x_3, x_4\). 2. **Scenario Table**: For each scenario, calculate sold hats as `=MIN(produced, demand)`. 3. **Revenue Calculation**: For each scenario, calculate total revenue and then expected revenue as weighted sum. 4. **Constraints**: Use SUMPRODUCT to represent the yarn constraint. 5. **Solver**: Set the objective to maximize expected revenue, add constraints, and solve for integer or continuous values. --- ### **Sample Excel Layout** | Hat Type | Cavanagh | Ribbon | Folded | Raw Edge | |---------------|----------|--------|--------|----------| | Produced (xi) | [x1] | [x2] | [x3] | [x4] | | Yarn usage | 2 | 1.6 | 1.2 | 1 | #### **Scenario Table** | Scenario | Prob | Cav Demand | Rib Demand | Fold Demand | Raw Demand | |----------|------|------------|------------|-------------|------------| | High | 0.45 | 60 | 180 | 250 | 360 | | Medium | 0.30 | 45 | 140 | 190 | 280 | | Low | 0.25 | 28 | 100 | 140 | 250 | For each scenario, calculate: - Sold hats: `=MIN(Produced, Demand)` - Scenario Revenue: `=SUMPRODUCT(Sold hats, Revenue per hat)` - Expected Revenue: `=SUMPRODUCT(Scenario Revenue, Probability)` --- ## **Step 4: Final Mathematical Model** ### **Variables:** - \(x_1\): Cavanagh hats to produce - \(x_2\): Ribbon hats to produce - \(x_3\): Folded hats to produce - \(x_4\): Raw hats to produce ### **Objective:** \[ \text{Maximize Expected Revenue:} \] \[ = 0.45 [60 \min(x_1, 60) + 45 \min(x_2, 180) + 35 \min(x_3, 250) + 25 \min(x_4, 360)] \\ + 0.30 [60 \min(x_1, 45) + 45 \min(x_2, 140) + 35 \min(x_3, 190) + 25 \min(x_4, 280)] \\ + 0.25 [60 \min(x_1, 28) + 45 \min(x_2, 100) + 35 \min(x_3, 140) + 25 \min(x_4, 250)] \] ### **Subject to:** \[ 2x_1 + 1.6x_2 + 1.2x_3 + x_4 \leq 650 \\ x_3 \leq 650 \\ x_i \geq 0 \quad (i=1,2,3,4) \] --- ## **Step 5: Solve in Excel** 1. Set up cells for \(x_1\) to \(x_4\). 2. For each scenario, use `=MIN([Produced], [Demand])` for each hat type. 3. Calculate scenario revenues. 4. Calculate expected revenue as the sumproduct of scenario revenues and probabilities. 5. Use **Solver**: - Set objective: Max expected revenue - Changing cells: \(x_1, x_2, x_3, x_4\) - Constraints: Yarn, folded, non-negative - Solve for optimal solution. --- ### **Summary Table for Excel** | Variable | Cell | Description | |----------|------|----------------------| | x1 | B2 | Cavanagh produced | | x2 | C2 | Ribbon produced | | x3 | D2 | Folded produced | | x4 | E2 | Raw produced | **You can now input these values and constraints into Excel and use Solver to get the optimal values for \(x_1, x_2, x_3, x_4\).** --- **Let me know if you want an Excel file template or further guidance on how to enter this in Excel!**