Question

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 7.3 x11 + 6.8 x12 + 6.1 x13 + 4.6 x21 + 9 x22 + 8.3 x23 + 8.1 x31 + 8.2 x32 + 5.8 x33 subject to the constraints x11 + x12 + x13 ≤ 610 x21 + x22 + x23 ≤ 580 x31 + x32 + x33 ≤ 230 x11 + x21 + x31 = 430 x12 + x22 + x32 = 310 x13 + x23 + x33 = 620 x11, x12, x13, x21, x22, x23, x31, x32, x33 ≥ 0

Explanation:

Step1: Define the objective function

The objective is to minimize the total distribution - cost. The objective function $Z$ 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: Define production - capacity constraints

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

Step3: Define demand - satisfaction constraints

Store A has a demand of 430, so $X_{11}+X_{21}+X_{31}=430$.
Store B has a demand of 310, so $X_{12}+X_{22}+X_{32}=310$.
Store C has a demand of 620, so $X_{13}+X_{23}+X_{33}=620$.

Step4: Non - negativity constraints

All decision variables $X_{ij}\geq0$, where $i = 1,2,3$ and $j = 1,2,3$.

To find the solution, input the objective function and constraints into Excel Solver. The values of $X_{ij}$ that minimize $Z$ will be the optimal solution. However, without actually running Excel Solver, we cannot provide the numerical values of $X_{ij}$ and the minimum value of $Z$.

Answer:

The linear optimization model is defined as above. To get the actual solution, use Excel Solver with the objective function and constraints.