QuickBooks Tips

Shortcuts: A list of key combinations for QuickBooks that make common tasks quicker.

Efficiency Tips: Some tips designed to increase the overall efficiency of your QuickBooks experience.

Setup and Customization Tips: Information on helpful features like password protecting, QuickBooks sharing, and other customizations.

Advanced Features : Several tips and tricks to help make even the most advanced QuickBooks tasks easier and more efficient.

Shortcuts

Efficiency Tips

Setup and Customization Tips

Accuracy Tips

Advanced Features

Shortcuts

Quickly Closing All Open QuickBooks Windows

To close all open QuickBooks Windows quickly, click on Window on the menu bar, and then Close All.

Using the Transaction History Button

The History button shows you all the transactions related to the one you are currently viewing. For example, view any invoice that you know is paid, then click on the History button. Then you'll see the payment details on your screen instantly.

Keys that help you save lots of time when working with dates

(Just type the key shown in the date field.)

  • T = today's date

  • M = First day of the month you are working in

  • H = Last day of the month you are working in

  • Y = First day of the year

  • R = Last day of the year

  • + = Move the date forward by one day

  • - = Move the date backward by one day

Chart of Accounts Shortcut

  • CTRL+A brings up the Chart of Accounts right away

Closing QuickBooks Shortcut

  • ALT+F4 closes QuickBooks software

Calculator Shortcut

  • When working in a register, you can use the = to bring up the calculator when adding or subtracting from an account

Memorized Transaction Shortcut

  • CTRL+T will bring up your Memorized Transaction list immediately

Back to Top

Efficiency Tips

How to back up your important QuickBooks data

  1. Click on File
  2. Click Save Copy or Backup
  3. Select the type of file you want to save
  4. Select either Online backup (subscription service) or Local backup
  5. Follow the onscreen instructions for Online backup
  6. For Local backup decide when to make a backup copy
  7. Complete the location where the file will be backed up (i.e. another computer or alternative file storage method)
  8. Click the Backup button

Back to Top

Seven Ways to Search QuickBooks

Over time, your QuickBooks company can grow in size to the point that it becomes difficult to find specific transactions. For instance, let's say that you hire a new employee, and want to order another desk to match the ones in your office. You vaguely remember the last time that you ordered a desk, but can't remember which vendor, or how much you paid. In this article we'll discuss seven ways that you can search QuickBooks to find transactions such as this, or when necessary, determine if a transaction was deleted.

#1 Registers

QuickBooks maintains a register for each account on your balance sheet, which includes bank accounts, inventory, accounts receivable, and other assets. There are also registers for accounts payable, loan accounts, and owners' equity. Depending upon what you're looking for, a register might be a fast way to find what you're looking for, such as that desk we mentioned at the start.

Registers can be opened multiple ways. Here are two we recommend:
  • On the Home Screen in the Banking area click on the Check Register icon. Choose the appropriate account from the list, then click OK. QuickBooks will then present the register in a separate window.
  • On the Home Screen in the Account Balances area click the plus sign to see a list of accounts and their balances. Double-click on the appropriate account. QuickBooks will then present the register in a separate window.

In the Register window you can use the Go To button to search the register. This can help you narrow your search within a register that contains many transactions. The Go To button is located in the upper left hand side of the window. Choose the field you want to search in and what you want to search for. Select the Back or Next buttons to search.

Keep in mind that registers are just one way to find transactions in QuickBooks, and won't always be appropriate for every situation. For instance, income and expense accounts don't have a register, in those cases you need to take another approach.

#2 Simple Find

Think of the Simple Find feature as an expanded version of the Go To feature within a QuickBooks register. Choose Edit, and then Find (or press Ctrl-F) to display the Find window.

If necessary, click the Simple tab at the top of the window. You can then carry out searches based on transaction type, such as Invoice, Estimate, Bill, Check, and so on:

  1. Choose a transaction type from the list.
  2. Optionally limit your search by completing the Customer/Job, Date, Invoice #, and Amount. It's not necessary to complete these additional fields, however without doing so you'll return a list of all transactions of a given type, which may or may not be helpful.

Once your list of transactions appears on the screen, you have several options:

  • Double-click on a transaction to view it, or click once on the transaction and then click the Go To button.
  • Click the Report button to display a Find report onscreen. As we'll discuss later in this article, you can then click the Modify Report button to further refine the results of the Find report.
  • Click the Export button to export the results to a comma-separated values (CSV) file or Excel spreadsheet.

#3 Advanced Find

This feature is akin to the Simple Find on steroids. You can search QuickBooks based on any combination of dozens of criteria. To use Advanced Filter, simply choose a field from the Filter column, and then set the desired criteria. Your input choices will vary based on the field that you choose. For instance, if you click on Name City, you can enter a single city.

Conversely, you can make multiple selections when you choose a field like Item or Account. Keep adding new filters as needed. You can craft some very elaborate searches in this fashion. To eliminate a filter, click once on the item within the list on the right, and then hit the Delete key. Alternatively, the Reset button will also clear the decks for you.

#4 Search

Current versions of QuickBooks feature a Search command on the Edit menu. You may be prompted to enable Google Desktop the first time that you choose this command. Doing so will allow you to use the same search terms and conventions that you use on the Internet to locate transactions within QuickBooks.

This feature is not automatically enabled, and doing so may cause minor performance degradation on your computer. Google Desktop automatically groups transactions by type, and you can choose to sort by date.

Indexing required: Google Desktop initially runs an indexing process on your QuickBooks data. The length of time required for this varies, based upon the size of your QuickBooks company. You'll get the best results from your search if you wait until Google Desktop has completed its initial index. New transactions will automatically be indexed after this one-time process completes.

#5 Customized Lists

By default, QuickBooks customer and vendor lists only display names and balance totals. You may not realize that you can add additional columns, such as phone number, and that each column is sortable:

  1. Choose Customers, and then Customer Center (or press Ctrl-J).
  2. Right-click on the customer list, and then choose Customize Columns.
  3. You can add as many additional fields as you wish to the list, as well as move fields up and down within the list. Click OK once you've made your changes.
  4. You can resize the columns within the list. Place your mouse between field names, and then drag to the left or right to resize the field.
  5. Click the name of a field within the list to sort based on that column. You can use this technique to search for a customer simply based on telephone number.

#6 Report Filters

Some QuickBooks reports are like a fire hose of data, giving you far more information than you really need. Fortunately, every report has a Modify Report button in the upper-left-hand corner of the screen, which enables you to pare down the results of the report. You can click the Filters tab of the Modify Report window and make the same choices that we discussed previously in the Advanced Filter dialog box.

#7 Audit Trail:

If you've exhausted all of the previous methods to find a transaction in QuickBooks, one final place to look is the audit trail. Someone could have deleted the transaction, either on purpose, or accidentally. The audit trail formerly was an optional feature in QuickBooks, but starting with the 2006 versions and onward, it's always on, so you'll always have a searchable record of every transaction ever entered in QuickBooks.

You cannot view the details of deleted transactions in the audit trail, but at least you'll know why it didn't appear through any of the other search methods:

  • Choose Reports, Accountant & Taxes, and then Audit Trail.
  • Once the report appears on screen, you can change the date range to pick a broader range if necessary. You can also click the Modify Report button and apply any filters that you wish. Any deleted transactions will be marked as such.

Summary

Hopefully you now have at least a couple of new ways to retrieve transactions or contact information from QuickBooks. Not every technique is appropriate for every search, but understanding these seven ways to search QuickBooks can broaden your skills, and help you retrieve information about anything within your accounting records.

Did You Know? There are hundreds of add-on products available for QuickBooks that may be able to help you streamline business processes that you currently carry out by hand. Visit http://marketplace.intuit.com to search the list of available products by industry or business need. Many applications are certified by QuickBooks, and most products include customer product ratings. Free trials are often available, so you can try products before you buy. The web site also includes a list of developers, so you can have a custom application written for you if can't find an existing tool to meet your needs.

Back to Top

Use Closing Date to Protect Prior Year Data

After you've closed the prior year's books, your records will become the basis for your tax return. It's critical that your QuickBooks records for a given year match the corresponding tax return, so consider setting a closing date in QuickBooks so that no one inadvertently changes the supporting documents for your tax return:

  1. Choose Edit, and then Preferences.
  2. Choose Accounting
  3. Click on the Company Preferences tab.
  4. You can use this window to determine if a closing date has been set.
  5. Click Set Date/Password, and then enter a closing date.
  6. Although optional, you should then set a password.

