My Overall sum on the Daily Tenders report is always a few cents out of balance with the Overall sum on the Daily Departments report. Has any one else seen this?
For example, on a day with 8k in sales we are 12 cents out of balance. We have lots of small sales, so I am wondering if this is a rounding error caused when you add up 1000's of sales vs. adding up the items in one sale?
Also, our sales tax rate is 9.9%; I am wondering if the sales tax on $1.00 displays as .10, but records in the database as .099?
Normally, the departments report is lower than the tenders report. Any thoughts?
My Overall sum on the Daily Tenders report is always a few cents out of balance with the Overall sum on the Daily Departments report. Has any one else seen this?
For example, on a day with 8k in sales we are 12 cents out of balance. We have lots of small sales, so I am wondering if this is a rounding error caused when you add up 1000's of sales vs. adding up the items in one sale?
Also, our sales tax rate is 9.9%; I am wondering if the sales tax on $1.00 displays as .10, but records in the database as .099?
Normally, the departments report is lower than the tenders report. Any thoughts?
Thanks for your reply. I guess I can roll up my sleeves and dig into the database tables to isolate this rounding problem, but this seems a bit unnecessary for such an expensive and widely used program.
Has any one else ever noticed this very basic out-of-balance condition (tenders don't equal departments+sales tax)? If every sale is stored correctly (with two decimal point accuracy), out-of-balance should be impossible!
Or perhaps the problem is with the report generator? Is there a rounding problem with groupmethodSum?
Any feedback will be appreciated. I want my bookkeeper to love RMS, but she is genetically opposed to out-of-balance conditions!
Thanks for your reply. I guess I can roll up my sleeves and dig into the database tables to isolate this rounding problem, but this seems a bit unnecessary for such an expensive and widely used program.
Has any one else ever noticed this very basic out-of-balance condition (tenders don't equal departments+sales tax)? If every sale is stored correctly (with two decimal point accuracy), out-of-balance should be impossible!
Or perhaps the problem is with the report generator? Is there a rounding problem with groupmethodSum?
Any feedback will be appreciated. I want my bookkeeper to love RMS, but she is genetically opposed to out-of-balance conditions!
Okay, between the KB article and Root's post I see it's not just me! I feel much better.
Do you happen to know the name of the table where sales tax is stored for each transaction? I would like to examine this table using SQL Enterprise Manager.
I have NOT looked into it too deeply, but I have the feeling that there are different ways RMS goes about calculating tax for a single batch. It would be interesting to know exactly how per transaction taxes, batch tax totals, etc. are calculated (as far as which data and formulas are used etc...) as there IS some sneaky discrepency in there somewhere. Like I said I have not fully investigated this, but I would hate to find that blaming all of it on rounding issues turns out to be the easy way out.
one thing in particualr as far as the KB article goes.
******* Potential Cause
There may sometimes be a small discrepancy in the Sales Tax totals in these reports because of how the software performs Sales Tax rounding.
For example, if you have an item priced $2.99 times a 8.35% Sales Tax, the total tax would be $0.249665. The software rounds this up to $0.25.
Resolution
There is no way to prevent this and it is a limitation of our currency only going out two decimal places.
********
First off, the software SHOULD round it up and store it as such. This is in no way an answer to the problem. UNLESS the batch total is re-figured (like my manager report example bolow) and not taken from the stored per transaction amounts. hmmm.....
Not to mention the manager reports do not add the numbers properly. For Example our tax is 6.5%: Say you have 4 entries for a taxable item in department A for $1.50 where the tax per item is $.10 totaling $1.60. When the report is grouped by department A the total tax adds up to $.39 instead of the $.40 that was collected. So the report has a grouping problem. It adds up the items sold prices ($6.00 in this case)then figures tax ($6.00 * 6.5% = $.39) rather than adding up the recorded tax values.
If anyone can shed some light on this topic please do so.
Okay, between the KB article and Root's post I see it's not just me! I feel much better.
Do you happen to know the name of the table where sales tax is stored for each transaction? I would like to examine this table using SQL Enterprise Manager.
I see now that sales tax is stored in many tables with 4 decimal places (including transactionentry, which is where my daily report comes from). This certainly would explain why adding up a day's worth of sales tax will never come out correctly.
As a SQL Server programmer myself, I cannot understand why sales tax would not be stored rounded to two decimal places. We CHARGE sales tax rounded to two places; we should STORE it that way.
Would I be wasting my time to post a suggestion that sales tax be stored correctly?
I see now that sales tax is stored in many tables with 4 decimal places (including transactionentry, which is where my daily report comes from). This certainly would explain why adding up a day's worth of sales tax will never come out correctly.
As a SQL Server programmer myself, I cannot understand why sales tax would not be stored rounded to two decimal places. We CHARGE sales tax rounded to two places; we should STORE it that way.
Would I be wasting my time to post a suggestion that sales tax be stored correctly?
1) The salestax column in the transactionentry table has four decimal places.
2) The salestax column in the transaction table is rounded to two places and will add correctly, giving an accurate total for the day.
Unfortunately, this means running yet another report each day, because no other report is based on info in the transaction table. I'm now up to five daily reports each day. Perhaps I'll have to buy Crystal reports, although I hate to. Wish there was a very simple built-in report to give the standard info every retailer needs.
Hope this new info helps someone with a similar dilemna!
Thanks for the research. I'd say it still warrants a suggestion to store the data in a 2 decimal, uniform manner in each table to prevent any discrepency..
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here.
All logos and trade names are the property of their respective owners.