Order from us for quality, customized work in due time of your choice.
LP Group Project Guideline
Topic: Application of Linear Programming
Group Size: 1–3 students (Self-Grouped)
Due Date: April 7, 2024
Task Outlines: The detailed Project is given in the pages following this guideline (you may read the project first before you continue on the guideline). Here is a summary of what you need to accomplish.
Part 1. Describe the problem and formulate the problem into two Linear Programming models.
Part 2. Use the Solver from Excel Spreadsheet to solve the two formulated LP models.
Part 3. Summarize solver solutions results and use the solver output reports to answer questions asked at the end of the project.
Project Report: Each group is required to create ONE project report that will contain 2 files.
File 1 – a Word File
• Name the file: MSD301-Sec#_Proj_Last names of group members
• This file should contain
i) Include Name of all group members, class time/session # at the beginning of the document
ii) Part 1. Complete and detail Linear Programming formulation
iii) Part II. Answers to all questions.
File 2 – an Excel File
• Name the file: MSD301-Sec#_Proj_Last names of group members
• Part II. Solve the formulated Linear Programming models from Part I using Excel Solver.
Submission: Each Group is required to submit ONE Project Report (2 files) using e-mail attachments to Lgong@Rider.edu.
• Your Email needs to contain the required subject: MSD301-Sec#_Proj_Last names of group members.
• If you use Google Docs and/or Google Sheets to create your report(s), ensure that your instructor can edit your file.
• Please keep backups of your files.
Questions? Please do not hesitate to ask the instructor if you have any
Remark: Students are strongly encouraged to read the transportation example (Example 3) in the class notes for help. A separate Zoom video clip (MSD301_Ch BAMod-B Linear Programming Part 4_Example/Project) can also be found in the Canvas Media Gallery. This example can help you to complete the project.
Location Section Project
A small company named LogTransit 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. Due to customer demands at stores increasing, the company decided to build a new manufacturing facility (Supplier) that will have a capacity of 200 units per week. After screening many potential sites for the new facility, Toledo and Cincinnati have been determined to be the two leading candidate locations.
Assume that Overall Transportation Costs 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 project 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 LP 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 LP 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 “CINCINNATI” is chosen as the new plant.
c. Important: 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 inequalities/equalities for which your decision variables need to satisfy, including non-negativity constraints.
Part 2. Use Excel Solver to solve the LP models formulated.
Important: you should complete Part 1 before you start Part 2
a) The Excel file should contain 4 sheets
• Sheets 1&2 are sheets containing the Excel Solver setups and Solutions of the two LP models.
o Name the Sheet1 as LP Toledo, Sheet2 as LP Cincinnati, correspondingly
o Please give Enough Explanations on cell definitions in the Excel Spreadsheet Setup so that you can obtain easy-to-read Solver Output Reports
• Sheets 3&4 contains the Answer report and Sensitivity reports generated by the Solver Outputs from solving the LP model of the Toledo Case.
Part 3. Use the Solver output reports from the two LP models from Part 2 to answer the following questions.
a) Summarize the Optimal Solutions and the Optimal Objective Values of Linear Programming models (Toledo and Cincinnati). Then draw a conclusion on which site, “Toledo” or “Cincinnati”, should be chosen as a New Plant location.
b) Use the information from the Sensitivity Report from the Toledo case to answer the following four questions.
Important: questions should be answered using the information from the Sensitivity Report ONLY (it is not acceptable to answer the questions by changing the Parameters and resolving the LP model.
Please Give clear explanations and point out locations (Exact cell position) in the Sensitivity Report where you come up with the answers.
i. If the shipping cost from Supplier 2 to Demand A is increased to $14, can you conclude the optimal solution/(shipping quantities from Suppliers to Customers) remains the same, and provide the updated optimal Transportation Cost based on the sensitivity report from the solver? Please provide the updated optimal Transportation cost if you can. Explain.
ii. If the shipping cost from Supplier 2 to Demand A is Reduced by $2, can you conclude the optimal solution/(shipping quantities from Suppliers to Customers) remains the same, and provide the updated optimal Transportation Cost based on the sensitivity report from the solver? Please provide the updated optimal Transportation cost if you can. Explain.
iii. If the new Toledo capacity is Increased by 30 units, can you determine the updated Optimal Transportation cost based on the sensitivity report from the solver? Please provide the updated cost if you do. Explain.
iv. If the new Toledo capacity is Reduced by 30 units, can you determine the updated Optimal Transportation cost based on the sensitivity report from the solver? Please provide the updated cost if you do. Explain.
Important Order to complete this project:
Important: A Linear Programming Model formulation is a Mathematical Model that contains three parts.
Important: You should complete Part 1 before you start Part 2.
Important: You should complete Part 2 before you start Part 3. Only the Solver Solution & Sensitivity Report can be used to answer questions of Part 3.
Order from us for quality, customized work in due time of your choice.