Case Studies

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: 

1. Too many values to be processed manually in an acceptably short period of time.
2. A lot of room for human error that might lead to low prediction accuracy.
3. No role division, which increases the probability of human error.
4. A manual approval process that often takes a long time due to the lack of notifications.
5. Data for sales prediction had to be taken manually from several external systems.

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: 

1. The information about previous sales is automatically aggregated from multiple external sources, saving time.
2. Millions of values per report are calculated automatically, saving even more time. 
3. Several sales scenarios make the process much more flexible.
4. Many additional parameters can be applied to the resales planning that was not possible before (such as employee experience or marketing campaigns).
5. The process of approval is fully automated, including notifications.
6. The system tracks the hierarchy of managers for each territory to ensure that the reports are sent to the right recipient.
7. New forms of drugs can be added to the system to be accounted for in the next resales planning.
8. Managers now have restricted data access and can only view information pertinent to their territory.