PLEASE SHOW ALL FORMULA FOR EXCEL STEP BY STEP

EXCEL FORMUL STEP by step

The Otter Creek Winery produces three kinds of table wine- a blush, a white, and a red. The winery has 30,000 pounds of grapes available to produce wine this season. A cask of blush requires 360 pounds of grapes, a cask of white requires 375 pounds, and a cask of red requires 410 pounds. The winery has enough storage space in its aging room to store 67 casks of wine. The winery has 2,200 hours of production capacity, and it requires 14 hours to produce a cask of blush, 10 hours to produce a cask of white, and 18 hours to produce a cask of red. From records of previous years' sales, the winery knows it will sell at least twice as much blush as red and at least 1.5 times as much white as blush. The profit for a cask of blush is $12,100, the profit for a cask of white is $8,700, and the profit for a cask of red is $10,500. The winery wants to know the number of casks of each table wine to produce. 1. Formulate and solve an integer programming model for this problem(15pts). The winery should produce casks of blush, casks of white, and casks of red. Alexis Harrington received an inheritance of $90,000, and she is considering two speculative investments-the purchase of land and the purchase of cattle. Each investment would be for 1 year. Under the present (normal) economic conditions, each dollar invested in land will return the principal plus 20% of the principal; each dollar invested in cattle will return the principal plus 25%. However, both investments are relatively risky. If economic conditions were to deteriorate, there is an 18% chance she would lose everything she invested in land and a 30% chance she would lose everything she invested in cattle. Alexis does not want to lose more than $20,000 (on average). She wants to know how much to invest in each alternative to maximize the cash value of the investments at the end of 1 year. 1. Formulate a linear programming model for this problem(5pts). 2. How much would the return for cattle have to increase for Alexis to invest only in cattle? (5pts) 3. Should all of Alexis's inheritance be invested according to the optimal solution? (5pts) 4. If the risk of losing the investment in land increased to 30%, how would this change the optimal investment mix? (10pts) The return for cattle has to be % for Alexis to invest only in cattle. Round your solutions to the nearest integer. Should Alexis invest all inheritance? Choose "Yes" or "No