Question $1$

Optimisation Problem$-$ Supply Network Design

Munya & Chibs Ltd$.$ is a market$-$leading manufacturer based in the UK$.$ The demand forecast in different regions estimated for next year is as follows:

$\backslash$table$[[$Region$,$Scotland,Wales,London,South West$],[$Demand Forecast $(000),180,95,175,130]]$

The managers are designing the manufacturing network. Four potential locations are to be considered. In addition, based on the business plans, there are two available capacity options for establishment of the new factories. The capacity of the plant could be $180000,$ or $250000$ units per year. The costs of producing and shipping a product unit to each of four markets along with the annual fixed costs at four locations are provided as below:

$\backslash$table$[[,$Production and transportation cost $(),\backslash$table$[[$Annual fixed cost of $180000],[$plant $($M$)]],\backslash$table$[[$Annual fixed cost of $250000],[$plant $($M$)]]],[,$Scotland,Wales,London,South West$],[$Swindon$,190,130,160,125,4,4\mathrm{.}40],[$Birmingham$,180,140,145,150,3\mathrm{.}5,3\mathrm{.}85],[$Manchester$,150,148,155,163,3\mathrm{.}9,4\mathrm{.}29],[$Glasgow$,120,160,180,203,3\mathrm{.}1,3\mathrm{.}41]]$

Please use Microsoft Excel Solver to solve this question and send the template solved on excel. Show each working

a$).$ How much it cost if the management want to open a plant in all four locations? Advise them the capacity of each plant. Is it a good decision?

$(20$ marks$)$

b$).$ Where should the company establish its plants to minimise the total cost? What is the optimal capacity selected for new plants at each optimal location?

$(15$ marks$)$

c$).$ Discuss and interpret the results obtained. Conduct a sensitivity analysis to extend your discussion. What other factors should they take into consideration to make a more reliable decision?

$(5$ marks$)$