Stock reconciliation to GL

Hi I have a problem I would like some help on. I am trying to reconcile the figure for stock in my GL to the value of the inventory. I have posted my item cost and cost to GL batches. If I run the Inventory valuation report the adjusted cost includes provisions for purchase invoices and sales invoices not yet processed. I have no sales shipments not invoiced but I do have stock received not invoiced. As at end of May the figures are as follows; Trial Balance for Stock: 149748.73 Inventory Valuation: 139454.91 (Adjusted Cost) Does anyone have any suggestions as to what the differences can be and how I can track them down? This is confusing me and I feel like I have lost clarity on the issue. I also have the figures for April: Trial Balance for Stock: 95064.17 Inventory Valuation: 61544.01 (Adjusted Cost) Theoretically the non-adjusted cost on the inventory valuation report added to the stock received not invoiced (minus any shipped not invoiced) should also add up to the adjusted cost, it does not I am running Navision 2.01 I am looking for a fresh perspective as I am pulling my hair out, any help gratefully received. Cheers Steve Steven Weaver XONITEK Systems (UK) Ltd +44 (0) 23 80641175 (Tel) +44 (0) 23 80641173 (Fax)

Questions for you: -Are you using the Inventory to G/L Reconcile report or the Inventory Valuation report? -Are you trying to reconcile with a report created on the last day of the period? -Are your inventory receipts and issues sourced from financials or are you using manufacturing? -Do you have a starting point when the G/L and Inventory reports were equal? -Do you have any customizations that might be the root of the problem? Traditional reconciliation issues are as follows: -Entries in the Inventory G/L account created using General Journal entries - these would not affect the Item Ledger -PO receipts not invoiced - no G/L entries -SO shipments not invoiced - no G/L entries Thanks, James

Hi James In answer to your questions 1. Using the Inventory Valuation report, the UK version does not have a Inventory to GL reconcile report. 2. I am trying to reconcile to a day on the last day of a period. 3. Inventory receipts and issues are from inventory, NOT manufacturing. 4. I don’t have a starting point when the GL was correct, this is a site that has been up and running since Oct 2000, and I have found items brought in at zero unit cost, so I doubt it has EVER been correct, but this is a little hard to prove, so I am taking the starting point of a previous month and trying to reconcile the net change. 5. No customisations are in this area. 6. I have taken out the general journals entered during the period. 7. I have accounted for PO recipts not invoiced 8. They invoice and ship at the same time so there is nothing here. I hope some of this helps, thanks for your time. Steve

Steve One of our clients has had difficulties reconciling stock to gl. One problem that they had was a date filter on the ‘Post Inventory Cost to G/L’ batch job. For example Running the job ‘Adjust Cost Item Entries’ the starting date is 01/04/2002 and the ending date is 31/05/2002; but when running ‘Inventory Cost to G/l’ a date filter was applied to the “Item Ledger Entry”.“Posting Date” of 01/05/02…31/05/02 which effectively prevented some adjusted costs from being posted to g/l

Hello Steve, I hope I am not stating the obvious but the Inventory Valuation report could be misleading because it does not give you a true historical position of inventory. Only Item Ledger entries that are open appear on the report. If you print the report for an earlier month, an Item Ledger entry that was open at that time could well be closed by a current month transaction. The only way round this is to print the Inventory Valuation report right on the reconcilation date. I am not sure if this is what you have done. James

Hi James This certainly is not what has happened, the inventory valuation is being run with an end date. Currently I am trying to reconcile the individual item ledger entries to the general ledger entries on a line by line basis (which is fun!). Problem is the end user wants an answer to the reason for non-reconciliation TODAY! Cheers Steve

Hi Steven, After you reconcile, please post a reply as to where the problem was??? As I see it James has covered all the cases where a difference could have arisen! Still if there is a difference it is worth finding it out and sharing it with us. Have Fun! Rohith Kamath Senior Consultant The Naviworld Group

Hi Rohith I certainly will let you know, unfortunately I do not believe the accounts have ever been in a state where they could be reconciled, and they do a heavy amount of line processing from a GL and item ledger perspective, for instance the smallest inventory posting group has 3000 entries a month. Basically I am not that confident of finding an answer, but I will keep digging! Yours in reconciliation hell Steve

quote:


Originally posted by Jamesmc: Hello Steve, I hope I am not stating the obvious but the Inventory Valuation report could be misleading because it does not give you a true historical position of inventory. Only Item Ledger entries that are open appear on the report. If you print the report for an earlier month, an Item Ledger entry that was open at that time could well be closed by a current month transaction. The only way round this is to print the Inventory Valuation report right on the reconcilation date. I am not sure if this is what you have done. James


James The Inventory Valuation report includes all ledger entries between the starting and ending date.

Hi James David is correct. My inventory valuation ends on 31/05/02. I had stock in with an open entry based in April, I transferred this to another location in June, this closed the Open April item Ledger Entry, and now the Open Entry is in June. However it still appears onteh inventory valuation report ending date 31/05/02. Still pulling my hair out Steve

Okay, an update I have selected one inventory posting group to analyse. I have matched the inventory item ledger entries to the general ledger entries. Movement in the month of May is as follows: General Ledger Entry: 6775.60 Item Ledger Entry: 4984.66 One transaction in the GL is a journal that will not hit the item ledger entry, this transaction is for 1828.94. Adjusted this gives us: General Ledger Entry: 6775.60 Item Ledger Entry: 6813.60 A difference of a mere 38.00 However if I run the inventory valuation report the movement, both adjusted and non, is 6083.02. This gives me a reconciliation difference of 692.58. Any ideas? Steve

Hi David, I am looing at report 10139 on version 2.01US and the Inventory Valuation report only shows open item ledger entries. I am guessing that the reports may be different between the two countries because Steve noted that, in the UK, there is no Inventory to G/L Reconcile report (#10138). James

Okay, an update I have selected one inventory posting group to analyse. I have matched the inventory item ledger entries to the general ledger entries. Movement in the month of May is as follows: General Ledger Entry: 6775.60 Item Ledger Entry: 4984.66 One transaction in the GL is a journal that will not hit the item ledger entry, this transaction is for 1828.94. Unfortunately compared to the previous post this is a negative entry, reducing the Item Ledger Entry: General Ledger Entry: 6775.60 Item Ledger Entry: 2155.72 A difference of 4619.88 (dam!) If I run the inventory valuation report the movement, both adjusted and non, is 6083.02. This gives me a reconciliation difference of 692.58 between the report and the ledger. Any ideas?

I suspect it is a date related problem - can you try reconciling one day at a time for this posting group? Cheers, James

Steve Can I review the basics briefly? (UK versions prior to Attain) After running the Adjust cost item entries and Post inventory cost to g/l reports then if everything is OK the following should be true. 1) You run the Inventory valuation report and find that the figure under ‘Value as of date’ is the same as the figure under ‘Cost posted to g/l’ 2) If there are receipts awaiting an invoice then there will be a line Adusted Cost (Qty.)and the figure under ‘Value as at date’ for this line less the figure from 1) will be the value of these receipts awaiting invoice. You prove this by running report 10540 Stock Received not Invoiced. If 1) is not true then it is possible that some filter has been applied to the period end programs which has allowed an ‘Adjusted Cost (Inv Qty)’ value to be calculated but not then posted to the g/l. To investigate this further you should write a report to list ledger entries where Adjusted Cost (Inv Qty)is not the same as ‘Cost posted to G/l’ and Invt. Qty. Not Adjusted is zero or add these fields to the item ledger enties form etc. If 1) is true but 2) is not then there are some problems with the handling of receipts. Some examples that I have come across are a) Goods have been received in error and a subsequent negative physical inventory adjustment has been made! b) Goods have been received into a location but invoiced at a different location. (This is only possible in earlier versions) Having different locations like this fools the Stock Received not Invoiced report. I hope some of this helps.

Hello David, Your last post proves that the US Inventory Valuation report is a very different beast to the UK version - there is no column for “cost posted to G/L”. Since I seem to be talking at cross purposes due to the differences in the product, I am going to have to “watch from the sideline” for this thread - I would be interested in the resolution however. Good luck, James

Hi David In answer to your question on the basics then 1) is not true, the cost posted to GL is not equal to the value as of figure, but the difference is very small. As for 2) the difference between the adjusted cost and the cost posted to GL is not hte stock received not invoiced total, but again the difference is only small. Having spent sometime on this I believe that the above is typical of the probelm, I have lots of small issues that add up to small differences which in total, add up to a big problem. Once I have my mind around the full problem I will detail it here. Whilst I do not believe I can reconcile the two figures I feel I can now say WHY I cannot reconcile the two figures! Thanks to everyone for everything Have a good weekend Steve

Okay Whilst this is not the complete answer on my reconciliation issue (GL to Inventory Valuation Report) I can only at this point describe the issues that have lead to problems with the reconciliation. Currently the end user, who has been a Navision User for two and a half years, are going through a long process to help get the situation under control – I hasten to add that I, and our company have only been involved with the end user for the past month! Anyway, these are some of the issues encountered that have made reconciliation difficult: 1. The initial system was set-up with the unit cost populated with zero on many items when they originally brought across the opening inventory. 2. Ever since goods have frequently been brought in with a zero unit cost, either through a purchase order or a positive adjustment. 3. The end user have never matched purchase order invoices to receipts, therefore no adjustments to the original costs (frequently zero) were ever generated, meaning no inventory or costs of sales entries were generated for certain sales. 4. Negative and positive adjustments were causing huge fluctuations in the stock. The reason was that they could buy 500 and get 100 free. They would handle this by booking in 600 and then doing a negative adjustment for 100 and positive 100 under a different item number. This would flush through normally, but some suppliers would invoice two lines, and some one, at an “average” cost (figuring in the 100 free). The processing of these items was always the same, irrelevant to the way they came in, they would do a negative adjustment at the true cost, and a positive adjustment at zero. They were unaware of any affect this was having to the Inventory/COGS accounts. 5. The unit costs have never been adjusted, however sales credits applied to sales with the unit costs of zero would also have been allocated at zero cost. 6. We had started to make adjustments to clear off the 100 page stock received not invoiced report, creating a receipt at zero unit cost, taking the existing stock out, bringing it back in at the correct unit price and making the necessary GL adjustments. Due to us only correcting the units costs of items with stock, the clean up process of eliminating all zero costs on item records was not complete. 7. Due to the nature of the business they would produce shipping documentation early, without stock, and then ship the goods as they came in. The invoices were then produced at a date acceptable to the customer (don’t ask). They did some unique alterations to the system date of the machine to fool the system (or themselves) and this tinkering with dates lead to some interesting manual journals to produce the accounts. 8. We have also discovered that what David suggested is also true, the adjust cost items entry routine was run with no date parameters, but the post cost to GL batch routine always has a monthly filter. Essentially we are pulling a piece of string and encountering further problems, at the moment we are correcting the issues as we find them to try and get them to a “true” position. Hopefully, once we get to a position where none of the strange processing affects the figures, then we will have a point we can move forward from and even, fingers crossed, be able to reconcile from!! Thanks everyone for the input, it is greatly appreciated Steve

WoW! and i thought that i had problems with inventory!! thanx for the feedback and good luck.