Investment repayment and Profit Distribution

[Willharveyjones]

Hi!

I'm new here! I am just working on a repayment and profit distribution schedule for a small budget film that I worked on as a passion project. Money from a sale is meant to be distributed to various people who contributed to the project and we want to calculate what will happen depending on what we sell it for. I can easily work out the formulas up until rows 16 to 18 where the remaining money is to be divided among 3 different investors with 3 different totals to reach. The amounts are to be divided to each of these investors according to the percentages 40%, 10% and 50% until their 'total to reach' is fulfilled. Once fulfilled it gets distributed to the other two etc. The remaining money is a profit and will be divided 50/50 amongst the investors and team. Is there a set of formulas I can use for this if the 'total to reach' may change?

WATERFALL CALCULATIONS.xlsx
	A	B	C	D	E	F	G	H
3					ESTIMATED SALE		$ 15,000.00
4
5		PAYMENTS
6	A	CAM		$ 50.00			$ 14,950.00
7	B	Sales Agent distributors		$ 50.00			$ 14,900.00
8
9
10	C	Recoupment	A) Cast	$ 9,000.00
11	C	Recoupment	B) Overages	$ -
12	C	Recoupment	C) Marketing Gross Receipts	$ -
13				C TOTAL	$ 9,000.00		$ 5,900.00	****Amount to be distributed
14
15			Distribution percentage	Total to Reach
16	1a	ATL Deferral	40%	$ 5,000.00
17	1b	Crew Defferals	10%	$ 8,000.00
18	1c	Investors (120% of investment)	50%	$ 10,000.00
19			100%
20
21
22
23		PROFITS
24	2	Production Team	50%				$ -
25	2	Investors Friends	50%				$ -
RECOUPMENT SCHEDULE

Cell Formulas
Range	Formula
G6	G6	=G3-D6
G7	G7	=G6-D7
E13	E13	=SUM(D10:D12)
G13	G13	=G7-E13
C19	C19	=SUM(C16:C18)
G24:G25	G24	=G$19*C24

 

[Willharveyjones]

Hi KRice! I really appreciate you helping me out with this! There is no way I would have been able to get to this solution. And apologies for the misleading labels - I will be more careful with that in the future. I appreciate your patience regardless. Thank you so much for everything - this is going to be such a useful tool!

 

[KRice]

I'm happy to help. I don't recall seeing such a scheme for distributions. It's messy. Have you tried out variations to see if the expected results are produced? The helper columns allow you to see which recipient is the focus for any given iteration, so you can follow along and track how the mini-distributions are made. This should be extensible should the number of recipients change...more iteration blocks would be copied and pasted (and ensure they link okay to each other). Let me know if you encounter any issues.