JUST PART THREE PLEASE

A small company named Logr Transit has at present 3 manufacturing facilities (suppliers) called Suppliers #1, 2, and #3 to supply goods to its 3 retail stores (customers) named Customer A, B, and C. Dae to eustomer demands at stores increasing. the company docided to build a new manafacturing facility (Supplier) that will have a capacity of 200 units per weck. Affer sereening many potential sites for the new facility, Toledo and Cincinnati have been determined to be the two leading candidate locations. Assume that Derull Thuswordution Cosil from Suppliers to Customers will be the main determinant to determine which of the two locations to be selected as the new plant site. The following two tables provide information on i) the capacities of old suppliers and new supplier candidates; ii) The per unit Transportation cost from existing Suppliers to customers and per unit Transportation cost from the new candidate supplier location to customers, and 3) demands from customers and available capacity of each supplier. One of the most important tasks of this project is to determine which New Supplier locations will result in the smallest total transportation costs. The following is what you need to do to complete this project Part 1: LP formulation a. Give a summary of the projoct Problem: - Describe in WORDS what Decision you need to make, and criterion on which your decision is based, and how Linear Programming models can be used to help you make your decision. b. Formulate the project problem into TWO L.P models. - To make the right decision for the project, you need to formulate TWO Linear Programming models. - You may only provide a complete and detailed L.P formulation for only one of the models. For example, assuming "TOLEDO" is chosen as the new plant. Then, give brief explanations of the difference in LP model formulation when "CVNCNNATT" is chosen as the new plant. c. Giporsant A Linear Programming Model formulation should be a Mathematical Model that contains - Clearly defined decision variables - A linear function of the Objective - All constraints in the form of Linear incqualities cqualities for which your decision variables need to satisfy, including non-negativity constraints. Part 2. Use Excel Solver to solve the L.P models formalated. Finirimingou sbould complete Part I before yoa start Part 2 a) The Excel file should contain 4 sheets - Sheets 1\&2 are shoets oontaining the Excel Solver setups and Solutions of the two LP models. - Name the Sheetl as LP Toleda, Sheet2 as LP Cincinant, cerreppondingly - Please give Enough Explanations oe cell definitices in the Excet Spreadiheet Setup so that you can obtain easy-to-fead Solver Output Reports - Sheets 3.4 contains the Annver nport and Senaithity repors generated by the Solver Outputs from solving the LP model of the Toledo Case. Part 3. Use the Solver results from the two LP models from Part 2 to answer the following questions. a) Summarize the Opuimal Solutiors and the Oprimal Objecrive Falues of Linear Programming models (Tolelo and Cincinnati). Then draw a conclusion on which site, "Toleds" or "Cincinnati", should be chosen as a New Plant location. b) Use the information from the Sensimivig Report from the Tolols ease to answer the following four questions. acceptable to answer the questions by changing the Parameter and resolving the L.P modeL. Where you come wp with the answers i. If the shipping cost from Sieplier 2 to Demand A is so $14, will the owcrall optimal shipping quantities from Suppliers to Customers semain the same? What will be Total Transportation Cost now? Exylain. ii. If the shipping cost from Sipplier 2 to Demand B is Roficol by S4 will this affect optimal shipping quantities from Suppliers to Customen? Eylain. iit. If the new Tolodo capacity is to 220 units, how mach Total Transpertation costs can be reduced? Explain. iv. If the new Toledo capacity is to 150 unitx, can you determine the optimal total shipping cost from the sensitivity reports? Explain. Important Order to complete this project: Ferporiner A Linear Programming Model formulatice is a Mathematical Model thar contains three parts. You should complete Part 1 before you start Part 2. You should complete Part 2 before you start Part 3. Only the Solver Solinion \& Senvianity Report can be used to answer viestions of Part 3