Volume discouting matrix over time

[bjornc]

Hi guys, i have a problem i have been trying to solve the last couple of days inbetween a very busy schedule, for the love of my life i can't seem to figure this one out.

The problem is quite simple by nature, i can do the math by hand, but i can't do the formula.

I have one Table which contains the pricing the tiers:

From	To	Price
0​	50​	1​
51​	100​	2​
101​	200​	3​

And then i am trying to calculate the revenue/invoice based on sold units accumulated:

	2026	2027	2028	2029	2030
Units sold	100	30	400	4000	3000
Total acc.	100	130	530	4530	7530
Revenue	250	?	?	?	?

Any help is appreciated.

 

[lrobbo314]

How are you calculating revenue? I can't track how you're getting 250 for 2026.

 

[bjornc]

How are you calculating revenue? I can't track how you're getting 250 for 2026.

I managed to clutter the copy-pasta, it should be 150 for 2026, and for 2027 it should be 210.

(Btw, how do edit my post, i can't find the edit button).

 

[bjornc]

and for 2027 it should be 210.

This is also wrong ofcourse, for 2028 it should be 210.

 

[bjornc]

This is also wrong ofcourse, for 2028 it should be 210.

(dear god) it should be 150, 210... x

 

[lrobbo314]

Ok, but I still don't understand how you're coming up with those values.

For example.

In 2026 it says that there were 100 units sold. The table says that the price per unit should be 2.

So, I would think that 2026 should be $2 * 100 units sold = $200.

 

[Dave Patton]

Commissions 2023.xlsm
	A	B	C	D	E	F
1
2		2026	2027	2028	2029	2030
3	Units sold	100	30	400	4000	3000
4	Total acc.	100	130	530	4530	7530
5	Revenue	150	90	1,200	12,000	9,000
6		150	90	1,200	12,000	9,000
7
8	Bracket	Rate
9
10	0	1
11	50	2
12	100	3
2a

Cell Formulas
Range	Formula
B5:F5	B5	=SUM((B4>$A$10:$A$12)*(B4-$A$10:$A$12)*($B$10:$B$12-$B$9:$B$11))-SUM($A$5:A5)
B6:F6	B6	=LET(cumul,SUM($A$3:B3),SUM((cumul>{0;50;100})*(cumul-{0;50;100})*({1;1;1})))-SUM($A$6:A6)

Commissions 2023.xlsm
	A	B	C	D	E	F	G
1	Total	7530	22440		TRUE
2		2026	2027	2028	2029	2030
3	Units sold	100	30	400	4000	3000
4	Total acc.	100	130	530	4530	7530
5	Revenue	150	90	1,200	12,000	9,000
6		150	90	1,200	12,000	9,000	Without row 4
7
8	Bracket	Rate
9
10	0	1
11	50	2
12	100	3
2a

Cell Formulas
Range	Formula
C1	C1	=SUM((B1>{0;50;100})*(B1-{0;50;100})*({1;1;1}))
E1	E1	=SUM(B5:F5)=C1
B5:F5	B5	=SUM((B4>$A$10:$A$12)*(B4-$A$10:$A$12)*($B$10:$B$12-$B$9:$B$11))-SUM($A$5:A5)
B6:F6	B6	=LET(cumul,SUM($A$3:B3),SUM((cumul>{0;50;100})*(cumul-{0;50;100})*({1;1;1})))-SUM($A$6:A6)

 

[bjornc]

Commissions 2023.xlsm
	A	B	C	D	E	F
1
2		2026	2027	2028	2029	2030
3	Units sold	100	30	400	4000	3000
4	Total acc.	100	130	530	4530	7530
5	Revenue	150	90	1,200	12,000	9,000
6		150	90	1,200	12,000	9,000
7
8	Bracket	Rate
9
10	0	1
11	50	2
12	100	3
2a

Cell Formulas
Range	Formula
B5:F5	B5	=SUM((B4>$A$10:$A$12)*(B4-$A$10:$A$12)*($B$10:$B$12-$B$9:$B$11))-SUM($A$5:A5)
B6:F6	B6	=LET(cumul,SUM($A$3:B3),SUM((cumul>{0;50;100})*(cumul-{0;50;100})*({1;1;1})))-SUM($A$6:A6)

Commissions 2023.xlsm
	A	B	C	D	E	F	G
1	Total	7530	22440		TRUE
2		2026	2027	2028	2029	2030
3	Units sold	100	30	400	4000	3000
4	Total acc.	100	130	530	4530	7530
5	Revenue	150	90	1,200	12,000	9,000
6		150	90	1,200	12,000	9,000	Without row 4
7
8	Bracket	Rate
9
10	0	1
11	50	2
12	100	3
2a

Cell Formulas
Range	Formula
C1	C1	=SUM((B1>{0;50;100})*(B1-{0;50;100})*({1;1;1}))
E1	E1	=SUM(B5:F5)=C1
B5:F5	B5	=SUM((B4>$A$10:$A$12)*(B4-$A$10:$A$12)*($B$10:$B$12-$B$9:$B$11))-SUM($A$5:A5)
B6:F6	B6	=LET(cumul,SUM($A$3:B3),SUM((cumul>{0;50;100})*(cumul-{0;50;100})*({1;1;1})))-SUM($A$6:A6)

In the first year, we sell 50 unit @ 1 USD = 50 usd. Then in the same year, we sell an additional 50 units @ 2 USD. = 150.
For the next year, we only sold 30 units @ 3 USD (since the total volume is above 100). So total revenue for that year is 90, but accumulated it is 210.

 

[Dave Patton]

What calculation would you expect on the the total?
The information does not seem consistent with the term Discount.
Why is the accumulated in second year 210?

Commissions 2023.xlsm
	A	B	C
15	130
16	Bracket	Rate
17
18	0	1	50
19	50	2	150
20	100	3	240
21	1E+35
2a

Cell Formulas
Range	Formula
C18:C20	C18	=MIN($A$15-A18,A19-A18)*B18+C17
A21	A21	=10^35

 

[lrobbo314]

I'm with Dave on this one. It seems like 2027 should be returning 240, not 210. Either way, this is what I have.

bj
	A	B	C	D	E	F
8		2026	2027	2028	2029	2030
9	Units sold	100	30	400	4,000	3,000
10	Total acc.	100	130	530	4,530	7,530
11	Revenue	150	240	1,440	13,440	22,440
Sheet2

Cell Formulas
Range	Formula
B10:F10	B10	=SCAN(0,B9:F9,LAMBDA(s,c,s+c))
B11:F11	B11	=MAP(B10#,LAMBDA(x,LET(t,x,a,IF(t-50>0,50,t),b,IF(t-100>0,50,t-a),c,IF(a+b>t,0,t-(a+b)),SUM(VSTACK(a,b,c)*{1;2;3}))))
Dynamic array formulas.