Question 1: Supplying Noodle Packets (15 marks) After the success of their marketing campaign for Korean-style noodles, the Statistics students have seen a significant increase in the demand for Korean-style noodles amongst Auckland students. As such, they are concerned with how they can efficiently send the crates of noodles from their import hubs to the required distribution centres. They import crates of noodles at the three hubs A, B, and C, but need to ship them to their distribution centres in Mount Wellington, Onchunga, Epsom, Avondale and Takapuna so they can be sold or sent on to students. These centres need 150, 60, 40, 100, and 50 crates respectively, cach month. The hubs import 150, 175 and 75 crates respectively, cach month. The cost (in $) for transporting a single crate from a hub to a distribution centre is given in the table below. A B Mount Wellington Onehunga Epsom Avondale Takapuna 0.75 1.25 2.75 2.5 5 5.5 3.5 4.5 4 2 4.5 3.5 3.5 3 1.5 The Statistics students wish to minimise the cost of shipping the noodles from the import hubs to the distribution centres. (a) Carefully formulate the above problem as a balanced transportation problem. Use the standard Tabular Transportation format given in lectures with the supply constraints in the rows, and remember to include all costs, supplies, demands and any bounds that are needed. (b) Enter the problem from part (a) into Excel and solve it. Hand in three screenshots or printouts: two of the resulting Excel spreadsheet: one showing the optimal values and the other showing the formulac; and one of the resulting sensitivity report. Follow instructions regarding output from the cover page. Be sure to also submit your Excel workbook to Canvas. (c) Give a brief (1-2 sentences) non-technical interpretation of Excel's answer to the Statistics students. The following describe independent changes to the problem formulated in part (a). Use your formula- tion and / or Excel output to answer the following questions. You should NOT re-solve these problem extensions in Excel (d) Due to another importer cancelling their order, there is now an additional 100 units available at hub A. Detail the changes required to the Tabular Format in (a) to account for this change so that it is still a balanced transportation problem. (e) How will the optimal solution and shipping cost change if the cost of transportation per crate from hub B to Onchunga increases by $0.50 per unit? () By how much will the shipping cost change if the demand at the Mount Wellington centre decreases by 10 crates? (g) The shipping company has introduced a new requirement that if any shipping is to occur between hub B and Takapuna, at least 60 crates must be shipped. There is also a fixed cost of $10 for any shipping to occur on this route, on top of the per-crate cost. How will the LP formulation (i.c. mathematical formulation, not Excel) of the problem change as a result of this new requirement? The LP uses the following syntax: The variables bij = the number of crate to be shipped from hub i to centre j, i c{A,B,C), je{M,O, E, A,T}. Ensure that you define any new variables introduced. You are not required to give the full formulation - only show the changes required
