- Ten Overlooked QuickBooks Reports That You Should Use
- 16 Bank Reconciliation Tips and Tricks
- Use Accounting Ratios to Stave Off Financial Problems
- Profit & Loss Report Versus Statement of Cash Flows
- QuickBooks Helps You Navigate Tricky Waters
- Get the Job Done and Track Costs too!
- How To Easily Analyze Payroll Transactions in Excel
- How To Generate Payroll Summary Reports in Excel
- Use QuickBooks Pro to manage your business and personal checking account.
- Save Time and Reduce Mistakes by Synchronizing Your Data
- How To Easily Track Your Inventory
- How to Take the Pain Out of Paying Your Bills
- 4 Ways to Manage Prices in a Down Economy
- QuickBooks 2012: New Paths to Better, Faster Financial Management
Billing for Time and Expenses: How It Works
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:
- Accounts Receivable
- 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. As shown in Figure 1, you’ll be able to see:
- How much cash you’ve taken in from sales and spent on expenses
- 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.
Figure 1: The Statement of Cash Flows report explains changes in your bank account balance.
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. As shown in Figure 2, 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 shown in Figure 3. 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.
Figure 2: The Collections Report gives you a jump start on dunning overdue customers.
Figure 3: You can adjust the wording of an overdue invoice e-mail for one customer at a time or change the default text.
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 shown in Figure 4. As with most reports in QuickBooks, you double-click on amounts to ultimately drill down to the original transaction.
Figure 4: The A/P Aging Summary helps you determine when bills are slipping into overdue status.
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. As shown in Figure 5, 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!
Figure 5: The Voided/Deleted Transactions Summary enables you to find transactions that appear to have vanished.
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, as shown in Figure 6. 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.
Figure 6: The audit trail shows every transaction-including modifications-in QuickBooks.
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. As shown in Figure 7, you can choose from multiple reports.
Figure 7: The Previous Reconciliation report option allows you to reprint missing account reconciliation reports.
10. Transaction History: Think of this as a ‘report within a report’, as you can only run it in certain circumstances. As shown in Figure 6, 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. As shown in Figure 8, QuickBooks will display a report that shows the entire history for a given transaction.
Figure 8: The Transaction History report provides shows all activity related to 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!
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 shown in Figure 1 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, as shown in Figure 2, 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.
Figure 1: The QuickBooks Begin Reconciliation window.
Figure 2: The QuickBooks Reconcile window.
1. Locate discrepancies As shown in Figure 1, click the Locate Discrepancies button to display the Locate Discrepancies window shown in Figure 3.
From there, click the Discrepancy Report button to display the report, as shown in Figure 4. This identifies any edited or deleted transactions that may affect your reconciliation.
Figure 3: QuickBooks can help you identify edited transactions that may disrupt your reconciliation.
Figure 4: Ideally your discrepancy report should never have any transactions listed.
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 window shown in Figure 1. 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. As shown in Figure 5, you can right-click on an amount, and then choose Edit Transaction to fix the error.
Figure 5:Right-click on an amount and choose Edit Transaction to correct a mistake.
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 shown in Figure 2 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 window shown in Figure 6 appears, you must complete the deposit process for these transactions.
Figure 6: Undeposited funds can pose problems with your reconciliation.
10. Hide unnecessary transactions Click the Hide Transactions after the Statement’s End Date check box shown in Figure 2 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 shown in Figure 2, 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.
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.
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 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.
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, and 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. As shown in Figure 3, change the Display Columns By to Months, and then click OK.
Figure 3: Change Display Columns By to Months when you want a month-by-month report.
When QuickBooks displays the 12-month report, as shown in Figure 4, click the Export button, and then click OK to send the report to Microsoft Excel.
Figure 4: You can convert the Balance Sheet Standard report into a twelve-month format.
As shown in Figure 5, row 9 contains the Accounts Receivable figures. In cell R9, enter this formula to calculate your average accounts receivable balance: =AVERAGE(F9:R9).
Figure 5: 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:
- Product Suggestion, as shown in Figure 6
- Bug Report
- Help 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.
Figure 6: Submit your wish-list items directly to the development team from within QuickBooks.
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. You can easily see the significance of these differences in Figures 1 and 2.
Figure 1: Cash-basis reports only reflect paid transactions.
Figure 2: Accrual-basis reports include all transactions – both paid and unpaid.
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.
As shown in Figure 3, specify either Cash or Accrual, and then click OK.
Figure 3: You can set either cash or accrual as your default report format.
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, as shown in Figure 4.
Figure 4: You can change the accounting method for your P&L on the fly.
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, shown in Figure 5, 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: As shown in Figure 5, 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 5: 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.
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 5. 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 5, 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. As shown in Figure 6, the QuickZoom icon appears when you hover over an amount. Double-click to display a detailed report, as shown in Figure 7.
Figure 6: The QuickZoom icon indicates that you can drill-down within a QuickBooks report.
Figure 7: A detailed report appears when you double-click on an amount within a QuickBooks 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:
- Choose Edit, and then Preferences.
- Choose Reports & Graphs, and then Company Preferences.
- Click the Classify Cash button, shown in Figure 3.
As shown in Figure 8, 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.
Figure 8: QuickBooks allows you to classify accounts as operating, financing, or investing activities.
Did You Know?
QuickBooks has a Product Information window that can provide a dizzying array of information. Press Ctrl-1 to display the window shown in Figure 9. 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.
Figure 9: Press Ctrl-1 to view the Product Information window.
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.
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 (such as 2010 or 2011), 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 similar to Figure 1. You won’t see any numbers if you chose the From Scratch option in step 5.
Figure 1: Starting with prior-year actual numbers can jumpstart your budget process.
- 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. As shown in Figure 2, 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.
Figure 2: The Adjust Row button makes it easy to quickly increase or decrease budget figures by a dollar amount or percentage.
QuickBooks offers four budget and two forecast reports. You’ll use these steps to run most of these reports:
- Choose Reports, Budget & Forecasts, and then the report of your choice.
- A three-screen wizard appears, asking you first which budget or forecast you wish to use. Once you’ve made a selection, click Next.
- 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 – As shown in Figure 3, 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.
Figure 3: Budget overview gives you a birds-eye view of your 12-month budget.
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, as shown in Figure 4. 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.
Figure 4: Narrow column widths can condense particularly wide reports.
- 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, as shown in Figure 5.
Figure 5: It’s easy to add or remove columns on any QuickBooks report.
- Budget vs. Actual Graph – This report doesn’t enable you to choose a budget – the current year budget is displayed automatically. As shown in Figure 4, 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.
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:
- Choose Reports, Company and Financial, 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:
- Click the Modify Report button.
- In the Columns section, choose Year from the Display Columns By drop-down list, and then click OK.
- On the report screen, choose a date range, such as 1/1/04 through 12/31/08, and then click the Refresh button. As shown in Figure 6, a multi-year comparison will appear onscreen. If you find this format helpful, click the Memorize button to save this report for later use.
Figure 6: You can convert the Profit & Loss Prev Year Comparison into a multi-year report.
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?
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:
- Choose File, Utilities, and then Convert Accountant’s Copy to Company File (QBW).
- Choose the Accountant’s Copy to convert.
- Click OK on the prompt shown in Figure 7.
- 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.
Figure 7: Converting an accountant’s copy to a working QuickBooks company can serve as a disaster recovery method.
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.
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:
- Click on the Customer Center button on the toolbar (or press Ctrl+J).
- Right-click on a customer name in on the Customers & Jobs tab, and then choose Add Job, as shown in Figure 1. 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.
The Add Job choice appears amidst many other choices.
- At this point the New Job window appears, as shown in Figure 2. 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.
The New Job dialog box is almost a mirror image of the New Customer dialog box.
- 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. For instance, Figure 3 shows the Customer:Job field on the Enter Bills screen. 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.
The Customer:Job column on the Enter Bills window is one way to assign expenses to a job.
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. The Enter Time window is shown in Figure 4.
Enable Time Tracking: Choose Edit, Preferences, Time Tracking, and then Yes to enable time tracking.
Use the Enter Time window to assign billable hours to a job.
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.
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:
- Choose Company, Planning & Budgeting, and then Set Up Budgets.
- Click the Create New Budget button to launch the Create New Budget wizard.
- Specify a fiscal year, choose Profit and Loss, and then click Next.
- Choose Customer:Job on the Additional Profit and Loss Criteria screen, and then click Next.
- Choose Create Budget from Scratch on the Choose How You Want To Create a Budget screen, and then click Finish.
- You’re now presented with the screen shown in Figure 5, where you can select a customer and/or job, and then enter your budget on an account by account basis.
All customer and job budgets are stored within a single budget screen.
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 FY2009-Profit and Loss by Account and Customer :Job.
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: As shown in Figure 6, 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, as shown in Figure 7.
You can assign vehicle mileage to jobs-and specify whether the miles are billable.
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.
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.
You can easily analyze your payroll data in Excel:
- In QuickBooks choose Reports, Employees & Payroll, and then Summarize Payroll Data in Excel.
- 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.
- Once macros are enabled, the dialog box shown in Figure 1 will appear.
This dialog box allows you to determine what reports should be generated in Excel from QuickBooks.
- Choose a time period, such as this Month, and then choose any of the optional reports.
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 2, 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 3.
The Summarize Payroll Data in Excel feature creates a variety of reports with just a couple of mouse clicks.
Double-click on any number within a pivot table to view the underlying detail on a new worksheet.
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:
- Right-click on the Grand Total row, and choose Hide Row. Otherwise the grand total will appear on the last employee’s report.
- In the case of the Employee Journal, right-click on the Transaction Name heading, and then choose Field Settings.
- 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, as shown in Figure 4.
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:
- Choose Employees, Payroll Tax Forms & W-2s, and then Process Payroll Forms.
- Choose either Federal or State, and then click OK.
- Choose a form from the resulting list, as shown in Figure 5, 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.
- Choose Reports, Employees & Payroll, and then Tax Form Worksheets in Excel.
- 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 shown in Figure 6.
QuickBooks can show you the underlying detail for several payroll tax forms.
- As you can see in Figure 6, you can generate one tax worksheet at a time:
- 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 at www.irs.gov/pub/irs-pdf/f941.pdf. Simply replace 941 with the corresponding form number in the preceding Internet address.
- State SUI Wage Listing – This form provides the details behind your State Umployment Insurance (SUI) form.
Once you choose a tax form, choose a report period from the list, or enter the dates of your choice.
- The Options/Settings button displays the dialog box shown in Figure 7, which lets you fine-tune the results provided by QuickBooks:
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.
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.
The New Year’s here, the Christmas bills are rolling in, and income taxes loom. Maybe you can’t save money just now, but how about an easy way to save time and keystrokes? If you use Microsoft Outlook 2003, 2007 or 2010 for contact management and QuickBooks Pro, Premier, or Enterprise 2005 and up for financial management, you can synchronize data to avoid entering the same contact information twice.
It’s easy, but you need to take care to follow instructions precisely anytime you’re integrating multiple databases. You can’t unring that bell. Start by backing up your data in each program, as shown in Figure 1. For Outlook, check your help files; the data file format changed in 2003. QuickBooks users should use the program’s standard built-in tools by clicking File|Save Copy or Backup. You can either save your QuickBooks file locally (to a CD or USB flash drive) or use QuickBooks Online Backup (30-day free trial; starts at $4.95/month for 5 GB).
Figure 1: It’s very important that you back up your Outlook and QuickBooks files before you synchronize.
Get in Sync
QuickBooks lets you synchronize three kinds of contact information with Outlook:
- Customer contact information contained in your Customer & Jobs list
- Vendor contact information from your Vendor list
- Contact information on your Other Names list
Note: You can’t synchronize employee contact information.
To get started, click File|Utilities|Synchronize Contacts. QuickBooks Contact Sync must be installed on your PC before you do your first sync. When the window shown in Figure 2 opens, click OK and follow the instructions for downloading and installing.
Figure 2: QuickBooks provides a wizard that walks you through the process of downloading and installing QuickBooks Contact Sync.
QuickBooks will prompt you to shut down Outlook before you start, if you haven’t already done so. When the installation is finished, you’ll see the window shown in Figure 3. And you’ll notice that the installation has added a new toolbar to your copy of Outlook.
Figure 3: QuickBooks tells you when your QuickBooks Contact Sync has installed properly.
Click Finish and restart Outlook. You’ll see a window titled QuickBooks Contact Sync for Outlook (this can be disabled once you’ve gone through the initial import by unchecking the box in the lower left corner). Make sure you’re logged into QuickBooks as the Administrator and that the company file you want to synchronize is open.
Click Get Started. A box that says Connecting to QuickBooks will open, and there’ll be a short delay. After the connection is made, the Begin Setup window opens. Click the Setup button to launch the wizard. If you have more than one Outlook contact file (for example, if you use Outlook with Business Contact Manager), you’ll have to select the file you want to sync.
Click Next. The next screen asks you to specify which contact types you want to sync (customers, customer jobs, and/or vendors). If any of your contacts are personal, you can choose to exclude those. Click Next after each of those screens.
QuickBooks Contact Sync includes a mapping tool, which helps ensure that the correct fields in each program are matched. For example, Company in one program should “map” to Company in the other as shown in Figure 4.
Figure 4: QuickBooks Contact Sync helps you make sure that fields in each program “map” accurately to each other.
The final step in the setup process is critical if you don’t want to lose important data, so choose the next option carefully. You need to tell QuickBooks Contact Sync what to do if the same contact exists in both programs but their properties are not exactly the same. Your options:
- Let the Outlook data win
- Let the QuickBooks data win
- Decide in each individual case
Once you’ve made your selection, click Save. If you want to go back over any of these settings, click Setup. Otherwise, click Cancel or Sync Now.
After you’ve completed the first synchronization, you’ll need to perform a manual sync each time you want to make the databases match. To do so, click the QuickBooks ContactSync menu in Outlook. This menu provides a number of options, including Preferences.
Sync Now and Save Time Later
Saving time these days is saving money. You can use those extra minutes (or hours) to build your business instead of always having to worry about running it. QuickBooks Contact Sync can give you some of those extra minutes, help you avoid frustration, and aid in keeping your databases clean and up to date.
2011 is soon drawing to a close. Do you know where your physical inventory items are? Whether you keep them in a closet, in an unused office, or a warehouse, you need to keep a close watch on how many products you have, how many have been ordered, and when it’s time to reorder. Fortunately, QuickBooks has tools that help you track all of those numbers. If you’re conscientious about making use of them, you should have a good sense of the state of your inventory, wherever you store it.
Note: These tools are not available in Simple Start or QuickBooks Online.
Let’s take a look at the life of an inventory item. First, you have to tell QuickBooks that you will be selling products. It asks for this information during the EasyStep interview, but if for some reason you didn’t set this up, you can still do it. Click Edit/Preferences, then Items & Inventory, and then Company Preferences. Make sure the first line is checked, as well as any others you want active, as seen in Figure 1.
Figure 1: You can have QuickBooks track your inventory by selecting this option in the Preferences window.
Next, check your Chart of Accounts to see if you need to add any accounts to meet your inventory needs. This is easy. Go to Lists/Chart of Accounts, or click the icon on the home page. The Chart of Accounts is simply a list of the accounts your company uses, and the balance for each. QuickBooks sets a chart up for you based on the type of company you have, but as your business grows, you may need to add more. Figure 2 shows an example of the Chart of Accounts window.
Figure 2: The Chart of Accounts window shows you the balance for each of your accounts.
To add a new account, click on the arrow next to Account and click New. Select the correct type of account, and answer the questions in the Add New Account window. If you have any questions here, consult QuickBooks’ help file.
See Your Inventory In High Definition
Next you’ll have to define your company’s products. Click Items & Services on the home page. The Item list opens. You can always come back here when you need to edit an item, but you may want to create one now. So click Item/New. You’ll see a screen similar to the one pictured in Figure 3, but its fields will be blank. Item records in QuickBooks contain a good amount of information about each item, which will be used in forms like invoices and documents like reports.
Figure 3: An item record allows you to define your company’s product.
Fill out the information in each item record for each item you sell. You won’t be able to alter the numbers in the lower right corner; these come from other parts of the program. As for the other values, the need to make changes depends on the field. When you defined the item, you entered an On Hand amount. This of course will change as you sell, so you can change the reorder point. Conversely, the average cost (value of your inventory) is calculated by the program; it’s the total cost of items in stock divided by the number of items in stock. On P.O. and On S.O. simply indicate how much of your inventory is promised on purchase orders and sales orders.
Get Better Organized
QuickBooks lets you create assemblies, groups of items that are sold together as a kit. If you want to create one, click Lists/Item List, then click the arrow next to Item and click New. Click the arrow under Type, and select Inventory Assembly. Fill in the blanks on the window as you would for a single item, and select the individual inventory items in the box at the bottom. This box is pictured in Figure 4.
Figure 4: An item assembly.
Once you’ve defined your items and assemblies, you can use them in two places primarily: transactions forms and records. Let’s say you’re creating an invoice. As you’re filling out the form, you’ll be able to click the arrow under ITEM and view a list of the items you’ve created. Click on one, and the details you’ve entered (like price) will appear. Click Reports/Inventory to customize and run the reports available.
Maintaining an adequate inventory-not keeping too much or too little on hand-is critical to your company’s financial balance. QuickBooks’ tracking tools can help you meet that ongoing goal.
Settle Up Fast with Quickbooks’ Bill Paying Tools
Some of the financial crystal ball-types are telling us there are signs that the recession may be drawing some of its last breaths. But those bills are still coming in, and you may have had a long, dry summer and less income that you can use to meet those business obligations.
The desktop versions of QuickBooks can help. They can’t magically make more money appear in your coffers, but they can help you manage your bills so you’re always aware of what’s coming up and don’t get any nasty surprises. This keeps both you and your vendors happy, and minimizes the chance of affecting your credit report adversely. You can also maximize cash flow by being hyper-aware of when each bill is due and timing them appropriately.
(These bill-paying tools are available in all QuickBooks versions above Simple Start.)
Enter First, Then Pay
Of course, you can mimic your old manual method of bill paying by simply using QuickBooks’ check-writing convention. But if you do this, you risk paying the bill twice. If you follow the process shown in Figure 1 by entering and the then paying, you’ll ensure that you record the expense in the same period it occurred.
To start, click the Enter Bills or Vendors/Enter Bills icon. The Enter Bills dialog box opens as shown in Figure 2. If you received a bill, be sure that box in the upper right is checked, and that the Bill radio button is filled in.
Figure 1: You’ll find these icons on QuickBooks’ graphical flow chart.
Figure 2: The Enter Bills dialog box.
Next, click the arrow next to the Vendor line to select an existing vendor or add a new vendor. Change the date if necessary, and enter a reference number (this may avoid confusion later). Then, enter the amount due.
When you initially set up vendors, you either set up terms for each vendor or accepted the default. So the Terms field should already be filled in, and will generate the correct bill due date. Enter a descriptive memo in that field if you’d like.
Tip: Use the right-click menu when you’re entering bills to see more options.
Since this was an expense, you’ll want to record it as such. Make sure the Expenses tab is highlighted, and click in the Account field. Click the arrow that appears to drop down the list, and select the appropriate expense type. Fill in the rest of the field on the line, making sure to check the Billable box if this is something you can bill back to a customer. If the expense needs to be split into separate categories, create a new line and amount for each. Your bill now looks something like Figure 3.
Click the Items tab and fill out the fields there if your expense involves products. You must have Inventory turned on to do this. Click Save & Close or Save & New. QuickBooks now works in the background, increasing Accounts Payable and dropping the bill into several reports.
Figure 3: Make sure your completed bill entry screen is as complete as possible.
Paying Your Debts
When it’s time to pony up, click on the Pay Bills icon, or click Vendors/Pay Bills. You’ll see a screen similar to Figure 4. Check the radio button next to the correct preference to view all bills, or to limit the list to those on or before a specific date. Put a check mark next to the bill(s) you want to pay. The correct amount should fill in by default, but you can change this to make a partial payment.
If you want to view the bill, take a discount, or use credits, click on those buttons. Select a payment date, method (check or credit card), and toggle to the correct account if it’s not showing.
Figure 4: The Pay Bills dialog box. Make sit easy to finish the job.
Once you’ve paid a bill, your Accounts Payable and checkbook balances decrease, and the vendor balance and reports are updated. QuickBooks stamps a PAID watermark on the bill to avoid confusion later on.
Tip: To find bills you’ve already paid, go to the Vendor Center.
So stop stacking your bills on an old spindle and ruffling through them every day to see what’s due. You’ll find that there are numerous benefits to using QuickBooks’ bill-paying features, such as an improved credit rating, a dearth of past-due notices, and better cash flow.
We are living in a period of “accelerated change”. Indeed, the ground does seem to be shifting beneath us almost faster than we can comprehend, so it’s important to stay nimble in these difficult times.
One way you can do so is to closely manage your prices. In some cases you may need to ratchet your prices up to cover a commodity cost-spike. Or, you may want to offer special deals to your best customers to help retain their business.
In this article we’ll discuss four methods you can use to manage prices (and change) within QuickBooks.
Create Discount Calculations
Studies have shown that it’s far easier to get additional sales out of existing customers rather than from new customers. Targeted discounts are just one way to try to encourage your customers to buy more.
However, if you do offer a discount, don’t just type over your standard prices on the QuickBooks invoice, create a discount calculation instead. This accomplishes two things:
- Your customers see on their invoice exactly how much of a break you’ve given them.
- You can track how successful your campaign was.
It’s easy to set up a discount calculation:
- Choose Lists, and then Item List.
- Click the Item button, and then choose New from the menu (or press Ctrl+N).
- As shown in Figure 1, Choose Discount from the Type list.
- Assign an item name, complete the description field, and then enter an amount or a percentage.
- Choose an account from the list-you may wish to create a separate account so that you can easily track the amount of discounts that you’ve offered.
- Choose Tax or Non-tax to indicate whether the discount is applied before or after sales tax, and then click OK.
Figure 1: A discount item allows you to create and track percentage or amount based discounts.
Keep in mind that discounts only apply to the previous row of the invoice or sales receipt. To apply the discount to multiple items, you must create a Subtotal item:
- Choose Lists, and then Item List.
- Click the Item button, and then choose New from the menu (or press Ctrl+N).
- Choose Subtotal from the Type list, and then assign an Item Name and Description, as shown in Figure 2.
Figure 3 shows a multi-line invoice, along with a subtotal and a discount on all of the items.
Figure 2: A subtotal item allows you to apply a discount to multiple items on an invoice or discount.
Figure 3: Include a subtotal on your invoice when you wish to discount multiple items.
Use Price Levels
Price negotiations are becoming more prevalent and you may find that you have to offer a standard discount to one or more customers in order to keep their business.
In such cases, you might find the price level feature helpful, so that you don’t have to remember to include a discount item on each invoice:
- Choose Lists, and then Price Level List.
- Click the Price Level button, and then choose New (or press Ctrl-N).
- As shown in Figure 4, assign a name to the price level, such as 10% Discount.
- QuickBooks Pro users can only establish Fixed % price levels, which are applied globally to all products. QuickBooks Premier and Enterprise users also have the option to create Per Item discounts, where you can selectively discount only certain items.
- Specify whether to increase or decrease item prices, and optionally choose a rounding method.
Figure 4: Price levels allow you to apply automatic discounts to everything a customer purchases.
Note: You can use price levels to increase or decrease prices.
Change Item Prices
Competitive or other pressures may mean that you need to globally change all of your prices at once. Fortunately, you can use the Change Item Prices feature to do so:
- Choose Customers, and then Change Item Prices.
- As shown in Figure 5, select an Item Type from the list, and then select the items you wish to change, or click the Mark All checkbox.
- Indicate a percentage or dollar amount to increase prices by. This can be based on the current price or current cost of the item. Enter a positive number to increase the price, or negative number to decrease the price.
- Click the Adjust button to see the impact of your changes in the New Price column, and then click OK to make the changes permanent.
Timesaver: You can also manually fill-in the New Price column if you prefer to make targeted adjustments to selected items. This is easier than manually opening each item one at a time.
Figure 5: The Change Item Prices feature allows you to adjust multiple prices at once.
Add a Surcharge
We’re fortunate that gas prices are currently far less than were they were just a few months ago. However, who knows how far they may go this summer during peak driving season.
At some point you may need to consider adding a fuel or other type of surcharge to help recover costs beyond what you’ve factored into your existing prices:
- Choose Lists, and then Item List.
- Click the Item button, and then choose New from the menu.
- As shown in Figure 6, choose Other Charge from the Type list.
- Assign an item name, complete the description field, and then enter an amount or a percentage.
- Choose an account from the list, and then click OK. As shown, you may wish to create a separate account so that you can easily track the amount you earn from the surcharge.
Important: As with discounts, Other Charge items only apply to the preceding row on an invoice or sales receipt. Be sure to add a Subtotal item to your invoice if you want the surcharge to apply to multiple rows of your invoice or sales receipt.
Figure 6: The Other Charge feature allows you to compute fuel and other surcharges.
As it usually does this time of year, Intuit has introduced new versions of its Pro and Premier products. QuickBooks 2012 promises to help you get better organized, save steps, and acquire more in-depth financial insights.
The new Express Start is designed for businesses that want to blast through setup and start entering customers and invoices. You have two other options though.
Advanced Setup is the old EasyStep interview that solicits more details. You can also open an existing file or convert data from Quicken or other accounting software.
Express Start requires minimal input. Company name, industry, company type, tax ID, and contact information is all that’s required. After you save your company file, it lets you start adding or importing customers/vendors/employees, products/services, and bank accounts.
Figure 1: Express Start simplifies company setup
An Activity-Driven Calendar
QuickBooks’ Reminders keep you apprised of each day’s tasks, but they don’t provide any information about the past or future. QuickBooks 2012 solves this problem with its new Calendar. When you enter an appointment, to-do, or key business task (invoices, bills, purchase orders, etc.), it appears in the calendar. You can display a graphical view of the month that tallies activities for each day and lists them below. Daily and weekly views are in list form and links open the original documents.
Figure 2: The new Calendar displays daily, weekly, and monthly views of your financial transactions
Save Excel Formatting
Once you’ve formatted a QuickBooks report in Excel, it’s frustrating to have to reformat it each time you run it for different time periods and/or with your ever-changing content. Excel Integration Refresh simplifies this process. You can now export a report to Excel, make formatting changes and save them, and then reapply them later to the same type of report using different date ranges and your updated QuickBooks data. Acceptable alterations include:
- Row and column header font formatting
- New formulas
- Renamed column and row headers, and report titles
- Resized columns
- Inserted columns and rows
- Inserted formula text
You can do this by opening your report in QuickBooks and clicking Update an existing worksheet, or by launching your report in Excel and clicking the QuickBooks tab on the toolbar, then the Update Report button.
Figure 3: This window opens when you click Update Report in Excel
A New Report Community
There’s always room for more report formats. QuickBooks 2012 offers a library of Contributed Reports, variations created either by Intuit or your fellow users. You can select one of these, like Customer Sales By Quantity By Item Detail and instantly populate it with your own data.
You can sort these templates by industry and rating, and view them as a list, in a grid, or in the Report Center’s Carousel view.
QuickBooks 2012 also saves you time with its new Centers. The Inventory Center works similarly to those available for customers, vendors, and employees. It’s a clearinghouse of item records and transactions that can be viewed and sorted. You can also do inventory housekeeping tasks here, like adding items and launching transactions.
The Lead Center helps you carefully track new leads that you either paste in from Excel or enter manually. You can add to-dos and notes to contact records, and convert them into customers.
Upgrading Can Be Tricky
Intuit has included other, smaller time-saving organizational and reporting tools in QuickBooks 2012, like One-Click Transactions, which lets you create related transactions from existing ones (i.e., invoice to credit memo) with one click.
There’s nothing especially difficult about using most of QuickBooks 2012’s new features. But upgrading and setup are sometimes quirky, and the Excel Integration Refresh tool has a learning curve. We’re happy to help you start your company file on the right foot or get acclimated to this latest version.
Figure 4: Track your leads and convert them into customers in the new Lead Center.
Billing for inventory parts is easy. Pick the items from a list and specify a quantity. Poof. Done.
Billing for costs, time or mileage is a little more complex. QuickBooks has built-in tools to help you do this, but it’s a bit of a process.
To simplify your workflow, do this groundwork first:
- Go to Edit | Preferences | Time & Expenses | Company Preferences. Click the Yes button under Time tracking and indicate your choices under Invoicing options. If you plan to mark up some costs and want a default number, enter a percentage and account (these can be changed on individual invoices).
Figure 1: As you do with other QuickBooks processes, make sure that your Preferences are set correctly.
- Add any billing items necessary by clicking Lists | Item List and then Item | New in the lower left corner.
- If you plan to bill for mileage, go to Lists | Customer & Vendor Profile List | Vehicle List and enter information about every business vehicle.
Invoicing for Services
If you’re a service-oriented company, you bill for time frequently. This is easy. You’re probably already familiar with the Enter Time entry in the Employees menu. Whether you make individual time entries or complete a timesheet, it’s critical that you make the correct selections for each Customer: Job, Service Item and Payroll Item field, and check the Billable box.
When you create invoices, this box will open after you select a customer:
Figure 2: QuickBooks lets you know when there are time and costs to be billed for each customer.
You can let QuickBooks enter the time totals now, or add them later by clicking the Add Time/Costs button. Either way, the Choose Billable Time and Costs window opens. Add a checkmark next to each entry that should be billed, and click Optionsâ¦ to indicate whether you want one line for each time entry or would rather combine all similar service item types.
Figure 3: QuickBooks wants to know which entries should be invoiced.
If you’re done with billable expenses for this invoice, click OK. If there are other costs that you covered, click the Expenses tab to see all transactions that you earmarked for this client on a bill, check or credit card. You have the option here to mark up the cost by a percentage or amount (even if you established this in Preferences), and to specify an account.
Do the same for Mileage, which you would have entered previously — when it was incurred — at Company | Enter Vehicle Mileage. Then select any Items that you purchased for the customer. Your records should be correct — assuming that you were conscientious about assigning expenses to customers and jobs.
Figure 4: It’s easy to pull billable expenses into invoices if they’re documented carefully.
Turning expenses into invoices and then into income can be complicated. Let us know if we can help. We are your partner in building a successful business.
Here’s a cool little keyboard shortcut. Hit F2 while you’re in QuickBooks, and you’ll get the Product Information screen. It’ll tell you everything you want to know about your specific copy of QuickBooks, like your release and license number, the file size, number of users logged in, audit trail status and the total number of accounts, customers, employees, etc.
As of May 31, 2012, Intuit is discontinuing support for QuickBooks Pro, Premier and Simple Start 2009, QuickBooks for Mac 2009 and QuickBooks Enterprise Solutions 9. You can continue to use these solutions, of course, but live technical support and add-on services like payroll, credit card processing, online banking and bill-pay will not be accessible. Talk to us about upgrading if you’re using any of these products or services.