re-allocation problem
 

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 Excel 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. 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)

Even better, 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 I can use Solver for this or how to net
out the redundant money movement, I will be 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:
From acct/Amount/To acct
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

One idea is to establish a "sweep" account for each client: in your
example call it 2899. The purpose of the sweep account is to
accommodate the client's changes to percentages of assets, and the net
balance of the xx99 account is always zero.

Using your sample data I mocked up this data:
Account numbers from 2800 to 2812 in A1:A9
Current dollar amounts ($500 each) in B1:B9
Cell B10: =SUM(B1:B9)
Client's desired asset percentages in C1:C9
Cell D1: =C1*$B$10 ... copy and paste from D1:D9
Cell D10: =SUM(D1:D9)
Cell E1: =D1-B1 ... copy and paste from E1:E10

A positive number in the E1:E9 range indicates the account on that line
has funds added to it from the "Sweep" account. A negative number
indicates the account on this line has funds removed to augment the
sweep account.

The advantage is the ease of transfers: the debits and credits are much
easier to calculate and are more intuitive.



Sienayr wrote:
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 Excel 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. 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)

Even better, 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 I can use Solver for this or how to
net
out the redundant money movement, I will be 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:
From acct/Amount/To acct
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