Automate cost allocation

Excel Help for Automate Cost Allocation in Automating Data Analysis Tasks

Automate Cost Allocation

Rate this: (3/5 from 1 vote)

	Hi everyone! I need to allocate costs into a table according to allocation rules I have in another sheet. Hopefully someone will be able to help me. The information I have has the following format: Activity: XXX Service: YYY Cost Pool 1: $$$$ Cost Pool 2: $$$$ Cost Pool N: $$$$$ (with every activity/service combination) In a separete table I have the allocation table I need to follow in the following format: Activity: XXX Service: YYY Cost Pool: 1 Allocate to driver: A124 (and so forth for every combination) My goal is to create a sheet with Activity/Service/Driver that will give me the $$$$ per driver. i.e. Activity: XXX Service: YYY Driver A124: $$$ Driver N: $$$ I've attached a small version of what the sheets in Excel will look like and hopefully it will give you a better idea of what I need to do. Here are some more specifics: 1. The number of pools and drivers are fix and always arrange in the same way, therefore it is possible to read the table by column position rather than header. 2. The program needs read the table and given the combination ActivityID/Service SegementID/Pool allocate to 1 of the drivers according to the Distribution rule. 3. This can't be done as a "fix" distribution because the number of rows can change in the future. Basically, the tool will search for the combinations in the white boxes and "distribute" or copy according to the rule in the yellow boxes. THANKS! Excel Spreadsheet:   A B C D E F G 1 Info I have  Info I have  Info I have  Info I have  Distribute Here  Distribute Here  Distribute Here 2 Activity ID  ServiceSegment_ID   Pool 1  Pool 2  Driver 1012  Driver 1014  Driver 1015 3  AA129-AC  SS520-AS 100          4  AA129-AC SS521-AS    150        5  AA130-AC SS520-AS  50  750        6               7  For every combination of Activity/ServSeg/Pool corresponds 1 Driver           The distribution rule spreadsheet loks like this Excel Spreadsheet:   A B C D 1  Activity ID   ServiceSegment_ID  Pool Driver  2 AA129-AC  SS520-AS  1  1012  3  AA129-AC SS521-AS  2  1015  4  AA130-AC SS520-AS  1  1014  5  AA130-AC SS520-AS  2  1012  6         7         THANKS!!!!!
	Posted by Mlewit1 on 29 Jun 2007

There are currently no replies to the "Automate cost allocation" topic of the Excel Help Forum for Automating Data Analysis Tasks. Post Reply