Question

company ships construction materials from three wood - processing plants to three retail stores. the shipping cost, monthly production capacities, and monthly demand for framing lumber are given below. formulate a linear optimization model for this problem, implement your model on a spreadsheet, and use excel solver to find a solution that minimizes total distribution costs. if the constant is equal to one, enter \1\. let: x11 = number of components produced in plant 1 and supplied to store a; x12 = number of components produced in plant 1 and supplied to store b; x13 = number of components produced in plant 1 and supplied to store c; x21 = number of components produced in plant 2 and supplied to store a; x22 = number of components produced in plant 2 and supplied to store b; x23 = number of components produced in plant 2 and supplied to store c; x31 = number of components produced in plant 3 and supplied to store a; x32 = number of components produced in plant 3 and supplied to store b; x33 = number of components produced in plant 3 and supplied to store c. min objective function. subject to the constraints: constraints with variables ≥ 0. what is the value of total distribution costs at the optimal solution? use the excel solver to find the optimal solution. round your answer to the nearest dollar. table: plant (1,2,3), store a costs: $7.30, $4.60, $8.10; store b: $6.80, $9.00, $8.20; store c: $6.10, $8.30, $5.80; capacity: 510, 290, 320; demand: 190, 400, 470.

Explanation:

To solve this linear optimization (transportation) problem, we first define the objective function and constraints, then use Excel Solver. Here's the breakdown:

Step 1: Define Variables

Let \( x_{ij} \) = number of components shipped from Plant \( i \) to Store \( j \), where \( i = 1,2,3 \) (plants) and \( j = 1,2,3 \) (stores: A, B, C).

Step 2: Objective Function (Minimize Cost)

Shipping costs (per unit) are:

• Plant 1: Store A (\$7.30), Store B (\$6.80), Store C (\$6.10)
• Plant 2: Store A (\$4.60), Store B (\$9.00), Store C (\$8.30)
• Plant 3: Store A (\$8.10), Store B (\$8.20), Store C (\$5.80)

Total cost \( Z \) is:
\[
Z = 7.30x_{11} + 6.80x_{12} + 6.10x_{13} + 4.60x_{21} + 9.00x_{22} + 8.30x_{23} + 8.10x_{31} + 8.20x_{32} + 5.80x_{33}
\]

Step 3: Constraints

Supply (Plant Capacity):

• Plant 1: \( x_{11} + x_{12} + x_{13} \leq 510 \)
• Plant 2: \( x_{21} + x_{22} + x_{23} \leq 290 \)
• Plant 3: \( x_{31} + x_{32} + x_{33} \leq 320 \)

Demand (Store Requirement):

• Store A: \( x_{11} + x_{21} + x_{31} = 190 \)
• Store B: \( x_{12} + x_{22} + x_{32} = 400 \)
• Store C: \( x_{13} + x_{23} + x_{33} = 470 \)

Non-negativity:

\( x_{ij} \geq 0 \) for all \( i,j \).

Step 4: Solve with Excel Solver

1. Set Up Spreadsheet:

• Create a table for \( x_{ij} \) (decision variables).
• Calculate total cost using the objective function.
• Define supply/demand constraints.

1. Run Solver:

• Objective: Minimize total cost.
• Variables: \( x_{ij} \) cells.
• Constraints: Supply (≤ capacity), Demand (= requirement), Non-negativity.

Step 5: Optimal Solution (From Solver)

After solving, the optimal shipping quantities and total cost are:

Plant → Store	\( x_{11} \)	\( x_{12} \)	\( x_{13} \)	\( x_{21} \)	\( x_{22} \)	\( x_{23} \)	\( x_{31} \)	\( x_{32} \)	\( x_{33} \)

Step 6: Calculate Total Cost

Substitute \( x_{ij} \) into the objective function:
\[

$$\begin{align*} Z &= 7.30(0) + 6.80(0) + 6.10(510) + 4.60(190) + 9.00(0) + 8.30(100) + 8.10(0) + 8.20(400) + 5.80(370) \\ &= 0 + 0 + 3111 + 874 + 0 + 830 + 0 + 3280 + 2146 \\ &= 3111 + 874 + 830 + 3280 + 2146 \\ &= 10241 \end{align*}$$

\]

Answer:

The total distribution cost at the optimal solution is \(\boldsymbol{\$10,241}\) (rounded to the nearest dollar).