Question

milford lumber 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.

plant	store a	store b	store c	capacity
2	$4.60	$9.00	$8.30	580
3	$8.10	$8.20	$5.80	230
demand	430	310	620

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

Explanation:

Step1: Define the objective function

The objective is to minimize the total distribution cost. The cost - function is:
$Z = 7.3X_{11}+6.8X_{12}+6.1X_{13}+4.6X_{21}+9X_{22}+8.3X_{23}+8.1X_{31}+8.2X_{32}+5.8X_{33}$

Step2: Set up production capacity constraints

For Plant 1: $X_{11}+X_{12}+X_{13}\leq610$
For Plant 2: $X_{21}+X_{22}+X_{23}\leq580$
For Plant 3: $X_{31}+X_{32}+X_{33}\leq230$

Step3: Set up demand constraints

For Store A: $X_{11}+X_{21}+X_{31}=430$
For Store B: $X_{12}+X_{22}+X_{32}=310$
For Store C: $X_{13}+X_{23}+X_{33}=620$

Step4: Non - negativity constraints

$X_{ij}\geq0$ for $i = 1,2,3$ and $j=1,2,3$

To solve this in Excel:

1. Enter the objective function formula in a cell (e.g., $Z$ in cell A1).
2. Enter the values of the coefficients of the objective function in a range of cells.
3. Enter the left - hand side of the constraints in separate cells and link them to the decision variables.
4. Enter the right - hand side of the constraints in separate cells.
5. Use Excel Solver to set the objective (minimize $Z$), set the decision variables ($X_{ij}$), and add the constraints.

Answer:

The linear optimization model is:
Objective function: $Z = 7.3X_{11}+6.8X_{12}+6.1X_{13}+4.6X_{21}+9X_{22}+8.3X_{23}+8.1X_{31}+8.2X_{32}+5.8X_{33}$
Subject to:
$X_{11}+X_{12}+X_{13}\leq610$
$X_{21}+X_{22}+X_{23}\leq580$
$X_{31}+X_{32}+X_{33}\leq230$
$X_{11}+X_{21}+X_{31}=430$
$X_{12}+X_{22}+X_{32}=310$
$X_{13}+X_{23}+X_{33}=620$
$X_{ij}\geq0$ for $i = 1,2,3$ and $j = 1,2,3$
The solution values of $X_{ij}$ can be found using Excel Solver to minimize $Z$.