Please-please-Help - Need to Resolve this - Allocation problem

[Guest]

I have a very pressing need to find a solution to this problem below. Any
help towards this is very much appreciated.

I am having trouble finding a formula that will allocate a number to
various “buckets†based on the seniority of these buckets. Let me exemplify:

I have operating cash flows of $200

Most senior bucket - 1 - Obligation A: $70
Second senior bucket - 2 - Obligation B: $180
Second senior bucket - 2 - Obiigation C: $60

So, the formula should allocate $200 by order of seniority -- allocating $70
to Obligation A since it is most senior. Then the remainder $130 (i.e $200 -
$70) should get allocated to second most senior. Since both Oblig B and C
are equally senior, remainder gets allocated to both equally or to the max of
the obligation. Which means that B will get $70 and C will get $60.

I should also mention that the seniority of obligation is not cell dependent
- i.e. the most senior obligation will not always occur in the first cell or
any one particular cell. All obligations will be in entered and then user
can assign 1 through "n" against the obligation to define which is senior - 1
being the most senior. The following may clarify as well (note B, C, and D
are all equally senior). Any help will be much appreciated! Thanks

Amount Available for distribution $200

Rank* Name Obligation Amt Paid Formula
1 A $70 $70 ?
2 B $40 $40 ?
2 C $55 $55 ?
3 D $30 $5 ?
2 E $30 $30 ?

* User input, random sequence, not dependent on cell – i.e. most senior
obligation can occur at the very end of table, for example.

 

[Guest]

In your scenario all priority 1's are paid first, then 2's and finally 3's;
in a case with 2-1's the most senior gets 100% with the remaining funds going
to the other #1; or if more than one #1 the allocation is divided equally if
there are not enough funds to pay off completely?

Are/could priorities be set-up by date(s)?

 

[Guest]

I set up you data on row 3. in A1 entered Disbursement and C1 entered 200

I sorted the ranks ascending and entered the following formulas

d4 =IF(AND(A4=1,C1>C4),C4)
d5 =IF(AND(A5=2,E4>C5),C5)
e4 =C1-D4
e5 =E4-D5

and copied formulas in line 5 down.

You'll probably need to add more if statements if you need to pay rank 3. If
itas more than 7 then use SUMPRODUCT

regards
Peter

 

[Guest]

Ignore my last post, this is done better with a macro. Copy this formula into
a VB Module, ALT + F11, Insert, Module and run the macro from Excel's tool
menu

Sub T()
Dim rnk As Range, lo As Integer, hi As Integer
Dim i As Integer, nr As Integer, j As Integer, y As String
amnt = 200
y = ActiveCell.Address
Set rnk = Application.InputBox("Enter the rank range", " Range of Ranks", y,
, , , , 8)
y = ActiveCell.Row
nr = rnk.Rows.Count + y
lo = Application.WorksheetFunction.Min(rnk)
hi = Application.WorksheetFunction.Max(rnk)
For i = lo To hi
For j = y To nr
If Cells(j, 1) = i And Cells(j, 3) < amnt Then
Cells(j, 4) = Cells(j, 3)
Cells(j, 5) = amnt - Cells(j, 4)
amnt = Cells(j, 5)
End If
Next
Next
End Sub

No sorting is required. see below

Rank Name Obligation Amt Paid Disbusement
3 D 30
2 B 40 40 90
2 C 55 55 35
2 E 30 30 5
1 A 70.00 70 130

regards
Peter

 

[Guest]

Either one of Billy's suggestions are excellent...exactly what I was going to
suggest.