A drug company produces six different products at its plant. Relevant details are shown on the Problem 4 worksheet. On that worksheet, note that production of each product requires labor and raw material. The problem is to formulate and use an optimization model to determine a production mix (e.g., how many pounds to produce of each product) that maximizes profit when 4,500 hours of labor and 1,350 pounds of raw material are available. Display the profit to two decimal places. The Changing Cells are not to be integer-constrained, and negative production should be ruled out. 2. [4 points] Assign appropriate range names to the cell with the objective function the range of and any other cells/ranges that will be used in the solver dialog box. There should be no cells in the Solver without range names. 3. [8 points]Formulate the model by entering the formulas for profit, labor used and raw material used. 4. [5 points] Use Solver to determine the production schedule that maximizes the total profit under the resources constraints. Create a Scenario named "Optimal" with these values for the input cells 5. [5 points] Now assume that at least 50 pounds of each drug must be made. Add this constraint and use Solver to determine the production schedule that maximizes the total profit in this case. Create another scenario ---Optimal50" with the values found by solver for the production quantities as the values of the inputs/changing cells. 6. [8 points] Generate a scenario summary report showing for each of the two scenarios the resulting profit and the amount of each resource (labor and raw material) used. Move the scenario summary report to the right of Problem 4 worksheet. Make sure that there are no cell addresses in your report. Drug Company Optimization Product Product 1 Product 2 Product 3 Product 4 Product 5 Labor Used per Pound 6 5 4 3 2.5 Raw Material Used per Pound 3.2 2.6 1.5 0.8 0.7 Unit Selling Price $12.50 $11.00 $9.00 $7.00 $6.00 Variable Unit Cost $6.50 $5.70 $3.60 $2.80 $2.20 Product 6 1.5 0.3 $3.00 $1.20 A drug company produces six different products at its plant. Relevant details are shown on the Problem 4 worksheet. On that worksheet, note that production of each product requires labor and raw material. The problem is to formulate and use an optimization model to determine a production mix (e.g., how many pounds to produce of each product) that maximizes profit when 4,500 hours of labor and 1,350 pounds of raw material are available. Display the profit to two decimal places. The Changing Cells are not to be integer-constrained, and negative production should be ruled out. 2. [4 points] Assign appropriate range names to the cell with the objective function the range of and any other cells/ranges that will be used in the solver dialog box. There should be no cells in the Solver without range names. 3. [8 points]Formulate the model by entering the formulas for profit, labor used and raw material used. 4. [5 points] Use Solver to determine the production schedule that maximizes the total profit under the resources constraints. Create a Scenario named "Optimal" with these values for the input cells 5. [5 points] Now assume that at least 50 pounds of each drug must be made. Add this constraint and use Solver to determine the production schedule that maximizes the total profit in this case. Create another scenario ---Optimal50" with the values found by solver for the production quantities as the values of the inputs/changing cells. 6. [8 points] Generate a scenario summary report showing for each of the two scenarios the resulting profit and the amount of each resource (labor and raw material) used. Move the scenario summary report to the right of Problem 4 worksheet. Make sure that there are no cell addresses in your report. Drug Company Optimization Product Product 1 Product 2 Product 3 Product 4 Product 5 Labor Used per Pound 6 5 4 3 2.5 Raw Material Used per Pound 3.2 2.6 1.5 0.8 0.7 Unit Selling Price $12.50 $11.00 $9.00 $7.00 $6.00 Variable Unit Cost $6.50 $5.70 $3.60 $2.80 $2.20 Product 6 1.5 0.3 $3.00 $1.20