If you set a date without a password, then a prompt will appear when someone attempts to enter or modify a transaction dated on or before the closing date. Conversely, a password prompt asks for the closing date password when it has been set.

It's generally best to set the closing date once you've completed all of your year-end reconciliations, printed W-2s and 1099s, and other year-end tasks. In fact, an ideal time is when you send the books out to have your tax return prepared.

Eliminating Uncategorized Income and Expenses

Unless you set a specific preference, users can enter transactions without specifying a revenue or expense account. Such transactions appear on the Profit & Loss Statement as Uncategorized Income or Uncategorized Expenses. If these items appear on your Profit & Loss Statement its an easy fix. Simply double-click on the amount, and then double-click on each of the transactions in the resulting transaction report. Assign accounts to each of the underlying transactions, and then click the Refresh button to see the effect on your report.

Fortunately you can set a preference in QuickBooks to ensure that no uncategorized transactions will ever slip through:

  • Choose Edit, and then Preferences.
  • Choose Accounting, and then click on the Company Preferences tab.
  • Ensure that Require Accounts is checked, and then click OK to save the preference.

How to Print W-2s and 1099s from QuickBooks

If you process payroll in QuickBooks, you'll soon need to print W-2 for your employees. The recipient copies of these forms must be postmarked by January 31, 2009, while you'll need to submit the government copies by February 28, 2009.

QuickBooks can print on blank perforated W-2 forms or preprinted W-2 forms. But before you embark on printing W-2 forms, make sure that you have the latest payroll update:

  • Choose Employees, and then Get Payroll Updates.
  • Click the Update button, and then follow the on-screen prompts to download the latest payroll updates and forms for your version of QuickBooks.

Once you've installed the payroll updates, you're now ready to print your W-2 forms:

  • Choose Employees, Payroll Tax Forms & W-2s, and then Process Payroll Forms.
  • Choose Federal Form, and then click OK.
  • Choose Annual Form W-2/W-3 Wage and Tax Statement/Transmittal, and then specify the year for which you're printing W-2s.
  • When the Select Employees for Form W-2/W-3 window appears, click Review/Edit to display a preview of each form to be printed. You'll walk through an interview, copies of the W-2 forms, the summary W-3 form, and then printing instructions.
  • Click Submit Form to display the dialog box. You then use this window to print the various copies of forms W-2 and W-3.

It's just as easy to print Form 1099 from QuickBooks. You may not realize that there are over a dozen different versions of the ignoble 1099 form.

However, most users only need Form 1099-MISC, which QuickBooks allows you to generate, as well as the transmittal Form 1096. 1099 must be postmarked by the same dates discussed previously for W-2s. Here's how to print 1099s in QuickBooks:

  • Choose Vendors, and then Print 1099s/1096. If this option does not appear, choose Edit, Preferences, and then Tax: 1099. Choose Yes on the Company Preferences tab, and then click OK.
  • When the 1099 and 1096 Wizard appears, click the Run Report button for step 1. When the Vendor 1099 Review report appears, carry out these steps:
  • Scroll down and ensure that all vendors that require a 1099 have a Yes in the Eligible for 1099 field. Check with your tax advisor if you're unclear as to whether any of your vendors should receive a 1099 form.
  • If you find any misclassified vendors, double-click on the vendor name, and then choose the Additional Info tab, and then set or clear the Vendor Eligible for 1099 checkbox. Filter the report to show only 1099 vendors, so that you can confirm that every 1099 vendor has a proper address and tax ID number entered in QuickBooks.
  • To do so, click the Modify Report button, and then click the Filters tab. Scroll down to the Eligible for 1099, and then choose Yes. Click OK, and then confirm that all vendors have tax IDs and addresses.
  • Return to the 1099 and 1096 wizard, and then click the Map Accounts button. Most 1099 vendors are classified as subcontractors, so ensure that Box 7 matches the account where you posted subcontractor income.
  • Click the Run Report button on the 1099 wizard. The report shows amounts that will appear on a 1099, as well as amounts you paid that won't be included.
  • Be sure to double-click each amount in the Uncategorized column. QuickBooks only allows you to map a single account to a given box on Form 1099, so you may need to change the account on one or more uncategorized transactions to ensure that the 1099 reports the proper amount. Keep in mind that reimbursed expenses are not typically included on Form 1099.
  • Once you've reviewed the summary report, click Print 1099s. Confirm the date range to use, and then use the Select 1099s to Print window to preview and then print your forms.

Back to Top

Fast access to company files you have used recently

Use the Open Previous Company option found on the File menu to give you fast access to company files you have used recently

Deleting invoices off the "Select Invoices to Print" window.

Here's how you delete some of those old invoices you no longer need to retain. This is a common problem in QuickBooks. Here's the fix - for each entry that is showing up in your Reminders list under "Invoices to Print", double- click on it. This action should take you to the invoice itself. Once here, remove the check mark in the box in the lower left hand corner that says "to be printed", then click "Save and Close". This invoice should no longer show up in the "Invoices to Print" list.

Filtering a Report - Selecting most of the items from a list

When filtering reports in QuickBooks, you may want to select most of the items on a particular list, but not all items to appear on your report. First choose the "selected" option from the list (selected accounts, selected names, etc).

Normally you would check each item that you want to appear on your report. However, if you want to check all the items on the list, click the first item and do not release the mouse button. While holding down the mouse button, slide the mouse pointer down just below the list.

When QuickBooks is done all of the items on the list will be checked. Release the mouse button. Now you can manually click the items to remove the ones that you do not wish to include in your filter.

Back to Top

Setup and Customization Tips

How to customize the settings on your QuickBooks desktop

  • Edit
  • Preferences
  • Desktop View

How to turn on the Reminders feature when opening a company file

  • Click on Edit
  • Click on Preference
  • Click the picture of Reminders from the list that appears
  • Make sure a check mark is in the box marked "Show Reminders list when opening a company file"
  • Click the OK button

Password Protecting QuickBooks

To password protect your QuickBooks data file click on Company in the menu bar, then Set Up Users. From here, you can assign a password for the main (Administrator) account in QuickBooks. You can also set up user accounts, passwords, and different levels of access if you have multiple people using your QuickBooks file. Make sure you keep your newly created password in a safe place (just in case you forget it!)

Back to Top

How To Setup QuickBooks to share with another computer

To allow the sharing of your QuickBooks file, your best option is to have a computer network in place. This network will provide the foundation for you to then share the file between two or more computers. You can have either a wired or wireless network within your office.

It is also possible to take a backup copy of the QuickBooks file from one computer and restore it to a second computer to allow sharing of the information. However, I don't recommend this if both computers will be entering QuickBooks data. If you do this, you won't know who has the most recent copy of the data file, and things can get messed up quite easily. Your best bet is to talk to a local computer consultant about a network for your home or office.

How to set up accounts for contractors that will be given 1099s.

The first thing you must do is tell QuickBooks that you will be issuing 1099s. Click on Edit (from the menu bar), then Preferences. Scroll down until you find the picture that says "Tax:1099" and click on it. On the Company Preferences tab, answer yes to the question about the issuance of 1099 forms, then follow the rest of the instructions on this screen.

Next, you'll need to identify those vendors who are eligible to receive a 1099. Click on Vendors, then Vendor List. Double-click on the first vendor in the list, then the "Additional Info" tab. In the lower left hand corner, put a check mark in the box that says "vendor eligible for 1099" and enter their tax ID number they have provided. Repeat this process for any vendors that need to receive a 1099.

Finally, to run the 1099's and related information, click on Reports, then Vendors, then either of the 1099 reports. Verify the data, correct if necessary, and print your 1099's all within QuickBooks.

It's important to review this topic with your accountant to ensure you are collecting the proper information and preparing the 1099 forms correctly for your business.

Back to Top

Accuracy Tips

Keeping Your 941 Report Accurate

Do not pay your payroll liabilities from the "Write Checks" window. If you use this window, QuickBooks will warn you to use the "Pay Liabilities" window, but will let you write the check. However when you print the 941, it will not reflect any payments that you made using the "Write Checks" window.

Use the "Pay Liabilities" window to create checks for all tax liabilities. Using this window will ensure that the payments are reflected accurately on the 941 report and that your liability accounts are properly reduced.

Recording of Barter Exchanges

