Payroll model.

[John Sitka]

Hi,

There are four pay types

standard
shiftpremium
doubletime
doubletimepremium

each hour a person books can be one of these types

Now when the payroll is calculated once a week the system
needs additional to be spoon fed to know which hours are overtime
so it has an additional set of overtime pay types

standardOT
shiftpremiumOT
doubletimeOT
doubletimepremiumOT

Obviously you can't record these types during the week because they are only meaningful after
the results are in for the whole week.

So as an example

an individuals hours for the week look like this

12 standard
12 shiftpremium
12 doubletime
12 doubletimepremium

They have worked 48 hours this week. If overtime kicks in after 40 hours.
they need to have 8 hours distributed to these codes

standardOT
shiftpremiumOT
doubletimeOT
doubletimepremiumOT

What approach could be used to make sense? it seems that there just is not enough information.
Maybe a bucket analogy. LIFO the last (in terms of time line) type(s) booked would be used as the
type for the overtime. Does that makes sense, could it be argued that the company is overpaying
or underpaying, would you be satifisfied, with that kind of system?

 

[Mark Wilden]

What approach could be used to make sense? it seems that there just is not
enough information.
Maybe a bucket analogy. LIFO the last (in terms of time line) type(s)
booked would be used as the
type for the overtime. Does that makes sense, could it be argued that the
company is overpaying
or underpaying, would you be satifisfied, with that kind of system?

This isn't a C# or even a programming question - it's a business question.

///ark

 

[gary]

Hello John,

How is the time recorded? Is it by time clock? If so then the rate
should just be matched to the clocked time. If it is just a number of
hours then then the rates should be apportioned to the relevant time
ranges or if it is just a total number of hours then the rates should
just be apportioned pro rata. I'm afraid your scenario raises more
questions than it answers.

Regards,
Gary
http://www.garyshort.org/

 

[John Sitka]

Thanks, I didn't notice.

This isn't a C# or even a programming question - it's a business question.

///ark

 

[John Sitka]

Thanks Gary,
Each record has a double value (8.0) with a flag to identify which type.The flag are manually set at time of record as before the work is begun it is known that
the shift is of a certain type.

A typical day for an individual
would look like
Job-activitycode-hours-flag
A1-154-4-standard
A1-158-2-standard
A2-154-4-standard.

It's the distribution of the overtime I can't get even the simplest of logic worked out for.
I realise there is not enough here but being stuck I know lots of people here have integrated
payroll applications and probably have a huge amount of good ideas.

 

[Samuel R. Neff]

Store the hours worked as one field:

UserWorkWeeks
----------------------------
UserWorkWeekId
UserId
WeekStartDate
HoursWorked

And then use a weekly process to do payroll which can do the
appropriate calculations and then store the calculated values back in
the database in a separate table:

CalculatedPayroll
---------------------------
UserWorkWeekId
PayTimeId
Hours
Rate


Accouting/Financial/Payroll applications are a rare breed where it's
often appropriate or necessary to break some fundamental DB rules in
favor of auditing, history, and performance (i.e., normally you never
store calculated values in a database).

HTH,

Sam

 

[PS]

Thanks Gary,
Each record has a double value (8.0) with a flag to identify which type.
The flag are manually set at time of record as before the work is begun it
is known that
the shift is of a certain type.

A typical day for an individual
would look like
Job-activitycode-hours-flag
A1-154-4-standard
A1-158-2-standard
A2-154-4-standard.

It's the distribution of the overtime I can't get even the simplest of
logic worked out for.
I realise there is not enough here but being stuck I know lots of people
here have integrated
payroll applications and probably have a huge amount of good ideas.

Assuming you want to distribute the overtime evenly then the amount of
overtime would be proportional based on the total hours for the week, e.g 20
Standard and 30 Doubletime would mean that of the 10 hours of overtime 4
hours would be StandardOT (20/50 x 10) and 6 hours would be doubletimeOT
(30/50 * 10).

Note that this "even distribution" of overtime is actually the law in some
states. For example in New York State the DOL law states that the overtime
rate be no less than 1 1/2 times the average rate paid for the week. For
example if the standard rate is $10/hour and the employee again worked 20
standard and 30 doubletime hours the employees average rate is $800 / 50
hours = $16/hour. Therefore OT on the 10 hours has to be paid at a minimum
of $24/hour.

PS

 

[John Sitka]

Thank you PS.
Great advice, and I now see two methodologies

1.) The stack method where I could sequentially count all types up to 40 hours as not overtime from the bottom of the
stack upwards. Then the types over and above that 40 hours retain their type but change to the overtime case (OT) of that type

2.)The proportional method you just described.

I need to ask the business about this base assumption you mentioned...

Assuming you want to distribute the overtime evenly then the amount of overtime would be proportional based on the total hours for
the week

Much appreciated.

 

[John Sitka]

Thank you Samuel
I would fully intend to do this.
But that information will truely be retained by the program
which generates the EFT (payroll file transfer) to the bank.
So the calculated values are retainable durable in a number of ways.

But I don't see where your suggestion maintains the type of pay

What that EFT program dosen't do however is help with theIf I was to enter 5 consecutive 10 hour days in this Payroll program.
it would not mark the fifth day as overtime, but it would require it coded
as such when generating the pay for that week. The ERP system on the other hand
would code that fifth day entry appropriately but the ERP system does not have a workable interface
to record thousands of time entries a day. However I built a program that makes that kind of entry
possible and then posts to the ERP system. I can then batch the coded aggregates back to the payroll
program.

It's the "appropriate calculations" for the aggregates I don't know, even what they are called.
With the advice given here, I have a clearer picture of what is involved.

I realize now I'm struggling with two different interpretations of overtime.
Daily overtime OR weekly averaged overtime and I fear this company has somehow got
a defacto hybrid of that in play. 10 hours working one day a week does not grant you
2 hours overtime but 10,8,8,8,8 days would. Obviously this is straight forward.

But for example

Monday 10 standard
Tuesday 8 standard
Wednesday 8 standard
Thursday 8 standard
Friday 8 doubletime

can be interpreted three ways

(overfill)
34 standard
6 doubletime
2 doubletimeOT

OR

(daily)
32 standard
2 standardOT
8 doubletime

OR

(proportional)
32 standard
8 doubletime
1.6 hours standardOT
..4 hours doubletimeOT


So you need either a proportional distribution or an overfill condition. With the overfill condition.
each separate time unit needs individual investigation to determine if it came before or after the overfill
condition was reached.