Мне нужно определить, сколько новых торговых представителей нужно нанять, исходя из предполагаемого количества будущих клиентов в Excel.

Question

Нам нужно провести анализ того, сколько новых торговых представителей нанять на основе ожидаемого количества привлеченных клиентов. Динамическая часть проблемы заключается в том, что когда мы нанимаем торгового представителя, у него есть двухквартальный период обучения, после которого он может привлекать клиента каждые три квартала, и есть период внедрения в один квартал, в течение которого торговый представитель все еще может привлечь другого клиента. Я пытаюсь придумать формулу, которая определяет количество торговых представителей, которых нужно нанять, и когда их нужно нанять. Период обучения происходит только один раз на представителя.

Я составил электронную таблицу, которая моделирует мои текущие мысли по этому поводу, но это не значит, что есть другие способы решения этой проблемы.

Я вручную сделал первые 5 лет на основе текущей оценки и раскрасил их, чтобы помочь понять, какая формула мне нужна. Красный цвет обозначает период обучения в течение 2 кварталов, который случается только один раз для любого торгового агента, любой из синих цветов (я использовал несколько синих цветов, чтобы разделить вещи) означает, что торговый агент работает над установлением клиента, а зеленый означает, что они завершили установление клиента. Если бы у меня была какая-то формула IF, которая имела бы двоичный результат, 0 или 1, и все еще соответствовала этой таблице, то я был бы готов. Вот обновленная ссылка и изображение! Пожалуйста, дайте мне знать, если что-то из этого сбивает с толку, и я постараюсь прояснить!

https://drive.google.com/file/d/16ihO__kDMw_uMGJpBDV9Ba0Mw2b40fx2/view?usp=sharing

Текущая модель таблицы

Answer 1

Мне удалось построить формулу, которая, как я думаю, делает то, что вам нужно. Ключевым моментом было последовательное нумерование кварталов, а затем использование функции MOD().

Формула, которую я написал, выглядит так:

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

• Первое условие оператора if — проверка того, прошли ли мы период обучения.
  • Current_Quarterэто счетчик, который равен 1в первом столбце. В вашей таблице это2019.5
  • Hire_Quarterэто последовательный квартал, когда представитель был нанят
  • Training_Qtrsэто количество кварталов, которое требуется для обучения. В вашем примере это2
• Если период обучения уже закончился, это условие имеет значение ИСТИНА, и мы используем функцию, MOD()чтобы узнать, привлек ли торговый представитель клиента в текущем квартале.
  • Торговый представитель привлекает клиента каждый третий квартал после завершения обучения (это значение хранится в Sales_Cycle)
  • Поэтому мы берем текущий квартал и вычитаем период обучения, затем проверяем, является ли результат кратным Sales_Cycle. MOD()возвращает ноль, если первый аргумент кратен второму аргументу.
  • Если это так, мы возвращаем количество торговых представителей, которые были наняты в этой строке,Hire_Qty

Я структурировал таблицу так, чтобы она соответствовала вашей, и вставил эту формулу в каждую строку. Вот скриншот того, как это выглядит:

Для создания этого примера формулы я использовал FormulaChop (полное раскрытие информации: я написал FormulaChop).Здесьскриншот вывода.Здесьэто ссылка для загрузки написанной мной электронной таблицы.