I need to determine how many new sales reps to hire based on an estimated number of future clients in Excel

Question

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 every 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/16ihO__kDMw_uMGJpBDV9Ba0Mw2b40fx2/view?usp=sharing

Current Table model

Answer 1

I was able to build a formula that I think does what you need. They key was to number the quarters sequentially, then use the MOD() function.

The formula I wrote looks like this:

=IF(Current_Quarter > (Hire_Quarter + Training_Qtrs - 1), IF(MOD(Current_Quarter - (Hire_Quarter + Training_Qtrs - 1), Sales_Cycle) = 0, Hire_Qty, ""), "")

• The first condition of the if statement is checking if we are past the training period
  • Current_Quarter is a counter, which equals 1 in the first column. In your spreadsheet, this is 2019.5
  • Hire_Quarter is the sequential quarter when the rep was hired
  • Training_Qtrs is the number of quarters it takes to train. In your example, this is 2
• If we are out of the training period, this condition is TRUE, and we use the MOD() function to know if a sales rep has generated a client in the current quarter
  • A sales rep generates a client every third quarter after their training is done (this value is held in Sales_Cycle)
  • So we take the current quarter and subtract the training period, then check if the result is a multiple of Sales_Cycle. MOD() returns zero when the first argument is a multiple of the second argument.
  • If it is, we return the number of sales reps that were hired in this row, Hire_Qty

I structured the spreadsheet to match yours, and put this formula in every row. Here is a screenshot of what it looks like:

I used FormulaChop to generate this example formula (Full Disclosure: I wrote FormulaChop). Here is a screenshot of the output. Here is a link to download the spreadsheet I wrote.