A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Complex cost allocation formula



 
 
Thread Tools Display Modes
  #1  
Old July 31st 08, 07:59 AM posted to microsoft.public.excel.worksheet.functions
vsoler
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.
Ads
  #2  
Old July 31st 08, 02:46 PM posted to microsoft.public.excel.worksheet.functions
John C[_2_]
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.
>

 




Thread Tools
Display Modes

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

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 11:47 PM.


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