Overview

Cloud Retailer uses FIFO (first in, first out) costing algorithms.  The system is sophisticated in that it dynamically assigns costs as new costing data becomes available.  This article outlines a few examples to help you understand how costing is applied to sales transactions and how it calculates current costs.  Descriptions of reporting data are based on built-in standard reports.  Custom reports may not follow the same rules.

This costing method was chosen and designed at great effort and cost by our team because it is far superior to other methods for beer/wine/liquor (and other) retailers.  It represents an enormous investment to deliver an inventory system that is not matched by any of our competitors.  It's typically only found in $1 million+ inventory systems.

Procedure

If you buy 10 of a product at a cost of $5 and then buy 10 more at a cost of $6 then the first 10 sold will have a cost of $5 and the next 10 will have a cost of $6 - the costing will be applied sequentially and this ordering will be based on the date you have marked the PO as being received and date the sales transactions have occurred.  When you view your current quantity on hand you will see a weighted average cost.  For example:

• If you viewed the cost after 1 was sold you'd see a cost of $5.53     (9*$5+10*$6)/19
• If you viewed the cost after 9 were sold you'd see a cost of $5.90   (1*$5+10*$6)/11
• If you viewed the cost after 12 were sold you'd see a cost of $6      (0*$5+8*$6)/8

If you sell products before receiving a PO (the date received on the PO is after the date of the sales) but there was no receipt of products that occurred prior to this, then the PO costing will backfill these sales provided the date the PO is set to be received is prior to the date of the sales transactions.   If there is a gap where quantity on hand has gone negative (there is no PO received during that window) it will use the best information available (typically the last cost a product was received at).

LAST COST

Anytime you view a report that shows you last cost, if the report contains per location/store data then the last cost figure will be per location.  If there is only one row of data per product (not per location) then the last cost will be for the entire enterprise.  The last cost represents the last purchase order or transfers that the product was received on (pending POs and transfers do not count).  The cost value inside the product properties is the weighted average cost (as referenced above) which is different than last cost.

INVENTORY COUNTS

Conducting a physical inventory count in your store creates a milestone in the system for your quantities.  If you make any adjustments prior to the inventory count (like receiving a PO that's backdated) - the quantity on hand value after the inventory count will not change but the value of the inventory adjustment will.  For example:

• If you had 10 in stock on January 1 with a cost of $8
• and on January  15 you took an inventory count and stated that you had 5 in stock, this would create an adjustment of 5 * $8 = $40 of adjustment
• Then on January 20 you realized that you forgot to input a PO into the system from last week and you create one that is backdated to January 14 and state that you received 4 - the adjustment for the inventory count would change to 1 instead of 5.

TRANSFERS BETWEEN STORES

Costing assigned to the products moved between stores through inventory transfers works in a similar way in how it dynamically attaches costs to products.  The oldest items at one location will be the first to move to the second location and the specific costs associated with those products will be attached as the inventory moves to the new store.

RECONCILING INVENTORY  

This section also answers:

What is "Total Past Adj" on the Detailed Inventory Transaction Report?

What is "Total Est Cost" on the Inventory Value Report?

In a perfect world, with any system, you'd be able to easily reconcile your inventory values.  The equation from a high level would be:

Starting inventory PLUS received purchase orders MINUS cost of goods sold PLUS/MINUS inventory transfers PLUS/MINUS other adjustments to inventory - and this is true in Cloud Retailer with a major caveat: negative inventory levels.

Again, going back to a perfect world you'd never sell an item that wasn't received and therefore your quantity on hand would never go negative.  Our conclusion is that this happens, depending on how well you manage your inventory, for some it's regular and for even the most scrutinizing retailer it still happens occasionally.   In order to make this balancing formula work we'd have to only allow the cost of these negative transactions to be $0 - this would have limited the systems ability to facilitate returns to vendors properly (IE if the refund price is different than what you paid) AND would skew cost of goods sold in a way that might be mathematically accurate but not usable for many of our users.  

To restate the problem, in cases where product is sold, goes negative, and stays there for whatever reason there is a void of data that relates to true cost.  We fill that void with the best information we have, which is typically the last cost.  By applying last cost to these events it creates an imbalance; we've made product or cost appear out of thin air.  This creates a "debt" that needs to be repaid and this is where "Total Past Adj" and "Total Est Cost" come into play.

Here's an example:

• Starting inventory: 3 at $8
• 4 are sold, creating a debt for 1 item.  New on hand is -1, last cost is $8.
• 4 are received at $7 - a debt of $1 is repaid (8-7=1).  New on hand is 3.
• 6 more are sold creating a debt for 2 items.  new on hand is -2, last cost if $7.
• 1 more received at a cost of $5, a debt of $2 is repaid (7-5=2).  New on hand is -1, meaning there is one more debt remaining to be repaid.  You can see how this plays out in the Inventory Transaction Report (orange are the debts).

• In a scenario where we don't have real costing data, the inventory value report uses the last cost ($5) for extended cost because it is simple and likely accurate.  For "Total Est Cost" which is used for reconciliation purposes we are evaluating the cost for the two that remain outstanding as $7 since the receipt of $5 only satisfied the 1 of the 3 that were negative when the assumed costing was $7.

You can use the "Extended cost" as the value of your inventory, it's likely more accurate than Total Est Cost however if you want to be able to reconcile your inventory costing data when you have some products going into negative territory then you'll need it.

Notice that the sum of "Total State Change" (in red above) balances to the Total Est Cost column in the inventory value report.

========

All that said - an easier solution may be to filter out negative items from your value report.  If you do indeed want to use the negative costing estimation algorithms you must enable it, and by doing so, your inventory value reports will take approximately 2X as long to run.  To do this go into the Global Entity Settings of your instance > Inventory > Inventory Cost - days of full calculation and set this value large enough so that it goes back to the last point in time all of your products were counted.  By default, this will be off (set to zero).

References