Data-Driven Profit Projection: Exploring Predictive Margin Analysis using Random Forests

Introduction:

In our previous article (Transforming Sales Volume Projection in the Specialty Chemical Industry: Harnessing the Potential of Machine Learning Predictive Modeling), we explored application of machine learning to forecast sales volumes, emphasizing the importance of margins in understanding profitability. In this article, we introduce a predictive margin analysis tool and showcase how data analytics and visualization can aid margin analysis. This tool, initially built using CTL Inc.'s year 1 sales data, can be adapted for any organization with good quality time series sales data.

Margin Forecasting at a Glance: CTL's Interactive Visualization Dashboard:

We've created and deployed a dashboard to visualize average margin predictions for CTL’s 69 products. A dynamic interactive application of the dashboard tool can be accessed at https://asharma2023.pythonanywhere.com/

Note that the predictions are accounting for the average volume sold over the year, and average production cost incurred in manufacturing these products.? This full-stack development utilized Python's Flask application in conjunction with HTML and JavaScript. The dashboard is presented as a webpage, as illustrated in Figure 1 below.

In Figure 1, users can select a product from the top-left dropdown menu. Other input values can be entered in the fields on the left side. Users are expected to provide relevant magnitude-based values for each field. A "Predict Margin" button is located at the bottom left. Upon entering all input values and clicking the button, the "Results" are displayed on the top right, and a bar chart emerges in the center. This chart features input values represented as blue bars, while the average predicted margin appears as a green bar. If the average predicted margin falls below the threshold value (15% in this case), the predicted margin bar turns red. An example scenario for product K-25 is shown in Figure 2, where input values and predicted margin are displayed.

The primary objective is to assist CTL's leadership in visualizing the impact of changes in production costs or pricing on specific product’s average margins. Average margin predictions are based on historical data, assuming other factors remain constant. However, variables such as shifts in order patterns or abrupt changes in raw material, labor, and energy costs are not factored into the model.

Using Random Forests for Margin Prediction:

We developed this tool using CTL Inc.'s annual sales data and the Random Forests machine learning algorithm. The tool predicts average margins for various products, offering a valuable insight into margin performance

A random forest consists of a group (an ensemble) of individual decision trees. Therefore, the technique is called Ensemble Learning. A large group of uncorrelated decision trees can produce more accurate and stable results than any of individual decision trees.

Insights from the Data Landscape

Before delving into comprehensive data analysis, we must highlight significant observations from the sales data. Notably, the top 10 products contribute around 63% of the total margin. With 69 distinct products across three lines, roughly 15% of these products generate a substantial 63% of the margin, leaving the remaining 85% to contribute a balance of 37% of the total margin. This insight prompts CTL Inc. to consider strategies like product rationalization or price adjustments to address the margin imbalance potentially caused by manufacturing costs or low pricing.

Model Effectiveness

The performance metrics of the random forests machine learning algorithm indicate its effectiveness in predicting average sales margin by product. Overall, the model achieved an R-squared value of 0.86, indicating a good correlation between the predicted and actual values of the test data. We used the model to predict the entire average margin data by product for year 1.? We then plotted the actual average margin versus predicted average margin and fitted the data to a line.? The linear fit gave a good R-squared value of 0.96 indicating that the model can explain 96% of the variance between actual and predicted sales volume values.

Unveiling Insights Through Data Visualization: A Deeper Look

In our exploration of predictive modeling via Random Forests, we delved into the realm of data visualization, aiming to extract invaluable insights. This visual journey is encapsulated in our key takeaways, drawing us deeper into the intricacies of the data.

Top 10 products delivering actual and predicted average margin

Central to our analysis were the top 10 products, where we sought to bridge the gap between actual and predicted average margins. With the aid of Tableau, we crafted a visual representation that presents a clear picture of these top 10 products' performance, captured in Figure 4 below.

In this visual landscape, the alignment between the actual and predicted average margins emerges as a striking feature. Collectively these top performers seem to transcend predictions, embodying the power of the models in play. Notably, the synergy is almost seamless, reinforcing the reliability of our predictive endeavors.

However, a nuanced detail emerges—amidst the concordance, an exception arises. The actual top ten margin product list is juxtaposed with the predicted counterpart, unveiling an intriguing replacement. CP-2, in the actual list, has ceded its position to K-25 in the predicted realm. This gentle anomaly speaks to the intricacies of forecasting, where the crystal ball occasionally unveils a surprise.

Furthermore, the dominance of product line K within the top 10 products' margin perspective is unmistakable, contributing to over 75% of the collective margin. This visual revelation beckons CTL's management team to action, instigating contemplation on the diversification of the product mix. The scene is set for the exploration of new avenues, a strategic approach to balance the scales and mitigate over-reliance on a singular product line.

In this holistic view, Tableau proves its mettle as more than just a visualization tool—it becomes the vessel that guides decision-makers toward data-informed solutions and strategic evolution.

Comparing Actual and Predicted Margins Across Product Lines

