Inventory - How Retail Express calculates Cost of Goods Sold (COGS)

Find FAQs and related articles at the bottom of this page

In order to accurately calculate costs, Retail Express applies a weighted average model of calculations. This weighted average occurs automatically as transactions are created and processed. If Retail Express were to use just the last received price, the costs would not accurately reflect the costs of all of the units in stock.

As transactions are processed the COGS value will update accordingly:

New Sale created COGS is set as the price at the store where the order is created
Sale is Fulfilled

Cost of Goods updated as the price at the Outlet fulfilling the goods

Note: this average is weighted if there are partial fulfilments at different costs

Transfer is created

Cost of the incoming stock is the current Average Weighted Cost at the Outlet sending the stock.

Stock is received (Transfer or Purchase Order)

Cost at the Receiving Outlet is updated based on the cost of the incoming Stock (in local currency)

Note: Gross Profit is calculated based on the Cost of Goods Sold (COGS) and the Sell Price set against each item on a sale (not the current price).

Example of COGS Calculations:

Starting with a brand new product with zero inventory (to demonstrate the increase/decrease accordingly):

  1. Product A has a Weighted Avg Buy Price of $25, and a COGS of $25mceclip0.png
  2. A PO is ordered and received at Bondi with the Supplier Ex price at $30
  3. Buy Price Ex updated for Bondi, COGS updated to $30mceclip1.png
  4. Chapel St creates and receives a PO with the Supplier Ex at $25
  5. Buy Price Ex updated for Chapel St, COGS Updated to $25 for Chapel St, remains at $30 for Bondi and the remaining outlets have averaged COGSmceclip2.pngmceclip3.png
  6. New sale is created at Oxford St, sourcing the product from Chapel St (sale unfulfilled)mceclip4.png
  7. Sale is fulfilled from Chapel St, COGS for the sale is as per Chapel St
    mceclip5.png
    Click to view full size

 

Frequently Asked Questions

If Retail Express were to use the last price from the Stock Receipt, it can potentially over/undervalue the COGS.

For example, if you have 1 in stock which was $11 and you receive another in at $10, the total cost of the 2 is $21, which averages out to $10.50 each.

If Retail Express were to use the last cost, then the last cost in that example would be $10, meaning both items at $10 is $20 total, which means your total costs for that product is $1 less than what you actually spent.

1. Ordered 10 items at $10 each, consider this “batch 1” for example purposes. Received in with a direct cost (freight) of $10 for the order.  Items are $10 each, but $10 direct cost is then divided among the 10 items.

Result COGS total value $11.00 per item, ($110.00 total COGS).
Sell 5 of those items, the Cogs on those sales will shows $11.00
You’re left with 5 on hand, still at $11.00 each ($55.00 total COGS).

 

2. Order & Receive 10 more of the same items, this is batch 2. This time items are $20.00ea due to a price rise from the supplier and $20 freight. For this purchase order (batch 2) in isolation, the COGS per item is $22 (buy price + (direct cost/qty) as each item receives $2 of the direct cost, this will show on the PO details.

Now, if you had since sold out of the first batch at the original price, you’d now have 10 new items in stock from batch 2 alone, at $220 total COGS ($22 each x 10). This is what would occur if you had sold all other stock on hand of this item at the old price (or adjusted them out, stocktake, etc). If there is no “weight” left to calculate against, then the new batch would come into stock at the values as per that new purchase order.

 However, we still have 5 in stock $11 COGS each from batch 1 to be amalgamated with batch 2 in order to achieve accurate total WAC for 15 items:

 

The calculation is effectively:

 (Qty Remaining from Batch 1 x Batch one COGS) + (Qty Batch 2 x Batch 2 COGS)

 (5x $11) + (10 x $22) / 15 = WAC

 55 + 220 / 15 = $18.33

 As you can see and/or expect, the value of the items now on hand is somewhere in the middle of the two separate cost prices, the weighted average of both.

 This Website can be used to test/calculate WAC per the above example.

 Outlet Specific COGS is calculated as the weighted average of the Outlet level COGS Pricing (to maintain a specific store’s own/independent COGS value. The calculation works much like the above example, but each store has it’s own independent WAC to represent what “that store” has paid for items and to maintain their own inventory valuation separate to other stores.


Valid and common scenario’s here is that Store A may be geographically much closer to the supplier of a product, than say, Store B. Store B accrues a higher freight charge then Store A and so can expect a higher WAC as compared to Store A. This could also occur if one store simply buys in greater quantities than another, and thus receives as supplier order discount due to the volume purchased. “That store” stands to benefit by way of a lower WAC compared to the other store.

 Example: If you have a quantity of 3 at one Outlet with a COGS of $10, and another quantity of 3 available at a second Outlet with a COGS of $12, the global total QTY is 6, with a COGS of $11.

 3 at 10, plus 3 at 12, divided by the total qty.

 (3 x $10) + (3 x $12) / 6 = Weighted Average COGS $11.

When an outlet sells a product, it’s their store specific COGS that is stamped on the sale.

Outlet specific pricing is also supported for POS Price/Sell price. This is to allow for stores to sell at different prices (again for various reasons but often to cover additional freight costs to supply the goods to their store). This is the “pink” pricing in Retail Express back office. Whilst it’s common to add/remove this outlet specific pink POS pricing, it is rare, and not recommended, to override the calculated buy ex/direct costs against a product that has already been received in the system. Users should tread with caution and be aware that doing so removes record of the actual weighted average cost price that the system had determined.

Only if a significant error was made on the receipt process and needs amending is this suitable. Standard practice for “price changes” is to change the Supplier Buy price which is used for future purchase orders. Else, by changing Buy Ex to represent the new supplier price, you’re also updating the COGS of the items “already on hand” even though they were rightly purchased at the previous supplier price.

Want more information? Find related articles here: