Improving Inventory Management with Advanced Analytics

Situation

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
1/31/2018 A $10,000
1/31/2018 B $9,000
12/31/2017 A $9,940
12/31/2017 B $8,940

Transactional Information

Date Asset ID Quantity Amount
1/31/2018 A 5 $20
1/15/2018 A 5 $20
1/1/2018 A 5 $20
1/31/2018 B 5 $20
1/15/2018 B 5 $20
1/1/2018 B 5 $20
12/31/2017 A 5 $20
12/15/2017 A 5 $20
12/1/2017 A 5 $20
12/31/2017 B 5 $20
12/15/2017 B 5 $20
12/1/2017 B 5 $20

 

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:

ID
A 01-18
B 01-18
A 12-17
B 12-17

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
2017-12-31 A 2017-12 A $9,940 15 60
2018-01-31 A 2018-01 A $10,000 15 60
2017-12-31 B 2017-12 B $8,940 15 60
2018-01-31 B 2018-01 B $9,000 15 60

ct-data-img-800.png

Results

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.