Customer Bonus Calculation

[#1]

I have gotten a new task which initially sounded very simple. Still, after some days of brain squashing, I didn't really figure out how to solve it yet.

First I try to explain it in text form:

Many of our customers get bonuses on a volume of sales basis for a period stipulated by contract. Bonuses are paid out at year's start, when all the bonuses from last year are accumulated. Bonus periods are not necessary one year, but may vary. I.e.: We have a special bonus promotion running from April-June. If customer A reaches €250.000 in this period, we grant him 2% bonus on his sales total (enhancement bonus, lvl.1), if he reaches €500.000, we grant him additional 1% on his sales total (enhancement bonus, lvl.1). Unaffected of this promotion, he generally gets 2% on his sales total for the whole year (fixed bonus) plus 1% if he reaches €1.000.000, plus 1%, if he reaches €1.500.000.

Assuming that customer A had €235.000 sales in between January-March 2012, €354.000 in between April-June 2012 and €487.000 in the rest of the year, this means:

	Bonus type	Period	relevant sales	Bonus	Reached	Bonus total
1	Yearly fixed bonus	01.01-31.12.	1.076.000	2%	Y	21.520
2	Yearly enhancement bonus lvl. 1	01.01-31.12.	1.076.000	1%	Y	10.760
3	Yearly enhancement bonus lvl. 2	01.01-31.12.	1.076.000	1%	N	-
4	Promotion bonus lvl. 1	01.04.-31.06.	354.000	2%	Y	7.080
5	Promotion bonus lvl. 2	01.04.-31.06.	354.000	1%	N	-
	Yearly payout					39.360

I think, this example is pretty simple. The problem, howevers, is not. There's one major time period (the calender year) where all the different bonuses if a customer should be summed up for. For each promotion there's one additional and variable time period in between, which may vary from one customer to the other. There are varying bonus agreements for different customers. Some may only have fixed bonuses, some may have 3 levels of enhancement bonuses, some may have 5; each with different sales levels, of course. It's not simply summing up sales and multiply it with a factor.

Luckily I have not yet designed the layout of the bonus table, so I'm open for suggestions regarding how to design it best. At the moment, i have gotten an excel-table as basis, that pretty much looks like this:

Cust-ID	Fixed	>10.000	>25.000	>50.000	>75.000	>100.000	>150.000	>250.000	>500.000
23578	1,0			1,0		2,0		3,0
28841	2,0
29412	1,0
29989						1,0	1,5	2,5

Any ideas on how to work this one out?

[#2]

Nobody? C'mon guys and gals ...