HWA Observability Over Splunk. Generating Business Metrics: Inventory Planning

In this post, we’ll see how key business metrics can be generated over a flow within HWA and then analyzed over Splunk for meaningful insights using clear, user-friendly dashboards.

Inventory Planning Case Study:

A detailed post entitled “Case Study: Inventory Management with Workload Automation” has been already written earlier on the flow itself.

https://www.workloadautomation-community.com/blogs/case-study-inventory-management-with-workload-automation

We’ll now look at the process for generating key business metrics from the flow and passing them to Splunk for visualization to provide enhanced business insights.

Here’s a brief summary of the flow:

A typical material requirements planning (MRP) flow in an enterprise context might be comprised of jobs which would execute as follows, with the goal being to come as close as possible to running a balanced plant.

1. MRP jobs which would compute the materials requirement for each inventory item in order to process the week’s orders. In this example, we’re considering angle brackets, machine oil, polish, and MS sheets.
2. A set of current inventory jobs which would compute the current inventory level for each inventory item: angle brackets, machine oil, polish, and MS sheets.
3. A set of extraction jobs which would parse the job logs for these jobs and extract the inventory levels for both MRP and current inventory.
4. A set of variable passing jobs which would pass the variables for each inventory item (in this example: angle brackets, machine oil, polish, and MS sheets) for each MRP level as well as the current inventory level.
5. A set of comparison jobs which would compare the current inventory level with the MRP inventory level for each inventory item.
6. If the inventory level is too low to process the current week’s orders, a procurement order is placed on a portal with all known vendors.
7. If the inventory level is high enough to process the current week’s orders, a sales order is placed on a portal with all known vendors.

Metrics Derived:

Some key metrics are derived from the flow.

Inventory Turnover Ratio:

Ideally a value between 2 and 4 will be set.

The metrics shown here were generated from our flow:

ITR_MSSHEET=COGS_MSSHEET/AVG_INV_MSSHEET=4.46

ITR_POLISH=COGS_POLISH/AVG_INV_POLISH=0.72

ITR_ABRACK=COGS_ABRACK/AVG_INV_ABRACK=2.63

ITR_MACHOIL=COGS_MACHOIL/AVG_INV_MACHOIL=9.02

Sell-Through Rate:

The sell-through rate measures the amount of inventory you’ve sold in a month versus the amount of inventory shipped to you from a manufacturer. Sell-through rate is an important retail sales metric that allows you to monitor the efficiency of your supply chain.

The metrics shown here were generated from our flow:

SELLTHROUGH_MSSHEET=(INVSOLD_MSSHEET/INVRECVD_MSSHEET)*100=73%

SELLTHROUGH_POLISH=(INVSOLD_POLISH/INVRECVD_POLISH)*100=78%

SELLTHROUGH_ABRACK=(INVSOLD_ABRACK/INVRECVD_ABRACK)*100=46%

SELLTHROUGH_MACHOIL=(INVSOLD_MACHOIL/INVRECD_MACHOIL)*100=94%

Inventory Days:

The metrics shown here were generated from our flow:

INV_DAYS_MSSHEET=(AVG_INV_MSSHEET/(COGS_MSSHEET/7))*7=10.97

INV_DAYS_POLISH=(AVG_INV_POLISH/(COGS_POLISH/7))*7=9.59

INV_DAYS_ABRACK=(AVG_INV_ABRACK/(COGS_ABRACK/7))*7=18.59

INV_DAYS_MACHOIL=(AVG_INV_MACHOIL/(COGS_MACHOIL/7))*7=5.43

Cost of Goods Sold:

Cost of goods sold (COGS) refers to the direct costs of producing the goods sold by a company. This amount includes the cost of materials and the labor used directly to create the good. It excludes indirect expenses, such as distribution costs and sales force costs.

The metrics below were generated from the company ERP system and retrieved through an HWA SAP job or an HWA Oracle eBusiness Suite job:

COGS_MSSHEET=$125000

COGS_POLISH=$23000

COGS_ABRACK=$20000

COGS_MACHOIL=$27000

Integration with Splunk:

We can integrate Splunk readily with HWA v10.1 as we are now generating WA Merge logs in JSON format. This means we can ingest into Splunk readily, and that any additional metrics generated in job logs can be sent as a curl payload through a job as follows:

/bin/curl “https://10.14.82.155:8088//services/collector” -H “Authorization: Splunk d7ced0c4-7709-4ccc-a192-e90b223fec35” –insecure -d ‘{“sourcetype”:”manual”,”event”:{

“COGS_MSSHEET”:”125000″,

“COGS_POLISH”:”23000″,

“COGS_ABRACK”:”20000″,

“COGS_MACHOIL”:”27000″,

“ITR_MSSHEET”:”4.46″,

“ITR_POLISH”:”0.72″,

“ITR_ABRACK”:”2.63″,

“ITR_MACHOIL”:”9.02″,

“INV_DAYS_MSSHEET”:”10.97″,

“INV_DAYS_POLISH”:”9.59″,

“INV_DAYS_ABRACK”:”18.59″,

“INV_DAYS_MACHOIL”:”5.43″,

“SELLTHROUGH_MSSHEET”:”73″,

“SELLTHROUGH_POLISH”:”78″,

“SELLTHROUGH_ABRACK”:”46″,

“SELLTHROUGH_MACHOIL”:”94″,

}}’

/bin/curl “https://10.14.82.155:8088//services/collector” -H “Authorization: Splunk d7ced0c4-7709-4ccc-a192-e90b223fec35” –insecure -d ‘{“sourcetype”:”manual”,”event”:{

“CURR_MACHOIL”:”2993″,

“CURR_ABRACK”:”7589″,

“CURR_MSSHEET”:”27991″,

“CURR_POLISH”:”31541″,

“MRP_ABRACK”:”21617″,

“MRP_MACHOIL”:”27012″,

“MRP_MSSHEET”:”16544″,

“MRP_POLISH”:”12698″,

}}’

The payload ingested into Splunk can be used to create clear, user-friendly dashboards in Splunk as shown here: