Inventory Provisions

Not all inventories in hand will get used before expiry. It is important to account for potential losses in value due to various factors such as expiration, damage, obsolescence, or theft. Over time, certain inventory items may decrease in value or become unsellable. Therefore, provisions need to be made to acknowledge and account for the potential loss in value associated with these items. By estimating and recognizing these potential losses, your company ensures accurate financial reporting and inventory valuation that reflects the true worth of the remaining inventory. 

Let's consider a healthcare firm as a simple example where determining the total value of provisions is necessary on a monthly basis. To calculate the total provisions, we can divide them into three primary categories: expired items, items that will expire after one year, and excess inventory for the upcoming one-year period.

1.Expired Items

Expired items refer to batches of inventory that have reached their expiration dates and can no longer be used or consumed safely. It is crucial to identify and track these expired items within the inventory. While most expired items cannot be salvaged or re-used, there are certain cases, particularly in the healthcare industry, where certain surgical items may undergo sterilization and become suitable for re-use.

2.  Expiry after 1 year

The focus is on items that are labeled as non-moving items and are projected to expire after one year. Non-moving items are those that have not been consumed or utilized within a specific timeframe, typically the past 6 months (or 12 months, depending on the company's specific criteria).

For the purpose of this categorization, the items included are those that meet the following criteria:

They have not been consumed or utilized within the defined timeframe (e.g., past 6 months).

There is an opening balance recorded for these items in the sixth prior month, indicating that they have been in the inventory for a significant period.

3. Excess inventory

Based on the inventory in stock and its expiration dates, it is possible to calculate excess inventory due to expiry within the coming one-year period. To determine this, a logical approach can be applied, considering factors such as the average consumption pattern over the past six months.

a. Identify inventory items that are projected to expire within the coming one-year period. This includes all items in stock that have an expiration date within the next 12 months.

b. Calculate the excess inventory on a monthly basis for each item. This calculation is based on the average consumption pattern over the past six months.

c. For each month, compare the projected consumption of the item with the available inventory quantity. If the projected consumption exceeds the available inventory, there is a potential shortfall.

d. If there is a projected shortfall in a particular month, utilize the available stock that remains valid in the following months to meet the demand. This helps optimize inventory utilization and minimize potential waste.

e. Conversely, if the projected consumption is lower than the available inventory for a given month, there is excess inventory. This excess inventory can be added to the provision calculation for that specific month.

Note: The example provided above demonstrates a simplified scenario, but implementing this calculation in Excel can be challenging. While it is possible to perform this calculation using SQL, it can also present some complexity.