Sound Electronics, Inc. produces a battery-operated tape recorder at plants located in Martinsville, North Carolina; Plymouth, New York; and Franklin, Missouri. The unit transportation cost for shipments from the three plants to distribution centers in Chicago, Dallas, and New York are as follows:
TO
FROM CHICAGO DALLAS NEW YORK
Martinsville 1.45 1.60 1.40
Plymouth 1.10 2.25 0.60
Franklin 1.20 1.20 1.80
After considering transportation costs, management has decided that under no circumstances will it use the Plymouth-Dallas route. The plan capacities and distributor orders for the next month are as follows
PLANT CAPACITY (units)
Martinsville 400
Plymouth 600
Franklin 300
DISTRIBUTOR ORDERS (units)
Chicago 400
Dallas 400
New York 400
Because of different wage scales at the three plants, the unit production cost varies from plant to plant. Assuming the costs are $29.50 per unit at Martinsville, $31.20 per unit at Plymouth, and $30.35 per unit at Franklin, find the production and distribution plan that minimizes production and transportation costs. Namely, provide the formulation of this transportation problem, with clearly defined variables, the objective function, and the constraints. Solve it by excel. Include the excel output.