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

July 2021 News & Tips

 
NEWS   SAGE 50   EXCEL
Sage 50 version 2022 releases today!   Modifying the 12 Period Income Statement to work for prior years.   Using IFERROR to prevent "#N/A" and "#DIV/0!" error messages and to prevent errors from cascading into related cells.



News

Sage 50 2022: Version 2022 of Sage 50/Sage 50cloud releases today. It should show up in your updates soon as they gradually roll it out to users.

The list of improvements is short, but they will be significant to some users.
  • State Paid Family and Medical Leave enhancements
    New payroll functionality has been added to accommodate the paid family leave deductions that have recently been added by several states. More details can be found in the Sage 50 knowledgebase.
     
  • Improvements to Banking Services
    Bank feeds have improved to help simplify bank reconciliation.
     
  • Remote Data Access resiliency
    Improvements to Remote Data Access (formerly Sage Drive) to help provide an improved user experience, including the option to open a company in read-only mode when offline.
     
  • Improvements to the 1099 process
     
Call or email to schedule an appointment if you would like assistance with installing your version 2022 upgrade. Click here if you want to view the system requirements.

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 - Creating a 12 Period Income Statement for Prior Years

One of Sage 50's built in financial statement formats, "<Standard> Income - 12 Period", gives you an income statement for the current fiscal year split out into monthly columns. People are often surprised to find that it doesn't let you choose a date range so you can't use it to report on prior years. But one simple change can unlock that ability. Simply view that report on screen, then click the Save button, give it a new name and click Save.

Now, when viewing the report, you can click the Options button and select a time frame. For this report to work correctly you have to select just the last month of the 12 month reporting period as your time frame. So if are on a calendar year your time frame would be FROM 12/01/20xx TO 12/31/20xx.

The report works by defining the time period for the 12th column and each preceding column counts back one period (month) from the defined time frame. So if you select FROM 01/0/2018 TO 12/31/2018 then the last column will be equal to the entire year and column 11 would be December 2017, 10th column would be November 2017, etc. That is why the standard report simply defaults the date to the last month of the current year.





 
 

Excel Tip - Using IFERROR() to Deal With Formula Errors

You have probably all seen Excel formulas that result in an error such as #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, or #VALUE!. Usually they indicate that you need to correct something in your formula or the data it references. But sometimes the condition that produces the error is unavoidable. In those cases not only does the error look bad, it can also interfere with formulas that reference that cell.

Here’s an example of a situation in which the conditions causing the error are unavoidable. In this spreadsheet we’re calculating the percentage change in sales from year 1 to year 2. So we have to divide the change amount by year 1 sales. For Item 2, which had no sales in year 1, that creates a divide by zero error.

 

A

B

C

D

E

Column E Formula

1

 

Year 1 Sales

Year 2 Sales

$ Change

% Change

 

2

Item 1 10,000 11,000 1,000 10% =D2/B2

3

Item 2 0 7,000 7,000 #DIV/0! =D3/B3

4

Item 3 15,000 12,000 (3,000) -20% =D4/B4

One way to deal with that is by embedding your formula within the IFERROR() function. IFERROR is very simple. It’s format is just IFERROR(value, value if error). So in the above example, instead of entering =D2/B2 in cell E2, you would enter =IFERROR(D2/B2,”-“). When you copy that formula down, then % Change for Item 2 would show a dash instead of the divide by 0 error.

I chose to display a dash when my formula resulted in an error, but you could use anything you want whether it’s text,a number,or a formula. Just remember that if you to use text, you must enclose it in quotation marks.

IFERROR isn’t just about making your spreadsheet look better. It can also prevent errors in other formulas. If a formula in another cell references the cell containing an error, either directly or within a range (such as when you sum a column) the first error will cause an error in the second formula too. For example, if you tried to sum column E in the above example, your sum would display #DIV/0! instead of -10%. But avoiding the original error by using IFERROR also prevent errors in any dependent formulas.

By the way, for those of you wondering about my math skills, I understand that you would not want to sum the % changes. But that was faster than writing a second example to demonstrate my point.




 

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 © 2021 IQ Accounting Solutions LLC, All rights reserved.
Email Marketing Powered by Mailchimp