The Mason Wine Company produces two kinds of wine - Mason Blanc and Mason Merlot. The wines are produced in 1,000-gallon batches. The profit for a batch of Blanc is $12,000 and the profit for a batch of Merlot is $9,000.

I need HELP answer this problem as an Excelfile. Thank you

The wines are produced from 64 tons of grapes that the company has acquired. A 1,000-gallon batch of Blanc requires 4 tons of grapes and a batch of Merlot requires 8 tons.

However, the production is limited by the availability of only 50 cubic yards (yd³) of storage space for aging and 125 hours of processing time. Each batch of each type of wine requires 5 yd³ of storage space. The processing time for a batch of Blanc is 15 hours and the processing time for a batch of Merlot is 7 hours.

The wine company will not produce more or less than the range of amounts demanded for each type. Demand for each type of wine is for at least 2 batches but is limited to not more than 7 batches. Even so, the demand for Blanc is the same as or is higher than the demand for Merlot.

Company executives do not want to depend on just one type of wine so they have mandated minimum production levels of both types of wine. Specifically, at least 15% of the total wine production must be Merlot. Likewise, at least 15% of the total wine production must be Blanc. Moreover, the amount of the Merlot produced should not be more than half of the total production.

Also, the break-even point on profit is $54,000. Therefore, company requires that it must make at least $54,000 in profit to do better than just break even (that is, profit must be $54,000 or more).

The company wants to set the production levels, in terms of the number of 1,000-gallon batches of both the Blanc and Merlot wines to produce so as to earn the most profit possible.

1. Formulate this problem mathematically. In lines entered below this part, state your complete mathematical representation of this problem. Your complete model must (i) define all the decision variables, (ii) state the objective function in terms of your decision variables, and (iii) state all of the constraints in terms of your decision variables. 2. In lines entered below this part, state the specific type of problem this is. Include an explanation of why. 3. a. Graph this problem consisting of the following steps: Graph all of the constraints. Identify and label the feasible region. b. Identify the optimal solution point using the contour sweep method consisting of the following steps: On top of the graph of the feasible region, overlay contours of the objective function to determine and identify the optimizing direction. Identify and label the point containing the optimal solution. c. Explain why the optimal solution is located at the point identified above. In a space created below this part, present a copy of your labeled graph. 4. a. Based on part 3, in lines entered below this part, state where the optimal solution point is located on your graph. b. Based on part 3, calculate and explicitly state in lines entered below this part the values of the optimal solution in terms of the optimal values of the decision variables and the optimal value of the objective function. 5. In lines entered below this part, explicitly state in plain language the optimal numbers of batches of Merlot and Blanc wines to make and the maximum amount of the profit. 6. Determine the optimal solution with the corner point evaluation method. a. In lines entered below this part, calculate and explicitly state the values of the decision variables and of the objective function at all the corner points of the feasible region identified in graph in part 3. b. In lines entered below this part, calculate and explicitly state the optimal solution and optimal value calculated by the method based on your results in part 6a. You must show those calculations. c. In lines entered below this part, compare the results in parts 4 and 6b. Are they the same or not? Either way, why? 7. a. Calculate and explicitly state in lines entered below the amounts of slack/surplus for each and every constraint of your model. b. In lines entered below this part, state in plain language the specific meaning of the numerical value slack/surplus for each and every constraint of your model in the context of the business situation. Be specific and complete. 8. In lines entered below, identify all the active, all the inactive, and all the redundant (if any) constraints. In other words, classify each and every constraint in your model as to whether it is active, inactive, or redundant. 9. Solve this problem with a computer using either Excel Solver or LINGO. In a space created below this part, present copies of both the computer input and output screens. Submit all of your computer files with your completed exam. 10. In lines entered below, explicitly state the optimal solution (in terms of the optimal values of the decision variables and of the objective function) that was calculated and stated on the computer output presented in part 9. 11. In lines entered below, compare your results in part 10 value with your previous results in parts 4 and 6b. In particular, state whether the results are the same or not. Include an explanation of why or why not. 12. In lines entered below, calculate and explicitly state the ranges of optimality on the profit per batch for each type of wine (that is, the ranges of optimality for each of the coefficients of the objective function stated as ranges [not as allowable increases/decreases]). 13. In lines entered below, calculate and explicitly state the ranges of feasibility of all the constraints (that is, the ranges of feasibility of the right-hand sides of all the constraints stated as ranges [not as allowable increases/decreases]). 14. In lines entered below, state in plain language the specific meaning of the numerical values of each of the non-zero dual prices in the context of this business situation. In other words, state what each non-zero numerical value of a dual price means in the context of this business situation stated on page 2. Be explicit and specific. 15. Suppose that it is now determined that the break-even point is $10,000 more than originally stated (that is, profit must be $64,000 or more). a. In lines entered below, explain how the feasible region changes, if at all. b. In lines entered below, explain how the optimal solution and the optimal value are changed, if at all;