Differing pay rates for multiple employees in a job costing spreadsheet?

1. Differing pay rates for multiple employees in a job costing spreadsheet?

Sorry, new on the block so hopefully not breaking any rules already (!)

I'm a cost administrator for a construction company. I keep costing spreadsheets for each building project with tabs for employee costs, professional fees, suppliers etc

The employee costs are in a table with the date (week ending) heading the columns and the employees listed in rows, their weekly hours are inserted into that table.

Now however I've got a situation where some employees pay rates are staying the same, some are going up and some are going down, this is all happening on different dates for different employees. So this simplified table doesn't seem fit for purpose anymore.

I am racking my brains on how to do this simply but I'm struggling. Has anyone had to do this before? I imagine it's common but might be wrong!

Any help would be greatly appreciated!

2. Re: Differing pay rates for multiple employees in a job costing spreadsheet?

Hi and welcome to the forum

You probably need another three column table with columns for Employee, Date from which pay applies, Pay Rate
Then add information for each employee

In the table that calculates the cost for any week it will need a formula to interrogate the table mentioned above.

Upload the workbook you have and manually add some examples of what you expect to see for different weeks and no doubt we can put the formulae together.

3. Re: Differing pay rates for multiple employees in a job costing spreadsheet?

Thank you for that.

I think I've uploaded a worksheet - as I am using them now - but it's not very clear!

I've added to the employee tab when the pay rates change into cell notes and what they are changing to is noted at the end of the table for the two 'example' employees. Anything needs clarifying just let me know!

Thanks again for any help!

4. Re: Differing pay rates for multiple employees in a job costing spreadsheet?

Are you using Excel for Mac? If so, please add 'for Mac' to your profile.

5. Re: Differing pay rates for multiple employees in a job costing spreadsheet?

Thanks

You show 3 pay rates for Employee 1

Nowhere does it say what date the pay rates change
Your formula in D6 of the Summary is =(512.52+47.4+11.78)/41.5 to give some overall rate of £13.78

However it's not clear where the three amounts in the parentheses come from nor why you divide by 41.5. Please explain these numbers

Please explain how you calculate this stuff. If an employee gets paid a different rate at some future point in time then as I said earlier the system needs to know the date the rate changes

6. Re: Differing pay rates for multiple employees in a job costing spreadsheet?

Thanks for getting back to me.

I haven't been given the new pay rate etc yet so this is just an example of what I can expect.

Like I said in previous post, I have outline the dates the pay rates change in the cell notes in the employee tab (I'll give you the cells the notes are in: AC6 and BG6 for employee one and BU7 for employee 2).

Not actually sure where that formula in Summary tab D6 came from. At this point how pay rates etc are calculated isn't really where I'm needing help - its more in finding a way of setting out costs for weekly hours while pay rates are constantly changing for each employee at different times!

For example, at the moment this test sheet is only showing the total cost of hours worked for two employees at one pay rate each. Chances are as time goes on, there is going to be multiple pay rates for everyone.

Hope that makes sense!

7. Re: Differing pay rates for multiple employees in a job costing spreadsheet?

Although I don't completely understand, perhaps the following (based on Richard's suggestion in post #2) will help.
On the Employee sheet a table is added that shows the pay rate date ranges.
Name, Pay Rate and Start Date are manually filled.
End Date is populated using: =IF(B23=B22,D23,TODAY())
Hours are populated using: =SUMPRODUCT((A\$6:A\$16=B22)*(B\$5:CF\$5>=D22)*(B\$5:CF\$5<E22)*(B\$6:CF\$16))
Pay is populated using: =C22*F22
On the Summary sheet, the Values column is populated using: =SUMIFS(Employee!G\$22:G\$26,Employee!B\$22:B\$26,A6)
Let us know if you have any questions.

8. Re: Differing pay rates for multiple employees in a job costing spreadsheet?

Thank you for this. It is along the lines of what I was thinking, so it's good to know I'm on the right track (even if I have very little experience in all this!)

Going to give that a go, I think I'll only really know what's working when I try it out in practice.

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1