Pandata Blog

AI design and development for high risk industries

Midwest Industrial Supply

Case Study – September 26, 2018

Midwest Industrial Supply specializes in environmentally friendly dust control applications, operating out of a series of regional satellite offices. When pricing a new spray opportunity, the sales team is incentivized to maximize the variable margin, or price minus the cost of chemicals, without easily considering how the opportunity fits within the constraints of scheduling and resources of the satellite office, which can negatively affect profits. As is the case with many engagements, centralizing critical data and implementing processes to manage quality resulted in substantial gains. In this case, these fixes will initially help improve profits by $150-$250k and expectations are the full realization of $1.0 million in total profit improvement.

We began our engagement with the goal of developing a dynamic pricing calculator that incorporates satellite capacity. Midwest possesses a wealth of relevant information. However, data is stored across different databases and spreadsheets, leading to difficulties in version control and increased potential for data inconsistencies. An essential first step was to develop a system to combine disparate data sources and migrate information contained in spreadsheets into a centralized, version controlled, database.

We began by performing an in-depth review of data sources to determine where relevant data were located and how the different data sources tied together. We also developed and implemented an extract, transform and load (ETL) process for moving pricing information from spreadsheets to a centralized database. This will allow for improved version control and ensure that future pricing will be based on the most accurate and current data. As a first step towards developing the dynamic pricing model, we created a custom, interactive dashboard that implements pricing algorithms and displays costs, prices and financial metrics at different levels of granularity. It also lets the user input theoretical prices and explore how profit margins both opportunity and companywide are affected.

In the process of reviewing data sources, we discovered data inconsistencies that could lead to costly calculation errors in pricing. Centralizing and unifying all data sources will greatly reduce such errors, potentially leading to several hundred thousand dollars of increased revenue. The dynamic pricing calculator will also lead to significant increases in revenue by properly pricing opportunities that differently utilize personnel and time resources. The calculator will also lead to significant savings in person-hours by automating much of the decision process and eliminating the need to cross-reference pricing tables from different data sources.

A key element to the success of this engagement is the collaborative nature of the process with the Midwest team. The pricing calculator will be refined based on feedback and ultimately designed with the combined goal of functionality and ease of use leading to long-term financial gains long after the engagement concludes. Going forward, we will develop a more sophisticated pricing model that directly uses local resources in pricing and implement the calculator such that it draws on centralized data assets and interacts seamlessly with the existing CRM infrastructure.