Follow

ID806 - Inventory Turns Report Overview & Sample

Inventory Turns Report providing Turn Ratio by Inventory Code (Copier, Printer, Accessory, etc.) along with Total COGS, Average Monthly Value as well as Quantity on Hand. 

Overview | Samples | Variables | Alert Functionality | OnDemand Report FilterBest Practices & Tips |  Related Alerts

Click to Subscribe

Overview

Overview

Inventory turnover is a financial ratio that you should be paying close attention to because it indicates the cash efficiency a company enjoys.  Any inventory on your shelf or in your technician's cars is typically the largest place where your cash is tied up.  The higher your turn rate, the lower your risk of inventory loss and/or obsolescence.

The speed with which a company can sell inventory is a critical measure of business performance. It is also one component of the calculation for return on assets (ROA); the other component is profitability. The return a company makes on its assets is a function of how fast it sells inventory at a profit. As such, high turnover means nothing unless the company is making a profit on each sale. This article has a good explanation.

Your turn rate is the number of times per year you sell (turn) your stock. Dealers typically will track 3 primary turn rates: Equipment, Parts & Supplies.

Historically the benchmarks were:

Equipment - 4 to 5
Supplies - 7 to 8
Parts - 2.5+

Although we are seeing new numbers of:

Equipment - 6 to 7
Supplies - 9 to 10
Parts - 4+

 

Run Schedule: No emails generated, OnDemand Report Only

Type of Output: OnDemand SSRS Report

 

 

*  *  *

Sample

Sample

COGS & Qty Turns:

The QTY Turns for previous X months is predicated on how much data we have. Since we have to go back 12 months to get the number for 1 month, you need at least 24 months of being on eAuto to get 12 periods of data. If Qty Turns is for 4 months, for example, that is 16 months of data (12 + 4) as we have to go back 12 months for each period.

ID806.PNG

ABC Classification:

ABC classification is based on the premise that not all inventory is of equal value. Instead if follows the Pareto Principle, where 20% of stock accounts for 80% of the value to the business. Using ABC classification you can therefore split inventory into three categories: 

Category A: this is the smallest category and consists of the most important stock items 

Category B: will generally be slightly larger in terms of volumes of SKUs and will usually be made up of products of less value 

Category C: this will typically be the largest category where products will contribute the least to your business’s bottom line 

a.PNG

b.PNG

*  *  *

Variables

Variables

ID806Variable.PNG

Variable1: When you run the report you can specify the # of months back you want to use for the warehouse item trending section.  Enter here the default # of months you want the report to start with.  Note: this portion has to read your entire inventory ledger file so be cognizant of the run time and load on your sql server if you run it during peak operation hours for more than 6 months history.

*  *  *

Alert Functionality

Alert Functionality

 

1. Report refreshes first Sunday of each month ONLY

2. Most recent Fiscal Period shown is the last period closed in eAuto. If you expect it to be more recent, then please check with your Accounting Department as to why fiscal period needed is not closed out.

3. If you subscribe to this report it will not install until after business hours due to the heavy table build required. So you will need to wait until the following day to actually run it.

4. A low turnover ratio implies weak sales and, therefore, excess inventory. A high ratio implies either strong sales and/or large discounts.

5. The report ignores drop ship and fixed asset warehouses.

6. Inventory turns are calculated using your inventory value against sales and service usage quantities. Inventory transfers , adjustments, etc are not used to calculate your turns score, only sales order and service call transactions are considered as usage.  The value/cogs calculation takes the total cogs for the previous 12 months, divided by the average monthly inventory value (The report calculates the on hand value for every item for each of the last 12 periods [for the appropriate group summing up by report grouping : inventory code, category, etc], divides that 12 month total by 12 to get your average monthly inventory value.)

7. The Qty turns is the same basis except using the total 'qty' of items sold over the previous 12 months divided by the average monthly total qty of items for the report grouping (inventory code, category, etc).
8. Our report uses eAutomate's IC Ledger file system which only includes inventory transaction amounts that affected your inventory value (receipts, issuance's, cost adjustments, physical qty adjustments, etc). We chose not to use the GL COGS account for the reasons below:

a) Some dealerships post their mfg rebates/credits directly to their GL COGS for the appropriate rebate (parts, equipment, etc) so that their GP is accurately impacted (while others post those rebates/credits to a revenue account, and still others post those $$ into a standalone GL account.
b) The technical challenge of trying to accurately associate a one-to-one Inventory Asset GL to a particular COGS account become apparent when we ran some tests and found many instances on items where both a sales and service code was applied (Use ID759 to catch these), that the Sales Code GL accounts (REV / COGS) did not align with the Service Code GL accounts. This resulted in the COGS going into different GL Accounts on a sales order than they might for the same item used on a service call. And in some cases, from a P&L perspective that might be by design.

 

Inventory Turnover Ratio Explained

Inventory Turnover Ratio Explained:

An example of how the Ratio is calculated is best told in a story problem :

Mike has a dealership and his cost of goods sold for the last 12 months running was $1,000,000. Mike’s inventory value at the start of that 12 month period was $3,000,000 and the ending value was $4,000,000. Mike’s turnover ratio is:

ScreenClip.png

Since we use a 12 month basis, the .29 indicates that Mike only sold approximately 1/3 of his inventory during the year, which implies that mike would need almost 3 years to sell his entire inventory (or complete one turn).

 

