Cost Driver calculation - Looking for formula/macro to calculate manpower cost allocation

I am working on calculating the cost drivers for my organisation.

We have 19 “Departments”, 11 “Head of the Departments” and around 1000+ “Employees”.

Due to Job rotation, Supervisory, Internal Expertise etc staff are often pulled / allocated to other department out of their parent department.

Form the staff attendance software we could get the monthly data of each staff worked in which department.

I have attached a sample database and my requirements.

I would appreciate if someone could help me getting the formula in the highlighted range of the attached worksheet.

Regards

Swastik

What you have attached is far from a database, more of a report layout.

I don't understand what you mean by those departments in the lower table.  It cannpt come to 570,000 when you only have A, B and C in the top table.

Try explaining how one line of that top table should be calculated, in English terms.

I think the other responder was confused (as I was) because your target total did not include Mssrs. A, B, and C. So your total on line 35 doesn't include lines 17-19.

My solution (attached) cheats a bit insofar as I created a table off to the side that calculates the breakdown of each employee's cost by department.  This information can be used in any number of ways.

You can do the cost allocation for individual employees via formulas, as I've done for Mssrs. A, B, and C in the attachment.  But it's not a feasible solution to use this approach if you have 1000+ employees as you indicated.

Hope this helps.

bb