Forecasting And Procurement At Le Club Fran Ais Du Vin Finance Essay

Forecasting And Procurement At Le Club Fran Ais Du Vin Finance Essay Le Club Franà §ais du Vin is founded in 1973 and had grown to a 10 million Euro per year business in 2004. The mission of Le Club is to offer wines of good to very good quality to its customers in France, Switzerland, and Germany, who receive interesting wines delivered directly to their homes. Every member of Le Club receives an offer of wine every two months via a catalog. Le Club Franà §ais du Vin largely carries French wines. The heterogeneity of French wines makes forecasting consumer demand for particular French wine extremely difficult. At Le Club Franà §ais du Vin, a group of professional wine experts create a sales forecast for each wine in the upcoming catalog taking into account both taste considerations and the season of the year in which the wine is offered in the catalog. Once the forecasting process is over, Le Club places an order with the wine grower, which happens months before publishing the catalog and at a point when little information beyond the wine experts personal opinions is available. The Club pays the wine grower 75 days after having received the shipment. If the wine forecast equals the actual demand or comes close to it these payment conditions are very favorable for Le Club. However, such desirable cash flows are not always the case. If Le Club has over forecasted sales for the catalog season, excess bottles are stored in the warehouse and are likely to be discounted in a future catalog (white wines are discounted by 40% of their retail price, and red wines by 30%). There is also an additional handling and shipping cost for discounted bottles of 1.25 Euro per bottle, and 0.10 Euro warehouse operational costs per bottle. The main problem of the company is the mismatch between forecasts and actual customer demand, which results in either excess inventory or unsatisfied customers. For example, the Club had ordered 10,000 bottles of the 2002 St Emilion wine for the companys January 2004 catalog, but only sold 1,704 bottles. On the other side, the Club forecasted to sell 10,000 bottles of the Cà ´tes du Rhà ´ne, but actually experienced a demand of over 11,000 bottles. The Club currently holds over 200,000 bottles of wine in its warehouse. The company has to choose between few options in order to decide how many bottles of each wine to order to maximize expected profit, to generate a certain fill-rate or to achieve a certain in-stock probability. If the manager chooses as an objective to maximize the expected profit, as seen in Exhibit 1, the total expected profit is supposed to be 147,998 Euro. However, the profit-maximizing order quantity may generate some unacceptable fill rate and in-stock probability from the firms customer service prospective. The fill rate varies in the range of 50% to 100%, while the stockout probability varies in the range of 0% to 83%. This scenario will result in a lot of unsatisfied customers who might choose a different supplier in the future. The customers of the Club place their order by mail, phone, fax, or over the internet. If the customers place their order by phone or online they can be informed right away if a particular wine is out of stock. However, as a large portion of Le Clubs customers are in their 60s, orders by mail are most common, and these customers are unaware of the availability of the wine there are ordering. It is very rare for the company to be able to place additional orders for wines that have been under forecasted. As a result all demand for a wine that remains unfulfilled is lost. Given the complications associated with stock-outs, Le Club aims at high availability for its wines throughout the catalog season. That is the reason why the first scenario is not suitable for the company. Let us assume that the company chooses to guarantee a fill rate of 99%, which means that 99% of the demand will be satisfied. As seen in Exhibit 2, the total expected profit is 102,382 , which is about 45, 000 euro less than the profit it generates in the first scenario, however, the in -stock probability is 94.74%. This is a better scenario for the Club, because it is going to guarantee that most of the customers during the season can be satisfied, and there is also a great probability that the customers demand can be satisfied even at the end of the season. The fill rate is a good measure of average customer service because it treats each customer as equally important. So, even though the company might experience some profit loss for certain types of wine, the total expected profit is 102,382 Euro, and along with that the Club can also achieve high levels of fill rate and in-stock probability. The third option for the club is to choose to set as its primary goal to achieve a high in-stock probability (let us assume 97.5% rate). As seen in Exhibit 3, in this case the total expected profit is only 88,138 Euro, which is almost half of the expected profit in the first scenario. The fill rate is 99.57%. We see that achieving a very high in-stock probability can be quite expensive and sets the company at a much lower profit level. This scenario is also unacceptable for the company. The company has to constantly try to balance the cash constraints inherent in holding large inventory positions with the goal of sustaining healthy margins (the club typically enjoys around 50%) while ensuring availability of a broad selection of wines even late in a catalog season. Therefore the club needs to make tradeoff to give up some of its profit in order to obtain higher fill rate and in-stock probability in order to ensure better customer service and to keep its positions in the market. The second scenario seems the most optimistic and optimal for the company it will lose some of its profit, but on the other side will guarantee a greater customer satisfaction, which is very important for the Club that capitalizes on a niche market. Appellation Q that maximizes expected profit Expected profit Fill Rate Stockout probability FAUGERES 12022 16235 88.47% 36.58% GRAVES 803 1847 91.12% 30.32% GRAVES 1149 2076 93.58% 23.77% PESSAC LEOGNAN 3241 11721 100.00% 0.00% CARTON PANACHE 6+2+4 5093 12880 99.38% 3.40% BORDEAUX CLAIRET 3461 3286 81.65% 50.00% CÔTES DE BOURG 1352 1985 90.00% 33.05% ENTRE DEUX MERS 1129 940 74.41% 61.14% BORDEAUX 4535 3063 74.63% 60.84% CARTON PANACHE 5493 5993 84.41% 44.98% Bordeaux 2127 1332 73.05% 62.96% VDP des Cà ´teaux de LArdà ¨che 1651 344 50.59% 83.87% VDP des Cà ´teaux de LArdà ¨che 1412 318 52.08% 82.91% VDP du Comtà © Tolosan 1041 227 48.72% 85.02% CARTON PANACHEE 1692 547 59.22% 77.54% CABERNET DANJOU 2630 2581 82.31% 48.84% SANCERRE 2092 6068 93.93% 22.76% CHINON 4071 4315 83.84% 46.05% ALOXE CORTON 2992 13549 100.00% 0.00% BOURGOGNE ALIGOTE 1013 1505 84.68% 44.44% GIVRY 1734 4028 99.95% 0.38% COTEAUX DU LYONNAIS 2543 2293 80.61% 51.78% CDR Vill RASTEAU 1075 2084 94.73% 20.40% GIGONDAS 2493 5225 100.00% 0.00% CÔTES DU VENTOUX 1052 1032 82.31% 48.84% CARTON PANACHE 3742 7788 95.87% 16.85% CORBIERES (6) 1155 1169 82.94% 47.71% GAILLAC 2248 2347 83.54% 46.60% MINERVOIS 3322 2847 79.57% 53.48% MADIRAN 14445 28372 94.95% 19.75% Total Expected Profit 147,998 Exhibit 1 Appellation Q that guarantees fill rate of 99% Expected sales Expected leftover inventory2 Expected profit (fill rate = 99%) In-stock probability FAUGERES 18121 10280 7841 12379 94.74% GRAVES 1133 642 490 1588 94.74% GRAVES 1510 857 653 1926 94.74% PESSAC LEOGNAN 1963 1114 849 10134 94.74% CARTON PANACHE 6+2+4 4832 2741 2091 12871 94.74% BORDEAUX CLAIRET 6040 3427 2614 1219 94.74% CÔTES DE BOURG 1963 1114 849 1632 94.74% ENTRE DEUX MERS 2265 1285 980 -341 94.74% BORDEAUX 9060 5140 3920 -1022 94.74% CARTON PANACHE 9060 5140 3920 3338 94.74% Bordeaux 4379 2484 1895 -737 94.74% VDP des Cà ´teaux de LArdà ¨che 5285 2998 2287 -3335 94.74% VDP des Cà ´teaux de LArdà ¨che 4379 2484 1895 -2682 94.74% VDP du Comtà © Tolosan 3473 1970 1503 -2623 94.74% CARTON PANACHEE 4530 2570 1960 -2289 94.74% CABERNET DANJOU 4530 2570 1960 1082 94.74% SANCERRE 2718 1542 1176 5678 94.74% CHINON 6795 3855 2940 2252 94.74% ALOXE CORTON 1812 1028 784 11367 94.74% BOURGOGNE ALIGOTE 1661 942 719 863 94.74% GIVRY 1359 771 588 3997 94.74% COTEAUX DU LYONNAIS 4530 2570 1960 663 94.74% CDR Vill RASTEAU 1359 771 588 1985 94.74% GIGONDAS 1510 857 653 5001 94.74% CÔTES DU VENTOUX 1812 1028 784 433 94.74% CARTON PANACHE 4530 2570 1960 7572 94.74% CORBIERES (6) 1963 1114 849 542 94.74% GAILLAC 3775 2142 1634 1181 94.74% MINERVOIS 6040 3427 2614 571 94.74% MADIRAN 18121 10280 7841 27136 94.74% Total Expected Profit 102,382 Exhibit 2 Appellation Q that guarantees In-stock probability = 97.5% Expected profit(in-stock probability = 97.5) Expected fill rate FAUGERES 19745 10565 99.57% GRAVES 1234 1444 99.57% GRAVES 1645 1820 99.57% PESSAC LEOGNAN 2139 10387 99.57% CARTON PANACHE 6+2+4 5265 12876 99.57% BORDEAUX CLAIRET 6582 466 99.57% CÔTES DE BOURG 2139 1450 99.57% ENTRE DEUX MERS 2468 -739 99.57% BORDEAUX 9872 -2297 99.57% CARTON PANACHE 9872 2286 99.57% Bordeaux 4772 -1366 99.57% VDP des Cà ´teaux de LArdà ¨che 5759 -4219 99.57% VDP des Cà ´teaux de LArdà ¨che 4772 -3410 99.57% VDP du Comtà © Tolosan 3784 -3300 99.57% CARTON PANACHEE 4936 -3017 99.57% CABERNET DANJOU 4936 526 99.57% SANCERRE 2962 5391 99.57% CHINON 7404 1450 99.57% ALOXE CORTON 1974 11703 99.57% BOURGOGNE ALIGOTE 1810 606 99.57% GIVRY 1481 4018 99.57% COTEAUX DU LYONNAIS 4936 85 99.57% CDR Vill RASTEAU 1481 1903 99.57% GIGONDAS 1645 5052 99.57% CÔTES DU VENTOUX 1974 210 99.57% CARTON PANACHE 4936 7347 99.57% CORBIERES (6) 2139 304 99.57% GAILLAC 4113 732 99.57% MINERVOIS 6582 -215 99.57% MADIRAN 19745 26076 99.57% Total Expected Profit 88,138   Exhibit 3