Ok. So I have managed to do 12.18, but I am struggling to do 12.19. Can you show me how you would do 12.19 in excel/analytic solver? I know this is a linear interger problem, but I have no idea how to set it up in excel and what to put into analytic solver. Can you please help by showing me step by step?

12.18 Distribution A company has two factories, one at Liverpool and one at Brighton. In addition it has four depots with storage facilities at Newcastle, Birmingham, London, and Exeter. The company sells its product to six customers C1, C2,,C6. Customers can be supplied from either a depot or from the factory direct (see Figure 12.6). The company would like to determine: (1) What distribution pattern would minimize overall cost? (2) What the effect of increasing factory and depot capacities would be on distribution costs? (3) What the effects of small changes in costs, capacities and requirements would be on the distribution pattern? (4) Would it be possible to meet all customers preferences regarding suppliers and if so what would the extra cost of doing this be? 12.19 Depot Location (Distribution 2) In the distribution problem there is a possibility of opening new depots at Bristol and Northampton as well as of enlarging the Birmingham depot. It is not considered desirable to have more than four depots and if necessary Newcastle or Exeter (or both) can be closed down. The monthly costs (in interest charges) of the possible new depots and expansion at Birmingham are given in Table 12.8 together with the potential monthly throughputs. The monthly savings of closing down the Newcastle and Exeter depots are given in Table 12.9. The distribution costs involving the new depots are given in Table 12.10 (in per ton delivered). Table 12.10 Which new depots should be built? Should Birmingham be expanded? Should Exeter or Newcastle be closed down? What would be the best resultant distribution pattern to minimize overall cost? 258 The distribution costs (which are borne by the company) are known; they are given in Table 12.7 (in per ton delivered). Table 12.7 a A dash indicates the impossibility of certain suppliers for certain depots or customers. Certain customers have expressed preferences for being supplied from factories or depots which they are used to. The preferred suppliers are Each factory has a monthly capacity given below which cannot be exceeded: Each depot has a maximum monthly throughput given below which cannot be exceeded: Each customer has a monthly requirement given below which must be met