multiple options for tax

[Peter Barfield]

Not sure if this is the right spot to post I apologise in advance if it isnt. My Scenario is this. I have a tax Table which includes GST (VAT) , State Tax, Wine Tax and Alco Pop Taxin the table I have the tax_ID, description and a multiplier for the purposes of calculation specific to each tax. i.e GSt 10% multiplier 1.1, Wine TAx 29% multiplier 1.29 and so on. Now some products are not subject to GST and some are that is OK and I can work that out. However, some products are subject too GST, Wine Tax and State Tax.(for simplicity in this example I will say that State Tax is not included). I have 2 fields set again for simplicity call it Tax 1 and Tax 2 + 2 Fields for the relevant tax multiplier(not sure if that is necessary) My question is and (I hope it is explained clear enough) is this. If the 1st field is set to GST then it shows as 10% and the multiplier is set to 1.1 however, how would one go about getting information to the 2nd field and getting the correct multiplier for the relevant tax. in this case the wine tax of 29% or multiplier of 1.29 gee i hope this makes sense.

 

[comment]

What is the result you are expecting to get here? I would think you would want dedicated tax rate and tax amount fields for each type of tax - otherwise, how will you be able to summarize the amounts collected for each tax type?

 

Edited March 29, 2016 by comment

[ggt667]

Do you have budget codes implemented in your solution? If so I would make a table that links budget code to tax rate; alternatively you could do this pr product.

[Peter Barfield]

Sorry Guys, it was late when I posted this and have just re-read and probably should have given more detail.

What I am trying to achieve is on an inventory table (with 3 tax fields ie TAx 1, TAX2 & TAX3 applying tax rates as required from the tax table (related via a tax_ID). As I said i can get it across if only 1 tax rate applies works like a charm but the 2nd and 3rd rate field (selected via dropdown list) if required dont return the information and I think that is logical as it is returning the data to the 1st instance. So how would one peform a routine or calculation to isolate the relevant 2nd & 3rd tax and more importantly its rate etc from the tax table into the inventory table. Hope this is a little clearer

[comment]

I am afraid I don't follow. You want to calculate the tax amounts in the Inventory table? That doesn't seem right, since tax rates are subject to change.

[Peter Barfield]

Comment, Sorry Im not real good at expressing what i am after. You are correct Tax Rates are subject to change. Thus my thinking is for them to reside in their own table whereby it can be changed easily and perhaps a script used to update the tax on inventory as required. My Sales and line items use a lookup to grab the tax rate from the inventory (thus as I understand any changes to the rate in future won't reflect on the previous sales they will remain as entered. I have an inventory table with a relationship to the tax rates via an ID. What you are suggesting (I presume) is that I dont have a tax table related to inventory. My thoughts were that the inventory table could "lookup" the relevant tax and take the tax rate from the tax table  and apply it to the inventory table tax fields. The tax amounts thus would be pulled from the tax table into the inventory or are you suggesting a more dynamic approach to allow for the rate change if it occurs? if so what process would you employ for this? bearing in mind any prevous sales would have to reflect the old tax rate not the new one. 

I had thought about using tax only at sales level however, some of those taxes are applicable to the cost prior to sale (or at landed) as well as to the sale itself thus the tax is applied at inventory level to obtain the correct costings per unit etc.

Cheers hope again this makes sense.

[comment]

IMHO, you need to lookup the tax rates directly from the Taxes table (or from a Preferences table) into the LineItems table. The Inventory table should keep track of which taxes are applicable to a product - not what their rates might be at any moment.

[Peter Barfield]

Comment, I see what you are saying and I agree in terms of the final tax at point of sale so to speak (it's how I originally had it set up). I guess where my grey matter is bombing out is in the pre - sale tax requirements. eg Wine Tax is not applicable at point of sale as it is calculated on the Landed Cost (for wine only) then GST is added to that to get a true per unit cost of your inventory item. Thus from there you calculate your sell price at whatever markup you want with those previous taxes calculated. Then as the item is entered into a sale a GST of 10% is added at point of sale where applicable (a Lookup at lineitems level would be appropriate in this instance). Thus your GST Debit/Credit system works. Maybe i am still looking at this wrong :-) or over /under thinking. I guess I still can't think for the love of mike how to pull 2 tax values from 1 table to 1 record. Cheers.

[comment]

22 minutes ago, Peter Barfield said:

Wine Tax is not applicable at point of sale as it is calculated on the Landed Cost (for wine only) 

I am afraid you have lost me at this point. If you have taxes that are applied upon purchase (?), then you must calculate them when purchasing (in OrderLineitems?).

If you want to add that to the cost, then what will you do when you renew the stock with a different tax rate?

 

28 minutes ago, Peter Barfield said:

I guess I still can't think for the love of mike how to pull 2 tax values from 1 table to 1 record.

That part, at least, is easy: use two fields in the target table. And I am not at all sure you need to have a record for each tax type. If you want to consider tax types as data (i.e let users add tax types without modifying the schema), then you would also need a join table of tax amounts - and you would have to run a script when creating a new line item. Again, this is assuming you want to be able to summarize the amounts of each tax type individually.

[Peter Barfield]

Comment, Thanks for the reply. Yes the wine tax had me scratching my head but your solution is solid. My problem is and I guess not rationale from the point of view of the end user (in this case) is that he wont be using this to order from he just does it manually from his head as he has done for  25+ years even though I have urged him to do it wholistically through this solution. It was an effort to get him to get this far. Old ways die hard. sometimes very hard. So There in lies my problem and I guess the solution is right in front of me I just have to get him to work with it i guess although I dont think this is going to be something that will happen. I know he should, his staff knows he should but he won't. Scratchy pieces of paper married to invoices is what you get. I kid you not. Cheers.