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)
