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:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Client A Sales[/TD]
[TD]Client A Revenue Share[/TD]
[TD]Client B Revenue Share[/TD]
[/TR]
[TR]
[TD]0-250,000 users[/TD]
[TD]30%[/TD]
[TD]70%[/TD]
[/TR]
[TR]
[TD]250,001-500,000 users[/TD]
[TD]35%[/TD]
[TD]65%
[/TD]
[/TR]
[TR]
[TD]500,001-1,000,000 users[/TD]
[TD]40%[/TD]
[TD]60%[/TD]
[/TR]
[TR]
[TD]1,000,001 + users[/TD]
[TD]45%[/TD]
[TD]55%[/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="width: 208"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Deposit [/TD]
[TD="align: right"]$1,500,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Price[/TD]
[TD="align: right"]$3.50[/TD]
[TD]/user/month[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 272"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD="colspan: 4"]Revenue distribution[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50%[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]until $1,500,000 is recovered
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Client A
Revenue Share[/TD]
[TD][/TD]
[TD]Client B Revenue Share[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]250000[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]70%[/TD]
[/TR]
[TR]
[TD="align: right"]250001[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]500000[/TD]
[TD="align: right"]35%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]65%[/TD]
[/TR]
[TR]
[TD="align: right"]500001[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1000000[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]60%[/TD]
[/TR]
[TR]
[TD="align: right"]1000001[/TD]
[TD][/TD]
[TD]+[/TD]
[TD][/TD]
[TD="align: right"]45%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]55%[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 648"]
<colgroup><col><col><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Year 1[/TD]
[TD]Year 2[/TD]
[TD]Year 3[/TD]
[TD]Year 4[/TD]
[TD]Year 5[/TD]
[TD]Year 6[/TD]
[/TR]
[TR]
[TD]New Users[/TD]
[TD="align: right"]50,000[/TD]
[TD="align: right"]100,000[/TD]
[TD="align: right"]150,000[/TD]
[TD="align: right"]150,000[/TD]
[TD="align: right"]200,000[/TD]
[TD="align: right"]250,000[/TD]
[/TR]
[TR]
[TD]Cumulative Users[/TD]
[TD="align: right"]50,000[/TD]
[TD="align: right"]150,000[/TD]
[TD="align: right"]300,000[/TD]
[TD="align: right"]450,000[/TD]
[TD="align: right"]650,000[/TD]
[TD="align: right"]900,000
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 648"]
<tbody>[TR]
[TD="width: 162"]Revenue[/TD]
[TD="width: 82"] ?[/TD]
[TD="width: 76"] ?[/TD]
[TD="width: 78"] ?[/TD]
[TD="width: 83"] ?[/TD]
[TD="width: 83"] ?[/TD]
[TD="width: 84"] ?[/TD]
[/TR]
</tbody>[/TABLE]


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.