Milford Lumber Company ships construction materials from three wood-processing plants to three retail stores. The shipping cost, monthly production capacities, and monthlydemand for framing lumber are given below.Plant12Store A$4.40$6.003$4.80Demand300Formulate 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 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 AX22 = 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 CStore B$7.80$7.10$4.60530Store C$6.10$3.90$3.90350Capacity280480450 MinX11 +X32 +Subject to the constraintsX11 +X21 +X31 +X33X11 +X12 +X13 +X12 +X13 +X21 +X13 -Select-vX23 -Select-vX33 -Select-X31 -Select-X32 -Select-X22 +X23 +X12 +X22 +X32 +X21 +X22 +X23 +X33 -Select-X11, X12, X13, X21, X22, X23, X31, X32, X33 ≥ 0What 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 +

The shipping cost($) from Plants to Stores is given above.

Answered: Milford Lumber Company ships…

Practical Management Science

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

See similar textbooks

Similar questions

Georgia Cabinets manufactures kitchen cabinets that are sold to local dealers throughout the Southeast. Because of a large backlog of orders for oak and cherry cabinets, the company decided to contract with three smaller cabinetmakers to do the final finishing operation. For the three cabinetmakers, the number of hours required to complete all the oak cabinets, the number of hours required to complete all the cherry cabinets, the number of hours available for the final finishing operation, and the cost per hour to perform the work are shown here: Cabinetmaker 1 Cabinetmaker 2 Cabinetmaker 3 Hours required to complete all the oak cabinets 47 40 27 Hours required to complete all the cherry cabinets 64 51 36 Hours available 40 30 35 Cost per hour $34 $41 $52 For example, Cabinetmaker 1 estimates it will take 47 hours to complete all the oak cabinets and 64 hours to complete all the cherry cabinets. However, Cabinetmaker 1 only has 40 hours available for the final…
The Gulf Coast Foundry is developing a long-range strategic plan for buying scrap metal for its foundry operations. The foundry can buy scrap metal in unlimited quantities from two sources, Atlanta (A) and Birmingham (B), and it receives the scrap daily in railroad cars. The scrap is melted down, and lead and copper are extracted for use in the foundry processes. Each railroad car of scrap from source A yields 1 ton of copper and 1 ton of lead and costs $10,000. Each railroad car of scrap from source B yields 1 ton of copper and 2 tons of lead and costs $15,000. If the foundry needs at least 21/2 tons of copper and at least 4 tons of lead per day for the foreseeable future, how many railroad cars of scrap should be purchased per day from Source A and Source B to minimize the long-range scrap metal cost?
the excel file president’s inn guest database provides a list of customers, rooms they occupied, arrival and departure dates, number of occupants, and daily rate for a small bed- and-breakfast inn during one month. room rates are the same for one or two guests; however, additional guests must pay an additional $20 per person per day for meals. guests staying for seven days or more receive a 10% discount. modify the spreadsheet to calculate the number of days that each party stayed at the inn and the total revenue for the length of stay.
The following table represents the cost to ship from Distribution Center 1, 2, or 3 to Customer A, B, or C. Is this a "balanced" or "unbalanced" model? Customer B 6 DC 1 2 3 Demand. Obalanced. A 4 5 3 200 2 5 350 unbalanced O not enough information. O equal sources and destinations C 8 7 9 400 Supply 500 400 300
Zucchero Sugar, Inc. has six processing departments for refining sugar—Affination, Carbonation, Decolorization, Boiling, Recovery, and Packaging. Conversion costs are added evenly throughout each process. Data from the month of August for the Recovery Department are as follows: Metric Tons Beginning Work−in−Process Inventory 0 Transferred in 22,000 Ending Work−in−Process Inventory 6,000 Costs Beginning Work−in−Process Inventory $0 Costs added during August : Direct materials 530,000 Direct labor 250,000 Manufacturing overhead 105,000 Total costs added during August $885,000 The ending Work−in−Process Inventory is 100% and 95% complete with respect to direct materials and conversion costs, respectively. The weighted−average method is used. How many metric tons of sugar were refined and transferred to the Packaging Department in August? A. 6,000 metric tons B.…
Create spreadsheets and use Solver to determine the correct volumes to be produced to minimize cost for the following problem. Your company has two trucks that it wishes to use on a specific contract. One is a new truck the company is making payments on, and one is an old truck that is fully paid for. The new truck’s costs per mile are as follows: 54₵ (fuel/additives), 24₵ (truck payments), 36₵ (driver), 12₵ (repairs), and 1₵ (misc.). The old truck’s costs are 60₵ (fuel/additives), 0₵ (truck payments), 32₵ (rookie driver), 24₵ (repairs), and 1₵ (misc.). The company knows that truck breakdowns lose customers, so it has capped estimated repair costs at $14,000. The total distance involved is 90,000 miles (to be divided between the two trucks).
Valencia products makes automobile radar detectors and assembles two models. Laser stop and Speedbuster both models use the same electronic components after reviewing the components required and the profit for each model, the firm found the following linear optimization model for profit, where L is the number of LaserStop models produced and S is the number of Speedbuster models produced. Implement the linear optimization model on a spreadsheet and use. Solver to find an optimal solution. Interpret the optimal solution, identify the binding constraints and verify the values of the slack variables . Maximize profit = 123 L + 139 S 19 L + 11 S < 4,000 (availability of component A) 6 L + 9 S < 35000 (availability of component B) L > 0 and S > 0 The optimal solution is to produce how many Laser stop models ? And how many Speedbuster models ? The maximum possible profit is ? Component A is or is not a binding…
A trucking firm handles four commodities for a particular distributor. Total shipments for the commodities in the years 2000 and 2015, as well as the prices in the year 2000, are reported in the following table. Commodity Shipments Price/Shipment2000 2000 2015 A 120 90 $1,200 B 86 73 $1,800 C 35 50 $2,000 D 60 70 $1,500 Develop a weighted aggregate quantity index with a 2000 base. (Round your answer to the nearest integer.) I2015 = ______
A retail has 8 stores supplied from 4 suppliers, where each supplier supplies different goods. Delivery from the Supplier is in trucks with a capacity of 40,000 units at a cost of $1100 per load plus $100 per delivery to the store. Inventory costs are $0.2 per unit per year. The Supply Chain Manager is considering whether to use direct shipping or Milk Run delivery for 4 stores for each truck in one shipment a) if annual sales in each store are 1000,000 units, which shipping method provides the lower cost b) if the annual sales in each store are 200,000 units, which shipping method provides the lower cost
Using Excel Solve the following LP Maximize $4x + $5y Subject to 2x + 3y ≤ 20 (labor, in hours) 6x + 6y ≤ 36 (materials, in pounds) 4x + 4y ≤ 40 (storage, in square feet) x, y ≥ 0 a) Write the original optimal solution and objective function value. b) What is the optimal solution and objective function value if you acquire 2 additional pounds of material? c) What is the optimal solution and objective function value if you acquire 1.5 additional hours of labor?
B & C

