I have an Accounting question related to cost allocation..?

Question

Two allocation bases have been proposed - salary and headcount (number of employees). Personnel costs are expected to be $1,000,000. The following data relate to the allocation:

(Since this is a spreadsheet imagine (or draw) a rectangle 3 cells wide and two cells high.)

Top left: Blank, Top Middle: Financial Planning, Top Left: Business Consulting.

Next line- Left: Salaries, Middle: $10,000,000, Right: $5,000,000

Bottom Line- Left: Headcount, Middle: 150 persons, Right: 50 Persons.

Required work from me: Prepare a schedule showing the allocations to the two divisions using each allocation base.

Now I have another block of spreadsheet cells 6 across and 3 high.

Starting on the left: Allocation base Proportion, (then to the right), Amount to be Allocated:, Financial Planning Amount, Amount, Business Consulting Proportion, Amount.
Next line is labeled Salaries and the five cells to the right are blank. Bottom line is labeled Headcount and the five cells to the right are blank.

Amount to Be Allocated for both Salaries and Headcount are supposed to be a $ amount. Two cells over under "Amount" both lines up and down should be $ amounts. Both cells below Financial Planning Amount and Business Consulting Proportion are supposed to be % amounts.

If anyone thinks they know how to do this, but would like to see the excel spreadsheet just tell me and leave your email address..I'd apreciate the help on this one.

Thanks

Answer 1

I'll deal with the logic of the allocations first, then I'll fill in the cells.

Total salaries for both divisions are $15m
Total headcount for both divisions are 200
So if you use salaries as a base for the allocation, Financial Planning (FP) should bear 10/15 of the $1m Personnel costs and Business Consulting (BC) shd bear 5/15 of $1m

If you use headcount as a base, FP shd bear 150/200 and BC shd bear 50/200.

Coming to the spreadsheet. You have 3 rows and 6 columns. You have filled the 1st row with headings. I'll talk about only the 2nd and 3rd rows. Here goes:

2nd row:
Col. 1 you've filled in "Salaries"
Col. 2 fill in $1m (personnel costs to be allocated)
Col. 3 fill in 66.67% (10/15*100)
Col. 4 fill in $666,666.67 (66.67% of $1m)
Col. 5 fill in 33.33% (5/15*100)
Col. 6 fill in $333,333.33

3rd row:
Col. 1 you've filled in "Headcount"
Col. 2 fill in $1m (personnel costs to be allocated)
Col. 3 fill in 75% (150/200*100)
Col. 4 fill in $750,000
Col. 5 fill in 25% (50/200*100)
Col. 6 fill in $250,000

Hope I understood the question correctly and this is what you want. I tried my best!

Answer 2

This is a good question, and one that made me curious for a very long time.