If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Complex cost allocation formula
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Complex cost allocation formula

#1
July 31st 08, 07:59 AM posted to microsoft.public.excel.worksheet.functions
 vsoler external usenet poster Posts: 79
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
July 31st 08, 02:46 PM posted to microsoft.public.excel.worksheet.functions
 John C[_2_] external usenet poster Posts: 1,358
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.
>

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Allocation formula [email protected] Excel Worksheet Functions 1 January 26th 08 11:00 AM Somewhat Complex: Allocation of Payments ryguy7272 Excel Worksheet Functions 7 January 21st 08 06:47 PM Please-please-HELP!!! Need to resolve this - Allocation Formula Chunkey Pandey New Users to Excel 1 November 25th 06 08: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 01:49 PM.