The case company (referred to as ABC) is a leading supplier of maintenance, repair and operating (MRO) products to businesses and institutions in the United States, Canada and Mexico with an expanding presence in Japan, India, China and Panama. The company works with more than 3,000 suppliers and offers nearly 900,000 products ranging from the industrial adhesive used in manufacturing, to hand tools, janitorial supplies, lighting equipment, and power tools. When something is needed by one of their 1.8 million customers, it is often needed quickly. So, quick service and product availability are key drivers to ABC's success.

Your assignment involves studying the US distribution network in ABC's supply chain. ABC works with over 250 suppliers in China and Taiwan regions. The suppliers produce products to ABC's specifications and ship to the US using ocean freight carriers from four major ports in China and Taiwan. From these ports, products are shipped to US entry ports in either Seattle or Los Angeles. ABC currently has free on board (FOB) shipping contracts with Chinese and Taiwanese suppliers by which the supplier is responsible for all costs incurred until the product is delivered to the US entry ports. Approximately 35% of the containers enter Seattle and 65% enter the Los Angeles port. Upon arrival at the port cities, containers are inspected by federal agents. After passing through customs, products are loaded onto the 20-foot and 40-foot containers and shipped by rail to ABC's central distribution centre in Kansas City. Variable processing costs at both Los Angeles and Seattle ports are $5 per cubic meter (CBM). The rate for shipping the containers to Kansas City is $0.002 per CBM per mile (this rate applies to all shipping by rail).

In Kansas City, the containers are unloaded and processed through a quality assurance check. This costs $3 per CBM processed. A negligible percentage of the material may need to be sent back to the supplier. Errors in quantity and package size are often found requiring accounting adjustments. Items are stored in the Kansas City distribution centre, which serves nine warehouses in the US. The nine warehouses each place orders at the distribution centre that contains all the items to be replenished. Kansas City consolidates the items on order onto pallets and ships them on 53-foot trucks to warehouses. Truck freight cost $0.025 per CBM (this rate applies to all shipping by truck) per mile. The demand forecasts for the items purchased from China/Taiwan for next year and shipping distances are given in Table 1.

Table 1: Demand and distance data

	Demand (CBM)		Distances (Miles)
Warehouse	Average	% of total Demand		From Kansas City	From Los Angeles	From Seattle
Kansas City	20,900	11%		0	1,620	1,870
Cleveland	17,100	9%		800	2,350	2,410
New Jersey	24,700	13%		1200	2,780	2,890
Jacksonville	15,200	8%		1150	2,420	2,990
Chicago	22,800	12%		520	2,020	2,060
Greenville	15,200	8%		940	2,320	2,950
Memphis	17,100	9%		510	1,790	2,330
Dallas	22,800	12%		500	1,430	2,130
Los Angeles	34,200	18%		1620	0	1,140
Total	190,000

Although a high percentage of demand was from warehouses in south and east of Kansas City, the question has surfaced concerning the 18% that will be shipped to Kansas City and then shipped back to the Los Angeles warehouse. This double-transportation could potentially be eliminated if ABC builds a new distribution centre in Los Angeles. The idea is to ship material arriving at the Seattle port by rail to a new Los Angeles distribution centre, which would be located at the current location of the Los Angeles warehouse.

It is estimated that the Los Angeles facility could be upgraded at a one-time cost of $1,500,000 and then operated for $350,000 per year. In the new Los Angeles distribution centre, containers would be unloaded and processed through a quality assurance check, just as is now done in Kansas City. The cost for doing this would be $5 per CBM processed. The cost associated with transporting the containers from the Los Angeles port to the Los Angeles distribution centre is negligible. After the material is processed in Los Angeles, the amount needed to replenish the Los Angeles warehouse (approximately 18%) would be kept and the rest sent by rail to Kansas City. It would then be stocked in the Kansas City distribution centre and used to replenish the other warehouses.

ABC management feels that it may be possible to make the change, but they are unsure if this would be an economical decisionand whether it would be a strategic change for better.

1. Analyse the US supply network, identify and explain other SSCM strategies that can be applied to optimise network costs? Using Linear/Integer or other Programming mathematical problem solving techniques research other distribution cost and network design methods.

Determine answers to the following questions.

A. Should the new Los Angeles distribution centre be added as a new node in the network? Is this strategically something that ABC should do? (8 marks)

Note: For the upgraded supply chain network, you need to consider Los Angeles as another distribution centre (this means you now have two distribution centres - Kansas City and Los Angeles) and you need to optimise the distribution network and minimise the total cost and make a strategic decision on Los Angeles as the distribution centre. Use Excel solver to determine the distribution plan and minimum total cost.

B. If there are multiple distribution centres in Kansas City, Los Angeles and Seattle, what would be the optimal decision on distribution and total cost for this? Assume any additional data you require. (5 marks)

Note: This one is the extension of number (ii), considering an additional distribution centre in Seattle. You may need to assume some additional data such as facility and operating cost. Please mention those assumptions in your report. Use Excel solver to determine the distribution plan and minimum total cost.

C. What factors have ABC not considered that might be important in moving strategically to the new SC configuration. Base your arguments on academic evidence of concepts or practices that can be applied in designing a network, and then explain the possible solutions? (5 marks)

how to do in excel? i need in excel and also word