Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,358
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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
Please-please-HELP!!! 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


All times are GMT +1. The time now is 03:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"