Problem 4: [30 points]: [You must formulate the problem, and…
Problem 4: [30 points]: [You must formulate the problem, and solve it using both Excel Solver and Graphical LP Solver at: https://linprog.tomchen.org/] A financial services company processes applications for Home Mortgages and Commercial Loans for its clients. Application processing has three steps – Screening, Credit Check, and Final Review. Both types of applications are first reviewed by the screening department. It takes this department 1 hour to review each Home Mortgage application and 2 hours to review each Commercial Loan application. After the screening, a credit check is performed, which takes 2 and 3 hours for Home Mortgage and Commercial Loan application respectively. Finally, the applications are sent to a loan officer for a final evaluation. It takes a loan officer 3 hours to do a final review of a Home Mortgage application and 5 hours to do a final review of a Commercial Loan application. The company also wants to ensure that it processes at least 250 Home Mortgage applications and 350 commercial loan applications each week. The company currently employs 30 staff members in the screening department, 50 staff members in the credit check department, and 80 loan officers for final evaluation. Each employee works for 40 hours per week. The company makes a $600 profit on each Home Mortgage application and a $750 profit on each Commercial Loan application. In order for the process to be effective, the company restricts the maximum number of applications (Home Mortgage plus Commercial Loan applications combined) to no more than 700 per week. a) Formulate the problem as a Profit-maximizing linear programming problem. Prepare a layout (template) to solve the problem on an Excel Spreadsheet. The layout must have proper format and appropriate labels. I need to see you solve the problem in Excel Solver in Honorlock recording to earn these points. [10 points] b) Set up the Model in Solver and solve the problem, generate all the Solver Reports. Explain your solution (optimal solution and the what the optimal value of the objective function is in a sentence or two. I should be able to solve the problem with your Solver settings when I grade, for you to earn these points. [10 points] c) Solve the problem using “Graphical LP Solver at: https://linprog.tomchen.org/” and paste the screenshot onto Excel worksheet. I need to see you solve the problem in Graphical LP Solver at: https://linprog.tomchen.org/ in Honorlock recording to earn these points. [10 points] Upload and Submit your Excel for this Question. If you don’t upload your Excel file, Exam cannot be graded. So, zero points will be recorded as the grade. NOTE: Do not violate academic integrity ONLY ONE SYSTEM IS ALLOWED FOR THE EXAM. Any allowed resource on Canvas you use for the exam must be visible in your Honorlock recording. Using a second computer or other gadgets is a violation of Academic Integrity. Honorlock recording must show the entire duration of your exam without any breaks. I must see you working in Excel and answering questions in your Honorlock recording. If I don’t see you working using Excel functions and formulas in Excel in your Honorlock recording, that would be an academic integrity violation, and ZERO POINTS will be recorded as your grade. All academic integrity violations will be reported to USF for further disciplinary action, that could be receiving an “F” grade for the course to other severe actions such as dismissal from the program, … etc. So, please follow instructions and maintain academic integrity.
Read Details