Question

wild 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
1 $7.30 $6.80 $6.10 610
2 $4.60 $9.00 $8.30 580
3 $8.10 $5.30 $5.30 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:
$x_{11}$ = number of components produced in plant 1 and supplied to store a
$x_{12}$ = number of components produced in plant 1 and supplied to store b
$x_{13}$ = number of components produced in plant 1 and supplied to store c
$x_{21}$ = number of components produced in plant 2 and supplied to store a
$x_{22}$ = number of components produced in plant 2 and supplied to store b
$x_{23}$ = number of components produced in plant 2 and supplied to store c
$x_{31}$ = number of components produced in plant 3 and supplied to store a
$x_{32}$ = number of components produced in plant 3 and supplied to store b
$x_{33}$ = number of components produced in plant 3 and supplied to store c

min $x_{11}$+ $x_{12}$+ $x_{13}$+ $x_{21}$+ $x_{22}$+ $x_{23}$+ $x_{31}$+ $x_{32}$+ $x_{33}$

subject to the constraints:
$x_{11}$+ $x_{12}$+ $x_{13}$ select
$x_{21}$+ $x_{22}$+ $x_{23}$ select
$x_{31}$+ $x_{32}$+ $x_{33}$ select
$x_{11}$+ $x_{21}$+ $x_{31}$ select
$x_{12}$+ $x_{22}$+ $x_{32}$ select
$x_{13}$+ $x_{23}$+ $x_{33}$ select
$x_{11},x_{12},x_{13},x_{21},x_{22},x_{23},x_{31},x_{32},x_{33}geq0$

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.

Explanation:

Step1: Define the objective function

The objective is to minimize the total distribution cost. The cost - per - unit from each plant to each store is given. The objective function $Z$ is:
$$Z = 7.30x_{11}+6.80x_{12}+6.10x_{13}+4.60x_{21}+9.00x_{22}+8.30x_{23}+8.10x_{31}+5.30x_{32}+5.80x_{33}$$

Step2: Define production capacity constraints

Plant 1 has a capacity of 610 units. So, $x_{11}+x_{12}+x_{13}\leq610$.
Plant 2 has a capacity of 580 units. So, $x_{21}+x_{22}+x_{23}\leq580$.
Plant 3 has a capacity of 230 units. So, $x_{31}+x_{32}+x_{33}\leq230$.

Step3: Define demand constraints

Store A has a demand of 430 units. So, $x_{11}+x_{21}+x_{31}=430$.
Store B has a demand of 310 units. So, $x_{12}+x_{22}+x_{32}=310$.
Store C has a demand of 620 units. So, $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 find the optimal solution, you would input the objective function and constraints into Excel Solver.

Answer:

The linear optimization model is:
Minimize $Z = 7.30x_{11}+6.80x_{12}+6.10x_{13}+4.60x_{21}+9.00x_{22}+8.30x_{23}+8.10x_{31}+5.30x_{32}+5.80x_{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$