using excel solver)

The Rohan Products Company (RPC), headquartered in Burlington, Wisconsin, produces a wide line of handcrafted home furnishings that are produced either in its own plants or by local artisans working under contract with RPC. Rohan is considering expanding their sales territory into the central and southeast regions of the United States. They have identified 22 cities in which they are leasing retail store locations and have developed detailed sales forecasts for each location. These detailed sales forecasts have been converted into hundredweights (cwt) sold annually at each store location. The term hundredweight refers to 100 pounds of product. At Rohan, the use of hundredweights as a unit of measure has been found to be a relatively accurate way of aggregating demand and is convenient in working out transportation costs. The annual demand at each of the 22 retail store locations is shown in the following table.

Annual Demands
Atlanta	275
Birmingham	160
Buffalo	240
Charleston	260
Charlotte	135
Chattanooga	160
Chicago	400
Cincinnati	200
Clevelend	320
Columbus	220
Detroit	190
Evansville	100
Ft. Wayne	140
Indianapolis	310
Knoxville	125
Louisville	340
Memphis	240
Nashville	210
Peoria	150
Pittsburgh	340
Richmond	300
St. Louis	260

Rohan has a company policy of leasing distribution warehouses and has identified warehouse space in eight possible cities. The typical leased warehouse facility has a cost structure that consists of a fixed cost and a variable cost. In addition, there is a lower limit (L) and an upper limit (U) associated with annual volume. The upper limit (U) or maximum, is the volume dictated by material handling equipment that no more than this amount of product can be moved through a warehouse. The lower limit (L) or minimum, functions as a guarantee of sorts to the leasing company. In each city, lease arrangements require a certain capacity to be leased if a warehouse is established. Specifically, Rohan can choose to operate at a volume lower than the lower limit L, but the fixed cost would be charged in any event. Only volumes more than the lower limit L would incur additional costs which would be equal to the variable cost multiplied by the excess volume. This data is shown in the following table.

Warehouse Cost and Capacity Estimates
Location	Variable Cost	Fixed Cost	Min. (L)	Max. (U)
Atlanta	$6	$2,700	200	1,750
Buffalo	$8	$2,900	150	1,250
Chicago	$9	$3,500	250	2,000
Cincinnati	$7	$2,200	200	1,500
Detroit	$8	$3,300	200	1,750
Pittsburgh	$8	$3,000	200	1,500
Richmond	$6	$2,000	150	1,000
St. Louis	$5	$1,800	200	1,500

The cost of transportation from each of the possible eight warehouse locations to each of the 22 retail store locations has been determined and is shown in the following table.

Unit Costs for Distribution
	Atlanta	Buffalo	Chicago	Cincinnati	Detroit	Pittsburgh	Richmond	St. Louis
Atlanta	$1	$13	$16	$11	$15	$11	$8	$12
Birmingham	$4	$15	$15	$12	$16	$13	$11	$10
Buffalo	$13	$1	$10	$8	$4	$4	$7	$13
Charleston	$7	$6	$11	$6	$8	$6	$4	$10
Charlotte	$4	$10	$15	$10	$13	$8	$4	$14
Chattanooga	$2	$11	$14	$9	$13	$9	$7	$11
Chicago	$16	$10	$1	$9	$6	$9	$14	$7
Cincinnati	$9	$6	$7	$3	$6	$6	$7	$7
Clevelend	$13	$3	$7	$6	$3	$5	$8	$10
Columbus	$10	$4	$8	$4	$5	$4	$7	$9
Detroit	$15	$4	$6	$8	$1	$7	$11	$11
Evansville	$9	$10	$7	$6	$9	$9	$10	$4
Ft. Wayne	$14	$8	$2	$7	$4	$8	$12	$6
Indianapolis	$12	$9	$4	$6	$7	$9	$11	$4
Knoxville	$4	$9	$12	$7	$11	$8	$6	$9
Louisville	$8	$8	$8	$4	$8	$9	$8	$6
Memphis	$8	$14	$12	$10	$14	$12	$12	$6
Nashville	$6	$12	$11	$8	$12	$10	$9	$7
Peoria	$16	$11	$2	$10	$8	$11	$15	$5
Pittsburgh	$11	$2	$9	$6	$5	$3	$5	$11
Richmond	$8	$7	$14	$9	$11	$7	$1	$14
St. Louis	$12	$13	$7	$9	$11	$11	$14	$1

The management of Rohan Products Company (RPC) wants to know how many additional warehouses are needed and where they should be located? To do so, RRPC wants you to develop a plan that minimizes total cost.

Please use Excel and list formulas and constraints.