By Joe Woodard, AccountingWEB QuickBooks columnist
Restricting Changes to the Chart of Accounts
This trick prevents users from making the following changes to the chart of accounts:
- Merging Accounts
- Changing Account Types
- Changing Sub-Account/Parent Account Assignments
Reason for the Trick
In QuickBooks Enterprise Solutions you can edit user preferences to prevent users from making these specific types of changes to the Chart of Accounts. However, you may still want to use the steps in this trick to prevent the file Administrator from making these changes as well.
Tip: For all editions of QuickBooks it is best to setup a unique username for everyone who uses a QuickBooks file - including the file administrator. Then, the file administrator can login as Admin only when he/she needs to perform an administrative-related function.
In QuickBooks Pro and Premier, any user with the ability to create/modify "sensitive accounting activities" can change account numbers, account names, merge accounts, change the type of accounts, etc. In all additions of QuickBooks the administrator of the file can always make these changes to the Chart of Accounts.
The changes to the Chart of Accounts that have the most far reaching (and historical) impact on financial data are 1) merging accounts – an action that is irreversible and 2) changing the type of an account – an action that you can usually reverse if you can find the change. The Closing Date does not protect the file from these changes or track these changes, even though the changes almost always impact previous financial years. The Audit Trail does not track these changes even though QuickBooks makes changes to every transaction that includes the affected account.
How to Perform this Trick
To prevent users from making these changes in Pro and Premier you can associate every account on the chart of accounts with a 1099 form in the Company Preferences. Once you associate an account with a 1099 form, you cannot change the type of the account, merge the account with another account or change the parent/sub-account hierarchy. It is best to use a 1099 form that you do not actually create and send to third parties.
Note: Even if you select every account from the list shown below, note that some account types do not show on the list (e.g. Accounts Receivable and Accounts Payable). Inactive accounts may also not show. Many of the account types that do not show on this list have restricted options. For example, you cannot add sub-accounts to Accounts Receivable or Accounts Payable and you cannot change the type of the accounts for an Accounts Payable or Accounts Receivable account. However, you can merge two Accounts Receivable or Accounts Payable accounts so this workaround does not protect against this activity. Accounts like Undeposited Funds do not allow you to change the type, to merge or to add sub-accounts, so the inability to associate Undeposited Funds with a 1099 type is not an issue.
Warning: If there are accounts setup in other "real" 1099 lines (usually Box 7) do not add them to your workaround box (e.g. Box 13 as shown below). If you do, QuickBooks will remove them from Box 7.
Note: If you or the client create new accounts, you will need to add these new accounts to the 1099 preferences to protect them from future changes.
Requiring Account Numbers
There is no preference to require the use of account numbers – only to turn account numbering on or off. By "on" or "off" QuickBooks means "show" account numbers or "hide" account numbers. The account numbers remain in the file unless you remove them from the account using the Edit Account window.
All of that said, there is a trick that will cause QuickBooks to require account numbers and will even let you know if there are accounts on the list (active or inactive) do not have accounts numbers assigned.
How to Perform this Trick
Step 1. Select the Edit menu and then select Preferences.
Step 2. On the Accounting Company Preferences, select the option to Show Lowest Subaccount Only. This option is only active when you check the Use Account Numbers checkbox.
Step 3. If all of the existing accounts have account numbers assigned, you will see no further prompts. You can simply click OK to exit and save your change. QuickBooks will then require you to use an account number for all accounts you create in the future – and will not allow you to remove the account number from existing accounts (though users can always edit the account numbers if the user has the privileges to do so).
Step 4. If there are accounts on the chart without account numbers you will see the window shown below. You must then go to the Chart of Accounts, assign numbers to all accounts (even the inactive accounts) and then return to Accounting Company Preferences to select the "Show Lowest Subaccount Only" checkbox.
Accessing a Composite Name List
How to Perform this Trick
Step 1. Open a check transaction.
Step 2. Place your cursor in the Payee field
Step 3. Press CTRL+L to display the names list.
Step 4. With the list displayed, select the View menu and then select "Add Names List to Icon Bar." This will allow you immediate access to the names list in the future.
Missing and Duplicate Invoice Report
It is important to assign a unique Invoice number to each Invoice you send to your customers. This unique number is a control device much like a Check number that allows you to research the customer's invoice and also helps you to assign the payment from the customer to the correct account and invoice.
The Reason for this Trick
Invoice number duplications and numbering sequence gaps (missing numbers) are important for any small business to quickly locate. QuickBooks includes a report allowing you to quickly locate missing and duplicate Check numbers, but not Invoice numbers.
How to Perform this Trick
Since QuickBooks has a report programmed to show you missing and duplicate Check numbers, finding a way to modify the existing report to show missing and duplicate invoice numbers is the best way to get the information you need.
Perform the following Steps to create a Missing and Duplicate Invoice Number report.
Step 1. Create a Missing Checks report by selecting the Reports menu, selecting Banking and then selecting Missing Checks. QuickBooks displays the window shown below.
Step 2. Do not change the default bank account that appears in the Specify Account field and press OK to display the report.
Step 3. QuickBooks creates a report of Checks in numerical order and the report notes any missing and/or duplicate check numbers.
Step 4. Click the Modify Report button and then click the Filters tab to display the report filters.
Step 5. Remove the Account filter and Amount filter shown below.
Step 6. Filter by Transaction Type for "Invoices" and by Detail Level for "Summary Only."
Step 7. Edit the title of the report and memorize the report for future use.
Note: You can use the steps above to filter by other transactions types as well. Doing so will allow you to create a Missing Journal Entries report, Missing Estimates report, Missing Sales Receipt report, etc. Filtering by any transaction type where the QuickBooks user controls the document numbers would produce beneficial reporting results.
Secret Keyboard Command: Edit Paycheck Window
There is no actual window called "Edit Paycheck" of course, but there is a powerful window you can use to make comprehensive changes to any of the information that shows on the Payroll Summary or Payroll Liabilities reports.
The Reason for this Trick
The best place to adjust a payroll liability balance is through the Adjust Payroll Liabilities window. However, if you need to adjust payroll items other than those associated with liabilities (e.g. deductions, federal withholding, state withholding, etc.) you cannot do so on the Adjust Payroll Liabilities window.
How to Perform This Trick
Step 1. Select the Help drop-down menu and then select About QuickBooks.
Step 2. With the About QuickBooks splash displayed press CTRL+ALT+Y. QuickBooks displays the Enter Year to Date Amounts wizard that used to be available as the default in QuickBooks 2000 and prior. This wizard is much more flexible than the one embedded in the Setup Payroll Wizard, allowing you to enter payroll information for any date range and any employee regardless of whether the information is dated before or after the payroll start date and regardless of whether all tasks in the Payroll Setup Wizard are complete.
Step 3. Use the Enter Historical Paycheck window to increase/decrease (i.e. debit/credit) any payroll item. You can click Affect Accounts to control which accounts, if any, receive a debit or credit from the amounts you enter on the window. The window will read "Quarterly Summary" but you can enter any date range you wish in the "From" and "To" fields to restrict the impact to a single pay period, month, quarter or even calendar year.
Showing the "Real" Amount of Open Sales Orders
In QuickBooks you can generate up to three different balances for Sales Orders, depending on which report you run: Open Sales Orders by Customer, Open Sales Orders by Item or by adding an "Open Balance" column to either of the first two reports. In a way, each of these open amounts can be "correct," depending on which perspective you need – but only one of the three balances shows the "real" amount – the amount you would chart your customers if you shipped everything that is currently on order.
Open Sales Orders by Customer
The Open Sales Order by Customer report shows one line for each Sales Order and lists the Sales Order Number and Open amount. The open amount on this report is the total Sales Order amount, not the amount that is currently open. In other words, if the Sales Order is partially shipped due to backordered quantities; the open balance on this report includes the partially shipped quantities. When reporting on Sales Orders by Customer, the main issue is the current open balance, but the total balance of Sales Orders that are fully or partially open. You use this report mainly to track which Customers have open orders so you can manage your Customer relationships.
Open Sales Orders by Item
This report shows a separate line Item for each line of the Sales Order form. If a line on a sales form is fully shipped, the line does not show on this report. However, if a line is partially shipped and has backordered quantities, the line shows the total including these shipped Items. As a result, the total on the Open Sales Orders by Item report will be the same or lower than the amount on the Sales Order by Customer report since the Sales Order by Customer report includes all of the lines on open Sales Orders, even those that are fully shipped.
Open Sales Orders by Customer/Item with Open Amount Column – the "Real" Open Balance
If you add the Open Amount column to the Open Sales Orders by Customer or Open Sales Orders by Item reports, this amount is the "true" open amount in that lines that are completely fulfilled and the quantities shipped from Sales Orders lines that are partially fulfilled are not included in this balance. So, it is best to add this column to the Sales by Customer and Sales by Item reports, memorize the reports and add the reports to the Icon Bar for regular use.
Showing the "Real" Amount of Open Purchase Orders
QuickBooks includes two reports that track open Purchase Orders: Open Purchase Orders and Open Purchase Orders by Job. These reports are available in QuickBooks Pro and above. Note: QuickBooks Premier and Enterprise Solutions: Manufacturing and Wholesale Editions include a report called Open Purchase Orders by Item. However, you do not need to purchase one of these editions of QuickBooks to create this report. This workaround assumes you are using QuickBooks Pro and do not have access to the Open Purchase Orders by Item report.
The Reason for This Trick
If you receive part of a Purchase Order only (i.e. if the vendor has backordered part of the Purchase Order), QuickBooks tracks the amount that is backordered, the amount you have previously received and amount that is currently open on the Purchase Order. You can view this information at any time by opening the Purchase Order and viewing the detail. However, QuickBooks does not reflect this reduced amount on the Open Purchase Orders report. Instead, QuickBooks shows you the original balance of the Purchase Order before you received products from the vendor.
How to Perform This Trick
If the standard reports in QuickBooks do not allow you to see the real open balance of the Purchase Orders, creating a new, customized report will get you much closer to your goal. When you have done all you can do with custom report options, you need to modify the way you track backordered quantities on Purchase Orders for some, but not all, backordering situations.
Part 1: Create an Open Purchase Orders by Item or by Vendor report.
Note: If you use the Manufacturing and Wholesale or Accountant Editions you can skip part 1. However, for the Open Purchase Orders by Vendor report you will have to open the Open Purchase Orders by Item report and change the Total by field to "Vendor" if you want to create that report.
Step 1. Select the Reports menu, and then select Custom Transaction Detail report.
Step 2. On the Display tab, set the date range to "All" and select "Item Detail" from the Total by field drop down menu.
Tip: If the file is large (e.g. over 200MB in Pro/Premier or over 500MB in Enterprise Solutions), set the date field to a narrower range, perhaps to include the previous fiscal year through the end of the current fiscal year. You will have to modify the report each year to roll the dates forward because there is not dynamic date option for a two year date range.
Step 3. On the Filters Tab, filter by: Transaction Type – Purchase Orders, Posting Status – Non Posting, Received – No, and Detail Level – All Except Summary.
Step 4. Change the report columns to include the following columns only: Date, Num, Name, Item, Item Description, Qty, U/M (if applicable), Amount and Balance.
Step 5. On the Header/Footer tab rename the report, "Open Purchase Orders by Item" and memorized the report for future use.
Step 6. If you use the Accountant Edition or Enterprise Solutions (any edition) you may need to remove the Debit and Credit columns and replace them with the Amount column.
Note: The amounts on the report will show as negative numbers. There is no way to prevent this except to export to Excel and make edits there. However, the amounts are still the correct numbers – just negative. If you prefer, you can edit the Fonts & Numbers tab so the amounts are in parentheses instead of preceded by a dash.
Tip: You can export the Open Purchase Orders by Item report from the Manufacturing and Wholesale or Accountant Editions and import the report into any edition of QuickBooks – Pro or above. You need to memorize the report using the Manufacturing and Wholesale or Accountant Edition in order to export it from the Memorized report list. Doing so will show you the same detail as you get from the steps above, but with positive amounts.
Note: You can create another report using the same steps to track Open Purchase Orders by Vendor with line-by-line detail. For Step 2 above, select "Vendor" instead of "Item Detail" in the Total By field drop down menu.
On the Open Purchase Orders by Item report, if any lines of the PO are completely received, the line drops from the report. In most situations, the total of this report will be less than the total of the Open Purchase Orders report, because the latter reflects every line of the Purchase Order even if the items on those lines are received.
Part 2: Modify the Purchase Orders if Necessary to Reflect Backordered Quantities on Separate Lines
The Open Purchase Orders by Item or Vendor reports get you closer to the correct Open Purchase Order amount, but there is still a weakness in the report. If a line on a Purchase Order is partially received, the entire amount of that line shows on the Purchase Orders by Item report – not the amount net of the products you have received. The only way a line will drop from the report is if all of the quantities on that line are received.
There is no report customization ability within QuickBooks to compensate for this overstatement, so you have to modify the Purchase Order to maintain accurate information on the report.
Step 1. Receive Purchase Orders normally. As you have always done, when you receive products using Purchase Order detail, you will pull all of the Purchase Order detail into an Item Receipt transaction. If the vendor did not ship all of the products on the Purchase Order, you will take two actions to reduce the amount of detail on the Item Receipt: 1) You will remove entire lines from the Items tab, or 2) You will change the quantity shown on lines in the Items tab. If you do only the first type of edit, you do not need to take any further action. If you do the second type of edit, continue with Step 2 below to ensure the accuracy of the Open Purchase Orders by Item report.
Note: You can also receive Purchase Orders directly to Bills (without using an Item Receipt), to Checks or to Credit Card Charges.
Step 2. After you save the Item Receipt, open the Purchase Order and locate any rows that contain partial shipments. This detail shows on the Purchase Order window, so you should have no problem spotting those lines.
Step 3. For each line with partially received quantities, change the quantity on the line to the amount that you received – to close the entire line.
Step 4. Create a new, separate line on the Purchase Order for only the backordered quantities.
Step 5. Repeat Steps 3 and 4 above for any additional lines with partially received quantities and save the edited Purchase Order.
Note: The Purchase Order total will not change since you will include the same items and quantities. You will simply move the backordered quantities onto a separate line of the Purchase Order.
Note: You do not need to edit the Purchase Order for lines that are fully received, as stated above.
Karl Irvin has developed a tool called Job Cost Reporter that provides an Open Purchase Orders report with the true open balance without having to split out the backordered lines as described in this trick. You can purchase this tool at www.q2q.us.
About the author
Joe Woodard is an Advanced Certified QuickBooks ProAdvisor and Intuit Solution Provider who has taught over 20,000 QuickBooks consultants across the country. Joe works with Intuit, state CPA societies, and Atlanta-area CPA firms to present advanced QuickBooks instruction to accounting professionals and software consultants. Joe has earned a unique relationship with Intuit as a trainer, consultant and author. Joe has built two successful accounting software consulting practices: the first in New Orleans, Louisiana and the second in Atlanta, Georgia – Creative Financial Software (CFS). In addition to consulting with small businesses, CFS provides advisory services to CPA firms and other QuickBooks ProAdvisors/Intuit Solution Providers across the country -- helping them to better service their clients who use Intuit products. Joe recently hosted the first annual "Scaling New Heights" QuickBooks conference in Atlanta.