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. |
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. |
All times are GMT +1. The time now is 03:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com