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

July2022 News & Tips

 
NEWS   SAGE 50   EXCEL
• 2023 Release Schedule
• 1099 Printing Changes
• v2020.1 & earlier coming activation problems
•Sage Intelligence Report being retired.
  Negative Inventory, Cost of Goods Sold, and SysCost Adjustments in Sage 50 / Peachtree   Flash Fill - A data entry trick you have to see to believe



News

Sage 50 2023 Release Schedule Change: Based on the last few releases, you probably expected version 2023 to release this month. But Sage recently announced that they are moving the major release date from summer to fall to align more with their other product releases. They have not announced a specific date.

They haven't announced any new features other than a change to how 1099s will be prepared. Beginning with version 2023, all users with an active plan will use Aatrix to prepare 1099s even if you don't use payroll (you will need to buy an Aatrix e-file plan if you want/need to e-file 1099s). Version 2023 will no longer offer the ability to print 1099s to pre-printed forms using Sage 50's internal forms engine. The table below gives more details.
 
  Sage 50 2022.x and earlier versions Sage 50 2023.x and later versions
Required forms and customer plans Print 1099/1096 forms to pre-printed forms from Sage 50
 
All customers with or without an active plan N/A
Print 1099/1096 forms from Aatrix
 
All customers with an active plan with payroll All customers with an active plan, with or without payroll
Note: You will need to purchase an Aatrix efile plan from Sage to use the Aatrix efile capabilities or use the Information Return Intake System (IRIS) with the IRS to efile manually.
 
Which forms should I purchase?
Note: Forms available from Harland & Clarke at sage.com/us/taxforms
 
Visit Sage Knowledgebase article 113094: Which tax forms should I order for year end? Visit Sage Knowledgebase article 115997: Changes to 1099 and 1096 printing/efiling in release 2023
In case you missed it…
  • Transport Layer Security (TLS) protocol: To ensure you are using the most current TLS protocol recommended by third-party standards organizations to provide end-to-end security of your data, you must be on Sage 50 version 2020.2 or later by September 30, 2022. After that date, you will be unable to install and automatically reactivate version 2020.1 and prior versions of Sage 50. Upgrade to the latest version to avoid disruption to your business. Visit this Sage City post for details.
  • Sage Intelligence Reporting Desktop: Effective October 1, 2022, Sage Intelligence Reporting will be retired and removed from the Sage 50 U.S. Edition portfolio. Visit this Sage City post for more information.
 
 

Support for version 2022 will end March 21, 2023.


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 - Negative Inventory, Cost of Goods Sold, and SysCost Adjustments

Sage 50 will let you sell more stock than it shows as on hand.  But have you ever wondered how cost of goods sold is calculated for those sales?  And have you ever seen entries in your general ledger with the description “Syscost Adjustment” and wondered where they came from?  Let’s look at what happens when you let your inventory go into the negative.

Let’s say you have been buying widgets for $100 each.  You get an order for 5 widgets. Sage 50 says you have 4 on hand, but you know more are coming so you enter the invoice and tell the customer they will be delivered in a few days.  Now your inventory shows -1 on hand. Since Sage 50/Peachtree has no way of knowing differently, it assumes that when you replenish this item the cost will be the same as the last time you purchased it.  So in this case, COGS for the fifth widget would be $100, just like each of the other 4 units.  If you look at the Inventory Valuation Report, you will see -1 widgets on hand with a value of -$100.

If the new widgets also cost $100 each, the cost of goods sold entry will be unchanged and there will be no real affect to letting your inventory go negative.

If the new widgets cost $105, and you record their purchase on the same date as the sales invoice (or earlier) the cost of goods sold for the 5th widget will be changed to $105.  All of your reports will look the same as they would if you had originally had 4 on hand at $100 and 1 at $105.

If the new widgets cost $105, and the purchase is dated after the sales invoice, a System Cost Adjustment, (or Syscost Adjustment in older versions of Peachtree), will be made that debits COGS and credits Inventory for $5.  This entry keeps the general ledger in line with the Inventory Valuation Report and corrects your total COGS on the income statement.  But the cost adjustment does not get linked back to the sale.  So if you look at the Items Sold To Customers report you will see that the COGS still shows $500, not $505.  The same is true of the Item Profitability Report.  And the Item Costing Report will show the $5 in the Adjust column, not Cost of Sales.

It’s up to you to decide if this is a problem.  If you closely monitor profitability by item, or you pay commissions based on profit instead of total sales, you probably should not allow your inventory to become negative.  Although, even then it would depend on how much your costs change.

If you use average costing, Sage 50 won’t always use last cost in negative inventory situations.  If you bought one widget for $100 and another for $110, you would have 2 on hand with an average cost of $105.  If you sell 3 widgets on one invoice, Sage 50 would assume the same cost of $105 each for all three units.  But if you sold 2 on one invoice, bringing your QOH to 0, and then sold another widget on a separate invoice, Sage 50 would use your last cost ($110) as the COGS amount for that invoice.  But system cost adjustments work the same way and have the same effect on reporting regardless of your costing method.



 
 

Excel Tip - Enter tip title here

I’ve given several tips over the years about how to take the data in Excel and split it apart or put things together, rearrange it, or replace it. The examples have ranged from simple to complex. But Flash Fill was added in Excel 2013 and it can do an amazing number of things without even requiring to you remember a function name or syntax. It’s something that’s easier to show than to write about, so I would encourage you to watch the video.

If you have the following list in Excel. Type Tony in the “First Name” column. Then go to the Data tab on the ribbon and choose Flash Fill, or just press Ctrl+e. Excel will fill in each of the first names based on the contents of the Contact column. Type “Aldred” in the Last Name column and Flash Fill again to fill in all of the last names.

Let’s say all of these people work at the same company and you want to build a list of email addresses. Type taldred@mycompany.com as Tony’s email address and click Flash Fill. In this specific case Excel will misinterpret what you want and fill in the other lines with “t” as the first letter of each email address. But correct the second line and Flash Fill again, and Excel will learn from your change and correctly fill in the rest of the list. In some cases you might have to make multiple manual entries in order for Excel to understand the pattern.

Contact First Name Last Name Email
Tony Aldred      
Nancy Archer      
Susan Lord      
Emma Armstrong      
Jeff Gillespie      
Sara Boring      
Chris Smith      
Bob Williams      
Francis Chapple      

Here’s an example using numbers instead of text. The phone numbers are difficult to read because they’re not formatted. Simply type in the number the way you want it in the next column on the first line, such as (770) 555-0654, then press Ctrl+e or click Flash Fill and the rest of the column will be completed for you.

Contact Telephone 1
Tony Aldred 7705550654
Nancy Archer 7705554660
Susan Lord 7705554661
Emma Armstrong 7705558824
Jeff Gillespie 7705558827
Sara Boring 7705554130
Chris Smith 7705554133
Bob Williams 7705554133
Francis Chapple 7705558858

The next time you’re faced with retyping a list of information in Excel to get it arranged the way you want, remember Flash Fill.




 

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