Question

Excel: Put response in a single spreadsheet and Include an Answer Report

STACycles manufactures bikes at factories in Albany, Syracuse, and Rochester. The bikes are sent to regional distributors in Detroit, Knoxville, and Atlanta, with varying shipping costs. The company would like to find the least-cost way to meet the demands at each of the distribution centers. Detroit needs to receive 800 bikes each month, Knoxville needs 600, and Atlanta needs 200. Albany produces 850 each month, Syracuse 650, and Rochester 300. The shipping cost per unit from each pair of cities is given in the table below:

Distributor

Factory Detroit Knoxville Atlanta

Albany 8 12 10

Syracuse 10 14 9

Rochester 11 8 12

Is it possible for STACycles to meet the given demands with its available supply? What is a possible routing plan which minimizes costs, and what is the minimal cost?

Answer 1

Yes,

It is possible for STA Cycles to meet the given demands with its available supply as the distributors demanded 1600 units(800+600+200) and the factories are able to produce 1800 units(850+650+300)

Possible Routing Plan:

Factory	Supply by Factories	Detroit	Knoxville	Atlanta
Demand for distributors		800	600	200
Albany	850	800	50	0
Syracuse	650	0	250	200
Rochester	300	0	300	0

Minimal Cost :

Supply by Albany factory to detroit distributor of 800 units   = $ 8

Supply by Albany factory to knoxville distributor of 50 units = $ 12

Supply by Syracuse factory to knoxville distributor of 250 units = $ 14

Supply by Syracuse factory to Atlanta distributor of 200 units = $ 9

Supply by Rochester factory to knoxville distributor of 300 units = $ 8

Total Minimal costs =$ 51

$ 51 is the Minimal Costs incurred for the possible routing plan.