November 2018 Tip Of The Month

November 2018 News & Tips

NEWS		SAGE 50		EXCEL
Year end is almost here. Don't forget to archive you data and close the year.		Memorized Transactions - Set up templates to use as shortcuts when entering transactions.		Compare two sets of data easily by using Excel's EXACT() function.

News

Year End: The end of 2018 is drawing near. That means, if you haven't already, you need to close 2017 before you'll be able to enter anything in 2019. It's also a good idea to make an archive copy of your company file before closing the year. If aren't comfortable doing these tasks yourself, contact me at 918-851-9713 or scollins@iqaccountingsolutions.com to schedule an appointment soon. The closer we get to January 1st, the busier my schedule gets,so don't delay.

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 - Memorized Transactions

Memorized transactions are like templates that you create so that you can use them later to speed up and simplify the entering of new transactions. Memorized transactions are available for:

Quotes
Sales Invoices
Purchase Orders
Payments
General Journal Entries

I don’t know why they left out sales orders and purchase invoices. If you think they should be added, you can go to https://sagecity.na.sage.com/support_communities/sage50_accounting_us/i and make a suggestion. If enough people ask for it, Sage might listen.

There two ways to create a memorized transaction. I’ll use a sales invoice as an example.

1. Go to the Maintain menu and choose Memorized Transactions -> Sales Invoices. You will get a screen that looks almost identical to the invoicing screen. But the first field is Transaction ID. This is like all other IDs in Sage 50/Peachtree. The ID is required and must be unique so that it can identify this memorized invoice in the list. Next enter a brief description of this invoice. Then fill in the rest of the fields like you would for a real invoice. Normally you would only fill in the parts that you expect to stay the same when you use this to create a real invoice. If you have a customer that routinely purchases the same thing, you could fill in a complete invoice. But if you have a group of items that are commonly sold together to many different customers, you could enter the items but leave the Customer ID blank. When you are done, click the save button. This does not post an invoice. It just saves your work to the memorized transaction list.

2. The alternate method of creating a memorized invoice is to start in the Sales/Invoicing screen. Fill out all or part of the invoice. Click on the small down arrow at the right edge of the Copy button. Then choose Memorize for future use. (If memorizing a payment, click the down arrow next the Save button, then click the Memorize button.) You will jump to an already completed Memorized Invoice window where you can assign a transaction ID and description. You can also edit it before saving if you need to. This method would be common if you are already entering an invoice when you realize you may want to issue the same or similar invoice again. After you save and close the Memorized Invoice window, you will still need to save you actual invoice, just like you normally would.

Now that you know how to create a memorized invoice, let’s talk about how to use one to create an actual invoice.

In the Sales/Invoicing window, click the down arrow at the right edge of the List button and click the Select button. You will be given a list of transaction IDs and description. Just select the one you want and click OK. Everything that was on the memorized invoice will be placed on your new invoice. You can now edit, save, or print the invoice just like you could if it had been hand entered. Saving your invoice does not affect the memorized transaction.

If you ever need to change the memorized transaction, you can edit it in Maintain – Memorized Transactions.

Forward To A Friend

Excel Tip - Use EXACT() to Compare Cells

Have you ever been frustrated staring at two lists trying to find the differences between them? Well if you have those lists in Excel it's a simple job. If both lists are numbers you don't even need to know any special functions, just subtract the number in one column from the number in the other column and copy your formulas down. Obviously, everything that equals zero is a match. But that wouldn't be much of a tip so let's look at a function that works on numbers or text.

The EXACT() function is really simple to use. Just list the two cells you want to compare in the parentheses, separated by a comma. To compare cell B2 to cell C2, simply go to an empty cell and enter =EXACT(B2,C2). Now copy that formula down the length of your list. Every line that matches will show TRUE and every line that doesn't match will show FALSE. EXACT() will work on any data type but you are limited to comparing two cells.

I love that I don't have to remember any complicated syntax to use EXACT() but every situation is different and there are many ways to approach this task. So also consider the Go To command, conditional formatting, IF() or, in case the two lists are sorted differently, VLOOKUP().

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 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