Document Sample
TaxCompliant_demo Powered By Docstoc
					         online demonstration
This demonstration is intended to “walk you through” the complete process of collecting tax
information through your shopping cart to the final integration of your sales data into your
Quickbooks accounting system and the Excel tax preparation system.

Shopping Cart

We begin by assuming we have an e-commerce web site for a phony wine shop located in
Vashon Washington. This shop is “phony” in that it exists only to demonstrate the system—it does not actually exist but you can access it at for demonstration purposes. We named our phony wine shop,
“A Phantom Wine Shop”.

The shopping cart software is the popular open-source Zen Cart, modified to ask for the 4-digit
zip extension, to query for tax rate information, and to store sales transaction
data in a special table (named taxcompliant) of the shopping cart database. The software
modifications are discussed under “Software Development” of the web


Once our shopping cart has been modified, we are ready to register with In
our demonstration, we indicate that we are located in Vashon, Washington and also have nexus
in Buffalo, New York and Birmingham, Alabama (we have phony wine shops located there). We
also choose to collect Use Tax in California and Texas. We are not collecting Sales or Use tax in
any other state. We click on “Registration” of the TaxCompliant web site and fill out the form to
indicate our selections (and also to indicate that we’re going “demonstration” mode). Note
also that we choose “demo” as our userid and password. (The userid and password would be
the same as those set up in the shopping cart software—in the table named
taxcompliantusers.) You can view our completed registration form before we click the “submit
your registration” button: Screen 1, Screen 2.

Registration allows your shopping cart to access tax files at (when the userid
and password on the registration matches the userid and password in the taxcompliantusers
table). The registration also indicates the type of tax information your shopping cart is looking
for, i.e. sales, use, or no tax for your customer’s particular address.

Sample Sales Transactions
We have conducted 12 sample sales transactions to hypothetical customers residing in
Alabama, California, New York, Washington, Texas, and Wisconsin. For illustration, you can
click on a sample sale to John Smith1 in Redmond, Washington (screen3, screen4), and a
sample sale to John Smith2 in Santa Ana, California (screen5, screen6). (Note that the sales tax
rate for John Smith1 (9.5%) is determined by Bob’s address in Redmond, not the address of A
Phantom Wine shop in Vashon (8.6%). This is an example of “Destination” Sales—the sourcing
rule for Washington and many other states, including those participating in the Streamlined
Sales and Use Tax Agreement.)

Accessing the Shopping Cart Database

Our sales transactions are stored in the taxcompliant table of our shopping cart database. The
next step is to access this table and copy its contents to our desktop so we can integrate the
data into our accounting system and our tax preparation system.

For our Zen Cart application, we established a database to contain all of the Zen Cart tables as
well as two new tables, taxcompliant and taxcompliantusers (see “Software Development” in
this website). This database resides on a server, which is identified by the server name and a
userid and password which control access. We use PhpMyAdmin, a free downloaded software,
to access the database, with the appropriate server name, userid, and password.

Using PhpMyAdmin, we open our shopping cart database which is named tcsales, screen7.
Click on tcsales and a list of tables is displayed on the left side of the page, Screen 4. Click on
the table, taxcompliant, and the table’s contents are displayed, screen8. (You can click on
“browse” to view data for each field in this table—in our case, the 12 sales transactions we
exercised for this demonstration.) Click on “Export” which is a tab along the top of the page,
and the Export screen is displayed, screen9. Under the list “Export”, click on the button for
“CSV for MS Excel”; under “Options” replace “NULL” under “Replace NULL by” with a blank
(highlight NULL and delete it). Click the button “Save as file” and select a name for your file—
your choice, but we suggest something like tcfile followed by the date, in our example
“tcfile112511”. After clicking on “Go”, you will be asked where you want to save your file—
again, this is your choice, but we suggest your documents or downloads folder—some place
where you can easily find the file.

Your sales transactions data have now been copied from the database residing on the server to
an Excel file-- in CSV format—residing on your computer.

Converting the Excel File to Quickbooks Accounting and Tax Preparation Software

After registering with you will receive by mail a DVD disk containing an Excel
application to convert your Excel file to file formats for Quickbooks and the
tax preparation system. To use this application you must have Microsoft Office Excel 2003 or
above installed on your computer, and be sure that your Excel is macro enabled.

We have included this Excel application as a link for this demonstration—click on TCExcel.xls.
(This Excel application includes the import steps and conversion to an .iif file discussed in the
following paragraphs.) Also, be sure your Excel is “macro enabled” to process its Visual Basic

You will note that TCExcel is an Excel workbook containing four worksheets: tcfile, process,
qbooks, and tax. With TCExcel.xls opened, open tcfile112511.csv—the file you exported to
your computer (see the section “Accessing the Shopping Cart Database”). Right click on the
tcfile112511 worksheet and copy it to the TCExcel workbook. The TCExcel workbook should
now contain five worksheets including tcfile112511. Now right click on the tcfile worksheet and
delete it. Right click on tcfile112511 and rename it as tcfile. The TCExcel.xls workbook is now
updated with the current tcfile for processing. In addition to converting the data to Quickbooks
and special tax preparation software (discussed below), you can use Excel tools to analyze your
data—such as comparing tax collections by State, Use and Sales tax categories, etc.

Data in tcfile now needs to be converted to files suitable for importing into Quickbooks and also
for importing into another Excel program for tax preparation. By clicking on the “process”
worksheet you will be prompted to enter dates for report processing and key information for
your Quickbooks accounting. We recommend setting up accounts for Accounts Receivable,
Accounts Payable, Income, and Other Assets as indicated in yellow on this worksheet. Make
sure the account names are exactly the same as those established in your Quickbooks chart of
accounts (the names are also case sensitive). Also note that you need to add the exact “types”
for Customers and Vendors (noted at the bottom of the “process” worksheet).

After making sure your input data on the process worksheet are correct, click on the oval, “Click
here to generate files”. The program generates the qbooks worksheet for Quickbooks and the
tax worksheet which is ready for the Excel tax preparation software. These are the qbooks and
tax worksheets which you can click to review.

There is one final step to prepare the qbooks worksheet for import into Quickbooks.
Quickbooks imports files in Intuit Interface Format (iif). These files are text files with headers
recognizable by Quickbooks. To convert the qbooks worksheet to an iif file, follow this

       -   Right click on the qbooks worksheet and copy it to a new workbook, say Book1.
       -   In Book1, click “Save As” and save it as a CSV tab delimited text file.
       -   With the Notepad text editor, open Book1.csv and then click Save As. Name the file
           something like qbooks111511.iif. You must manually type the .iif following the
           name of your file (e.g. qbooks111511).

The file qbooks111511.iif is now ready for import into Quickbooks—screen10.

Import to Quickbooks

We created a Quickbooks application for our Phantom Wine Shop with this chart of accounts.
Note that the chart of accounts is the standard default list Quickbooks recommends for an e-
commerce retailer, but with the added accounts discussed in the section above. For example,
we added an Accounts Receivable account, “TC ACCOUNTS RECEIVABLE”. For our
demonstration, we did not include any accounting transactions before our import of the
qbooks111511 file.

To import, click File/Utilities/Import. This produces the page asking for the file name to be
imported, which we supply by double clicking on qbooks11511.iif (which we had saved in our
Documents folder). A message box will appear indicating “your data has been successfully
imported”. To view results, click on Detailed Balance Sheet Report (screen11, screen12) and
the Profit and Loss Detail Report, screen13.

Import to Excel Tax Preparation

Customers who choose to use the tax preparation system will receive each
quarter an updated Excel application named “TaxReport” on DVD disk. This is a large Excel
workbook containing over 100 worksheets for tax returns, supplements, etc.

After copying the “TaxReport” workbook to your computer, you can import the data file
necessary to process tax returns. This data file is the “Tax” worksheet of the TCExcel workbook
which was discussed under the section, “Converting the Excel File to Quickbooks Accounting
and Tax Preparation Software.”

To import the “Tax” worksheet, you will need to first open the TCEXcel workbook. Now open
the TaxReport workbook—the “Tax” worksheet is one of five worksheets contained in this
workbook. Right click on the “Tax” worksheet and copy it to the TCExcel workbook. Since there
is already a worksheet named “Tax” in the TCExcel workbook, your copied file will appear as
“Tax (1).” Right click on the “Tax” worksheet and delete it. Right click on the “Tax (1)
worksheet and rename it as “Tax”. Your data file is now ready to be processed.

To generate tax returns, click on the “Process” worksheet in the TCExcel workbook. This
produces a screen which asks for your Company’s information and process dates, screen14.
Clicking on the oval, “Process Your Tax Returns”, generates the tax returns applicable to the
sales transactions data in the “Tax” worksheet. (Processing may take considerable time,
depending on the volume of transactions, so please be patient while the program executes!)
Note that returns generated are listed on this worksheet; you can then click on the tabs (i.e.
worksheets) listed at the bottom of the Workbook to view or print the tax returns and/or
supplements for each state where you had sales.

For our demonstration, we generate tax returns for the following States:


California—screen16 , screen17, screen18, screen19, screen20, screen21

New York—screen22, screen23, screen24, screen25

Texas—screen26, screen27

Washington—screen28, screen29, screen30

Note that the tax returns are “facsimiles” of the actual returns to be filed with the states. Data
on these worksheets can be copied to the actual returns for e-filing or sending paper returns.
Obviously, your actual returns may contain data which is not displayed on these worksheets—
penalties, interest, particular exemptions, etc.

This completes the demonstration. A summary of the steps discussed above:

   1. We modified our shopping cart software to query for sales or use tax
      based on either the physical location of our store or the delivery location of our sales.
   2. We registered with to allow our shopping cart to access and to specify our particular requirements.
   3. We exercised some sample online transactions.
   4. We accessed our shopping cart database and exported our transaction data to our
   5. We converted data from the shopping cart to file formats for Quickbooks accounting
      and tax preparation software.
   6. We imported transaction data to our Quickbooks.
   7. We imported transaction data to our Excel Tax Preparation program and prepared tax returns
       for the States where we had sales.

Please feel free to contact if you have questions or particular comments
about this service. Constructively participating in the web site blogs will also be very much

Shared By: