Case Study

Top Dairy Co-op Improves Forecasting and Increases Profits

The Customer’s Challenge

A top U.S. agribusiness and dairy company found themselves heavily dependent on an Excel-based forecasting simulation to optimize the profit margins on their product. However, maintaining and scaling such a complex tool built within a single, massive spreadsheet had become infeasible — putting their entire business model at risk.

phData’s Solution

phData helped rewrite the convoluted spreadsheet-based Monte Carlo simulation as a simple, easy-to-use Python application — thoroughly validating the reconstructed solution, enabling all-new forecasting capabilities, and dramatically improving performance and automatability — so the agribusiness can sustainably maximize margins and growth.

The Full Story

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.

Milking the product for all it’s worth

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:

Knowhow and grit: a recipe for success

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.

The benefits of a job well-done

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.

Business Outcomes:

Chart for Food and Agribusiness Case Study phData

Take the next step
with phData.

Learn how phData can help solve your most challenging data analytics and machine learning problems.

Data Coach is our premium analytics training program with one-on-one coaching from renowned experts.

Accelerate and automate your data projects with the phData Toolkit