Please answer q2 with the solver (including the functions in each cells) also in
ID: 378168 • Letter: P
Question
Please answer q2 with the solver (including the functions in each cells) also including the lp formulation in writing
CASE STUDY Managing Growth at SportStuff.com In December 2008, Sanjay Gupta and his management jackets from families and any surplus equipment from team were busy evaluating the performance at manufacturers and retailers and sell these over the Internet. SportStuff.com over the previous year. Demand had The idea was well received in the marketplace, demand grown by 80 percent. This growth, however, was a grew rap mixed blessing. The venture capitalists supporting the sales of $0.8 million. By this time, a variety of new company were very pleased with the growth in sales and and used products were being sold, and the company the resulting increase in revenue. Sanjay and his team, received significant venture capital support. however, could clearly see that costs would grow faster than revenues if demand continued to grow and the in the outskirts of St. Louis to manage the large supply chain network was not redesigned. They decided amount of product being sold. Suppliers sent their to analyze the performance of the current network to see product to the warehouse. Customer orders were how it could be redesigned to best cope with the rapid packed and shipped by UPS from there. As demand growth anticipated over the next three years idly, and by the end of 2004, the company had In June 2004, Sanjay leased part of a warehouse grew, SportStuff.com leased more space within the warehouse. By 2007, SportStuff.com leased the entire warehouse and orders were being shipped to customers all over the United States. Management divided the United States into six customer zones for SportStuff.com Sanjay Gupta founded SportStuff.com in 2004 with a mis- sion of supplying parents with more affordable sports planning purposes. Demand from each customer zone equipment for their children. Parents complained about in 2007 wa having to discard expensive skates, skis, jackets, and shoes that the next three years would see a growth rate of because children outgrew them rapidly. Sanjay's initial about 80 percent per year, after which demand would plan was for the company to purchase used equipment and level off. s as shown in Table 5-15. Sanjay estimated (continued)Explanation / Answer
1.
Demand
Demand 2007
Northwest
320,000
Southwest
200,000
Upper Midwest
160,000
Lower Midwest
220,000
Northeast
350,000
Southeast
175,000
Total Demand
1,425,000
The capacity of the current warehouse in St. Louis is 2,000,000 units per year, more than enough to accommodate 2007’s demand.
Costs are calculated as:
Fixed Warehouse Cost = $220,000
Variable Warehouse Cost = ($0.20/unit)(1,425,000 units) = $285,000
Holding Cost = $475,000+0.165*1,425,000 = $710,125
Shipping Cost = $1,068,750
Shipping Recouped from Customer = (1,425,000/4)*$3 = $1,068,750
Total Network Costs = $1,254,500
Note that the spreadsheet used for these calculations employed the linear holding cost function of $475,000+0.165F, which results in a holding cost of $710,125 and a total plan cost of $1,254,500.
Subsequent holding costs will use the single linear function to determine holding costs.
If demand increases by 80% per year for 2008, 2009, and 2010 and SportStuff.com wishes to use St. Louis as their only warehouse center, the following demands and costs are realized. The optimal solution for 2008 calls for one large warehouse rather than two small ones.
Demand 2008
Northwest
576000
Southwest
360000
Upper Midwest
288000
Lower Midwest
396000
Northeast
630000
Southeast
315000
Total Demand
2,565,000
Total Shipping Cost
$1,994,625
Total Holding Cost
$898,225
Warehouse Cost
$888,000
Total Shipping Recoup
$1,923,750
TOTAL COST
$1,857,100
For 2009, one small and one large warehouse is optimal.
Demand 2009
Northwest
1036800
Southwest
648000
Upper Midwest
518400
Lower Midwest
712800
Northeast
1134000
Southeast
567000
Total Demand
4,617,000
Total Shipping Cost
$3,590,325
Total Holding Cost
$2,473,610
Warehouse Cost
$1,518,400
Total Shipping Recoup
$3,462,750
TOTAL COST
$4,119,585
For 2010, one small and two large warehouses are optimal
Demand 2010
Northwest
1866240
Southwest
1166400
Upper Midwest
933120
Lower Midwest
1283040
Northeast
2041200
Southeast
1020600
Total Demand
8,310,600
Total Shipping Cost
$6,462,585
Total Holding Cost
$5,538,747
Warehouse Cost
$2,632,120
Total Shipping Recoup
$6,232,950
TOTAL COST
$8,400,502
2.
Small warehouses in Seattle and St. Louis
Northwest
Southwest
Upper Midwest
Lower Midwest
Northeast
Southeast
Total Supply
Seattle
576,000
360,000
0
0
0
0
936,000
Denver
0
0
0
0
0
0
0
St. Louis
0
0
288,000
396,000
630,000
315,000
1,629,000
Atlanta
0
0
0
0
0
0
0
Philadelphia
0
0
0
0
0
0
0
Total Demand
576,000
360,000
288,000
396,000
630,000
315,000
Total Shipping Cost
$1,688,625
Total Holding Cost
$1,373,225
Warehouse Cost
$1,033,000
Total Shipping Chg
$1,923,750
TOTAL COST
$2,171,100
A lower total system cost is achievable if SportStuff abandons their small St. Louis warehouse and opts for a single large warehouse in Atlanta.
Total Shipping Cost
$2,068,875
Total Holding Cost
$898,225
Warehouse Cost
$888,000
Total Shipping Chg
$1,923,750
TOTAL COST
$1,931,350
For 2009: Small warehouses in Seattle and St. Louis plus a small warehouse in Atlanta
Northwest
Southwest
Upper Midwest
Lower Midwest
Northeast
Southeast
Total Supply
Seattle
1,036,800
648,000
0
0
0
0
1,684,800
Denver
0
0
0
0
0
0
0
St. Louis
0
0
518,400
497,096
984,504
0
2,000,000
Atlanta
0
0
0
215,704
149,496
567,000
932,200
Philadelphia
0
0
0
0
0
0
0
Total Demand
1,036,800
648,000
518,400
712,800
1,134,000
567,000
Total Shipping Cost
$2,897,775
Total Holding Cost
$2,186,805
Warehouse Cost
$1,663,400
Total Shipping Chg
$3,462,750
TOTAL COST
$3,285,230
For 2010, Small warehouses in all cities results in
Total Shipping Cost
$4,965,995
Total Holding Cost
$3,746,249
Warehouse Cost
$2,892,120
Total Shipping Chg
$6,232,950
TOTAL COST
$5,371,414
Northwest
Southwest
Upper Midwest
Lower Midwest
Northeast
Southeast
Total Supply
Seattle
1,866,240
116,640
0
0
0
0
1,982,880
Denver
0
1,049,760
933,120
0
0
0
1,982,880
St. Louis
0
0
0
1,283,040
41,200
0
1,324,240
Atlanta
0
0
0
0
0
1,020,600
1,020,600
Philadelphia
0
0
0
0
2,000,000
0
2,000,000
Total Demand
1,866,240
1,166,400
933,120
1,283,040
2,041,200
1,020,600
A lower cost solution of small warehouses in Seattle and Atlanta and large warehouses in Denver and Philadelphia results in a savings of $248,018.
Total Shipping Cost
$5,082,976
Total Holding Cost
$3,271,249
Warehouse Cost
$3,002,120
Total Shipping Chg
$6,232,950
TOTAL COST
$5,123,395
Demand 2007
Northwest
320,000
Southwest
200,000
Upper Midwest
160,000
Lower Midwest
220,000
Northeast
350,000
Southeast
175,000
Total Demand
1,425,000