S
sienayr
Greetings,
I am hoping someone out there may be able to give me some advice on a
problem I am working on.
I am using Access 2000.
Scenario:
A client has 5 accounts, which may or may not have a balance. The
client then provides new percentages that they would like each of the 5
accounts to end up with, based on the total balance of all 5 accounts
added together. (The number of accounts is variable; the example below
uses 9 accounts.)
In order to achieve those balances, money is moved back and forth
between the accounts. Cashing out all 5 accounts into a pot and then
reallocating the money based on the provided percentages is not an
option.
Currently a spreadsheet model calculates the necessary movement between
accounts, but the movement is redundant in several cases, where money
moves from account A to account B, then from account B back to account
A. I am trying to figure out a way to load the account numbers,
balances and desired end percentages into a table, then create the
transactions necessary to move the money between accounts in order to
achieve the desired end percentages. However, I would like to
eliminate redundancies; I would like to take the net amount of the
redundant transactions and only move the money in one direction. (See
example below)
[Ultimately I would like to find a way to achieve the desired end
percentages with the least number of transactions required to do so. I
believe Excel solver could do that, but that is a little beyond me.]
If anyone has any ideas on how to net out redundant movement I will be
very grateful.
thanks for your time,
Ryan
client request and account balances:
acct/current balance/desired percentage of total balance ($4,500)
2800 500.000 15.00%
2801 500.000 10.00%
2802 500.000 25.00%
2803 500.000 5.00%
2804 500.000 13.00%
2805 500.000 21.00%
2810 500.000 5.00%
2811 500.000 3.00%
2812 500.000 3.00%
transactions:
2800 50 2804
2800 125 2803
2800 25 2810
2800 65 2811
2800 105 2812
2800 25 2805
2800 15 2802
2801 50 2804
2801 125 2803
2801 25 2810
2801 65 2811
2801 105 2812
2801 25 2805
2801 15 2800
2801 15 2802
2802 75 2801
2802 50 2804
2802 125 2803
2802 25 2810
2802 65 2811
2802 105 2812
2802 25 2805
2802 15 2800
2803 75 2801
2803 50 2804
2803 25 2810
2803 65 2811
2803 105 2812
2803 25 2805
2803 15 2800
2803 15 2802
2804 75 2801
2804 125 2803
2804 25 2810
2804 65 2811
2804 105 2812
2804 25 2805
2804 15 2800
2804 15 2802
2805 75 2801
2805 50 2804
2805 125 2803
2805 25 2810
2805 65 2811
2805 105 2812
2805 15 2800
2805 15 2802
2810 75 2801
2810 50 2804
2810 125 2803
2810 65 2811
2810 105 2812
2810 25 2805
2810 15 2800
2810 15 2802
2811 75 2801
2811 50 2804
2811 125 2803
2811 25 2810
2811 105 2812
2811 25 2805
2811 15 2800
2811 15 2802
2812 75 2801
2812 50 2804
2812 125 2803
2812 25 2810
2812 65 2811
2812 25 2805
2812 15 2800
2812 15 2802
I am hoping someone out there may be able to give me some advice on a
problem I am working on.
I am using Access 2000.
Scenario:
A client has 5 accounts, which may or may not have a balance. The
client then provides new percentages that they would like each of the 5
accounts to end up with, based on the total balance of all 5 accounts
added together. (The number of accounts is variable; the example below
uses 9 accounts.)
In order to achieve those balances, money is moved back and forth
between the accounts. Cashing out all 5 accounts into a pot and then
reallocating the money based on the provided percentages is not an
option.
Currently a spreadsheet model calculates the necessary movement between
accounts, but the movement is redundant in several cases, where money
moves from account A to account B, then from account B back to account
A. I am trying to figure out a way to load the account numbers,
balances and desired end percentages into a table, then create the
transactions necessary to move the money between accounts in order to
achieve the desired end percentages. However, I would like to
eliminate redundancies; I would like to take the net amount of the
redundant transactions and only move the money in one direction. (See
example below)
[Ultimately I would like to find a way to achieve the desired end
percentages with the least number of transactions required to do so. I
believe Excel solver could do that, but that is a little beyond me.]
If anyone has any ideas on how to net out redundant movement I will be
very grateful.
thanks for your time,
Ryan
client request and account balances:
acct/current balance/desired percentage of total balance ($4,500)
2800 500.000 15.00%
2801 500.000 10.00%
2802 500.000 25.00%
2803 500.000 5.00%
2804 500.000 13.00%
2805 500.000 21.00%
2810 500.000 5.00%
2811 500.000 3.00%
2812 500.000 3.00%
transactions:
2800 75 2801
2800 50 2804
2800 125 2803
2800 25 2810
2800 65 2811
2800 105 2812
2800 25 2805
2800 15 2802
2801 50 2804
2801 125 2803
2801 25 2810
2801 65 2811
2801 105 2812
2801 25 2805
2801 15 2800
2801 15 2802
2802 75 2801
2802 50 2804
2802 125 2803
2802 25 2810
2802 65 2811
2802 105 2812
2802 25 2805
2802 15 2800
2803 75 2801
2803 50 2804
2803 25 2810
2803 65 2811
2803 105 2812
2803 25 2805
2803 15 2800
2803 15 2802
2804 75 2801
2804 125 2803
2804 25 2810
2804 65 2811
2804 105 2812
2804 25 2805
2804 15 2800
2804 15 2802
2805 75 2801
2805 50 2804
2805 125 2803
2805 25 2810
2805 65 2811
2805 105 2812
2805 15 2800
2805 15 2802
2810 75 2801
2810 50 2804
2810 125 2803
2810 65 2811
2810 105 2812
2810 25 2805
2810 15 2800
2810 15 2802
2811 75 2801
2811 50 2804
2811 125 2803
2811 25 2810
2811 105 2812
2811 25 2805
2811 15 2800
2811 15 2802
2812 75 2801
2812 50 2804
2812 125 2803
2812 25 2810
2812 65 2811
2812 25 2805
2812 15 2800
2812 15 2802