Overtime salary formula problem

[jbmedic]

I need to be able to figure out how much it would cost if employees were working 48 hour work weeks. total hours is in I12 and hourly wage is f18. Attached is the worksheet.
Thank you for your help in advance!

[mikeTRON]

So I guess I have a few questions for you.

40hrs per week needs 4.2 employees and 48hrs per week needs 3.5.... How do you plan on dealing with the decimals? You could either let people go BEFORE they get to 48 hours, which I would find ideal, or you can round up to 4 and 5 people needed.

Also, Are we assuming 150% overtime pay?
What cells are you wanting populated with formulas because it seems J12 and K12 already have some calculated biweekly and annual cost.

Cost Calculation.xlsx

[6StringJazzer]

Can you staff fractional employees? That is, you show that with 48-hours weeks, you need 3.5 employees. Can you have 1/2 employee, or do you have to pay for 4 full employees?

The way you have your calculations, with fractional employees and hourly multipliers, the cost of 48-hour weeks is exactly the same as 40-hour weeks. All your costs are loaded to hourly rates, not headcount, so the number are based on total hours, not workweek. If you look at how J12 is calculated you will see what I mean. It only looks at total hours worked, not workweek or employee headcount.

However, calculating employee cost is more complicated than what you have shown. For example, you have a benefit multiplier times the hourly rate. But benefits should probably be allocated to the first 40 hours, and not continue to be added for overtime hours (e.g., health insurance does not cost more if an employee works overtime). Also, are they getting time-and-a-half for overtime?

[jbmedic]

The decimals would be part time employees. For example a 168 hour week would have 3 full time employees working 48 hours a week and one part time employee working 24 hours. The decimal would basically be part time employee at regular time rate. The most important calculation is the bi-weekly and annual cost. The number of employees was mostly part of me trying to figure out a formula with no success.

Yes overtime is calculated 150%
J12 and K12 are the calculated costs based on a 40 hour work week so that is completed.
Thank you for your interest and reply.

[jbmedic]

6 string jazzer,
You are right! The benefits would be based on the first 40. Taxes and workers comp are based on total hours worked. Overtime needs to be calculated at 150%.
I tried to simplify the formula I needed help with from a bigger project. The fractional portion of the # of employees would be covered at the regular rate or 100% of the pay.
Hopefully this explains thing a little better. Thanks for your reply.

[jbmedic]

Problem Solved thank you for your input!

[6StringJazzer]

Glad to hear it's worked out. If your question has been resolved, please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."