please solve problem 4 and make sure to use Microsoft Excel with Analytic Solver(or Open Solver) and not Excel Solver.

@ 53% .. T-Mobile Back 6:04 PM HW #2.pdf Problem 14 (20 points) A manufacturer of rotary pumpt is planning production for the next four months. The forecast demand for the rotary pumps is shown in the following table, Rotary pump SEP OCT NOV DEC Standard 650 875 790 1.100 Heavy duty 900 350 1.200 1,300 At the beginning of September, the warehouse is expected to be completely empty. There is room for no more than 1,800 rotary pumps to be stored. Holding costs for both types are per unit per month. Because workers are given time off during the holidays, the manufacturer wants to have at least 800 standard rotary pumps and 850 heavy duty rotary pumps already in the warehouse at the beginning of January Production costs at $125 per unit for standard rotary pumps and 5135 per unit for heavy duty rotary pumps. Because demand for materials is production costs are expected to me by 5% per month through the end of the year Labor to make the standard rotary pump is 0.45 hours per unit, making heavy duty rotary pumps takes 0.52 hours per unit of labor Management has agreed to schedule at least 1.000 hours per month of labor. As many as 200 extra bours per month are available to management at the same cost, except during the month of December, when only 100 extra bours are possible. Formulate the phen problem scenario as a linear program so as to determine the production schedule for standard and heavy duty rotary pumps for the four months Scfupave the problem using Microsoft Excel with Analytic Solver (or Open Solver). For the mathematical formulation, provide a complete mathematical description of the decision variables with units), objective function and constraints. You should use explicit (or implicit) form. Also, the spreadsheet model setup should provide clearly labeled values used for the decision variables, constraints, and objective function Problem 5 (20 points) A real estate developer is planning to build an office complex. There are three office sizexcurrently under consideration mall, medium and large Small offices can be rented for $600 per month medium offices can be rented for 750 per month and large offices can be rented for $1.000 per month. Each small office requires to square feet, cach medium office requires 800 square feet and each large office requires 1,000 square feet. The current plot of land available to the developer is 100,000 square feet. The developer wants to ensure that the office complex has at least 3 units of each offices. Moreover, zoning restrictions limit the total number of offices to 50. The developer solved this problem such that he could accrue maximum rent from the small, medium, and large offices he build Your job is to analyze this sensitivity report and answer the following Sensitivity Report Adjustable Cells Cell Name SBS4 Optimal Values Small SCS4 imal Values Medium SD154 Optimal Values Large Final Reduced Objective Allowable Allowable Cout Coefficient Increase Decrew 3 0 00 400 1E0 3 0 750 250 TE 44 1000 15:30 250 E Dashboard Calendar To Do Notifications Inbox @ 53% .. T-Mobile Back 6:04 PM HW #2.pdf Problem 14 (20 points) A manufacturer of rotary pumpt is planning production for the next four months. The forecast demand for the rotary pumps is shown in the following table, Rotary pump SEP OCT NOV DEC Standard 650 875 790 1.100 Heavy duty 900 350 1.200 1,300 At the beginning of September, the warehouse is expected to be completely empty. There is room for no more than 1,800 rotary pumps to be stored. Holding costs for both types are per unit per month. Because workers are given time off during the holidays, the manufacturer wants to have at least 800 standard rotary pumps and 850 heavy duty rotary pumps already in the warehouse at the beginning of January Production costs at $125 per unit for standard rotary pumps and 5135 per unit for heavy duty rotary pumps. Because demand for materials is production costs are expected to me by 5% per month through the end of the year Labor to make the standard rotary pump is 0.45 hours per unit, making heavy duty rotary pumps takes 0.52 hours per unit of labor Management has agreed to schedule at least 1.000 hours per month of labor. As many as 200 extra bours per month are available to management at the same cost, except during the month of December, when only 100 extra bours are possible. Formulate the phen problem scenario as a linear program so as to determine the production schedule for standard and heavy duty rotary pumps for the four months Scfupave the problem using Microsoft Excel with Analytic Solver (or Open Solver). For the mathematical formulation, provide a complete mathematical description of the decision variables with units), objective function and constraints. You should use explicit (or implicit) form. Also, the spreadsheet model setup should provide clearly labeled values used for the decision variables, constraints, and objective function Problem 5 (20 points) A real estate developer is planning to build an office complex. There are three office sizexcurrently under consideration mall, medium and large Small offices can be rented for $600 per month medium offices can be rented for 750 per month and large offices can be rented for $1.000 per month. Each small office requires to square feet, cach medium office requires 800 square feet and each large office requires 1,000 square feet. The current plot of land available to the developer is 100,000 square feet. The developer wants to ensure that the office complex has at least 3 units of each offices. Moreover, zoning restrictions limit the total number of offices to 50. The developer solved this problem such that he could accrue maximum rent from the small, medium, and large offices he build Your job is to analyze this sensitivity report and answer the following Sensitivity Report Adjustable Cells Cell Name SBS4 Optimal Values Small SCS4 imal Values Medium SD154 Optimal Values Large Final Reduced Objective Allowable Allowable Cout Coefficient Increase Decrew 3 0 00 400 1E0 3 0 750 250 TE 44 1000 15:30 250 E Dashboard Calendar To Do Notifications Inbox