Wages problem...need help!

Cianos

Hi folks,
Was hoping to get a bit of help with this problem.

Here's the scenario;

Employee works with boss under 2 different circumstances. Circumstance 1 is where the boss gives the employee jobs for which the employee is paid a fixed rate per hour...e.g, €10/hour.

Circumstance 2 is where the employee seeks out a job and earns a % of the earnings from that job. (For these jobs, the employee is rewarded with a higher pay rate since the earnings are based on his own initiative). For these jobs, the rate of pay is determined by the gross amount generated on a week to week basis. The reference point for the earnings is the following;

At the start of the week the employee earns 70% of gross earnings, but at gross earnings of €1400 in a week, the employee deserves 60%, or €840.

So for example, if the employee has gotten 10 hours of work straight from the boss but also finds €1400 worth of business in a week, his pay is €10x10 = €100 + (€1400 x 60% = €840) = €940.

To make it fair, the employee will earn a lower % the more business he generates in a week, because less work will be required to find that work.

So, the % earnings of the employee should start at 70%, but at the point where €1400 gross has amounted, the employee earns 60%. So every euro earned brings the 70% closer to 60% after which point every subsequent euro will earn the employee 60% (60 cent).

What I'm looking for is a way to calculate this based on a weekly earnings basis. ie, if employee has generated €997 worth of business, how far down the scale from 70% should that be calculated so as to know how much is owed to the boss and the employee?

Hope the above is clear...and thanks v.much for any help given!

MathsManiac

A quick glance at some of your other recent posts leads me to believe you're a business person with a real question, rather than a student with a homework assignment. Hence, I'm assuming the rules allow me to actually answer the question, rather than just give hints!

Just to clarify: are you saying that as soon as any money is brought in by the employee, the 70% is to immediately start reducing in a uniform fashion towards the 60%, reaching the 60% at exactly the point where the earnings are €1400?

If that is so, then the employee's proportion of gross earnings is represented by a straight line from (0,0.7) to (1400,0.6). That is, y = 0.7 - (1/14000)*x. Multiplying this by the earnings gives the equation you need:

Y = 0.7*x - (1/14000)*x^2, where x is the weekly earnings brought in, and Y is the employee's cut of those earnings.

x = €997 gives Y = €626.90

(Of course you've to add on the pay for the circumstance 1 work.)

Cianos

Brilliant, thanks very much that seems to work!

Out of interest, can you explain why when you use the formula to calculate, say, earnings of €10,000, the employee earns -142.86 rather than a profit from the earnings? With the spreadsheet I can just add an 'if' statement to say that for at and anything over €1400 the employee earns 60% thereafter, but just curious to know.

Thanks again

edit: and yeah, this is a real life problem rather than part of an assignment

red_fox

That's because the formula accounts for the change in percentage from €0 -> €1400 but the formula 'thinks' the change continues even after that (so at 2800 the employee earns 50%). In practice you'll need to seperate into two cases, 0 <= x <= 1400 and x > 1400, a single nice formula won't give the answer (the function is not 'smooth' - there's a shape change at 1400)

MathsManiac

Indeed, it's just as red_fox has explained.

And here's another way of looking at it. Look at the attached graph.
wages.gif

The green curve represents the formula I gave you. The red line is the amount the employee would get from a straight 60% cut.

The line and the curve meet when x = 1400, (reflecting the fact that both give the same amount of money in that case).

You want to give the guy the green amount up to x=1400 and the red one afterwards.

The green one is a curve that continues to bend downwards as x increases. (It's called a parabola, or "quadratic curve", as it involves "x squared".) See this version of the graph, which shows higher x-values:
wages2.gif

Obviously, as you noticed, you wouldn't want to follow that green curve indefinitely. (Well, YOU might, but HE wouldn't!) In fact, it tops out at €4900, after which your employee starts losing money the more business he brings in! As you've already noticed, after €9800, you're on a great deal: not only does your employee get no cut at all, but he starts paying you for the privilege of drumming up business. (If he brings in €10K of business, you could toss him 14 hours of fixed rate work, and call it quits!)

Yes, you can solve it by using an "IF" statement in your spreadsheet. But you might also consider whether the calculation is too complicated a way of achieving what you want. Are you going to be able to satisfy the employee that you're doing the sums correctly?

An alternative that could follow similar principles would be to agree a tiered schedule of commission: Employee gets 70% of first €?? of business he brings in, 65% of next €?? of business, 60% of remainder. It would certainly be easier for him to understand.

Cianos

MathsManiac wrote: »

Indeed, it's just as red_fox has explained.

And here's another way of looking at it. Look at the attached graph.
wages.gif

The green curve represents the formula I gave you. The red line is the amount the employee would get from a straight 60% cut.

The line and the curve meet when x = 1400, (reflecting the fact that both give the same amount of money in that case).

You want to give the guy the green amount up to x=1400 and the red one afterwards.

The green one is a curve that continues to bend downwards as x increases. (It's called a parabola, or "quadratic curve", as it involves "x squared".) See this version of the graph, which shows higher x-values:
wages2.gif

Obviously, as you noticed, you wouldn't want to follow that green curve indefinitely. (Well, YOU might, but HE wouldn't!) In fact, it tops out at €4900, after which your employee starts losing money the more business he brings in! As you've already noticed, after €9800, you're on a great deal: not only does your employee get no cut at all, but he starts paying you for the privilege of drumming up business. (If he brings in €10K of business, you could toss him 14 hours of fixed rate work, and call it quits!)

Yes, you can solve it by using an "IF" statement in your spreadsheet. But you might also consider whether the calculation is too complicated a way of achieving what you want. Are you going to be able to satisfy the employee that you're doing the sums correctly?

An alternative that could follow similar principles would be to agree a tiered schedule of commission: Employee gets 70% of first €?? of business he brings in, 65% of next €?? of business, 60% of remainder. It would certainly be easier for him to understand.

Thanks very much for all of that, very nicely explained. The combination of the quadratic curve and the 'if' rule once it hits €1400 is agreed as fair by both parties, and since both will be able to see the spreadsheet should leave no room for arguments We had thought about doing a tiered approach, but the gaps in between can mean someone is up or down a significant amount of money. The curve allows it to be absolutely precise, which is nice.

Thanks again