Berling Associates Using Supply and Demand Data For Better Inventory Management

1. Berling Associates Using Supply and Demand Data For Better Inventory Management An Illustrative Inventory Management Solution

2. Introduction To Our Illustrative Inventory Management Solution • Our inventory management views provide a number a “paths” into product supply and demand data. • There are three major categories of views in our illustrative Inventory Management Solution: supply and demand flow over time, inventory on-hand, and inventory performance. • In the following slides, we will look at illustrative views within each of these groups and how the views are used to manage product supply and demand. • The goal in the design of the illustrative views and in our supporting development effort is to provide inventory managers, product planners and purchasing personnel instant access to information on every dynamic aspect of inventory. This is accomplished with the ease of a click of a mouse, eliminating the need to rely on others to write long and complex queries and eliminating the learning curve associated with using business intelligence tools. Our focus is on providing quick access to the data they need to perform their inventory management activities.

3. Illustrative Inventory Management Solution • Inventory Management Views • Supply and Demand Flow Over Time • Historic Supply and Demand View • Future Supply and Demand View • Inventory On-Hand • Inventory Overview Dollars View • Weeks of Inventory On-Hand View • Aging Dollars View • Aging Quantities View • Inventory Performance • Turnover View • Turn & Earn View

4. Supply And Demand Flow Over Time - Historic Supply And Demand View Overview • Our illustrative views for inventory management offer two views into supply and demand flow over time, a historic view and a future view. The historic view provides a view back into time. (Any number of weeks can be presented in the view.) • The inventory quantity on-hand is rolled back through the application of supply (receipts) and demand (sales) data with recognition of adjustments to product receipts, sales, and on-hand quantities in the system files. This view gives one, at a glance, a sense of the “balance” in the product flow during recent history. This helps to explain why the company may be in a particular inventory position. This also yields planning lessons for the future. • All of the views within our inventory management solution are “drillable”, with the point and a click of a mouse, down through the product hierarchy to the item or SKU level. This feature provides flexibility and usability for those with big picture needs and for those with more detail level needs.

5. Supply And Demand Flow Over Time - Historic Supply And Demand View Product Family Level View • Creates a view of historical quantities on-hand based on orders, both customer and supplier, in the system • Quantity in Week N = Quantity in Week N-1 - Supply in Week N-1 + Demand in Week N-1 – Adjustments in Week N-1

6. Supply And Demand Flow Over Time - Future Supply And Demand View Overview • The historic view of supply and demand flow told us where we have come from in terms of balance within our supply line. • The future view gives us an opportunity to see where we will be in the coming weeks. (Any number of weeks can be displayed in the view.) • In our illustration, the future inventory positions are derived based on supply (purchase orders in the system with future dates) and demand (sales orders in the system with future delivery dates); there is an option to load forecast data into the data tables assuming it is available to the SKU or item level. • This view provides the call to action on current or planned orders. With this view the company is enabled to know what is going to happen to its on-hand stock quantities based on up to the minute information. The company is looking forward and pro-acting versus “looking in the rear view mirror” and reacting. Again, all views are drillable to the SKU or item level of detail. Each view is also exportable in an Excel format.

7. Supply And Demand Flow Over Time - Future Supply And Demand View Product Family Level View • Creates a view of quantities on-hand in the future based on existing orders, both customer and supplier, in the system • Quantity in Week N = Quantity in Week N-1 + Supply in Week N – Demand in Week N

8. Supply And Demand Flow Over Time - Future Supply And Demand View Drill-Down Into Product Family All The Way To Item Level • Creates a view of quantities on-hand in the future based on existing orders, both customer and supplier, in the system • Quantity in Week N = Quantity in Week N-1 + Supply in Week N – Demand in Week N

9. Inventory On-Hand - Inventory Overview Dollars View Overview • After evaluating the balance in the company’s supply and demand flow, the inventory on-hand reports provide the company views into the current inventory on-hand quantities across a number of dimensions. • The Inventory Overview views provide a context for the inventory quantities on-hand. This view is available in both dollars and units; the dollars view is illustrated. • As with all the views, each page has an explanation of what the company is seeing and how what it is seeing has been derived. The inventory quantity on-hand information is framed with orders on-hand information, a weeks supply calculation, and future receipts information. These additional pieces of information provide a view into inventory on-hand quantities within the context of “how is what the company has today in balance with its future needs”. • Again, all views are drillable to the SKU or item level of detail. Each view is also exportable in an Excel format.

10. Inventory On-Hand - Inventory Overview Dollars View • Orders On-Hand are all the current customer orders in the system • Total Inventory represents the current quantity on-hand • Available Inventory represents the current quantity on-hand less all Orders On-Hand • Weeks Supply Available represents the number of weeks of current inventory available based on the average demand during the last year

11. Inventory On-Hand - Weeks Of Inventory On-Hand View • The weeks supply information was just seen in the Inventory Overview Dollars View. Because of the utility of this calculation, this information becomes the focus of this set of views. • In addition to the weeks supply calculation, the inventory on-hand quantities are presented in both units and dollars in each view. Again, all views are drillable to the SKU or item level of detail. Each view is also exportable in an Excel format. • Weeks represents the number of weeks of inventory available based on the average demand during the last 52 weeks • Value is the unit cost of the current Quantity On-Hand

12. Inventory On-Hand - Aging Dollars View Overview • The Aging Reports are another set of views within the inventory on-hand views. The Aging Views are available in both dollars and units; the illustration is in dollars. • The Aging Reports “age” the quantities on hand into 52 week “buckets”. The 52nd week bucket is all supply that is 52 weeks and older. • This report pinpoints challenges in the company’s inventory positions for both inventory managers and product planners. • Again, all views are drillable to the SKU or item level of detail. Each view is also exportable in an Excel format.

13. Inventory On-Hand - Aging Dollars View Product Family Level View • Ages inventory dollars by weeks in inventory, i.e. column “0” represents unit cost of all inventory on hand; items in column “4” represent inventory 4 weeks or older; items in column “52” represent inventory 1 year or older • OOH or Orders On-Hand represent the dollar value of all future orders presently in the system

14. Inventory On-Hand - Aging Quantities View Drill-Down Into Product Family All The Way To Item Level • Ages inventory units by weeks in inventory, i.e. column “0” represents all units of all inventory on hand; items in column “4” represent inventory 4 weeks or older; items in column “52” represent inventory 1 year or older • OOH or Orders On-Hand represent the total units in all future orders presently in the system

15. Inventory Performance – Turnover View Overview • The last group of views in our illustrative Inventory Management Solution are inventory performance views. • These views focus on turnover and turn and earn calculations. Turnover has long been considered a key measure of inventory performance. • Uniquely, the calculation is developed based on units versus dollars. The reason for this is to provide a more realistic view of inventory turnover, free from the inherent timing and valuation issues associated with using item master or standard costs and attempting to consider purchase price value-based variances. • In addition to the turnover calculation, the components of the calculation are displayed. • Again, all views are drillable to the SKU or item level of detail. Each view is also exportable in an Excel format.

16. Inventory Performance - Turnover View • Turnover is computed by dividing the Total Demand for the last 52 weeks by the Average Quantity On-Hand

17. Inventory Performance - Turn & Earn View Overview • The traditional turnover view has been enhanced by the addition of gross margin. • Turnover times gross margin percent provides insight as to how the company makes money and where. Not only should the company drive toward increasing inventory turns, but it should do so within the context of generating margin. The greater the turnover and the higher the margin, the more money it makes. • The turn & earn index calculation is supplemented with information on inventory on-hand and 52 weeks of sales data, the components of the calculation. • Again, all views are drillable to the SKU or item level of detail. Each view is also exportable in an Excel format.

18. Inventory Performance - Turn & Earn View • Turn & Earn is computed by multiplying Margin % times Turnover • Margin is computed by subtracting Sales from Cost of Sales and dividing by Sales • Turnover is computed by dividing the Total Demand for the last 52 weeks by the Average Inventory On-Hand • Inventory Dollars represents the Quantity On-Hand times the “layered” unit cost • Sales represent dollar Sales over the last 52 weeks, before adjustments for discounts

19. Inventory Performance - Turn & Earn View Drill-Down Into Product Family To Family Category Level • If the Turn & Earn on the Air Filter Product Family looks off, a click of a mouse will allow one to drill into the Air Filter Product Family down to the Family Category group • If from this view, the Family Category 7116 looks impressive, it might be worth investigating why the T&E is so good in this category; there may be lessons to be shared among the other categories

20. Inventory Performance - Turn & Earn View Drill-Down Into Product Family Category To Item Level • The drill-in reveals why the performance in this family is so robust • It also reveals some work to be done in several of the items • From this point, the analysis could move to the Aging Quantities View to get a broader sense of the depth of the poorer performers • From there, the analysis could move to the Future Supply and Demand View to determine what “tweaks” can be made in the current and future supply flow

21. Summing Up Our Illustrative Inventory Management Solution • Our Inventory Management Solution enables companies to more effectively manage their supply chain activities. • Inventory managers, product planners and purchasing personnel can instantly access information on every dynamic aspect of inventory. • This is accomplished with the ease of a click of a mouse, eliminating the need to rely on others to write long and complex queries and eliminating the learning curve associated with using business intelligence tools. • Our focus is on providing quick access to the data company personnel need to perform their inventory management activities. • With our Inventory Management Solution the data within each deep and detailed view can be exported to Excel for further analysis or manipulation. • Our Inventory Management Solution is a data store application, which interacts with the company’s ERP system. Our views in our application are customized to fit each company’s particular needs.

22. Contact Us Rob Berling Berling Associates 550 Pharr Road, Suite 212 Atlanta, GA 30305 tel. 404.365.9836 ♦♦♦ fax. 404.365.9837 www.berlingassociates.com rberling@berlingassociates.com