0

I'm trying to wrap my head around taxable sales, non-taxable sales, and tax paid using only a view values our Point-of-Sale outputs each month.

I have Order Total and Tax as the two figures I need to work with. The problem(s) I'm having are when some sales have taxable items and non-taxable items in the same sale. We can always go back and look at the individual invoices for assurance, but I would prefer to be able to do this in Excel.

Here are all the fields I'm working with:

  • Order Total(total price of sale)
  • Tax (only the amount of tax paid for this Sale)
  • MINUS TAX (=[@[Order total]]-[@Tax]
  • ASSUMED TAXABLE (=IF([@[EXPECTED TAX]]=[@Tax],[@Tax],1/0.06*[@Tax]))
  • ASSUMED NONTAXABLE (=ABS([@[ASSUMED TAXABLE]]-[@[MINUS TAX]]))
  • EXPECTED TAX (=[@[MINUS TAX]]*0.06)

The issue I'm having is in some cases, when the calculations don't match up exactly because of "rounding" errors. Here's an example:

ORDER TOTAL: 202.66
TAX: 11.47
MINUS TAX: 191.19
ASSUMED TAX: 191.17
ASSUMED NONTAXABLE: 0.02
EXPECT TAX: 11.47

Due to the calculations used, there is a margin of error in this example of $0.02.

How do I account for those margin of errors and make the backwards calculations (Starting with a total and working your way backwards to assume Tax and Subtotals) operate more reliably?

EDIT:
I totally forgot to mention that, yeah, this is all due to how Tax Tables work, but I'm not entirely sure how to fix that with Excel.

EDIT 2:

ORDER TOTAL: $82.24
TAX: $4.66
MINUS TAX: $77.58
ASSUMED TAXABLE: $77.67 (=IF([@[EXPECTED TAX]]=[@Tax],[@Tax],1/0.06[@Tax]))
ASSUMED NONTAXABLE: $0.09 (=ABS([@[ASSUMED TAXABLE]]-[@[MINUS TAX]]))
TAX OFF EXPECTED: $0.00 (=IF(ABS([@Tax]-[@[EXPECTED TAX]])<([@[Order total]]0.06),0,(ABS([@[EXPECTED TAX]]-[@Tax]))))
EXPECTED TAX: $4.65 (*=[@[MINUS TAX]]0.06)

Here, EXPECTED TAX is off by $0.01 from what it actually is. Additionally, ASSUMED NONTAXABLE should be $0.00. Also, ASSUMED TAXABLE, is way off ($0.09 - which is the ASSUMED NONTAXABLE)

BeardedSith
  • 129
  • 11

1 Answers1

0

You can wrap your formulas in Round() (or RoundDown() or RoundUp()), which allows you to control the number of digits you want to round to (most likely 2 digits in your case). Worked for me with the numbers in your example.

Pomul
  • 392
  • 3
  • 11
  • Still having the same problem using ROUND(). Here's an example - Total Sale was $62.90. Amount of tax in that number is $3.56. Subtracting tax from the Total Sale gives us $59.34. However, if we try to "backwards" calculate that based on the amount of tax, we get $59.33 instead of $59.34. Wrapping the formula in ROUND() gives the same numbers. eg. ``=ROUND(IF([@[EXPECTED TAX]]=[@Tax],[@Tax],1/0.06*[@Tax]),2)`` – BeardedSith Jun 04 '19 at 11:31
  • The error is caused by the floating point precision : https://support.microsoft.com/en-ca/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel. Namely here it's your 1/0.06 floating point that causes the problem. You can try the solution in the blog post, but you might as well reverse calculate the whole thing. Instead of 1/0.06, use 1/(Tax/Minus Tax)*Tax. I'd still suggest you wrap everything in Round(). – Pomul Jun 04 '19 at 12:56
  • That's what the formula is written to do isn't it? Bring up Tax if TRUE and calculate Minus Tax if FALSE. – Pomul Jun 04 '19 at 14:04