If you have customers who are also vendors you may decide to trade some or all of your services / products in exchange for payment.

To record such a barter transaction, invoice the customer for the goods provided or services performed as you normally would. To record the "payment" use the "Receive Payment" function to apply the barter amount against the invoice the same as you would when receiving cash or a check as follows:

Go to Customers: Receive Payment.

  • Payment Amount will be the barter amount (the amount of the invoice you received from your vendor).
  • Pmt. Method will be Barter.
  • Check the radio button for "Group with other undeposited funds".
  • Save this transaction.

Go to Banking: Make Deposits.

The payment you just received will come up in the Payments to Deposit screen. If there are also other payments to deposit, make sure you select only the payment(s) being recorded for the barter exchange. When you hit OK the Make Deposits screen will come up with the barter deposit(s) showing. Before recording the deposit make a negative deposit entry on the next blank line below the barter deposit for the amount of the barter as follows:

  • Deposit To is your normal operating checking account.
  • Date is the date you would have normally paid your vendors invoice.
  • Memo should be changed from Deposit to Barter.

If you have entered the vendors invoice as a bill for payment, Received From is the vendor name and From Account is Accounts Payable.

If you have not entered the vendors invoice as a bill for payment, leave Received From blank. In the From Account column select the expense account you would charge the vendors invoice to, the same as if you were entering it for payment. In the Memo column note the vendors invoice number.

In the Amount column enter the vendors invoice amount with a negative sign first. This negative amount should exactly offset the deposit amount above, resulting in a "Zero" deposit transaction. Save the "deposit" and the transaction is complete.

Back to Top

Advanced Features

Ten Overlooked QuickBooks Reports That You Should Use

Just about every QuickBooks user relies on the Report Center and Reports menu, but if you're like most, you have a small handful of reports that you tend to rely on. In this article we'll go off the beaten path and explore ten reports that many users overlook. Even if you are using some of these reports, we're sure you'll find a few more to add to your repertoire.

1. Profit & Loss Summary Prev Year Comparison: To access this report, choose Reports, Company and Financial, and then Profit & Loss Summary Prev Year Comparison. Most business owners rely on the Profit & Loss Summary report, but comparing your results to last year can provide quick insight into whether your revenue is growing or contracting-as well as how fast expenses are rising.

2. Balance Sheet Prev Year Comparison: You'll find this report also within the Company and Financial section of the Reports menu. As with your income statement, it's important to compare where certain balances stand now versus last year:

  • Cash
  • Accounts Receivable
  • Inventory
  • Accounts Payable
  • Other Liabilities, such as lines of credit or short term loans

3. Statement of Cash Flows: As with the two preceding reports, you'll find the Statement of Cash Flows in the Company & Financial section of the Reports menu. Profit & Loss reports enable you to see what you earned, while Balance Sheet reports help you determine what you have-as well as what you owe. However, neither report necessarily provides a clear picture of where cash is coming from, or going to. You'll be able to see:

  1. How much cash you've taken in from sales and spent on expenses
  2. Cash inflows or outflows from borrowing, repayment, or investing activities

In short, this report shows you exactly what caused your bank balance to increase or decrease during a given report period.

4. Collections Report: Tricky economic times mean it is more important than ever to keep track of your collections. Fortunately QuickBooks makes it easy to contact customers with overdue invoices: choose Reports, Customers & Receivables, and then Collections Report. The report provides a phone list and shows all overdue invoices. However, you can also use this report to quickly e-mail copies of overdue invoices to your customers. To do so, double-click on a transaction within the Collections report to view the invoice, and then click the Send button at the top of the invoice form to display the Send Invoice form. You can modify the wording shown to be more direct, such as a subject line of 'Overdue Invoice' or perhaps e-mail text along the lines of-I've attached a copy of your overdue invoice. If there's a problem with our products or services, please let me know immediately, otherwise I trust that you'll remit payment promptly. To change the default e-mail text, choose Edit, Preferences, and then choose Send Forms. Select Invoice from the Change Default For list, make your changes, and then click OK.

5. A/P Aging Summary: Although it's key to make sure that your customers are paying in a timely fashion, it's just as important to pay your vendors, too. Unpaid bills can result in phone calls, e-mails, and other unnecessary interruptions. Choose Reports, Vendors & Payables, and then A/P Aging Summary to display the report.  As with most reports in QuickBooks, you double-click on amounts to ultimately drill down to the original transaction.

6. Trial Balance: Many business owners overlook the Trial Balance report, since it's one of the few reports in QuickBooks that uses the terms Debit and Credit. However, it's a helpful report, as it shows you all account balances in a concise format. If anything looks out of order, simply double-click on the amount to view the underlying detail. Choose Reports, Accountant & Taxes, and then Trial Balance to view this report.

7. Voided/Deleted Transactions Summary: It's no surprise that small businesses are much more prone to fraud than large businesses. Small business employees usually wear multiple hats, so it's often impossible to separate financial duties (bigger businesses can do this with ease). Fortunately QuickBooks makes it hard for perpetrators to cover their tracks: choose Reports, Accountant & Taxes, and then Voided/Deleted Transactions Summary. You'll be able quickly identify any transactions that have been deleted from QuickBooks. Granted, this isn't an end-all solution by any means, but it is a helpful management tool. Plus, if a transaction ends up 'vanishing' from QuickBooks, you can use this report to see who deleted it!

8. Audit Trail: The audit trail was an optional feature in earlier versions of QuickBooks, but is permanently enabled in recent versions of QuickBooks. This provides a complete record of every entry made in QuickBooks. The downside to that is that you can end up with a massive report. Don't worry, as it's easy to filter this report and narrow your search. To do so, choose Reports, Accountant & Taxes, and then Audit Trail. Once the report appears, click the Modify button, and then click on the Filters tab. You can filter by date range, amount, or dozens more fields.

9. Previous Reconciliation: It's a good practice to always print at least the summary report once you've reconciled a bank or credit card account. Someone else could edit a reconciled transaction, which could cause the reconciliation to be out of balance. A printed copy of the report shows that the account reconciled as of the report date, although you will still have to untangle the edited transaction. However, if you close out the reconciliation screen, you have a second chance to print your report: choose Reports, Banking, and then Previous Reconciliation.  You can choose from multiple reports.

10. Transaction History: Think of this as a 'report within a report', as you can only run it in certain circumstances. You must have a transaction open on the screen or single-click on a transaction within a report. You can then choose Reports, and then Transaction History. QuickBooks will display a report that shows the entire history for a given transaction.

Did You Know? The Microsoft web site offers hundreds of free spreadsheet and word processing templates. Options range from timesheets to analysis tools to contract documents. Visit http://office.microsoft.com/ templates , and then search for a template by use (home, office, school), collection (real estate, small business, wedding), or keyword. Indeed, if you've created a template that you rely on, you can submit it to the site and share your work with others!

Back to Top

16 Bank Reconciliation Tips and Tricks

Although it may seem like drudgery, reconciling your bank account is a critical accounting task that you should carry out each month. Doing so helps ensure the integrity of your financial reports, since most of your accounting transactions ultimately affect cash in some fashion.

Further, QuickBooks is a much more powerful tool for your business if you use it to its fullest extent. Most likely you've been reconciling your bank account all along, so in this article we'll discuss the tricks and techniques you need to know to streamline the process.

If you're new to QuickBooks, you start the bank reconciliation process by having your bank statement in hand, and then choose Banking, and then Reconcile. The Reconciliation screen appears. In most cases, you enter the ending balance from your bank statement, add any interest or fees, and then click Continue.

You mark transactions as cleared and then click Reconcile Now. However, it's not always that simple, so read on to learn how to sail over any hurdles that may appear.

1. Locate discrepancies

Click the Locate Discrepancies button to display the Locate Discrepancies window.

From there, click the Discrepancy Report button to display the report. This identifies any edited or deleted transactions that may affect your reconciliation.

2. Confirm your beginning balance

Your beginning balance should always tie to your bank statement, but if it doesn't, click the Undo Last Reconciliation button until you reach a point where the beginning balance matches your bank statement. You must then redo the reconciliations to bring your books current and resolve the discrepancy.

3. Don't forget interest and fees

Be sure to record any interest and fees in the QuickBooks Begin Reconciliation window. Alternatively you can record deposit and check transactions to record interest and fees, or the very savvy can use journal entries.

If you go this route, be sure to debit cash and credit interest income for interest earnings or credit cash and debit bank charges for any fees incurred.

