The COVID-19 pandemic led to a lot of unexpected changes in 2020 and many have continued into 2021. Suddenly with many people working from home, the demand for oil fell significantly while home renovations saw a surge, increasing the prices of building materials. At one point, the price of oil was negative – meaning buyers were paying suppliers so they would not have to take possession of the oil. This had a downstream impact on oil byproducts such as plastic resin and gasoline, causing prices to plummet. Lumber on the other hand has had prices increase over 300% in the last year.
These price fluctuations can have a massive impact on your profitability, working capital, and financial statements. They also raise a lot questions and trigger decisions which need to be made – if you are operating an oil refinery that produces gasoline and your warehouses are already full of oil, do you keep producing, potentially lowering the price of gasoline further, or like in 2020, do you pay the oil suppliers so you don’t have to take possession of the oil? Similarly, if you run a recycling center and the price of resin has plummeted, will you be able to recover your operating costs by recycling plastic bottles? On the other side, say you run a furniture manufacturing business – with the price of lumber increasing, do you pass those costs on to your customer? What impact does the lumber price increase have on my working capital? 2020 presented organizations with unprecedented volatility in commodity prices for which most were unprepared. Their established business models and reporting processes were simply unable to deliver the timely and accurate information required to operate in uncharted territory.
For any business, it is critical to have insights into your costs and inventory valuations. With the accelerating pace of change in today’s environment it is even more critical that these insights be real-time so you can make the most informed decisions for your business. It is critical that changes to the demand plan are shared instantly along the supply chain. Similarly, if your products rely on an underlying commodity to produce, then gaining access to real time commodity costs can make all the difference of making decisions based on production levels and pricing.
One of our customers, (the supply chain finance department) of a large multi-national CPG company, encountered a challenge to understand how underlying price changes impacted their organization and supply chain. The supply chain finance team was responsible for generating the cost forecast each month that ultimately rolled into publicly reported financial statements. Many of the cost forecasts were dependent on underlying commodities, which often fluctuated. The existing process to generate the forecasts was not standardized, with various excel models owned by multiple individuals. In addition, the company’s supply plan was not integrated into the cost forecast, with the team often using last year’s forecast as an assumption for this year’s demand. With no integration to the supply plan, there was no insight into changes in consumer demand as a result of the COVID-19 pandemic. In addition, the models did not use real-time commodity costs, often only updating the costs on a quarterly basis, this led to significantly over and under-inflated cost forecasts.
Our team was brought in to assist in the digital transformation of this process. The goal was to standardize and digitize the existing process and provide real-time visibility into the cost forecasts. Alteryx was used as the primary data aggregation and ETL (extract, transform, load) tool. Various data sources were brought into Alteryx including the supply plan, commodity cost data, shipping history and more. The financial calculations were performed in PowerBI, which also served as the primary reporting interface for users. Drill-down reporting was developed so users could better understand cost variances and how changes to the supply plan impacted costs and inventory levels. The resulting tool improved accuracy of the forecasts, and provided transparency to the process, giving downstream customers confidence in the forecast. Now, our client can make inventory, production, and pricing decisions with up-to-date, real time information that uniquely positions them to respond to market volatilities and outmaneuver their competition.
Understanding how cost fluctuations impact your financials and your supply chain is not easy and there are many moving pieces, but Thought Logic can help. Our team consists of top consultants across the domains of supply chain, finance and analytics that we bring together to solve your most complex problems. If you have any questions on the article or would like to learn more please reach out to our Supply Chain practice lead, Mike Kerr, at firstname.lastname@example.org