Helpful Excel Spreadsheet for Cleaning Up Vendor Files
These spreadsheets were taken from the data converted from Escape Classic to Escape Online 5 the week of December 5, 2005. NOTE—We DO NOT recommend you print this spreadsheet; use the Data Sort tool, described below, to select just a part to work on. Then add information (such as an X) to note the rows you want to group together, sort and then print using the selection command. 1. Select the cells to print. 2. From Menu, choose File Print, then choose Selection
The Data Sort features allows you to quickly view vendor data in various ways. This tool could be used for reviewing your Vendors and determining what changes you want to input in Classic Vendor records to clean up records for: 1099 Reporting Independent Contractor Reporting Conversion to Online 5
Getting Started—Understanding the Spreadsheet
Column Names and what they represent Column What is the data? Is data in Classic? Name Changes? Blank space for reviewer to put note or NO identifier Status Classic Vendor status Yes: Inactive=I, Active=A or Blank Reqs Does this Vendor have Requisitions in 2005? Yes, but not as a yes/no field; Gary built this Y or N from records TID Type Online 5 Field based on information in the Gary built this from Tax ID number format TaxID field for TIN or SS# Vendor ID Vendor number Yes Name Vendor name Yes NameSort Vendor Sort name Yes Omit 1099 The same as Classic—if Y in classic a Y in Note that for Classic a Y means to NOT send a spreadsheet 1099 form. Data below is based on Vendor Issue Address only from Classic Street Vendor street Yes City Vendor City Yes State Vendor State Yes Zip Code Vendor Zip Yes Using the Escape system for generating 1099.doc Page 1 of 6
Using Excel Auto Filters
Whenever a down arrow is blue (instead of black) a selection has been made. Selections generally reduce the number of rows you see.
These have been turned on for your spreadsheet. Using the down arrows makes it easy to look at just one area of vendors by choosing what you want to see. They can also be turned off.
For example:
Reviewing Vendors that should get a 1099
1. To see Vendors that have had Requisitions this year and should get a 1099 Choose the Reqs Column u and choose yes To clear any selections— click the down arrow & choose all. To be more specific—go to another column and choose again. The list will shorten to just the rows where Yes is in the column. Then click in the Omit1099 column and choose N and the list will be shorter. 2. Now you can sort the remaining rows using Data Sort however you want to review the data. Identify the rows to make changes and then go into Classic and make needed changes to those Vendors. Tip: To print what only what you need to make changes use the Excel feature of Print Selection. Select the rows and columns you want to print, from the Menu choose Print and from the Print dialog box choose o Selection. Then use the Preview button to see if any other changes are needed.
Reviewing Vendors to make then Inactive
1. Repeat #1 above except choose No (you will still need to review the list) 2. Click on the status column and choose Blank 3. You could resort however you want to work the list or choose another column such as Omit1099 and choose Y or N depending on what you want. Once you have a list of Vendors to make changes to; sort by Vendor number and then call up the list to work in Classic to make changes. There are many variations using the Data Sort option. Use the column noted user notes to mark records you want to sort & select by to print a small part of the spreadsheet. Helpful Web Information http://www.irs.gov/instructions/i1099gi/ix01.html
http://www.edd.ca.gov/taxrep/txicr.htm
Using the Escape system for generating 1099.doc Page 2 of 6
Using the Escape system for generating 1099’s & Independent Contractor Reporting. Clean up 1099 Vendor Files
Tip: You could use the data sort options in the Excel file to prepare for these updates 1. AP Module Vendors Process 1099 Select Tax Year 2005 2. List 1099 by Tax ID Number (Building the list may take some time!) *Check W9’s or send for new W9 if missing Fed Tax ID or SS# update each Vendor with missing Tax ID entries *Also, check for duplicate tax ID number investigate & correct *Also, check the bottom of this list for valid number 3. List 1099 by Alpha (Building the list may take some time!) Look for duplicate Vendors and correct/merge 4. List 1099 by Zip (Building the list may take some time!) Look for missing or incomplete addresses
Review if Vendor should receive a 1099
Selecting Vendors to update 1099 codes
From AP Module Vendors Alpha a to zz F3 When list appears, key in zz Wait a few minutes—you will see it show all Vendors (if you don’t do the zz, it just shows 1 page and you have to keep using Page Down) Then back to menu Reports Vendors Set up to Receive 1099’s Vendors set up to Receive 1099’s
This is another option other than using the Excel spreadsheet (above) to review Vendors. Can check this against W9’s to see if require a 1099 Using the Escape system for generating 1099.doc Page 3 of 6
Recommend that at a minimum 2 checks be completed 1. For all entries with Fed Tax ID instead of an SS#--check the W9 or request a new one to see if they really should be marked to receive a 1099. (Entries with a Fed. Tax ID will not be subject to Independent Contractor Reporting) 2. Check entries with SS#--if this is only for Employee pay backs such as mileage and direct pay reimbursements; change Escape Classic code to Omit 1099 = Y (they will not receive a 1099)
To use Escape Classic to prepare for the Independent Contractor Report File
After printing the above-Escape once & you will be back at the list of all vendors. Menu Independent Contractors Select by Requisition
Requisition Dates—search goes faster if use end of month date F3 When list appears, again use the zz and wait You will notice the Alpha list at end will be at end of alphabet, also will see total selected number be greater than the 1 page count.
Using the Escape system for generating 1099.doc
Page 4 of 6
Use Control + Page Up to get back to start of the list Enter on 1st Vendor—can use F8 to go thru the list, when find a vendor with a Federal Tax ID rather than SS#, Escape out to mark that record. Continue on until you have marked all records with a Federal Tax ID, then Remove those records from the list for Independent Contractor Reporting. Indep Contractor Remove Marked Records (now you have a list of Independent Contractors) Follow the balance of the detailed instructions in Independent Contractor Reporting http://www.acoe.org/ACOE_Docs_Buss/Finance/IndependentContractorInstructions.pdf From the steps involved; you can see that having the 1099 Vendors properly identified can save time!
Another Option for Identifying Independent Contractors
After you have cleaned up your Vendors and have a good 1099 Vendor list of active vendors. A Commodity Group of IC has been added to each districts Purchasing Codes (Exception: ACOE already had ICR created for Independent Contractors.) You can see this from Purchasing Module Codes Fixed Asset Groups/SubGroup
After cleaning up the Vendor Files; this code can be Added to the Vendor Screen as a Commodity Code which you can use to select vendors that may require Independent Contractor Reporting: Go back to Vendors, build your list for 1099 Vendors, then review and determine if they also qualify as Independent Contractors for EDD reporting requirements. If so: Enter on each one to add the commodity code.
Shift + Enter in the Commodity field will bring up the list.
You can add this Commodity Code if the vendor would be subject to independent contractor reporting criteria. Using the Escape system for generating 1099.doc Page 5 of 6
Then you can go back and clear the Select/Sort and then re-select Select/Sort Commodity Group
F3 Finish the tasks outlined in the handout. http://www.acoe.org/ACOE_Docs_Buss/Finance/IndependentContractorInstructions.pdf
Using the Escape system for generating 1099.doc
Page 6 of 6