Calculation for Expenses and Profit Loss

[zubair99]

I need someone to produce a spreadsheet to manage the following problem for us through providing a spreadsheet.
"We are 3 people with 3 different bank accounts, we have decided to share the expenses and profits between us 30%, 35% and 35%. So what we need is an excel where we can enter the expense individually from each person and who paid from their account and then it would automatically divide the expense. We would like it to divide the profits as proportionally as well. Also, as example of complication is that when a person pays for an expense say $100, he is only responsible for $30, he needs $70 from the total profit and then the remaining profit is divided proportionally. I hope this makes sense.

Thank you

 

[DarrenRobinson]

Hi there, a good way to solve this problem is to separate the issues of expense reimbursement and total profit. The way to do this is add up the total expenses for each of the three individuals and notionally fully reimbursement them. Add up the total reimbursement and subtract this from the profit; this will give you actual profit your company has made. To calculate the amount to reimburse each individual create three columns: Date (cell A5), Amount (B5), Paid By (C5). Populate this with every expense. If an expense is paid by the company, then leave the Paid by empty. If its paid by an individual, put their name in. Then up the top, above all of the data in cell A1 write the first individual's name, A2 second person's name and A3 the third person. In cell B2 the formula is: =SUMIF($C$6:$C$3500,A1,$B$6:$B$3500). Copy that formula in cells B3 and B4. This will give the total amount to be reimbursed to the three of you. Reduce the profit by this amount.

Payment to each of you is then = Share * (Profit - Total Reimbursement) + Individuals Reimbursement.

Let me know if that works for you.