Question

I need a step by step breakdown (with pictures preferrably) of how to solve this problem in excel and please include how to set it up from the very beginning.

Also include a model drawing with nodes if possible.

Acme Block Company has orders for 80 tons of concrete blocks at three suburban locations as follows: • Northwood -- 25 tons • Westwood -- 45 tons Customers • Eastwood -- 10 tons.  Acme has two plants, each of which can produce 50 tons per week.  Delivery cost per ton from each plant to each suburban location is shown on the next slide.  How should end of week shipments be made to fill the above orders?

Delivery Cost Per Ion Northwood Westwood Eastwood 30 24. 40 Plant 1 30 Plant 2 40 42

Answer 1

Distribution Distribution Distribution Supply Plant 1 24 30 40 50 Plant 2 30 40 42 50 Capacity 4 Distribution Distribution Distribution Total Plant 15 45 0 50 Plant 2 20 0 30 50 Total 25 45 30 Total 3330 cost

Solver Parameters Set Objective: Distribution 1 Distribution 2Distribution 3 Supply Min Oyalue of 24 Plant1 50 Plant 2 30 50 40 42 By Changing Variable Cells: 8S9 SD$10 Capacity Subject to the Constraints BS1225 SC$12-45 S0$12-30 SE$10-SES3 Distribution 1 Distribution 2 Distribution 3 Total Plant 1 45 0 0 Plant 2 0 Rel 2 Total 25 4 Total costj- 3330 Load Make Unconstrained Variables Non-Negative Select a Solving Method: GRG Nonlinear Sheet1 Solving Method TYPE SUM(B9:B10) in B12 SUM(C9:C10) in C12 (although you don't have to type this one ,drag the corner of B12 along row till D12) SUM(D9:D10) in D12 similarly SUM(B9:D9) in E10 -SUM(B10:D10) in E11 =SUMPRODUCT(B2:D3,B9:D10) in B14 add all the constraint as shown in pic and then solve it Z 3330
THANK YOU PLEASE RATE IT