May 2021 News & Tips
|Memorial Day, Monday 5/31
||How to handle customer down payments or prepayments.
||Time saving keyboard shortcuts
IQ Accounting Solutions will be closed on Monday, May 31st in observance of Memorial Day. I hope you enjoy your holiday weekend but please take a moment to remember those who lost their lives in military service and the families they leave behind.
IQ Accounting Solutions LLC
Sage 50/Peachtree Tip - Customer Prepayments
Do you require down payments or prepayments from your customers? I'm often asked how to record prepayments since there is no invoice that the payment can be applied against. Today I'm going to go over two ways to a deal with this and some of the pros and cons of each method.
OPTION 1: Use the Prepayment Checkbox in the Receive Money window.
This seems obvious once it's pointed out, but in that grey space between the header area and the detail lines of the Receive Money window, there's an inconspicuous checkbox that many people overlook. It's simple labeled "Prepayment" and its purpose is exactly what it sounds like.
When you check the box, the Apply to Revenues
tab will automatically be selected and the GL account will be set to your accounts receivable account (more about that in a minute). Just enter the amount and, if wanted, a description, and click Save
. On the aged receivables report or in Receive Money
prepayments will look just like a credit memo, and you apply them to an invoice in the same way as an open credit memo too. Once the related invoice has been created, go into Receive Money
and check the Pay
box for both the prepayment and the invoice it should be applied to (adjust the amount paid on the invoice to match the prepayment if the payment was less than the invoice).
Let's go back to the GL account on the prepayment. It gets set automatically to your accounts receivable account. Under accounting rules this should really go to a liability account, usually named something like Customer Deposits. So why would Sage do this? They do it so that your aged receivables report will match the Accounts Receivable on your balance sheet. And if the unapplied prepayments are a small percentage of your total receivables, it is an acceptable shortcut. But if unapplied prepayments are a significant percentage of your receivables you will want to change the GL account on each prepayment to a liability account used for tracking your total prepayments. As I just mentioned, that will result in the default Aged Receivables report not matching the balance in Accounts Receivable on your general ledger. The simple solution to that is to go into the options on the Aged Receivables report, select Account ID
in the Select a filter
section, and select your accounts receivable account number from the drop down list. This will give you a report that excludes the prepayments and, therefore matches your general ledger. Using this method you can also use the Aged Receivables report to get a listing of your unapplied prepayments by setting the Account ID
filter equal to the customer prepayments liability account.
Using the Prepayments check box is easy and convenient, but there are a couple of drawbacks too.
- Once a prepayment has been applied to a transaction, you can't open the prepayment transaction in the Receive Money window. If you try you'll get a message that says "You are not allowed to view transaction detail for a prepayment that has been applied to an invoice." It's not the end of the world, but it can be a nuisance.
- You can't record a prepayment and a regular invoice payment on the same transaction. So if a customer sends you one check that covers an existing invoice and the down payment on a new order, you'll have to record it as two separate payments.
- I have been told by people who specialize in Sage 50 data repair that, if a database gets corrupted, prepayments are difficult if not impossible to repair.
OPTION 2: Post prepayments to a liability account in the Receive Money window without using the Prepayment Checkbox.
If you don't want to use the Prepayments check box, you can still use the Receive Money
window, and just set the GL Account on the Apply To Revenues
tab to the liability account you set up to track customer prepayment/down payments. If you have to track sales tax, you will probably want to delete the sales tax code at the bottom of the Receive Money so this payment doesn't get included on the Taxable/Exempt Sales report.
Prepayments recorded this way will not appear on the Aged Receivables report and they won't appear on the Apply To Invoices tab of the Receive Money window later. So how do you apply them to an invoice? There are two choices.
One way is to use the Receive Money window. On the Apply to Revenues
tab, enter a description such as "Apply prepayment", set the GL account to the liability account for prepayments, and enter the amount as a negative. Then on the Apply to Invoices
tab, check the Pay
box for the related invoice. If you're recording a check and applying a prepayment at the same time, the Receipt Amount will equal the check amount even though the invoice is paid in full. If you are applying a prepayment and nothing else, change the Amount Paid to the amount of prepayment being applied (if it's not the full invoice amount), confirm that the Receipt Amount is zero, and save the transaction. Any remaining balance on the invoice will be available to pay later.
Another way is to include a line on the sales invoice, setting the GL Account to the prepayments liability account and entering the amount of prepayment you want to apply as a negative amount. If you track sales tax, this can make your reporting more complicated and you will probably want to set up an exempt Item Tax Type (Maintain
menu > Default Information
> Inventory Items > Taxes/Shipping
) for applied prepayments so you can easily back them out of the totals on your Taxable/Exempt Sales report. Those who use this method like it because it allows them to send an invoice showing that the down payment was applied without having to save the invoice, go to Receive Money to apply the payment, then go back to Sales Invoicing to print/email the invoice.
Regardless of which way you apply the prepayment, the benefits of "option 2" (ie not using the Prepayment checkbox) are that you can maintain proper accounting methodology and track prepayments/down-payments in a liability account without causing the Aged Receivables report to disagree with the general ledger. It also avoids any potential data integrity risks that may come with using the prepayment checkbox. Also, if you need to show the application of a prepayment as a line item on the invoice, this is the method that will allow that. The downside is that, when recorded this way, prepayments don't show up like a credit memo on the customer's account, so you need a manual system to make sure you apply the right amount after the invoice is created. And you have to manually reconcile the balance of the liability account.
Forward To A Friend
Excel Tip - Time Saving Keyboard Shortcuts
Everyone uses Excel a little bit differently, so a trick that’s a big time saver for one may not do much for someone else. But here are three (in no particular order) that I find myself using frequently. If your favorite didn’t make the list, I’d love to hear it.
Ctrl+Home or Ctrl+End moves you to the beginning (Ctrl+Home) or the end (Ctrl+End) of your spreadsheet much faster than you can using the mouse. Yes, those keys between the letters and the number pad do have a purpose! Bonus tip: combine either one with the shift key and you will select everything from the current cell to the beginning or end of the spreadsheet.
Ctrl+; (semicolon) to enter the current date. If that seems too obscure to remember, it’s corollary will help. Ctrl+: (colon,like you would use when typing the time) is used to enter the current time. So Ctrl+: and Ctrl+; are related just like time and date are related.
Alt+= will sum a column or row. If you are at the bottom of a column or end of a row, Alt+= will automatically insert the sum function for you. One warning, if there is an empty cell in the column or row, the formula will only sum the cells below (or to the right of) the empty cell.
For those of you who are completely mouse-centric, you don’t actually type the + in any of the shortcuts. Just hold down the Ctrl or Alt key while you type the appropriate shortcut key.
Forward To A Friend
If you know other Sage 50/Peachtree users, do them (and me) a favor and pass this along to them. If you are someone that this has been passed on to, and would like to be added to my email list, you can subscribe for free at www.iqaccountingsolutions.com/signup.html
or send me an email at email@example.com
. If you need to change your email address or would like to unsubscribe, you can use the links at the end of this email.
IQ Accounting Solutions LLC
10611 E 17th Place
Tulsa, OK 74128