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

December 2021 News & Tips

 
NEWS   SAGE 50   EXCEL
Merry Christmas!

Schedule your year end appointment soon.

January 2022 Payroll Tax Update released.
  Easily create an income statement showing both dollar and percent change from last year to this year.   Copy and paste cells without copying the cell borders



News

Merry Christmas: IQ Accounting Solutions will be closed on Friday, December 24th for the Christmas holiday.

Year End: If you need assistance with archiving and closing 2020 so you can start working in 2022 please call or email to schedule your appointment soon.

2022 Tax Update: The January 2022 payroll tax update has been released. It should show up automatically for payroll users. But you can also check for it by going to the Services menu and choosing Check For Updates. The tax update will need to be installed before you can run payroll in January. Contact me if you need assistance with installing the update.

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 Comparative Income Statement

You can easily compare this year’s income and expenses to last year’s, and calculate the difference both in dollars and as a percentage using a report generated by Sage 50. You don’t even need to send it to Excel to calculate the change. One of the built in financial statements, <Standard> Income 2 yrs,  gives us a good start. You can find it by going to the Reports & Forms menu and choosing Financial Statements. Find <Standard> Income 2 yrs in the list and double click it to view the report. At the Options window, I like to uncheck the Show Zero Amounts box so the report only shows accounts with balances. Click the OK button to accept the options and view the report. What you get is a basic income statement with columns for current month and year to date columns for both this year and last year.

At this point many people would send the report to Excel if they wanted to calculate the change from last year to this year. But we can very easily add those calculations to the report. Click the Design button that is at the top of the report window. In the designer, you’ll see a column of grey buttons down the left side of the window. Double click the large button labeled Column Desc or right-click on it and choose Properties. Here we can define the columns for our financial statement. Go to the first unused line (that should be line 6 on this report) and check the Print box. For the Contents choose Formula. Type Variance for the title. Moving to the Options section at the upper right, set Align Title to Right Of Column. You can also adjust the column width if you want. Now to calculate the from last year to this year, in the Formula Activity section, set the first Column field to 2. Below that choose Subtract from the drop down list and set the other Column field to 3. That will subtract column 3 (current month last year) from column 2 (current month this year). Now use the Move UP button to move the formula into the column 4 position.

Now let’s add a column to show the percent changed. Go to line 7, check the Print box and set the Contents to Percentage. Enter a title such as Var %. This column can be narrower. I usually set it to around 8. And align the title to the right. In the Percentage section set the first Use Column field to 4 (the variance amount) and the second one to 3 (last year’s amount). Use the Move Up button to make this column 5.

Now repeat that process for the year to date columns. The variance formula will use columns 6 and 7 (This YTD and Last YTD) and the percentage will use columns 8 and 7 (YTD variance and last YTD). You won’t need to move these formulas. When you have finished, click OK to return to the main design window.

This report has a second row of column titles. Since we added columns we’ll need to adjust those titles. Below the Column Desc button is Column Title. Double-click it. You’ll need to remove the This Year title from column 4 and Last Year from column 5, and re-enter them in columns 6 and 7 respectively. Click OK to save your change and return to the design window.

Click the Preview button to view your new financial statement. If everything is the way you want it, click Save and enter a new name for your report. If you want to make more changes, click the Design button to return to the financial statement designer.

If you only want the percentage variance on your report, click the Design button, double click on Column Desc and uncheck the Print box on both formula rows.




 
 

Excel Tip - Paste Cells Without Borders

Do you ever get tired of removing or fixing a cell’s borders after you copy something in Excel? It’s really frustrating to discover that your last minute copy placed some random cell borders in your otherwise neatly formatted spreadsheet, or that the cells you pasted into the middle of a table have no borders even though everything around them does.

Good news! Excel gives you the option of not pasting the borders from the cell you just copied. Instead of doing a normal paste, right-click where you want to paste the cells and go to Paste Special (don’t click on Paste Special, just let it open the submenu), or if you prefer to use the ribbon, click the down-arrow under the Paste. Either way, find and click the No Borders button. Depending on your version of Excel, it will look something like this  or this .

If the area you pasted into has no borders, the newly pasted cells will also have no borders. If you pasted into an area that is already formatted with borders, the newly pasted cells will retain the existing borders.




 

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