PLEASE SHOW HOW YOU GOT THE ANSWER FOR B WITH EXCEL!!

Straight Arrow Steam Beer Company

Straight Arrow Steam Beer Company is a small brewery capitalizing on the growing demand of American beer drinkers for premium beers and ales. In its 10 years of operation, the company has grown from a single plant with local distribution to two plants and four distribution centers located around the country.

Company policy is to produce at capacity at its two plants. It stores any excess beer at the plant during periods of lower demand, so that it can meet demand in periods when demand for their product exceeds capacity.

Shirley White, who works on the marketing staff for Straight Arrow, schedules shipments each month from the company's two plants to its four distribution centers. She is currently working on the distribution schedule for next month. Table A shows the capacity at each plant and the anticipated demand at each of the four distribution centers. Table B shows the shipping cost, per barrel, from each plant to each distribution center.

Table A Plant Capacity, Barrels Warehouse Demand, Barrels

A 2500 W 1200

B 2900 X 1400

Y 1500

Z 1000

Table B Shipping Cost $/barrel

From Plant A From Plant B

To Warehouse W 10 3

To Warehouse X 9 5

To Warehouse Y 2 8

To Warehouse Z 6 7

a.) Develop a shipping schedule that will minimize transportation costs. List the shipping pattern and the total cost for this schedule.

The company's long-range planning committee has recently completed an extensive analysis of future demand for our beer across the country. The committee's statisticians have projected that over the next 5 years, monthly demand at each of the distribution centers will increase by 50% over the current monthly demand. It also projects a $1 increase in shipping costs from the two existing plants to the four distribution centers. To meet future demand projections, the company plans to build a new brewery. The monthly capacity of the new facility should be sufficient to allow Straight Arrow to cover anticipated demand five years from now, with their three facilities. Two sites for the new plant are under consideration. These will be labeled plant C and plant D. Table C lists the expected transportation costs, per barrel, from each proposed site to the 4 distribution centers. The company projects that production costs will be $0.75/barrel higher at plant C than they would be at plant D.

Table C Transportation Cost, $/barrel

From Plant C From Plant D

To Warehouse W 8 9

To Warehouse X 3 7

To Warehouse Y 5 4

To Warehouse Z 6 3

b.) What should be the capacity of the new facility?