Transcript QuickBooks Tricks

QuickBooks Tricks
Missing and Duplicate Documents
(Control and Error checking Issue)
Missing Check Numbers Report
Missing & Duplicate Invoices Numbers Report
Missing & Duplicate JE Numbers Report
Missing & Duplicate Estimate Numbers Reports
Missing & Duplicate Sales Receipt Numbers
1. Start with Missing Checks Report
2. Remove Account Filter
3. Add Transaction Type Filter
4. Change Title
5. Memorize.
(Included)
(Custom)
(Custom)
(Custom)
(Custom)
Suppress Account Numbers on Reports
Benefits of Account Numbers:
1. Dictate Order within Type (i.e. Accum. Depr.)
2. Faster Data Entry
Issue:
Many CPAs don’t want account numbers in Reports
(You Can Hide them as Admin and in Single User Mode)
Better Solution:
1. Edit Account Descriptions
2. Show reports based on Description only
Descriptions can differ from accounts (AR vs. Trade Receivables)
4 Steps to Prevent Prior Period Changes
1. Set up user names and passwords so users can’t log in as
administrator.
2. Edit user preferences to prohibit bypassing the closing date.
3. Establish a closing date and move it forward each month after
review and adjustments.
4. Set a closing date password.
CTRL + 1 Product Information
1. Print Screen, Paste and e-mail it to yourself.
2. QuickBooks Edition
3. License Number
4. Product Number
5. File Size
200 to 500 MB – QuickBooks Pro or Premier
1 GB – QuickBooks Enterprise
6. List Size Limits
28,000 Items
14,000 Customers, Vendors, Employees and other names
7. File location
Export Lists to Excel
– Edit –
Import Lists from Excel
Sometimes you need to make massive editing changes to
QuickBooks, and it is easier to make these changes in Excel. For
example:
1. Inventory Prices
2. Account Descriptions
3. Inventory Descriptions
4. Customer Addresses
5. Etc.
Turn on Auto Filter When Exporting to Excel
Benefit:
Drop down boxes will then allow you to filter a multitude of ways
Pivot QuickBooks Data in Excel
Problem: QuickBooks data is not ready for pivoting
Solution:
1.
Export Transaction Detail by Account Report to Excel without blank columns
2.
Format Columns A & B to “General”
3.
Insert formulas in Column A to Tag Label and Non-Label Cells in Column B. ie: In cell
A2: =IF(B2=$A$1,2,1)
4.
Copy column A and paste values to convert the formulas to text
5.
In cell B3 insert formula referencing cell above. ie: =B2
6.
Copy formula in cell B3
7.
Highlight cells B2 thru the end of data in column B
8.
Press the F5 (GoTo) key
9.
Choose Special, and check “Blanks”, and select OK
10. Paste – This action will insert account names next to each transaction
11. Copy column B and paste values to convert the formulas to text
12. Insert column headings in A & B – Your data is now ready to pivot