Posting GM Warranty Payments With Excel

(<< Continued from previous page)

Additional Info: Useful Tweaks and Functions for Warranty Templates
Obviously, different dealerships handle warranty payments in different ways. In my case, for instance, payments from GM claim memos could go to any of four accounts. Thus, building a one-size-fits-all spreadsheet to handle warranty posting is a tough task.

However, if you have a decent working knowledge of Excel, there's no reason you can't build a spreadsheet similar to my GM Memo Posting Template (screenshot below) that will get your particular job done.

One of the tweaks that I added to my personal-use version of the GM template was the ability for it to determine the correct warranty account automatically. There are two ways one could go about this:

(1) Create a formula that checks both the RO number (Column A) and the VIN (Column B). If the first digit of the RO number (Column A) is an "A" then it inserts account 26111 (my GM PDI receivables account) in Column E. If not, then if the third digit of the VIN is a "6" or a "Y", it inserts account 26301 (my Cadillac warranty receivables account). Otherwise, it inserts account 26300 (my GM miscellaneous warranty receivables account).

More specifically, the formula in E2 would read as such:

=IF(LEFT(A2,1)="A","26111",IF((OR(MID(B2,3,1)="6",MID(B2,3,1)="Y")),"26301","26300"))

(2) Again, create a formula that checks both the RO number (Column A) and the VIN (Column B). If the first digit of the RO number (Column A) is an "A" then it inserts my PDI receivables account into Column E. If the RO number begins with anything else, then Excel checks the third digit of the VIN against a VLOOKUP table (screenshot) in another worksheet, and returns the correct warranty receivables account for that carline. If it can't find the VIN's third digit in the VLOOKUP table, it just inserts a default warranty account into Column E.

In this case, your (more complicated) formula might look like this:

=IF(ISBLANK(A2),"",IF(LEFT(A2,1)="A",Setup!$D$4,IF(ISNA(VLOOKUP(MID(B2,3,1),Setup!$C$6:$D$12,2,FALSE)),Setup!$D$5,VLOOKUP(MID(B2,3,1),Setup!$C$6:$D$12,2,FALSE))))

As a bit of further explanation, the function "MID(B2,3,1)" returns the third digit of the VIN in cell B2. The array designated by "Setup!$C$6:$D$12" is my VLOOKUP table showing the VIN digits and their corresponding warranty accounts. As the screenshot shows, my PDI warranty account is contained in "Setup!$D$4". My default, catch-all warranty account is contained in "Setup!$D$5".

It's worth nothing that I don't "lock" the cells in Column E, however, since a GM payment could also conceivably go to my extended-warranty receivables account. (A GMPP contract payment, for instance.) There's no way to determine a payment of this type by analysis of the RO number or VIN. I simply have to make this change by hand if needed.

I also use a formula which allows Excel to differentiate between PDI payments and normal warranty payments and automatically generate the correct control number. From the GM claim memo, if the RO number begins with "A", then we know it's a PDI payment. Where I work, such PDI payments are controlled not by the RO number, but by the last 8 digits of the VIN. My formula in cell G2 looks like this:

=IF(ISBLANK(A2),"",IF(LEFT(A2,1)="A",MID(B2,10,8),A2))

I'd then use Auto Fill to insert it throughout Column G.

Needless to say, if you're interested in constructing and tweaking your own Excel warranty-payment spreadsheets — and you probably should be — then you'll want to use Excel's Help feature (as well as the internet) to familiarize yourself with the following functions:

CONCATENATE

LEFT

MID

IF

OR

VLOOKUP

[Quick 'n' helpful site: Excel 2003 Text Functions]

Get a handle on those five functions, and you'll be well on your way to warranty-posting Nirvana!

<< Return to Introduction