Figure 5 illustrates a comprehensive view of total margins attributed to each of the three distinct product lines. Notably, these lines vary significantly in terms of total sales, leading to differing contributions to the overall margin landscape. This variance prompts us to delve into margins normalized by total quantity (Metric Tons), a lens through which we can discern the true champions in performance.

However, it's in Figure 6 where the narrative takes an intriguing twist, focusing on the top 10 products when viewed through the prism of margin per unit (margin/mT) and predicted margin per unit (predicted margin/mT).

Within this fresh perspective, a nuanced story unfolds. The customary hierarchy of top performers undergoes a transformation. Now, the spotlight shifts to product line A, emerging as the frontrunner in normalized margin performance. A staggering revelation—nearly 60% of the top 10 margin/mT contributors hail from product line A. Compellingly, product line C secures its significance by contributing approximately 35% of the total top margin through a singular product.

Strikingly, a mere 5% of the margin/mT tapestry stems from six other products. Equally surprising is the subdued representation of CLT’s largest product line, K, which remains conspicuously absent from this forefront. This visualization casts a spotlight on areas that demand CLT’s attention for performance enhancement.

This journey through margins normalized by quantity transcends mere numbers—it carves a path toward actionable insights. As the distribution of top performers unveils its uneven facets, CLT’s strategic compass is primed for recalibration. Guided by this revelatory visualization, the course of action becomes clearer. The mission: to cultivate a more balanced landscape.

Exploring the Bottom 10 Products: Actual vs. Predicted Average and Margin/mT Analysis"

Continuing our investigation beyond the top performers, we turn our focus to the bottom 10 products, unraveling their average actual and predicted margins. Employing Tableau’s dynamic bubble charts, we present these findings in Figures 7 and 8.

In Figure 7, an intriguing pattern emerges—the bottom 10 products by average margin remain consistent across both actual and predicted perspectives. A striking revelation surfaces—over 60% of the diminished margins within this group originate from product line A. This phenomenon could potentially be attributed to the sales volume stemming from this specific product line. Consequently, delving into margin per unit (margin/mT) rather than total average margin could unveil a more authentic assessment of underperforming products.

Shifting our gaze to the bottom 10 products by margin/mT and predicted margin/mT, we further dissect the data in Figure 8.

Figure 8 unveils a significant trend—product line K dominates the landscape of the bottom 10 products when evaluated based on margin per unit and predicted margin per unit. While there is a modest presence of product lines A and C (constituting less than 10%) within the actual margin/mT data, product line A's representation is entirely absent in the realm of predicted margins, with product line C making a minimal appearance (less than 5%).

This stark disparity paints a different portrait— one where CLT's largest product line, K, is no longer at the helm of margin per unit delivery. Given this product line’s substantial utilization of business resources, an imperative emerges—to enhance both product pricing and manufacturing cost on a per unit basis. Simultaneously, nurturing and optimizing the product lines that demonstrate commendable margins per unit becomes paramount.

In sum, this in-depth analysis not only highlights the significance of evaluating margins on a per unit basis but also charts a course toward strategic interventions that align with CLT's mission for sustained growth and profitability.

Unveiling the Power of Data Analytics and Visualization for Margin Analysis

Within this comprehensive article, we've embarked on an illuminating journey that underscores the prowess of data analytics and visualization techniques in the realm of margin analysis. Our exploration began with the introduction of a cutting-edge user chatbot-driven predictive modeling and data visualization tool, the remarkable outcome of a full-stack development endeavor utilizing Python's Flask module, HTML, and JavaScript.

Predictive Modeling with Chatbot Interface:

The focal point of our journey was the utilization of the Random Forests machine learning algorithm—an agile and versatile tool. Through rigorous analysis, we harnessed this algorithm to craft a predictive model, meticulously tailored for margin prediction. The foundation of our endeavor lies in enhancing user understanding. By enabling dynamic insights into the impacts of altering variables—such as price, cost, and quantity—on margin, we offered invaluable visibility and control.

Insights with Tableau:

But our journey didn't stop there. With the outstanding visualization capabilities of Tableau, we delved into the heart of the matter—unearthing the top and bottom performers from a margin perspective. This dual-pronged approach examined both the overall margin landscape and the granular margin per unit perspective. Our visualizations identified pivotal insights, spotlighting not only the products and product lines ripe for optimization but also illuminating avenues for maximizing margins—be it through meticulous product rationalization or strategic pricing initiatives.

A Resounding Conclusion:

In summation, this exploration stands as a testament to the remarkable synergy between data analytics and visualization. It transcends mere analysis to empower proactive decision-making. As we conclude this journey, the strength and significance of our work resound clearly—with great insights, harmonizing data-driven analysis, and the art of visualization, reshaping the landscape of margin analysis.

Acknowledgments: I would like to express my sincere appreciation to Mr. Jeff Wolff and Dr. Hassan Rmaile for their invaluable support, insightful suggestions, and contributions to the development of this article.