The business defines each product's minimum, reorder, and maximum stock levels so as to maintain an adequate quantity in inventory. We often calculate these numbers and insert them into an ERP system that automatically generates purchase orders or work orders every time an item reaches the reorder stock limit In QlikView, we can use sales and purchase cycle data to easily calculate each limit and compare it to historical inventory behavior. We use the following formulas to calculate each stock level:
Reorder Stock Level = Max Lead Time * Max Daily Sales
Minimum Stock Level = Reorder Stock Level – (Avg Lead Time * Avg Daily Sales)
Maximum Stock Level = Reorder Stock Level – (Min Lead Time * Min Daily Sales) + Reorder Quantity
We use a predefined lead time, or the time needed to restock an item, from the item master data table. We also assume that the minimum daily sales amount of any item is 0 and that the reorder quantity is equal to the reorder stock level. Given these assumptions the maximum stock level is the reorder stock level multiplied by two.
Like much of the information at a glance. We therefore use the following trellis chart to compare each item's historical inventory behavior with the calculated stock levels.
Let's create a line chart with the following dimensions and expressions:
|
Dimensions | |
|---|---|
|
Labels |
Value |
|
Item |
|
|
Year-Month |
|
|
Expressions | |
|
Labels |
Value |
|
Actual Stock |
sum({$<_Periodicity={'Monthly'}>} [Inventory Balance Quantity]) |
|
Reorder Stock Level |
max(Total <Item> {$<$(vSetRolling12Modifier)>}aggr(sum(Quantity),_KEY_Date, Item))*max([Item Lead Time]) |
|
Min Stock Level |
[Reorder Stock Level]-sum({$<$(vSetRolling12Modifier)>}
Total <Item>
Quantity)
/
networkdays(
addmonths(max(Total _KEY_Date),-12)
,max(Total _KEY_Date)
)*avg([Item Lead Time]) |
|
Max Stock Level |
2*[Reorder Stock Level] |
In the Dimensions tab, click Trellis… and tick the Enable Trellis Chart option.
The set analysis variable in the previous expression is from Rob Wunderlich's QlikView Component's library and allows us to determine the stock levels based on twelve months of sales data. The actual twelve-month period we use depends on the date that we select in QlikView. In the particular case of Min Stock Level, we use the networkdays() function to calculate the average daily sales by working days. We also have the option to calculate the maximum and minimum daily sales using more advanced methods. For example, we can experiment with the fractile() function and use 5% or 95% fractiles to remove outliers. We can also use the same t-distribution functions, to calculate a more conservative daily sales average.
Finally, let's create a customer aging report that helps us monitor the operations that impact DSO.
If we are to lower DSO, we need to make sure that customers pay on time. We monitor collections using a customer aging report. The following report shows the customers' total balances and categorizes it into bins based on the original due date of the each payment. As we're mostly interested in the past due payments, it groups these amounts into thirty-day period bins.
The same report structure can be used to monitor suppliers in order to maintain a healthy DPO:
Let's create a pivot table with the following dimensions and expressions:
|
Dimensions | |
|---|---|
|
Labels |
Value |
|
Customer |
|
|
Status |
|
|
Expressions | |
|
Labels |
Value |
|
A/R Balance |
|
|
<space> |
|
|
<space> |
='' |
- Select the second expression and select Linear Gauge in Display Options.
- Click Gauge Setting and define the Min in the Gauge Settings section as
0and Max as the following expression:sum({$<_Periodicity={'Monthly'}>} [A/R Invoice Balance]) - In the Segments Setup section, delete Segment 2 and change the color of Segment 1 to blue.
- In the Indicator section, select Mode Fill to Value.
- Disable the Show Scale option.
- Enable the Hide Segment Boundaries and Hide Gauge Outlines options.
- In the Sort tab, select Status and only enable Numeric Value.
- In the Presentation tab, enable the option to Show Partial Sums for both dimensions and enable the option to show Subtotals on Top.
- Pivot the table as shown in the previous figure.
The creation of the [A/R Invoice Days Overdue Bin] field in the script makes this report easy to create. In the script, we subtract the invoice due date by the date of the data snapshot and then use several nested if-statements to assign that result to a bin. As this field is relative to each snapshot's date and not today's date, we can analyze the aging report over time. The field is also a dual() data type where Current is 0, 0-30 is 1, 31-60 is 2, and so on. This feature allows us to sort the field more easily by selecting only Numeric Value option in the Sort tab.
How well a customer pays us, or their DSO is an important indicator of how important that customer is to our business. Let's continue the customer stratification exercise, and see how we use DSO to evaluate a customer's importance.