For a leading U.S. agribusiness and dairy company ($15BN+), the ability to make data-driven decisions provides a critical survival advantage. Their business model depends on squeezing the maximum-possible profit out of every drop of milk their members produce. Now, with growing competition from alternative dairy products, the importance of optimizing their tight profit margins is greater than ever.
To determine how much product should be contracted for sale as milk vs. retained for sale as byproducts like yogurt or powdered milk, the agribusiness company had been relying on a Monte Carlo simulation tool. However, the simulation — which was, by necessity, extremely complex, weighing an enormous range of inputs, potential risks, and degrees of randomness — was built entirely in Excel, as a single, gargantuan spreadsheet.
Not only was it difficult to use; it was a nightmare to maintain. The agribusiness company knew they needed a more sustainable, performant, and scalable solution to help them sell their product more profitably.
Overhauling such an involved simulation was clearly going to require a herculean effort — especially given the fundamental complexities of the business problem at-hand.
The simulation’s purpose was to predict just the right volume of milk that the agribusiness company should contract to sell in order to maximize their overall profit margins. Contract too much milk, and they’d be left with insufficient stock to produce their most profitable byproducts; but contract too little, and they’d have to offload more volume into increasingly less-profitable byproducts — or in a worst-case scenario, to dump unsold milk.
To find that sweet spot, the simulation has to account for an enormous spectrum of known and unknown factors. These include:
And that’s just scratching the surface.
Risk-based simulation model to maximize Milk and butter/powder complex margins through milk volume optimization.
Naturally, a simulation with such a large range of dependent inputs and variables, was incredibly difficult to manage as a spreadsheet:
The agribusiness company’s technical leaders ultimately decided to rebuild their unwieldy, spreadsheet-based Monte Carlo simulation as a simple, easy-to-use Python application; however, their own data science team was already overloaded and it takes them a considerable time to hire new talent. So, looking for a partner they could trust to tackle such a massive, complex project — and to tackle it fast — they turned to the phData Data Science team.
And the team delivered: from hypothesis analysis to input simplification and solution validation, they rewrote and implemented the model in Python, then worked tirelessly to ensure the reconstructed simulation performed as expected.
To accurately reconstruct the solution, the phData team had to dive deep into the original Excel-based solution — churning through countless columns and individual cells to account for all the hidden dependencies, all the undocumented changes, and all the many inconsistencies in data structure, even within individual columns — as well as continually revising their Python code as they wrote it to keep up with a business that was rapidly evolving while the project was still in-flight.
However, because they came equipped with both the data science expertise to understand what made the original simulation tick and the grit and dedication to help the customer succeed — at one point, during the validation phase, spending an entire weekend to track down a single discrepancy amidst the thousands of spreadsheet cells — the phData team got the job done, and got it done fast.
With their new-and-improved Monte Carlo simulation — lately reborn as a much easier-to-maintain Python application — the agribusiness company now has a much more sustainable foundation to help them maximize the overall profit margins on their product.