Prompt: puente de margen por precio, volumen, mix y coste unitario calculado en código, y un asistente de IA que lo explica

En dos frases

«El margen sube, pero el porcentaje de margen baja» es una de las frases más habituales en un comité de dirección y una de las más difíciles de explicar sin un puente. Esta entrada te da las fórmulas, un script que las aplica a las líneas de factura y demuestra que los efectos suman, y un prompt que pide al asistente de IA que explique el resultado en lugar de calcularlo.

Los cuatro efectos, definidos

Para cada producto i y dos periodos (0 = comparación, 1 = actual), toma las unidades Q, el precio neto medio P (venta neta entre unidades) y el coste unitario medio C. El margen unitario es m = P − C, las unidades totales son Q_T y el peso de un producto en el mix del periodo 0 es s_0,i = Q_0,i / Q_T0.

• Volumen: (Q_T1 − Q_T0) × Σ s_0,i × m_0,i. Más unidades, con el mix y los márgenes antiguos.
• Mix: Σ (Q_1,i − Q_T1 × s_0,i) × m_0,i. Las mismas unidades totales, pero con más peso de unos productos.
• Precio: Σ Q_1,i × (P_1,i − P_0,i). Lo que aportó el cambio de precio neto sobre las unidades de hoy.
• Coste unitario: −Σ Q_1,i × (C_1,i − C_0,i). Lo que restó el cambio de coste unitario sobre las unidades de hoy.

Con estas definiciones los cuatro efectos suman exactamente la variación del margen bruto, sin residuo. Hay otras convenciones válidas (por ejemplo, valorar el precio con los volúmenes antiguos), pero elige una, déjala por escrito y mantenla todos los trimestres; cambiar de convención cambia la historia.

Los datos ficticios

La muestra es un distribuidor con tres productos y diez clientes que compara el tercer trimestre de 2025 con el de 2026: 278 líneas de factura con unidades, precio de tarifa, descuento, venta neta y coste. La tarifa subió un 4 % y los costes de compra alrededor de un 6 %; el mix de ventas se desplazó hacia el producto más caro. Sembramos tres líneas sospechosas en 2026.

• Líneas de factura de dos trimestres (CSV, columnas en español)
• Script del puente y de validaciones (Python, solo biblioteca estándar)

Ejecuta python3 pvm.py sales-lines.es.csv (los rótulos de la salida están en inglés). La parte del puente del script es corta:

volume = (q1t - q0t) * sum(share0[p] * m0[p] for p in prods)
mix = sum((Q[P1][p] - q1t * share0[p]) * m0[p] for p in prods)
price_eff = sum(Q[P1][p] * (price[P1][p] - price[P0][p]) for p in prods)
cost_eff = -sum(Q[P1][p] * (ucost[P1][p] - ucost[P0][p]) for p in prods)
assert abs((volume + mix + price_eff + cost_eff) - (M1 - M0)) < 0.01, "el puente no cuadra"

Antes del puente, el script revisa cada línea y señala, sin corregir: unidades a cero con venta, coste superior a la venta neta y descuentos del 25 % o más. Las líneas señaladas quedan fuera del puente y se informan con sus totales para que puedas cuadrarlas aparte.

En hoja de cálculo

Con una fila por producto y las columnas B a G para Q0, Q1, P0, P1, C0 y C1 (filas 2 a 4), los cuatro efectos son (Excel en español, con punto y coma):

Peso 0 (H)            =B2/SUMA($B$2:$B$4)
Margen unitario 0 (I) =D2-F2
Volumen               =(SUMA(C2:C4)-SUMA(B2:B4))*SUMAPRODUCTO(H2:H4;I2:I4)
Mix                   =SUMAPRODUCTO(C2:C4-SUMA(C2:C4)*H2:H4;I2:I4)
Precio                =SUMAPRODUCTO(C2:C4;E2:E4-D2:D4)
Coste unitario        =-SUMAPRODUCTO(C2:C4;G2:G4-F2:F4)

Añade una celda que reste los cuatro efectos a la variación del margen: tiene que dar cero. Python en Excel, que Microsoft documenta como forma de ejecutar Python dentro de un libro, es una alternativa si las líneas de factura no caben cómodamente en fórmulas.

Qué devuelve el script

El margen bruto pasa de 340.628 (39,7 %) a 397.768 (37,2 %) en las líneas limpias.

Se apartan tres líneas, con 18.336 de venta neta y 17.934 de coste: una venta de P-200 con un 38 % de descuento que además está por debajo del coste, una venta de P-300 por debajo del coste y una línea con 1.250 de venta y cero unidades.

