Summary: A Fortune 500 pharmaceutical company headquartered in New Jersey, United States demonstrated the impact of strategic data transformation to better prepare for the future. Their solution utilizes the capabilities of Microsoft Azure, Power BI, and ValQ to harness the potential of their data to perform predictive financial modeling and planning to inform improved business outcomes.
Watch this video to hear the finance director from the Fortune 500 pharmaceutical company present his journey with using Power BI for planning.
Fortune 500 pharmaceutical company uses Power BI to solve planning challenge
The US-based Fortune 500 pharmaceutical company faced challenges with fragmented data that involved sorting through multiple data sources (i.e., Excel, flat file, SAP, and other cloud-based systems) engaged with a disjointed toolset. These barriers prevented access to critical information which made it increasingly difficult to perform the economic modeling and evaluation along with metrics calculations.
The finance director for supply chain management within the company was looking for a way to provide much deeper end-to-end analysis of the demand/delivery cycle to understand cost drivers and other performance metrics. This solution would enable insight into the actionable areas of the business that are driving the economics and therefore the finances reported in their financial results.
Ultimately, their solution needed to unify a significant amount of disparate data in a coherent manner, that would allow them to perform predictive modeling to understand financial implications of certain actions prior to execution.
Power BI’s low-code reporting visualization was suitable for the company’s finance director to begin work on the proof of concept. Because of its flexibility, security, and functionality it would also scale well further into implementation. Also, Power BI’s data modeling and data source connection capabilities made it a no-brainer tool for their solution. Most importantly, Power BI supported the planning capabilities the director needed through the partnership with Visual BI, and their ValQ application, which delivers forecasting, simulations, and performance analysis.
The platform behind the data
The platform the company is leveraging for POC—which took 10 days to implement—takes all source data and moves it to the Azure environment. Source data is loaded via Azure Data Factory into Azure Data Lake Storage Gen2 for storage. From there data transformation is processed via Azure Databricks and Azure Machine Learning in two stages.
Stage one involves the necessary steps to make the data fit for purpose, which are to:
- Clean and purge
- Organize and structure
- Establish relationships
- Apply business logic
- Model to support analysis
The second stage builds on this through leveraging the data platform functions that:
- Automate tasks
- Generate analytics and insights
- Detect anomalies
- Simulate model changes
- Predict
This data manipulation and transformation is the key to the entire application development process. The data serving and interaction layer leverages Microsoft Power Platform whereby the solution uses Power BI to present the data and Power Apps to allow user interaction with the data. Whether a user pulls data in from the Power BI dashboards or Excel, the information will be the same because of the existing data transformation process.
The next key element in the company’s solution is the integration of Visual BI’s planning tool, ValQ. The simulation capability begins with a demand signal while volume assumptions flow downward to trigger each process.
Visualization of the company’s simulation model structured around demand signals which result in production-focused forecasting.
The Fortune 500 company’s integrated planning process starts with a demand signal. This signal feeds the supply network plan—how a company produces materials to meet the overall demand. The next step is to incorporate the financial supply chain, which tracks the cost of physically transporting products in and out of various markets. The final step in the process is managing inventory and determining distribution logistics. Each step in this process is a driver of financial outcomes (see image 2. left swim lane).
The financial outcomes lead to financial assumptions (see image 2. horizontal swim lanes) that are applied to each process step to arrive at the various forecasts and analyses (see image 2. right swim lane).
The pharmaceutical company spends significant time and obtains a large amount of utility from the Power BI platform in the area focused on production management. Power BI helps them to understand various aspects of production management such as the timing and scheduling of the events, repackaging counts, runtime versus setup time, and switch over time on the factory line or floor. The knowledge of how these aspects influence required resources is used to determine how to optimize the output over time, and thereby become cost efficient. By adjusting the various inputs in Power BI, the pharmaceutical company can understand how each decision will impact the overall outcome which then automatically realigns planning.
The overall planning system solution utilizes Power BI and ValQ to treat all financial assumptions as levers that are adjustable to reveal deep insights into how each variation will impact the overall supply chain outcome. In this way, they simulate and understand how pulling one lever will financially impact every other financial factor which then automatically plans each stage of the supply chain process.
End to end production cost visibility
Once the demand signal and what needs to be produced has been established, the finance director looks to the production requirements and associated costs. With Power BI dashboards, the company is able to see end-to-end analysis reports with insight into every step in the manufacturing process, where they occur, and what the cost structure is at each step along the way.
With all of these elements calculated and displayed in a composition tree, the user sees an accumulated cost for the material to be sold in the Chain Cost Analysis dashboard. The decomposition tree capability makes simple and easy what would be an unwieldy amount of data to navigate through. Each bar within the report demonstrates the cost drivers or the or the cost impact of each step in the manufacturing process on a per unit basis, which allows for end-to-end visibility over a complex process.
Now, cost drivers and accumulated costs to be quickly and easily identified within the larger scope of the production process so that the company can focus on opportunities for improvement. As key areas are flagged for improvement within the multi-step production process, the user can access a deeper Step Cost Analysis report within the Power BI dashboard, which gives an embedded cost overview.
This enables analysis of the costs on the manufacturing floor—including labor, machinery usage—and how it translates into cost-per-unit. The user can then identify throughput; how many units per hour the given manufacturer is able to produce, at what cost.
Within the Step Cost Analysis view, users access overhead cost details on a product-specific basis, which is critical for a large company that produces a large variety of products with variable production requirements.
The next level of analysis involves the material itself needed to perform the production process and to obtain the IDs of those materials, determine the quantity, and overall cost of materials.
The solution then looks across the business portfolio materials for any given manufacturing site to understand what the different contentions for resources are, what products go through that site, and the overall cost of anything that specific plant touches. Ultimately, this delivers a holistic view of the manufacturing process rather than just one product or one production location.
Modeling, simulating, and forecasting with confidence
The company’s Power BI reporting paired with ValQ ‘s embedded predictive modeling capabilities enables economic assumptions to be woven into the model to arrive at what is the final cost per unit of a particular portfolio of products.
The simulation model starts with demand, filters down to supply, and then the spending to support the production processes is captured, which will end up resulting in the final cost. A critical feature of ValQ is the ability to link all these relationships together within the model.
For instance, if a user elects to scale demand up or down by geographic region, the model will automatically update the supply requirements in the network that supplies that specific region.
Within this simulation, the model calculates all the implications to demand and provides supply requirements in order to meet the demand modeled. It then updates all supply by stage and by plant automatically match the required supply to the group of plants in the network that would support that region’s demand.
Company’s simulation and planning solution dashboard which combines Power BI and ValQ capabilities.
Simulation and planning capabilities allow users like the finance director to manipulate a massive amount of critical data with ease to drill down to the individual cost components that are associated to it and that drive it, and then model changes there.
Effectively it starts with volume, the volume drives the cost, and the costs have their own independent variables that can be adjusted as well, to model the financial impact of a particular decision.
Planning with certainty and saving time
By applying technology to business processes, the pharmaceutical company has experienced the elimination of redundancy of data and redundant process-based work. This has ultimately saved a significant amount of time and effort, but also allows them to do more thorough analysis and provide quick feedback as to the impact of that analysis.
The solution’s unique combination of Power BI and ValQ enables planning with certainty for the Fortune 500 pharmaceutical company based on a variety of economic assumptions.
Their simulation and planning tool also provides line-of-sight into the downstream economic impact of changes across organizations, which has reduced bias through enabling data-based decision-making and evaluations.
To read more Power BI customer stories like this one visit our Microsoft customer stories portal.