How Do You Calculate Excess Inventory?

How Do You Calculate Excess Inventory?

When it comes to inventory, calculating excess or old inventory can be a daunting task. For a long time, I was faced with the menace of calculating excess inventory. But what is excess inventory?

Basically, Old or excess inventory is a selected class of items on the stock that moves on a slower rate than the rest of the items in an inventory. Decades ago, computing such inventory meant that I would spend no less than a week to get it right. At the same time, I would be forced to manually update my inventory to avoid incurring losses.

Today, I need not worry about old inventory tabulation. Thanks to technology, I can identify and select old inventory with ease by recording the specific amount of money required. For an accountant, this is mostly seen as uncouth. Even so, I find the need to be able to easily identify old inventory and calculate them without the need for software. After all, I can never be sure with programmed tools. I like to remain handy in all my dealings, especially when it comes to inventory. This is not to say that I don’t trust different inventory tools in the market today, but at the least, having basic old inventory tabulation is of utter importance even when it comes to using such tools.

How to calculate excess inventory

One of the most important aspects of calculating excess inventory is to determine the inventory turnover ratio. The inventory turnover ratio gives me a clear idea of which items in my inventory derail my cash flow and profit outputs, thus incurring unnecessary costs.

Although this procedure is always done to tabulate old inventory in an entire stock, it can as well be used to determine which specific products are moving slower and causing losses in business.

In order to achieve this, you determine the total amounts of products sold, for the old inventory and dividing it by the average inventory of the specific product or products in the store. This is a basic idea that I always use to first determine the inventory turnover ratio. At this stage, using inventory software’s and tools come in handy as compared to conducting the tally as per item.

To get it done with ease, I normally identify the total amount of the product sold from the record and receipts provided by the inventory program. Thereafter, I divide the amount by the initial cost amount of the product in question. The tool will automatically generate the ratio for me, thus saving me time and hard work. Once the ratio is higher, the performance is better and I will not have to worry.

However, if the ratio is smaller, it means that the performance of the product is poor and I will have to calculate the e value of the excess inventory in order to determine how to cut on loses.

FORMULA

Inventory Turnover Ratio= Cost of Goods Sold/ Average Inventory.

Example

In the first 6 months of 2018, I sold different brands of diapers in my store. Each diaper cost me an average f 8USD (regardless of the brand) while purchasing from my vendor. Total diapers sold totaled 48000 USD. At the beginning of the year, my inventory totaled 18,500 USD for the entire store. By the end f the six months, my inventory totaled 11,300 USD. Therefore, in order to calculate my excess inventory turnover ratio, simply added the opening and closing inventory for the diapers and divided them by two.

[18,500+11,300]/2= $14,900

However, in order to determine whether my excess inventory was performing better o worse, I would have to determine the Inventory turnover ratio. So, when I use the above formula,

Inventory turnover ratio= $48,000/$14,900=3.22

This means that during the six months, I sold the diapers averagely three times. This means that I have a lot of excess inventory that I would have to sell at a low price to avoid incurring losses.