Introduction to Operations Management (IDS 532)

Le Club Francais Case

Hint: Setting up the calculations in excel is highly recommended so that you can focus on
interpretation and recommendation, rather than calculations.

1. Discuss why the news vendor model may or may not be a reasonable model for
computing order quantities in this case?

I think this model can predict demand to some extent, but there are big errors. We use the data in

Exhibit 1 to make this figure. The orange line is demand, the blue line is forecast, and the gray

line is error. Through observation, we find that there are errors at almost every point and some of

them are very large. For example, point 40. But French wine is different from Australia. French

wines are produced by many small and medium-sized wineries, each of which tastes different

and therefore difficult to predict. Therefore, except for the special case of point 40, although the
error is large, the error is within a certain range. Therefore, we think this is a more reasonable

model.

The case Club Francais du Vin, The French Wine Club, provides its members high quality wines

at a valuable price. The wine club creates their catalog with a choice of wines from mid-size

growers. Offering club members to choose wine from mid-size growers allows them to attract

the most a distinct niche market, as most wine customers purchase their wine from native

supermarkets, such as Carrefour and Champion. The challenges in this case deal with predicting

how much wine will be ordered by customers, using historical demands. Ordering optimal

quantities to satisfy client demand is crucial to offering exceptional products and to maximize

profits, while reducing unnecessary losses because of overstock.

Due to that, is that the newsvendor model is a reasonable way for computing order

quantities in this case. The reason why this model would be a strategic one, is because of the

perishability of these goods, in this case it would be the relatively low shelf life for both white

and red wine. Let us start by understanding the model further. First, the opportunity cost or cost

of underage in this case would be losing a customer order if demand were to exceed supply. On

the other hand, is the cost of overage, which would be the loss from ordering too much and

having to discard or sell at a discount. The newsvendor model helps us address this, with the

critical fractile, which is the ideal point on the demand distribution.

As director of the Le Club Francais, Stephane Zanella should analyze historical forecasts

and demand information to order optimal quantities of wine for each coming season. This may

not be a straightforward task as customer preferences play a major role on how much wine will

be sold and remain in inventory. If Stephane orders an unnecessary amount of a specific wine,
then he will be selling them at a discount causing profits to suffer. On the other hand, if he orders

too little of a specific wine, then he could have discontent customers. Each of these scenarios

lead to negative consequences for the company and that is why an accurate analysis is needed of

quantities for each season.

2. What are the costs of having one bottle too few in inventory (underage cost)? What are
the costs of having one bottle too many in inventory (overage cost)? List these costs
qualitatively and then attempt to attach numbers to them for a 10 Euro bottle of white
wine.

Underage cost is understocking cost: Cu = Price of Sale - Procurement costs- Transportation

cost. For example, Selling price = 10 euro, and from the article we knew the cost of a bottle = 5
euro, the shipping price=1.25 euro. Therefore, the Understocking cost=10-5-1.25=3.75 euro.

Overage cost is Overstocking Cost = Cost - Discount price = Holding cost capital + Holding
storage cost + Shipping and Handling cost + Procurement cost - Discount price.
Because both white wine and red wine have different storage time and discount rates, their
overstocking costs are different, thus we have to calculate their costs separately.

Overstocking cost for white wine: Selling price = $10, the cost of bottle = 5 euro, Shipping
Price = 1.25 euro, we knew the cost of capital is 15%, so Holding cost capital = Procurement
Cost * 15% * 8 / 12 = 0.5 euro, Holding cost storage = 0.1 * 8 = 0.8 euro, Discount = 40% off,

Discounted Price = 10 * 0.6 =6 euro, thus Co = 5 + 1.25 + 0.5 + 0.8 - 6 = 1.55, then critical ratio
of white wine = Cu / (Cu +Co) =0.71.
Q = NORMINV (0.71,1653.45,1886.03) = 2697.15

Overstocking cost for red wine: Holding cost capital = Procurement cost * 15% * 15 / 12 =
0.9375 euro,
Holding cost storage = 0.1 * 15 = 1.5 euro, because Discount rate is 30%, so Discounted
Price = 10 * 0.7 = 7 euro, thus Co = 5 + 1.25 + 0.9375 + 1.5 - 7 = 1.6875 euro, then the Critical
ratio for red wine = Cu / Co + Cu = 0.69.
Q = NORMINV(0.69,1653.45,1886.03) = 2588.638
3. Assume the underage cost is 3 Euro and the overage cost is 1 Euro. Based on Le Club’s
past forecasting performance (Exhibit 1), how many bottles would you order of a wine that
is forecasted to sell 2000 bottles? (Assume this is the final forecast, i.e., the forecast after
any potential adjustments to ensure that the aggregate forecast across all items matches
some target.)

First let us determine the A/F ratios:

Average A/F = 0.8625

Std A/F = 0.3977
We can also calculate the critical ratio - Cu/Co + Co = 0.75

For the supply of of 2000 bottles of wine -

Mean = 2000*0.8625 = 1725
Standard Deviation = 2000*0.3977 = 795.4
Q=NORMINV(0.75,1725,795.4 )=2279.592

4. Look into trends in the demand and forecast data. Do you see any bias in the forecast?
Do you see any correlations between the price of wine and the forecasting error?
We plot the data in Exhibit 1. From the graph, we can see that the prediction of this model is
inaccurate, and almost every point has a certain error. If we exclude the special case of Point 40,
we can see that although there is a big difference between the maximum and minimum demand
for liquor, people's demand for most liquor is relatively close because the price is much smaller
than forecast. Therefore, in order to better see the price changes in the graph, we expanded the
price value by 100 times. From the comparison of error and price, we find that the prediction
error of higher price wine is relatively small. In other words, the price is inversely proportional to
the error.
5. How much of each wine listed in Exhibit 2 would you order? As Zanella’s consultant,
think of how you would convey key findings without presenting calculations for every
single wine.

We can use the mean of A/F ratio and the standard deviation of A/F ratio. Using mean of A/F
ratio * forecast we can know the mean of demand of each wine. The standard deviation of A/F
Ratio * forecast, we can know the standard deviation of demand of each wine. Then we have to
calculate Cu = p - c and Co = c - s.
Critical Ratio = Cu / Co+Cu.
Then use excel type Q=NORMINV( critical ratio, mean of demand, standard deviation of
demand )
The Q is the answer to “how much of each wine listed in Exhibit 2 would you order?”

6. Discuss patterns and any peculiarities that you find in the order quantities and critical
ratios across different wines.

Forecast Demand A/F Ratio Error of Price Cu Co Critical Q Critical Ratio *

Forecast Ratio 10000

10,000 11,280 1.128 1280 590 1.7 2.123 0.44466 8099.2497 4446.6241
1,200 252 0.21 948 720 2.35 1.985 0.54209 1088.804 5420.9919
900 540 0.6 360 1050 4 1.634 0.70992 976.7971 7099.2789
800 864 1.08 64 690 2.2 2.016 0.52171 709.5542 5217.1335
3,000 2169 0.723 831 820 2.85 1.878 0.60269 2906.5189 6026.9627
900 1034 1.148 134 2295 10.22 0.312 0.97043 1454.3809 9704.3034
600 384 0.64 216 995 3.72 1.693 0.68753 635.8594 6875.4686
400 414 1.035 14 1070 4.1 1.613 0.71764 437.7420 7176.4577
1800 612 0.34 1188 595 1.72 2.117 0.4488 1465.520 4488.8997
1374.166
3960 5436 1.372 1476 667 5.62 1.289 0.8133 4828.939 8133.4267
600 528 0.88 72 3390 15.7 -0.85 11.0573 #NUM! 10573.725
900 1014 1.1267 114 1090 4.2 1.505 0.7361 1004.897 7361.963
1200 1500 1.25 300 950 3.5 1.74 0.667 1245.512 6678.592
2500 2,070 0.828 430 330 0.4 2.39 0.1428 1101.663 1428.890
3000 2,784 0.928 216 325 0.37 2.404 0.1349 1278.962 1349.0725
2000 1,974 0.987 26 330 0.4 1.885 0.175 987.185 1750.547
2500 4,057 1.622 1557 450 1 2.271 0.305 1657.746 3056.351
2600 1,992 0.7661 608 358.75 0.54 2.368 0.186 1329.073 1866.902
3000 726 0.242 2274 1190 4.7 1.485 0.759 3438.063 7598.262
800 402 0.502 398 590 1.7 2.123 0.444 647.9399 4446.624
2500 1380 0.552 1120 540 1.45 1.78 0.448 2035.512 4489.164
900 612 0.68 288 610 1.8 2.101 0.461 743.983 4613.1667
1800 1170 0.65 630 520 1.35 2.197 0.3805 1339.807 3805.4968
1500 960 0.64 540 755 2.52 1.947 0.5645 1394.850 5645.2176
3000 2100 0.7 900 730 2.4 1.685 0.5875 2859.769 5875.1529
2300 2934 1.275 634 550 1.5 2.165 0.4092 1780.094 4092.071
300 703 2.343 403 1995 8.72 0.630 0.9325 438.069 9326.251
500 480 0.96 20 890 3.2 1.804 0.6394 503.639 6394.404
2700 1968 0.728 732 470 1.1 2.25 0.328 1858.718 3282.969
1800 1356 0.753 444 1372.5 5.61 1.291 0.812 2193.815 8129.087
300 324 1.08 24 2950 13.5 0.575 0.959 467.336 9591.474
1200 567 0.47 633 750 2.5 1.675 0.598 1157.794 5988.023
900 741 0.823 159 890 3.2 1.605 0.665 932.276 6659.729
2100 1910 0.90 190 1246.5 4.98 1.425 0.77 2455.214 7775.32
2000 1176 0.588 824 682.5 2.16 2.024 0.516 1763.351 5164.371
2200 1788 0.812 412 517.5 1.33 2.20 0.378 1631.851 3780.751
900 834 0.927 66 617.25 1.83 1.74 0.513 790.654 5132.594
2100 2208 1.051 108 637.5 1.93 2.072 0.483 1781.793 4831.482
1100 1191 1.082 91 971.25 3.60 1.564 0.697 1178.068 6974.495
10000 1704 0.170 8296 1040 3.95 1.645 0.705 10807.658 7059.874
The critical ratio is far less than Q. Therefore, in order to better see the changes in critical ratio in
the figure, we expanded the critical ratio by 10,000 times.From the figure we can see that Q and
critical ratio are inversely proportional. Q goes down when the critical ratio goes up.

7. Explain if the order quantities make sense from a customer service standpoint (e.g. price,
availability, quality, etc).

We plot the data. From the figure we can see that Q is close to forecast, but at many points
Q is smaller than forecast. This may lead to a shortage of supply and demand. From the point of
view of serving customers, we think the order quantities don't make sense.