Worksheet design: How to allocate costs to plants?
 

Hi,

I'm trying to find a way to allocate administrative costs to plants. I have
a worksheet set up to do it, but it's complicated and doesn't handle adding
new accounts gracefully. Here's a simplified example of what I'm trying to do:

Take all of the costs in the Admin department:

Dept Acct Cost
Admin Labor 100
Admin Supplies 200
Admin Travel 300

And allocate them to the following plants:

Dept Pct
Plant1 25%
Plant2 35%
Plant3 40%

Like this:

Dept Acct Cost
Plant1 Labor 25
Plant1 Supplies 50
Plant1 Travel 75

Dept Acct Cost
Plant2 Labor 35
Plant2 Supplies 70
Plant2 Travel 105

Dept Acct Cost
Plant3 Labor 40
Plant3 Supplies 80
Plant3 Travel 120

The departments from which the costs are being allocated (Admin in this
example) are fairly stable. The number of Accts (Labor, Supplies, Travel)
change each month.The percents also change each month.


I'd really appreciate any help on how to set this up. We have lots of
accounts and plants and maintaining the worksheet I have is a pain.

Thanks!

David