4. Double-check your ending balance

Always double-check your ending balance input when you start the reconciliation. A simple transposition or other error here can make it appear that you've missed a transaction.

5. Look for transpositions

Sometimes you'll mark all transactions as cleared, but still have a difference. In such cases, divide the difference by 9. If it divides out evenly, then there's a good chance that you transposed a number on a transaction.

For instance, a $63 dollar difference divided by 9 returns 7 could mean that a transaction was entered incorrectly. You can right-click on an amount, and then choose Edit Transaction to fix the error.

6. Pick a side, any side

Don't mix and match deposits and withdrawals. Reconcile your Deposits and Other Credits first, and then confirm that the total items you marked cleared ties to the amount shown on the Reconcile window.

Then reconcile Checks and Payments - doing one side a time limits your search area for missing or misposted transactions.

7. Clear the decks

If you get tangled up in a reconciliation, click the Unmark All button to start over.

8. Enter missing transactions

You can add missing transactions without closing the reconciliation window. Simply choose a command from the menu across the top or from the Home screen. Saved transactions will instantly appear in the reconciliation window.

9. Check undeposited funds

Choose Banking, and then Make Deposits. If the Payments to Deposit window appears, you must complete the deposit process for these transactions.

10. Hide unnecessary transactions

Click the Hide Transactions after the Statement's End Date check box to have fewer transactions to sift through.

11. Void old transactions

Old, uncleared transactions can linger on forever - locate such transactions within your register, choose Edit, and then Void. The banking system generally considers checks to be stale after six months.

Such lingering transactions are often duplicates of a transaction that cleared.

12. Clear voided transactions

Always clear transactions with a zero balance as these won't affect your reconciliation, but do clutter up the Reconcile window.

13. Bank online

Some institutions allow you to synchronize your records with your online statement. This involves a matching process that automatically clears transactions that match, and makes it easy to quickly post new transactions.

14. Use your keyboard

Rather than using your mouse to click on each transaction that you wish to clear, use the arrow keys on your keyboard to move up and down. Press the spacebar to toggle a transaction as cleared or uncleared.

15. Walk away and come back later

If you just can't seem to get the unreconciled difference down to zero, the best thing to do is click the Leave button, and then resume the reconciliation tomorrow. A fresh eye can do wonders.

16. Reconcile More Frequently

If you can access your bank account online, you can reconcile your bank statement as often as you wish. Consider reconciling accounts with heavy volume weekly or twice a month.

Back to Top

Use Accounting Ratios to Stave Off Financial Problems

Does the mere mention of accounting ratios may put your teeth on edge, and bring back bad memories of Accounting 101? It shouldn't as ratios can help your quickly determine how your business compares against others.

Banks often use ratios to analyze your financial statements as part of the loan approval process, so it's helpful to know in advance how you'll be measured. Even better, ratios allow you to compare your business against your peers since many trade groups publish lists of average ratios within an industry.

Although ratios may have made you drowsy during accounting class, they can be a fascinating way to measure your company's financial performance.

Gross Profit Margin

Simply put, gross profit margin-sometimes referred to as gross margin-is your revenues less your cost of sales. For some industries, this is a very meaningful metric, while it won't mean as much to others. For instance, manufacturers, restaurants, and retailers often treat gross profit as a key performance indicator.

In such environments, one typically purchases inventory at one price, and ideally sells it to someone else at a higher price. The spread between these two numbers is the gross profit margin.

Let's say that you buy $40 of pine straw (we're trying to avoid the accounting class term widget) and sell it for $60. In this case, $20 of gross margin divided by $60 of sales yields a gross margin percentage of 33%. Thus one-third of your sales are available to put toward overhead items, such as office supplies, payroll, rent, taxes, and so on.

Ideally your gross margin is high enough to cover your overhead and leave you with a profit. With that example in mind, let's see how you can calculate your own gross profit margin.

Caveat: Gross profit margin isn't meaningful to everyone. For instance, if you're a self-employed service provider, you may not have any cost of sales.

Your salary is arguably all or most of your profit. You can certainly count your salary as cost of sales and compute a gross profit margin, but you might not find much value in the result.

To begin, choose Reports, Company and Financial, and then Profit & Loss Standard. As shown in Figure 1, look for the Gross Profit amount, and then divide this by Total Income.

The Profit and Loss Standard report provides the figures you need to calculate gross profit in Quickbooks

Figure 1: The Profit and Loss Standard report provides the figures you need to calculate gross profit.

In this case, $30,953.20/$51,241.16 shows a gross profit margin of 60.4%. Is that good? Is it bad? Very often the answer is 'it depends', which is why you should try to compare yourself to similar companies in your industry.

However, let's consider the restaurant industry. Many owners strive to keep their gross margin at around 63%, which means a cost of goods sold percentage of 37%. The gross profit ratio enables you to track this key measurement, but you must ensure that your transactions are being recorded in the proper accounts.

The percentages can skew if, let's say a telephone bill, is miscoded to Food Costs, instead of Telephone. Similarly, your cost of goods sold might look great only because someone miscoded food costs into an overhead account, such as Supplies.

Profit Margin

Profit margin is another commonly used ratio that you can derive from the Profit & Loss Standard report by dividing Net Income by Total Income. In essence, this is the percentage of sales that the owner of a business gets to keep before Uncle Sam gets his share. Profit margins vary widely by industry.

For example, a grocery store chain may have profits of $2 billion, but a profit margin of just 2.6%. An oil company may have staggering profits in dollars, but their profit margin is often just 10%. Conversely, some software companies have a profit margin of 28% or more.

For example, a grocery store chain may have profits of $2 billion, but a profit margin of just 2.6%. An oil company may have staggering profits in dollars, but their profit margin is often just 10%. Conversely, some software companies have a profit margin of 28% or more.

As with gross profit, the best way to determine whether a profit margin is reasonable is by comparing the result to one's peers. The construction company shown in Figure 1 has Net Income for the period of $13,123.48, which when divided by Total Income of $51,241.16 returns a profit margin of 25.6%.

Inventory Turnover Ratio

This ratio illustrates how many times a year that you're selling your entire inventory. This can help you gauge whether you may be holding too much inventory, or not enough. This ratio is based on cost of goods sold divided by average inventory.

As you've seen, cost of goods sold appears on the Profit and Loss Standard report look for Total COGS but you'll have to perform a quick calculation to determine average inventory. To do so, divide the sum of your beginning inventory plus ending inventory by 2.

Although you can use several different reports in QuickBooks to determine the beginning and ending balance of your inventory, try this first: choose Reports, Company and Financial, and then Balance Sheet Prev Year Comparison.

Change the report date to This Fiscal Year, and then look for the inventory account balance, as shown in Figure 2.The ending balance for last year is also the beginning balance for this year.

If you need beginning and ending balances for a shorter period, such as a quarter, choose Reports, Accountant and Taxes, and then General Ledger. Set the report dates to the period of your choice, and then use the beginning and ending balances for your inventory account.


Figure 2: The Balance Sheet Prev Year Comparison
can provide beginning and ending inventory balances.

Average Collection Period

This ratio helps you determine how long it takes your customers to pay their invoices. The formula is a little more complex than some of the other ratios: number of days multiplied by average accounts receivable balance, divided by credit sales.

For instance, let's say that your average accounts receivable balance is $30,000, and you had total sales of$400,000 for the year. 365 multiplied by 30,000 is 10,950,000. This amount divided by our total sales of $400,000 is 27.38, meaning that on average your customers pay their invoice in just under 30 days.

Be sure to monitor your average collection period, as your cash flow can tighten quickly if that ratio increases. If you typically invoice your customers, then you can use the Total Income figure from your Profit & Loss Standard report.

Keep in mind: Average collection period won't be of interest if your customers pay on the spot, such as in a retail store or restaurant.

Although QuickBooks doesn't directly provide a figure for average accounts receivable, you can quickly customize a report to aid in this calculation:

  • Choose Reports
  • Company and Financial
  • then Balance Sheet Standard.

Click the Modify report button, and then set the From and To dates to match the period shown on your Profit & Loss report. Change the Display Columns By to Months, and then click OK.

When QuickBooks displays the 12-month report, click the Export button, and then click OK to send the report to Microsoft Excel.

As shown in Figure 3, row 9 contains the Accounts Receivable figures. In cell R9, enter this formula to calculate your average accounts receivable balance: =AVERAGE(F9:R9).


