Formulario de análisis comercial en Excel: usando la función LAMBDA.

Con la llegada de la función LAMBDA a Excel, me di la tarea de buscar que puedes hacer con ella, y no se me ocurrió mejor cosa que desempolvar mi viejo formulario comercial y llevarlo a Excel.

formulario analisis comercial
Descarga la imagen de ser necesario.

Si aun no entras a la moda LAMBDA, puedes ver de lo que estoy hablando aquí.

Si bien no he hablado mucho de esto, básicamente mi vida con Excel empezó como un analista comercial. Mi misión era analizar la información de ventas para realizar proyecciones estadísticas en busca de la rentabilidad.

logo lacoste
mi primer firma como planner 😀

FORMATO COMERCIAL BÁSICO

Voy a construir un formato para análisis general de información como el que veras abajo. Este formato es tradicional en el análisis comercial y combina KPIs de inventario, ventas y financieros.

formato OTB

Como puedes ver, concentramos la información tanto real como proyectada para hacer el seguimiento anual de la operación.

  • Inventarios: Tanto plan para iniciar el mes, como iniciales y finales para medir desplazamiento de producto.
  • Ventas: Plan y reales contando el descuento aplicado al producto.
  • Markdowns: Que representan el descuento otorgado al cliente. Este regularmente es la diferencia entre precio de venta del producto – venta neta.
  • Costos: Se maneja un plan de costo de venta y un real para medir si estamos recuperando lo gastado en el producto.
  • Pedidos pendientes: Si nuestra empresa genera ordenes de compra, este indicador nos dirá el restante por llegar de inventario. Este indicador es crucial para la planeación de compras e inventario.

FORMULAS COMERCIALES LAMBDA: MARGEN

Lo primero que vamos a calcular será el margen de ganancia, el cual nos dice que tanto del costo de ventas hemos recuperado con la venta con descuento. La fórmula es la siguiente:

ROS = VENTAS - COSTO.VENTA

Para pasarla con la función LAMBDA, debemos hacerlo como sigue:

=LAMBDA(venta,costo,SUM(venta)-SUM(costo))

Observa como usamos la función SUMA en cada variable, esto es debido a que el usuario podría querer calcular el margen acumulado. Una de las ventajas de LAMBDA es que podemos usar otras funciones para complementar el cálculo.

También será importante crear la función para margen porcentual, que según la teoría comercial se calcula así:

=LAMBDA(venta,costo,(SUM(venta)-SUM(costo))/SUM(venta))

FORMULAS COMERCIALES LAMBDA: FACTOR COSTO

Algo que solemos omitir muchos analistas es el factor costo, que nos ayuda a saber si los precios que estamos colocando al producto son los adecuados. El marcaje dirá cuantos productos a precio lleno son necesarios para reponer el inventario gastado.

=LAMBDA(venta,md,costo,((SUM(venta,md)-SUM(costo))/SUM(costo))-1)

Cuando hablamos de factor costo, estamos revisando la proporción del costo sobre el precio de venta de lo vendido. En términos simples, que tanto pesa el costo sobre lo vendido.

=LAMBDA(venta,md,utilidad,(SUM(venta,md)-SUM(utilidad))/SUM(venta,md))

FORMULAS COMERCIALES LAMBDA: INVENTARIOS.

Pasando a los inventarios, vamos a revisar que tanto producto estamos vendiendo y a que velocidad. La función por crear será la rotación, misma que requiere conocer el inventario promedio, por lo que usaremos sumas y promedios.

=LAMBDA(venta,inventario,SUM(venta)/AVERAGE(inventario))

Otra función útil en los inventarios es el inventario promedio. Con este indicador, podemos saber que tanto producto de venta tenemos en promedio para vender siempre. Cubrimos ventas especiales o estrategias de descuentos.

=LAMBDA(invi,invf,SUM(invi,invf)/COUNT(invi))

Ahora los meses de inventario (MOS), que nos ayudan a saber que tanta cobertura de venta tenemos disponible en este momento, usando como referencia el promedio de ventas del periodo seleccionado.

=LAMBDA(inventario,venta,inventario/AVERAGE(venta))

Por último, opte por introducir la función meses de cobertura que, si bien es casi lo mismo que MOS, funciona mejor para indicadores de planeación.

=LAMBDA(inventario,venta,inventario/SUM(venta))

FORMULAS COMERCIALES LAMBDA: RENDIMIENTO.

El punto crucial de todo, donde se ve la efectividad.

El indicador ROI es el más común y mas efectivo para el negocio comercial, ya que permite revisar si el dinero gastado en inventario ha logrado dar liquidez al negocio.

=LAMBDA(rot,gmper,(AVERAGE(rot)*AVERAGE(gmper)))

Entre mas grande es el indicador, más dinero convertimos en utilidad.

FORMULAS COMERCIALES LAMBDA: OPEN TO BUY

Terminamos es largo post con la función madre de los compradores y planeadores de demanda. El abierto de compra (OTB por sus siglas en ingles) es un indicador dinámico que se construye utilizando la información de la empresa en tiempo real.

No hay que confundirlo con un plan de ventas, ya que el OTB es el resultado real de la ejecución mientras que el plan es un indicador estático.

La forma de calcular este abierto es la siguiente:

  • Tomamos el inventario inicial plan del mes en curso.
  • Restamos al de arriba el inventario actual
  • Luego, vamos a sumar el resultado de la diferencia entre las ventas plan – venta real. Si la venta real es mayor, la resta es un valor absoluto.
  • Continuamos sumando al resultado la diferencia entre los MD plan – MD reales. Si es que ya hemos agotado el presupuesto de rebajas, debemos convertir este número a 0.
  • Por último, restamos a todo este resultado los pedidos aun pendientes por entregar. Esto servirá para bajar el numero si es que nuestros proveedores están teniendo retrasos en la entrega.
=LAMBDA(invp,invi,vplan,vreal,mplan,mreal,pedidos,invp-invi+ABS(SUM(vplan)-SUM(vreal))+IF(SUM(mplan)>SUM(mreal),0,SUM(mplan)-SUM(mreal))+SUM(pedidos))

Un calculo más complejo, pero no por eso imposible.

CREANDO EL FORMULARIO.

Todas las funciones LAMBDA depositadas arriba, deben ir escritas en las etiquetas de datos para poder ser recicladas en la hoja de cálculo. Así es como debería de verse.

etiqueta función LAMBDA

Y cuando terminamos de introducir todas a las etiquetas, nuestro menú de nombres lucirá de la siguiente forma:

funciones LAMBDA

Ahora para usarlas, solamente debemos llamar a la función con la nomenclatura que conocemos y hacer que funcione.

formulario comercial función LAMBDA Excel

Cualquier duda que puedan tener nos leemos abajo.

Formulario de análisis comercial en Excel: usando la función LAMBDA.
Scroll hacia arriba