Posting GM Warranty Payments With Excel

(<< Continued from previous page)

Step #3: Copy, Paste, and Post
1. Now, once we've verified that the total of Column C matches the total we've been paid on this memo (it's the amount on the "Claim Memorandum Recap" page, labelled "TOTAL NET CREDITS"), we want to select all our payment data:

2. Click EDIT --> COPY. 3. If you don't already have it, download my Excel GM Memo Posting Template.

4. Fire up the GM Memo Posting Template.

5. Make sure cell A1 (the bright yellow one) is selected. Your screen should look like this:

6. In the GM Memo Posting Template, click EDIT --> PASTE and paste your payment data into cell A1 of the template. Now your screen should resemble:

7. All that's left to do now to allow Excel to create our posting data (it's in Column I) is for us to fill the cells of Column E in our template with the necessary account(s) to which we want each payment line to post. Easy peasy!

For instance, in the screenshot above, I'd fill cell E2 with my PDI receivables account, as that is a PDI payment (denoted by RO number beginning with "A"). I'd fill the other cells in Column E with whatever warranty receivables account is appropriate. (If you're up to it, I describe how you might build a formula to take care of this task automatically.)

NOTE: If your AGJE is set up to post payments to a single warranty account by default, you'll need to use another version of the spreadsheet. Right-click this link, and select "Save Target As" to download the alternate version, which omits the account inputs.

8. If you need to change a control number (e.g., your PDI payments are probably controlled by some portion of the VIN number rather than by RO number) in any row(s), simply type over the auto-generated control number in Column G.

9. In the following screenshot, I've filled in all the accounts and made my necessary control-number change in cell G2:

Notice how Column I now looks a lot like what you'd be posting in accounting/AGJE in "Expert Mode."

10. Select the data from Column I, like so:

11. Click EDIT --> COPY. (Or right-click and COPY.)

12. Open up your ADP window, either in Reflection or w.e.b.Suite. Go to accounting (AC) and select function AGJE.

13. Select UPDATE.

14. Select COLUMN/ROW MODES.

15. Enter your company number, journal, date, and other header info as necessary.

16. THIS IS IMPORTANT! When you enter the data-entry portion of the page (screenshot), press [F8] to select Row mode. Once you've hit [F8], your cursor area will change to this.

17. Click EDIT --> PASTE. (Or right-click and PASTE.)

18. When Reflection has finished pasting the data, enter your offset account, the debit (the total amount paid to you on the claim memo), and your manufacturer's control number.

19. As long as your posting balances, press [F5] to post.

You're done!

Continue to "Useful Tweaks and Functions" >>