Your QuickBooks survival kit for year-end tasks
By Amy E Vetter, CPA.CITP, Advanced Certified QuickBooks ProAdvisor
When most people are winding down the year, our work just begins as we start ramping up for busy season. Past and prospective clients begin to bring in their accounting records and QuickBooks data files so they can issue financial statements for the year. Deciding what to do with these files, and how best to do it, is often a complicated matter. But there are ways to make your life and your client's experience a whole lot better at year end. In this article, I'll walk you through some of the steps to close out and clean up these QuickBooks files at year end, show you one of the most talked about features for QuickBooks 2010 – the expanded Client Data Review – and how it can save you time at year end, and provide you with QuickBooks Survival Kit Year-End Task List you can give to your clients so they can better prepare their files for your review.
Where to begin!
Before you even begin your year-end clean up, always create a backup of your client's data. In fact, as part of your year-end planning session with your client, talk to them about the method they're using to back up their QuickBooks file. To back-up a data file, select the File menu, choose Save Copy or Back-up, then Backup Copy and follow prompts for saving the back-up file. As part of this process, your client should be verifying their data to ensure that there are no errors in the QuickBooks database. To run this process, go to the File menu, choose Utilities, then Verify Data. If there are any database errors found, then they will be prompted to Rebuild their data file. Once they have confirmation that there are no errors in their file, they can back-up their data.
If you are going to be making changes to your client's data, it is important to document your changes separate from your client's work. If you exchange Accountant's Copy files with your client, your changes will be tracked and documented for you and your client to print for your records. Another way to track the changes you make to your client's data file is to have the Administrator of your client's file create you as a separate user assigned to the External Accountant role. New last year in QuickBooks, the External Accountant role allows the Accountant to access all areas of QuickBooks similar to the Administrator role, providing access to all sensitive accounting activities and information in the QuickBooks data file without needing to be logged in as the Administrator.
The Administrator of the QuickBooks data file is the only person who can create an External Accountant User in the QuickBooks file. Once created, the Accountant should always log on with their own login to perform their accounting tasks in the QuickBooks file. To set up an External Accountant user, select the Company menu, Set up Users and Passwords, then Set up Users…, choose Add User. Have the Administrator enter in your Username and Password, then select Next, assign the External Accountant User role to your Username.
Next Step – Troubleshooting Beginning Balances!
Once you are logged on as the External Accountant, the next step is to select the Accountant menu in QuickBooks Premier Accountant Edition 2009 or 2010 and choose Client Data Review. The Client Data Review feature is included in QuickBooks Premier Accountant 2009 and 2010, QuickBooks Enterprise 9.0 and 10.0 and can be accessed in QuickBooks Pro as long as you log in as the External Accountant User. The purpose of the Client Data Review is to help you find and clean-up data entry errors made by your clients in QuickBooks without having to search for them manually.
TIP: Another time saving benefit of the Client Data Review can be to reduce the amount of time it takes for you to propose on a new client engagement. Upon an initial sales meeting with a prospect, you will be able to run this review on the data file and review at a high level for errors in the QuickBooks file. This will enable you to give a better initial time budget for the job.
Once in the Client Data Review, you will select your review period and walk through the various tasks listed to assess the accuracy of the file. One of the most time saving features of the Client Data Review is Troubleshooting Prior Balances. This feature compares the last Trial Balance you issued to the balances that are in QuickBooks for the same cutoff date so you can tie out your beginning balances. It calculates the differences and suggests a Journal Entry to get your balances back in line. Once you save your Journal Entry, it will suggest a Reversing Journal Entry as of the 1st day of the next period to post as well.
Four Power Reports!
QuickBooks provides several reports to assist in troubleshooting beginning balances when you cannot identify the problem through Client Data Review or if you choose not to use Client Data Review because you are working on a Back-up file of QuickBooks that you are not returning to the client. Each of these reports can be found by selecting the Reports menu, Accountant & Taxes and choosing a report that will best troubleshoot the beginning balance discrepancy problem.
The Audit Trail Report lists all transactions entered into the file and the complete history of changes, deletions and /or voids to those transactions. If a client makes a change to a prior period transaction, the Audit Trail Report will show the effects of the change.
The Audit Trail will display who did what in the file, provided that your client has set up a User ID for each person entering transactions into QuickBooks. If every person is sharing the same User ID (which is not recommended!) the Audit Trail will only show a list of the transactions and changes to the file, but not who did those entries.
This report shows modified transactions or transactions changed or added on or before the Closing Date. Similar to the Audit Trail Report, the current status of the transaction as well as the original transaction are shown. You can view the transactions that might need to be reversed, or adjusted, to get back to the correct balances.
In QuickBooks, the Closing Date is used to lock your data file to prevent users from making changes on or before a specified date. To enter a Closing Date, select the Edit Menu, choose Preferences…, then Accounting, select the Company Preferences tab, then choose Set Date/Password and enter in the Closing Date and Password. At the very least, your client should lock the file at the end of each fiscal and/or calendar year. It is best to enter in a Closing Date monthly, after bank reconciliations are performed, sales tax and payroll taxes are filed and adjusting entries are posted for the month.
Note: This report will only show data if a Closing Date has been established and will only track changes made after the Closing Date has been established. If you make changes, then establish a Closing Date, the changes you made before establishing the Closing Date are not tracked.
In QuickBooks versions 2005 or later, QuickBooks creates an activity log of all voided and deleted transactions. QuickBooks tracks all voided/deleted activity whether or not a closing date has been set or if a transaction is dated in a previous reporting period.
To display more information about a transaction on this report, double-click on the transaction. QuickBooks will then display the impact of the original transaction on the General Ledger.
In QuickBooks 2005 and newer versions you can view the detail of transactions posted to Retained Earnings. Select the Lists menu, Chart of Accounts and then double click on Retained Earnings account.
Identify entries made in error to the Retained Earnings Account. Double click on an entry to edit and correct the account assigned.
Time to Clean up the Data!
After you have tied out your beginning balances, your next attack is the current year transactions. Adjustments can be made directly to a transaction itself or through journal entries. If the file is going to be given back to the client, it is best to adjust the transactions themselves rather than make journal entries so that the data file works well for operational accounting purposes for the client, not just financial reporting. If your preference is to make journal entries, you can create Adjusting Journal Entries through the Working Trial Balance window in the Accountant Edition. Select the Accountant menu and then choose Working Trial Balance.
The Adjustment column records the Adjusting Journal Entries that you make in the file. Your entries will be separated from the client's entries, if you check off the Adjusting Entry box at the top of the Make General Journal Entries screen when you enter them.
If you decide that you are going to clean up the data rather than make journal entries, there are other features of the Client Data Review that will assist you with these tasks:
· Review List Changes: This features identifies any changes such as additions and deletions made to the Chart of Accounts, Items, Fixed Asset Items and Payroll Items Lists.
· Fix Unapplied Payments and Credits: For both the Customer and Vendor Lists, open payments and credits are identified so that they can be matched to open invoices or vendor bills all in one window. This feature also allows you to match customer payments affecting Undeposited Funds with Deposits that were directly entered into the register.
· Reporting: Reports are provided to help identify Sales Tax Payments and Payroll Liability Tax Payments that were created with a manual check rather than through the right module in QuickBooks.
· Memory Joggers: There are many other tasks that will take you directly to the activity in QuickBooks that needs to be completed such as Bank Reconciliations and Inventory Adjustments.
Expanded Client Data Review for 2010
In 2010, there were additional features added to the Client Data Review to help you with cleaning up transactions in QuickBooks.
· Inventory Troubleshooting – This feature allows you to review the setup of the inventory items and make corrections as needed from one screen. Additionally, it will compare the inventory asset balance on the Balance Sheet to the Inventory Valuation Summary report, as well as identify any inventory items that are negative so you can make the appropriate adjustments.
· Batch Writeoffs – An important step to go through at year-end is to review the Accounts Receivable reports and write off what is uncollectible. Rather than making a journal entry for this, the Client Data Review feature will allow you to write off invoices, statement charges, and finance charges from one screen
· Batch Reclassifying – As you are reviewing transactions in your client's QuickBooks file, you may find that there are groups of transactions coded to a specific vendor or customer that are incorrectly accounted for. With this feature, you are able to reclassify multiple transactions at the same time from one window.
· Fix Incorrectly Recorded Sales Taxes - Identify manual checks that were created to pay sales tax liabilities that were not properly posted through the Pay Sales Tax function. This feature will allow these transactions to be replaced with a Sales Tax Liability Check.
When you are finished with reviewing the financial data and making your adjustments, you can print or save reports in the QuickBooks file for your records of the work that you did in your Client's file. As part of the Client Data Review, there is an option to run a report called Audit Trail of Review and also to print the Task List of what you completed and any notes you had.
If you are using an Accountant's Copy file, before creating the file to export back to the client, you can print a report of all the changes you made to the QuickBooks file.
If you are using neither an Accountant's Copy file nor the Client Data Review, at a minimum you want to keep a back-up of the QuickBooks file after you finished your adjustments for your records. Additionally it is a good idea to print or save the Audit Trail report filtered for your User ID to save with your workpaper files.
And lastly, don't forget to set a Closing Date either before you give back the Accountant's Copy file to your client or after you enter in your year-end adjusting entries into their live data file.
QuickBooks Survival Kit Year-End Task List
To assist you in providing guidance to your clients of the tasks that they should complete at year-end, we have prepared a simple, easy-to-follow checklist that incorporates all of the above tips and suggestions, along with some additional recommendations to efficiently and accurately close your books for the year.
ü Review the Working Trial Balance to find changes made to prior years' transactions that affect Retained Earnings
ü Review the Chart of Accounts for any newly created or unnecessary accounts
ü Review the Item List to ensure it is coded to the correct Income and Cost of Goods Sold Accounts
ü Review Accounts Receivable for Open Invoices that should be written off to Bad Debt
ü Review Accounts Payable by opening the Unpaid Bills report to ensure accuracy
ü Review Customers and Vendors to check for duplicates, missing information or errors
ü Review Payroll Transactions and Payroll Item Set-up for accuracy
ü Ensure that all Bank Reconciliations have been completed for the year and they were not forced to reconcile through the Reconciliation Discrepancies account
ü Review the Audit Trail Report for any suspicious or changed transactions
ü Review the Inventory Items Set-up, quantities and values on hand
ü Review the Retained Earnings QuickReport to ensure that there were not changes in the account since the prior year-end and/or entries made directly to the account
ü Enter any Depreciation as applicable
ü Reconcile Cash, Credit Card, and Loan accounts with the period-end statements
ü If you are on the accrual basis of accounting, prepare General Journal Entries to accrue expenses and revenues
ü If you are closing the books for a Partnership or other entity in which multiple equity accounts are used, you need to make journal to transfer equity balances to the appropriate Partners capital accounts
ü If your business is a sole proprietorship, enter a General Journal Entry closing Owner's Drawing and Owner's Investments into Owner's Equity
ü Use the Clean up Data File utility to condense the size of the file
ü Perform a physical inventory on December 31 and enter an Inventory Adjustment transaction if necessary
ü Set the Closing Date to the last day of the period (December 31)
ü Print/Save and file the following reports as of your Closing Date: Balance Sheet Standard, Profit & Loss Standard, Statement of Cash Flows, Trial Balance, Inventory Valuation Summary (if applicable) and Fixed Asset Item Listing for the year
ü Prepare and send the IRS Form 1099 to each of your eligible vendors with copies to the IRS
ü Verify the data file for errors, and if errors are found, run the Rebuild Data on the QuickBooks file to fix the database
ü Back up your data file on a special backup drive, network server or through QuickBooks Online Back-up
Planning for Next Year
Once the year end work is done, QuickBooks can help you and your client plan for the year ahead. Using the Cash Flow Projector at year-end helps you in preparing a six-week preview of all your incoming and outgoing cash. This information will help you project upcoming shortages and plan disbursements of cash accordingly). The projector takes into account your cash on hand, accounts receivable and accounts payable.
The Business Plan Tool in QuickBooks walks you through a series of questions to develop a detailed plan for your business that goes out 5 years which follows the format recommended by the U.S. Small Business Administration.
And finally, create a Budget with your client and enter the information into the Planning and Budgeting option under the Company menu in QuickBooks. This will allow you to create reporting throughout the following year comparing your actual results to your budgeted numbers, by month, by class or by customer.
About the author
Amy Vetter, CPA, CITP, Advanced Certified QuickBooks ProAdvisor is Executive Director of Technology in a Box, LLC, an author, a contributor to QuickBooks reference materials, a member of the Intuit Trainer/Writer Network, and a frequent presenter for the Intuit Academy.
Click here to find events in which all members of the Trainer/Writer Network are teaching.