Executive Summary
Inventory forecasting presents significant challenges due to a multitude of factors. These include allowances calculations within ERP systems, the absence of a consolidated inventory view (so-called one source of truth), internal policy adjustments, and the challenges involved in developing and assessing slow-moving inventory and working capital what-if scenarios.
These challenges lead to issues such as excess inventory, low inventory turns, and elevated working capital deployment. Such problems have direct, negative impacts on the profit and loss statement (P&L) and cash flow.
Inventory Calculations are Opaque
Companies implement inventory valuation and allowances calculations in the ERP, but calculations in the ERP system are usually opaque and poorly documented, many times those who implemented the logic for calculating provision for obsolescence have long left the company.
Opaque and rigid calculations result in Finance losing confidence in the data over time, and needing to reverse engineer logic in spreadsheets. This is particularly cumbersome as inventory finances around costing, excess inventory, aging and allowances are quite data-heavy and can take up significant finance capacity to manage.
Hard to Do a What-if Analysis to Evaluate Slow-Moving Inventory and Working Capital Scenarios
A problem with slow moving inventory can be resolved in various ways: from scrapping, to reworking, to sales discounts. These options are all valid, and normally companies will go for a mix of activities, but the challenge is simulating the P&L and Balance Sheet impact via what-if scenarios.
Problems with high working capital deployments due to low inventory turns are even more complex than slow moving. Low inventory turns are usually an indicator of poor S&OP, which tends to be followed by poor visibility of inventory processes.
Slow moving and working capital what-if scenarios are hard to build due to lack of granular data, and even when granular data is available, it is hard to build them due to the size or the quality of the data.
One Source of Truth of Inventory is Hard to Build Due to Data Spread Across Domains and Multiple ERPs
Forecasting requires combining data from multiple sources in order to get an accurate picture of the future inventory levels and allowances. However, the required data is not centralized in one place, and it is spread across domains such as Sales, Marketing, Production and Purchasing, and thus in many systems such as ERPs, CRMs, CDPs, Excel, SharePoint, and more.
And sometimes even data on the same topic, such as inventory entries or aging, is spread across multiple ERPs. This is a situation typical of companies with healthy M&A activities.
Calculation Logic Changes Take Years to Be Implemented
Policy changes impact the way inventory allowances have to be calculated by finance. The inventory allowances logic can take years to be correctly implemented in the ERP. This leaves the Finance department with a gap of several years, with the only option being manual calculations, integrations, and data wrangling in spreadsheets.
While this can be a short-term solution, it becomes a serious problem over time due to talent shortages, reduced Finance capacity, lack of backups, and manual errors.
Why it Matters
On the P&L side, profitability is adversely impacted by the need to book a Net Realizable Value allowance (writing-down) and later on by selling below cost or even scrapping (and writing-off) your stock.
On the Balance Sheet side, excess inventory means blocking resources in working capital which could have much better use. In other words, not being able to control and prevent excess inventory has a direct negative impact in terms of cash flow.
Countermeasures
If your ERP is doing a good job at calculating current inventory value and allowances reserves, it is OK to keep using it, but forecasting and simulations should be moved outside and a one source of truth should be put together for Finance. That way finance can efficiently simulate scenarios.
When building the one source of truth, everything should be consolidated in well architected datasets, which allows the finance teams to control all variables and easily build models and simulate scenarios.
FAQ
What is P&L forecast?
A P&L (Profit and Loss) forecast is a financial projection that estimates a company’s future revenue, expenses, and profitability over a specific period. It involves analyzing historical financial data, projecting sales, estimating costs, and factoring in external elements like market conditions. This process includes estimating future revenue, cost of goods sold, operating expenses, and other variables that impact the company’s bottom line. P&L forecast helps businesses anticipate financial outcomes, assess profitability, and make informed decisions.
How to forecast P&L?
To perform a P&L forecast:
- Review historical financial performance.
- Estimate future sales and revenue trends.
- Project operating costs, overhead, and variable expenses.
- Include potential changes like product launches or policy adjustments.
- Factor in external risks like market shifts, competition, and economic conditions.
How to forecast inventory on a balance sheet?
Forecasting inventory on the balance sheet requires understanding past inventory turnover rates, sales trends, and production schedules. It also involves projecting future inventory purchases and allowances for obsolescence or slow-moving stock, ensuring an accurate reflection of working capital.
What are the main challenges of inventory forecasting?
Inventory forecasting is difficult due to opaque ERP calculations, lack of a centralized inventory view, policy changes, and challenges in evaluating slow-moving inventory and working capital scenarios. These factors complicate accurate forecasting and scenario planning.
How do inventory forecasting issues impact financial performance?
Poor inventory management leads to excess stock, low inventory turnover, and high working capital deployment. This directly affects the profit and loss (P&L) statement through write-downs and write-offs, while excess inventory ties up resources and harms cash flow.
What is a "one source of truth" for inventory, and why is it necessary?
A “one source of truth” consolidates all inventory data into a single, reliable system. Achieving this is tough because inventory data is scattered across different departments and systems, making it hard to have a unified view for forecasting and decision-making.
How do policy changes complicate inventory calculations?
Changes in company policies often require updates to inventory calculation logic in ERP systems, which can take years to implement. This forces Finance teams to rely on manual calculations, increasing the risk of errors and inefficiencies.
