Updating inventory values

Introduction

If your business requires that you keep inventory, you might want to know the value of your assets at any given moment. You don't have to wait until the end of the month or the end of a GL period to find that out. Paragon uses a weighted average formula to calculate the average value of the items that you are keeping in your warehouses.

Inventory value is calculated from the adjustment history which is calculated based on the accounting currency in MyCompany. This is what is used for both the journal and the history values which must match.

The Inventory Value of an item is stored in a provisioned attribute in the item module and although t his attribute can be changed manually, the goal should be to let Paragon fill this value and update it with a weighted average based on purchase cost and stock fluctuations.

Inventory value calculations

To get started, we will need to set up your inventory value calculations. To access this setting you will need to navigate to the financials tab in my company. You can find your company module from the settings page, and click the financials tab once you're in.

There are two options when calculating which inventory values are used with each shipment:

FIFO - First In First Out
LIFO - Last In First Out

This setting will determine the sequence with which to ship out items. Each individual unit was given an inventory value when it was received into stock, therefore, each unit will have its own value when being sent out.

Recalculating inventory values

After you have selected either FIFO or LIFO for your inventory value calculations, you then have access to two other options: valuation by warehouse and recalculate inventory values.

Valuation by warehouse: This will calculate value for items in reference to each warehouse location if checked.

Recalculate inventory values: This will recalculate your inventory. You may need to use this to organize any manual changes to inventory or if you change your inventory value calculation.

Hitting the recalculate inventory values button will bring up a modal asking you to confirm your choice. Hitting recalculate will update your inventory values and will show progress before it has completed. (The modal will automatically close when it has finished.)

Screen setup

Go to screen setup and add the attribute inventory value to your product screen. This attribute is a monetary value type. It will not contain any value on the product if you have not had any transactions involving that product. So, you can only see it in the edit mode.

The formula

As stated in the introduction, the inventory value of a product, at any moment, is the weighted average of the unit costs of the goods received at the warehouse.

In this example, for the bicycle, we are going to calculate the inventory value by looking at the history tab in the inventory module. Looking at the receipts that have not been voided, there are 3 receipts for this item:

First: on 4/14/21, 150 units were received for the cost of $225 each.
On 4/16/21, 1 unit was also received for the cost of $225, giving a total of 151 units at $225.

Second: Also on 4/16/21, another 6 units were received for the cost of $200 each.

Third: On 4/19/21 a further 200 units were received at $210 each.

Calculation:

Total quantity received = 151 + 6 + 200 = 357

Total cost of items received = 151*$225 + 6*$200 + 200*$210= $77,175

Thus the weighted average cost per unit becomes: $77,175/ 357 = $216.15

View the calculated value

Going back to the product module, now we have the same value sitting in the inventory value attribute.

The system allows you to edit the inventory value of your product at will. All you have to do is click on the edit button and change the number. However, you should bear in mind that the new value will stray from the weighted average formula. In order to bolster this fact, the system shows an asterisk above the modified value.

It is worth mentioning that your inventory value will go back to reflect the formula once again when you ship or receive any units of this product.

What's next?

If you have more questions about how ParagonERP uses inventory - read up on these topics:

Transactions that affect inventory

Inventory transfers with related warehouses

Inventory movements