Allocate amount to multiple pots/fund according to priority and remaining shortfall

[Intermediate]

Hi I'm a first time user motivated to join by the Excel problem I have.
(Excel 2007/ Win 7)

I'm attempting to set up a speadsheet to manage my savings. Each month I designate some of my income (x) towards my savings. I have multiple savings pots eg. emergency fund, kid's education fund, retirement fund. Each pot is prioritised as either Urgent (u), Short-Term (s), Mid-Term (m), or long-term (l).
Each fund has a target amount, opening balance and shortfall. I would like to automatically allocate the incoming amount (x) between the funds so that all of (x) is allocated pro-rata between all of the urgent funds first, and then if any of (x) remains allocate it in turn to any (s) funds pro-rata, then (m) funds pro-rata, then (l) funds pro-rata.

The pro-rata is fairly easy to allocate via fractions of the whole except that funds approaching their target (eg fund 2 below) often require less than their possible pro-rata allocation and the amount needs to be capped. The capping using =Min([shortfall], [pro-rata allocation]) causes some of the allocation that was initially for fund 2 to become spare again but it is now no longer allocated anywhere.

Also - I'm not sure how to allocate in sequence U first, then S, M, L

The spreadsheet I have goes like this - any help welcome - THANK YOU SO MUCH:

A | B | C | D | ETC
1) MONTH: | JAN | FEB | MAR | etc
2) INCOME (X) | £100 | £120 | £90 | etc
3)
4) FUND 1
5) PRIORITY: | U | U | U | etc
6) TARGET: | £7000 | £7000 | £7000 | etc
7) OPENING BAL: |£2000 | | |etc
8) SHORTFALL: | £5000| | |etc
9) AUTO ALLOCATION: | Y1 | | |etc
10)
11) FUND 2
12) PRIORITY: | U | U | U | etc
13) TARGET: | £4000 | £4000 | £4000 | etc
14) OPENING BAL: |£3900 | | |etc
15) SHORTFALL: | £10| | |etc
16) AUTO ALLOCATION: | Y2 | | |etc
17)
18) FUND 3
19) PRIORITY: | L | L | L | etc
20) TARGET: | £500000 | £500000 | £500000 | etc
21) OPENING BAL: |£0 | | |etc
22) SHORTFALL: | £500000| | |etc
23) AUTO ALLOCATION: | Y3 | | |etc
24)
25) FUND 4 etc

 

[cyrilbrd]

Hi,

you can post data using this:

Link.

Do you have a predetermined ratio of distribution or is it solely dependent to the level of urgency?

 

[Intermediate]

Hi,

you can post data using this:

Link.

Do you have a predetermined ratio of distribution or is it solely dependent to the level of urgency?

Thanks for clarifying Cyrilbrd,

All available income is allocated to all urgent funds first. If income (x) is greater than the sum of the shortfalls for all urgent (U) funds then no dilemma because each one can be topped up by the shortfall amount to achieve the target amount and the remaining income is assigned to short-term (s) funds etc..

However if X < (U1+U2+U3 etc shortfalls) then (X) is allocated pro rata as follows:
Income (x) = £100
U1 Shortfall = £100
U2 Shortfall = £50
U3 Shortfall = £40
U4 Shortfall = £10
Total Shortfall = £200

Each account receives (X)*(Un/)

U1 receives 100*(100/200) = £50
U2 receives 100*(50/200) = £25
U3 receives 100*(40/200) = £20
U4 receives 100*(10/200) = £5

If however (x) had been £250 the pro-rata amount for U1 becomes U1=250*(100/200)= £125 which is larger than the shortfall and would result in the savings pot having too much so the allocation needs to be capped at the shortfall amount so that £250 is allocated to U1-4 above as follows:

U1 receives £100
U2 receives £50
U3 receives £40
U4 receives £10

and £50 is still available for allocation to S1-n, M1-n, L1-n using the same principles as above.

Thanks

 

[Intermediate]

Hmm... In describing the problem I may have cracked it myself!!! I'll try a few ideas and see what happens.

Thanks

 

[cyrilbrd]

Qui Scribit Bis Legit

keep us posted though.

 

[Intermediate]

Yep... it seems to work. I've added a few more control rows. It now looks like this and seems to work:

01) MONTH: | JAN |
02) Income (X): | £250 |
03) Total Urgent Shortfalls (Ut): | £200 | <-- This is found using =SUMIFS() to search through all of the funds
04) Total Short Term Shortfalls (St): | £100 |<-- This is found using =SUMIFS() to search through all of the funds
05) Total Mid Term Shortfalls (Mt): | £2,050 |<-- This is found using =SUMIFS() to search through all of the funds
06) Total Long Term Shortfalls (Lt): | £100,000 |<-- This is found using =SUMIFS() to search through all of the funds
07) Allocations from (X) towards (Ut) = (Ua): | £200 | <-- This is found by =IF(Ut=0,0,MIN(X,Ut))
08) Allocations from (X) towards (St) = (Sa): | £50 | <-- This is found by =IF(St=0,0,MIN(X-Ut,St))

and so on... This establishes the size of the fund available to each category U,S,M and L.

The £200 from (Ua) can them be divided pro rata between all of the U funds U1,U2,U3 etc.