Cómo usar el prompt

Pega la salida del script como PUENTE e INCIDENCIAS, pon en LIMITE_DESCUENTO el mismo umbral que en el script y añade el CONTEXTO. En el ejemplo: «tarifa +4 % desde enero; dos proveedores subieron precios entre un 5 % y un 7 %; impulso de P-200 con un nuevo distribuidor en el norte».

Qué debería contener una buena respuesta

Escrita por nosotros como referencia, no es salida de un modelo:

• La paradoja del %. El margen en euros subió porque se vendieron más unidades de P-200, y P-200 deja más euros por unidad que los otros. Pero P-200 tiene el porcentaje de margen más bajo (alrededor de un tercio, frente a cerca del 38 % de P-100 y del 56 % de P-300), así que el porcentaje medio baja. Las dos cosas son ciertas.
• Precio frente a coste. El precio aportó 39.018 y el coste unitario restó 38.005: la subida de precios cubrió casi exactamente la de costes en euros. Producto a producto, el coste de P-200 subió más que su precio neto (+3,33 frente a +2,72 por unidad), así que es el producto en el que el precio no siguió al coste.
• Incidencias. El descuento del 38 % puede ser un acuerdo especial con el nuevo distribuidor; confírmalo con ventas antes de llamarlo error. La línea sin unidades puede ser un cargo por servicio o un ajuste de precio facturado sin cantidad; pregunta a facturación.
• Acciones. Revisar el precio de P-200 frente a su nuevo coste; fijar una regla de aprobación para descuentos superiores al 25 %; medir el trimestre siguiente el precio neto de P-200 y el número de líneas apartadas.

Variantes por ERP: dónde están las líneas de factura

El script necesita una fila por línea de factura (o por producto y cliente) con unidades, venta neta y coste de ventas:

• Odoo: Contabilidad › Informes › Análisis de facturas, agrupado por producto y cliente, con cantidad, importe sin impuestos y coste si los costes de producto están al día.
• Microsoft Dynamics 365 Business Central: los movimientos de valor de tipo venta, que llevan importe de venta e importe de coste por movimiento de producto, abiertos en Excel.
• NetSuite: una búsqueda guardada de transacciones de venta con artículo, cantidad, importe y coste estimado o beneficio bruto.
• SAP S/4HANA: las partidas individuales del análisis de margen, que ya separan ingresos y coste de las ventas por producto y cliente.
• Sage y A3: las estadísticas de ventas o el listado de líneas de factura por artículo y cliente, exportados a Excel y cruzados con el coste del artículo si el listado no lo incluye.

Comprueba dos cosas antes de fiarte del coste: si es el coste real a la fecha de la venta o el coste estándar de hoy, y si los abonos están incluidos como líneas negativas.

Pasos de revisión humana

1. Cuadra la venta neta y el coste de las líneas limpias más las apartadas con la cuenta de resultados de cada periodo.
2. Decide qué hacer con cada línea apartada (incluirla, corregirla en el ERP o dejarla fuera) y déjalo por escrito.
3. Comprueba que el puente cuadra (el script se detiene si no lo hace).
4. Lee la explicación del asistente contra la tabla: cada cifra que use tiene que estar ahí.
5. Acuerda las acciones con ventas y compras antes de que aparezcan en un informe.

Errores típicos

• Mezclar precios brutos y netos. Usa la venta neta después de descuentos; el precio de tarifa con una columna de descuento aparte esconde dónde se fue el margen.
• Coste estándar en un periodo y real en el otro. El efecto coste unitario mide entonces un cambio de método, no de coste.
• Productos nuevos. Un producto que no existía en el periodo 0 no tiene margen antiguo. Trátalo en una línea aparte («productos nuevos») en lugar de meterlo a la fuerza en el mix.
• Cambiar el orden de los efectos. Valorar el precio con volúmenes antiguos o el mix con márgenes nuevos da otros repartos; mantén una sola convención.
• Quitar líneas malas sin decirlo. Las líneas apartadas tienen que informarse y cuadrarse; si no, el puente deja de coincidir con la cuenta de resultados.
• Dejar que el asistente calcule el puente. Producirá efectos que no suman, o que suman gracias a un residuo inventado.

Límites

El puente explica el margen bruto por producto. No reparte gastos generales, logística ni rappels que no estén en la factura, y no te dice qué clientes son rentables después del coste de servirlos. Los nombres de clientes y productos son información comercial: anonimízalos o usa un plan de empresa aprobado antes de pegarlos en un servicio de IA.