We are having a problem with our inventory and COGS where it seems these accounts are updated with adjustments that cancels out the COGS portion for our sales and DR’s back the amount to Inventory. When we sell merchandise, Sales and A/R are posted properly. The corresponding Inventory and COGS are also posted properly. However, another entry is posted to Inventory and COGS that reverses the cost of the merchandise dollar for dollar. This does not happen all the time but is does occur routinely. The G/L source Code reads: INVTPCOST. I looked at the Business Posting Group in the General Ledger Set Up and noticed the “Inventory Adjust. Account” is the same as our COGS account number. Should this account be a different interim account? Also, for the corresponding DR posting into the Inventory account, should this post to the same interim account as the CR to COGS? What is this entry? Why is it posting? Please let me know if you need additional info.
Hi Skolnick, If you are an Attain Database, there are two elements important here. Firstly the costing method which you are using in the database and secondly whether you have activated the “Automatic” and “Expected” Cost posting fields in the Inventory Setup. Please check up with you setup for the following accounts, Inventory Adjustment (should be different from COGS), Interim Accounts for Inventory, COGS and Accrual (if you activated the expected costing feature)
Hi Skolnik, it would apear that your Posting Groups have been setup incorrectly. You need to sit with your NSC and get this resolved ASAP. Unfortunately the only way to fix this is going to be a lot of manual adjustments. Just to do a basic test, you need to run two reports : “Inventory Valuation” and “Inventory to G/L reconciliation” if these reports do not agree, then you have a setup problem.
Hi Rohith, Thank you for the response. I checked the setups and found the following: The “COGS Interim” account DOES post to the COGS account! The “Inventory Adjustment” account DOES post to the COGS account! The “Inventory Accrual Interim)” account posts to an inventory account (not the main inventory account). Automatic and Expected Posting boxes are both checked. We also run Period Adjust Cost - Item Entries and Post Inventory Cost to G/L periodically (I think daily now) from the Inventory - Periodic Activities menu. When a sale is posted, how should the cost flow through the accounts? I feel that inventory is CR and an interim account is DR. Then the account is CR with the corresponding DR flowing to COGS. If this is the case, then I need to change these accounts right away (obviously I do need to make this change…). This confusion is starting to clear up. Please let me know. Thanks.
Hi David, I did speak with my NSC and he corresponded w/Microsoft to resolve this issue. Unfortunately it is still not resolved and therefore I am turning to your guidance which has helped in the past for another very similar issue. The Inventory Valuation and Inventory Reconcile reports do not agree. We attempted to balance the reports but had no luck. Instead, I want to have the flow of cost record properly for now then attempt to balance the two reports. I have found this forum very useful for problems and issues. Thank you.
I am sure that we can give you a lot of guidance and direction, but also be aware that the setup and configuration of Inventory is different for every implementation, so please make sure you discuss with your NSC before makeing changes. The first piece of Advice I can give you, is to turn off Automatic inventory posting, at least until everthing is resolved. OK, now to get started, a few questions: Version : Service packs / hot fixes : Manufacturing: yes/no Assembly BOMs: yes/no How many Item ledger entries: Has the database been upgraded: If so from what version: Costing method(s) : Exact costing in Returns:
Hi Skolnick, Additionaly I would also suggest you to look into the archives of this forum. I remember many such issues raised previously by members. You could put a search on “costing” and can find some useful info regarding the same. Cheers,
HI Skolnick, Intially, if the Inventory and COGS are posted correctly then the Setup may be correct. But this reversal happens because of your Costing Method and the Cost application. Do you have any inventory stock which are valued at 0 cost. If so when you run the Adjust cost Item entries and Post inventory cost to G/L the system follows the internal table called Item Application entry. Every Outbound (Sales, Neg. Adjmt, Transfer Out) will always be applied to a an Inbound Entry (purchase, positve adj, transfer In). If your Inbound entry cost is 0 and the outbound cost also ought to be 0. Although intially system post it with the standard cost or the Current average cost at the time of posting this will be readjusted as explained above. Look in the item application entry for the reversed value entries.
Good morning David, Rohkam and Prashcool, In response to your questions I’ll start with David. Version : 3.60 Service packs / hot fixes : Did not see anything in the About screen in the Help section. Manufacturing: yes/no No - Not a manufacturer Assembly BOMs: yes/no Yes, we do have BOM’s How many Item ledger entries: Do not understand question but think this is what you are asking: For a sale, Inventory and COGS have two entries each. The Inventory entries are one DR and one CR for the same amount, the COGS account has two entries. The COGS entries are a DR and A CR. All the DR’s and CR’s for both accounts are the exact dollar amount - the cost. If there are more than one item, the entries are multiplied by the number of items invoiced! Has the database been upgraded: Do not think it has been. This is fairly new software. If so from what version: N/A Costing method(s): Average Costing Exact costing in Returns: What does this mean? Rohkam: I just found the Search feature to look at old posts. Normally this is the first step I take, but I did not think there was a search feature in this forum. By your mentioning there was a Search feature, I found it. Thank you. Prashycool: For one of the transactions in question I looked at the Item Application table. Applied Quantity, Quantity, Invoiced Quantity were all the same negative amount (3 units were sold, -3 units showed up in these areas.). This was also a drop ship. Refering back to the earlier post about Interim accounts, should the incorrect DR to Inventory and the incorrect CR to COGS post instead to a different Temporary (Interim) account? This sounds so familiar to the earlier problem I had with an Interim A/P account that you helped me with. The steps taken to clear up that problem seem to be what need to be done for this problem. Thanks for your help!
Hi Skolnik (do you have a name), Firstly we can see an immediate problem, BOMs and average costing. AH what a wonderfull combination. On the topic of Item ledger Entries, that question is more to define the size of rthe problem. Go to File–>Database–>Information–>Tables - Find table 32, and see how many there are. The reason is that if there are not too many it may not be too late to manually fix things. Any way, the big think about average costing, is that it is based on Invoiced Quantities, whereas all other Navision costing methods are based on Quantity, so the first step is to see if this is just an issue of haveing too many open receipts. TO check this, create a test database, a copy of the live system, and post all purchase invoices and sales Returns, then run all the inventory adjustment routines, and see if the reports are still out of balance. Second problem is that Navision Assembnly BOMS and Average costs do not work together. So this is more likely what is happening. In brief, BOMs basically do no roll up average costs to the assmbled itme as you might expect. Your NSC should explain this to you in more detail. You can test this youself. Just create (on your test database) a BOM with two lines, create a purchase order for these new items, and recieve them both with a bogus PO cost, eg $99,999. Create a BOM and sell it. Then post the purchase invoce at (say) $10 per item. Now run the adjustment routine, and you will see that COgS is way out. (PS: there is code in the Item Adjustment code unit that should address this issue, but I don’t see that it works for Average cost.) If you are having this problem, there are three fixes, one is to move to Standard costs, the other is to move to manufacturing the last is to stop using BOMs. Check and let us know if this is happening.
Just as I thought, the culprit was posting groups! This was similar to the other problem encountered with the Interim A/P activity. I ran a few transactions through on the live database, made two corrections and it seems that everything is posting properly. Thanks for your help! Louis
You are welcome. [:)]