A QuickBooks tip for entering Job-to-data costs and billing data at the item level when converting or archiving.
Use this procedure to enter Job-to-date (“JTD”) costs and JTD billing so the Job Profitability Detail Report is accurate at the Item level after converting your existing Quickbooks file to a new database file, or after archiving old QuickBooks transactions. In both cases, you are bringing over beginning balances as of the conversion date into your new data file, and this procedure will prove useful for reporting on jobs that are in progress as of the conversion date.
From your old data file:
Run your Job Profitability Detail Reports for each job in progress to obtain the JTD cost and revenue figures as of the conversion date. In the date fields, leave the first field blank, enter the conversion date in the second date field. You will use the figures on this report for your data entry in the new data file.
In the new data file:
- Create a fake Vendor called “Opening Balance”. You will make this inactive after completing this procedure.
- Create an Other Expense account in the Chart of Accounts called “9999-Conversions-JTD Costs”. You will make this inactive after completing this procedure.
- Create an Other Income account in the Chart of Accounts called “9998 · Conversion – JTD billing”. You will make this inactive after completing this procedure.
- Create a Service Item called “Opening balance JTD Costs” and link it to “9999 · Conversion – JTD Costs”. You will make this inactive after completing this procedure.
- Print your Item List. (Reports>List>Item Listing) Modify the report to display columns for Account and COGS Account; remove price, cost, tax code, etc.
- Look at your Item list and determine which Service Items appear on your Job Profitability Detail Reports. These are the items to edit in step 9. These are active items.
- In the Item List window, make all other Service Items temporarily inactive. Making them inactive will keep them from appearing in the Add/Edit Multiple Items screen allowing you to quickly copy down the account edits you will perform in step 9.
- Create a backup before performing step 9!
- Using the Add/Edit Multiple Items screen (available in QB 2010 and later) ,edit “all active service items” and point the COGS/Expense account to account 9999 and point the Account/Income account to 9998. Change the top item, and quickly copy down the account edits to the other items. Click Save. Do not update existing transactions. You will have to answer No repeatedly by pressing N until it scrolls through all your changes.
- When you are done with the following procedures, you will edit these same Items again, and change the COGS/Expense and Account/Income accounts back to what they were. Do not update existing transactions. You will have to answer No repeatedly by pressing N until it scrolls through all your changes.
To enter the JTD Costs for each item on each job:
Enter a zero dollar Bill to the “Opening Balance” vendor for the JTD cost. On the item tab enter each Item and the costs on the job profitability detail report and the appropriate Customer/Job. Keep the Billable box checked. On the last line, enter a negative number to the “Opening balance JTD Costs” Item with no customer:job.
This results in no AP balance, leaves no effect on GL, but leaves the correct JTD cost by Item in your Job Cost reports.
Create a new zero-dollar Bill for each active job as of the conversion date.
To enter the JTD Billing for each job:
Enter an Invoice for the Customer:Job for the total Billing-to-date on the job. The Add Time/Costs box will pop up, select the Item tab and bring over all Items to the invoice. You now only have to edit the dollar amounts to agree with your JTD Billing on your Job Profitability Detail report.
Enter a Journal Entry for the total amount of the JTD Billing on the job. The debit is to the “9998 · Conversion – JTD billing” account, leave the Name field blank. The credit is to Accounts Receivable. Select the Customer/Job name in the Name field.
In Receive Payments, enter the Customer/Job, and you will find the above Journal Entry is available as a credit against the Invoice you created above. Apply the Journal Entry to the Invoice.
The net effect on GL is zero, the invoice is paid, and the correct Job-To-Date Billing as of the conversion date will be reflected in your Job Cost reports.
Create a new Invoice, Journal Entry and Receive Payment entry for each active job as of the conversion date.
Run your Job Profitability Detail Reports on the new data file and compare with the same report on the old data file. They should agree. If not, you’ll need to double check your data entry.
I hope you find this QuickBooks tip to be helpful. If so, please leave a comment or share it on your favorite social media platform by using the buttons below.
This QuickBooks Tip provides instructions for how to rebuild your QuickBooks data file. The rebuild function will help maintain the overall health of your file.
Is your QuickBooks file very large? Is it running slow? Are you in a multi-user environment where 2 or more people are inputting a lot of information on a daily basis? If you answered yes to any of these questions you would probably benefit from rebuilding your data file on a regular basis.
The QuickBooks Rebuild Data function is like a cleaning lady. When you access the rebuild function it looks at all of the information in your file and makes sure that it has been filed correctly. Think of your desk, covered in piles of papers that need to be filed. Before you actually put all of the papers in the filing cabinet, you sort and separate the various piles into organized files. The rebuild function performs the same task.
How to Rebuild Your Data File:
You’ll need to be logged into QuickBooks as the Administrator. If you have a large file, this could take quite awhile – so plan to run this process at lunch time, or gear it up just before you leave for the day.
- From the File menu
- Choose Utilities
- Rebuild Data
The first thing that the Rebuild will require you to do is to make a backup of your data file, go ahead and do that.
As soon as the backup has completed, the rebuild function will automatically start. This tool will take two passes through your file, looking for problems, organizing your data, and trying to fix any problems with your data that it finds. If you sit an watch the process, don’t be concerned if QuickBooks seems to stop responding or stop working at 99% on the first pass and 50% on the second. This just means that it’s found things that are wrong, is attempting to fix them, and then going back to check that everything it found on the first pass has been fixed.
There are times when the Rebuild function will fail, this usually means that there is data damage within your file that the rebuild cannot fix (it does have limited abilities). In a situation like this, you’ll need additional assistance to fix the issues. If you run into this situation, I would recommend that you investigate the services of Accounting Users, Inc., QB or Not QB, or The Bottom Line.
Our own QuickBooks file contains 11 years of data and I rebuild it on a monthly basis and have for many years.
If you found this article helpful, please leave a comment or share it with others.
Yes, it’s true. The IRS can *LEGALLY request a backup of your QuickBooks (or Peachtree) company data file if you are being audited.
BACKGROUND:
In 2010, the IRS purchased somewhere between 1,500 and 2,000 licenses for the QuickBooks 2010 Premier Accountant Edition from an ISP (Inuit Solutions Provider) and not directly from Intuit as it is made to look in announcements (SEE NOTE BELOW).
Approximately 1,100 agents were trained to utilize QuickBooks and these agents have been instructed to obtain a copy of the taxpayer’s data base for the year under examination ONLY when it is necessary. This method of examination of taxpayer records will not be used in all cases — however, it will be up to the examiner.
When requested, the electronic files should be provided on a CD, DVD, or flash/jump drives to ensure the security of the files. Email should NEVER be used to submit files.
Apparently business owners and tax professionals have been advocating that the IRS begin accepting taxpayer records in electronic format instead of continuing to use more traditional paper books and records for audit purposes. This is according to the IRS Small Business/Self-Employed Examination Division; who is responding to the wishes expressed in tax practitioner focus group interviews conducted at the 2008 Nationwide Tax Forums as well as other stakeholders.
Why on earth would anyone want the IRS to begin accepting taxpayer records in electronic format? Well, in reality it does provide advantages, such as:
- Reducing the taxpayers burden because they don’t have to print records which are stored within their accounting files.
- Provides a complete set of records, which decreases the number of items included in the original request and/or follow up requests for additional documentation.
- Results in faster audits and therefore provides faster resolutions.
Look for tomorrows article on what to do if the IRS requests your QuickBooks data file.
NOTE: Thanks to Alison Ball from Intuit for taking the time to contact me and let me know that the licenses were not purchased directly from Intuit. According to Alison, Intuit is actually prohibited from selling to the government, probably because it would be a conflict of interest because Intuit relies on the IRS to provide new tax code each year.
——————————————————–
*RESOURCES FOR ACCOUNTING PROFESSIONALS AND OTHER INTERESTED PARTIES:
The legal authority for requesting a taxpayer’s QuickBooks or Peachtree backup files and accounting records is based on:
- IRC Section 6001 - http://www.law.cornell.edu/uscode/html/uscode26/usc_sec_26_00006001—-000-.html
- Regulation 1.6001-1(a) and -1(e) – http://frwebgate.access.gpo.gov/cgi-bin/get-cfr.cgi?TITLE=26&PART=1&SECTION=6001-1&TYPE=PDF
- Revenue Ruling 71-20 http://www.uiowa.edu/~fusrmp/irsruling71-20.html
- Revenue Procedure 98-25 – http://www.irs.gov/businesses/article/0,,id=180708,00.html
It is also important to note, that Revenue Procedure 98-25 does not prevent or exempt a taxpayer from providing electronic records, if those records exist. If a taxpayer or authorized representative refuses to provide the database and the agent determines it is necessary for the audit, a Summons to obtain the information could be issues.
Also see, http://www.ebaea.org/cgi-bin/dada/mail.cgi/archive/Bulletin/20100623212855/ and http://www.irs.gov/businesses/small/article/0,,id=229050,00.html
Additional online discussions regarding this topic can be found at:
Tax Almanac – http://www.taxalmanac.org/index.php/Discussion:IRS_Auditors_to_be_trained_in_Quickbooks
Successful QuickBooks Consultants on LinkedIn – http://www.linkedin.com/groupItem?view=&gid=157449&type=member&item=23839279&qid=64ddee41-3223-43aa-a3b6-fc63f104925a&goback=.gmp_157449
Everybody says it, but most people don’t really understand it until they need to restore some data they don’t have saved. Only then does it sink in for most people. Not that I’ve never lost anything, but when your job in college was rotating 9-track tapes to offsite locations for 20 hours a week for a bank, backup has been ingrained to my genes for longer than I can think. One of the reasons DEC was my favorite operating system is that every time you saved your work, a copy was made, so unless you purged your old files, you could see the state of your work as it progressed and recover to any point in time you wanted. Still to this day Windows hasn’t completely duplicated this built in functionality.
But this does lead to what I feel are the 3 types of backups that everyone should have.
First the total system failure scenario: If a virus came in and deleted everything on your hard drive, or the drive fails, can you recover without spending days or weeks rebuilding your PC with your installed software, preferences and files? This is preparing for the absolute worst. Everyone should have a backup of this type.
The next type of back is backups for applications. If something major ugly happen in say your accounting software, wouldn’t it be nice to restore just it to the end of the prior day and just redo the current day, instead of correcting the current mess, or say entering a month of data. That is why we designed our backups to let you backup every time you exit the program and then purge extra backups from prior days as well as today
Lastly a backup of all your files should be made often. A backup of what you keep in your My Documents folder. So if you accidentally delete a file that you need, it can easily be restored. This backup should also include your application backups described above.
It is quite important to have backups in more than one place. Fires or theft can clear out an office, so keeping a backup in a safe deposit box or an off-site server is very important.
And once you have you plan, it is best to run a quick test to see that you can restore a file should you really need to.
It seems like a lot of time and investment, but if something goes wrong it will be worth it and you won’t even need to panic.
How much data can your QuickBooks company file hold before it becomes “stuffed”, overflowing like a file cabinet, and SLOW? This is a very good question that most people don’t take into consideration — well because it’s software and should hold an unlimited amount of data.
QuickBooks Pro and Premier are designed for small businesses with 20 or fewer employees and annual revenues of less than $1 million per year, according to the latest information from the ProAdvisor certification study materials. It is intended to store at least 2 years of detailed information in a company file – this allows you to compare your current business years performance with that of a previous year.
Quickbooks Enterprise is designed for larger businesses having 20-250 employees, needing up to 30 simultaneous users, stricter user access to financial information with 115 different permission settings, want to connect multiple locations, perhaps have remote workers, need to combine financial statements from multiple company files, and have more than $1 million in annual revenue.
How fast a QuickBooks file grows varies significantly from one company to another. there is no “average” or “typical” company file size, since every business tracks different information.
How quickly a data file grows to “overflowing” will depend on the number of transactions that are entered, the amount of information entered per transaction, and the number of “links” per transaction.
For example, a company that enters 500 1-line invoices per month might find that their data file is smaller than another company that enters 100 5-line invoices per month. Another example would be a company who usually receives 5 separate payments per invoice would have a larger data file than a company who receives one payment per invoice.
A good way to estimate the growth of your QuickBooks company file is to take the average number of monthly transactions (keeping in mind that an invoice, a payment and a deposit represent 3 separate transactions; while a bill and a bill payment represent 2 transactions), and multiply that by 2 KB to determine approximately how much your file will grow each month. You can then take the monthly amount and multiply it by 12 to determine an estimated yearly data file size.
According to Intuit, QuickBooks can handle a maximum or 2 billion transactions, however, they do not state which version, (Pro, Premier, or Enterprise) this figure applies to.
In addition to estimated annual file growth and a maximum number of transaction that QuickBooks can handle, business owners must also take into consideration the maximum number of List items that QuickBooks can can accommodate.
The table below indicates the maximum number of items that can be held in individual and combines lists.
| List Name | Maximum Number of Items (Simple Start, Pro, Premier) |
Maximum Number of Items (Enterprise) |
| Chart of Accounts | 10,000 | 10,000 |
| Items, including Inventory Items Group Items can contain 20 individual items |
14,500 | >100,000* (29,000 in version 6.0 & earlier) |
| Job Types | 10,000 | 10,000 |
| Vendor Types | 10,000 | 10,000 |
| Customer Types | 10,000 | 10,000 |
| Payroll Items | 10,000 | 10,000 |
| Payroll Items per Employee Record | 25 | 100 |
| Price Levels | 100 | 100 |
| Classes | 10,000 | 10,000 |
| A/R & A/P Terms – TOTAL | 10,000 | 29,000 |
| Payment Methods | 10,000 | 10,000 |
| Shipping Methods | 10,000 | 10,000 |
| Customer Messages | 10,000 | 10,000 |
| Memorized Reports | 14,500 | 29,000 |
| Memorized Transactions | 14,500 | 29,000 |
| To Do Notes | 10,000 | 10,000 |
| Total Names – Employees, Customers, Vendors, Unit of Measures and Other Names – COMBINED |
14,500 | 100,000* (29,000 in version 6.0 and earlier) |
| Sales Reps | 14,500 | 29,000 |
| Sales Tax Codes | 10,000 | 10,000 |
| Billing Rate Levels | 100 | 100 |
| Fixed Asset Items | 10,000 | 10,000 |
| Ship Via | 10,000 | 10,000 |
| Templates | 10,000 | 10,000 |
| Units of Measure | 10,000 | 10,000 |
| Ship To Addresses | Unlimited (?) | Unlimited (?) |
Additional resources from around the web:
- Danger Danger – Your QuickBooks File May Implode – by Scott Gregory
- How to Repair QuickBooks Files – by Shannon Tucker
