Well, there seems to be a considerable amount of confusion out there as to whether or not a report exists that can be used as a subsidiary ledger to tie out to the Accrued Purchases account in GP. I'm happy to say - yes, there is! In fact, there is a Received not Invoiced report (the old, ugly Report Writer type) and a Received not Invoiced SmartList (found under the Receivings Line Items folder and called 'Shipments Received but not Invoiced'). I've used both reports and they will in fact support the balance in your Accrued Purchases account, given the following -
1. You must drive a stake in the ground and reconcile this account, even if it means making a general journal entry to make a one-time adjustment to the account balance. This is required because many times, when importing beginning balances and open receiving transactions during data migration, there's a tendency to just not get this right. Beginning balances get imported with no matching receiving transactions and receiving transactions are imported with no matching GL transaction.
2. If you're using the Received not Invoiced report (Reports>Purchasing>Analysis>Received/Not Invoiced), you should be in good shape. However, if you want to use the RNI SmartList, you'll need to make a couple mods to the OOB (out-of-the-box) list.
First, add the 'Extended Cost' field to the OOB SmartList. This doesn't quite provide what we need, but gets us close. Dump the SmartList into Excel and then enter the following formula into the Extended Cost field: (QTY Shipped - QTY Matched - QTY Rejected)*Unit Cost. This will result in an accurate Received not Invoiced value for the line item. Copy this formula to the other records in the report, add a total, and voila - you have your RNI report in Excel format.
The reason we have to modify the extended cost field is because that field in the SmartList doesn't account for the 'QTY Rejected'.
Compare the total on your RNI SmartList to your RNI report. They should match. If not, then analyze the differences. There's probably an error in your SmartList definition or formula entry in Excel.
Once you're confident that you have the right number, compare this to your Accrued Purchases account total. If necessary, post a general journal entry to tie your account out to the RNI report. Now,if you really want a useful report, replicate this SmartList in SQL Reporting Services or Crystal. You can avoid the Excel dump.
Then, and this is critical, especially in a high-volume environment - reconcile this account DAILY to your SmartList and/or report. Any discrepancies can be spotted and corrected easily. Trust me, it's a nightmare if you have to analyze weeks or months worth of transactions.
A reconcile a day will keep the auditors away :)
Enjoy!
PS, I'll get better at this blogging stuff as time goes on with images and other cool stuff. Please be patient with me!
1. You must drive a stake in the ground and reconcile this account, even if it means making a general journal entry to make a one-time adjustment to the account balance. This is required because many times, when importing beginning balances and open receiving transactions during data migration, there's a tendency to just not get this right. Beginning balances get imported with no matching receiving transactions and receiving transactions are imported with no matching GL transaction.
2. If you're using the Received not Invoiced report (Reports>Purchasing>Analysis>Received/Not Invoiced), you should be in good shape. However, if you want to use the RNI SmartList, you'll need to make a couple mods to the OOB (out-of-the-box) list.
First, add the 'Extended Cost' field to the OOB SmartList. This doesn't quite provide what we need, but gets us close. Dump the SmartList into Excel and then enter the following formula into the Extended Cost field: (QTY Shipped - QTY Matched - QTY Rejected)*Unit Cost. This will result in an accurate Received not Invoiced value for the line item. Copy this formula to the other records in the report, add a total, and voila - you have your RNI report in Excel format.
The reason we have to modify the extended cost field is because that field in the SmartList doesn't account for the 'QTY Rejected'.
Compare the total on your RNI SmartList to your RNI report. They should match. If not, then analyze the differences. There's probably an error in your SmartList definition or formula entry in Excel.
Once you're confident that you have the right number, compare this to your Accrued Purchases account total. If necessary, post a general journal entry to tie your account out to the RNI report. Now,if you really want a useful report, replicate this SmartList in SQL Reporting Services or Crystal. You can avoid the Excel dump.
Then, and this is critical, especially in a high-volume environment - reconcile this account DAILY to your SmartList and/or report. Any discrepancies can be spotted and corrected easily. Trust me, it's a nightmare if you have to analyze weeks or months worth of transactions.
A reconcile a day will keep the auditors away :)
Enjoy!
PS, I'll get better at this blogging stuff as time goes on with images and other cool stuff. Please be patient with me!
8 comments:
Awesome summary, just what i needed! Thanks a bunch.
Glad it helped!
It looks to me like even closed POs appear on this SmartList, so how can I use it to reconcile?
Bud, there are a number of reasons this can occur, mostly related to improper transaction processing. Check out this link on the GP Forum addressing this exact issue - https://community.dynamics.com/gp/f/32/t/104907.aspx.
Thanks, Frank
What if the Invoice price doesn't match the unit cost on the posted receipt? It would seem that simply (QTY Shipped - QTY Matched - QTY Rejected)*Unit Cost would not give us an accurate valuation of the received by not invoiced. Because our vendors take forever to get us the invoices, prices changed and we are often invoiced at prices that don't match the receipt. How can I figure out what my valuation should be for the quantities. I'm a SQL guy, so if you could put it in terms of tables, that would be swell.
Hi Marc, the invoice cost is technically not relevant, since this report reflects receipts not yet invoiced. Once the invoice is matched and posted, the item drops off this report. Until you receive the invoice, as far as you know, your cost is the receipt cost, right?
What if there are old items in accrued purchases (received not invoiced) that were mistakenly paid another way or if they indeed were never invoiced (and will not be invoiced). I have items sitting in inventory and accrued purchases that are a couple of years old that I would like to clear out. Any suggestions how to do this?
Hi Sue, it's been quite some time since I've worked on GP, having moved into the AX world about 3 years ago. It's been even longer since I took on a cleanup of old, un-received POs. The best suggestion I can offer would be to post your question on the GP forum - https://community.dynamics.com/gp/f/32. Lots of experts there that can help as well.
Good luck!
Post a Comment