2. Remember the Sonoma Apple problem in the previous homework. This problem is based on that but realize it is a bit different. The problem was solved in Excel and the sensitivity report is shown below. Answer the following questions based on the sensitivity report. (If a question can't be answered using the sensitivity report say so. You don't need to change the model and solve it.) Variable Cells Cell Name $B$12 Decision Variables x1 SC$12 Decision Variables x2 $D$12 Decision Variables x3 SE$12 Decision Variables x4 Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 6000 0 1 0.592592593 1E+30 13555.55556 0 1.2 1E+30 0.470588235 0 -1.777772778 1 1.777777778 1E+30 1711.111111 0 - 1 1E430 2,352941176 Constraints Cell Name $F$16 Constraint 1 (Applesauce Demand) Ihs (Aix) SF$17 Constraint 2 (Apple Juice) Ihs (Aix) SFS18 Constraints 3 (30% min) Ihs (A x) SF$19 Constraint 4 (80% max) ths (A lx) SF$20 Constraint S (Budget) ths (AIX) Final Shadow Value Price 6000 0.111111111 5000 0.422222222 133.3333333 0 9644444444 0 16000 1 111111111 Constraint Allowable Allowable R.H. Side Increase Decrease 6000 9625 137.9310345 5000 2000 54250 0 133.3333333 1E#30 0 15-309644.444444 16000 400 12700 (a) How many jars of apple sauce and apple juice should Sonoma Apple produce? (b) How much money should Sonoma Apple allocate for each product type? (c) What is the profit of the optimal solution? (d) Which constraints are non-binding? (e) What would happen if the max demand for applesauce is increased to 8000? (9 What would happen if the max demand for apple juice is increased to 8000? (g) What would be the value of having $1000 more budget? (h) How much would Sonoma Apple lose if there is $1000 less budget? Tamir inn maline un calca nroducts: Western Foods salsa and Mexico City salsa. Essentially, ti 3. The Sonoma Apple Products Company purchases apples from local growers and makes applesauce and apple juice. It costs $0.80 to produce a jar of applesauce and $0.60 to produce a bottle of apple juice. The company has a policy that at least 30 percent but no more than 70 percent of its output must be applesauce. The company wants to meet but not exceed the demand for each product. The marketing manager estimates that the demand for applesauce is a maximum of 5,000 jars, plus an additional 3 jars for each $1 spent on advertising for applesauce. The maximum demand for apple juice is estimated to be 4,000 bottles, plus an additional 5 bottles for every $1 spent on advertising for apple juice. The company has $16,000 to spend on producing and advertising its two products. Applesauce sells for $1.75 per jar, and apple juice sells for $1.85 per bottle. Formulate a linear optimization model to help the company determine how many units of each product to produce, and how much advertising to spend on each product, in order to maximize profit. a. Formulate this problem. Make sure to use the 3 steps and clearly explain all variables, objective function and the constraints. b. Formulate this problem in Excel Solver. Make sure to include the Answer Report in your Excel Solution C. Make sure to report your optimal solution and the optimal objective value. (Screen shot is not accepted.) d. Identify the binding constraints (Screen shot is not accepted.) e. Identify slack associated with the constraints (Screen shot is not accepted.) 2. Remember the Sonoma Apple problem in the previous homework. This problem is based on that but realize it is a bit different. The problem was solved in Excel and the sensitivity report is shown below. Answer the following questions based on the sensitivity report. (If a question can't be answered using the sensitivity report say so. You don't need to change the model and solve it.) Variable Cells Cell Name SB$12 Decision Variables x1 $C$12 Decision Variables x2 $D$12 Decision Variables x3 $E$12 Decision Variables x4 Final Reduced Objective Allowable Allowable Value Cost Coefficient Increase Decrease 6000 0 1 0.592592593 1E430 13555.55556 0 1.2 1E430 0.470588235 0 -1.277777778 1 1.277777778 1E+30 1711.111111 0 -1 1E+30 2.352941176 Constraints Cell Name SFS16 Constraint 1 (Applesauce Demand) Ihs (A ix) SFS17 Constraint 2 (Apple Juice) lhs (A lx) SFS18 Constraints 3 (30% min) lhs (A_ix) SFS19 Constraint 4 (80% max) ths (A_b) SF$20 Constraint 5 (Budget) Ihs (A_ix) Final Shadow Value Price 6000 0.111111111 5000 0.422222222 133.3333333 0 9644,444444 0 16000 1.111111111 Constraint Allowable Allowable R.H. Side Increase Decrease 6000 9625 137.9310345 5000 2000 54250 0133.3333333 1E430 0 1E+30 9644,444444 16000 400 7700 (a) How many jars of apple sauce and apple juice should Sonoma Apple produce? (b) How much money should Sonoma Apple allocate for each product type? (c) What is the profit of the optimal solution? (d) Which constraints are non-binding? (e) What would happen if the max demand for applesauce is increased to 8000? (f) What would happen if the max demand for apple juice is increased to 8000? (g) What would be the value of having $1000 more budget? (h) How much would Sonoma Apple lose if there is $1000 less budget