Figure 3: Use the Accounts Receivable figures
to calculate your average accounts receivable balance.

As you can see, the average collection period ratio enables you to determine how long it takes your customers to honor your invoices, which in turn has a direct impact on your cash flow.

Other Common Ratios

Current Ratio: Divide current assets by current liabilities to determine a firm's liquidity.

Quick Ratio: Subtract inventory from current assets, and then divide by current liabilities to apply a more severe liquidity measurement.

Debt Ratio: Divide total debt by total assets to determine how much of the company is financed by debt. Return On Assets: Banks often add net income plus interest expense together, and then divide this by total assets to determine the firm's return on assets. This figure typically needs to exceed the interest rate of a loan that you may be contemplating.

Compare Yourself to Others

Now that you understand how to calculate ratios based on your financial results, the next step is to compare yourself to your peers. You may belong to a trade group that makes benchmarks available to its members. If not, a good first step is the BizStats web site, at www.bizstats.com

Your line of business may be included in their free offerings, but even more information is available on a subscription basis. You can find even more resources by searching the Internet search for the term 'industry benchmarks'.

Did You Know? You can send your thoughts about QuickBooks to Intuit directly from within QuickBooks. To do so, choose Help, Send Feedback Online, and then one of these choices:

  • liroduct Suggestion
  • Bug Reliort
  • Helli System Suggestion

Any of these links will display an online from in your web browser so that you can submit your thoughts directly to the QuickBooks development team. QuickBooks frequently updates its' products, so before you send a bug report, choose Help, and then Update QuickBooks. Click the Update Now button to ensure that you have the latest patches and fixes for your version of QuickBooks.

Back to Top

Profit & Loss Report Versus Statement of Cash Flows

If you're like most QuickBooks users, you rely on the Profit & Loss Standard report to monitor how your business is doing. However, you may have overlooked an even more valuable report: the Statement of Cash Flows.

The Profit & Loss Standard (P&L) report is important in its own right, but it only provides partial insight into the health of your business. While the P&L shows what you earned and spent, the Statement of Cash Flows shows you where the cash came from and went to, also known as sources and uses.

As you'll see in this article, you can use the Statement of Cash Flows to determine the how various activities increased or decreased your cash balance during a given report period.

Cash versus Accrual

Unlike some accounting packages, QuickBooks allows you to run most reports on either the cash or accrual basis.

Cash-basis means that transactions don't appear on your Profit & Loss statement until either your customer pays their invoice or you pay a vendor (or employee). So, if you enter a bill in QuickBooks to be paid later, the expense won't immediately appear on a cash-basis P&L.

Similarly, invoices that you send to customers won't immediately appear on a cash-basis P&L. The expense appears when you write a check to the vendor, and the revenue appears when the customer honors their invoice. Accordingly, cash-basis reports don't necessarily report a company's true financial performance.

You could have a stellar looking Profit & Loss Report, but a list full of unpaid bills in QuickBooks. Accordingly, many accountants prefer that business owners use accrual-basis reports.

Accrual-basis reports recognize the effect of every transaction on your P&L immediately. Customer invoices appear on accrual-basis P&L reports as soon as you save the transaction, as do unpaid vendor bills.

Accrual-basis reports provide a much better picture of where the business stands, but can make it harder to understand your current cash position. However, a cash-basis P&L isn't a panacea for managing cash flow, as your business has many transactions that don't affect the P&L.

For instance, loan payments, owner distributions, and owner contributions affect your balance sheet, which tracks assets, liabilities, and equity. Fortunately, the Statement of Cash Flows reflects these types of transactions and more, so it's a great companion to both cash-basis and accrual-basis P&L reports.

Set Your Preference

You can instruct QuickBooks to always display your reports on either cash or accrual basis:

  • Choose Edit, and then Preferences.
  • Choose Reports & Graphs, and then Company Preferences.
  • Specify either Cash or Accrual, and then click OK.

Of course, at any time you can change a report to the other format. For instance, if your preference is set to accrual, but you may sometimes want to view a cash basis P&L:

Choose Reports, Company & Financial, and then Profit & Loss Standard.

Click the Modify Report button, and then choose Cash in the Report Basis section.

NOTE: Most, but not all, reports in QuickBooks allow you to change between cash and accrual. When a report is onscreen, choose Modify Report.

If you don't see the Report Basis section, then you'll know that you can't toggle the report basis. Now that you understand the ins-and-outs of running cash and accrual basis reports, let's explore the Statement of Cash Flows.

The Statement of Cash Flows

Let's say that your cash balance at the beginning of your fiscal year was $100,000, and today it is $75,000. The net income figure on your P&L won't give you the full details on why your cash balance decreased, but the Statement of Cash Flows will. To do so, choose Reports, Company & Financial, and then Statement of Cash Flows.

Report periods: This report automatically defaults to This Fiscal Year-To-Date, but you can choose another time period if you wish. To do so, make a choice from the Dates drop-down list, or modify the From and To dates, and then click the Refresh button.

Report periods: As shown in Figure 1, this report automatically defaults to This Fiscal Year-To-Date, but you can choose another time period if you wish. To do so, make a choice from the Dates drop-down list, or modify the From and To dates, and then click the Refresh button.

Figure 1: The Statement of Cash Flows defaults to the current fiscal year.

Your Statement of Cash Flows report will include up to three major sections:

  • Operating Activities
  • Investing Activities
  • Financing Activities

Don't worry if your report only includes one or two of these sections - sections only appear when you had relevant transactions during the report period. Let's explore each of these sections individually.

Operating Activities

The Operating Activities section of the Statement of Cash Flows recaps activities related to running your business. This section will always start with Net Income, followed by an adjustments section.

The adjustments reconcile your net income with the net cash provided by the operating activities. For instance, refer to Figure 1 . Net income s $112,999 but the Net Cash Provided by Operating Activities is $42,584. Accordingly, the statement of cash flows identifies the $70,415 difference. Let's investigate a couple of the items:

Accounts Receivable (-$71,759) : During the report period we sent invoices to our customers, of which $31,503.08 remain unpaid. These unpaid invoices are reflected in the Net Income figure, so QuickBooks deducts these because we haven't received this cash yet

Inventory Asset (-$17,354): Amounts that we spend on inventory don't become part of Net Income until we've sold the items. At that point QuickBooks posts the expense to cost of good sold, and reduces our inventory account accordingly. Purchasing inventory is a use of cash, so it appears as a negative amount on our Statement of Cash Flows.

Remember: The purpose of the Statement of Cash Flows is to reconcile our net income with the actual change in our cash account. Thus non-cash activities, such as unpaid customer invoices or amortized prepaid expenses get subtracted or added from Net Income, so that you can get a clear picture of where cash went during the report period.

Employee Advances (-$62): We paid $62 to an employee as an advance, which has not yet been repaid. This amount isn't included in Net Income, but is a use of cash, so the amount is deducted. When our employee repays the advance, our Statement of Cash Flows will reflect a positive amount, since at that point we'll have a $100 source of cash.

Prepaid Insurance ($893): During the report period we amortized, or used up, $893 of prepaid insurance. This expense is included in our Net Income figure, but we didn't write a check for it during this report period, so QuickBooks adds this expense back.

Accounts Payable ($13,537): We've entered bills into QuickBooks totaling $13,537 that we haven't paid yet. In effect, we're temporarily borrowing this money from our vendors, so it's a source of cash. Later, our Statement of Cash Flows will show a use of cash when we pay the vendor bills. This same treatment applies to credit cards and other liabilities.

As you look through the Statement of Cash Flows, you may also see Investing and Financing activities. Investing activities may include owner contributions as a source of cash, or in the case of the report in Figure 1, the purchase of $11,500 in furniture as a use of cash.

Financing activities will show borrowing on a line of credit or other loan as a source of cash, while loan repayments (net of interest) will appear as uses of cash. In the end, you'll see exactly what caused your cash balance to increase or decrease during the report period.

Research: You can easily investigate why amounts appear on your Statement of Cash Flows. The QuickZoom icon appears when you hover over an amount. Double-click to display a detailed report.

Organizing the Statement of Cash Flows

QuickBooks makes an educated guess at what accounts in your chart of accounts should appear on the Statement of Cash Flows. However, you may encounter instances where activities appear in the wrong section, or don't appear at all on the report. You can easily remedy such situations:

  1. Choose Edit, and then Preferences.
  2. Choose Reports & Graphs, and then Company Preferences.
  3. Click the Classify Cash button.

