Help! Tiered sales/bonus structure...

Hi,

I am having trouble creating the formulas to this tiered bonus structure. Any help would be greatly appreciated!!

The bonus structure is the following:


Pot 1: 0 to 300,000 in sales = 3.0% bonus for a total of $9,000
Pot 2: 300,000 to 600,000 in sales = 5.0% bonus for a total of $15,000
Pot 3: 600,000 to 900,000 in sales = 7.0% bonus for a total of $21,000
Pot 4: 900,000 to 1,200,000 in sales = 9.0% bonus for a total of $27,000

The bonus amount is calculated per quarter and is cumulative. For example, if I produced sales in Q1 = $100,000, I would get $100,000 *.03 = $3,000 (all in Pot 1). If I then produced sales in Q2 = $300,000, that would make my total sales = $400,000. I would get the remaining amount in Pot 1 ($6,000) and some in Pot 2 (400,000-300,000) * .05 = $5,000.

I am trying to build the formulas to calculate the bonus amounts by quarter for each Pot. Please see attached. Cells highlighed in grey need formulas. Any help would be greatly appreciated!!!

Sale structure.xlsx