Necesito determinar cuántos nuevos representantes de ventas contratar en función de un número estimado de futuros clientes en Excel

Question

Tenemos que hacer un análisis sobre cuántos nuevos representantes de ventas contratar en función de la cantidad de clientes incorporados esperados. La parte dinámica del problema es que cuando contratamos a un representante de ventas, tiene un período de capacitación de 2 trimestres, después del cual puede captar un cliente cada 3 trimestres, y hay un período de implementación de 1 trimestre, durante el cual las ventas El representante aún puede conseguir otro cliente. Estoy tratando de encontrar una fórmula que determine la cantidad de representantes de ventas que se contratarán y cuándo es necesario contratarlos. El período de entrenamiento sólo ocurre una vez por repetición.

He creado una hoja de cálculo que modela mi pensamiento actual sobre esto, pero esto no significa que haya otras formas de resolverlo.

Hice manualmente los primeros 5 años según la estimación actual y la coloreé para ayudar a dar una idea de qué tipo de fórmula necesito. El rojo indica un período de capacitación de más de 2 trimestres que ocurre solo una vez para cualquier agente de ventas, cualquiera de los colores azules (usé varios azules para ayudar a separar las cosas) significa que el agente de ventas está trabajando para establecer un cliente y el verde significa que tiene terminado de establecer el cliente. Si tuviera algún tipo de fórmula IF que tuviera un resultado binario, 0 o 1, y aún así coincidiera con esta tabla, entonces estaría listo. ¡Aquí está el enlace y la imagen actualizados! ¡Avíseme si algo de esto es confuso e intentaré aclararlo!

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

Modelo de tabla actual

Answer 1

Pude crear una fórmula que creo que hace lo que necesitas. La clave era numerar los cuartos secuencialmente y luego usar la MOD()función.

La fórmula que escribí se ve así:

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

• La primera condición de la declaración if es verificar si hemos pasado el período de entrenamiento.
  • Current_Quarteres un contador, que es igual 1en la primera columna. En su hoja de cálculo, esto es2019.5
  • Hire_Quarteres el trimestre secuencial en el que se contrató al representante
  • Training_Qtrses el número de cuartos que se necesitan para entrenar. En tu ejemplo, esto es2
• Si estamos fuera del período de capacitación, esta condición es VERDADERA y usamos la MOD()función para saber si un comercial ha generado un cliente en el trimestre actual.
  • Un representante de ventas genera un cliente cada tercer trimestre después de finalizar su capacitación (este valor se mantiene en Sales_Cycle)
  • Entonces tomamos el trimestre actual y restamos el período de entrenamiento, luego verificamos si el resultado es múltiplo de Sales_Cycle. MOD()devuelve cero cuando el primer argumento es múltiplo del segundo argumento.
  • Si es así, devolvemos el número de representantes de ventas que fueron contratados en esta fila,Hire_Qty

Estructuré la hoja de cálculo para que coincidiera con la tuya y puse esta fórmula en cada fila. Aquí hay una captura de pantalla de cómo se ve:

Utilicé FormulaChop para generar esta fórmula de ejemplo (Divulgación completa: escribí FormulaChop).Aquíes una captura de pantalla de la salida.AquíHay un enlace para descargar la hoja de cálculo que escribí.