Please do fast 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 lunbelow.Let:Store C$1.20$2.503$4.60Demand160540Formulate a linear optimization model for this problem, implement your model on a spredsheet, and use Excel Solver to find a solution that minimizes total distribution costs. If the constant is equal"1".X11 = number of components produced in Plant 1 and supplied to Store AX12 = number of components produced in Plant 1 and supplied to Store BX13 = number of components produced in Plant 1 and supplied to Store CX21 number of components produced in Plant 2 and supplied to Store AXz2 = number of components produced in Plant 2 and supplied to Store BX23 = number of components produced in Plant 2 and supplied to Store CX31 number of components produced in Plant 3 and supplied to Store AX32 = number of components produced in Plant 3 and supplied to Store BX33 number of components produced in Plant 3 and supplied to Store CX13+MinX11+Subject to the constraintsX11+X11X12+X12+X12Plant12X13X13X21+X21+X21Store A$5.50$5.80$2.90790+Store B$3.30$2.40$1.70X22+X22+X22X23++X23Capacity320720510+X31+X31+X31X23X11, X12, X13, X21, X22, X23, X31, X32, X33 20What 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.$+X32+X32+X32+X33-Select--Select-X33 -Select-v-Select- v-Select- vX33 -Select- v per 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 givenPlant12Store C$1.20$2.50$4.603Demand160540linear optimization model for this problem, implement your model on a spredsheet, and use Excel Solver to find a solution that minimizes total distribution costs. If the constant is equal to one, enterumber of components produced in Plant 1 and supplied to Store Aumber of components produced in Plant 1 and supplied to Store Bumber of components produced in Plant 1 and supplied to Store Cumber of components produced in Plant 2 and supplied to Store Aumber of components produced in Plant 2 and supplied to Store Bumber of components produced in Plant 2 and supplied to Store Cumber of components produced in Plant 3 and supplied to Store Ahumber of components produced in Plant 3 and supplied to Store Bumber of components produced in Plant 3 and supplied to Store CX12+X13+X11+he constraintsX11+X11X12+X12X13X21+X13X21+X21Store A$5.50$5.80$2.90790+Store B$3.30$2.40$1.70X22+X22+X22X23++Capacity320720510X23X31+X23X11, X12, X13, X21, X22, X23, X31, X32, X33 20value of total distribution costs at the optimal solution? Use the Excel solver to find the optimal solution. Round your answer to the nearest dollar.X31+X31X32+X32+X32+X33-Select- v-Select-X33 -Select-v-Select--Select-X33 -Select- v

Min 5.5 X11 + 3.3 X12 + 1.2 X13 + 5.8 X21 + 2.4 X22 + 2.5 X23 + 2.9 X31 + 1.7 X32 + 4.6 X33…

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 frami below. Let: Plant Store C 1 Store B $3.30 $2.40 $1.20 2 $2.50 3 $1.70 $4.60 Demand 160 540 Formulate a linear optimization model for this problem, implement your model on a spredsheet, and use Excel Solver to find a solution that minimizes total distribution costs. If the constant is "1". 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 Xz2 = 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 X12+ X13+ Min X11+ Subject to the constraints X11+ | X11 X12+ X13 X12 X21+ X13 X21+ Store A $5.50 $5.80 $2.90 790 X21 X22+ X22+ X23+ Capacity 320 720 510 X23 X31+ X22 + X23 X11, X12, X13, X21, X22, X23, X31, X32, X33 20 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. X31+ X31 + X32+ X32+ X32 + X33 -Sel -Sel X33 -Sel -Sel -Sel X33 -Sel

6th Edition

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:WINSTON, Wayne L.

Chapter2: Introduction To Spreadsheet Modeling

Section: Chapter Questions

Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...