Formula Help to Work out Commission Percentages

[YorkeG]

Hello,

I would like some help with a commision calculator spreadsheet I am trying to put together.

Essentially, this document will take the monthly sales of our company and calculate the value of the bonus pool that will be shared among staff.

The commission pool grows as monthly sales (cell B4) climb above the breakeven level (cell B2). Our commission structure is based on thresholds which are based on sales above breakeven (cell B6). The thresholds are marked on the spreadsheet. For the first million dollars above breakeven, 5% of the gross profit dollars goes into a bonus pool. For the second million dollars above breakeven, 6% of the gross profit dollars go into a bonus pool, and anything about that point has 7% directed into a bonus pool.
Gross profit dollars (cell B10) is the sales above breakeven applied by the gross profit margin (cell B8).

I would like a formula created to calculate the value of the bonus pool.

Here is a calculation to explain the correct value in the example of the numbers on the attached spreadsheet:

Sales Above breakeven = $ 1,021,563.47

First $ 1,000,000 above BE is subject to 5% of Gross Profit Dollars: ($1,000,000 x 40% = $400,000. $ 400,000 x 5% = $ 20,000)

The remaining $ 21,563.47 above BE is subject to 6% of Gross Profit Dollars: ($ 21,563.47 x 40% = $ 8625.38. $ 8,625.38 x 6% = $ 517.52)

The bonus pool is then $ 20,517.52 ($ 20,000 + $ 517.52)

I trust this makes sense, but please let me know if any further clarification is needed.

I look forward to the help from someone!! Thank you!

[Bo_Ry]

Please try

=SUMPRODUCT(TEXT(B4-N(+E8:E11),"0.00;\0;0")*G9:G12)*B8