Question broken down.

1. What cell(s) reference designates the Set Objective in "Solver"?

2. What formula should be entered in cell F8 to compute total profitability?

3. What formula should be entered in cell F12?

4. Which cells are the Changing Cells as designated by "Solver"?

5. What cell(s) designates the "Cell Reference" in Solver "Add Constraint" for Assembly.

6. What Solving Method should be used in Solver?

7. Which constraints are binding?

8. What is the optimal objective function value?

9. By how much would the profit contribution of product variant B has to increase before it will be profitable to produce B?

10. Suppose that the production manager procures an additional 25000 minutes of polish time. What impact will this have on the current optimal objective function value?

11. Suppose that the production manager has an additional 10000 minutes of assembly time. What impact will this have on the current optimal objective function value?

12. Suppose that the objective function coefficient for product variant A increases by $2. What impact will this have on the current values of the optimal solution?

Here is a LP model of a manufacturing process concerned with the production of four variants of the same product, A,B,C, and D,, each of which will yield a certain profit per unit sold, and each requires a certain time for assembly, packing and polishing. Given the current state of the labor force the company estimates time limits available for each of the availability operation. The objective is to determine how many of each variant to make to achieve the greatest possible profit while satisfying all constraints. Maximize 1.5A+2.50 +30 +4.5D Subject to Assembly 2A+4B+3C +70 $100,000 minutes Polish 3A+2B+3C +4D 50.000 minutes Pack 2A+3B + 2C+5D $60,000 minutes A,B,C,D 0 Figure 1 - Excel spreadsheet used to solve the LP model. Cells B5:E5 are the designated cells for the optimal values of A, B, C, and D product variants while cell F8 is the designated cell for the objective function value. Cells F11:F13 designate the left-hand-side of the constraints. NOTE: To answer the questions on Sensitivity Analysis you first need to solve the problem in Excel and obtain the Sensitivity Report. F G H Sohver Parameters Set Objective: To: Max Value of Changing Variable Calls Total Subject to the Constraints add B E 1 Product-Mix Problem (4 Products and 3 Resources) 2 3 4 Decision Variables B D 5 Units to Make 6 7 Objective 8 Profit 1.5 2.5 3 4.5 9 10 Constraints 11 Assembly 2 4 3 7 12 Polish 3 2 3 4 13 2 3 2 5 14 15 Add Constraint 16 17 18 Cell Reference Constraint 19 20 Change Used Relation Available 100000 50000 60000 NA Delete Pack Beset All Load/Save Mage Unconstrained variables Non-Negative Select a Solving Method: GRG Nonlinear Ogtions GRG Nonline Solving Method Simplex Evolutionary Select the GRG Nonlinear engine for Solver problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver problems, and select the Evolutionary engine for Solver problems that are non-smooth