Please complete in Excel with all of the formulas and constraints.

Richmond St. Louis $8 $12 $7 $13 $14 $7 $9 $9 $11 $11 $7 $11 $1 $14 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. ROHAN PRODUCTS COMPANY 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. 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. (20 points) Warehouse Cost and Capacity Estimates Buffalo 275 160 240 260 135 160 Annual Demands Atlanta Birmingham Buffalo Charleston Charlotte Chattanooga Chicago Cincinnati Clevelend Columbus Detroit Evansville Ft. Wayne Indianapolis Knoxville Louisville Memphis Nashville 400 200 320 220 190 Buffalo 100 140 310 Location Variable Cost Fixed Cost Min. (L) Max. (U) Atlanta $6 $2,700 200 1,750 $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 S6 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 Atlanta $1 $13 $16 $11 $15 $11 Birmingham $4 $15 $15 $12 $16 $13 $13 $ $1 $10 $8 $4 $4 Charleston $7 $6 $11 $6 $8 $6 Charlotte $4 $10 $15 $10 $13 $8 $ Chattanooga $2 $11 $14 $9 $13 $9 Chicago $16 $10 $1 $9 $6 $9 $9 $6 $7 $3 $6 $6 Clevelend $13 $3 $7 $6 $3 $5 Columbus $10 $4 $8 $4 $5 $4 Detroit $15 $4 $6 8 $8 $1 $7 Evansville $9 $10 $7 $6 $9 $9 Ft. Wayne $14 $8 $2 $7 $4 $8 Indianapolis $12 $9 $4 $6 $7 $9 cm $4 Knoxville $9 $12 $7 $11 $8 co Louisville $8 $8 $8 $4 $8 $9 Memphis $8 $14 $12 $10 $14 $12 Nashville $6 $12 $11 $8 $12 $10 Peoria $16 $11 $2 $10 $8 $11 Pittsburgh $11 $2 $2 $9 $6 $5 $3 Cincinnati 125 340 240 210 150 340 300 260 ** Peoria Richmond $8 $11 $7 $4 $4 $7 $14 $7 $8 7 $7 $11 $10 $12 $11 $6 $8 $12 $9 $15 $5 Pittsburgh Richmond St. Louis 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 Richmond St. Louis $8 $12 $7 $13 $14 $7 $9 $9 $11 $11 $7 $11 $1 $14 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. ROHAN PRODUCTS COMPANY 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. 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. (20 points) Warehouse Cost and Capacity Estimates Buffalo 275 160 240 260 135 160 Annual Demands Atlanta Birmingham Buffalo Charleston Charlotte Chattanooga Chicago Cincinnati Clevelend Columbus Detroit Evansville Ft. Wayne Indianapolis Knoxville Louisville Memphis Nashville 400 200 320 220 190 Buffalo 100 140 310 Location Variable Cost Fixed Cost Min. (L) Max. (U) Atlanta $6 $2,700 200 1,750 $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 S6 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 Atlanta $1 $13 $16 $11 $15 $11 Birmingham $4 $15 $15 $12 $16 $13 $13 $ $1 $10 $8 $4 $4 Charleston $7 $6 $11 $6 $8 $6 Charlotte $4 $10 $15 $10 $13 $8 $ Chattanooga $2 $11 $14 $9 $13 $9 Chicago $16 $10 $1 $9 $6 $9 $9 $6 $7 $3 $6 $6 Clevelend $13 $3 $7 $6 $3 $5 Columbus $10 $4 $8 $4 $5 $4 Detroit $15 $4 $6 8 $8 $1 $7 Evansville $9 $10 $7 $6 $9 $9 Ft. Wayne $14 $8 $2 $7 $4 $8 Indianapolis $12 $9 $4 $6 $7 $9 cm $4 Knoxville $9 $12 $7 $11 $8 co Louisville $8 $8 $8 $4 $8 $9 Memphis $8 $14 $12 $10 $14 $12 Nashville $6 $12 $11 $8 $12 $10 Peoria $16 $11 $2 $10 $8 $11 Pittsburgh $11 $2 $2 $9 $6 $5 $3 Cincinnati 125 340 240 210 150 340 300 260 ** Peoria Richmond $8 $11 $7 $4 $4 $7 $14 $7 $8 7 $7 $11 $10 $12 $11 $6 $8 $12 $9 $15 $5 Pittsburgh Richmond St. Louis 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