Multiple Profit Loss Calculation

[Leonard1]

Hi,

I'm not certain which formula to use here or if I'm doing this in the most efficient way and hoping someone could help?

The scenario is that I have an overall income but the expenditure is split between more than one person. The expenditure of these people is to be repaid through the income and paid in order. So person 1 will get paid first and so on.

The total income is calculated and pulled from a seperate sheet, as is the expenditure of the individuals. I've set a sheet up like this for the prfit/loss and expect the numbers to read as follows:
[TABLE="width: 427"]
<TBODY>[TR]
[TD][TABLE="width: 500"]
<TBODY>[TR]
[TD]Total Income[/TD]
[TD]£900[/TD]
[TD][/TD]
[TD][/TD]
[TD]Expenditure[/TD]
[TD]Profit/Loss[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 1[/TD]
[TD]£500[/TD]
[TD]£0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Person 2[/TD]
[TD]£500[/TD]
[TD]-£100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Club[/TD]
[TD][/TD]
[TD]£0[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD="align: right"]</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]


What I'm looking to achieve is Person 1's profit or loss to come fom the total, then person 2's profit or loss to be calculated using the remaining amount and the club to receive anything extra.

Can anyone help with this?

Thanks

 

[Chris Macro]

what are the percent profits allocations? Or is it split evenly between all three parties?

 

[bruderbell]

It appears that a person cannot take a profit and the club cannot take a loss. Is that correct?

 

[Leonard1]

Sorry I didn't include that, you're correct. An individual cannot make a profit from this so the aim is get the indivduals back to even. Once this is achieved it moves to the next individual. If all individuals are even then the club can make a profit. The club can't make a loss as the original capital came from the individuals.

 

[bbott]

If I'm reading this right....

Assuming the following:
Total income in cell B1
Expenditures starting in D2
P/L starting in E2

Formula for E2: =IF(B1>D2,D2,B1-D2)
Formula for E3: =IF(B1-E2>D3,D3,B1-E2)
Fromula for E4: =IF(SUM(E2:E3)=B1,0,B1-SUM(E2:E3))

 

[bbott]

Nevermind, didn't quite work...

 

[bbott]

Formula for E2: =IF(B1>D2,D2,B1-D2)

Using same assumptions, change this formula to: =IF(B1>D2,D2,B1)

Now it should work.