Accounting Setup and Usage - QuickBooks

Accounting Setup and Usage - QuickBooks

Realtrac 10 features integration with the Intuit QuickBooks Pro software accounting package. Businesses leveraging this integration will be able to export Invoices, Credit Memos and Purchase Orders from Realtrac 10 to the QuickBooks Pro system.

// Configuration and Setup

There are two steps to setting up the configuration between Realtrac and QuickBooks. First, we must configure Realtrac to let it know which QuickBooks accounts we went our Invoices, Credit Memos and Purchase Orders to be saved in. After we make these configurations, the first time we export data from Realtrac, QuickBooks will prompt us to confirm that Realtrac is allowed to export data. We’ll cover these two steps now.

Configure QuickBooks Accounts in Realtrac

Realtrac provides a program, called Realtrac Accounting Setup that is required to set up the connection to QuickBooks. This program should be installed in your Start Menu under the Realtrac > Utilities folder. This program only needs to be run once, even if different users or workstations will be exporting data to QuickBooks.

Before you can configure the Realtrac accounting interface, your PC must already have a working copy of the Realtrac 10 Client. If you can launch Realtrac and connect to your system, you are ready to configure the accounting interface. If you do not yet have a working Realtrac 10 Client, visit the section on configuring your Realtrac 10 client before continuing.

To launch the Realtrac Accounting Setup utility, navigate to the Realtrac folder in your Start Menu, then locate the program under the 'Utilities' folder.

Fig 1 - Accessing Realtrac Accounting Setup

The Realtrac Accounting Setup utility is an application that features 3 tabs. Each tab represents an accounting package (or in the case of the “None” tab, the lack of accounting integration). Since we are integrating with the QuickBooks accounting program, we should click on the QuickBooks tab. Within this tab, we see the 3 fields we have to configure.

Fig 2 - QuickBooks Configuration fields

We must configure the 4 accounts to sync Realtrac with QuickBooks.

With your accounts set up, you are now able to click the Save button on the interface, and exit the program. The initial accounting setup is not complete, and Realtrac is now ready to communicate with QuickBooks. This configuration only needs to be done once per Realtrac installation. Any Realtrac user with QuickBooks installed on their PC will be able to use the exporting capabilities in Realtrac.

Confirm Realtrac Communication with QuickBooks

QuickBooks is understandably skeptical when an external program tries to push data in to QuickBooks. It has a system of checks to make sure you, the user, know that an external program (such as Realtrac) is making changes to your accounting system.

The first time you export data from Realtrac, QuickBooks will show a prompt similar to the following:

Fig 3 - QuickBooks confirmation screen

This screen tells us an external application (in this case, “Realtrac 10”) is trying to modify the 'Test Company' QuickBooks company file. To confirm access, it is recommended to select “Yes, whenever this QuickBooks company file is open”. Realtrac does not need access to sensitive information such as Social Security number and credit card details, so leave that check box unselected. Click the Continue button.

After we continue, we will see another prompt confirming that we wish to let Realtrac 10 access the company’s data file.

Fig 4 - Confirm Realtrac access to QuickBooks

Click the Yes button to confirm that Realtrac can access QuickBooks. QuickBooks will show another screen confirming that the changes have been made. At this point, the transaction will continue, and Realtrac is authorized to communicate with QuickBooks.

//Exporting

First, it’s important to note that QuickBooks must be running on your computer when you attempt to synchronize data between Realtrac and QuickBooks. So, first and foremost, launch your copy of QuickBooks and open your company. Realtrac is now ready to communicate with QuickBooks.

If you attempt to Post data from Realtrac to QuickBooks without QuickBooks running on your PC, you will receive an error in the client.

The very first item transferred from Realtrac to QuickBooks will result in QuickBooks asking for confirmation (Fig 3-4).

Invoices

For a general introduction to the Realtrac Invoice system, visit the Invoice Group section of the manual. This portion of the manual will discuss only the exporting of invoices from Realtrac to QuickBooks. Invoices can be accessed either from the Invoice Group or via the Invoice tab in the Job Group. Regardless of path used to view or edit an invoice, the actual invoice entry screen looks and acts the same.

When the Export button is pressed, the invoice is automatically exported to QuickBooks. In the process of exporting the invoice, the Realtrac software communicates several pieces of information to QuickBooks:

Customer – Realtrac uses the Customer code (set in the Customer / Vendor setup screen in the System group) to see if the customer already exists in QuickBooks. If the customer does not exist, it is automatically added.
Terms – Realtrac checks to see if the terms of the invoice are present on the Terms list in QuickBooks. If it is not, Realtrac automatically adds them.
Sales Person – Realtrac checks to see if the sales person is already defined as an employee in QuickBooks. If the employee does not exist, Realtrac automatically adds the sales person to QuickBooks Employee List.
Shipping Method – Realtrac checks to see if the shipping method listed in the invoice is present in Ship Via List in QuickBooks. If the shipping method is not present, Realtrac automatically adds it.
Invoice Items – Realtrac checks to see if the item being invoiced is present in the Item List in QuickBooks. If the item is not present, Realtrac automatically adds the item.

Once these steps have been completed, the Realtrac client creates the invoice in QuickBooks and transfers the information over on the sale (quantity purchased and price each). QuickBooks will reflect the same invoice number that Realtrac uses for the item order.

Sales Rep Integration

Both Realtrac and QuickBooks allow a sales rep to be setup on a per invoice basis. When posting an invoice from Realtrac over to QuickBooks, Realtrac will attempt to set the sales rep on the resulting QuickBooks invoice. Typically, this process happens automatically and without having to configure anything, but the process can be customized and in some circumstances may require configuration. First, let’s take a look at how sales reps are configured in QuickBooks.

The most important thing to note about sales reps in QuickBooks is that it is driven by the initials, and not the actual full name of the employee. So we see in Figure , our sales reps Bob Sakuta and Matt Fuerst have initials that correspond directly with their Name, but our sales rep Josh Borkowski is set up in QuickBooks corresponding with the initials “JHB” (not simply “JB”). Let’s take a look at what happens in the perfect world, with a Realtrac Invoice that is associated with our Matt Fuerst sales rep.

When Realtrac attempts to post this Invoice to QuickBooks, it will notice that there is a Sales Rep assigned to it. Realtrac will then attempt to automatically figure out what the initials for this Sales Rep should be (since QuickBooks wants us to tell it the initials of the Sales Rep, not their full name). Using it’s incredible powers of deduction, Realtrac will conclude that the initials for Matt Fuerst are most likely “MF”, and will in turn tell QuickBooks about the new invoice, and tell it to use the “MF” Sales Rep. Since we established in Figure that we do actually have a Sales Rep in QuickBooks with the initials MF, everything works swimmingly. After posting, our QuickBooks invoice looks as follows:

But when we go to post a Realtrac invoice for our Josh Borkowski sales rep, the process will not go as smoothly.

When Realtrac goes to post this Invoice to QuickBooks, it will once again attempt to deduce Mr. Borkowski’s initials. Realtrac will determine Josh Borkowski’s initials are “JB”, and attempt to post the invoice accordingly. The problem is, Josh Borkowski is set up as a sales rep in QuickBooks, but with the initials “JHB”, not “JB” (as shown in Figure 8). We have ourselves a classic catch 22. Realtrac will initially ask QuickBooks if it has a sales rep with the initials “JB”. QuickBooks will tell Realtrac it does not, and Realtrac will attempt to create a Sales Rep in QuickBooks for the user. However, when we go to create the QuickBooks Sales Rep, QuickBooks will not allow that to happen, since it actually does already have a Sales Rep named Josh Borkowski (just with a different set of initials than Realtrac expects).

In cases like this, users are able to explicitly set the initials of Realtrac Employees / Sales Reps. If we travel in to the Employee Group (available under the main Toolbox menu in the Realtrac toolbar – note that users will need permissions to view/edit the employee setup) we can see each employee has an “Accounting Code” field.

With the Accounting Code set for this employee, Realtrac will no longer try to automatically guess at what the QuickBooks initials are, instead, we will use the initials the user has defined. As soon as the Accounting Code field is set in the Realtrac Employee Group, then Invoices associated with this user will post successfully!

Purchase Orders

For a general introduction to the Realtrac Purchase Order system, visit the Purchasing Group section of the manual. This portion of the manual will discuss only the exporting of purchase order records from Realtrac to QuickBooks.

Realtrac users are able to access purchase orders either from the Purchase Order group or the via the Purchase Order tab in the Job Group. Regardless the path used to view or edit a purchase order, the actual purchase order screen looks and acts the same.

In fig above, you’ll note the enabled Post button in the lower left hand corner of the screen. When this button is enabled, the purchase order is eligible to be exported from Realtrac to QuickBooks. When the Post button is pressed, the purchase order is automatically exported to QuickBooks. In the process of exporting the purchase order, the Realtrac software communicates several pieces of information to QuickBooks:

Realtrac checks to see if the Vendor exists in QuickBooks. If the Vendor has a Tax Code defined in the Realtrac Customer / Vendor setup screen, then that value is used. If the Vendor has no Tax Code, then the main Vendor code is used. A vendor must have a Vendor code in order to integrate with QuickBooks.
Realtrac checks the terms defined on the purchase order. If those terms are not currently a set of Terms in QuickBooks, Realtrac automatically adds the terms to the Terms List in QuickBooks.
·Realtrac checks if the items being purchased are in the Inventory Items List in QuickBooks. If it is not, Realtrac automatically adds the items to the QuickBooks Inventory Items List.

Once these steps have been completed, the Realtrac client creates the purchase order in QuickBooks and transfers the information over on the purchase (quantity purchased and price each). QuickBooks will reflect the same purchase order number that Realtrac uses for the item order. After the purchase order has been exported, we can check in QuickBooks and confirm that the data was received and is proper.

Purchasing with Multiple Expense Accounts

With some additional setup work within QuickBooks, Realtrac users are able to set up and maintain multiple expense accounts based on different items being purchased. Before we show the setup and configuration necessary to support this, let’s examine what goes on when Realtrac sends a purchase order over to QuickBooks. The behind the scenes magic of what goes on during a Post a PO to QuickBooks is:

First, Realtrac will confirm that QuickBooks knows about the Vendor the purchase order is for.

If QuickBooks does not have the Vendor in their Vendor List, Realtrac will automatically create the Vendor for the user in QuickBooks.

Realtrac will test to see if QuickBooks is aware of the Terms, Shipping Method and/or Sales Rep that is listed on the Realtrac Purchase Order.

If any of these items are not present on the various Item Lists in QuickBooks, Realtrac will automatically create the entries for the user.

Realtrac now looks through each item on the Purchase Order, and makes sure that the QuickBooks Item List has the items listed.

If the Items on the PO are not currently in the QuickBooks Master Item List, Realtrac will create the items in the list for the user.

Now that Realtrac knows that QuickBooks is aware of all the data that is going to be on the QuickBooks Purchase Order, Realtrac will begin sending over the actual Purchase Order itself.

Realtrac pulls in the Vendor from the QuickBooks Vendor List
Realtrac pulls in the Terms, Shipping Method, Sales Rep information from the QuickBooks lists.
Realtrac pulls in the QuickBooks Items that match the items on the Realtrac Purchase Order.
Realtrac now submits the completed, fully built Purchase Order from Realtrac to QuickBooks.

So in our above scenario, let’s focus in on Step #3. If we’ve got an item named Blue #543 on our purchase order, Realtrac asks QuickBooks “Hey, do you have an item named Blue #543 on the Master Item list?”. If the answer is “Yes”, Realtrac moves on. If QuickBooks doesn’t have that item in its list, Realtrac will attempt to make the item automatically in the QuickBooks Master Item List (which we note in Step #3-A above). At this point, Realtrac is going to use the default “Expense Account” account as set up in the Realtrac Accounting Setup Utility to define what Expense account the item Blue #543 is going to use by default.

If I look in my QuickBooks Item List, I will see the item has been created, and Office Supplies is the expense account used for purchasing this item.

But, let’s pretend I have my QuickBooks Expenses set up such that I wanted my purchases of paint to land in my “Paint Supplies” instead of the normal “Office Supplies” account. No problem! In this case, it’s as easy as switching the Account right in this Edit Item interface in QuickBooks:

Once we make the change, QuickBooks will confirm the change with us, and are even nice enough to change the expense account on the existing PO that we just posted over!

At this point, the Blue #543 item on the PO we already posted over, along with all future PO’s with Blue #543, are going to have the expenses go in to the “Paint Supplies” expense account, rather than the “Office Supplies” expense account.

This has been a very long winded way to say this simple (hopefully, after the previous example) statement: Whatever the expense account is for the item in the QuickBooks Item List, is the Expense account that will be associated with the item when Realtrac posts a PO with the item on it.

Best Practice in Purchasing

So there are 2 ways a Realtrac user can choose to implement this ability to custom set the Expense Account for items they are purchasing:

Post the item to QuickBooks using Realtrac for the first time, and then manually change the Expense account in the QuickBooks item. (Which is the process described and shown above.)

Manually create the item in the QuickBooks Item list, assign it the expense account of your choice, and Realtrac will automatically use the item you created, rather than creating the item.

It’s recommended that our users pick one method or the other, and stick with that protocol for all purchases.

If a user chooses to manually create the items in QuickBooks themselves, they have total control over the setup of that item. However it is set up in QuickBooks, Realtrac will simply follow the setup for that item.

//Additional Item Codes

Before we discuss adding additional item codes, we need to examine a bit more in depth how the Realtrac and QuickBooks integrate. For the purposes of this discussion, we’ll talk exclusively about invoicing, but the basic concepts are the same for how Realtrac integrates with QuickBooks purchasing (purchase orders).

During the process of Realtrac transmitting an invoice over to QuickBooks, Realtrac will ask QuickBooks if it has the item on the invoice in the QuickBooks Master Item list. Assuming it does, Realtrac asks QuickBooks for the details on the item, including its default Income account.

These means that any Realtrac invoice with this part, “08081978-13”, will be associated with the “Sales” income account (#47900). But what if you have a part where you want the income to go to a different income account? Realtrac has that ability!

The ability to add additional Item Codes, and assign those item codes to invoice items, freight charges and/or miscellaneous charges is what the middle section of the Accounting Setup program is used for.

Let’s set up a painfully contrived example and see how this works in practice. I’ve got an item that I sell where we want the income associated with the part to always land in my Accessory income account, not my traditional Sales income account.

First, I’m going to use the Utility to add an item entry in to Realtrac named “Accessory Sales”. Add the code and click the Add button.

Next, I need to add an Item in to the QuickBooks item list. I am going to create a new item, named “Accessory Sales”, and in QuickBooks, set up that item to use the “Accessory” income account.

At this point, my systems are set up. I can now use the Realtrac Parts Editor to assign the QuickBooks “Accessory Sales” item to a Realtrac part. This will mean that when we post the resulting invoice to QuickBooks, Realtrac is going to tell QuickBooks the invoice item is “Accessory Sales”, not the actual item on the invoice. Let’s set up this mapping in the Realtrac Part Editor. Open the Realtrac Part Group by navigating to the “Part Estimates and Jobs” option within the Realtrac Toolbox in the main Realtrac toolbar. Navigate over to the Part Editor tab, and find the part you wish to override. Select the overridden part name from the “Acct Setting” drop down box.

Let’s see what happens now when I make a Realtrac invoice for this part, and post that invoice over to QuickBooks.

And when posted to QuickBooks, instead of the actual part, we see the invoice in QuickBooks is using the Item “Accessory Sales”, since we overrode the part in Realtrac.

Item Code Override (Invoicing)

By default, when Realtrac posts an invoice for QuickBooks, we take the Item(s) on the Realtrac invoice and search the QuickBooks Item List for that item. If the Item doesn’t exist in the QuickBooks Item List, Realtrac will automatically create the item in the QuickBooks Item List for the user. Some customers would prefer the Item(s) on the QuickBooks Invoice to always contain the same generic QuickBooks Item. For Realtrac users that require this scenario, Realtrac provides a method to allow this mode of operation. (There could well be accounting problems running the Realtrac/QuickBooks in this mode. Make sure to review this decision with your accounting professional before running Realtrac in this mode.) Note: In order to switch Realtrac to this mode, the user needs to put a text file named item.txt in the C:\ProgramData\Realtrac\ directory. If the Realtrac user has multiple computers that post Invoices from Realtrac to QuickBooks, note that the item.txt file must exist on each machine that has the Realtrac and QuickBooks integration. Also note that if the accounting user(s) get new PCs, this item.txt file will need to be recreated on the new computer. Lastly, by default the ProgramData directory will be hidden, users may need to show hidden folders in order to easily access this folder.

Within the item.txt file, simply type in the word/name of the QuickBooks Item that you wish to appear on all QuickBooks invoices/invoice lines. Do not press enter/carriage return, simply type in the QuickBooks Item Name and save the file. (In the Figure above, the user has chosen to set up their integration such that every QuickBooks Invoice/Invoice Line will use the item code “Grapes”.)

When run in this mode, Realtrac will not attempt to make the overridden item in QuickBooks for the user. In other words, make sure the Item Code you put in the item.txt file already exists in the QuickBooks Item List before attempting to post a Realtrac invoice in this mode. With this file saved, restart Realtrac if running, and attempt a post to QuickBooks.

//Importing Customer and Vendors

Realtrac 10 and the Accounting Setup utility offer the ability to do a one-time import of the customers and vendors that are present in QuickBooks, into Realtrac. Before attempting this step, please make sure your accounts are configured properly as described above.

As you can see at the bottom of Figure , the Accounting Setup utility has a button labeled Import Cust and Vend. With QuickBooks open and logged in (with enough privileges to see and manipulate customer records), clicking this button will pull in all active Customers and Vendor in QuickBooks, creating those same Customers and Vendors in Realtrac. The process is automatic, the user only need to press the button and wait for the results. Please note this process may take several minutes, and you may be unable to access QuickBooks on the PC during the process (if you use QuickBooks in a multi-user environment, other users can continue to use QuickBooks normally during the import).

Once the customers and vendors have been imported, for all future new customers and vendors, the user only need to add the customer and/or vendor to Realtrac. The first time a customer is invoiced, Realtrac can automatically create the customer in the QuickBooks customer list for the user. Ditto for the vendors and purchase orders. The first time a user posts a PO for a newly acquired vendor, Realtrac will automatically create the vendor in the QuickBooks vendor list for the user.

//Sales Tax Support

Realtrac supports syncing sales tax on invoices transmitted between Realtrac and QuickBooks. Many businesses using Realtrac invoice business to business, where sales taxes are normally not collected. But for users that sell direct to customers, Realtrac supports syncing with a single tax authority (IE: for a company that only collects sales tax for the Tax authorities of the state they are operating in) or syncing Invoices with multiple tax collection agencies (if a Realtrac user is large enough that it has presences in multiple states and jurisdictions, and has to remit sales tax to many different agencies). Note that at this time Realtrac does not support the QuickBooks “Sales Tax Group” process. We can only define single “Sales Tax Items” to Realtrac invoices. In this section we will first cover the setup for customers that remit to only a single tax authority. After covering that process, we will talk about the special case where users can set up additional tax authorities in both QuickBooks and Realtrac and sync the invoices together. It’s important to note that for companies that pay to multiple tax agencies, they still need to follow the instructions and details in the Single Tax Authority section below. Then in addition, they should read and follow the directions in the Multiple Tax Authorities section that follows. Using a Single Tax Authority in Realtrac and QuickBooks If a Realtrac user pays only a single tax authority (typically the tax agency in the state their business resides in), Realtrac expects that tax authority to be set up in QuickBooks with the name “Sales Tax”. (Unlike a lot of data, Realtrac will not automatically create this special Sales Tax Item in your QuickBooks Item List for you.)

So we would expect that in the QuickBooks Item List, there will be a Sales Tax Item named Sales Tax similar to:

When setting up this item in QuickBooks, the QuickBooks user/administrator can set the Description, Tax Rate and Tax Agency to the values appropriate for their needs. A Realtrac/QuickBooks user in Michigan may set their QuickBooks “Sales Tax” item up as follows:

With the “Sales Tax” item created in QuickBooks, our setup is complete. What happens now is, when a Realtrac user creates an invoice that needs to have sales tax applied, they can type either the sales tax rate or the specific tax amount in to the Realtrac invoice:

After setting the sales tax on the Realtrac invoice, when the user chooses to Post/Export this specific invoice to QuickBooks, Realtrac will set the following fields on the QuickBooks invoice it creates:

Set the QuickBooks Sales Tax Item to “Sales Tax” (which will tell the Invoice to use the QB Item “Sales Tax” for items that are taxable)
Set the taxable status to “TAX” for each item (which will tell QB we want to apply “Sales Tax” QB Item to each Item on the QB Invoice) Here’s an invoice after we posted it to QuickBooks:

Realtrac, QuickBooks and Multiple Tax Authorities

For Realtrac users that have multiple tax authorities, we have to do a little more set up within Realtrac. It is presumed that the user already has multiple Sales Tax Items present in their QuickBooks Item List. This means if a user adds a new tax authority while using Realtrac, they will need to first add the Sales Tax Item to their QuickBooks themselves. In this example below, the QuickBooks user has the standard “Sales Tax” item, as well as ones titled “Illinois Sales Tax” and “Ohio Sales Tax” (each item set to have it’s own specific sales tax rate depending on the location and rules).

This Realtrac user is going to have to use the Realtrac Accounting Setup to add the “Illinois Sales Tax” and “Ohio Sales Tax” options to Realtrac. Within Realtrac Accounting Setup, the user will input the name and select the Sales Tax radio button and then hit the “Add” button. They will do this for each QB Sales Tax Item they want to use within Realtrac.

With all of the QB Sales Tax Items added via the Realtrac Accounting Utility, we are now ready to use these alternate Sales Tax Items in Realtrac. Our Realtrac user makes an invoice for a customer in Ohio, which happens to be a state that their company has a nexus in, so they are required to collect and remit sales tax to the authorities in the wonderful state of O-H-I-O.

With the invoice set up (items added, FOB, Shipping, Terms all set) we are able to apply our state of Ohio specific sales tax. In Step #1, the user needs to right click in the Sales Tax Percentage field and select the QB Sales Tax Item they want to apply to the Realtrac invoice:

And Step #2 is simply user typing in the sales tax percentage in the Realtrac invoice, which will automatically calculate the exact amount to collect.

After posting this invoice to QuickBooks, we see the following:

Similar to the prior QuickBooks invoice, we see the specific item (“08081978-12”) has its tax status set to “Tax”. Unlike the prior QuickBooks invoice, our generic “Sales Tax” item is not being used. Instead, the “Ohio Sales Tax” is being used, since the code was set on the Realtrac Invoice via the right click menu. 33 Realtrac QuickBooks Integration Setup Guide v2.7 As a cool tip, if the user names their QB Sales Tax Item with the percentage included, Realtrac will automatically calculate the Sales Tax on the Realtrac invoice when the user selects it in Step #1 above (in other words, they don’t have to do Step #2). So for example, if we had set up our “Ohio Sales Tax” QuickBooks Item as “Ohio Sales Tax 5.75%” Realtrac would automatically know the sales tax was 5.75%, and the user wouldn’t need to execute Step #2 above.

//Requirements

Realtrac 10 supports integration with QuickBooks 2007 and newer. Realtrac 10 only interfaces with the Windows desktop version of the QuickBooks program. QuickBooks Online (previously named QuickBooks Online Edition or QBOE) is not currently supported. QuickBooks must be installed and running on the same PC as the Realtrac client software to use export features.