International businesses often have established recurrent processes that have to be performed several times per year, but their optimization leaves much to be desired. Business growth often comes with routine operations being blown out of proportion by the amount of data that has to be managed by a lot of people. To solve this issue for one of our clients, we developed a resales planning system.
The client is an international pharmaceutical company with branches in over 100 countries. Every drug they manage has a special form, with over 50 forms total. Each drug is part of a business unit that operates the resale of the drug.
The process used to look like this:
There are multiple regional managers for city A, and each one is responsible for their list of drugs. They have access to sales statistics for previous periods. Based on these statistics, each manager enters the sales of their drugs into an Excel file that is then sent to a senior manager. That manager receives Excel files from dozens of regional managers. Those files comprise several million values, which the managers have to manually merge into several comprehensive sales planning reports such as drugs, drug forms, regions, and country. That had to be done several times a year, each calculation yielded millions of values.
This approach was ineffective and even restrictive due to several factors:
Usually, optimizing such a process would involve an OLAP database that allows managing big volumes of data and can also offer very fast operation speed. However, the client did not have specialists with the necessary expertise at the time, and they would not be able to support the system further down the line. Thus, we implemented a special database that could ensure that millions of values will be processed at a high speed. Other more common databases would take up to several minutes trying to process standard requests with that amount of data.
The scenarios can be easily modified to take personal employee experience into account, which might boost sales of other parameters that are selectable from inside the system. Every manager has access only to the territory of their expertise; there are several dozens managers overall.
Moreover, the system features an approval system in which the regional manager sends a report to the senior manager, who either approves it or sends it back with comments on how it could be improved. The process includes notifications and comprehensive user-friendly interface for easier communication across the hierarchy that the system manages. As soon as the head of sales approves the final aggregated report, the planning period is considered closed.
In theory, this system is scalable for any sales or resales planning since its main goal is to manage two trees of values: territories and a product catalogue. As a result, the sales planning process was improved in many ways: