Sonoma Valley Wines Case Solution
MGMT570 (Spreadsheet Modeling and Simulation): Case 1
Sonoma Valley Wines Case
Q1. Following table shows the optimal decision (within given constraints in the case) George need to make to maximize his profit:
*Please refer to the excel file, “(Q1)” sheet, for more detail calculation.
Amount
Decision Variable
Petite Sirah
Sauvignon Blanc
Total
Wine Bottles to sell (year 1)
4,469
6,704
11,173
Wine Bottles to sell (year 2)
62,457
26,767
89,225
Spend on Grapes (year 1)
$ 3,575.42
$ 4,692.74
$ 8,268.16
Spend on Grapes (year 2)
$ 46,842.97
$ 22,752.30
$ 69,595.27
Spend on Advertisement (year 1)
$ 893.85
$ 837.99
$ 1,731.84
Spend on Advertisement (year 2)
$ 10,409.55
$ 2,676.74
$ 13,086.29
General strategy during year 1 would be to focus on selling Sauvignon Blanc as it requires lower investment due to low grape cost and advertisement cost; but maintain the minimum sales requirement of Petite Sirah of 40% or 4,469 bottles. However on year 2, the Sauvignon Blanc’s grape cost increases, which ultimately exceeds that of Petit Sirah. Thus, with limited budget, George should focus on selling Petite Sirah and maintain the maximum sales of 70% or 62,457 bottles.
Thus, the total profit at the end of year 2 would be: $ 692,644.59
*Please refer to the excel file, “(Q1)” sheet, for detail calculation.
Q2. The strategy will be the same as above, Q1; focus on Sauvignon Blanc at first, and focus on Petite Sirah at second year. However, as bank expected, profit will decrease to $598,958.64 from previous profit of $692,644.59 without such price decrease of Sauvignon Blanc. Although the profit has decreased about 13.5%, it should not restrict the bank in lending as it has large positive profit potential. Generally, bank should be willing to lend money when the price is greater than the cost; for Petite Sirah, total cost is $1, and $0.92 on year 1 and year 2, and for Sauvignon Blanc, $0.825 and $0.95 on year 1 and year 2 respectively.
*Please
Sonoma Valley Wines Case
Q1. Following table shows the optimal decision (within given constraints in the case) George need to make to maximize his profit:
*Please refer to the excel file, “(Q1)” sheet, for more detail calculation.
Amount
Decision Variable
Petite Sirah
Sauvignon Blanc
Total
Wine Bottles to sell (year 1)
4,469
6,704
11,173
Wine Bottles to sell (year 2)
62,457
26,767
89,225
Spend on Grapes (year 1)
$ 3,575.42
$ 4,692.74
$ 8,268.16
Spend on Grapes (year 2)
$ 46,842.97
$ 22,752.30
$ 69,595.27
Spend on Advertisement (year 1)
$ 893.85
$ 837.99
$ 1,731.84
Spend on Advertisement (year 2)
$ 10,409.55
$ 2,676.74
$ 13,086.29
General strategy during year 1 would be to focus on selling Sauvignon Blanc as it requires lower investment due to low grape cost and advertisement cost; but maintain the minimum sales requirement of Petite Sirah of 40% or 4,469 bottles. However on year 2, the Sauvignon Blanc’s grape cost increases, which ultimately exceeds that of Petit Sirah. Thus, with limited budget, George should focus on selling Petite Sirah and maintain the maximum sales of 70% or 62,457 bottles.
Thus, the total profit at the end of year 2 would be: $ 692,644.59
*Please refer to the excel file, “(Q1)” sheet, for detail calculation.
Q2. The strategy will be the same as above, Q1; focus on Sauvignon Blanc at first, and focus on Petite Sirah at second year. However, as bank expected, profit will decrease to $598,958.64 from previous profit of $692,644.59 without such price decrease of Sauvignon Blanc. Although the profit has decreased about 13.5%, it should not restrict the bank in lending as it has large positive profit potential. Generally, bank should be willing to lend money when the price is greater than the cost; for Petite Sirah, total cost is $1, and $0.92 on year 1 and year 2, and for Sauvignon Blanc, $0.825 and $0.95 on year 1 and year 2 respectively.
*Please