Fortune 500 BioTech and Pharma Company optimizes its demand planning process using Snowflake

A Fortune 500 BioTechnology and Pharmaceutical company had its manufacturing and supply chain set up in many locations in North America, Europe, and Asia Pacific. Materials were manufactured in Europe or Asia Pacific and delivered to customers in North America and vice versa through a transit network involving multiple plants.

The demand planning process translates the customer-centric forecast from the point of revenue recognition back to the production site(s) to provide the plants with the visibility necessary to plan for raw material requirements, manufacturing line capacity, and staffing necessary to meet customer needs. 

This complex supply chain was completely running manually in Microsoft Excel via complex mathematical formulas. Running these manually through multiple plants across different geographical regions in the supply chain required recursive calculations which made the process error-prone, human-dependent, and extremely difficult to scale. Once the source data was available in Snowflake, they asked phData to rebuild this solution in Snowflake using native Snowflake features like Stored Procedures, Functions, etc.

The phData DE team started the initiative by doing research into deciphering the core logic behind the complex calculations and incorporating business feedback into what changes would be required to make the process a scalable one. The source datasets from SAP systems involved monthly forecasts, current inventory, and mapping between various materials and their plant attributes, such as minimum order quantity (MOQ), rounding value (RV), reorder point (ROP), etc. 

The team then translated the complex mathematical formulas using the attributes mentioned above into SQL UDF (User Defined Function) and mapped the recursive workflow from the current month to the next 24 months using Recursive CTEs in Snowflake. The same process was then applied to all the plants in the network, and all the logic was wrapped into a SQL Stored Procedure. The duration of the forecast (24 months) was kept as a parameter for the Stored Procedure so that it could be scaled easily.

The final result on each execution was a total of 120 intermediate-derived facts (replenishment/demand quantities for each month for each plant in the network) and three final derived facts (replenishment/demand plan for the production plant) for the current forecasting month, which the analytics team consumed for each material in the supply chain.