Sonoma Valley Wines Case
Sonoma Valley Wines
William D. Whisler
California State University, Hayward1. (a) There are six variables for each of the two years, giving a total of 12 variables. All of these variables must be nonnegative
PPS,t = Production of Petit Sirah in year t, t = 1, 2, bottles
PSB,t = Production of Sauvignon Blanc produced in year t, t = 1, 2, bottles
SPS,t = Sales of Petit Sirah in year t, t = 1, 2, bottles
SSB,t = Sales of Sauvignon Blanc in year t, t = 1, 2, bottles
APS,t = Advertising for Petit Sirah in year t, t = 1, 2, dollars
ASB,t = Advertising for Sauvignon Blanc in year t, t = 1, 2, dollars
The objective is to maximize profits.
There are four groups of conditions, three with four constraints each and one with two constraints: (i) sales must be less than demand, (ii) sales must be less than production, (iii) sales must be greater than the minimum values given in the case, and (iv) cash balance conditions each year. Thus, the total number of constraints is 14.
(i) Sales must be less that demand each year.
(ii) Sales must be less that production each year for each product.
(iii) Minimum and maximum sales levels exist each year.
which gives
for t = 1, 2.
(iv) There are cash flow constraints for each year. For the first year
and for the second year
(b) The spreadsheet summarizing the formulation is given below.
138430110490
2. The solution summary table that follows gives the solutions, obtained by Excel’s Solver, to all parts of the case. All answers have been rounded off to the nearest whole number.
165735-911860
Below is the Excel spreadsheet used to find the solution for Question 2. The formula =SUMPRODUCT(D5:O5,$D$22:$O$22) is entered in cell T5 and copied down to cells T6:T19. The Solver dialog boxes are shown immediately after the spreadsheet. Cell T5 contains the value of the objective function for the solution, $692,645 and the values of the variables are in cells D22:O22.
13843011430
24193533655
William D. Whisler
California State University, Hayward1. (a) There are six variables for each of the two years, giving a total of 12 variables. All of these variables must be nonnegative
PPS,t = Production of Petit Sirah in year t, t = 1, 2, bottles
PSB,t = Production of Sauvignon Blanc produced in year t, t = 1, 2, bottles
SPS,t = Sales of Petit Sirah in year t, t = 1, 2, bottles
SSB,t = Sales of Sauvignon Blanc in year t, t = 1, 2, bottles
APS,t = Advertising for Petit Sirah in year t, t = 1, 2, dollars
ASB,t = Advertising for Sauvignon Blanc in year t, t = 1, 2, dollars
The objective is to maximize profits.
There are four groups of conditions, three with four constraints each and one with two constraints: (i) sales must be less than demand, (ii) sales must be less than production, (iii) sales must be greater than the minimum values given in the case, and (iv) cash balance conditions each year. Thus, the total number of constraints is 14.
(i) Sales must be less that demand each year.
(ii) Sales must be less that production each year for each product.
(iii) Minimum and maximum sales levels exist each year.
which gives
for t = 1, 2.
(iv) There are cash flow constraints for each year. For the first year
and for the second year
(b) The spreadsheet summarizing the formulation is given below.
138430110490
2. The solution summary table that follows gives the solutions, obtained by Excel’s Solver, to all parts of the case. All answers have been rounded off to the nearest whole number.
165735-911860
Below is the Excel spreadsheet used to find the solution for Question 2. The formula =SUMPRODUCT(D5:O5,$D$22:$O$22) is entered in cell T5 and copied down to cells T6:T19. The Solver dialog boxes are shown immediately after the spreadsheet. Cell T5 contains the value of the objective function for the solution, $692,645 and the values of the variables are in cells D22:O22.
13843011430
24193533655