Question

Cullowhee Valley Vineyards produces three kinds of wine - white, rose, and rẻd. 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.

Answer 1

To maximize profit, set up constraints and an objective function to solve for the optimal number of casks of each type of wine using Excel's Solver tool.

Step-by-step explanation:

To maximize profit, we need to determine the number of casks of each type of wine that should be produced. Let's assume we produce x casks of white, y casks of rose, and z casks of red. We can set up the following equations to represent the constraints:

1. 0.28x + 0.31y + 0.25z ≤ 25 (grapes available)
2. 16x + 18.5y + 20z ≤ 2000 (production capacity)
3. z ≤ 0.5(x + y) (demand for red)

The objective function is to maximize the profit, which can be represented as:

Profit = 7500x + 8100y + 8700z

We can solve this optimization problem using Excel's Solver tool by setting up the equations and adding the objective function. By solving these equations, Excel will provide the optimal values for x, y, and z that maximize the profit.