Place a checkbox in the appropriate column. You cannot remove balance sheet accounts from the statement, but you can optionally include income and expense accounts. However, keep in mind that this is not a typical need, and you should only proceed under the guidance of your accountant or tax advisor.

Did You Know? QuickBooks has a Product Information window that can provide a dizzying array of information. Press Ctrl-1 to display the Product Information window. Some key elements on this screen include the product number shown at the top.

Each QuickBooks user in your office should have the same release number. The size and location of your QuickBooks file is shown in the File Information section, while you can use the List Information section to determine how many customers and vendors you have in QuickBooks.

Back to Top

QuickBooks Helps You Navigate Tricky Waters

The price of gasoline is just one of many factors putting pressure on our economy as a whole. Now it's more important than ever to keep a close eye on your company's performance. Many business owners compare financial results to an annual budget. If you don't have your budget in place yet, we'll show you how to get started. Even if you have, we'll show you how to use last year's results as a measuring stick with comparative financial reports. Once you understand these techniques, we'll explain why you should create a monthly appointment with yourself to ensure that your results continue to measure up and take action if they don't.

TIP: Keep in mind that tough financial years do have a silver lining-you'll likely pay less in income taxes. If revenues are down or expenses are up, don't forget to trim your withholding or estimated tax payments accordingly. Doing so enables you to boost your cash flow now, rather than waiting around on a tax refund next spring.

Budget Basics

The QuickBooks Planning & Budgeting menu gives you the ability to create budgets and forecasts. In reality, both features work the same way, so we'll use creating a budget as our example. But which one should you use? You might find it helpful to use the Forecast feature as an alternate budget and as a best-case scenario, while the Budget feature offers a better expectation of reality. Either way, here's how to create a budget in QuickBooks:

Choose Company, Planning & Budgeting, and then Set Up Budgets.

When the Set Up Budgets window appears, click the Create New Budget button in the upper right-hand corner.

Select the year that you'd like to create a budget for select Profit and Loss, and then click Next.

Balance sheet budgeting: QuickBooks offers the ability to create a budget for balance sheet accounts, such as planning for expected levels of cash, inventory, accounts receivable, liabilities, and so on. However, most small business owners find that just a Profit and Loss budget is sufficient for their needs.

Most users will choose No Additional Criteria on the next screen. However, QuickBooks does provide the option for a more granular budget that you break down to the customer, job, or class level. Click Next once you make a selection.

The next screen asks if you want to start with a blank budget from scratch or if you want to use last year's actual data as a starting point. Most users will find it helpful to use the previous year as a starting point. Click Finish after you make a choice.

At this point you're presented with a screen called Setup Budgets . You won't see any numbers if you chose the From Scratch option in step 5.

Proceed with entering or updating your budget. Click the Save button as needed to preserve your work as you go, and then click the OK button when you're finished.

Budget Tips: The Copy Across button enables you to copy the same amount across all twelve months. The Adjust Row Amounts button provides a quick way to adjust existing numbers up or down by either a percentage or dollar amount. You can edit your budget at any time: choose Company, Planning & Budgeting, and then Set Up Budgets. Choose your budget from the Budget list, and then make changes as needed.

Budget Reports

QuickBooks offers four budget and two forecast reports. You'll use these steps to run most of these reports:

  1. Choose Reports, Budget & Forecasts, and then the report of your choice.
  2. A three-screen wizard appears, asking you first which budget or forecast you wish to use. Once you've made a selection, click Next.
  3. The next screen asks which report layout to use - you may only choose one, Account by Month - click Next after you confirm your choice.

Click "Finish" to display your report:

Budget Overview - This report provides a twelve-month view of your budget.

Budget vs. Actual - This 52 column report can be tricky to navigate, as the default format shows these columns for each month, as well as a 12-month total.

Report Taming Tips: There are a couple of ways to bring this report down to size. First, most users can eliminate the % of Budget column. To do so, click the Modify Report button, and then deselect % of Budget in the Add Subcolumns For section. Next, you can shrink the width of the columns. To do so, drag the diamond between the first actual and budget columns to the left . When you release the left mouse button, choose Yes when asked if you want to resize all of the columns. Alternatively, click the Export button to send the report to Excel.

Profit & Loss Budget Performance- This report compares your month and year-to-date actuals to the budgeted amounts, and also displays the 12-month budget. Although this report doesn't display dollar or percentage variances, you can easily add these columns. Click the Modify Report button, and then select $ Difference and/or % Of Budget in the Add Subcolumns For section.

Budget vs. Actual Graph - This report doesn't enable you to choose a budget - the current year budget is displayed automatically. This report enables you to get a graphic view of how your results measure up to your budget. You can choose between different budget views:

P&L By Accounts - This view compares your Profit & Loss accounts, also known as income and expense, to the corresponding budgets. The report automatically sorts variances by difference, and you can view up to six accounts at a time.

P&L By Accounts and Jobs - This view compares your P&L accounts on a job-by-job basis. Jobs with the largest total variance from budget will be presented first, and as with accounts, you can view six at a time.

P&L By Accounts and Classes - This view compares your P&L accounts on a class basis. As with the other views, you can view up to six classes at a time. This button appears even if you haven't set the Enable Class Tracking preference.

Class Tracking: Classes allow you to you track costs by department, project, or other categories. To enable class tracking, choose Edit, Preferences, and then Accounting. On the Company tab, select Enable Class Tracking.

Graph Printing limitation: You cannot print more than one page of the budget graphs at a time, so you'll have to click Next Group and then click Print to create a hard copy of each report group. QuickBooks doesn't provide a way to print all of the graphs in one fell swoop. You also can't modify the graph format, other than to choose a different date range.

Comparative Reports

Regardless of whether you use budgets in QuickBooks or not, it's always helpful to compare this year's results to last year. Doing so enables you to see trends in your data, such as how automobile expenses have increased. Such a report is just a couple clicks away:

  1. Choose Reports
  2. Company and Financial
  3. and then Profit & Loss Prev Year Comparison.

By default you'll see this year compared to last year. However, you can easily create a multi-year comparison:

  1. Click the Modify Report button.
  2. In the Columns section, choose Year from the Display Columns By drop-down list, and then click OK.
  3. On the report screen, choose a date range, such as 1/1/04 through 12/31/08, and then click the Refresh button. A multi-year comparison will appear onscreen. If you find this format helpful, click the Memorize button to save this report for later use.

Summary

In this article we discussed how you can use the budget and forecast feature in QuickBooks to plan the future of your business. As each month rolls by, you can compare your plan to actual results. In addition, you can compare this year's results to last year, or even the last several years.

Did you know? that an accountant's copy of a QuickBooks file can be converted to a normal QuickBooks company, i.e. a .QBW file? There are limited circumstances where you'd want to do so, because it's not possible to merge two .QBW data files together. However, let's say that you lose access to your QuickBooks company because your hard drive crashes or someone steals your laptop. These are situations where a converted accountant's copy would be better than starting from scratch. If you need to do this, ask your accountant to carry out these steps in their version of QuickBooks:

  1. Choose File, Utilities, and then Convert Accountant's Copy to Company File (QBW).
  2. Choose the Accountant's Copy to convert.
  3. Click OK on the QuickBooks Information prompt .
  4. Assign a name to the new company file, and then click Save.

A final warning prompt will appear to confirm that this copy will overwrite any existing client copy of the books.

Of course, the best defense is to make frequent back-ups of your QuickBooks data on removable media, such as the USB flash drives that often cost less than $10. These easily allow you to carry your QuickBooks back-up offsite, such as in your purse or briefcase. But, it's good to know that your accountant might be able to provide a working QuickBooks company - as long as you recently sent your accountant's copy along to them.

Back to Top

Get the Job Done and Track Costs too!

Many businesses can benefit from tracking revenue and expenses by project, known as job tracking in QuickBooks parlance, it's ready for your immediate use. For instance, let's say that your company installs residential elevators. Several different builders contract with you to install your product in their high-end homes. In such cases the builder would be the customer, while each house that you install an elevator in would be a job. You can associate as many jobs with a customer as you wish. As you'll read in this article, you can not only associate revenues, but also expenses with jobs, as well as create a budget so that you can track your prognostication against reality.

Jobs versus Classes

Users sometimes confuse jobs with classes. Both allow you to track revenue and expenses, but are typically used for different purposes:

