Full file at

SonomaValley Wines[1]

William D. Whisler

CaliforniaStateUniversity, Hayward

1. (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.

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.

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.

3. If the price of Sauvignon Blanc decreases by half in the second year, the coefficient of SSB,2 changes to 3.5 in the objective function The solution summary table shows that the values of the variables do not change although the profit decreases to $598,959.

4. One can see without making a new Excel spreadsheet that including a holding cost of 10¢ per bottle does not change the solution given in Question 2 because there is no inventory left over at the end of either year; sales equals production for both products each year. However, solving a new spreadsheet with two additional variables for the inventory of Petit Sirah and Sauvignon Blanc carried forward from the first year to the second year, shown below, confirms this conclusion.

5. (a) If grape costs increase by 50% the objective function and constraints 1 and 8 will change as shown below (cells D5, F5, J5, L5, D6, F6, D13, F13, J13, L13). Production, sales, and advertising all decrease in the solution. In addition, the profit decreases to $339,877.

(b) If grape costs increase by 100% the objective function and constraints 1 and 8 will change as shown below (cells D5, F5, J5, L5, D6, F6, D13, F13, J13, L13). The solution yields production, sales, and advertising that are less than in part (a). In addition, the profit decreases to $198,841.

6. (a) If an 8% discount factor is used, all of the coefficients in the objective function change as shown below (cells E5, G5, J5:O5). The cash flows SPS,1, SSB,1, PPS,2, PSB,2, APS,2, and ASB,2 occur at the end of the first year or the beginning of the second year and use a discount factor of 1/1.08 = 0.9259. The cash flows SPS,2 and SSB,2 occur at the end of the second year and use a discount factor of (1/1.08)2 = 0.8573. The solution summary table shows that the values of the variables do not change. The value of the objective function decreases to $592,407 although a comparison with the original value in Questions 2 is not possible because in this question the objective function is the present value of the profit.

(b) If a 6% discount factor is used all of the coefficients of the objective function change as shown below (cells E5, G5, J5:O5). The cash flows SPS,1, SSB,1, PPS,2, PSB,2, APS,2, and ASB,2 occur at the end of the first year or the beginning of the second year and use a discount factor of 1/1.06 = 0.9434. The cash flows SPS,2 and SSB,2 occur at the end of the second year and use a discount factor of (1/1.06)2 = 0.8900. The solution summary table shows that the values of the variables do not change. The value of the objective function decreases to $615,357 although a comparison with the original value in Questions 2 is not possible because in this question the objective function is the present value of the profit.

(c) If a 10% discount factor is used, all of the coefficients in the objective function change as shown below (cells E5, G5, J5:O5). The cash flows SPS,1, SSB,1, PPS,2, PSB,2, APS,2, and ASB,2 occur at the end of the first year or the beginning of the second year and use a discount factor of 1/1.10 = 0.9091. The cash flows SPS,2 and SSB,2 occur at the end of the second year and use a discount factor of (1/1.10)2 = 0.8264. The solution summary table shows that the values of the variables do not change. The value of the objective function decreases to $570,700 although a comparison with the original value in Questions 2 is not possible because in this question the objective function is the present value of the profit.

7. If George accepts the loan he would have another $9,000 available in the first year and $7,200 in the second year (after subtracting the $2,800 interest payment). This changes the right-hand side of constraint 1 to 19,000 and the right-hand side of constraint 8 to 17,200. The solution indicates that production, sales, and profits will increase, with the latter going up to $1,306,427. This is much larger increase than the interest George would pay so he should accept the offer.

8. If the advertising rule of thumb is incorrect then constraints 3, 5, 10, and 12 change as shown below (cells H8, I10, N15, O17). The solution indicates that production, sales, and profits decrease, with the latter decreasing to $588,060.

9. If George’s personal concerns that Petit Sirah sales should be between 40% and 70% of sales are ignored, constraints 6, 7, 13, and 14 are deleted. The solution indicates that only Sauvignon Blanc is produced and sold in the first year and only Petit Sirah is produced and sold in the second year. Profits will increase to $753,636.

This edition is intended for use outside of the U.S. only, with content that may be different from the U.S. Edition. This may not be resold, copied, or distributed without the prior consent of the publisher.

[1] The format of the equations is adapted from a problem in Operations Research for Business by Roger D. Eck, Wadsworth Publishing Company, 1976, p. 129.