I'm stumped on how to tackle the problem of getting sales tax automated into a cost sheet I use for estimating projects.
My company sells a systems solution that includes hardware, software, professional services, freight, and travel expenses. We are required to pay sales tax in all states we operate in.
So when I put a project cost estimate together, I have two dimensions to deal with:
1) State/County/City for the tax rate (some states have 1 rate across the state, some add county tax on top, and some add city tax on top of both - here in California the combined sales tax rate varies by city)
2) Which of our product lines are taxable varies by state as well (i.e., Colorado charges sales tax on hardware & software while New York charges sales tax on hardware, software, freight, installation labor, and customer support agreements)
I'm not sure how to account for this. Right now I have a giant table as a list and the user has to scroll through hundreds of city/county/state combinations to find the one he needs.
I would much rather have the user enter the state from a drop down. If that state has one rate, it is returned in a cell. If that state tax varies, the next drop down would be for county. This would return a rate unless the rate varies by city as well. In that case, the user would have a third drop down for city which would then return the rate. Depending on the state, I would want my sheet to automatically determine what gets taxed (I will have a summation for each category - hardware, software, etc.).
Any ideas on where I should start or what the best approach is?
My company sells a systems solution that includes hardware, software, professional services, freight, and travel expenses. We are required to pay sales tax in all states we operate in.
So when I put a project cost estimate together, I have two dimensions to deal with:
1) State/County/City for the tax rate (some states have 1 rate across the state, some add county tax on top, and some add city tax on top of both - here in California the combined sales tax rate varies by city)
2) Which of our product lines are taxable varies by state as well (i.e., Colorado charges sales tax on hardware & software while New York charges sales tax on hardware, software, freight, installation labor, and customer support agreements)
I'm not sure how to account for this. Right now I have a giant table as a list and the user has to scroll through hundreds of city/county/state combinations to find the one he needs.
I would much rather have the user enter the state from a drop down. If that state has one rate, it is returned in a cell. If that state tax varies, the next drop down would be for county. This would return a rate unless the rate varies by city as well. In that case, the user would have a third drop down for city which would then return the rate. Depending on the state, I would want my sheet to automatically determine what gets taxed (I will have a summation for each category - hardware, software, etc.).
Any ideas on where I should start or what the best approach is?