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

Intermediate

New Member
Joined
Apr 27, 2012
Messages
13
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

Well-known Member
Joined
Feb 2, 2012
Messages
3,845
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

New Member
Joined
Apr 27, 2012
Messages
13
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 (y)= £200

Each account receives (X)*(Un/(y))

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

New Member
Joined
Apr 27, 2012
Messages
13
Hmm... In describing the problem I may have cracked it myself!!! I'll try a few ideas and see what happens.

Thanks
 

Intermediate

New Member
Joined
Apr 27, 2012
Messages
13
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.
 

Forum statistics

Threads
1,081,840
Messages
5,361,618
Members
400,642
Latest member
tekster23

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top