Question

Stephane Zanella is the general manager of The French Wine Club. Zanella’s current task is to decide The Club’s order for the wines in the upcoming catalog. Ordering occurs several months before publishing the catalog and at a time when little information beyond the wine expert’s opinion is available.

Upon receiving an order, the grower decorates the bottles with a label unique to The Club and sends the order to The Club’s warehouse in Dijon. The unique label makes it impossible to compare prices with supermarkets and enables Le Club to achieve 50% gross margins. In addition to the cost of purchasing each bottle, The Club incurs a $1.25 shipping and handling cost per bottle on its outbound shipments to customers. For example, for a bottle with a $10 retail price, The Club pays about $5 in procurement costs and $1.25 to ship the bottle to the customer. Customers do not pay for shipping, but the grower pays for inbound shipping to the warehouse.

Zanella knows that if he buys too many bottles for a catalog season, then the excess bottles are stored in the warehouse and discounted in a future catalog. As a rule of thumb, Zanella assumes that overbought wine needs to be discounted by 35% off its retail price (e.g., a $10 bottle would be sold for $6.50) to liquidate the inventory. Each bottle sold at discount incurs the $1.25 cost of shipping to the customer. Furthermore, due to the time spent in the warehouse, each discounted bottle incurs a $1.1 storage cost and an opportunity cost of capital equal to 15% of the purchase price. For example, a bottle The Club purchases for $5 and sold through a discount catalog incurs a storage cost of $1.1 and an additional $0.75 for the opportunity cost of capital (0.15*$5). While it is costly to overbuy, it is also costly to be too conservative. Thus, Zanella worries about “too much and too little”. While Zanella’s decision would involve around 30 wines, he has decided at this time to think carefully about the sample of the eight red wines in the table below.

1. To maximize The Club’s expected profit, how much of each of the wines in the table should be ordered?

2. Calculate the expected profit for each wine using the Q computed above.

3. If Zanella orders the quantity that maximizes expected profit, for each wine what is the probability that it will have inventory leftover that must be discounted?

4. If Zanella decides that it must have a 75% in stock probability for each wine, how much of each wine would he order? Do you recommend that these quantities be ordered?

Wine	Retail Price (per bottle)	Demand Forecast Mean	Demand Forecast Standard Deviation
VDP	8.00	4,000	1,300
Bordeaux	5.00	3,000	1,080
Minervois	5.50	4,000	1,500
Cotes du Ventoux	6.00	1,200	480
Cotes de Bourg	7.20	1,500	510
Madiran	9.00	12,000	3,000
Givry	13.00	1,000	375
Pessac Leognan	20.00	1,400	500

Answer 1

Extract the data as shown below: Retail price (per bottle) Wine VDP Bordeaux Minervois Cotes du Ventoux Cotes de Bourg Madiran Givry Pessac Leognan | Demand forecast (Mean) 4,000 3,000 4,000 1,200 1,500 12,000 1,000 1,400 Demand Forecast (standard deviation) 1,300 1,080 1,500 480 510 3,000 375 500 7.2 20 Shipping and handling cost per bottle = $1.25 Determine the order quantity from the mean demand forecast and its corresponding standard deviation forecast as shown below: Formulate MS excel spreadsheet as shown below:

B E F Retail Demand Forecast Expected demand price (per Demand (standard Wine bottle) forecast Mean) deviation) Maximum Minimum Order quantity VDPS 4000 1300 =C3+D3 =C3-D3 =MIN(E3-F3) Bordeaux 5 3000 1080 =C4+D4 =C4-D4 =MIN(E4:F4) Minervois 5.5 4000 1500 =C5+D5 =C5-D5 =MIN(E5:F5) 6 Cotes du Ventoux 6 1200 480 =C6+D6 =C6-D6 =MIN(E6:F6) Cotes de Bourg 7.2 1500 510 =C7+D7 =C7-D7 =MIN(E7:F7) Madiran 9 1 2000 3000 =C8+D8 =C8-D8 =MIN(E8:F8) Givry 13 1 000 375 1 =C9+D9 =C9-D9 =MIN(E9:F9) 10 Pessac Leognan 20 1400 500 1=C10+D10 =C10-D10 =MINE 10:F10) Screenshot 1: Formulated spreadsheet to find order quantity The resulted view of formulated spreadsheet is shown below: G A B E F Demand Demand Forecast Expected demand Retail price forecast (standard Wine (per bottle) (Mean) deviation) Maximum Minimum VDP 8 4,000 1,300 5,300 2,700 4 Bordeaux 3.000 1,080 4,080 1,920 5 Minervois 5.5 4,000 1,500 5,500 2,500 6 Cotes du Ventoux 1,200 480 1.680 720 7 Cotes de Bourg 7.2 1.500 510 2,010 8 Madiran 9 12,000 3,000 15,000 9.000 9 Givry 131,000 3751,375 625 10 Pessac Leognan 20 1,400 500 1,900 900 Screenshot 2: Result of formulated spreadsheet Order quantity 2,700 1,920 2,500 720 990 9,000 625 900 990 Here, minimum order quantity is expected to attain maximum profit. This is because, in case of maximum order quantity there are high chances of selling wines at discounted price.

