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

April 2022 News & Tips

 
NEWS   SAGE 50   EXCEL
Special offers for users on versions 2013 - 2019   Easily automate recurring transactions   The benefits of tables in Excel



News

Special Offers: Sage is running a promotion for users of Sage 50 versions 2013 through 2019. They are offering 50% off the first year of a Sage50 Cloud subscription. I know I talk a lot about perpetual licenses, but if you are using an older version and have considered switching to a subscription so you can always stay current, this the time to take the leap.

If you're interested, call or email me for details and pricing. The discount only applies to the first year of the subscription. Once you convert to a subscription you cannot switch back to a perpetual license.

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

Many people have transactions such as rent or depreciation that they enter every month on the same date for the same amount. There is no reason to enter these transactions into Sage 50/Peachtree by hand every time. You can easily create a recurring transaction that will do it for you.

First let’s talk about what kind of transactions are good candidates for recurring transactions:

  • It needs to be something that happens on a regular schedule. It can be weekly, bi-weekly, every 4 weeks, monthly, per fiscal period (for those who don’t use regular calendar months), quarterly, twice a year, or yearly. If you want to enter the same transaction repeatedly, but not on a fixed schedule, you need a memorized transaction instead of recurring.
  • The amounts, GL accounts, customer/vendor, etc should be the same each time, although they can be edited later.
  • Only sales invoices (AR), purchase invoices (AP), payments and general journal entries can be set to recur.

A monthly journal entry for depreciation, an AP invoice for office rent, or a payment for an auto-draft of insurance premiums would all work well as recurring transactions.

Now let’s walk through the process of setting one up.

  • Start by entering the transaction as you normally would, with the exception of one field, which I’ll come back to.
  • Once you have entered all of the information, look for the Copy button in the toolbar at the top of the window. Click the down arrow next to it and choose Create Recurring. If you are entering a Payment (including Write Checks) or if you are using version 2011 or older, just click the Recur button. The Create Recurring Transaction window will open.
  • Use the drop down list to choose how often you want the transaction to occur.
  • At Date Range the date you used on your transaction will automatically be entered as the First transaction date. You can change it if you need to. For monthly or longer frequencies, this date is the date on which it will recur in each period. For shorter frequencies, such as weekly, this date determines the day of the week on which it will recur. Then you have 3 choices for the ending date:
    End On lets you choose a specific ending date.
    End after __ occurrences lets you choose how many entries you want in the series, including this one.
    No end date will continue the transaction until you stop it.
  • Click OK and the transactions will be posted. If the end date is beyond the end of your last open year, the remaining transactions will automatically be created during the next year end close

Now back to the one field that I mentioned is an exception to how you normally enter transactions. That field is Reference/Invoice Number/Check Number, depending on which type of transaction you are entering.

  • General Journal Entry. You can enter a reference or leave it blank. If you enter one, the same reference will be used for all journal entries in the series.
  • Customer Invoices. You can enter an invoice number or leave it blank. If you enter an invoice number it will automatically be incremented on each recurrence. Or leave it blank if you want assign the invoice number later.
  • Vendor Invoices. You can enter an invoice number or leave it blank. If you enter an invoice number it will automatically be incremented on each recurrence. If you leave it blank you'll be prompted for a starting invoice number when setting the schedule in the Create Recurring Purchases screen.
  • Payments or Write Checks. Check number must be left blank

With all recurring transactions you can edit or delete them later. When you save your changes (or delete a transaction) Sage 50 will ask you if you want to update just this transaction or this one and all remaining transactions.




 
 

Excel Tip - Benefits of Tables

In the general sense, a table is just data that you have arranged into columns and rows. But in Excel, when you use the Format As Table button to define an area on your spreadsheet as an Excel table, there are several things you’ll be able to take advantage of that make entering and working with your data a little easier.

You can define a table before or after you have started entering data. If you’re starting from a blank worksheet, select the area you’ll be using for your table. Don’t worry about the number of rows, that will be adjusted later. If you already have data on your worksheet, just make sure your cursor is within the area you want to define as a table. Click the Format as Table button (It’s located in the Styles section of the Home ribbon) and choose a formatting style from the list. In the window that pops up. You can adjust the area selected for the table in the “Where is the data for your table” field. Also choose whether or not your table has headers. Normally that box should be checked. Clearing it will tell Excel that the first row in your table is data, not column headings. Click OK. You’ve just defined and formatted a table. So what does that do for you?

Easier Data Entry – When you enter something in the last column of a table, pressing the tab key will take you to the first column of the next line instead of to the next column.

Easily Locate Information – You’ll see a down arrow button on the right edge of each column heading (the header row). This button gives you quick access to filtering and sorting your table.

Shaded (Banded) Rows – If you miss green bar paper you’ll love this. Many of the table formatting options have alternating light and dark shaded rows to make it easier to follow each line. And when you reach the end of the formatted area, Excel will automatically extend the formatting as you enter data in the next row. Formatting is also adjusted if you insert rows or columns. On the Design tab of the ribbon there’s an option for banded columns if you prefer to have the columns shaded instead of rows.

Formulas Automatically Copied to Each Row – When you enter a formula in a table, it will automatically be copied to each cell in that column. It will even be placed in new rows as you add them.

Easy to Read Formulas – If you enter a formula in a table by clicking on the cells (for example, type “=” then click on cell B2, type “+” and click on cell C2), your formula will use the column headings instead of the cell reference. For example if column D is “Qty” and column E is “Unit Price” you might end up with a formula that reads =[@Qty]*[@[Unit Price]] instead of =D2*E2. Microsoft calls this a “structured reference” and “=D2*E2” is called an explicit reference. If you rename a column, the formulas will self adjust.

Flexible Total Row – On the Design tab of the ribbon (visible only when you are in the table) in the Table Style Options there’s a check box for Total Row. Check it and a row for totals will be added at the bottom of your table. By default only the last column will have a total, but click in any cell on that row and a button will appear that lets you choose a total type for that column. Choices are:

  • None
  • Average
  • Count
  • Count Numbers
  • Max
  • Min
  • Sum
  • StdDev
  • Var
  • More Functions (this will open the function wizard, letting you select from all of Excels functions)

Easy to Resize – As I mentioned before, when you get to the end of a table, entering data on the next line will automatically expands the table, including formatting and formulas. The same is true for columns. But you can also resize a table by dragging the small triangle shaped handle that’s in the bottom right corner of every table. Or, on the Design tab of the ribbon you can click the Resize Table button if you want to type in a cell range for your table.

If you’re like me you’ve ignored tables in Excel because you were already comfortable doing most of these things other ways. But when you look at everything tables offer (especially easier data entry) it makes sense to change your routine and take advantage of tables.




 

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