Average Cost of Inventory

the blog image

Feb 07, 2016

For accounting purposes there are a couple of ways of dealing with selling and purchasing of inventory and the changes in the cost of purchasing inventory over time. It is rare that a merchant will purchase items to sell in their Shopify store for the exact same price over time. It can be important to use an average cost price that takes into account these purchasing price changes over time.

Profiteer provides the option to use a average cost price (AVCO) calculations for merchants that need this kinds of accounting support. The formula and its pattern of use will be explained first, followed by an explanation of how inventory levels may be affected.

The AVCO Formula

To illustrate how this change works we will take an example product that has a current Shopify inventory quantity of 10, with a current cost price of 10.00. Assume no items are in any customer carts for now. The merchant purchases 20 items to add to the inventory quantity of this product, with a cost price slightly higher at 12.00. The cost price value to use for all future sales is found from a formula taking the total cost of inventory over the total number of units. In this case the new cost price will be found as:

(10 * 10) + (20 * 12)
_____________________
      10 + 20

      = 340 / 30
      = 11.33

Simple Export/Import Cost Price Updates (No AVCO)

When a merchant exports COGS using the dashboard button Export COGS, a CSV is compiled of every variant in their shop, including the current inventory quantity and cost price. The merchant can change the cost prices and then import the CSV file. Profiteer App will transfer the cost price values from the CSV to each variant’s cost price used for sales. No inventory levels are changed, and the onus is on the merchant to ensure they used reasonable value for their cost prices. Manual cost price averaging is probably the way to look at this.

AVCO Cost Price CSV Exports

In Profiteer App Preferences (reachable from the App banner or the Action button on the dashboard), there is a switch to turn AVCO calculations on or off. When switched on, the Export CSV will be compiled with two new columns for use labelled as AVCO Inventory Quantity and AVCO Cost Price. The values are always zero in the Export. Any rows with zero values are ignored by the Import making these columns benign unless changed.

AVCO COST PRICE CSV Import

If AVCO is on Profiteer App will try and process each variant with the new values provided for inventory quantity and cost prices. The existing inventory level of the variant in Shopify has to be non-negative. Additionally Shopify has to be selected for the variant’s inventory management. If those two criteria are not met then only the value in the cost price column will be applied to the variant. In other words, conventional updating will take place where only the cost price is changed, matching the cost price value in the CSV (not the AVCO Cost Price value).

For variants with positive Shopify inventory quantities, a value for new inventory and a new cost price value, the formula for AVCO will be applied with all four of the inventory and cost values to produce the new weighted average cost price. This value will then be used to the variant’s new cost price.

AVCO Inventory Changes

The AVCO new amount of inventory will be automatically added to the existing Shopify inventory through the use of the built-in Delta inventory change. That means Shopify will respect the amount of and inventory item sitting both on the shelf and in any live customer shopping carts. The merchant can complete two crucial tasks at once with the AVCO process

  1. they get new inventory levels and
  2. new cost prices that make sense without the hassle of manual calculations