Tiered tax rates

[BenVH]

Hi there everyone,

This is my first time posting so I thought I'd introduce myself. Currently I am a business student, but have spent the last 5 years working in the brewing industry as a brewer. Right now I am interning for a local brewery and doing a bit of budgeting for them.

Here's what I can't figure out:

The Canadian federal gov't taxes breweries based on a sliding scale: produce less than 2000 hectolitres get taxed at $3.12/hl, exceed 2000hl and get taxed at the next rate on any volume that exceeds 2000hl, exceed 5000hl and get taxed at the next rate, and so on and so on.

For example, say annual production is 6200 hectolitres. The first 2000hl would be taxed at $3.12, the next 3000hl(2001-5000) would be taxed at $12.49, and the next 1200hl(5001-15000) would be taxed at $12.49.

I've attached a worksheet with the tax scale on it.

Thanks a lot for having a read. Any help would be greatly appreciated

Ben

[ConneXionLost]

Probably not the most elegant solution (better could be done with a lookup table), but:

Please Login or Register  to view this content.

Cheers,

[shg]

I got rid of the merged cells (the bane of Excel) and left two empty cells above the lowest rate.

Please Login or Register  to view this content.

The formula in E4 is =SUMPRODUCT((E3>I5:I10) * (E3-I5:I10) * (J5:J10-J4:J9))

[Colin Legg]

I don't think that's quite right, ConneXion?

Here's my effort:

Please Login or Register  to view this content.

[ConneXionLost]

Thanks Colin, you're right. I think I used the wrong values in the last array. Trying again:

Please Login or Register  to view this content.

[Colin Legg]

Looks better , just that the numbers in the look up table had 3 significant places (eg 3.122 rather than 3.12).

[ConneXionLost]

Another good reason to use a lookup table!

[BenVH]

Thanks very much everyone for your help in figuring that out. I am constantly in awe of Excel and those that have mastered it.

Thanks again,
Ben