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 1 $7 30 $6 80 $6 10 610 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 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 Item 13 X 21 X 22 X 23 Select Item 18 X 31 X 32 X 33 Select Item 23 X 11 X 21 X 31 Select Item 28 X 12 X 22 X 32 Select Item 33 X 13 X 23 X 33 Select Item 38 X 11 , X 12 , X 13 , X 21 , X 22 , X 23 , X 31 , X 32 , X 33 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 $

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Jul 07, 2024

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

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
1	$7.30		$6.80		$6.10	610
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:
	X₁₁ = number of components produced in Plant 1 and supplied to Store A
	X₁₂ = number of components produced in Plant 1 and supplied to Store B
	X₁₃ = number of components produced in Plant 1 and supplied to Store C
	X₂₁ = number of components produced in Plant 2 and supplied to Store A
	X₂₂ = number of components produced in Plant 2 and supplied to Store B
	X₂₃ = number of components produced in Plant 2 and supplied to Store C
	X₃₁ = number of components produced in Plant 3 and supplied to Store A
	X₃₂ = number of components produced in Plant 3 and supplied to Store B
	X₃₃ = number of components produced in Plant 3 and supplied to Store C

Min X₁₁ + X₁₂ + X₁₃ + X₂₁ + X₂₂ + X₂₃ + X₃₁ + X₃₂ + X₃₃

Subject to the constraints

X₁₁ + X₁₂ + X₁₃ -Select-=Item 13

X₂₁ + X₂₂ + X₂₃ -Select-=Item 18

X₃₁ + X₃₂ + X₃₃ -Select-=Item 23

X₁₁ + X₂₁ + X₃₁ -Select-=Item 28

X₁₂ + X₂₂ + X₃₂ -Select-=Item 33

X₁₃ + X₂₃ + X₃₃ -Select-=Item 38

X₁₁, X₁₂, X₁₃, X₂₁, X₂₂, X₂₃, X₃₁, X₃₂, X₃₃ 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.