Can you answer only question number 2 with an Linear Programming formula and a Linear Programming in excel with showing the solver as well?

This is a supply chain class question.

CASE STUDY 1: Managing Growth at SportStuff.com Adapted from the case that appears at the end of Chap. 5 in your textbook The numbers in this case description are slightly different. Therefore, use this description and not the one in the textbook. In December 2007, Sanjay Gupta and his management team were busy evaluating the performance at SportStuff.com over the previous year. Demand had grown by 80 percent. This growth, however, was a mixed blessing. The venture capitalists supporting the company were very pleased with the growth in sales and the resulting increase in revenue. Sanjay and his team, however, could clearly see that costs would grow faster than revenues if demand continued to grow and the supply chain network was not redesigned. They decided to analyze the performance of the current network to see how it could be redesigned to best cope with the rapid growth anticipated over the next three years. SportStuff.com Sanjay Gupta founded SportsStuff.com in 2004 with a mission of supplying parents with more affordable sports equipment for their children. Parents complained about having to discard expensive skates, skis, jackets, and shoes because children outgrew them rapidly. Sanjay's initial plan was for the company to purchase used equipment and jackets from families and any surplus equipment from manufacturers and retailers and sell these over the Internet. The idea was very well received in the marketplace, demand grew rapidly, and by the end of 2004 , the company had sales of $0.8 million. By this time a variety of new and used products were being sold and the company received significant venture capital support. In June 2004, Sanjay leased part of a warehouse in the outskirts of St. Louis to manage the large amount of product being sold. Suppliers sent their product to the warehouge. Customer orders were packed, and shipped by UPS from there. As demand grew, SportStuff,com leased more space within the warchouse. By 2007, SportStuff,com leased the entire warchouse and orders were being shipped to customers all over the United States. Management divided the United States into six customer zones for planning purposes. Demand from each customer zone in 2007 was as shown in Table 1. Table 1. Regional demand at sportstuff,com for 2007 Sanjay estimated that the next three years would see a growth rate of about 85 per cent per year, after which demand would level off. The Network Options Sanjay and his management team could see that they needed more warchouse space to cope with the anticipated growth. One option was to lease more warchouse space in St. Louis itself. Other options included leasing warehouses all over the country. Leasing a warchouse involved fixed costs based on the size of the warehouse and variable costs that varied with the quantity shipped through the warchouse. Four potential locations for warchouses were identified in Denver, Seattle, Atlanta, and Philadelphia. At each location, sportstuff.com could lease either a small (about 100,000 sq. ft.) or large ( 200,000 sq. ft.) warchouse. Small warchouses could handle a flow of up to 2 million units per year, whereas larger warehouses could handle a flow of up to 4 million units per year. The current warchouse in St. Louis was small. The fixed and variable costs of small and large warehouses in different locations are shown in Table 2. The variable cost includes receiving, material handling, and order fulfillment costs. Sanjay estimated that the inventory holding costs at a warehouse (excluding warehouse expense) had a fixed cost component (regardless of facility volume) of $475,000 and a variable cost component of about $0.165 per flow unit per year. Thus, a warehouse handling 1,00,000 units per year incurred an inventory holding cost of $475,000+$165,000=$640,000 for the year. SportStuff.com charged a flat fee of $3 per shipment sent to a cultomer. An average customer order contained four units, SportStuff.com in turn contracted with UPS to handle all its outbound shipments. UPS charges were based on both the origin and the destination of the shipment and are shown in Table 3. Table 3. UPS charges per shipment (four units of product) Management estimated that inbound transportation costs for shipments from suppliers were likely to remain unchanged, no matter what warchouse configuration was selected. 1. Sanjay is first evaluating the option of leasing more warehouse space in St. Louis. For each of the upcoming three years, what is the total cost that SportStuff.com would incur if all warchouses leased are in St. Louis, i.e., it did not lease any warchouse space outside of St. Louis? 2. Next, suppose Sanjay wants to consider evaluating a nationwide warehouse network using the locations specified in the case. Considering each year one-by-one, what supply chain network configuration (i.e., number and location of warehouses, their capacities etc.) do you recommend for SportStuff.com? CASE STUDY 1: Managing Growth at SportStuff.com Adapted from the case that appears at the end of Chap. 5 in your textbook The numbers in this case description are slightly different. Therefore, use this description and not the one in the textbook. In December 2007, Sanjay Gupta and his management team were busy evaluating the performance at SportStuff.com over the previous year. Demand had grown by 80 percent. This growth, however, was a mixed blessing. The venture capitalists supporting the company were very pleased with the growth in sales and the resulting increase in revenue. Sanjay and his team, however, could clearly see that costs would grow faster than revenues if demand continued to grow and the supply chain network was not redesigned. They decided to analyze the performance of the current network to see how it could be redesigned to best cope with the rapid growth anticipated over the next three years. SportStuff.com Sanjay Gupta founded SportsStuff.com in 2004 with a mission of supplying parents with more affordable sports equipment for their children. Parents complained about having to discard expensive skates, skis, jackets, and shoes because children outgrew them rapidly. Sanjay's initial plan was for the company to purchase used equipment and jackets from families and any surplus equipment from manufacturers and retailers and sell these over the Internet. The idea was very well received in the marketplace, demand grew rapidly, and by the end of 2004 , the company had sales of $0.8 million. By this time a variety of new and used products were being sold and the company received significant venture capital support. In June 2004, Sanjay leased part of a warehouse in the outskirts of St. Louis to manage the large amount of product being sold. Suppliers sent their product to the warehouge. Customer orders were packed, and shipped by UPS from there. As demand grew, SportStuff,com leased more space within the warchouse. By 2007, SportStuff,com leased the entire warchouse and orders were being shipped to customers all over the United States. Management divided the United States into six customer zones for planning purposes. Demand from each customer zone in 2007 was as shown in Table 1. Table 1. Regional demand at sportstuff,com for 2007 Sanjay estimated that the next three years would see a growth rate of about 85 per cent per year, after which demand would level off. The Network Options Sanjay and his management team could see that they needed more warchouse space to cope with the anticipated growth. One option was to lease more warchouse space in St. Louis itself. Other options included leasing warehouses all over the country. Leasing a warchouse involved fixed costs based on the size of the warehouse and variable costs that varied with the quantity shipped through the warchouse. Four potential locations for warchouses were identified in Denver, Seattle, Atlanta, and Philadelphia. At each location, sportstuff.com could lease either a small (about 100,000 sq. ft.) or large ( 200,000 sq. ft.) warchouse. Small warchouses could handle a flow of up to 2 million units per year, whereas larger warehouses could handle a flow of up to 4 million units per year. The current warchouse in St. Louis was small. The fixed and variable costs of small and large warehouses in different locations are shown in Table 2. The variable cost includes receiving, material handling, and order fulfillment costs. Sanjay estimated that the inventory holding costs at a warehouse (excluding warehouse expense) had a fixed cost component (regardless of facility volume) of $475,000 and a variable cost component of about $0.165 per flow unit per year. Thus, a warehouse handling 1,00,000 units per year incurred an inventory holding cost of $475,000+$165,000=$640,000 for the year. SportStuff.com charged a flat fee of $3 per shipment sent to a cultomer. An average customer order contained four units, SportStuff.com in turn contracted with UPS to handle all its outbound shipments. UPS charges were based on both the origin and the destination of the shipment and are shown in Table 3. Table 3. UPS charges per shipment (four units of product) Management estimated that inbound transportation costs for shipments from suppliers were likely to remain unchanged, no matter what warchouse configuration was selected. 1. Sanjay is first evaluating the option of leasing more warehouse space in St. Louis. For each of the upcoming three years, what is the total cost that SportStuff.com would incur if all warchouses leased are in St. Louis, i.e., it did not lease any warchouse space outside of St. Louis? 2. Next, suppose Sanjay wants to consider evaluating a nationwide warehouse network using the locations specified in the case. Considering each year one-by-one, what supply chain network configuration (i.e., number and location of warehouses, their capacities etc.) do you recommend for SportStuff.com