Inventory Valuation Report

Discover how Amiad Water Systems, a global producer of water filtration products, lowered their Inventory Value by monitoring & forecasting inventory levels with a tailored report in a day.

Inventory valuation is the cost associated with an entity’s inventory at the end of a reporting period. It forms a key part of the cost of goods sold calculation, and can also be used as collateral for loans. This valuation appears as a current asset on the entity’s balance sheet.

Amiad Water Systems Ltd. (‘Amiad’), a global leader in water treatment and filtration solutions, made a strategic decision to reduce inventory cost worldwide.  Although inventory is managed within SAP ERP, it was challenging to produce an updated operational report, which reflected real-time data and 6-month forecast of inventory value, Globally, by site (Amiad operates 8 sites worldwide) and by material item.

A one-time one-day work effort with RainbowSAP replaced a 5-day labor needed whenever a new report was required. As a result, the execution of the corporate guidance was made easier and more effective, the agility of the mid-level managers to make the right decisions based on the accurate information has dramatically increased, and the company’s inventory forecasting unlocked saving potentials as a result of adjusting planning of replenishment orders when they seemed to surpass the defined thresholds.

PAIN POINT: 

Amiad launched various projects aimed at reducing the inventory levels, but the ability to monitor the outcomes of the projects, given the availability of 135,000 material items across 8 different sites worldwide, has been challenging. The company wanted to identify both material items that presently exceed the desired thresholds, as well as to identify in advance material items that are forecasted to exceed thresholds in the near future. Identification of the latter could trigger preventive measures, such as reduction of stock replenishment.

So far, In order to produce a Global Inventory Valuation monitoring report, users needed to download to a spreadsheet data from 4 different tables & apply very complex formulas. Since there are 135,000 material numbers across 8 different sites, the running time when creating such a report was very long. Each one of the 8 sites required nearly a day to create a single inventory valuation report – about 5 working days for all 8 sites combined. The outcome (an excel spreadsheet) was extremely heavy and opening the file was tedious. As a result, as much as necessary the Inventory Valuation Report was, it was not produced regularly, but every month to three months. Even then, the file size required the users to split data and any additional analysis or modification in the structure of the report translated into further investments. Hence, the Operational Managers were semi-blind when trying to execute upon the corporate guidance related to the reduction of Inventory Value.

ACTION: 

The Company has taken a Z-Report that is using SAP MRP data (based on a Logical Database DBM), and uploaded it to RainbowSAP as the baseline report. This table displays MRP data such as inventory levels at a specified moment, movement inventory data (move-in, move-out) and other relevant inventory control data.

Having the Z-Report as a baseline, thanks to RainbowSAP, various Data fields from SAP MARC Plant Data (MARC table – ‘Material Connection Disclosure’) and from SAP MARA (‘General Material Data’) were added as new additional columns within minutes to the Z-Report baseline. Other columns were added as well to the report from SAP MB52 table (Display Warehouse Stocks).

Once the structure of the data was combined into one single view, as described above, the data was organized, within RainbowSAP, by Material Item (by Site) and by Month (6-rolling months forward). The data was aggregated by Material Number, and displayed as the summary of inventory level and value by material item (with drill down to the granular Material item’s movement information).

At the next step, in accordance with the inventory level policy, the company calculated the desired safety stock level and maximum stock level by Material Item. As a result, each material item had a clear acceptable inventory level range. Thanks to the ‘Stock Value per Unit’ column (added to the report with RainbowSAP), a simple formula calculation within Rainbow has now provided the customer minimum and maximum stock value per Material Item, and consequently the Acceptable Stock Value Range per Material Item.

Next, the aggregated monthly data (displayed on 6-rolling months forward forecast), was color-coded with formulas through RainbowSAP’s Conditional Formatting capabilities, as following:

    • GREEN: Inventory value within the acceptable range
    • ORANGE: Inventory value consistently outside the acceptable inventory range
    • RED: Anticipated changes of inventory slipped outside the acceptable range
    • YELLOW: Inventory Value in a risky position with respect to the acceptable range

At the following illustration, a sample of the Material Items associated with site #1300 (one of Amiad’s 8 sites), specifies the movement list accessible through the ‘Records’ column (far left column):

Finally, by using RainbowSAP’s Suite Publisher Module, Amiad will send automated email alerts to the Inventory Manager at each site. The Inventory manager will receive notification emails, with all necessary details, outlining the problematic Material Items for his attention. From within the email, the Inventory Manager could drill down to the material item data on the transactional level, subject to his SAP authorization clearance. In addition, any Inventory Manager at any site could now run the Inventory Valuation Report at any time in order to further analyze the data and for any other purpose.

A second Publisher Job will consolidate top issues within each one of the sites, all displayed on the same pre-scheduled email, sent regularly to Amiad’s COO and Global Head of Inventory. This will enable them to take actions as they will have a global and by site views – as well as to report progress related to Company’s Guidance of lowering the Inventory Value. 

RESULT: 

From a process that lasted over 5 days of manual labor, delivered outdated information (as data was exported to a spreadsheet), and with various limitations as a result of Big Data and other technical matters – Amiad’s managers could move from a defensive to a proactive mode. The Company has visibility on current and forecasted Inventory stock levels and costs on a global level, site level and by Material item and month – enabling various decision-makers and operational managers to take corrective measures and to measure impact of Inventory Value reduction programs. 

All of the above was made available within a one-time one-day of work effort, without development, which is equivalent to the time invested previously per the production of a less-capable report of one single sight. 

Furthermore, few end-users were technically capable of creating the Inventory Valuation report as opposed to today – that each person with the right SAP authorization level could address his personalized needs by a single mouse-click.

Before using RainbowSAP, which enables the creation of this report without development and On Production (hence, eliminates the need to put in place a development requirement document and then pursue an ABAP development path), it was alternatively planned to execute the Inventory Valuation Report through standard ABAP development – a painful, long, tedious and invested process that would have ended up with a Z-Report, that amongst other matters is ultimately unsupported by SAP.