Question

A winery has the following capacity to produce an exclusive dinner wine at either of its two vineyards at the indicated costs:

Four Italian restaurants around the country are interested in purchasing this wine. Because the wine is exclusive, they all want to buy as much as they need but will take whatever they can get. The maximum amounts required by the restaurants and the prices they are willing to pay are summarized in the following table.

The costs of shipping a bottle from the vineyards to the restaurants are summarized in the following table.

The winery needs to determine the production and shipping plan that allows it to maximize its profits on this wine.

a. Select the correct LP model for this problem.

Let X_ij represent Number of bottles produced at vineyard i sold to restaurant j.

I MAX 39X11 + 36X12 + 34X13 + 34X14 + 32X21 + 36X22 + 37X23 + 34X24 ST X11 + X12 + X13 + X14 ≤ 3,100 X21 + X22 + X23 + X24 ≤ 3,500 X11 + X21 ≤ 1,800 X12 + X22 ≤ 1,250 X13 + X23 ≤ 2,300 X14 + X24 ≤ 1,750 Xij ≥ 0	II MIN 39X11 + 36X12 + 34X13 + 34X14 + 32X21 + 36X22 + 37X23 + 34X24 ST X11 + X12 + X13 + X14 ≥ 3,500 X21 + X22 + X23 + X24 ≥ 3,100 X11 + X21 ≥ 1,750 X12 + X22 ≥ 2,300 X13 + X23 ≥ 1,250 X14 + X24 ≥ 1,800 Xij > 0
III MAX 39X11 + 36X12 + 34X13 + 34X14 + 32X21 + 36X22 + 37X23 + 34X24 ST X11 + X12 + X13 + X14 ≤ 3,500 X21 + X22 + X23 + X24 ≤ 3,100 X11 + X21 ≤ 1,800 X12 + X22 ≤ 2,300 X13 + X23 ≤ 1,250 X14 + X24 ≤ 1,750 Xij ≥ 0	IV MIN 39X11 + 36X12 + 34X13 + 34X14 + 32X21 + 36X22 + 37X23 + 34X24 ST X11 + X12 + X13 + X14 ≤ 3,100 X21 + X22 + X23 + X24 ≤ 3,500 X11 + X21 ≤ 2,300 X12 + X22 ≥ 1,800 X13 + X23 ≤ 1,750 X14 + X24 ≥ 1,250 Xij > 0

Select  I,II,III,IV

b. Create a spreadsheet model for this problem and solve it using Solver. What is the wine production and shipping plan that maximizes profits to the company? If your answer is zero, enter "0".

	Bottles to Sell
	Restaurant 1		Restaurant 2		Restaurant 3		Restaurant 4
Vineyard 1
Vineyard 2

c. What is the total profit for the optimal solution?
$

Answer 1