If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#1




Complex cost allocation formula
Hello everyone,
I am faced to an accounting problem that has emerged at the time of the 2009 budgeting process. I prepare the yearly budget in an Excel table that has 3 columns: the first two are codes, the last one is a figure. Account CostCenter Amount 1 a 12 1 b 13 1 c 8 2 a 21 2 a 3 2 c 7 3 a 12 3 b 4 Then, my budget goes through an allocation or assignment process to end users. In the example, the total cost of CostCenter a is allocated in 30% to User X and in 70% to user Z. CostCenter User PCT a X 30% a Z 70% b Y 55% b Z 45% c X 15% c Y 85% Eventually, I want to get a table like the one below, where the 4.8 is calculated as 12*30%+8*15% Account User Amount2 1 X 4,8 1 Y 13,95 1 Z 14,25 2 X 8,25 2 Y 5,95 2 Z 16,8 3 X 3,6 3 Y 2,2 3 Z 10,2 I can get the results I want by using an intermediate auxiliary table. No problem here. However, I am interested in obtaining directly my Amount2 column without intermediate calculations. I am ready to accept array formulas if needed. Can anybody help? Thank you very much. 
Ads 
#2




Complex cost allocation formula
The way I see it, you will need an intermediary table. This table, however,
can be automated, and hidden. This solution is also assuming that in your 'final' table, you are keying in the account and the User, and just need the Amount 2 to populate. I can post it if you like. But won't if my assumptions are incorrect.  John C "vsoler" wrote: > Hello everyone, > > I am faced to an accounting problem that has emerged at the time of > the 2009 budgeting process. > > I prepare the yearly budget in an Excel table that has 3 columns: the > first two are codes, the last one is a figure. > > Account CostCenter Amount > 1 a 12 > 1 b 13 > 1 c 8 > 2 a 21 > 2 a 3 > 2 c 7 > 3 a 12 > 3 b 4 > > Then, my budget goes through an allocation or assignment process to > end users. In the example, the total cost of CostCenter a is allocated > in 30% to User X and in 70% to user Z. > > CostCenter User PCT > a X 30% > a Z 70% > b Y 55% > b Z 45% > c X 15% > c Y 85% > > Eventually, I want to get a table like the one below, where the 4.8 is > calculated as 12*30%+8*15% > > Account User Amount2 > 1 X 4,8 > 1 Y 13,95 > 1 Z 14,25 > 2 X 8,25 > 2 Y 5,95 > 2 Z 16,8 > 3 X 3,6 > 3 Y 2,2 > 3 Z 10,2 > > I can get the results I want by using an intermediate auxiliary table. > No problem here. > However, I am interested in obtaining directly my Amount2 column > without intermediate calculations. I am ready to accept array formulas > if needed. > > Can anybody help? > > Thank you very much. > 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Allocation formula  [email protected]  Excel Worksheet Functions  1  January 26th 08 12:00 PM 
Somewhat Complex: Allocation of Payments  ryguy7272  Excel Worksheet Functions  7  January 21st 08 07:47 PM 
PleasepleaseHELP!!! Need to resolve this  Allocation Formula  Chunkey Pandey  New Users to Excel  1  November 25th 06 09:40 PM 
Conditional Cost allocation  CotoJoe  Excel Discussion (Misc queries)  0  September 14th 05 08:23 PM 
Serial number allocation  Array formula  Space Ape  Excel Worksheet Functions  0  July 3rd 05 07:32 AM 