Calculate Commission Structure

[Cameron]

I am working on a database where I need to take a monthly
production figure add it to the running total for the
year and multiply everything below $67,800 by 50% and
everything above $67,800 by 95%. It would also really be
great if I could somehow reset the annual figure every
year in September. Column A is a date figure so I am
thinking that somehow I can tie it to that. Can anyone
help me think of an easy way to put this formula
together. I would greatly appreciate the help.

 

[Tushar Mehta]

Other than column A being a date, what is the layout of the rest of the
data table?

Where is this data base? In XL? In a relational db? Somewhere else?

Since you want to reset the YTD in September, would it be fair to
assume your year runs from 9/1 to 8/31?

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tushar Mehta]

OK, first of all, I would strongly recommend you reorganize your data
into a relational table structure. Having monthly data in a separate
sheet is conducive to nothing but problems.

Second, is the commission structure dependent on monthly sales or year-
to-date sales? Is the $92,500 a threshold for each trade, each month,
or each year?

I still don't know what you have in columns A, B, and C. On the one
hand it appears you have individual trades, but your definition of
column C makes it look like a summary figure for the entire month.
Maybe, you should post some sample data.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[GB]

I am also totally unsure about what the OP is doing. However, I would GUESS
that the following is what he wants to do:

He has set up a column (D) with year to date sales that automatically resets
itself every september.
I guess the $92,500 is for YTD

He needs to set up a column (F) that records YTD net commission and resets
itself in the same way as col D,
=IF(month(A10)<>9,E10+F9,E10)

He just calculates in col E the correct YTD commission and deducts what has
already been paid
So col E is:
=50%*D10 + 45%*(Max(0,D10-92500)) - IF(month(A10)<>9,F9,0)

HTH - but no responsibility taken if you pay your salesmen the wrong amount!

Incidentally, the commission formula based on YTD appears grossly unfair
because it ties the salesman in until September each year. Is that standard
industry practice?

Geoff