December 31, 2008

Copying Reports in Report Writer

Well, it's been a bit of a dry spell blog-wise since I've been on holiday the last couple of weeks but I thought it would be good to end the year by getting off my lazy backside and doing something constructive while I sit here in NW PA trying to stay warm.

A couple of weeks ago, I posted a topic regarding this same subject but left out the details of how to copy an existing, modified SOP Invoice to create a modified SOP History Invoice and eliminate all the work of making the modifications in Report Writer. This method will save you a ton of time if you need to do the same thing.

1. Export the SOP Invoice package file using Customization Maintenance.
2. Open the package file using Notepad.
3. Perform a Find/Replace, replacing all the references to 'WORK' tables with 'HIST' tables. (i.e., replace SOP_HDR_WORK with SOP_HDR_HIST)
4. Change the report name in the file.
5. Save the package file.
6. Import the modified package file back into GP using Customization Maintenance.
7. Voila! Your report is modified.


I have to thank my friend and fellow MVP Mariano Gomez for showing me how to do this. It saved me hours of time versus modifying the form within Report Writer.

Here's wishing everyone a happy and safe MMIX!

December 9, 2008

Understanding and Loving your Purchase Accrual Account

Here's an update to my 3 Way Match blog below. For all the debit & credit folks, this one includes the transactions in T-account format that I wasn't able to figure out how to successfully post here.

http://msdynamicsworld.com/story/accounting/dynamics-gp-three-way-match-process-or-how-i-learned-love-my-purchase-accrual-accou

Report Writer SOP Document Mods

OK boys and girls, I'm writing this so you don't go down the same path I thought I was going to have to. I was modifying some SOP documents for a client today, including the SOP Short Invoice, SOP Blank Quote, SOP Blank Order, and SOP Blank Return, cleaning up someone else's work. My task was to perform some additional mods on these forms and complete the customization of the Blank History Invoice form, which my predecessor had started. Unfortunately, when it comes to the Invoice & History Invoice forms, you can't use the Short form for one and the Blank form for the other. The SOP Document settings require you to select either Blank, Short, Long, or Other. In other words, if you use the Short Invoice form, you have to use the Short History Invoice form.

There were a ton of mods already completed on the Short Invoice form so I was looking at having to create the Short History Invoice form from scratch - not a cheery prospect by any means. I was lamenting this with our friend Mariano and lo and behold - he had the solution! This was one of those 'been there, done that' scenarios for him. He explained a way to copy the existing Short Invoice form, rename it, and use it for the Short History Invoice form. Thirty minutes later - I was done!!

In a nutshell, you export the form you want to copy, open the package file in Notepad, make a few changes, rename the form, save it and re-import it to GP. Thanks to my buddy I'm writing this blog tonight instead of pulling out my non-existent hair with Report Writer! I'll let my man explain in detail how to do this. Mariano, you have the floor . . .

December 5, 2008

Update to the Received not Invoiced Topic

I just discovered this morning that the canned RNI report in GP does not include items that have been received and not invoiced that are on closed POs. This to me is a glaring error in the canned report. Therefore, I would advise everyone to use the SmartList RNI as it does include data from closed POs. Of course, if you're a masochist, you could modify the canned RNI report in Report Writer.

Apparently when I reconciled the Purchase Accrual account to the canned RNI for a client earlier this year, none of their purchase orders were closed.

Therefore, the recommendation in my previous post to reconcile your SmartList RNI to your canned RNI is invalid.

Happy reconciling!

December 4, 2008

Dynamics GP Three-Way Match Process

or, How I Learned to Love My Purchase Accrual Account

A significant number of clients that I’ve worked with in the past (and present) have a tough time wrapping their brains around the Purchase Accrual account and how it is interrelated with receiving and invoicing of purchased goods. This lack of clarity results in unreconciled accounts, duplicate postings to inventory and expense accounts, and an accountant’s worst nightmare if allowed to go unchecked for very long. This is an attempt to help clarify the Purchase Accrual concept and point out the necessity of keeping it reconciled with the Received not Invoiced report and/or SmartList.

