La mayoría de los equipos FMCG no concilian los datos del retailer con una herramienta. Lo hacen en Excel, y el problema rara vez es la herramienta. El problema es que el libro mezcla el archivo en bruto del retailer, el mapeo del analista, las fórmulas de comparación y las notas sobre excepciones en las mismas hojas, así que cada mes el analista reconstruye la lógica a mano y nadie puede decir exactamente qué cambió. Una conciliación que no puede reproducirse no es un proceso; es una serie de decisiones que casualmente quedan registradas en una hoja de cálculo.
Esta guía estructura la conciliación en Excel en cinco zonas —datos en bruto, mapeo, controles, comparación y excepciones— y muestra qué debe contener cada zona, qué no debe contener y cómo encajan entre sí para que el mismo archivo pueda volver a ejecutarse el mes siguiente con la misma lógica y datos distintos.
Estructura del libro
Un libro, cinco zonas, cada una en su propia hoja, en un orden fijo. La hoja de bruto contiene el archivo del retailer exactamente como se entregó, sin nada más. La hoja de mapeo contiene las tablas que traducen códigos de retailer, tiendas y periodos a sus referencias internas. La hoja de controles contiene los recuentos y comprobaciones que demuestran que las entradas están completas antes de que corra la comparación. La hoja de comparación contiene los datos unidos y las diferencias. La hoja de excepciones contiene las líneas de diferencia que necesitan una decisión, con responsable, estado y antigüedad.
El orden no es decorativo. Los datos fluyen en una sola dirección, de bruto a mapeo a comparación, y los controles se sitúan entre bruto y comparación para que una entrada mala nunca llegue a la tabla de diferencias. Cuando un analista necesita explicar un número, retrocede por el flujo hasta la fuente en bruto. Cuando una fórmula se rompe, se rompe en una zona, no en una hoja que contiene tres tipos de contenido.
La misma disciplina aplica a las pestañas dentro de una hoja. La hoja de comparación, por ejemplo, debería tener un diseño fijo para el periodo actual, no diez meses pasados uno al lado del otro con posiciones de columna distintas. Los archivos históricos se quedan en la carpeta de versiones, no en el libro. Un libro con una tabla de comparación limpia por periodo es más fácil de leer, de comprobar y de automatizar que uno que crece hacia los lados cada mes.
Reglas de los datos en bruto
La hoja de bruto es un contrato: el archivo del retailer entra exactamente como se entregó y en esa hoja nunca se escribe encima. El resto del libro lee esos valores, así que cualquier arreglo manual aplicado a la hoja de bruto corrompe el rastro de auditoría. Si el archivo del retailer trae una fecha en el formato equivocado o una cantidad claramente disparatada, el arreglo pertenece a un paso de transformación, no a la celda de origen.
Dos reglas prácticas lo hacen posible. Primero, importe el archivo con una herramienta en lugar de copiar y pegar: el flujo de combinación de archivos de Power Query convierte una carpeta de archivos mensuales del retailer en una tabla única con un paso de transformación documentado, y la misma consulta puede actualizarse el mes siguiente en lugar de reconstruirse. Segundo, añada un sello de recepción a la tabla en bruto —nombre del archivo, fecha de recepción y recuento de filas— para que el libro pueda demostrar con qué versión de los datos del retailer está trabajando.
El recuento de filas es el primer control y pertenece a los datos en bruto, no a la cabeza de alguien. Si el retailer envió 4.000 filas el mes pasado y 3.700 este mes, la diferencia es una tienda o una categoría que falta, y la conciliación debería detenerse hasta que eso se explique. Comparar el recuento de filas entregado con una población esperada es la comprobación más barata de todo el proceso, y es la que más libros se saltan porque el analista «notó que el archivo parecía pequeño».
Joins de mapeo
La hoja de mapeo contiene las tablas de consulta de las que depende la comparación: código de producto del retailer a SKU interno, código de tienda del retailer a ubicación interna, periodo de reporting del retailer a su periodo financiero. Cada consulta es una tabla pequeña en su propia parte de la hoja y tiene una fecha de vigencia, porque los códigos de producto y de tienda cambian. La consulta es el lugar donde aterriza el mapeo de producto que se hace en el resto de la conciliación.
Los joins de la hoja de comparación referencian estas tablas por nombre. Si un código del retailer no tiene mapeo, el join devuelve un valor ausente y la línea se convierte en excepción, lo cual es correcto: un producto sin mapear es una diferencia por resolver, no una fila que descartar. La comprobación de cardinalidad también importa. Una consulta que devuelve dos SKU para un código de retailer es un error de mapeo que duplica silenciosamente las ventas; la comparación debería marcar cualquier resultado uno-a-muchos en lugar de dejar que Excel elija la primera coincidencia.
Mantener el mapeo en su propia zona es lo que permite probarlo. Cuando aparece un código nuevo del retailer, el analista añade una fila a la tabla de mapeo y toda la comparación se vuelve a ejecutar. Cuando alguien propone «arreglar» una cifra de ventas cambiando un join, la hoja de mapeo lo hace visible como un cambio de mapeo con fecha, en lugar de una edición invisible dentro de una fórmula.
Tabla de comparación
La tabla de comparación une los datos del retailer con los datos internos a un grano declarado —tienda por producto por periodo— y produce la diferencia de cada línea. El grano es un conjunto de columnas, no una frase: cada fila lleva tienda, producto, periodo, unidades del retailer, unidades internas y la varianza. Las líneas que coinciden dentro de la tolerancia se marcan como cuadradas. Las que no coinciden son la tabla de diferencias que alimenta la hoja de excepciones.
La lógica de comparación debe ser estable y tener nombre. Si la varianza es retailer menos interno, si se calcula en unidades o en valor, si una diferencia de calendario se netea o se muestra por separado —cada una es una decisión que cambia el resultado y cada una debe escribirse una sola vez en la hoja de documentación del libro, para que la corrida del mes siguiente no cambie silenciosamente el signo de la varianza. Lo mismo aplica a las tolerancias: una tolerancia es una regla con un valor y una justificación, no un número que el analista recuerda aplicar.
La hoja de comparación también debería llevar los totales de control: unidades entregadas de la hoja de bruto, unidades mapeadas tras los joins y la proporción de valor que cuadró. Esos tres números le dicen si la tabla de diferencias es real. Si el valor mapeado es el 97% del valor entregado, el 3% que falta en el join es un problema de entrada, y ninguna investigación del 5% de varianza tendrá sentido hasta que el join esté completo.
Excepciones
La hoja de excepciones es un registro, no un borrador. Cada línea de excepción lleva los identificadores, la varianza, una categoría de causa, un responsable, un estado y el periodo en que apareció por primera vez. Las categorías son fijas —calendario, mapeo, devoluciones, error de datos, cambio comercial— para que la misma diferencia no se etiquete de forma distinta según la persona. Los estados también son fijos: abierta, pendiente de evidencia, resuelta, disputada, acordada.
El registro de excepciones es lo que convierte una conciliación en un proceso de control. Una diferencia que aparece tres meses seguidos con el mismo código de retailer es un problema recurrente con una causa raíz, y el registro hace visible la recurrencia porque la línea lleva su periodo de primera aparición. Una diferencia que se «resuelve» cambiando la cifra del retailer y nunca vuelve a aparecer es una historia distinta de una resuelta con evidencia. El registro registra cuál de las dos ocurrió.
Esta es la zona donde la conciliación conecta con el ciclo de vida de excepciones que usa el resto del modelo operativo. La hoja de excepciones de Excel y un sistema formal de excepciones pueden contener los mismos registros con los mismos campos; la hoja es la versión a pequeña escala que funciona mientras el proceso es manual, y mantiene la misma disciplina para que migrar a un sistema después sea una migración, no un rediseño.
Versionado
Una conciliación solo vale lo que vale su historial, porque los archivos del retailer llegan corregidos y la corrección cambia la respuesta. Cada corrida mensual produce una versión del libro vinculada a una versión del archivo del retailer. El libro mantiene la fecha de corrida, el nombre del archivo, el recuento de filas de recepción y la proporción mapeada en una hoja pequeña de registro, para que cuando el retailer envíe un archivo actualizado en la semana tres, el analista pueda afirmar exactamente qué cambió entre la corrida uno y la corrida dos en lugar de absorber silenciosamente los números nuevos.
La carpeta de versiones guarda las corridas cerradas. Una carpeta por periodo, el libro guardado con un sufijo de versión y el archivo fuente conservado junto a él. Esto cuesta unos segundos de disciplina y elimina los dos modos de fallo que dominan la conciliación manual: el analista que trabaja sobre una versión antigua del archivo del retailer sin saberlo, y el analista que corrige una cifra en la salida sin poder decir si cambió la fuente o la lógica.
El versionado también hace seguro compartir el libro. Cuando el número se mueve entre la versión del lunes y la del martes, el registro de corrida dice por qué. Sin él, cada movimiento de una varianza reportada dispara una re-ejecución de memoria, y el equipo concilia la conciliación en lugar de los datos.
Puntos de falla
El libro fallará en sitios predecibles, y nombrarlos es la mitad del arreglo. El primero es la hoja de bruto editada a mano, que rompe silenciosamente el rastro de auditoría. El segundo es la lógica que vive en la cabeza del analista —la tolerancia, el signo de la varianza, el tratamiento de las devoluciones—, de modo que un cambio de persona cambia el resultado sin registro. El tercero es el mapeo uno-a-muchos, que produce ventas duplicadas con apariencia plausible. El cuarto es el control de cobertura ausente, donde un archivo que perdió 300 filas «cuadra» bien porque todas las filas restantes coinciden.
El hilo común es que cada fallo es invisible en la salida final. Una conciliación que cuadra puede estar mal en las cuatro formas. Los controles —recuento de recepción, proporción mapeada, comprobación de cardinalidad, registro de corrida— son las partes del libro que hacen visibles esos fallos, por eso son zonas y no ocurrencias.
La investigación sobre hojas de cálculo da la misma advertencia desde el lado de la evidencia: las tasas de error a nivel de celda en el desarrollo de hojas de cálculo se sitúan en unos pocos puntos porcentuales, y en un libro con cientos de celdas de fórmula eso significa que los errores están presentes, no que son posibles. La respuesta no es abandonar Excel sino tratarlo como software: la lógica es código, los datos en bruto son entrada, y a la hoja le aplica la misma disciplina de pruebas que al código. El trabajo de estandarización que gobierna los archivos que alimentan este proceso pertenece al mismo sistema.
Dónde se rompe una conciliación en ExcelLos datos en bruto y la lógica comparten las mismas hojas, así que el resultado no puede probarse, las excepciones viven en notas y el libro se reconstruye cada mes. El primer paso es separar las cinco zonas y añadir el recuento de recepción; la pregunta de la automatización solo es real cuando esa corrida es estable.
Cuándo Excel deja de ser la respuesta
Excel es la herramienta correcta para la transición de un caos mensual ad hoc a un proceso controlado, y es la herramienta equivocada para un proceso que se ha vuelto estable y de alto volumen. La frontera no es el tamaño de la empresa; es si la lógica del libro sigue cambiando. Si las mismas cinco zonas han producido el mismo resultado limpio durante varios meses con solo los datos cambiando, esa lógica está lista para automatizarse. Si el analista sigue reescribiendo el mapeo, la tolerancia y las categorías de excepción cada mes, automatizar ahora automatizaría el cambio mismo.
La prueba de preparación es sencilla: tome las últimas tres corridas. Si el libro no requirió cambios estructurales en esos tres meses, el proceso es bastante estable para pasarse a una herramienta o a una operación gestionada, y el marco de decisión para la automatización aplica directamente. Si cambió cada mes, el problema no es la herramienta; es que la conciliación todavía no se ha convertido en un proceso definido con reglas definidas —y las zonas del libro de esta guía son la vía más rápida para convertirlo en uno.
Preguntas frecuentes
¿Se pueden conciliar datos de retailer correctamente en Excel?
Sí, durante un periodo de transición definido, siempre que el libro separe entradas en bruto, mapeo, controles, comparación y excepciones. Los problemas empiezan cuando las celdas de entrada y las celdas de fórmula comparten las mismas hojas, porque entonces la lógica no puede probarse, el resultado no puede auditarse y el libro se reconstruye desde cero cada mes. Esa separación es toda la diferencia entre una corrida repetible y un caos mensual.
¿Cuál es el error más común en una conciliación en Excel?
Mezclar los datos en bruto con el análisis. Los analistas pegan los archivos del retailer en el mismo libro, escriben encima de las celdas de origen, introducen valores fijos dentro de columnas de fórmula y copian rangos a mano. La investigación sobre errores en hojas de cálculo encuentra errores en un pequeño porcentaje de las celdas, y en un libro de cualquier tamaño eso produce errores de resultado final con seguridad. La solución es separar zonas para que los datos en bruto nunca se toquen.
¿Cuándo debería dejar de hacer la conciliación en Excel?
Cuando la corrida sea estable, las reglas estén documentadas y el cuello de botella restante sea el tiempo, no el diseño. Si la misma lógica del libro ha producido el mismo resultado limpio durante varios meses, esa lógica es candidata a la automatización. Automatizar un proceso que cambia cada mes automatiza el cambio; automatice solo lo que ha dejado de cambiar.
Fuentes
- Microsoft Learn, Combine files overview — Power Query.
- Raymond R. Panko, Spreadsheet Development Error Experiments.
La documentación de Microsoft se cita para el paso de recepción de archivos combinados que describe esta guía. La investigación de Panko se cita por el hallazgo general de que las tasas de error a nivel de celda en hojas de cálculo se sitúan en unos pocos puntos porcentuales. Esta guía no afirma una tasa concreta para los libros de conciliación de retail en particular, porque las fuentes describen el desarrollo de hojas de cálculo en general y no este caso de uso específico.