Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 | |||
Please-please-HELP!!! 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 |