The advantage of ordering minimum order quantity is that there are zero percent chances of any leftover wines in inventory. Therefore, there is 0% probability of selling leftover wines at discounted prices. Calculate the profit for each type of wine as shown below: Formulate MS excel spreadsheet as shown below: J K MN Demand Retail price forecast Demand Forecast Order Wine (per bottle) (Mean) (standard deviation) quantity Profit VDP 4000 1300 2700 =13*M3 4 Bordeaux 5 3000 1080 1920 =J4*M4 5 Minervois 5.5 4000 1500 2500 =J5*M5 6 Cotes du Ventoux 6 1200 480 720 =J6*M6 7 Cotes de Bourg 7.2 1500 510 990 =J7*M7 Madiran 9 12000 3000 9000 =J8*M8 9 Givry 13 1 000 375 625 =39*M9 10 Pessac Leognan 20 1400 500 900 =J10*M10 Screenshot 3: formulation of spreadsheet to find profit for each type of wine

The resulted view of formulated spreadsheet is shown below: - M N Demand Demand Forecast Retail price forecast (standard Order Wine (per bottle) (Mean) deviation) quantity VDPS 8.00 4,000 1,300 2.700 Bordeaux S 5.00 3,000 1,080 1,920 Minervois S 5.50 4,000 1,500 2,500 6 Cotes du Ventoux $ 6.00 1,200 480 720 7 Cotes de Bourg $ 7.20 | 1,500 510 990 8 Madiran S 9.00 12.000 3,000 9,000 9 Givry S 13.00 1,000 375 625 10 Pessac Leognan $ 20.00 1,400 500 900 Screenshot 4: Result of formulated spreadsheet Profit $21.600.00 $ 9,600.00 $13,750.00 $ 4,320.00 $ 7,128.00 $81,000.00 $ 8,125.00 $18,000.00 Hence, from Screenshot 4, it can be seen that the highest profitable wine is Madiran with a profit of $81,000.00 and the least profitable wine is Cotes du Ventoux with a profit of S4320.00 Calculate the quantity of each type of wine to be ordered when there is 75% of stock available for each wine in inventory, as shown below: Formulate MS excel spreadsheet as shown below:

a R Demand Demand Forecast Retail price forecast (standard Order 75% in stock Order to be Wine (per bottle) (Mean) deviation) quantity quantity placed VDP 4000 1300 2700 =0.75*T3 =T3-U3 4 Bordeaux 15 3000 1080 1920 =0.75 T4 T4-U4 Minervois 5.5 4000 1500 2500 =0.75*15 1=T5-U5 6 Cotes du Ventoux 6 1200 480 720 =0.75*T6 1=16-06 7 Cotes de Bourg 7.2 1500 510 990 =0.75*17 =17-U7 8 Madiran 9 1 2000 3000 9000 1=0.75*T8 =T8-US 9 Givry 13 1 000 375 625 1=0.75*T9 =T9-U9 10 Pessac Leognan 20 1400 500 900 1=0.75*T10 =T10-U10 Screenshot 5: Formulated spreadsheet to find required order to be placed The resulted view of formulated spreadsheet is shown below: u v 5 6 7 8 Wine VDP Bordeaux Minervois Cotes du Ventoux Cotes de Bourg Madiran Givry Pessac Leognan R S т Demand Demand Forecast Retail price forecast (standard Order (per bottle) (Mean) deviation) quantity $ 8.00 4,000 1,300 2,700 S 5.00 3,000 1,080 1,920 $ 5.50 4,000 1,500 2,500 $ 6.00 1,200 480 720 S 7.20 1,500 510 990 S 9.00 12.000 3,000 9,000 S 13.00 1,000 375 625 S 20.00 1,400 500 900 Screenshot 6: Result of formulated spreadsheet 75% in stock quantity 2,025 1,440 1,875 540 743 6.750 469 675 Order to be placed 675 480 625 180 248 2,250 156 225 10

Therefore, based on the results of Screenshot 6, it is not recommended to order the quantities calculated in part 1. As, this may result in selling the wine bottles at discounted prices in future.