Question

House Beer has three breweries in Portland, New York and Scranton. The cases of beer are sent to warehouses in Boston and Atlantic City and then shipped to the distribution centers in Pittsburgh, Cincinnati, and Atlanta. The shipping cost per case from each brewery to each warehouse and the supply and demand are given below. (15 points)

                                                                                   Warehouse

Brewery	Boston	Atlantic City
Portland	0.38	1.16
New York	0.50	0.40
Scranton	1.20	0.60

                                                                             Distribution Center

Warehouse	Pittsburgh	Cincinnati	Atlanta
Boston	0.70	1.20	1.50
Atlantic City	0.50	0.90	1.20

Capacity of Each Brewing			Demand at Each Distribution Center
Portland	1600		Pittsburgh	1800
New York	2000		Cincinnati	2000
Scranton	2500		Atlanta	2300

      a.   Draw the network diagram for this problem.

            b.         Using Excel, solve the above problem and give the optimal solution

Answer 1

The network diagram is presented below .

Table on the costs of distribution from one center to another .

From the table above , The table below has been constructed .

The Network table can be referenced for the same flow , the column Cost 1 informs the cost of transportation from brewery to Warehouse and the Cost 2 is the transportation cost from Warehouse to distribution center . Sum of the cost is presented in the "Total cost".

The table once populated has been sorted with the total cost with values from lowest to smallest.

Now to allocate the value based on demand and supply We can identify the demand from the distribution center and then alocate the lowest cost route to the demand from the supply available. Also note here that the supply is limited based on the capacity of the brewery and hence the number is allocated based on the lowest cost and amount of supply available.

The final cost value is arrived by multiplying the total cost vs the supplied quantity.