Problem $1(40$ points$)$

The Ela & Beren Fixtures manufactures and distributes industrial chandeliers. The company started with

a small production plant in Birmingham, AL and gradually built a customer base throughout Southeast.

A distribution center was established in Atlanta, GA$,$ and later as business expanded, a second

distribution center was established in Nashville, TN$.$ The Birmingham plant was expanded when the

company began marketing its products in Tennessee, Kentucky, Carolinas, Virginia, West Virginia, and

Ohio.

With the growth of business, the company opened a third distribution center in Cincinnati, OH $,$ and just

two years ago opened a second production plant in Richmond, VA$.$ Manufacturing costs differ between

the company's production plants. The cost of each chandelier is $$22\mathrm{.}50$ per unit in Birmingham. The

Richmond plant uses newer and more efficient equipment, and as a result, its manufacturing costs come to

$$18$ per unit.

The quarterly production capacity is $30,000$ units at the older Birmingham plant and $20,000$ units at the

Richmond plant. No shipments are allowed from the Richmond plant to the Atlanta distribution center.

Due to the firm's rapid growth, little attention has been paid to the efficiency of the distribution system,

but the company management thought that it is about time to evaluate. The cost of shipping from each

one of the plants to each one of the distribution centers is shown in the table.

The company serves nine customer zones from the three distribution centers. The cost of shipping from

each one of the distribution centers to the nine demand zones is given in the following table. Note that

some distribution centers do not serve certain customer zones because the costs would be prohibitive. The

demand of the customer zones is given at the bottom of the table based on forecast for the next quarter.

In the current distribution system, demands at the Savannah, Charlotte, Miami, and Montgomery

customer zones are satisfied by shipments from the Atlanta distribution center. In a similar manner, the

Chattanooga, Columbus, and Lexington customers are served by the Nashville distribution center, and the

Louisville and Indianapolis customer zones are served by Cincinnati distribution center. The Birmingham

plant serves Atlanta and Nashville distribution centers, while the Richmond plant supplies the Cincinnati

distribution center.

You have been called to make recommendations for improving the system:

Part a: $(15$ pts$)$ In order to capture the improvement, you first need to estimate the costs of the current

system. If the company does not change its current distribution strategy, what will be distribution system

cost for the next quarter?

Part b: $(25$ pts$)$ Suppose that the company would like to realign the assignments of customers to

distribution centers, and distribution centers to plants. What would be the optimal plan? How much does

it improve over Part a$?$ Please comment on the changes. Should Ela and Beren fixtures change their

distribution $($transportation$)$ plan? If so$,$ what would be their savings?

HINT: For N$.$A$.$ transportation costs consider a large $($very big$)$ number.

Please use Excel Solver for both parts and show all steps.