Calculating sales tax

[Egan]

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?

 

[nise23]

I definitely think this is doable. For the dependent drop-down boxes, you can use data validation and the INDIRECT function. If you search for these terms on here you will find lots of info. The website http://www.contextures.com/xlDataVal01.html has been super helpful to me in explaining this.

I would rethink the order of your drop downs. In most cases it will probably be sufficient to have the user enter city and state since the city is in one county so county tax either needs to be added or not.

So then to look up the tax you would use the VLOOKUP function in your table.

These videos captures the essence of what you will be doing:
http://www.youtube.com/watch?v=Ko68RF89JVk
http://www.youtube.com/watch?v=-78bH0RZ7MA&feature=relmfu

This should give you something to get started, and post back with more specific questions!

 

[Egan]

I definitely think this is doable. For the dependent drop-down boxes, you can use data validation and the INDIRECT function. If you search for these terms on here you will find lots of info. The website http://www.contextures.com/xlDataVal01.html has been super helpful to me in explaining this.

I would rethink the order of your drop downs. In most cases it will probably be sufficient to have the user enter city and state since the city is in one county so county tax either needs to be added or not.

So then to look up the tax you would use the VLOOKUP function in your table.

These videos captures the essence of what you will be doing:
http://www.youtube.com/watch?v=Ko68RF89JVk
http://www.youtube.com/watch?v=-78bH0RZ7MA&feature=relmfu

This should give you something to get started, and post back with more specific questions!

Thanks!

I'm quite familiar with vlookup so that part should be easy.

The only hiccup on looking up by city first may be that many states, and in some cases even counties within a state, may have duplicate city names. So I think I need all 3 pieces to make sure I'm looking up the right tax rate. Does that make sense?

I'll test out that INDIRECT validation tonight.

 

[nise23]

It does make sense, and you can make it work either way. It might depend on the kind of work you do, too. If the county is common knowledge, it's no big deal. As a user, I would prefer to only have to select the county if it is important because I hardly ever know what county a city is in, but this may not be the case for "your users". Just something to consider...