Managing Complexity Through Data Warehousing

Coca Cola Japan uses a solution from NCR Teradata to manage almost 1 million soft-drink vending machines.

1188400663902 10315873

Supply chain executives sometimes say their job is simple- just get the right product to the right place at the right time to satisfy customers.

In practical terms, however, this can be a huge challenge, requiring agile logistics coupled with expert use of strategic data to create accurate forecasts, which in turn must be readjusted continually to reflect actual item movement––all while minimizing operational costs. Rather than a simple job, in the real world the supply chain executive's task may be more like juggling flaming torches while balancing on a ball.

For most companies these days, accumulating the kinds of data necessary to drive the supply chain is not the problem. Raw data in today's business world is fairly cheap and plentiful.

The real challenges and opportunities lie in how the mountains of data that may be collected are managed, analyzed and utilized. The crux of the issue is not unlike the central challenge of the supply chain itself: how to get the right information to the right people at the right time, so they can make the right decisions.

The more complex the marketplace, the more complicated the challenge. One example of how some companies are solving this puzzle is provided by the Coca Cola Japan Group, which is using advanced data warehousing techniques to manage vending machine operations.

Complex Market, Short Product Lifecycles

The beverage vending market in Japan is the very definition of complex. The country has an installed base of approximately 2.4 million soft-drink vending machines, including nearly a million that sell Coca Cola products. In Japan, these machines typically supply both hot and cold products, with each machine stocking 20 to 30 different SKUs, including a host of beverage varieties not seen in the U.S.

The durable life of hot products in the vending machines is typically just three weeks.

While the vending environment is saturated, with almost no room to add more machines, the rate of new product introductions continues to increase.

According to the Nikkei point-of-sale analysis, between April 2005 and March 2006 some 995 new drink products were introduced in Japan-a typical annual rate.

With so many new varieties coming into the market each year, it is not surprising that product lifecycles continue to shorten. Many new products come and go in the space of just a few months.

At the same time, the vending market in Japan faces new and increasing competition from other channels, particularly convenience stores. While the vending industry is highly developed and more complex than in the U.S., widespread growth of convenience stores in Japan is a recent phenomenon. In the last five to 10 years, they have become a popular destination for young people in particular, with most now open 24 hours and offering other attractions such as ATMs and kiosk terminals.

C-stores have proved formidable competitors in the beverage market, offering even more choices than Japan's already well-stocked vending machines. This is true particularly in the category of PET-packaged soft drinks, which have grown in popularity compared to the canned products more typically available from machines, in part because PET bottles are re-sealable.

In a market that puts a big premium on novelty, coupled with growing preferences for fresher product and new "healthy" drink varieties, convenience stores and other retail outlets are providing stiffer competition than ever before, resulting in a significant loss of market share for vending operations over the last decade.

From 1998 to 2002, vending's share of the beverage market shrank from 44 percent of total units sold to 38 percent, as the percentage of sales through retail stores grew from just over half to 62 percent of total beverage purchases.

In that same period, the industry average number of cases sold per machine decreased from 290 annually to 254 , while the total number of vending machines grew by less than one-tenth of one percent.

Moreover, in recent years Coca Cola Japan, which franchises 12 bottling companies in Japan, has faced increased competition from other beverage suppliers, including entrants from other industries who see strategic opportunities to expand into the high-margin vending machine channel.

During this same period, promotional costs and distribution expenses have increased, while lower-margin products have grown as a percentage of sales.

Using Vending Machine Data

In this ultra-challenging environment of stiffening competition, diversifying consumer needs and desires and rising costs coupled with lower profitability, Coca Cola Japan group was prompted to examine ways it could better exploit data from vending machine sales, in order to reverse the declining market share trend while improving profitability.

Coca-Cola West Japan Co. (CCWJ), Mikuni Coca-Cola Bottling Co. (CCBC) and Hokuriku CCBC were among 12 bottlers who became involved in this

These companies were already capturing column-by-column data from vending machines, but it was used almost exclusively for settlement. The companies had no good way to capitalize on this untapped goldmine of detailed information on exactly what was selling where and when and at what price.

Their goal became the development of a system that would enable them to leverage this potential wealth of data for both strategic and operational purposes, to support management and analysis of sales, refill ratios, stock-outs, machine failures, refill planning support, location performance and all aspects of category management.

Working with NCR Teradata, Coca Cola bottling partners developed an extensive data warehousing application to accomplish these objectives. More recently, the system was linked by Hokuriku CCBC directly to its vending machine column control system, creating a comprehensive data management architecture that can be used for everything from high-level strategic analysis to supporting daily route operations.

Data from the vending machines is collected by the system in two ways. About 20,000 Coca Cola vending machines are wirelessly networked online, so that data from their operations flows into the system automatically. Data from off-line (i.e. non-networked) machines is collected by route salesmen on hand-held terminals when they visit each location and uploaded to the host system when they return to the office at the end of their routes.

The information collected includes sales data by column, including volume and price; sold-out information, including column, product, time and duration; and machine breakdown information, including time and cause.

The data is stored in the data warehouse and analyzed by marketing people and operations managers at each bottler. Aggregated sales information by vending machine is sent to Coca Cola Japan and to The Coca Cola worldwide headquarters. Data is maintained in the warehouse for at least three years in granular form.

Among the most important jobs in vending operations is deciding which vending locations need to be visited when, so route salespeople don't waste time visiting unnecessary locations, notes Atsumasa Shimizu, managing director of Hokuriki CCBC and president of HISCOM, a subsidiary of Hokuriki CCBC, which specializes in vending software technology applications.

For this application, real-time sales information provided by online machines is most beneficial, but historical data is also helpful in analyzing trends that can be used for route planning.

"The secret of improving operations is to increase the length of visit or call intervals while prohibiting sold-out situations," Shimizu points out. Normally, increasing the interval between visits would lead to more stock-out occurrences. By leveraging real-time, "fresh" data from the online, networked vending units, the bottlers have been able to greatly improve forecasting, resulting in better route sales productivity coupled with fewer stock-outs.

Mikuni CCBC, the first to implement the data warehouse system in 1998, for example, achieved a 13.6 percent decrease in stock-outs combined with a five percent increase in refill rates and a 10 percent increase in route salesman productivity in the first year. Both Mikuni route salesmen and high-level managers make decisions based on information accessed in the data warehouse. These and other benefits from the system resulted in improved profitability to the tune of 800 million yen a year.

Other key applications include strategic product support, i.e. deciding on the most appropriate inventory at each individual location based on sales performance and trends. This includes determining when to switch columns from hot to cold product or vice versa.

Other crucial applications center on managing stock-outs. These include both the operational tasks related to refilling empty columns and the more strategic task of reducing stock-out situations through improved column management and more efficient call interval planning.

Selecting The Right Product Mix

With approximately 200 SKUs available for vending at any time, but room for only 10 to 20 product varieties in each machine, selecting the right mix of products for each location is a big challenge. The Column Control System supports the route salesman in planning the optimal assortment for each specific location. Today, information from the data warehouse, including both fresh sales data and historical trends, is fed into the Column Control System to enhance this process.

Key bottlers, including CCWJ and Hokuriku CCBC, have been working with Teradata for more than a year to leverage the capabilities of the data warehouse to improve the vending business. The bottlers are working to start a data warehouse center where they can share vending applications and IT infrastructure in the future without implementing their own sites. This shared data warehouse concept has been recommended by the Coca-Cola Japan franchiser.

The data warehouse also plays an important role in new product management. Fresh data from the online vending machines is used to help evaluate the performance of new products, while real, up-to-date consumption data also helps with phase-out management.

Data from the online machines represents immediate, direct feedback from consumers, Shimizu points out and has been of tremendous value in evaluating trends not only in networked machines, but in offline locations as well, by applying information from online vending units to offline units with similar consumer characteristics. It has been particularly invaluable in evaluating new product performance, where historical trends, of course, are not available.

Among Hokuriku's key goals for further improving the system is to find ways to lower wireless communications costs, to enable a greater percentage of machines to be networked online.