I have a Capital Investment Distribution table that I have been working on and the logic conditions that I have to apply seem to be getting excessive. I am wondering if there is possibly a simpler method that is escaping me.
D3:M3 represents Capital available for Distribution at the end of each fiscal year.
D4:M4 represents the minimum required Distribution Benchmark C2 plus any carryover from the prior year (negative value in ROW 6) if the amount available wasn't sufficient:
=IF($C$2-D$6<$C$2,$C$2,$C$2-D$6)...
(ignore D4 because there are no additional conditions for that cell)
D5:M5 represents the actual Distribution amount paid provided that there is enough Capital available from ROW 3 and if there isn't then the amount is paid to the extent of Capital available not to exceed the amount in ROW 4:
=IF(D$3<0,0,IF(D$3<=D$4,D$3,IF(D$4>$C$2,D$4,$C$2)))
ROW 4 and ROW 5 aren't that bad and I can live with them but it's ROW 6 that is brutal.
D6:M6 represents the remainder of the Distribution paid if the Capital available is sufficient to cover the amount from ROW 4, if Capital available is insufficient it results in a negative balance that is added back to ROW 4 +1COLUMN:
=IF(AND(D$3<=0,C$6<0),D$5-D$4,IF(AND(D$3<=0,C$6>=0),D$5-D$4,IF(AND(C$6<0,D$4-D$5>0),D$5-D$4,IF(AND(C$6<0,D$4-D$5=0),D$3-D$5,D$3-D$5))))
You can adjust the numbers (positive, negative, Zero) in ROW 3 to see how the conditions flow.
I hope I have explained this sufficiently and it is fairly self-explanatory in the attached worksheet. Let me know if you need further explanation.
example2.xlsx
Thanks
Bookmarks