Understanding the problem
A very large manufacturer approached us with a simple task of building them a reporting mechanism for component pricing in order to aid them with direct negotiations. As we started to get acquainted with the business process of the manufacturer we discovered that many processes were executed with excel spreadsheets. The decision makers at the company told us that they intuitively knew that the situation had drawbacks, but didn’t have the knowledge to implement better solutions.
More perplexing to us was the fact that there were three separate ERP packages available that seemingly had some - if not all - of the functionalities required. Of course the mere fact that there were three systems in place indicated a significant disconnect between the corporate level and the individual departments. We found out that the usage of spreadsheets was directly tied to the fact that many of the interactions with third parties was conducted with spreadsheets as lowest common denominator indicating that a forced change to an alternative system would most likely not be fruitful.
In addition, due to the rapid rotation of the workforce, training of new systems would prove very inefficient whereas basic knowledge in excel was expected from any employee. We had to figure out a way in which we utilize the current processes while introducing the least amount of new information to be learned.
Learning from domain experts
In order to really gain an understanding of the business process we requested to be fully immersed in the daily operations. Not as observer, but rather as part-time employee. We quickly identified one process that seemed most labor intensive and inefficient where the team would semi-manually compare component pricing of a new project against a spreadsheet with historical pricing for the same components – the same list that we were tasked to create a reporting mechanism for. Other than the inefficiency of manual labor, the process was not taking into consideration price discrepancies stemming from volume variances – effectively negating the coherence of the produced reports. In addition, due to the manual labor involved, many of the components weren’t manually verified and compared.
Identifying the low hanging fruit
With the newly gained insight into the process, we decided to propose an automated solution for the component pricing comparison making sure that the data cleaning process for the end-user was kept to a minimum. The final solution after full buy-in from the business decision makers requires only a re-save of the familiar excel sheets into csv format and the mapping of the fields via a simple, interactive interface. Not only does the team member get immediate feedback of current vs. historic pricing, but all component manufacturers that don’t have pricing for loaded components get automated pricing inquiry packets. Now the team doesn’t only have a reporting mechanism of component pricing – our initial task – but a price history graph with cost points in conjunction with order volume and package pricing, giving a significantly more objective image on the price history.