Home 
Search 
Today's Posts 
#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. 
#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. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Allocation formula  Excel Worksheet Functions  
Somewhat Complex: Allocation of Payments  Excel Worksheet Functions  
PleasepleaseHELP!!! Need to resolve this  Allocation Formula  New Users to Excel  
Conditional Cost allocation  Excel Discussion (Misc queries)  
Serial number allocation  Array formula  Excel Worksheet Functions 