New Business Direction LLCDid you know that there are at least 40 different file extensions in QuickBooks®? Those are just the ones that I found when researching this article – I am sure there are many more!
There are extensions for everything from Loan Manager and Financial Statement Designs to Online Banking and Images. There is even one called LGB which stands for Little Green Box. This file contains encrypted information on user names and passwords.
Here is just a sampling of the file types and extensions in QuickBooks.
It is important for accounting professionals to understand the different file types and file extensions involved with QuickBooks®.
There are five (5) main file extensions used for holding transactions and data for a QuickBooks® company file. The table below describes these file extensions.
| Extension |
File Type |
Description |
| .QBW
|
QuickBooks® for Windows company file |
This is the main file type for a QuickBooks® company file. All of your data is entered into this file. |
| .QBB | QuickBooks® backup file
|
A backup file is a compressed file containing everything you need to recreate your company file and QuickBooks® environment. Use a backup file to safeguard your QuickBooks® files against accidental data loss. When you create a backup, QuickBooks® starts a log of transactions (.TLG) that you have entered since the last time you backed up. To open a .QBB file, go to the File menu and click Restore. Double-clicking the file and choosing Open from the File menu will NOT restore or open a backup file. |
| .QBM
|
QuickBooks® Portable company file | This is a compressed file that contains all of the data of a QuickBooks® company file, but not the database indexing, so it is much smaller than the .QBW or .QBB files. It is useful for transferring data through the Internet because the data file size is much smaller than any of the other file types and it can be restored to the full .QBW on the receiving end. |
| .QBX
|
Accountant’s Copy (Export File) |
This is also a compressed file format and it is used specifically for transferring a file from an end user (client) to an accountant for review. This includes a Dividing Date which prevents the client from creating transactions on or before this date. |
| .QBA
|
Accountant’s Copy (Working Copy) |
When the accountant opens a .QBX file they must restore and convert it to a .QBA file in order to enter transactions and run reports. |
It is important to assess the client’s needs and determine which file type is appropriate for the situation. It will depend on the type of work to be performed, the condition of the client’s current data file, the nature of their operations and a number of other factors. If you have a solid
understanding of the different file types and options available, then you can make a more informed decision on how to best serve the needs of your client.
QuickBooks® also uses many other file extensions to access or store other types of data associated with QuickBooks®. The table below describes a few of these file extensions. It is not an all-inclusive list.
|
Extension |
File Type |
Description |
|
.QBW.TLG
|
Transaction Log File for QuickBooks® company file
|
When you backup a company file, QuickBooks® starts a log of transaction that you have entered since the last backup. In case of accidental loss of data, Intuit Technical Support can use your most recent backup in conjunction with the transaction log file to recover your data. This log file is also used if you sync your QuickBooks® data with certain online services using Intuit Sync Manager. This allows ongoing syncs to happen much more quickly than the initial full upload of QuickBooks® data into your online service. Note: A manual backup (not an automatic backup) is required to ‘reset’ the .TLG file. If the .TLG file is too large, create a manual backup. |
| .QBA.TLG
|
Transaction Log File for Accountant’s Copy
|
When you backup an Accountant’s review copy, QuickBooks® starts a log of transaction that you have entered since the last backup. In case of accidental loss of data, Intuit Technical Support can use your most recent backup in conjunction with the transaction log file to recover your data. Note: This file is created during backup provided that you have set a verification level. |
| .QBY
|
Accountant’s Copy (Import File)
|
When you (the Accountant/Consultant) are finished making changes in an Accountant’s Copy, you provide the client with a .QBY file to be imported into their company file. Note: This file only includes the changes made by the Accountant and not all of the transactions of the company. |
| .IIF
|
Intuit Interchange Format file
|
You can import and export lists and/or transactions using text files with an .IIF extension. |
| .ND
|
QuickBooks® Network Data file
|
A configuration file that allows access to the QuickBooks® company file. Note: DO NOT DELETE this configuration file. |
| .DES |
Form Design Template file |
This file type is created when you export a form design from the templates list. |
For a printable list of file types and extensions, click here.
This article of QuickBooks Tips and Tricks was based on the 2012 version of QuickBooks.
I was telling a friend of mine about the topic of this months’ newsletter and he said “you’re the expert in QuickBooks® and you make a living from setting it up and cleaning it up and teaching people how to use it – why in the world would you tell people NOT to use it?”
The answer is simple – QuickBooks® is a really great program, it’s relatively low cost and it works well for most small businesses. However, one size does not fit all. It’s like that suit in the back of your closet. If QuickBooks® doesn’t fit, it won’t work and it won’t get used.
Fortunately, QuickBooks® has many solutions in their product line-up and with our expertise and knowledge we can customize and tailor a program specifically for you and your small business.
Data File Sizes and Transaction Limits
What impacts whether you should use QuickBooks® Pro or Premier or the more powerful version, Enterprise Solutions, is the size of your data file today and how that data is expected to grow over the time period for which you want to keep all transaction data in one (1) company data file.
The rate of growth of QuickBooks® company data files varies significantly from company to company. There is no “average” or “typical” data file size, since businesses track different information. How quickly a file grows depends on the number of transactions, the amount of information entered in each transaction and the number of “links” per transaction.
If your business generates more transactions than QuickBooks® can handle, performance may be affected. Technically, QuickBooks® Pro and Premier can handle two billion transactions in a single company file. In practice, however, tasks like running reports and finding transactions can take longer if the file grows too large.
We recommend that QuickBooks Pro and Premier data files should not exceed 200MB and that Enterprise Solutions files should not exceed 300 MB. These are general guidelines, based on several factors. There are no specific maximum file sizes and your system will not come to a crashing halt if you exceed these recommended sizes, however, as the data file grows, the overall performance of the software declines and the risk of data corruption increases.
To see how large your data file is currently, open your QuickBooks® file and press the F2 key. A Product Information box will appear and your data file size will be listed on the left hand side of the screen under File Information.
Item or List Limitations
QuickBooks® also has item limitations – counts that cannot be exceeded. Companies with list requirements that exceed those in the following table are not a good fit for QuickBooks®. This list highlights some of the more important physical limits and it is not an all-inclusive list. These counts are “combined” counts. For example, in Premier, you can have 14,500 customers, vendors AND items combined, not 14,500 per list.
| List Name | Pro and Premier | Enterprise Solutions |
| Chart of Accounts | 10,000 | 10,000 |
| Names (customers, vendors, employees and other names) | 14,500 | >100,000 |
| Items (including inventory) | 14,500 | >100,000 |
| Classes | 10,000 | 10,000 |
| Price Levels | 100 | 750 |
*For an all inclusive list click here
For companies that require a more robust inventory, we recommend using Enterprise Solutions, QuickBooks® Point of Sale or an add-on software such as Fishbowl.
To find out how many items your data file is currently using, open your QuickBooks® file and press the F2 key. A Product Information box will appear and your List Information will be shown on the right hand side of the screen – be sure to scroll down to see all of the lists.
User Access Limitations
QuickBooks® limits the number of users who can access the company data file simultaneously. Companies that require more users simultaneously accessing the company data file than QuickBooks® supports are not suited for QuickBooks®.
Inventory Method Limitations
Companies that require an inventory method other than average cost (such as FIFO or LIFO) may want to use QuickBooks® with an add-on software for the inventory feature. Note that an exception is QuickBooks® Online Plus – as of mid-2010 it offers Inventory using the FIFO cost method. Another exception is QuickBooks® Enterprise Solutions for 2012 with Advanced Inventory which has a FIFO option as well.
| QuickBooks® Product | # simultaneous users |
| Pro | 3 |
| Premier | 5 |
| Online Plus | 5 |
| Enterprise Solutions | 30 |
| Online | 25 |
Summary
Although QuickBooks® works quite well for companies of various sizes and across many industries, there are a few situations in which QuickBooks® may not be the best solution.
If you are interested in finding out more about any of the QuickBooks® line of products or if you would like to discuss what solution is best for your small business, please call us.
This article of QuickBooks Tips and Tricks was based on the 2012 version of QuickBooks.
If your company has a policy that allows you to give your employees advances on upcoming payroll, you can use QuickBooks® and QuickBooks® Payroll to record the advance, which can then be reimbursed to the company through payroll deductions. We recommend that you document any loans to employees with terms for repayment clearly spelled out and signed by both parties.
Note: This QuickBooks® tip assumes that you have an active QuickBooks® payroll subscription.
First, create an Asset Account to Track the Employee Loan and Repayment
This is a one-time setup task
- Go to the Lists menu and click Chart of Accounts
- Click Account at the bottom of the list and then click New
- Under Other Account Types choose Other Current Asset and then click Continue
- If you are using account numbers, enter the number in the Numbers field
- In the Account Name field, enter Employee Advances
- Click Save & Close
Next, you can either write a regular check (non-payroll) to the Employee for the Advance and post it to the Other Current Asset account Employee Advances or you can pay the Advance through Payroll in which case you will need to create a Payroll Item of the type “addition”.
This is a one-time setup task.
- Go to the Lists menu and click Payroll Item List
- Click Payroll Item at the bottom of the list and then click New
- Select the Custom Setup method and click Next
- Choose Addition and click Next
- Enter the name for this addition as Employee Loan
- Click Next
- On the Expense Account screen, choose the Other Current Asset account, Employee Advances, that you created in Step 1 for tracking this expense and click Next
- Select None as the tax tracking type
- Leave the taxes unchecked and click Next
- Select Neither in the calculate based on quantity screen and click Next
- Select Net Pay and click Next
- Leave the default rate and limit blank and click Finish
Use this addition on the Employee’s paycheck under Other Payroll Items. Enter this amount as a positive number.
Then, create another Payroll Item of the type “deduction”Again, this is a one-time setup task.
- Go to the Lists menu and click Payroll Item List
- Click Payroll Item at the bottom of the list and then click New
- Select the Custom Setup method and click Next
- Choose Deduction and click Next
- Enter the name for this deduction as Loan Repayment and click Next
- Leave the first two fields blank, then under the Liability account field, choose the Other Current Asset account, Employee Advances, that you created in Step 1 for tracking this expense and click Next
- Select None as the tax tracking type and click Next
- Leave the taxes unchecked and click Next
- Select Neither in the calculate based on quantity screen and click Next
- Select Net Pay and click Next
- Enter a default rate and limit if desired and then click Finish
Note: You should only enter a default rate and limit if the rate and limit will apply to everyone using the Payroll Item. The limit may be marked as an annual limit and if so, you will need to manually adjust or remove the amount at the beginning of the calendar year or it will start recalculating.
Use this deduction on the Employee’s paycheck under Other Payroll Items. Enter this amount as a negative number.
Create a Custom Summary Payroll Report to Track Employee Loan Balances
- Go to Reports
- Select Employees & Payroll
- Select Payroll Summary
- Click on Customize Report in the upper left corner
- On the Display tab, choose All Dates
- Select Payee in the Display Columns by drop-down menu
- Check % of Row and uncheck Hours and Rate
- Click on the Filters tab, select Accounts and choose the Other Current Asset account used for the Employee Advances on the drop-down menu
- Click OK
Manage Employee Loans that will be Repaid over Time on Multiple Paychecks
- Edit the Employee
- Change tabs to Payroll and Compensation Info
- Under Additions, Deductions and Company Contributions select the Loan Repayment Payroll Item
- Enter the amount to be paid by paycheck in the Amount column as a negative amount.
- Enter the balance remaining to be paid at this point into the Limit column as a negative amount.
- Click OK
Note: QuickBooks® will automatically deduct this repayment on each paycheck and will stop the deduction once the limit has been reached.
This article of QuickBooks Tips and Tricks was based on the 2012 version of QuickBooks.
When you run financial statements on a Cash Basis in QuickBooks®, the results may not be what you expect. For example, you may find balances for Accounts Receivable and/or Accounts Payable on Balance Sheets run on a Cash Basis. This often means that the client has assigned a Payable or a Receivable to a Balance Sheet account, rather than to an Expense or Income account. There are other reasons this happens too.
Reasons for Accounts Receivable on a Cash Basis Balance Sheet
- There may be open balances on Invoices that use Items linked to Balance Sheet accounts. An example would be a Customer Deposit linked to a Liability account.
- There may be a prorata cost of Inventory Items listed on open Invoices. For example, if an Invoice that includes Inventory Part Items is half paid, half the cost of the Inventory Part Items will remain in Accounts Receivable.
- There may be unapplied credits from Credit Memos or Payments. You can find these entries easily because they appear as negative numbers on the Open Invoices report.
- There may be Sales Tax due listed on an accrual basis. You can change the Sales Tax Preference to Cash Basis to eliminate this problem. CAUTION: Check with your State taxing agencies for rules regarding payment of Sales Tax – some States require the basis for Sales Tax accruals to match the basis for Income Tax filing.
Reasons for Accounts Payable on a Cash Basis Balance Sheet
- There may be Bills using Items linked to Balance Sheet accounts.
- There may be Bills entered for a Note Payable or to buy a Fixed Asset.
- You may find the cost of Inventory Part Items on open Bills.
- There may be unapplied Vendor Credits or Prepayments.
To review balances in Accounts Receivable and/or Accounts Payable:
Filter a transaction report with a paid status of Open transactions and date range of All to get a report showing the transactions that QuickBooks® did not reverse as part of the internal Cash Basis conversion.
- From the Reports menu, choose Company & Financial
- Choose Balance Sheet Standard from the submenu
- Click Customize Report
- Select Cash as the Report Basis
- Click OK
- Double-click the balance in the Accounts Receivable and/or Accounts Payable account
- Click Customize Report, and then click the Filters tab
- In the Filters list, select Paid Status and then select Open
- Click OK
Complete the Cash Basis Conversion
To complete the Cash Basis conversion, use a Journal Entry to adjust away the Accounts Receivable and/or Accounts Payable balances. For the Journal Entry, create a Customer called ***A/R CPA Use Only*** and an Accounts Payable Vendor called ***A/P CPA Use Only***. Use these names to transfer the balances to whatever accounts you choose for the adjustments.
These Journal Entries are Reversing Entries as of the first day in the next fiscal period and you must apply the Journal Entry and the Reversing Entry against each other to offset them or you will have Unapplied Credits going forward.
Do not use Accounts Receivable and/or Accounts Payable as the first line of a journal entry in QuickBooks®. To find out why, see our next Accounting Professionals Only newsletter.
Please contact me if you have any questions or need further details regarding this information. This article was based on the 2012 version of QuickBooks®.
Rhonda Rosand, CPA
Certified QuickBooks® ProAdvisor
New Business Directions
Please note that you should rarely need to void a payroll check. Remember that voiding a payroll check is not like voiding other kinds of checks; paychecks have associated tax and other liability calculations and payments that need to be taken into consideration. You are required to amend and file corrected payroll tax returns if you void a paycheck from a previous tax period.
To correctly replace a lost or destroyed paycheck and ensure that your payroll data remains accurate, you need to print a replacement check with a new check number and make a record of the lost or destroyed check.
Find the Paycheck in QuickBooks®
First, open the check register and find the paycheck that was lost or destroyed. In the Banking section on the Home page, click the Check Register icon. If you have more than one bank account, select the appropriate account and click the OK button. This brings up the Check Register where you can search for the lost or destroyed paycheck. You could also search for this check in the Employee Center.
Once you find the original paycheck, make a note of the check number and the net pay amount. You will need these later.
Double-click the entry in the register to open the Paycheck window. Select the To be printed checkbox and click the Print button at the top of the window to reprint the check using the next check number in your numbering sequence. Click Save & Close in the Paycheck window and close the check register.
This is the replacement paycheck that you will give the employee.
The next steps create a record of the lost or destroyed paycheck.
In the Banking section on the Home page, click the Write Checks icon.
-
- Use the same check number as the original lost or destroyed check.
- Make the date the same as the day the check was originally written.
- Make the check payable to the employee (ignore the message about using the built-in payroll features.)
- Make the check amount the net payment amount from the original check.
- Note in the memo line that this check was replaced by the check you reprinted.
- On the Expenses tab, choose Payroll Expenses in the Account column.
Click Save & Close.
Open the check register again. Find the check you just created, right-click it, and click Void Check.
You should now see the word “VOID” in the Memo field and a check amount of “0.00.” You now have a record in your check register of the lost or destroyed check.
*Please note that clients using the Assisted Payroll service and/or QuickBooks® Payroll with Direct Deposit may need to perform additional steps when reissuing paychecks to Employees.
This article of QuickBooks Tips and Tricks was based on the 2012 version of QuickBooks.
New in 2012 – QuickBooks® Accountant and Enterprise Accountant versions only
Starting with the 2012 version of QuickBooks® Accountant and QuickBooks® Enterprise Accountant, accounting professionals can now set a date range to extract transactions for a specified period of time and condense transactions outside the date range.
This preserves transactions in the condensed file only for a particular period with entries prior to that period summarized and entries after that period removed.
This is useful for clients with file size and list limit issues as well as for third-party requests of information – not only for audit requests, but also in the event of a business sale, a divorce or legal dispute when you do not need to provide the entire QuickBooks® file.
First thing – BACKUP your data file.
You are creating a new file to be issued to a third-party and you do not want to overwrite your working file.
Then under the FILE menu, UTILITIES, CONDENSE DATA – Accept the prompt that it is ok to lose Budget data.
When it prompts you for what transactions you want to remove, select “Transactions outside of a date range” to prepare a period copy of the company file. Set your before and after dates to include the period you want to keep.
Then when it prompts you how transactions should be summarized, choose “Create one summary journal entry”.
Remove the recommended transactions.
Remove the unused list entries.
Begin Condense.
This article of QuickBooks Tips and Tricks was based on the 2012 version of QuickBooks.
If you are using the Accrual method of accounting and you extend credit to your Customers, as soon as you create an Invoice it is recorded in QuickBooks® as an Accounts Receivable.
Unfortunately, you will not always collect all credit you have extended. From time to time you will have Bad Debt – money you billed to a Customer that will never be collected.
Generally, you should write off bad debt only after exhaustive efforts to collect. For any significant amounts, you should call the customer until you are convinced that the customer cannot or will not pay.
At the end of the year, you will want to write off the outstanding customer invoices you know are not going to be paid.
First, check to see if this account and item have already been set up in your QuickBooks® file. Go to Lists, Chart of Accounts and look for an account named Bad Debt Expense. Go to your Item List and look for an Other Charge Item called Bad Debt or Write Off. If these are already on your lists, you can skip both Step 1 and Step 2.
Step 1: Create an Account for tracking Bad Debt Expense
This is a one-time setup task.
- Go to the Lists menu and click Chart of Accounts
- Click Account at the bottom of the list and then click New
- Under Account Type choose Expense and then click Continue
- If you are using account numbers, enter the number in the Numbers field
- In the Account Name field, enter “Bad Debt Expense”
- Click Save & Close
Step 2: Create an Item that points to your Bad Debt Expense account
This is also a one-time setup task.
- Go to the Lists menu and click Item List
- Click Items at the bottom of the list and then click New
- Under Type choose Other Charge and name it “Bad Debt”
- In the Amount or % field, leave a zero
- From the Tax Code list, choose Non (if you use Tax Codes)
- In the Account field, select the account you created in Step 1 – Bad Debt Expense
- Click OK
Step 3: Use the Item you created in Step 2 to generate a Credit Memo*
- Click Customer Center
- On the Customers & Jobs tab, select the customer whose account you will not be able to collect
- Right-click and choose Create Credit Memos/Refunds
- Use the Bad Debt item for the amount of the unpaid invoice (s) that you are writing off
- Click Save & Close
*Creating a Credit Memo is the preferred method to write off Bad Debts, especially if the original sale included Sales Tax. We have not included the specific procedures for writing off a bad debt with Sales Tax. Please call for additional guidance if you are presented with this issue.
Step 4: Apply the Credit
In the available credit window that appears when you Save & Close the Credit Memo, it asks “what would you like to do with this credit?”
- Click Apply to an Invoice
- Click OK
- Select the Invoices that you are writing off
- Click Done
Every bad debt has a story and you need to talk to customers who refuse to pay their invoice. Discussing the reason for nonpayment may provide you the information necessary to prevent the problem from occurring again. Be proactive and contact the customer as soon as the invoice is late.
This article of QuickBooks Tips and Tricks was based on the 2012 version of QuickBooks.
Non-sufficient funds (NSF) is a term used in the banking industry to indicate that a demand for payment (a check) cannot be honored because insufficient funds are available in the account on which the instrument was drawn. An NSF check is often referred to as a bad check or a bounced check.
When a customer’s check is returned for insufficient funds, perform the following steps in QuickBooks®
First, check to see if these items have already been set up in your QuickBooks file. Go to Lists, Item List and look for Other Charge Items called Bounced Check and Bounced Check Fee. If they are already on your Item List, you can skip step 1.
Step 1: Create items for tracking bounced checks and their associated charges: This is a one-time setup task.
- Go to the Lists menu and click Item List
- Click Items at the bottom of the list and then click New
- Under Type choose Other Charge and name it “Bounced Check”
- In the Amount or % field, leave a zero
- From the Tax Code list, choose Non (if you use Tax Codes)
- In the Account field, select your bank account (for example, Checking)
- Click Next.
- Create a second Other Charge item titled “Bounced Check Fee” for the service charge you assess your customers for bouncing checks
- In the Amount or % field, leave a zero
- From the Tax Code list, choose Non (if you use Tax Codes)
- In the Account field, choose Bank Service Charges
- Click OK
Step 2: Use the items you created in Step One to re-invoice the customer for the bounced check, plus any bank fees you want to recover.
- Click Customer Center
- On the Customers & Jobs tab, select the customer with the bounced check
- Right-click and choose Create Invoices
- For the first line item on the invoice, use the Bounced Check item for the amount of the bad check
*Note: Because this item is linked to your bank account, this will reduce your bank account by the amount of the bounced check.
- For the second line item on the invoice, use the Bounced Check Fee item for the amount of any bank fees that you want to recover from the customer
- Complete filling out the invoice as usual
Step 3: QuickBooks® has a Bounced Check Letter that you can send along with the new invoice
- Select the customer in the Customer Center
- Go to the Word menu at the top of the Customer Center and click Prepare Letter to Customer
- Follow the onscreen instructions in the Letters and Envelopes wizard
- In the Choose a Letter Template window, choose Bounced Check
Step 4: Enter your bank’s bounced check charge when you reconcile your bank statement
When you reconcile your bank statement:
- Include your bank’s charge for the bounced check in the Service Charge field
- In the Service Charge Account field, enter Bank Service Charges
This article of QuickBooks Tips and Tricks was based on the 2012 version of QuickBooks.
Each January we rush around to gather tax information on the independent contractors we paid in the prior year in order to send them a 1099. Here are some basic tips for making it easier and less hurried for next year.
Form W-9
As a trade or business, you are required to obtain a Form W-9 from your independent contractors before you pay them, better yet – before they do any work for you. This is regardless of how much money you pay them. This form will give you the name, business name, address, entity type and taxpayer identification number.
Who Needs a 1099?
Service Providers – Anyone you pay in the course of your trade or business for services rendered. This includes Accountants, Consultants, Architects, Engineers, Designers, Contractors, Plumbers, Electricians, Installers, Landscapers, Snow Removal companies, Cleaning companies, the Auto Repair technician, Entertainers – anyone who performs a service or casual labor for your business who is not your employee.
Rents – If your business pays rent for office space or land or equipment, you are required to send the recipient a 1099 for the amount of rent you have paid them.Exclusions – Sole proprietorships, partnerships and LLC’s that are taxed as sole proprietors and/or partnerships receive a 1099. You are not required to send 1099’s to a Corporation or a Tax Exempt entity. The Form W-9 will provide information regarding the entity type.
Threshold – Send to recipients to whom you have paid $600 or more during the calendar year.
Deadline
January 31st of the following year to recipients. February 28th to the Internal Revenue Service.
This article of QuickBooks Tips and Tricks was based on the 2012 version of QuickBooks.