In an ideal world (and we all want to live there, right?) these are the correct steps, in chronological order, for receiving goods, entering the vendor invoice, and paying the vendor. Following these steps in correct order ensures a successful three-way match process and happy GL accountants -

1. Issue Purchase Order
2. Receive goods
3. Enter invoice
4. Pay invoice

1. Purchase Order Entry

This is not a transaction per se. The PO is just a document that stores the information against which Receiving Transactions will be done – unless the receipt and/or invoice are entered at the time of PO entry, which GP allows with the proper security but surely violates segregation of duty principles.

2. At goods receipt, the accounting transaction that occurs is (using inventory as an example)

Debit (Dr.) Inventory (01-99-1300-000-00)
Credit (Cr.) Purchase Accrual (01-99-2001-000-00)

3. At invoice entry (using the Enter/Match transaction), the accounting transaction that occurs is

Dr. Purchase Accrual (01-99-2001-000-00)
Cr. Accounts Payable (01-99-2000-000-00)

4. At invoice payment, the accounting transaction that occurs is

Dr. Accounts Payable (01-99-2000-000-00)
Cr. Cash (01-99-1023-000-00)

As you can see, Purchase Accrual is just a “clearing account” used to temporarily hold the value of the goods until the invoice is received and matched against the receipt. (This concept, in the good old days, took the form of a file drawer with packing slips and copies of purchase orders stapled together and formed the basis of the - now get this - the Purchase Accrual journal entry made at the end of each month.)

Note: The Purchase Accrual account gets cleared to zero when the Invoice is posted – as long as the Invoice Quantity is greater than or equal to the Receipt Quantity. If the Invoice Quantity is less than the Receipt Quantity, a balance will remain in the Purchase Accrual account (the value of the difference between the Receipt Quantity and the Invoice Quantity) and will be cleared when the remaining quantity is invoiced.

If the remaining quantity is never invoiced, the balance in the Purchase Accrual account will be cleared if and when the Purchase Order is closed.

The Receiving/Invoicing/Payment cycle is Complete
-------------------------------------------------------------------------------------------------------------

Here’s what happens if the Invoice Receipt is not matched to the Goods Receipt using the Enter/Match transaction:

1. At goods receipt, the accounting transaction that occurs is

Debit (Dr.) Inventory (01-99-1300-000-00)
Credit (Cr.) Purchase Accrual (01-99-2001-000-00)

2. At invoice entry (using the Payables Transaction Entry), the accounting transaction that typically occurs is

Dr. Inventory (01-99-1300-000-00)
Cr. Accounts Payable (01-99-2000-000-00)

Effect – inventory is debited twice, Purchase Accrual never gets debited to clear the balance!!

Therefore, it is imperative to:

• Receiving must enter the receipt the day the goods are received so accounting will have a transaction to match the invoice to!!
• When posting the invoice, if there is a PO involved –
o Do not post the invoice until the receipt is posted
o Match the receipt quantity and $$ to the invoice quantity and $$
• Make sure your account distributions are correct
• If you don’t see a receipt to match the invoice to, immediately tell the Receiving Department so they can post the Goods Receipt transaction before you proceed!!

A FINAL TIP

Never, never, ever post a general journal entry to AR, AP, Inventory, or Fixed Assets. These all tie to sub-ledgers and posting a JE to one of these control accounts will cause them to not reconcile to their sub-ledger!!

December 1, 2008

Received not Invoiced

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!

Welcome to GP2theMax!

Well, since everyone else is doing it, I decided I might as well have some fun blogging too! Seriously, though, I'm hoping this blog will provide useful information about how to get the maximum return on your GP investment. This blog will focus mostly on day-to-day transaction processing and how to do it more quickly and effectively. We'll post lots of tips and tricks and some of the lesser-known features in GP that can really boost productivity with a minimum of new learning involved.

So, here we go. Please make suggestions as to how to improve this blog. This is OUR space. Let's get the Max out of it!