Time-Series Problem - Pay Rates vs. Bill Rates

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. 

1 Reply
Yes you can do time series trend analysis in Excel. I have custom developed a template that can do a form of time series analysis segmentation automatically. Here is a post I wrote on this forum describing it, and, let me know if this is a technique your are looking to apply: https://techcommunity.microsoft.com/t5/excel/automatically-create-time-series-segments-in-excel/m-p/...