Recommended textbooks for you

Practical Management Science

Operations Management

ISBN:

9781337406659

Author:

WINSTON, Wayne L.

Publisher:

Cengage,

Operations Management

ISBN:

9781259667473

Author:

William J Stevenson

Publisher:

McGraw-Hill Education

Operations and Supply Chain Management (Mcgraw-hi…

Operations Management

ISBN:

9781259666100

Author:

F. Robert Jacobs, Richard B Chase

Publisher:

McGraw-Hill Education

Practical Management Science

Operations Management

ISBN:

9781337406659

Author:

WINSTON, Wayne L.

Publisher:

Cengage,

Operations Management

ISBN:

9781259667473

Author:

William J Stevenson

Publisher:

McGraw-Hill Education

Operations and Supply Chain Management (Mcgraw-hi…

Operations Management

ISBN:

9781259666100

Author:

F. Robert Jacobs, Richard B Chase

Publisher:

McGraw-Hill Education

Business in Action

Operations Management

ISBN:

9780135198100

Author:

BOVEE

Publisher:

PEARSON CO

Purchasing and Supply Chain Management

Operations Management

ISBN:

9781285869681

Author:

Robert M. Monczka, Robert B. Handfield, Larry C. Giunipero, James L. Patterson

Publisher:

Cengage Learning

Production and Operations Analysis, Seventh Editi…

Operations Management

ISBN:

9781478623069

Author:

Steven Nahmias, Tava Lennon Olsen

Publisher:

Waveland Press, Inc.

SEE MORE TEXTBOOKS