BUSN – A family friend has a small winery in California

A family friend has a small winery in California. He has asked you to spend an all expense paid week (including free wine) at his beautiful estate. In exchange he’d like you to assist him by applying your Management Science expertise.The winery produces 3 wines: Blanc, Red and Blush. The winery has produced 17 tons of grapes this season. A cask of blanc requires 0.21 tons of grapes, a cask of red requires 0.24 tons and a cask of blush requires 0.18 tons. The winery has enough space for 80 casks.The winery has 2500 hours of production time available. A cask of blanc requires 12 hours of production, red requires 14.5, while blush needs 16. Traditionally, the demand for blush has been no more than half the sales of the other 2 combined.A cask of blanc will realize a profit of $7500, a cask of red $8200 and a cask of blush $10,500.Your job is to work individually to write a report for the winery’s owner. The owner is not a Management Science weenie, so you should write the report using everyday language and graphs if appropriate. The report should be 4 pages or less, not counting any backup material (Excel work) that you provide. You should talk about the effects, limits, etc. for each of his questions. Your job is to provide good advice, not just to answer questions.The winery’s owner wants to know:How much and what types of wine should be produced in order to maximize the profit?If America’s taste for red wine lessens and profit drops, how does that affect him?Cash flow is tight, the owner has enough to do one of the following which should he do?Buy another 0.5 tons of grapesAdd 500 hours of productionAdd capacity to store 4 more casksHe fears that blush wine profits will drop significantly and he may choose not to make any. At what profit should he discontinue producing blush?If he chooses to add storage capacity, how much will each additional cask earn him?Please submit your responses here in an Excel file and a Word file.