The location of the five customers and the number of shipments per year needed by each customer are given in Table 1. All locations are in miles, relative to the point x = 0 and y = 0. 

 

Customer	Location	Shipments/year
1	(5, 10)	200
2	(10, 5)	150
3	(0, 12)	200
4	(12, 0)	300
5	(7,  8)	250

 

 

Following initial investigation, the company has identified two possible warehouse locations:

• L1 (3, 4) with an annual capacity of 750 shipments
• L2 (100, 110) with an annual capacity of 1500 shipments. 

 

Company A can build either or both of these warehouses. The annualized costs to build a warehouse are £50,000 in L1 and £30,000 in L2 respectively. If only one warehouse is built, it will ship to all customers. However, if both warehouses are built, then the company must decide which warehouse will ship to each customer. There is a travelling cost of £1 per mile.  

Develop a single model to minimize total annual cost, where a straight-line distance is used to represent the distance between two locations and then use the Excel Solver to optimize it.