Jobs are helpful when you want to track activity for a specific project for any of your customers. In addition, jobs allow you to create estimates and utilize progress invoicing.

Classes are effective when you want to track costs by department, for an internal project, or other activities where you're not necessarily billing a customer. In addition, you must enable classes in QuickBooks by choosing Edit, Preferences, Accounting, and then choose Use Class Tracking on the Company Preferences tab. For instance, a medical office might create a class for each doctor, so that expenses can be allocated fairly among practitioners. Once you enable class tracking, you'll notice Class fields appear on various transaction screens that you can use as needed.

Note that you can associate customer invoices and expenses with classes, but you'll find that jobs typically work better for that purpose. However, you can also use jobs and classes in conjunction with each other, for even greater tracking capabilities.

Create a Job

Unlike classes, no special set up is required for jobs, which are always associated with a customer. Since you probably already have numerous customers established in QuickBooks, let's see how to add a job:

  1. Click on the Customer Center button on the toolbar (or press Ctrl+J).
  2. Right-click on a customer name in on the Customers & Jobs tab, and then choose Add Job.  Alternatively, you can choose Add Job from the Edit menu after you select a customer name.
    Beware: It's possible to create a job within a job, so be sure that you click on the customer name, and not a job name, when you click Add Job.
  3. At this point the New Job window appears . Assign a job name, and then complete the Address Info tab. Much of this should carry forward from the associated customer record, but you can override the information as needed for this job.
  4. The Additional Info and Payment Info tabs work in the same fashion as with customers, but you'll note that jobs have an additional Job Info tab. This allows you to maintain these fields:

Job Status: This field lets you assign one of these labels to the job: None, Pending, Awarded, In Progress, Closed, and Not Awarded. You cannot add additional choices to this list, but you should change the status as the job works through the various stages of its lifecycle.

Start Date: Enter the date the job begins.

Projected End: Enter the expected end date for the job. Doing so will later let you compare how your projected job end dates match up to the actual job end dates.

End Date: Leave this field blank until the job is completed.

Job Description: This field allows you to assign an additional description beyond what appears in the Job Name field at the top of the screen.

Job Type: This user-definable field allows you to assign types to jobs as you wish-choose Add New from the list to add a new type. You can edit or delete this list by choosing Lists, Customer & Vendor Profile Lists, and then Job Type List.

Job Info tab: Confusingly, the Job Info tab also appears when you add a new customer. However, you cannot add a customer and a job at the same time. You can use the Job Info tab to store information about that customer, but it's best to first add the new customer, and then add the job in the fashion described above.

Assign Jobs to Transactions

Estimates, Sales Orders, and Invoices can only be assigned to a single job, but you can split other transaction types among multiple jobs. Therefore you'll choose a job name from the Customer:Job drop down at the top of the Invoice screen-estimates and sales orders work in the same fashion. Other transaction types have either a Customer:Job column or a Name column where you can assign line items to specific jobs. In addition, each transaction screen includes a Billable column where you can indicate whether an expense is reimbursable by your customer. You'll leave the Billable field blank if you're simply tracking costs for a given job, or click in the field to place a checkmark that will indicate that your customer should be billed for the charge.

Payroll: The paycheck detail screen allows you to assign payroll to a job, but it's best to use QuickBooks time tracking feature to do so. The paycheck detail screen doesn't allow you to specify whether time is billable, but the other screens do. Choose Employees, Enter Time, and either Use Weekly Timesheet or Time/Enter Single Activity.

Apply payments: Remember to apply customer payments against the job and not the customer itself. Otherwise you might encounter situations where the customer has a credit balance and the job has a debit balance that net out to zero.

Budgets

QuickBooks enables you to create a budget for your job, but there's a catch: you can only budget within a single fiscal year. So, if your job spans more than 12 months, or crosses fiscal years, you may have to use some creativity, such as splitting the job budget between two fiscal years. However, with that caveat in mind here's how you'll create a budget for your jobs:

  1. Choose Company, Planning & Budgeting, and then Set Up Budgets.
  2. Click the Create New Budget button to launch the Create New Budget wizard.
  3. Specify a fiscal year, choose Profit and Loss, and then click Next.
  4. Choose Customer:Job on the Additional Profit and Loss Criteria screen, and then click Next.
  5. Choose Create Budget from Scratch on the Choose How You Want To Create a Budget screen, and then click Finish.
  6. You're now presented with the screen where you can select a customer and/or job, and then enter your budget on an account by account basis.

Consolidated Budget: Keep in mind that you only need to run the Create New Budget wizard once for a given fiscal year. Simply change the Customer:Job field on the budget screen to budget additional jobs. Once you've run the wizard, you can view or maintain your budget by choosing Company, Planning & Budgeting, and then Set Up Budgets. If necessary, choose your budget from the drop-down list. QuickBooks automatically assigns a name like FY2010-Profit and Loss by Account and Customer :Job.

Reports

Although job tracking is a helpful way to ensure that you bill your customers for reimbursable expenses, most business owners would agree that the primary benefit is the report capability. As you would expect, QuickBooks provides a variety of job-specific reports-choose Reports, and then Jobs, Time & Mileage to access these choices:

Job Profitability Summary: This report compares actual costs to actual revenues and shows a dollar difference. You can optionally display a percentage difference-this is helpful if you want to determine margins. To do so, display the report onscreen, and then click Modify Report. Click % Difference, and then click OK. By default this report summarizes all jobs, but you can limit the time frame or filter the report to view selected jobs or types of jobs.

Job Profitability Detail: This report allows you to view one customer or job at a time, and provides much more detail than the Job Profitability Summary report. However, this job shows data by inventory item. Note that if you select a customer, then all jobs for that customer are summarized together, but you can also select a single job instead.

The Job Profitability Detail report provides the best overview of a single job.

Profit & Loss by Job: This report provides a column for every job for the specified report period, and details activity by account-as opposed to the Item ID approach used by the Job Profitability Detail report.

Unbilled Costs by Job: This key report enables you to track any expenses that you've marked as billable but haven't yet passed along to customers. For instance, the Enter Bills and Write Checks screens enable you to assign expenses to a job. An adjacent field enables you to also mark the charge as billable. In turn, QuickBooks notifies of pending unbilled charges when you invoice the customer.

TIP: It's easy to disable the 'unbilled charges available' prompt on QuickBooks invoicing screen, so it's a good practice to click the Add Time/Costs button on the Invoicing screen and look for unbilled charges on each tab.

Open Purchase Orders by Job: Most small businesses don't use purchase order tracking, but those that do can use this report to determine what items are still on order for jobs in progress.

Mileage by Job Summary and Detail: Most small businesses simply have to absorb the cost of mileage, and therefore don't choose to track mileage by job. However, if tracking this level of detail is helpful to your business, these reports will be a key tool. In such cases, use Enter Vehicle Mileage on the Company menu to log your travel.

The Job Is Done

In this article we helped you get up and running with QuickBooks job tracking feature. We discusses how job tracking differs from class tracking, and showed how you can use job tracking to allocate revenue and expenses to jobs, as well as track billable charges. We helped you establish budgets for your jobs, and then we wrapped up the article with an overview of QuickBooks job tracking reports.

How To Easily Analyze Payroll Transactions in Excel

Although the Employees & Payroll reports menu in QuickBooks offers numerous reports that let you slice and dice your payroll data, you may sometimes yearn for more flexibility. When you do, the Summarize Payroll Data in Excel and Tax Form Worksheets in Excel features will transform your payroll data with just a couple of mouse clicks. The Summarize Payroll Data in Excel feature helps you review historical payroll transactions; while the Tax Form Worksheets in Excel lets you peer into the summary numbers that appear on the tax forms that you generate from QuickBooks.

Excel required: Note that you must have Microsoft Excel installed on your computer to use these features. If you don't already have Microsoft Excel, you can download a free 60-day trial from www.trymicrosoftoffice.com .

Summarizing Payroll Data in Excel

This feature is available in QuickBooks 2004 and later, and enables you to generate numerous payroll reports in Excel with just a couple of mouse clicks. Keep in mind that the mix of reports that you see may vary, based on your version of QuickBooks. In addition, the Excel-based reports take two different formats:

Pivot table-based: Excel's pivot tables feature summarizes rows of data into a concise format. In this case the rows of data are in QuickBooks, so the resulting spreadsheet becomes an extension of QuickBooks. In general, pivot tables offer several special benefits:

  • You can rearrange the pivot table by dragging and dropping fields
  • You can double-click on any number within the pivot table to drill down to the underlying detail
  • Certain fields in the pivot table include drop-down lists, from which you can exclude certain items or employees.
  • You can set the pivot table to put a page break between each employee or item, which enables you to easily print a separate report to share with each person on your team.

We'll explore some of these capabilities later in this article.

Worksheet-based: The worksheet-based reports that QuickBooks generates are static in nature, meaning you can't double-click on any numbers to view the underlying detail. These reports are similar in nature to reports that are generated when you use the Send to Excel feature to analyze any of QuickBooks built-in reports. You can, however, copy and paste portions of the reports into other workbooks, or modify the reports to meet your needs.

QuickBooks' Summary Payroll Data Reports

The following payroll data reports appear in the 2007 and 2008 versions QuickBooks Pro or higher - other versions of QuickBooks might not include some of these standard reports:

Employee Journal - This pivot table-based report lists payroll transactions by employee, including pay date, check number, and deductions.

YTD Summary - This worksheet-based report summarizes compensation and withholding by employee. This report appears on a worksheet labeled YTD Summary 1.

Hours - This pivot-table based report summarizes hours by employee, by type - this enables you to see regular, overtime vacation, and sick hours in a concise format.

Rate & Hours by Job - This report summarizes payroll data on a job by job basis. Make a choice from the Customer:Job drop down at the top of the worksheet to see costs for a particular customer or job.

State Wage Listing - This report breaks out state-assessed taxes, such as unemployment, so that you can see the wages, excess wages, wage base, and tax amount for the specified report period.

Quarterly - This report provides summary level wage and tax data sorted by Social Security Number by quarter. If you choose to see payroll for an entire year, this report will include a column for each of the four quarters.

Depending upon your QuickBooks version, you may also be able to generate these reports:

8846 Worksheet - This worksheet helps employers calculate the tax credit that can be collected on employer taxes paid on certain types of employee tips.

Effective Rates By Item - This pivot table-based report appears in a worksheet labeled Calculated %, and shows the percentage used to calculate various taxes, including Social Security, Medicare, and unemployment, as well as other payroll items such as disability and workers' compensation.

YTD Recap - Similar in nature to the YTD Summary, this worksheet-based report lists employee activity on a payroll item-basis. This report will appear on a worksheet labeled YTD Summary 2.

Deferred Compensation - This report displays any compensation and deferred compensation by tax-tracking type, and is sorted by date.

943 Worksheet - This worksheet helps employers that are required to file Form 943, Employer's Annual Tax Return for Agricultural Employees.

   

Back to Top

How To Generate Payroll Summary Reports in Excel

You can easily analyze your payroll data in Excel:

  1. In QuickBooks choose Reports, Employees & payroll, and then Summarize payroll Data in Excel.
  2. Instructions will appear onscreen in Excel if you need to enable macros. Think of macros as custom programming embedded in an Excel workbook. QuickBooks ships with prebuilt Excel workbooks that contain the programming necessary to generate the Excel worksheets, but you must first instruct Excel to enable macros.
  3. Once macros are enabled, the QuickBooks payroll Reports Workbook window will appear.

This dialog box allows you to determine what reports should be generated in Excel from QuickBooks.

The export from QuickBooks may take a couple of minutes, depending upon the size of your QuickBooks file - you'll see onscreen progress indicators. As shown in Figure 1, a workbook with several reports will appear automatically. As discussed previously, you can double-click on any number within a pivot table-based report and view the underlying detail, as shown in Figure 2.

The Summarize Payroll Data in Excel feature creates a variety of reports with just a couple of mouse clicks.

How To Generate Payroll Summary Reports in Excel


Figure 1: Double-click on any number within a pivot table to view the underlying detail on a new worksheet.

Figure 2

How To Print One Employee Per Page

You can set a pivot table-based report, such as the Employee Journal, to print one employee per page:

  1. Right-click on the Grand Total row, and choose Hide Row. Otherwise the grand total will appear on the last employee's report.
  2. In the case of the Employee Journal, right-click on the Transaction Name heading, and then choose Field Settings.
  3. In Excel 2003 and earlier, click the Layout button, or in Excel 2007 click the Layout & print tab. Choose Insert page Break After Each Item.

Pivot table-based reports can be printed on a per-item or per-employee basis.

You can apply this technique to any of the pivot table-based reports.

How To Create Payroll Tax Forms Worksheets in Excel

This feature - if present in your version of QuickBooks - makes it easy to view the underlying detail for the tax forms that QuickBooks can generate for you. As you're probably aware, it's easy to print payroll tax forms:

  1. Choose Emliloyees, payroll Tax Forms &amli; W-2s, and then process payroll Forms.
  2. Choose either Federal or State, and then click OK.
  3. Choose a form from the resulting list, and then follow the onscreen prompts.

Most payroll tax forms can be printed directly from QuickBooks. If you have questions about the numbers that appear on these reports, or you want to audit the figures, the Tax Form Worksheets in Excel provides the underlying detail with just a couple of mouse clicks.

  1. Choose Reports, Employees & Payroll, and then Tax Form Worksheets in Excel.
  2. After a moment an Excel workbook will appear onscreen. If a Welcome screen appears, follow the onscreen prompts to enable macros in Excel. Once macros are enabled, you'll see the dialog box QuickBooks Tax Worksheets.
  3. Once you choose a tax form, choose a report period from the list, or enter the dates of your choice.
  4. The Options/Settings button displays the QuickBooks Tax Forms Workbooks – Options / Settings dialog box, which lets you fine-tune the results provided by QuickBooks.

QuickBooks can show you the underlying detail for several payroll tax forms.

Quarterly 941 - This worksheet summarizes the figures you need to complete your quarterly From 941, which you use to inform the Internal Revenue Service of the total income taxes withheld from employee paychecks, as well as the employee and employer share of Social Security and Medicare taxes.

Annual 944 - In certain instances the IRS will notify an employer in writing that Form 944 can be filed annually instead of filing Form 941 on a quarterly basis. Do not file Form 944 unless you receive instructions from the Internal Revenue Service.

Annual 940 - This worksheet provides the detail required to file your Federal Unemployment Tax Return.

Annual 943 - This worksheet provides the detail needed to compile the Employer's Annual Federal Tax Return for Agricultural Employees.

Annual W2/W3 - This choice gives you the underlying detail for each employee's W2 form, as well as the summary figures that make up your W3 form.

Download IRS Forms: All IRS forms are available for free download. For instance, you can download Form 941 nad many other useful Federal Tax Forms at www.irs.gov.

State SUI Wage Listing - This form provides the details behind your State Unemployment Insurance (SUI) form.

Most users won't find it necessary to do so, but you can refine how QuickBooks generates the tax forms and payroll summary worksheets.

By default QuickBooks lists the company name and report dates in the page headers of your Excel worksheet. You won't typically see these onscreen, but you will when you choose to print the worksheet, or display it in Print Preview mode.

Hide Detailed Data Returned from QuickBooks - depending upon the tax form you choose, you may see more or less detail onscreen. Typically you'll want to leave this choice selected.

How To Change Report Options

You don't have to return to QuickBooks if you decide that you want to generate a different tax form worksheet, or perhaps change the report dates. The steps differ slightly, depending upon your Excel version:

Excel 2003 or earlier: Choose Get QuickBooks Data or Update Tax Worksheet from the floating QuickBooks Link or QuickBooks Tax Link toolbars, respectively. These toolbars are easy to restore if you inadvertently close them: right-click on any of your Excel toolbars, and then choose QuickBooks link or QuickBooks Tax link.

Excel 2007 - Click on the Add-Ins tab of the ribbon, choose QB Payroll Summary Reports in the Custom Toolbars section, and then choose either Get QuickBooks Data to update the payroll summary, or Update Tax Worksheet to update a tax form.

Note that if you simply change the dates for the payroll summary or a tax form, your existing worksheet will be overwritten. However, if you choose a different tax form, an additional worksheet will appear within your workbook.

Use QuickBooks Pro to manage your business and personal checking account.

QuickBooks Pro can be used to manage many different files. In your case, you could have a QuickBooks file called "Business", and a completely separate QuickBooks file called "Home" to manage the separate checkbooks. For more information on the creation of a new company file, click on File in the menu bar, then New Company.

Back to Top

Call now to speak with a CPA and receive a no cost initial consultation.