please help
spreadsheet:
Variable Cells Constraints Decision Variables Total Profit: 61375 ConstraintsC1C2C3C4C5C6C7C860007250725051257500175001062550002600075007500600075001000030005000 Part c) Use the Sensitivity Report that you generated and refer to your original answer. Assume that we will decrease the right-hand-side value of C6 (Max. available fruit juice) to 6,980. Label your answers, "Part c." Amount of the Decrease equals what value? 0.5 points Allowable Decrease equals what value? 0.5 points Is this change within the Allowable Decrease? Yes or No? 0.5 points What will the new objective function value equal? Note: You must calculate this value in your Excel spreadsheet using the simple formula that we used in all Sensitivity Analysis spreadsheet posted on Canvas. 1.5 points Part 2: Excel Solver (10 Points) Given: You are provided the model formulation below. Decision Variables: W= gallons of white wine to purchase and use in the production of a wine cooler blend R= gallons of rose wine to purchase and use in the production of a wine cooler blend F= gallons of fruit juice to purchase and use in the production of a wine cooler blend Objective function: MAX: 1.45W+1.30R+2.25F Subject to: Constraints: C1 (Use at least 35% white wine): C2 (Use at least 19% rose wine): C3 (Use no more than 34% fruit juice): C4 (Max. available white): C5 (Max. available rose): C6 (Max. available fruit juice): C7 (Non-negativity): 0.65W0.35R0.35F00.19W+0.81R0.19F00.34W0.34R+0.66F01W10,0011R13,9991F7,025W,R,F0 Instructions: 1. Use an the spreadsheet attached and revise it to create a Solver model and to generate a sensitivity report. 2. Answer the questions below in your Excel spreadsheet. Part a) How many of each ingredient should be purchased, and what is the value of the objective function (Total Profit)? List "Part a" by your answers. 4 points Note: You must answer all Sensitivity Analysis questions below in the upper right hand part of your spreadsheet. Part b) Use the Sensitivity Report that you generated and refer to your original answer. Assume that we will decrease the Net Profit per gallon of F to \$2.19. Label your answers, "Part b." Amount of the Decrease equals what value? 0.5 points Allowable Decrease equals what value? 0.5 points Is this change within the Allowable Decrease? Yes or No? 0.5 points What will the new objective function value equal? Note: You must calculate this value in your Excel spreadsheet using the SUMPRODUCT formula that we used in all Sensitivity Analysis spreadsheets posted on Canvas. If all you do is to revise your model and re-run it, you will receive 0 points. 1.5 points Variable Cells Constraints Decision Variables Total Profit: 61375 ConstraintsC1C2C3C4C5C6C7C860007250725051257500175001062550002600075007500600075001000030005000 Part c) Use the Sensitivity Report that you generated and refer to your original answer. Assume that we will decrease the right-hand-side value of C6 (Max. available fruit juice) to 6,980. Label your answers, "Part c." Amount of the Decrease equals what value? 0.5 points Allowable Decrease equals what value? 0.5 points Is this change within the Allowable Decrease? Yes or No? 0.5 points What will the new objective function value equal? Note: You must calculate this value in your Excel spreadsheet using the simple formula that we used in all Sensitivity Analysis spreadsheet posted on Canvas. 1.5 points Part 2: Excel Solver (10 Points) Given: You are provided the model formulation below. Decision Variables: W= gallons of white wine to purchase and use in the production of a wine cooler blend R= gallons of rose wine to purchase and use in the production of a wine cooler blend F= gallons of fruit juice to purchase and use in the production of a wine cooler blend Objective function: MAX: 1.45W+1.30R+2.25F Subject to: Constraints: C1 (Use at least 35% white wine): C2 (Use at least 19% rose wine): C3 (Use no more than 34% fruit juice): C4 (Max. available white): C5 (Max. available rose): C6 (Max. available fruit juice): C7 (Non-negativity): 0.65W0.35R0.35F00.19W+0.81R0.19F00.34W0.34R+0.66F01W10,0011R13,9991F7,025W,R,F0 Instructions: 1. Use an the spreadsheet attached and revise it to create a Solver model and to generate a sensitivity report. 2. Answer the questions below in your Excel spreadsheet. Part a) How many of each ingredient should be purchased, and what is the value of the objective function (Total Profit)? List "Part a" by your answers. 4 points Note: You must answer all Sensitivity Analysis questions below in the upper right hand part of your spreadsheet. Part b) Use the Sensitivity Report that you generated and refer to your original answer. Assume that we will decrease the Net Profit per gallon of F to \$2.19. Label your answers, "Part b." Amount of the Decrease equals what value? 0.5 points Allowable Decrease equals what value? 0.5 points Is this change within the Allowable Decrease? Yes or No? 0.5 points What will the new objective function value equal? Note: You must calculate this value in your Excel spreadsheet using the SUMPRODUCT formula that we used in all Sensitivity Analysis spreadsheets posted on Canvas. If all you do is to revise your model and re-run it, you will receive 0 points. 1.5 points
