Una casa exportadora añade en marzo dos partidas nuevas a su plantilla de deal —un recargo de la naviera y la fumigación de la mercancía en origen— y las escribe en las dos filas que hay debajo de la última. La fórmula del margen sigue siendo =SUMA(D6:D17) y las filas nuevas son la 18 y la 19. Durante nueve meses esas partidas se pagan, se ven en pantalla y no entran en el GP: 400 € por contenedor × 100 contenedores = 40.000 € de margen que nunca existió, descubiertos al cuadrar el año con la contabilidad.
El caso es reconstruido: no es un cliente nuestro ni una empresa concreta. Lo hemos montado sobre un fallo que se repite en las hojas de trading —el primero de la auditoría de tu Excel en una tarde— con importes ilustrativos y redondos, para que cada cuenta se pueda rehacer con una calculadora. Abajo está cómo pasó, por qué Excel no avisó y las tres comprobaciones que lo habrían cazado en abril y no en enero.
La plantilla y el cambio de marzo
La casa tiene sede en España, lleva las cuentas en euros, compra legumbres a productores de Perú y las exporta desde el Callao a clientes europeos, vendidas CFR puerto de destino: unos 130 contenedores de 20 toneladas al año. Con CFR, la exportadora paga el despacho de exportación y el flete hasta el puerto de destino, con sus recargos, y sus contratos de venta le cargan además la fumigación y el certificado fitosanitario que pide el país de destino; lo que pasa en destino lo paga el cliente.
Cada deal es una pestaña de Excel copiada de una plantilla, a la que en la casa llaman ficha. Arriba, la venta (D3), la compra (D4) y el GP en la cabecera (G3); debajo, doce partidas de coste en las filas 6 a 17, con el concepto en la columna B y el importe del deal en la columna D. El GP es una sola fórmula:
=D3-D4-SUMA(D6:D17)
D3: venta D4: compra D6:D17: las doce partidas de costeA finales de marzo, el jefe de operaciones añade a la plantilla dos partidas que hasta entonces se iban a gastos generales: el recargo de emisiones de la naviera, que va con el flete (120 € por contenedor en este ejemplo), y la fumigación en origen con su certificado fitosanitario (280 €). Las escribe donde lo haría casi cualquiera: en las filas 18 y 19, justo debajo de la última. Desde ese día, todas las fichas nuevas se copian de esa plantilla.
¿Por qué Excel no amplió la suma?
Porque las filas no se añadieron dentro del rango, sino a continuación. Una SUMA sobre un rango se ajusta sola cuando insertas o borras filas, pero Microsoft pone la condición en su página de la función SUMA: se actualiza «siempre y cuando no esté fuera del rango al que hace referencia la fórmula». Las filas 18 y 19 estaban fuera.
- Insertar dentro del rango: con clic derecho en la fila 17 y «Insertar», la fila nueva entra por encima de la 17, dentro de
D6:D17, y la fórmula pasa a=SUMA(D6:D18). - Escribir o insertar justo debajo: la fila 18 ya está fuera de
D6:D17. La fórmula se queda como estaba y nada da error. - Los casos intermedios: en su página sobre cómo detectar errores, Microsoft dice que «es posible que» una fórmula no incluya los datos insertados entre el rango original y la celda de la fórmula. Es posible, no seguro, y esa es la conclusión práctica: no dependas de que Excel lo haga ni de que no lo haga.
En Hojas de cálculo de Google, la ayuda oficial no describe qué hace una referencia normal como D6:D17 al añadir filas debajo; solo documenta que las referencias a una tabla «se actualizan cuando añades o quitas datos de la tabla». Pruébalo en una copia de tu plantilla.
¿Y el triángulo verde? La regla «Fórmulas que omiten celdas en una región», según la documentación de Microsoft, compara el rango con las celdas con datos adyacentes a la celda de la fórmula: su ejemplo es un =SUMA(D2:D4) en D8 con números en D5, D6 y D7. Aquí el GP estaba en la cabecera, lejos de las partidas, y no encaja en ese patrón. Además, muchas hojas la tienen desactivada.
Nueve meses después: cómo apareció
En el cierre del año, la responsable de administración cuadra cada deal con la contabilidad: lo facturado por los proveedores e imputado a la operación frente al coste que entra en el GP de la ficha, es decir, venta − compra − GP. Sumando la columna D entera habría cuadrado; con el coste que usa el GP, no: las fichas se quedan 40.000 € por debajo, y la diferencia tiene tres rasgos que la delatan:
- Empezaba en abril. Los deals de enero a marzo cuadraban al euro; todos los posteriores, no.
- Era proporcional a los contenedores. Cada deal descuadraba exactamente 400 € por contenedor: no era una factura perdida, era una regla.
- Coincidía con dos partidas. 120 + 280 = 400. En cualquier ficha de abril en adelante, las dos filas estaban ahí con sus importes bien puestos. Bastaba con hacer clic en G3 para ver que la fórmula terminaba en la fila 17.
| Trimestre | Contenedores | Recargo de emisiones (120 €) | Fumigación (280 €) | Fuera del GP |
|---|---|---|---|---|
| Abril a junio | 33 | 3.960 | 9.240 | 13.200 |
| Julio a septiembre | 33 | 3.960 | 9.240 | 13.200 |
| Octubre a diciembre | 34 | 4.080 | 9.520 | 13.600 |
| Total | 100 | 12.000 | 28.000 | 40.000 |
Partidas fuera del rango = 120 + 280 = 400 €/contenedor
Contenedores afectados = 33 + 33 + 34 = 100
Margen sobrestimado = 400 × 100 = 40.000 €
Por tonelada (20 t por contenedor): 40.000 € ÷ 2.000 t = 20 €/tVeinte euros por tonelada parecen poco hasta que se ponen al lado del margen. En el contenedor de quinoa desglosado línea por línea, el GP es de 88 € por tonelada: un error de 20 €/t es más de una quinta parte. Las facturas se pagaron: lo que estaba mal era el margen del informe, y cualquier oferta calculada con esa plantilla.
Las tres comprobaciones que lo habrían cazado
Ninguna exige cambiar de herramienta, y cada una ataca el fallo por un lado distinto.
1. Un control en la ficha: contar partidas y sumar por otro camino
Una celda junto al GP que haga dos preguntas: si el número de partidas con nombre en la columna B coincide con el número de importes que entran en el total, y si la columna D sumada con un rango holgado da lo mismo que la suma del GP. Si alguna respuesta es no, la ficha dice «REVISAR».
=SI(Y(CONTARA(B6:B40)=CONTAR(D6:D17);SUMA(D6:D40)=SUMA(D6:D17));"OK";"REVISAR")El rango D6:D17 tiene que ser el mismo que usa el GP. En el caso, la primera ficha de abril, de cuatro contenedores, habría dado 14 partidas con nombre frente a 12 importes en el total, y 1.600 € de diferencia entre las dos sumas: 400 € por cada contenedor del deal. Dos detalles: escribe 0 en las partidas sin coste en vez de dejarlas vacías, porque CONTAR no cuenta las celdas en blanco, y recuerda que D6:D40 también es un rango: déjale holgura de sobra.
2. Una tabla con fila de totales en vez de un rango
La solución de fondo es que no haya un rango que se pueda quedar corto. Selecciona las partidas, conviértelas en tabla con Ctrl+T y activa la Fila de totales. Según Microsoft, sus celdas aparecen vacías y, al elegir Suma en el desplegable de la columna, Excel escribe SUBTOTAL(109,…) con una referencia a la columna de la tabla, no a unas celdas. El GP pasa a ser =D3-D4-SUMA(TablaCostes[Importe]), y esas referencias estructuradas se ajustan solas cuando la tabla gana filas. La condición es añadir las partidas nuevas dentro de la tabla, con Tab en la última celda o con «Insertar filas de tabla»: una partida escrita debajo de la fila de totales queda fuera, como antes. En Google Sheets, Insertar → Tabla y =SUM(TablaCostes[Importe]) cumplen la misma función: según su ayuda, las referencias de tabla se actualizan al añadir o quitar datos.
3. Cuadrar cada deal al liquidarlo, no el año al cerrarlo
El fallo salió en enero porque fue la primera vez que alguien comparó las fichas con algo externo. Ese cuadre se puede hacer deal a deal, en cuanto llegan las facturas. Es la misma conciliación que separa el margen de tu Excel del que cobras, y aquí habría saltado en cuanto se liquidara el primer deal de abril: con cuatro contenedores, 1.600 € de diferencia, exactamente las dos partidas.
| Comprobación | Dónde vive | Cuándo habría avisado |
|---|---|---|
| 1. Conteo y suma de control | En cada ficha, junto al GP | En la primera ficha de abril, al rellenar las dos partidas |
| 2. Tabla con fila de totales | En la plantilla | No habría hecho falta si las partidas nuevas se añaden dentro de la tabla (Tab en la última celda o «Insertar filas de tabla»); una escrita debajo de la fila de totales queda fuera |
| 3. Cuadre al liquidar | Entre la ficha y la contabilidad | En cuanto se liquidara el primer deal de abril |
Lo que hace Granel con esto, y lo que no
En Granel, las partidas de coste de un deal no son filas de una hoja sino una lista, y el GP se calcula recorriéndola entera: una partida que añades —a mano o con la plantilla estándar de doce— entra en el margen, porque no hay un rango que se pueda quedar corto. Cada partida guarda el importe estimado y, al liquidar, el real, y la pestaña de liquidación los pone uno al lado del otro. La calculadora de P&L, gratuita y sin registro, hace la misma cuenta con hasta 30 partidas.
Lo que Granel no hace es cuadrar tus deals con tu contabilidad ni leer tus facturas: el importe real lo introduce alguien. Tampoco arregla una hoja rota al importarla: si tu ficha calcula el GP con una SUMA debajo de las partidas, el importador cuenta solo las filas que esa fórmula cubre; una partida que la SUMA dejaba fuera tampoco entra, y la vista previa no la delata porque los dos GP coinciden. Antes de importar, pasa la comprobación 1.
Preguntas frecuentes
¿Por qué mi SUMA de Excel no incluye las filas nuevas?
Porque las añadiste fuera del rango que suma, normalmente justo debajo de la última fila. Excel amplía la referencia cuando insertas filas dentro del rango, no cuando escribes a continuación. Convierte las partidas en una tabla y suma la columna de la tabla.
¿Cómo compruebo que el total de costes incluye todas las partidas?
Compara el número de partidas con nombre (CONTARA sobre la columna de conceptos) con el número de importes que entran en la suma (CONTAR sobre el mismo rango que la SUMA). Si no coinciden, falta algo en el total.
¿Pasa lo mismo en Google Sheets?
La ayuda de Google no documenta cómo se comporta una referencia normal al añadir filas debajo del rango. Sí documenta que las referencias de tabla se actualizan al añadir o quitar datos, así que la solución es la misma: usar una tabla.
¿Cada cuánto conviene cuadrar los costes de los deals con la contabilidad?
Deal a deal, al liquidar cada uno, y no una vez al año. Un error de plantilla se repite en todas las fichas que salen de ella: cuanto antes se compara una con las facturas, menos fichas arrastran el mismo fallo.