For manufacturers of perishable products, time is quite literally money - and if it isn’t managed correctly, that money will be lost. Every year, manufacturers lose billions of dollars on inventory that expires before it reaches customers.
The problem is particularly acute for a company like GlaxoSmithKline, whose supply chain is subject to a number of unique constraints. Recently, the pharmaceutical giant partnered with Thorogood to create an analytics solution that would enable it to tackle the problem. The result was a new Power BI platform that shows just how much value firms can realize with a technology-forward approach to their supply chain dilemmas.
A new approach to an old problem
According to an analysis published by Management Science in November 2018, consumer packaged goods firms lose from one to two percent of gross retail sales each year on expired inventory. In the pharmaceutical industry, the challenge is even more daunting. For a global company like GlaxoSmithKline, minimizing write-offs requires an accurate accounting of a slew of variables, many of them dependent on the regulatory frameworks of the markets in which products are sold.
After years of implementing tactical solutions, GlaxoSmithKline wanted to build a tool that would allow it to act in a more informed and proactive manner. Thorogood had already helped the company position itself for such a future by designing and deploying a data mart that could feed the kind of application that it envisioned. GlaxoSmithKline saw an opportunity to fully harness the power of that data and build towards the future.
The first step was to put the marker to the white board and come up with a list of clearly defined goals and focused scope. GlaxoSmithKline’s vision was a tool that would allow users to monitor the flow of inventory in nearly 50 of its markets. More specifically, it wanted a tool that gave GlaxoSmithKline teams the ability to understand how to best manage the stock in market. By doing so, GlaxoSmithKline could ensure stock was in the right place, at the right time, and not wasted.
With decades of experience building analytics solutions aimed at tackling business challenges of all sorts for a realm of blue-chip companies, Thorogood understood that GlaxoSmithKline’s aspirations were well within reach. Designing such a solution required an intimate understanding of its business processes and the variables that impacted the company’s performance. GlaxoSmithKline evaluated its distribution cycle using three key performance indicators: the stock in hand, stocks in transit and stocks on shelves. Given that each of the products came stamped with an expiration date, and that each of its markets had different rules governing how close to that date a product could remain on shelves, timeliness and accuracy were an essential focus. GlaxoSmithKline needed a tool that would provide a comprehensive view of reality in as close to real time as possible.
With Power BI and GlaxoSmithKline’s existing data mart, Thorogood was able to automate the flow of data using Azure Data Factory pipelines that would eliminate hours of manual labour spent in many of the calculations and data-loading processes involved in the spreadsheet-based setup. Furthermore, the new level of automation would ensure that analyses were being performed using the correct sets of information. It would also create a level of standardization for its stock at risk across markets, creating a framework in which GlaxoSmithKline could take a global view of planning.
Creating a user-focused tool
A robust data discovery tool should be built with both the business problem and business user in mind. This premise is inherent to Thorogood’s Iterative Dashboard Methodology, which the team used to develop the user-end portion of the solution. The method features consultants working side-by-side with users and decision-makers to design an initial version of the dashboard, and then continuously adapt the dashboard views through subsequent rounds of feedback. The end output is a fully functional solution that satisfied the need of the business users.
In GlaxoSmithKline’s case, the result was a system that leveraged Power BI’s visualization capabilities to give users the ability to quickly ascertain the most granular of realities and then perform data-driven analyses on any identified problems. Using the dashboard, analysts can now see at a glance which products are at expiration risk. They can also identify products that are moving slower than anticipated. From there, users can perform a root cause analysis within Power BI, which they can use to pinpoint the exact cause of a slow-moving or expired product with a great deal of accuracy and confidence.
One of the most innovative features of the solution is the way it facilitates collaboration and analyses among users. While GlaxoSmithKline wanted to take a more scientific approach to its at-risk stocks, it also recognized that the process is not an exact science. Users wanted to retain the ability to flexibly scenario plan that a spreadsheet-based approach had given them. Thorogood’s consultants accomplished this using PowerApps. In addition to allowing users to simulate various planning scenarios and adjust key input variables, the app facilitated collaboration by allowing users to enter comments and agree on actions to be carried forward to the next month based on the case scenario.
A foundation to build on
The launch of the new solution has transformed GlaxoSmithKline’s ability to manage its stock and limit losses. While most of its users are supply chain planners and managers, the tool provides a general enough overview that higher-level executives can use to understand the current picture. Positioning itself better for the future, GlaxoSmithKline is looking at enhancing the solution and increase its usability by expanding the set of actions and insights available to users from within the tool. For example, the company is exploring ways to incorporate Machine Learning models to allow for a better prediction of stocks at risk and proposed mitigations. Overall, the new tool offers a powerful look at the ways that forward-thinking firms in a variety of industries can leverage technology to minimize losses.