ABC Analysis

ABC Analysis(otherwise known as ABC classification) Explained:

ABC analysis (or ABC classification) is used by inventory management teams to help identify the most important products in their portfolio and ensure they prioritize managing them above those less valuable. 

ABC classification is based on the premise that not all inventory is of equal value. Instead if follows the Pareto Principle, where 20% of stock accounts for 80% of the value to the business. Using ABC classification you can therefore split inventory into three categories: 

Category A: this is the smallest category and consists of the most important stock items 

Category B: will generally be slightly larger in terms of volumes of SKUs and will usually be made up of products of less value 

Category C: this will typically be the largest category where products will contribute the least to your business’s bottom line 

Your inventory’s ‘value’ can be based on a number of criteria, such as annual sales revenue, profitability or annual consumption value. 

Report provides A, B, C classification by Annual Qty Usage and Annual Cost:

Use + (plus) sign to see detail at Item Level:

a.PNG

b.PNG

The graph below illustrates how 80% of a company’s sales revenue comes from 20% of their stock items: 

1.png

ABC analysis & The Pareto Rule for inventory management: 

ABC Classification Calculation Example 

Here is a working illustration of how to divide your inventory using annual consumption value. We’re going to use Frank’s Fasteners business as the example: 

1. Use this formula to calculate the annual consumption value of each item: Annual number of units sold (per item) x cost per unit 

2.PNG

2. List the products in descending order based on their annual consumption value

2a.PNG

3. Total up the number of units sold and the annual consumption value

3.PNG

4. Calculate the cumulative percentage of items sold and cumulative percentage of the annual consumption values using the totals

4.PNG

5. Determine the thresholds for splitting the data into A, B and C categories. The threshold for determining the ABC split will be unique to your company and your product mix, but typically it’s close to 80%/15%/5%

5.PNG

How to Put your Data to Good Use 

With the calculations complete, you can use your final data to review how you currently manage the inventory in each category. If you find that you’re treating all items the same (in terms of the stock you hold and the purchases you make regardless of their category) then you probably have inefficient and needlessly expensive inventory policies. This means you’re probably over- and under-ordering on many product lines. 

The good news is that there is plenty of room for improvement! And improvement will lead to reduced inventory, delivery and management costs. 

The best way to start is to adapt your purchasing and inventory policies to each group from the ABC analysis. This could include setting up sophisticated ordering processes for all A items, such as checking every purchase order and spending more time discussing lead times with suppliers to guarantee best value and timely deliveries. In contrast, C items should take up much less of your time and could be ordered automatically to save valuable human resource.

XYZ Analysis 

As discussed in our post on the importance of ABC analysis in inventory management, ABC analysis can be very beneficial as a simple way to prioritize your workload and help reduce the hours spent ordering and managing inventory. 

However, there are limitations of the model. For starters, ABC classification is arguably over simplistic due to the categories being static (unless regularly reviewed to allow items to move between groupings) and the evaluation criteria being one-dimensional. 

To help overcome these issues, you can apply XYZ analysis. 

XYZ analysis is a framework to classify products based on their variability of demand. 

X items: regular demand 

Y items: strong variability in demand 

Z items: very irregular and difficult to predict demand 

This means that you can segment items based on their forecastability – the likelihood that their demand will vary from their forecast. 

Adding another level of insights to your inventory classification model allows you to make more informed ordering and stocking decisions. For example, it makes sense to treat AX items (that are valuable and have a constant demand) differently than AZ items (with erratic demand). If demand is steady and easy to predict (X items), your safety stock levels can be much lower than products where demand is much more volatile (Z items). 

From ABC – XYZ Analysis to Automation 

While XYZ analysis adds more sophistication to the ABC framework, it doesn’t solve the problem of the calculations quickly becoming out of date. Products can rapidly move between categories as their sales rise and fall or as they move through the product life cycle. 

What’s the solution? Use an inventory optimization tool to automate the process. Software such as EazyStock will allow you to categorize your inventory based on multi-dimensional criteria including demand, sales frequency, number of picks and annual consumption value. 

By automating your inventory classification process, you can be sure that it stays up-to-date with re-classifications taking place daily so products are always managed according to the most relevant inventory policy. 

From <https://www.eazystock.com/blog/how-to-use-abc-analysis-for-inventory-management/ 

*  *  *

OnDemand Report Filters

OnDemand Report Filters

 

ID806ReportFilters.PNG

Most recent Fiscal Period shown is the last period closed in eAuto. If you expect it to be more recent, then please check with your Accounting Department as to why fiscal period needed is not closed out.

 

*  *  *

Best Practices & Tips

Best Practices & Tips

To validate the inventory valuation number ("This period Ending Value: $782,564" in the sample above) run the Inventory Valuation report in eAutomate with the following parameters:

1. Run by period

2. Use the Advanced Filter on the Items to filter by the Inventory Code. (In the sample above, the inventory code is "Copiers MFP"):

ID806.jpg

Report breaks down your Inventory Turns for each Inventory Code in eAuto:

Inv_Codes.png

*  *  *

Related Alerts

Related Alerts

ID759/Inventory Items With Conflicting AccountsThis process will ensure your Sales and Service codes are not tied back to conflicting GL revenue and COG accounts. Items should have matching revenue GL and COG accounts regardless of the process that is used to relieve inventory.

*  *  * 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk