Sage 100

 View Only

  • 1.  How is average cost calculated in Sage?

    Posted 01-23-2020 16:53
    I have been working with a customer on an issue she's having in Sage and it brought up a good question that I thought I knew the answer to, but her system is somehow proving me wrong.  

    I won't spare you with all of the details of her situation, but they have Lotted items that they treat like Standard Cost items and do all receipts and production entries in a way that the unit cost of the item is always the same.  However, the average cost in the CI_Item table is different from the average cost in the IM_ItemWarehouse table.  I understand that these numbers may be different in normal situations, but if the received or produced cost of the item is always $1, the average cost should always be $1 anywhere an average cost is calculated.  

    So besides the calculation of item's extended cost divided by quantity on hand, how does Sage calculate these average costs?  Are they taking an average cost based on receipt or production history of the item that's not in inventory any longer?  Does it calculate the average costs differently based on the valuation of the item?  If it's simply that it calculates the extended cost divided by QOH, then it's not calculating correctly and we probably need to run some utilities. But wanted to be sure that's the case before we do anything.  Thanks,

    Chris

    ------------------------------
    Chris Love
    Accounting Systems, Inc. (ASI)
    ------------------------------


  • 2.  RE: How is average cost calculated in Sage?

    Posted 01-23-2020 17:07
    There's an article on that in the Sage kb which may be of help.

    ------------------------------
    - Brett
    ------------------------------



  • 3.  RE: How is average cost calculated in Sage?

    Posted 01-23-2020 17:28
    Not sure which KB, but attached are two for correcting issues related to item costs.

    ------------------------------
    Moira Goggin
    Executive Director
    90 Minds, Inc.
    ------------------------------

    Original Message


  • 4.  RE: How is average cost calculated in Sage?

    Posted 01-23-2020 17:30
    Per Sage KB 36659

    Average Cost represents the average cost of the stock currently on hand for an item. It is calculated and stored for all items (not just those defined with an Average Cost valuation). The average cost displayed in Item Maintenance is the overall average cost for the item. Items will have a different average cost for each warehouse containing stock. This "warehouse" average cost is the cost used for sale transactions if an item is valued at average cost.

    For all items other than Standard Cost items, these results can be seen by running the Inventory Detail Transaction Report.

    1. Open Inventory Management, Reports, Inventory Detail Transaction Report
      • Sort Report By, Item Code
      • Unit Cost to Use for Standard Cost Items, Historical
      • Selection Criteria, By Date
      • Starting Period, 01/01/1900
      • Ending Period, 12/31/9999
      • Select Item Code
      • Select Warehouse, if applicable
      • Click Preview (this will be a large report since it is based upon each individual inventory transaction so preview is recommended)
    2. Scroll through the pages of the report to find the total for the warehouse
    3. Take the Extension / Quantity = Unit Cost (average cost for warehouse)
    4. To confirm the item's average cost, take the total extension for all warehouses divided by the total quantity for all warehouses.

    Notes:

    • Negative average costs are not normal, but can exist under certain combinations of transactions. It is more likely to happen for LIFO or FIFO items when the quantity on hand has gone negative due to sales being processed prior to receipts.
    • Standard cost items use the standard cost entered in item maintenance for valuation reports, as well as sale and receipt transactions. This is also used to post to General Ledger. The average cost represents the average cost paid for the stock currently on hand, and may cause concern when it prints on the Stock Status report or the Inventory Inquiry screens
    • The average costs (both overall and warehouse) are updated every time a transaction is updated to inventory. It is calculated as the new value on hand divided by the new quantity on hand. If the new quantity on hand is zero, the average cost remains unchanged since there is no quantity to use in an average cost calculation.
    ​​​

    ------------------------------
    Moira Goggin
    Executive Director
    90 Minds, Inc.
    ------------------------------

    Original Message


  • 5.  RE: How is average cost calculated in Sage?

    Posted 01-23-2020 17:33
    Any repair receipts at zero costs?

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------



  • 6.  RE: How is average cost calculated in Sage?

    Posted 01-24-2020 08:34
    @Moira Goggin, your 'KB 36659' post above looks like the one I was thinking of. ​

    ------------------------------
    - Brett
    ------------------------------

    Original Message


  • 7.  RE: How is average cost calculated in Sage?

    Posted 01-24-2020 09:16
    @Moria Goggin ​Yes, that's the article I read, and that's what I understand it to be.  Let me get a little deeper into what they do and maybe that'll help me explain the issue.  As I originally stated, they have items that are assigned the Valuation of "Lot", but they treat them as if they're Standard Cost items.  They have a custom utility that piggy backs off Sage's stock Standard Cost Adjustment utility that they run at the end of each year, but what it does is not only change the standard cost for the CI_Item, but it also changes the unit cost of the tiers in IM_ItemCost for all non-zero quantity on hand tiers. 

    In the case of this one item, they have a receipt in 2019 where the received the item in at a Unit Cost of $20.65, and another receipt in 2020 using the updated standard cost of $20.53 according to the Transactions tab in Item Inquiry.  On 12/31/2019, the custom Standard Cost Adjustment utility subtracted an extended cost of $194.40 to bring the total QOH Extended Cost down from $20.65/unit to $20.53 and the lots in the Cost Detail tab both show a Unit Cost of $20.53.  

    So when Sage is calculating the average cost, where is it pulling the unit cost from?  Is it pulling it from IM_ItemCost for tier valued items, or is it pulling it from IM_ItemTransactionHistory?

    I am interested in seeing how running the hidden utility to Balance Quantity and Cost and everything that comes with it to see if that fixes some of the issues, and we certainly need to look back at the standard cost adjustment utility to determine if this is going to be an ongoing issue.  But we wanted to know for sure how the Avg. Cost is getting calculated and what values its using to calculate it with before we did anything else.  Thanks everyone!

    ------------------------------
    Chris Love
    Accounting Systems, Inc. (ASI)
    ------------------------------

    Original Message


  • 8.  RE: How is average cost calculated in Sage?

    Posted 01-24-2020 19:29
    The average costs (both overall and warehouse) are updated every time a transaction is updated to inventory. It is calculated as the new value on hand divided by the new quantity on hand. If the new quantity on hand is zero, the average cost remains unchanged since there is no quantity to use in an average cost calculation.

    Likely the IM_Cost and IM_ItemWarehouse and the hidden utility probably uses the IM Transaction History as a compare point.

    Remember the old IMBLZ1?  We used to run it to re balance IM2 and IM3 (cost and warehouse, I think if I have the order correct).  I'm sure one of our development partners could shed more light on the files used for avg cost calcs.

    ------------------------------
    Moira Goggin
    Executive Director
    90 Minds, Inc.
    ------------------------------

    Original Message


Related Content