Sales Tax Report

[Jules Klapper]

I am trying to create a Sales Tax report and having difficulty. First, I created a data base which lists Date of transaction, Net Sales, Sales Tax, County (there are many Counties in NY to keep track of, and return net sales and Sales Tax returned). Every quarter I need to prepare a report on the sales taxes collected.

The report needs to look for records between month/01/2001 and month/01/2001 (3 months) and summarize the totals for net sales, sales tax, return net sales and returned sales tax by county.

Any help would be appreciated.

[crimmelcp]

RE: Sales Tax Report

Jules:
2 ways that I know might work.
Method 1- In the transaction Table put fields for the following
County code
county name
county tax rate.
city code
city name
city tax rate

Create a county table with the fields
County code
county name
county tax rate

Create a city table with the fields
city code
city name
city tax rate
All updates to the tax rates will be done in one of these tables.

When you enter records into the transaction table, you can do look ups on the county code and city code and fill in the names and rates.
That way you will get the proper rate in the transaction table when the record is entered.
Then you could just do reports from the transaction table.

Method 2
Create the same fields in the transaction table
Create the county and city tables
add effective dates(to and from ) to both the county and city tables.
Then you could use the post operation to look up the correct rates for the dates needed.
Charlie Crimmel

[crimmelcp]

RE: Sales Tax Report

Jules:
If the rates change in the middle of the quarter
(jan and Feb has a tax rate of .03)
March increases to .035)
You need the ability to calculate the correct rates for a period of time.
Either method should work.
Charlie Crimmel

[Jules Klapper]

RE: Sales Tax Report

Thanks Charlie,

However, I only have the fields mentioned because that is all that I need: County & rate (this is the only sales tax collecting authority). Then I have the net sales total and the sales tax fields for which I enter data for any day (date field) that I collect the taxes. The only other fields pertain to any returns of merchandise I enter, so I have a field for returns: one for sales total and sales tax with the date and county and rate for that record.

How would this affect what you suggested?

Jules

[crimmelcp]

RE: Sales Tax Report

Jules:

Why dont you zip up your app and either post it on the board or send it to me so I can look at what you have.
I do this all the time in our payroll app.
In Indiana every county has a different tax rate depending on whether the person is a resident of Indiana or not.
Ohio has school districts based on where the person lives and city tax based on where the job is.
Charlie Crimmel

I still think you could have a county table with the county code, name and rate.
County code
county name
county tax rate.

Create a county table with the fields
County code
county name
county tax rate

All updates to the tax rates will be done in the county table.

When you enter records into the transaction table, you can do look ups on the county code and fill in the names and rates.
That way you will get the proper rate in the transaction table when the record is entered.
Then you could just do reports from the transaction table.

[JohnZaleski]

RE: Sales Tax Report

Jules,
It sounds to me like you are collecting a single record for each sale transaction whether a sale or a return with a date. It is a simple matter within Alpha's report creation to create ask variables fromdate and todate and select records that fall within that date range. It is also simple to create summary calculated fields to add them up using the drag and drop expression genie. Then in the report parameters you could specify no detail section so that just the totals print out.
John

[Jeff Moses]

RE: Sales Tax Report

Jules, We've been around the block several times calculating and reporting sales tax. A couple of questions first.
1. Do you calculate on an accrual or cash basis or both? Not all states calculate the same way.
2. If you calculate on a cash basis do you recieve payments for a charge in full or do you break it up into indvidual deposits on a charge?
3. When you create a bill, are all of the charges taxable or can some be exempt?
4. Do you have any customers that are always tax exempt customers included in the totals?
5. Do you give the user the option to calculte the sales tax on an accural or cash basis?
6. Do the reports you need have to have all the figures required to fill out a Sales Tax coupon?

If you can answer these questions I may be able share some of our sales tax nightmares and their solutions!
Jeff

[Jules Klapper]

RE: Sales Tax Report

I am not sure what you mean by cash versus accrual: I collect the tax and pay the state every 3 months. I have a convuluted accounting system: I have been using One Write Plus (OWP) DOS version for years because they have a tracking system you can set up that can be applied to Sales Taxes. However, I just got a new computer with Windows XP and DOS programs are just not right for this system. I have tried almost all the other off the shelf accounting systems, including the OWP windows system but none have an ability to track numerous sales tax rates.

On my charges and invoices sales tax is broken down: subtotal for merchandise+sales tax=total amount. Some can be exempt but when I enter the data in the data base I created specifically for sales taxes, I have already taken this into account.

4. no
5. no
6. no, I can always do some manual work but I do need to get the totals for each tax rate for the 3 month period.

Thanks for your help,

Jules

[Jeff Moses]

RE: Sales Tax Report

Jules,

Accrual reporting means that the full taxable amount of an invoice is reportable at the time the invoice or charge is created, even if you have not received payment yet.

Cash reporting means that you are only responsible for sales tax on actual amount of cash received during the tax period.

In other words if you invoice something for $1000 and only recieve $500 on the invoice during the period, using accrual reporting you must still report $1000 and make payment on that amount. If you don't ever get the balance you have to file and adjustment at the end of the year. Using Cash reporting you would only have to report $500 and pay the tax collected on $500.

Accrual reporting is much easier to track as you don't have to deal with partial payments on charges but I believe some states still allow cash reporting. Most like cash reporting as you don't have to front the tax until you get it from the customer.
Jeff

[Jules Klapper]

RE: Sales Tax Report

I am a retail business so we book money as it is received. 95% of our sales are credit card, check or cash.

I hope I conveyed what I am trying to do correctly. I am trying to create this system so I can obtain a Windows based upgrade for my OWP accounting system. Since I know it can't track sales tax the way I need, I created the little data base with the fields mentioned in A5 so I could enter data about the sales taxes collected and get a report I could use when I filled out the State form quarterly.

Jules

[crimmelcp]

RE: Sales Tax Report

Jules:
If you zip up your application so we can look at your tables and forms, I am sure that some one could help and send it back to you.
We are not interested in the data, just the forms layout and the formula being used in calculated fields.
That way we can better understand what you need.
Charlie Crimmel