Problem and assignment

In this segment it is vital to ensure that your commercial partners receive perfect service and reliable deliveries. Companies such as Alza, OK, Vodafone or Globus don’t tolerate gaps in deliveries – while at the same time, you can’t buy more warehouse stock than you’ll sell because unsold stock would make your goods more expensive and make you uncompetitive.

To allow Witty Trade to estimate future demand and order goods accordingly, it works with sales reports that its partners regularly e-mail to it.

These reports are different for every partner, and most are generated automatically, although a minority of partners create them manually, and they are usually sent as e-mail attachments in XLS, CSV or PDF format.

Reports from vendors contain three important pieces of data:

• Item ID
• Number of pieces sold
• Number of pieces left in the warehouse

Witty Trade used to process these reports manually, and from this would create a complete report on all products that allowed it to determine the turnover rate of individual products and make a limited estimate of how much of which product would have to be ordered additionally.

The creation of these reports resulted in errors and was fairly labour-intensive due to the differing formats and data used (e.g. vendor’s internal ID rather than the product ID, etc.). Realistically, this activity would take up half the working hours of the employee assigned to the task.

Furthermore, the report was only updated once per week, even where continually updated data was available. Additionally, only one report was available, even though Witty Trade has several product managers responsible for various product series and customers, and therefore primarily need to see their own data.

Solution

To find a solution to all these problems, Witty Trade consulted Revolt BI. We adopted a comprehensive approach to the issue and created a completely new system with many advantages:

• Automatic loading of source reports from vendors from the e-mail inbox, and the subsequent processing, repair and cleansing of all data
• Creation of a control system to highlight possible errors (e.g. monitoring of expected range of values)
• Prediction of future sales, using elements of machine learning to increase the quality of forecasts
• Creation of required reports
• All steps are fully automated, without the need for human intervention, and updated every 30 minutes
• All code lists and other source data are stored with the client, who can administer them itself

Result

The data obtained allowed us to create many useful Tableau reports that help Witty Trade achieve better commercial results and a higher degree of client satisfaction.

• Sell-in (sale to partners) and sell-out (sale to end customers) reports – including retail prices from the internal system.
• Report on warehouses, residual stock and alerts for “left-over” goods.
• Reports for individual product/account managers, including future sales predictions and an overview of optimal quantities of warehouse stock and the optimal volume of the next order, showing the status of the partner’s stocks and whether there is time to order new goods in view of delivery lead-times.
• Other Tableau reports in which the client can monitor data categorized by partner, product, various time frames, attributes (product life cycle) and metrics sale vs. piece, warehouses, turnover rate, orders recommended above)