I have a problem I'm trying to analyze. We have to do an analysis on how many new sales reps to hire based on the number of expected onboarded clients. The dynamic part of the problem is that when we hire a sales rep, they have a 2 quarter training period, after which they are able to make a client which takes 3 quarters, and there is an implementation period of 1 quarter, during which the sales rep can still make another client. I am trying to come up with a formula that determines the number of sales reps to be hired and when they need to be hired. The training period only happens once per rep.
I have made a spreadsheet that models my current thinking on this, but this doesn't mean there are other ways it could be solved.
I have manually done the first 5 years based on the current estimate, and colored it to help give an idea as to what kind of formula I need. Red indicates a training period over 2 quarters which happens only once for any sales agent, any of the blue colors (I used several blues to help separate things) means that the sales agent is working on establishing a client, and green means that they have finished establishing the client. If I had some sort of IF formula that had a binary result, 0 or 1, and still matched this table then I'd be set. Here's the updated link & picture! Please let me know if any of this is confusing and I will try to clarify!
https://drive.google.com/file/d/16ih...ew?usp=sharing
