View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
vsoler vsoler is offline
external usenet poster
 
Posts: 79
Default 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.