Run out of ideas...Need help with Revenue Distribution

[spm1515]

Working on a business model and having trouble coming up with a formula that can manage my revenue distribution model.

Parameters:
Client A will pay Client B a deposit of $1,500,000 against future revenue distribution. Client A will retain 50% of Client B's revenue distribution until the deposit of $1,500,000 has been covered.

Once deposit has been covered the following revenue distribution will be followed:

Client A Sales	Client A Revenue Share	Client B Revenue Share
0-250,000 users	30%	70%
250,001-500,000 users	35%	65%
500,001-1,000,000 users	40%	60%
1,000,001 + users	45%	55%

<tbody>
</tbody>

Any insight is greatly appreciated.

 

[gaz_chops]

Maybe vlookup
Table

[TABLE="width: 370"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Client A Sales[/TD]
[TD]Client A Revenue Share[/TD]
[TD]Client B Revenue Share[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"]70%[/TD]
[/TR]
[TR]
[TD="align: right"]250000[/TD]
[TD="align: right"]35%[/TD]
[TD="align: right"]65%[/TD]
[/TR]
[TR]
[TD="align: right"]500000[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"]60%[/TD]
[/TR]
[TR]
[TD="align: right"]1000000[/TD]
[TD="align: right"]45%[/TD]
[TD="align: right"]55%[/TD]
[/TR]
</tbody>[/TABLE]

=VLOOKUP($A11,$A$2:$C$5,2)
Change 2 to 3 for Client B

[/FONT]
[TABLE="width: 249"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]200000[/TD]
[TD="align: right"]30%[/TD]
[/TR]
[TR]
[TD="align: right"]501000[/TD]
[TD="align: right"]40%[/TD]
[/TR]
[TR]
[TD="align: right"]260000[/TD]
[TD="align: right"]35%[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=Lucida Grande]

 

[spm1515]

Not sure I'm following the vlookup formula. It doesn't seem to account for the $1.5M deposit that needs to be recouped before rolling to the the revenue distribution.

Also, I don't think I was clear in my original post that I'm only trying to find Client A's revenue distribution as they gain users and get their deposit back.

 

[gaz_chops]

Not sure I'm following the vlookup formula. It doesn't seem to account for the $1.5M deposit that needs to be recouped before rolling to the the revenue distribution.

Also, I don't think I was clear in my original post that I'm only trying to find Client A's revenue distribution as they gain users and get their deposit back.

I don't think it is clear from your OP what exactly you are after! Maybe add an example of what results you are after.

 

[spm1515]

Deposit	$1,500,000
Price	$3.50	/user/month

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Revenue distribution
50%			until $1,500,000 is recovered
				Client A Revenue Share		Client B Revenue Share
0			250000	30%		70%
250001			500000	35%		65%
500001			1000000	40%		60%
1000001		+		45%		55%

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

	Year 1	Year 2	Year 3	Year 4	Year 5	Year 6
New Users	50,000	100,000	150,000	150,000	200,000	250,000
Cumulative Users	50,000	150,000	300,000	450,000	650,000	900,000

<colgroup><col><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>

Revenue

?

?

?

?

?

?

<tbody>
</tbody>

Need to figure out what the revenue will be as the cumulative users grow each year, plus accounting for the $1.5M deposit that has to be earned back before going into the revenue distribution breakdown above.

 

[gaz_chops]

So 50% is retained by A, the remainder is split as per your table?

 

[spm1515]

Client A needs to recoup the $1.5M deposit through their revenues (50/50 revenue split with Client B) prior to falling into the Revenue Distribution table. Once Client A has recouped all $1.5M then you have to pay attention to the number of users Client A has signed up each year so you know what revenue distribution tier they are in.