M7.3 Cullowhee Valley Vineyards produces three kinds of wine - white, rose, and red. The company has 25 tons of grapes available to produce wine this season. A cask of white requires 0.28 tons of grapes, a cask of rose requires 0.31 tons, and a cask of red requires 0.25 tons. The vineyard has enough storage space in its aging room to store 90 casks of wine. The vineyard has 2,000 hours of production capacity, an it requires 16 hours to produce a cask of white, 18.5 hours to produce a cask of rose, and 20 hours to produce a cask of red. From past sales the vineyard knows that the demand for red will be no more than half the sales of the other two wines combined. The profit for a cask of white is $7,500, the profit for a cask of rose is $8,100, the profit for a cask of red is $8,700. How many casks of each type of wine should be produced to maximize profit? Formulate and solve in Excel. SOLUTION: The optimal solution, the value of the profit, should be $725,000 Questions: (Be sure to answer questions using full sentences and quantitative values) a) Based on the optimal solution, how many casks of each type of wine should be produced? b) Based on the original formulation, if the vineyard determined that the profit from rose was $7,300 instead of $8,100, how would this affect the optimal solution? c) Based on the original formulation, if the vineyard could obtain 0.5 tons more of grape, 500 more hours of production capacity, or enough storage capacity to store 4 more casks of wine, which should it use? d) Based on the original formulation, all three wines are produced in the optimal solution. How little would the profit for white (to nearest $100 - increasing this value by $100 would result in white wine being produced) must be for it no longer be produced? Use the LP Template.x/sx provided to you in the Module 7 Resource page. Solve the problem using Excel Solver, place the formulation and answers to the questions in the appropriate text boxes. I will delete your decision variables in your Excel submission and re-solve your problem. There is no credit for the problem if your Excel submission doesn't solve. If necessary, remove problem constraints so that your Excel file solves. It is better to get partial credit than no credit. Your Excel Solver solution must result in the follow dialog box for full credit for the solution. Note the highlighted text. Solver found a solution. All Constraints and optimality conditions are satisfied. When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex LP is used, this means Solver has found a global optimal solution