We complement the previous section's working capital analysis with a closer look at the elements that make up each measure. In the case of DSI, we analyze Average Inventory Value and Annual Cost of Goods Sold (COGS). This auxiliary analysis helps us understand whether an increasing DSI is the result of rising inventory levels or decreasing sales. It also helps us detect which product is not rotating frequently enough.
Let's combine the related metrics and have them share the same dimension axis, as in the following visualization:
- Let's create three separate combo charts. We represent the current period with bars and the last period with circles. In each chart, we set the orientation to be horizontal, and move the legend to the top. When we use Ctrl + Shift to place the legend on top, we wait until its red outline covers the entire top section so that the labels appear in a row:
Title
DSI
Dimensions
Labels
Value
Item
ItemExpressions
Labels
Value
Current
avg({$<_Periodicity={'Monthly'},[AsOf Months Ago]={">0<=12"}>}aggr( sum({$<_Periodicity={'Monthly'},[AsOf Months Ago]={">0<=12"}>} [Inventory Balance]),[Year-Month],Item))/sum({$<[AsOf Months Ago]={">0<=12"}>} [Cost])*365Past
This is the same as the Current DSI but replace
[AsOf Months Ago]={">0<=12"}with[AsOf Months Ago]={">12<=24"}Title
Average Inventory Value
Dimensions
Labels
Value
Item
ItemExpressions
Labels
Value
Current
avg({$<_Periodicity={'Monthly'},[AsOf Months Ago]={">0<=12"}>}aggr( sum({$<_Periodicity={'Monthly'},[AsOf Months Ago]={">0<=12"}>} [Inventory Balance]),[Year-Month],Item))Past
This is the same as the Current Inventory Value but replace
[AsOf Months Ago]={">0<=12"}with[AsOf Months Ago]={">12<=24"}Title
COGS
Dimensions
Labels
Value
Item
ItemExpressions
Labels
Value
Current
sum({$<[AsOf Months Ago]={">0<=12"}>} [Cost])Past
This is the same as the Current COGS but replace
[AsOf Months Ago]={">0<=12"}with[AsOf Months Ago]={">12<=24"}Create a container object and, in the Presentation tab, select Container Type as Grid. Set Columns to 3 and Rows to 1.
- Drag each chart into the container object.
- In the Sort tab of each chart, enable only Expression and select Descending. Insert the following code into the expression field:
avg({$<_Periodicity={'Monthly'},[AsOf Months Ago]={">0<=12"}>}aggr( sum({$<_Periodicity={'Monthly'},[AsOf Months Ago]={">0<=12"}>} [Inventory Balance]),[Year-Month],Item))/sum({$<[AsOf Months Ago]={">0<=12"}>} [Cost])*365 - We cannot scroll through the three charts at the same time, so, in the Dimension Limits tab of each chart, let's select the option to Restrict which values are displayed using the first expression.
- In the same tab and under the Show Only option, we change the value to First.
- In the Presentation tab, disable the option to Suppress Zero-Values.
- Finally, after verifying that each row of bars corresponds to the same item, let's remove the dimension labels in the second and third charts by deselecting the Show Legend option in the Dimensions tab.
Instead of using a common scroll bar, we repeatedly click on the bar that represents Others in order to scroll through the charts and review more items. When we analyze all three measures in a single view, it becomes clear that the DSI of most of the items is increasing and that this increase is due to both an increase in the inventory value and a decrease in COGS.
After breaking down each working capital element and analyzing its parts, the next step is to analyze more closely the operations that cause these results. Let's continue to explore the inventory data in more detail and compare each product's inventory levels with their corresponding minimum, reorder, and maximum levels.