Let's break down the requirements step by step, starting with the mathematical formulation for both scenarios. --- ### 1) **Objective: Minimize Total Cost (Both Production and Fixed Costs)** #### **a) Formulate the Problem with Mathematical Models (MIP)** #### **Sets and Indices** - Plants/Sites: \(i \in \{\text{Toronto (T)}, \text{Halifax (H)}, \text{Winnipeg (W)}, \text{Vancouver (V)}\}\) - Markets: \(j \in \{\text{Atlantic (A)}, \text{Central (C)}, \text{Prairies (P)}, \text{Pacific (F)}, \text{Territories (R)}\}\) #### **Parameters** - \(c_{ij}\): Variable production and shipping cost from plant \(i\) to market \(j\) (see table) - \(f_i\): Fixed cost of opening plant \(i\) (in $1,000,000, from table) - \(d_j\): Demand in market \(j\) (in '000 units/year, from table) - \(K_i\): Capacity of plant \(i\) ('000 units/year) - Toronto: \(K_T = 1000\) - Halifax, Winnipeg, Vancouver (if opened): \(K_i = 1600\) #### **Decision Variables** - \(x_{ij}\): Number of units shipped from plant \(i\) to market \(j\) ('000 units/year) - \(y_i\): Binary variable, 1 if plant \(i\) is opened, 0 otherwise #### **Objective Function** Minimize total cost (fixed + variable): \[ \text{Minimize } Z = \sum_{i} f_i y_i + \sum_{i} \sum_{j} c_{ij} x_{ij} \] #### **Constraints** 1. **Demand Satisfaction**: \[ \sum_{i} x_{ij} = d_j \quad \forall j \] 2. **Plant Capacity**: \[ \sum_{j} x_{ij} \leq K_i y_i \quad \forall i \] 3. **Toronto is Always Open**: \[ y_{\text{Toronto}} = 1 \] 4. **At Most Two Additional Plants**: \[ \sum_{i \in \{\text{Halifax, Winnipeg, Vancouver}\}} y_i \leq 2 \] 5. **Non-negativity and Integer Constraints**: \[ x_{ij} \geq 0, \quad y_i \in \{0,1\} \] --- #### **b) Solve with Excel Solver** **Setup in Excel:** - Create a table for \(x_{ij}\) variables (plants vs. markets). - Create cells for \(y_i\) (binary, for Halifax, Winnipeg, Vancouver; Toronto = 1). - Objective cell: sumproduct of costs and \(x_{ij}\) + sumproduct of fixed costs and \(y_i\). - Constraints are implemented as formulas: - Sum of \(x_{ij}\) by market = demand. - Sum of \(x_{ij}\) by plant ≤ capacity × \(y_i\). - Toronto \(y_i = 1\). - Sum of \(y_i\) for new plants ≤ 2. - \(y_i\) binary; \(x_{ij}\) ≥ 0. **Solve with Excel's Solver using "Simplex LP" for LP relaxation or "GRG Nonlinear" for integer (MIP) solution.** --- ### 2) **Single Source Policy (Each Market Supplied by Only One Plant)** #### **c) Formulate with MIP** Add a new binary variable: - \(z_{ij}\): 1 if market \(j\) is supplied by plant \(i\), 0 otherwise. **Revised Decision Variables:** - \(x_{ij}\): Number of units shipped (as before) - \(y_i\): Binary (as before) - \(z_{ij}\): Binary #### **Revised Constraints** 1. **Each Market Assigned to Only One Plant**: \[ \sum_{i} z_{ij} = 1 \quad \forall j \] 2. **Flow Only if Assigned**: \[ x_{ij} = d_j z_{ij} \quad \forall i, j \] or, in some solvers, \(x_{ij} \leq d_j z_{ij}\) and \(\sum_i x_{ij} = d_j\). 3. **Plant Capacity**: \[ \sum_{j} x_{ij} \leq K_i y_i \quad \forall i \] 4. **At Most Two Additional Plants**: \[ \sum_{i \in \{\text{Halifax, Winnipeg, Vancouver}\}} y_i \leq 2 \] 5. **Plant Open if Used**: \[ z_{ij} \leq y_i \quad \forall i, j \] 6. **Non-negativity and Integer Constraints**: \[ x_{ij} \geq 0, \quad y_i, z_{ij} \in \{0,1\} \] **Objective is still:** \[ \text{Minimize } Z = \sum_{i} f_i y_i + \sum_{i} \sum_{j} c_{ij} x_{ij} \] --- #### **d) Solve with Excel Solver** - Add a table for \(z_{ij}\) (binary) in Excel. - Modify constraints as above. - Use "GRG Nonlinear" or "Evolutionary" algorithm for binary/integer programming in Excel Solver. --- ## **Summary Table for Variables and Constraints** | Symbol | Description | Type | |------------|----------------------------------------|---------| | \(x_{ij}\) | Units shipped from plant \(i\) to \(j\)| Real ≥0 | | \(y_i\) | 1 if plant \(i\) is open, 0 else | Binary | | \(z_{ij}\) | 1 if plant \(i\) supplies market \(j\) | Binary | --- ## **Final Notes** - **For part (b) and (d):** Setup in Excel as described, plug in the actual numbers from the table, and run Solver to get the specific site(s) chosen and the shipment plan. - **Optimal sites:** You will need to implement and run this in Excel to get the exact solution, but the formulation above gives you everything you need for the setup. --- **If you want, I can walk you through the actual Excel setup or provide a sample Excel layout. Let me know!**