Exporting to QuickBooks^®,  Quicken^® or Microsoft^® Money^® from Guest Tracker 3.1

Table of Contents

1. Setting up your Accounts
   1. Accessing the Guest Tracker Export Wizard
   2. Bank/Asset Accounts
   3. Income & Lodging Accounts
   4. Liability Accounts
   5. Additional Considerations about Accounts

2. How does this work? What Gets Exported When?
   1. Advanced Deposits & Gift Certificates
   2. Overpayments & Refunds
   3. Reservation, Non-Reservation & Cash Sales Charges
   4. An Example
      
3. Exporting From Guest Tracker & Importing into your Accounting Program

Setting up your Accounts

The most critical part of exporting from Guest Tracker and importing into your Accounting Program is setting up your accounting categories in each system properly so that Guest Tracker can communicate properly with the Accounting Program.

Accessing the Guest Tracker Export Wizard

The Export Wizard is located in Preferences within the Administration (Admin) section of Guest Tracker.

1. Click the Administration button to display the Administration section.

2. Click the Preferences button to display the Preferences window.

3. Click the Charges tab to display the Charges Preferences.

4. Click the Export Options button to display the Revenue Export wizard.

5. Select the desired file export format: QuickBooks - .IIF or Quicken - .QIF. NOTE: To export to Microsoft Money, choose the Quicken format. Also, click the Starting Date "Change" button and enter the beginning date for your export. Give careful consideration to the date that you enter as the Starting Date should only be set once unless you want to start completely over with a new financial database. 

CAUTION: Changing the Starting Date after you've already exported may re-export data that has already been exported to your financial package. All reservations with a departure date prior to the starting export date, will be set to the exported status. That means data that was previously exported may be exported a second time.

6. Click the Next button to continue setting up your Accounts!

Bank/Asset Accounts

A Bank or Asset Account is required for each of the different types of payments or deposits that can be made in Guest Tracker: Credit Cards, Checks, Cash or Other. The Credit Card Account gives you the option of using Sub Accounts (in QuickBooks) or Separate Accounts (in Quicken) for each of the different types of Credit Cards that are used for additional detail.

You also have the option of specifying whether each of these accounts is a Bank Account or Asset Account. A Bank Account would typically be used in situations where you deposit money in the bank the same day that you receive it. This is often the case with Credit Card payments when used with an automated processing system (such as ICVerify) which deposits the money in the bank for you electronically every day.

An Asset Account should be used in situations where you've received a payment of some sort but you will not be depositing that money into your bank account immediately. In QuickBooks, this is referred to as an Undeposited Funds account and serves as a holding bin for the money until you can deposit that money into the bank and transfer it to the appropriate bank account in your accounting program.

Export Tip: If all of your money is deposited into the same Bank or Asset Account, you can use the same name for these accounts. However, if you use the same name for more than one Bank/Asset Account, they MUST be the same type of account (ie. Bank or Asset). For example, see the Checks, Cash & Other accounts specified in the following graphic, they all use the "Undeposited Funds" Asset account in the accounting program.

Income & Lodging Accounts

Each of your Revenue Accounts within Guest Tracker must be setup as Income Accounts in your accounting program. When processing a payment of any type, the export will Credit (increase) the appropriate Bank/Asset Account and Debit (increase) the appropriate Income Account. 

If you are taking advantage of the concept of Sub Accounts in your accounting program, you can still use that feature with the Guest Tracker export by naming your Revenue Accounts in Guest Tracker using the "MainAccount:SubAccount" format. For instance, if you have a Sales Account which has Coffee Mugs, Sweaters, and Robes as sub accounts in your accounting program, you can name your Revenue Accounts in Guest Tracker "Sales:Coffee Mugs," "Sales:Sweaters" and "Sales:Robes" to match these accounts in your accounting program.

In Guest Tracker you must also specify which account is your Default Lodging Account when setting up the export. When exporting, Guest Tracker will process all of the Lodging charges on a guest's bill first and will process charges from other Revenue Accounts after all Lodging charges have been dealt with.

There is also a way to deal with more than one Lodging Account. For instance if you have Cabins and a Motel, you may have a Revenue Account for your Cabins and a separate Revenue Account for the Motel. In this situation, you can rename your Revenue Accounts in Guest Tracker using a common prefix and a colon. For example, "Lodging:Cabins" and "Lodging:Motel". This will create an Income Account called Lodging with Sub Accounts of Cabins and Motel within your accounting program. When setting up the export within Guest Tracker, you can specify either one of these accounts as your Lodging Account and Guest Tracker will then process charges from EITHER one of these accounts before processing other charges on a guest's bill. In fact, it will process charges from any Revenue Account that starts with the word "Lodging" (or anything prior to the first colon).

Liability and Tax Accounts

Finally, a Liability Account will be required for each of the following categories: Advanced Deposits, Purchased Gift Certificates, Tax1 and Tax2. These Liability Accounts are typically temporary holding bins. 

For instance, when you receive an Advanced Deposit, this deposit will appear in the appropriate Bank/Asset account and also in the Advanced Deposits liability account since it represents money that was deposited in the bank but it does NOT contribute to income yet because you've received money but have not yet provided a product or service for that money. When you receive a payment (or when that guest arrives or departs if the Deposit pays for the entire bill), the money is moved out of the Advanced Deposits liability account and into the appropriate Income and Tax Liability accounts, indicating that you have now earned that money and it can now be accounted for.

QUICKBOOKS WARNING

If you have QuickBooks setup to charge sales tax (as specified in the QuickBooks Preferences under the Sales Tax category), Guest Tracker will not be able to export into your Sales Tax account. It is recommended that you create a separate Sales Tax account in QuickBooks. This account will contain the Guest Tracker Sales Tax and when you pay taxes, you can transfer the tax from the Guest Tracker Sales Tax account to your main QuickBooks Sales Tax account.

If you do try to use the same Sales Tax account, the following error will occur when trying to import your Guest Tracker data:

Additional Considerations about Accounts

The Account Names that you enter into the Guest Tracker Export Wizard are NOT Case Sensitive. So if you have a Bank Account called "CREDIT CARDS" in your accounting program, valid names for the Credit Card Account Name that you enter into the Export Wizard would include "Credit Cards" or "CREDIT CARDS" or "credit cards". Depending on your Accounting Program, however, upon importing Guest Tracker data into your Accounting Program, the Account Names in your Accounting Program may be updated according to the way you've named them in Guest Tracker.

Also, if you have not yet setup an account for each of the accounts in the Guest Tracker Export Wizard, the accounts will be automatically created for you the first time you import into your Accounting Program. Please be aware that Account Name and Account Types MUST match. So if you've named your Advanced Deposit Liability Account "DEPOSITS" in the Export Wizard and you already have a Bank Account named "DEPOSITS" in your Accounting Program, an error will occur when trying to import the data into the Accounting Program and the import most likely will not complete successfully.

The last step of the Export Wizard will also give you the opportunity to Print the list of accounts that Guest Tracker requires to be setup within your Accounting Program so you can easily go and check your work.

How Does this Work? What Gets Exported When?

Guest Tracker exports using the Cash Accounting Method (with the exception of Advanced Deposits which will be discussed in a moment). Cash Basis Accounting is a method of bookkeeping in which you regard income as occurring at the time you actually receive a payment. The Accrual Accounting Method is the other way to account for your revenue. Accrual Basis Accounting is a method of bookkeeping in which you regard income as occurring at the time you ship a product or render a service. If you use the Accrual Accounting Method, you will NOT be able to use the Export feature described in this document. We recommend that you use Guest Tracker's Revenue section using the Date Sold option and manually transfer your totals into your accounting program if you are using Accrual Basis Accounting.

Businesses typically use either the Cash method or the Accrual method of accounting but most small lodging properties use a combination of these methods. In general, Guest Tracker accounts for your income when Payments are received from guests, which abides by the Cash Accounting basis. Thus, the export is driven by Payments that are entered in the system. When a Payment is received, Guest Tracker will export that Payment on the day it was received and distribute it to the appropriate Income & Tax Accounts. If the Payment is posted to a bill that has an Advanced Deposit or Gift Certificate on the bill, that Deposit and/or Gift Certificate will also be accounted for and distributed to the appropriate Income & Tax Accounts on the date of that first Payment.

Advanced Deposits & Gift Certificates

In Guest Tracker, Advanced Deposits and Gift Certificates are considered liabilities until the guest pays their bill. When a Payment is entered into Guest Tracker, the deferred income from Advanced Deposits and Gift Certificates is then recognized as revenue. At that time, the sum of the Advanced Deposit and the Payment is distributed to the appropriate Income Accounts and Tax Liability Accounts.

If an Advanced Deposit or a Gift Certificate pays for the entire amount of the reservation (the Balance Due is $0.00), then revenue is recorded on the reservation's arrival or departure date (depending on your Guest Tracker Post Charges Preference: when posting To Arrival or Daily, the Arrival Date is used, when posting To Departure, the Departure Date is used as shown in the following graphic).

Please Note: Donated gift certificates are not exported in this version. Only purchased gift certificate revenue data is exported. If you are using Guest Tracker's concept of Purchased/Donated Gift Certificates, only the Purchased portion of the Gift Certificate will be exported. The Donated portion will be ignored by the export.

Overpayments & Refunds

If a guest pays too much and their bill in Guest Tracker shows a Credit Balance, this overpayment will be applied as a Liability to the Advanced Deposit Liability account. Refunds are processed on the day that they are applied to a guest's bill and they will Credit the Advanced Deposit Liability account (decreasing your liability) and will also Debit (decrease) the appropriate Bank or Asset Account.

Which Charges are Exported?

Only Reservation Charges (charges that apply to a reservation) and Non-Reservation Charges (charges that apply to a Person but not a reservation) are exported. Cash Sales (charges that do not apply to a Person or a Reservation such as Point of sale charges) are NOT exported. If you use the concept of Cash Sales in Guest Tracker, you will need to manually enter these charges into your Accounting Program (again, you can use the Revenue Section in Guest Tracker to display and print reports to get these numbers).

An Example

Joe Traveler calls on June 10th and makes a reservation for September 15th. A deposit of $100 is required to confirm the reservation and is charged to Joe's credit card. The total cost of the reservation is $200 + 5% State Tax + 2% Local Lodging Tax, bringing the total to $214, less his $100 deposit for a balance due of $114 when he arrives on September 15th.

On June 10th, the list of Charges in Guest Tracker will show:

These new transactions are flagged in the Guest Tracker database as NOT EXPORTED. When the Export operation is performed on June 10th, the only transaction that is exported is the deposit, since it represents money that was deposited in the bank. The deposit will appear in the appropriate Bank/Asset Account and also in the Advanced Deposits Liability Account. The deposit does NOT contribute to income until Joe makes his payment in September at which point it will be distributed to the appropriate Income Accounts.

When Joe arrives on September 15th, he writes a check for his remaining balance. The list of Guest Tracker Charges will now appear as follows:

This time when the export operation is executed on September 15th, the following transactions will be posted for 9/15; the date that the payment was received:

The $100 deposit is moved out of Advance Deposits and distributed as follows:

$93.46 to the Lodging Account  
$4.67 to the State Tax Account
$1.87 to the Local Tax Account

The $114 payment is added to the appropriate Bank/Asset Account and distributed as follows: 

$106.54 to the Lodging Account
$5.33 to the State Tax Account
$2.13 to the Local Tax Account.

All transactions for this reservation will now be flagged as exported.

Exporting & Importing Procedures

For a detailed description of how to create an export file from Guest Tracker and import that file into your accounting program, see pages 4-9 through 4-12 in the "What's New in Version 3" guide.

*All product and company names are trademarks or registered trademarks of their respective companies.