Time-Series Problem - Pay Rates vs. Bill Rates

%3CLINGO-SUB%20id%3D%22lingo-sub-1850868%22%20slang%3D%22en-US%22%3ETime-Series%20Problem%20-%20Pay%20Rates%20vs.%20Bill%20Rates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1850868%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%20Please%20forgive%20me%20if%20this%20is%20not%20the%20right%20location%2Fmedium%20to%20post%20this.%20I'm%20looking%20for%20a%20little%20direction%20on%20a%20complex%20(relatively%20speaking)%20problem%20I'm%20currently%20faced%20with.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20summarize%20-%20my%20business%20groups%20employees%20into%20%22buckets%22%20by%20bill%20rate%20%26amp%3B%20pay%20rate%2C%20from%201-10.%20Bucket%201%20is%20the%20lowest%20paid%20and%20has%20the%20lowest%20bill%20rate%2C%20and%20bucket%2010%20is%20the%20highest%20paid%20and%20has%20the%20highest%20bill%20rate.%20In%20addition%2C%20we%20measure%20company%20and%20personal%20efficiency%20by%20a%20ratio%20called%20%22effective%20multiplier%22%2C%20which%20is%20Net%20Revenue%2FDirect%20Labor%2C%20or%20in%20the%20individual's%20case%2C%20Bill%20Rate%2FPay%20Rate.%26nbsp%3BNaturally%2C%20each%20person's%20%22multiplier%22%20varies%2C%20but%20we%20aim%20to%20structure%20each%20rate%20such%20that%20the%20ratio%20stays%20right%20around%203.80.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%3A%20When%20does%20each%20employee%20need%20to%20be%20moved%20to%20the%20next%20highest%20%22bucket%22%20or%20bill%20rate%2C%20to%20stay%20at%20within%20a%203.70-3.85%20range%2C%20with%20a%20standard%204%25%20raise%20across%20the%20board%20every%20year%2C%20and%20no%20change%20in%20bill%20rates.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EHopefully%20that%20makes%20sense!%20For%20reference%2C%20I've%20included%20some%20data%20here%20and%20an%20example%20as%20an%20attachment.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1850868%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Regular Visitor

Hello! Please forgive me if this is not the right location/medium to post this. I'm looking for a little direction on a complex (relatively speaking) problem I'm currently faced with.

 

To summarize - my business groups employees into "buckets" by bill rate & pay rate, from 1-10. Bucket 1 is the lowest paid and has the lowest bill rate, and bucket 10 is the highest paid and has the highest bill rate. In addition, we measure company and personal efficiency by a ratio called "effective multiplier", which is Net Revenue/Direct Labor, or in the individual's case, Bill Rate/Pay Rate. Naturally, each person's "multiplier" varies, but we aim to structure each rate such that the ratio stays right around 3.80. 

The problem is: When does each employee need to be moved to the next highest "bucket" or bill rate, to stay at within a 3.70-3.85 range, with a standard 4% raise across the board every year, and no change in bill rates. 

Hopefully that makes sense! For reference, I've included some data here and an example as an attachment. 

0 Replies