In the left-hand side upper-horizontal bar chart that we have been looking at, we can see Inventory Turns in orange and the total Value of the stock in blue. We also have a vertical bar to establish an average inventory value that we would like to maintain. At least, we assume that is what the bar represents since it is the same color as the horizontal Value bar and since it has the Target label next to it in the same color. Unfortunately, to see all the product lines, we need to use a scrollbar. Perhaps we should either forego the bottom row to add room to display everything, or group our product lines into a few high-level groups that can then be drilled down on for detail.
For example, if I use the bar graph, I can see Dairy, and it stands out because it has the fewest inventory turns. That seems very curious to me as I would expect dairy products to be perishable and require more inventory turns than Frozen Food, the row above Dairy. Also Dairy Products Group represents 18 percent of what we can immediately see and 11 percent of our total Inventory value but has very low inventory turns compared to the other product lines, which are readily visible. Unfortunately, nothing on the screen tells me what percent of the inventory value Dairy represents. To get those comparisons, we have to scroll all the way to the bottom of the right-hand side List Box to get the grand total inventory value. Then, we scroll back up to get the Dairy value and use a calculator to do the math.
Another inconvenience is that changing the top bar selection of years and months changes the order in which items are displayed in the graph and list. This tells us immediately what the highest-value Product line is in a given time period, but it means that, when we go back to look at what happened with Dairy, it has moved. So, perhaps, although this is an interesting demonstration of what can be done, because we are dealing with so much information it is not a good example of what should be done. Three of the four quadrants use a measure labeled Inventory Turn Over. What does that represent?
The standard calculation for Inventory Turns is the Cost of Goods Sold from the Income Statement divided by the Average Inventory for the Period.
The number of days a company should be able to sell through its inventory varies greatly from industry to industry. Retail stores and grocery chains are going to have a much higher inventory turn rate since they are selling small-priced products. Companies that manufacture heavy machinery, such as trucks and construction equipment, are going to have a much lower turnover rate since each of their products may sell for hundreds of thousands of dollars.
In this case, we are looking at a grocery store or wholesale grocery, so the earlier question about Dairy turnover needs to start with the formula being used in QlikView. Start by right-clicking on the right-hand side list display and choosing Properties. Next navigate to the Expressions tab and place your cursor on the Inventory Turns expression to see the calculation in the Definition box. In the box, you will see the following formula:
Sum(ThroughputQty*CostPrice)/Sum(StockOH*CostPrice)
We cannot tell whether this is a valid formula for Inventory Turns based on the definition, but it looks as though it might be taking the number of units sold in the given period multiplied by the cost of the units to get the Cost of Goods Sold amount in dollars. Then, it is dividing by the remaining stock on hand multiplied by the cost of the units. Hopefully, if this was a real operation, reporting real numbers instead of just a demonstration, the appropriate average functions would take place before they reached the numbers used in this formula.
So, we can bring this formula down to a level suitable for comparison purposes if we have:
- 4,000 cartons of milk that have sold in this month at $2 each
- 4,200 cartons of milk last month, again sold at $2 each
- 1,100 cartons of milk remain at the end of this month, costing $1 each
- 800 cartons of milk remain at the end of last month, costing $1 each
We will assume there is no leftover stock from the prior month, since milk is a perishable item, for ease of calculation. We are going to have the following mathematical calculations to get to the one month Inventory Turns for this month: (4000 * $1.00)/(((1100 * $1.00) +(800 * $1.00))/2 ) = 4.2. We used 1 dollar in this example formula to make the math simple.
That gives about four inventory turns in the month. But we do not know if that is good or bad without comparing that to industry averages and prior performance. For dairy inventory turns comparison information, you will find a good article at:
http://www.cooperativegrocer.coop/articles/2004-01-09/controlling-your-inventory
At an individual product level and individual month, inventory turns is only a viable measure if we understand what it means in terms of our business. By setting goals for inventory turns for each category, we can evaluate specific items in stock and create fluid inventory averages to better manage product flow.
Going back to our manually calculated 11 percent of inventory value for the Dairy Product group, to have the percentage of inventory value readily available in our Inventory dashboard would be useful in understanding what the Inventory Turns might mean. If we have 11 percent of our cost of goods tied up in a set of items that is turning over more slowly than our other product groups, we might want to look at ways to increase turnover, particularly of a perishable item. But we may be limited by supply and demand. We have to stock the items because our customers expect availability, but there may be constraints outside our control.
The upper-right corner list box displays numbers related to the graphs and changes them when drilled into. The top column header say that the right-most column is Inventory Turns, but the display is in alphabetical order of the rows, so we have to scroll down to see Dairy. Alternatively, we can close each group to see just the top level product group by clicking on the minus sign (-) next to each group, turning it into a plus sign (+):
Figure 7-1: Click on any minus signs to close the Group detail and display a plus sign to make more Groups visible
When looking at Inventory Profile in the lower-left-hand side quadrant, we can see that the majority of the value we have in products has inventory turns of less than three. Therefore, Dairy Product Group with Inventory Turns of 4.01 seems reasonable:
Figure 7-2: The majority of product values have fewer than three inventory turns
By clicking on the bottom turnover line for between 12 and 15 inventory turns, we bring Breakfast Foods and Produce at the top of our list on the bar chart under Inventory Turns and Value. Suddenly, we are presented with a totally different picture. The Inventory Turns & Value bar chart in the upper-left corner shows Breakfast Foods as having the highest Value and Turns. The Inventory On Hand Report shows a selection of product groups with turns in the 12 to 15 range with Alcoholic Beverages at the top because of the alphabetical order. Suddenly, Dairy Group is showing Inventory Turns of 14.6 percent. How can that be when it was in the 4 percent range before?
Figure 7-3: Click on any minus signs to close the Group detail and display a plus sign to make more Groups visible
We have to drill back down by opening the plus sign next to Dairy and rearrange our list to discover that this represents only one item within Dairy Group—Cheese.
Figure 7-4: Drill down into Dairy to reveal a high turnover inventory item in cheese
The lower right-hand side quadrant has a line chart of On Time Delivery. We can see that we have a Target of 95 percent on-time delivery. We can also see that the lines compare our on-time delivery year over year just as in the common size income statement we built in Chapter 2, QlikView Dashboard Financial KPIs — another good use for year-over-year line graphs.
Here, we can see that we did better in 2009 than in 2010 or in the current year. We are building 2011. Looking closer, we can also see that the scale is 85 percent to 100 percent, so we are not doing quite as badly as we would be if that was a range of 50 percent on-time to 100 percent on-time, but we are still failing to meet our goals quite often. Our formula for our delivery on-time measure is:
Sum([Late Shipment])/Count([OrderID])
One of the questions that we might ask is whether Target is set realistically. Since we do not know what constitutes the Late Shipment measure or whether the Target is 10 minutes, 10 hours, or 10 days, all this really tells us is that we want more information to see whether improvement is needed in our delivery schedule or an adjustment of our Target.