Improving Inventory Management with Advanced Analytics
A manufacturer wanted to improve its ability to project the inventory levels needed to fulfill orders without overstocking product. The company had the data needed to project inventory levels, but was using a manual process and excel spreadsheets to complete the analysis, which was a time-consuming manual process. In addition, one system was using information from a point in time (period-end) and the other used daily transactional details. This was particularly challenging, because there were multiple transactions and multiple time periods to analyze. Even though both data sets could be blended on a unique identifier in other data visualization tools, it was possible that there might be nagging aggregation errors because of the dual data sources. We needed to bring both raw datasets together into a summarized period-end format, eliminating the need to perform manual adjustments in excel each month.
The Continuus Approach
To bring both raw datasets into a singular format, we used Alteryx to bring the datasets together in to avoid overinflating sales or inventory information. The information we were using looked something like this:
Period End Information
|Date||Asset ID||Asset Value|
To bring the datasets together accurately, we created a concatenation formula to properly group the transaction information with the corresponding period as follows:
Uppercase([Asset ID]) + " " + DateTimeFormat([Date],"%Y-%m")
This resulted and the following ID output:
We then grouped the transaction information by our new ID field above and used the summarize tool to sum the quantity and amount. From there, we joined back the period end information with the summarized transaction data with the newly-formed ID.
Finally, we performed several data formulas to cleanse the final output:
|Date||ID||Asset ID||Asset Value||Sum Quantity||Sum Amount|
With faster visibility into sales trends, the manufacturer is able to quickly spot trends and adjust production up or down to ensure that fixtures are in stock, but not overstocked. This will help them improve their bottom line while ensuring the ability to meet customer orders.