2. Optimisation with spreadsheets 25 marks Learning Objective: The purpose of this part of the assignment is to assess your understanding of how spreadsheets can be used in optimisation to find a lowest cost answer to a common operations problem. You must also be able to demonstrate the ability to adjust the spreadsheet to account for changes in situations and understand how to analyse a changing situation. Optimising transportation for Always Healthy: Currently, Always Healthy operates THREE factories (Auckland, Wellington, and Christchurch). One of the items they are making is a special diabetes test kit which they supply to healthcare retail outlets around New Zealand via 9 distribution centres (DCs). Table 2 shows the amount of weekly demand at each DC. Table 3 contains information about the weekly supply capacity from each factory. Table 4 shows shipping costs for Always Healthy between each factory and the 9DCs. Tahlin 5 a) Using this information, find the lowest total cost for Always Healthy to meet the demand for diabetes test kit in the DCs using the three factories. Report to management on the total cost for the shipping and present in a table how many units from each factory are allocated to be shipped to each DC. b) Assume that Always Healthy plans to close their Christchurch factory (Factory 3). All of the capacity is shifted to the other factories. Table 5 lists the new levels of capacity at each factory. Report on the new total cost for the shipping and present in a table how many units of the diabetes test kit from each factory are now allocated to be shipped to each DC. (Word limit: approximately 100 words) Include images of your Excel workings in the report