Question 4: Network Design in Excel (25 marks)

Your company is planning to expand its operations from a direct delivery to the markets to a distribution network. The company needs to decide the location of its plants and warehouses. It is important to select strategic locations for its plants and warehouses so that it can maintain the freshness of its products and minimize total costs.

However, the company has not yet determined the number of plants and warehouses to open. The raw materials market is dominated by three big players (S1, S2, and S3). These companies

are interested in supplying raw materials but the available capacity for every supplier is limited.

Four potential sites have been identified as suitable locations to build the plants (P1, P2, P3, and P4). Based on the engineering designs, each of the plants will have a limited monthly capacity. There will be associated fixed costs to operate the plants each month. Additionally, transportation costs will be incurred to move the raw materials from suppliers to plants. Similarly, five candidate sites have been selected to install the warehouses (W1, W2, W3, W4, and W5). Due to space constraints, each warehouse will have a limited monthly capacity and will incur a monthly fixed cost. Again, transportation costs will be incurred to move the final product from plants to warehouses. There are currently ten major urban centers based on a market analysis (M1, M2, M3, M4, M5, M6, M7, M8, M9, and M10). All of these markets will be served from the warehouses. Transportation costs will be incurred from warehouses to markets. The market analysis also provided information about the expected average monthly demand per market.

For your model, consider the inclusion of all suppliers, plants, warehouses and markets. The transport is downstream and has to pass through all facility types. In the basic configuration, each facility on one tier can supply to all facility in the following tier downstream; with corresponding cost for the transport between each facility combination.

1. a) Implement a model in Excel, assuming data of your choice to test the model. Note that it is important to design the model such as data can be easily inserted in the model to calculate a solution. In the first model, only capacity constraints and cost on the connections should be considered.

2. b) The company decides to analyse if warehouses should be opened or not; i.e. as they have a high fixcost for operation. Implement a constraint that includes the fixcost for the warehouses.

Consider for the answer the introduced models and solution approached during the lecture.