Copy
News and tips for Sage 50 (formerly Peachtree), Excel, and whatever other general nerd knowledge I think will help computer users be more productive.

IQ Accounting Solutions LLC

March 2022 News & Tips

 
NEWS   SAGE 50   EXCEL
Reminder that Sage 50 2019 and older cannot be reinstalled, even with a traditional perpetual license.   Do you have a customer who is also a vendor? Here is how you can clear the customer's balance in A/R against the vendor's balance in A/P.   Want an Excel formulas that check for certain conditions to be met? Use the IF() function to evaluate multiple criteria by combining it with AND() or OR().



News

Warning for users on versions 2019 and older: This is a reminder about a serious issue I told you about in January that affects all users on Sage 50 versions 2013 - 2019. Those versions of Sage 50 use the Pervasive PSQL Workgroup Engine behind the scenes to manage Sage 50's database. The publisher of Pervasive has announced they will no longer support older versions of that product, even to the extent of not issuing activation codes to licensed users who reinstall. That means if you reinstall versions 2013 through 2019 of Sage 50, or install it on a new computer, the software will not activate, rendering Sage 50 unusable.

This does not mean that Sage will shut off your software. As long as you don't need to reinstall, you'll be able to keep using your existing version. But there are times when the solution to a problem in Sage 50 is to reinstall. And we all know that replacing your computer doesn't always happen on a planned schedule.

Upgrade to Sage 50 2022 and save 20% off list price. Traditional perpetual licenses are still available. Your purchase includes 1 year of updates so you will also receive version 2023 when it releases this summer. Call or email for pricing or questions. Visit my "new features" page to see what features have been added since your last upgrade.

Steve Collins
IQ Accounting Solutions LLC
918-851-9713
scollins@iqaccountingsolutions.com
www.iqaccountingsolutions.com


A complete archive of these tips is available at http://www.iqaccountingsolutions.com/blog
 
 

Sage 50/Peachtree Tip - How to Offset a Customer Balance Against a Vendor Balance

If you have a customer that is also a vendor, you may want to apply their A/R invoices against their A/P balance. To do that, first you will need to set up a new GL account. Then you have a simple two step process to clear both balances.

First, set up a dummy cash account that will be used as a clearing account. Go to MaintainChart Of Accounts. Enter a number for your new account in the Account ID field. I would recommend giving it a number that falls at the end of your range for cash accounts. Enter a description such as A/R - A/P Clearing Account.  Leave the Account Type set to Cash and click Save.

Next, enter a cash receipt to mark the AR invoices as paid.  Go to the Tasks menu and choose Receive Money. Change the cash account to the clearing account you just set up. Enter the customer ID and check the invoices to be paid just like you would if you had received a check. You will need to enter something in the Reference even though there is no check number; enter something like “trade” or the AP invoice number you’re offsetting this against. The payment method isn’t important in this case, but you could set up “Trade” as a payment method if you want to by going to Maintain – Default Information – Customers – Pay Methods.

Finally, enter a payment to clear off the AP invoices. Go to TasksPayments. Change the cash account to the clearing account, enter the vendor ID, and check the invoices that you want to offset against receivables. You can enter a partial payment amount if needed to get the total to exactly match the amount of receipt from the previous step. Enter a dummy check number before saving the payment.

The cash receipt will debit the clearing account and the payment will credit it, resulting in a zero ending balance. A non-zero balance in the clearing account always means that something has been entered incorrectly or is not complete.

One important note is that both the Receipts and Payments windows remember the last cash account that was used, so be sure to change it back to your regular cash account next time you enter a transaction. Or, better yet, open an existing receipt and payment and re-save them before closing the windows to reset the cash accounts.





 
 

Excel Tip - Using IF() with AND() or OR()

Excel’s IF() function, in case you aren’t familiar with it, allows you to do one thing if a chosen condition is met, but do something else if that condition is not met. For example, when evaluating test scores you might want to display “Pass” if the score is 80% or better, but display “Fail” if the score is below 80%.

IF is an extremely useful function, but it is limited to evaluating one condition. What if you need to evaluate multiple conditions? That’s where the AND() function and the OR() function come in. AND and OR are each able to evaluate up to 255 conditions (Excel calls those conditions arguments). AND() will give the result “True” only if all of the conditions (arguments) are met, otherwise the result is “False”. But OR() will give the result “True” if any of the conditions are met.

By inserting an AND formula or an OR formula into the section of an IF formula where it would normally evaluate one condition, you can effectively evaluate multiple conditions.

As an example, let’s build a formula that will calculate a 10% commission only if an invoice has been paid and the margin is at least 20%.
 

  A B C D E F
1 Invoice # Sales Amount Cost Margin Paid? Commission
2 1001 332.00 299.00 9.9% Yes -
3 1002 785.00 550.00 29.9% Yes 78.50
4 1003 6,644.00 4,186.00 37.0% No -
5 1004 6,818.00 5,114.00 25.0% Yes 681.80


First let’s look at two simple examples that we’ll be able to combine to build the formula we need.

If there was only one condition that needed to be met, such as that the invoice must be paid, then we could use the following formula in cell F2:

=IF(E1=”Yes”,B2*0.1,0)

Remember that each “argument”, as Excel calls them, is separated by a comma. So, in plain English, the formula says IF cell E1 = Yes, then multiply the contents of cell B2 by 0.1, if not then the commission is 0.

Since we have more than one condition that needs to be met, we want to replace the criteria from the above formula, E1=”YES” , with a simple formula using the AND function.

If we were to enter the formula =AND(D2>=0.2,E2=”Yes”) into cell F2 the result would be FALSE. The invoice is paid (E2=”Yes”) but the margin is less than 20% (D2>=0.2). If we had used OR instead of AND with the same arguments the result would be TRUE since OR only requires one of the arguments to be met. With either AND or OR you can include up to 255 arguments. Just list all of the arguments (your conditions) between the parenthesis and separate them with commas.

To get the commission formula for this example, all that’s left to do is to take the first formula =IF(E1=”Yes”,B2*0.1,0) replace the single condition E1=”Yes” with =AND(D2>=0.2,E2=”Yes”). When you put them together looks like this:

=IF(AND(D1>=0.2,E1=”Yes”),B2*0.1,0).

In plain English that’s: If cell D1 is greater than or equal to 0.2 and cell E2 = Yes,then multiply cell B2 by 0.1. If not, then the result is 0.

Remember that you don’t have to write formulas from scratch. If you go to the Formulas tab and click the Insert Function button, you can choose a function and it will give you a fill-in-the-blank form to help you get everything entered in the right order.




 

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 newsletter@iqaccountingsolutions.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.

Steve Collins
IQ Accounting Solutions LLC
918-851-9713
10611 E 17th Place
Tulsa, OK 74128
 
Copyright © 2022 IQ Accounting Solutions LLC, All rights reserved.
Email Marketing Powered by Mailchimp