Macfarlan Smith has earned its place in medical history. The company held a royal appointment to provide medicine to Her Majesty Queen Victoria and supplied groundbreaking obstetrician Sir James Simpson with chloroform for his experiments in pain relief during labor and delivery. Today, Macfarlan Smith is a subsidiary of the Fine Chemical and Catalysts division of Johnson Matthey plc. The pharmaceutical manufacturer is the world's leading manufacturer of opiate narcotics such as codeine and morphine.
Every day, Macfarlan Smith is making decisions based on its data. The company collects and analyzes manufacturing operational data, for example, to allow it to meet continuous improvement goals. Sales, marketing, and finance rely on data to identify new pharmaceutical business opportunities, grow revenues, and satisfy customer needs. Additionally, the company's manufacturing facility in Edinburgh needs to monitor, trend, and report quality data to ensure the identity, quality, and purity of its pharmaceutical ingredients for customers and regulatory authorities such as the U.S. FDA and others as part of current good manufacturing practice (CGMP).
Challenges: Multiple Sources of Truth and Slow, Onerous Reporting Processes
The process of gathering that data, making decisions, and reporting was not easy, though. The data were scattered across the business including in the company's bespoke enterprise resource planning (ERP) platform, inside legacy departmental databases such as SQL, Access databases, and stand-alone spreadsheets.
When those data were needed for decision making, excessive time and resources were devoted to extracting the data, integrating them, and presenting them in a spreadsheet or other presentation outlet.
Data quality was another concern. Because teams relied on their own individual sources of data, there were multiple versions of the truth and conflicts between the data. And it was sometimes hard to tell which version of the data was correct and which was not.
It didn't stop there. Even once the data had been gathered and presented, making changes "on the fly" was slow and difficult. In fact, whenever a member of the Macfarlan Smith team wanted to perform trend or other analysis, the changes to the data needed to be approved. The end result was that the data were frequently out of date by the time they were used for decision making.
Liam Mills, Head of Continuous Improvement at Macfarlan Smith highlights a typical reporting scenario:

Questions for Case
1. What were the data and reporting related challenges that Macfarlan Smith faced?
2. What were the solution and the obtained results/ benefits?