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.