[NCnc2]

I need help with both manual calculations & excel problem setup. My Boss wants me to setup an input program in excel that would output a solution. I need to input an expected average reimbursement rate, but the program needs to output two information: the base Reimbursement Rate AND the Time-Half Reimbursement Rate, given a total hours of 60,000 as an example ( or any other number of hours).

CASE OR SITUATION:
1) I will bill client a base rate and an overtime rate.
2) I pay the employees a base rate and an overtime rate.
3) My current Billing Rate is $27 regular hours, and $40.5 in overtime
4) Our average payroll cost, based on the total hours worked is $30

DESIRE OUTCOME
1) I need to input an expected average billing rate per hour. For example $38
2) The program should tell me the split between a base and overtime billing rate. Meaning that I will input one number that is the average expected billing rate, and the program would tell me how much should be my base billing and my overtime billing.
If you need additional information, please let me know.

Thank you for any suggestions.

[Anonymous]

Edit: I'm not sure what you're asking is possible. It seems like your trying to solve for 2 unique variables in a single equation. You would have to pick a rate for the base billing or for the overtime in order to know what the other should be in order to get to the desired average.

Does that make sense?

[NCnc2]

@eMeRGeD:

Yes indeed, you understood my point perfectly. I have had sleepless nights trying to device a way to do it with excel, since every other way would not work for me manually. I was hoping that some of you guys that are really smart out there could find a way around the problem.

Regards

[Anonymous]

With 2 unknown variables there are infinite possibilities for what the base rate and overtime rate could be.

Let's say the average is your $38. Also let's say 100 hours are base and 50 hours are overtime.

$38 = (100x + 50y)/150

You can pick ANY base rate (> or = to 0) and there will ALWAYS be an overtime rate that brings you to an average of $38. You'll need to determine one of the rates in order for this to work. For instance, you could find out the OT rate if your base is $27 and you want the average to be $38 and there are x number of hours for each.

[Ian]

Only way it could work is if your overtime rate is truly a derived value such as time and a half. Then your second variable becomes your first variable with a coefficient attached and eMeRGeD’s example becomes solvable:

$38 = [100x + 50(1.5x)]/150

[Anonymous]

^ Absolutely this. Thanks for pointing that out @Ian

[Anonymous]

Take a look at my calculation and excel set up and let me know if this would work. I have a screenshot here as its too much to type

Or try this: https://ibb.co/gkhXEy

[Anonymous]

I came up with *a* solution, but I'm definitely no math wiz.

38 = 27X + (1 – X) * 40.5
38 = 27X + 40.5 – 40.5X
13.5X = 2.5
X = 0.185 x 60 minutes = 11.11 minutes regular time @ 27/hr
60 – 11.11 = 48.89 minutes overtime @ 40.5/hr

Check:
(11.11/60) x 27 = $5
(48.89/60) x 40.5 = $33
5 + 33 = $38

[TooOld]

@Bobby:
Thank you for the time you put into that calculation; it is appreciated. I will be reviewing that calculation during my 4th of July break.

@Calvinus:
Your calculations is interesting; I will be reviewing it later. Thank you for taking the time to contribute; it is appreciated.

@eMeGReD:
Thank you for the expanded explanation. Thank you for your contribution, and thank you for staying engaged.

@Ian:
Thank you for your contribution.

Sincerely,

[ohiocpacma]

I don't see how you would do that without knowing the ot/reg hours split. The simplest solution I could think of is:
Base Rate = Average Rate/(1-OT%)+1.5*OT%)
Where OT % = Expected Overtime hours/ Total Hours
OT Rate = 1.5 x Base Rate

Therefore, you would need to know two inputs for this to work:
Average Wage (which you said you know)
OT% (Sounds like this is unknown to you)

[TooOld]

@OhioCPACMA:
Based on our prior records, the OT was 25.76% of the total hours worked. However, there are two challenges that I faced when taking this approach.

1) The future OT hours is not known; but I can work off my historical records to get some ideas.
2) The funny thing that I found was that even though the OT hours was about 26% of the total hours, its contribution to the total revenue was greater than 26%. This makes sense to me now because of the fact that the OT is 1.5.

***One fun fact that I have noticed also is that we used to think that there was a linear relationship between wages and billings. Meaning that if we are paying out a lot of overtime in wages, even though we are billing equal number of hours in overtime, our expectation was that we would be losing out…particularly when the markup is so thin. But to my surprise, that was not the case. I found out that the NET absolute dollar amount is more favorable to the company. So, we now push our customers for more overtime.

Regards

[Anonymous]

@TooOld:

Please let me know if the solution I posted is not correct… If it's not then I might have misunderstood the question.

Also, if you have good historical information, you might want to consider running a Regression analysis and get the equation of the line and use that for your calculations going forward.

[TooOld]

@Bobby:

I ran some tests on your equation today and I do believe your equation is promising. I like it a lot. My associate is out town for the 4th of July, but will be back in the office on Monday. Together, he and I, will consider every angle pertaining to the question and your equation as we make our way to the client. I will definitely let you know how it works out. As for the application of regression analysis, it is something that I have thought about; but because I have not run one since my college days, I would have to get a crash course at some point.

Thank you Bobby….I will give you feedback on Wednesday. Monday and Tuesday we will be working on this problem in our flight to meet with a client (the client that is central to this problem).

Regards

[Anonymous]

Assuming an 8-hour workday plus 2 hours overtime (i.e. 20% overtime), just dividing the desired rate by 1.1 should give a rough estimate.

Example: 38/1.1 = 34.55/hr regular, 51.82 overtime

Or using your historical rate:

38 = (.7424)X + (.2576)*1.5X
38 = 1.1288X
X = 33.66 regular, 50.50 overtime

[NCnc2]

@Calvinus:

Your last suggestion is simple and elegant and it is something that I can deploy and work on during my meeting. This estimation can be done on a napkin.

I am curious about how you initially came into the 1.1 from a 20% in your example. However, when I looked at your calculation using my historical data, I see how you got the 1.1.

Question: Did you derive the 1.1 from any other way besides the historical data that I provided?

I will appreciate your explanation.

Regards,

[Anonymous]

Yes, it's the same equation but plugging in .8 and .2 instead. Good luck!

[Author]

Replies