Comprobador de Auditoría de Fórmulas
Cuándo usarlo
Usa esta skill al revisar un modelo de Excel creado por otra persona, o al verificar tu propio modelo antes de compartirlo con un cliente o una parte interesada de alto nivel. Actívala cuando un modelo haya sido marcado para revisión de calidad, cuando esté ocurriendo una transferencia de versión, o antes de que un modelo se utilice para tomar una decisión financiera significativa. Es más valiosa como un control de calidad previo a la presentación: ejecutar esta auditoría antes de que un modelo se presente a una junta directiva, un comité de inversiones o un cliente.
Qué hace
Proporciona un proceso de auditoría de modelos sistemático y paso a paso que cubre: las herramientas nativas de auditoría de fórmulas de Excel (Rastrear Precedentes, Rastrear Dependientes, Comprobación de Errores), detección y corrección de valores codificados en fórmulas, identificación de referencias circulares, envoltura consistente de IFERROR y documentación de los hallazgos de auditoría en un registro de revisión. El resultado es un registro de auditoría completo y una lista de problemas priorizados para corregir.
Método
-
Guardar una copia de trabajo antes de auditar. Antes de realizar cualquier cambio, guarda el modelo como un nuevo archivo con "_Audit_YYYY-MM-DD" añadido al nombre. Todas las ediciones durante la auditoría deben realizarse en esta copia. Esto preserva el estado original para comparación.
-
Ejecutar la herramienta de Comprobación de Errores. Ve a Fórmulas > Comprobación de Errores. Excel recorrerá todas las celdas que contienen errores (#N/A, #REF!, #DIV/0!, #VALOR!, #NOMBRE?) y mostrará el error y la fórmula. Para cada error: diagnostica la causa (clave de búsqueda faltante, celda de origen eliminada, tipo de datos incorrecto, rango con nombre faltante), corrígelo y documéntalo en el registro de auditoría con: referencia de celda, tipo de error, causa, corrección aplicada, gravedad (Crítico, Mayor, Menor).
-
Encontrar y resolver referencias circulares. Ve a Fórmulas > Comprobación de Errores > Referencias circulares. Excel listará cualquier celda con dependencias circulares. Las referencias circulares son casi siempre un error en un modelo financiero (excepto en escenarios de cálculo iterativo, que son raros y deben ser explícitamente intencionales). Para cada referencia circular: rastrea la cadena de dependencia, identifica el bucle y rómpelo reestructurando la lógica de la fórmula. Documenta cada referencia circular encontrada.
-
Usar Rastrear Precedentes para verificar la lógica de la fórmula. Para cada fila de cálculo importante (ingresos, EBITDA, FCF, valor patrimonial, TIR), haz clic en la celda y presiona Ctrl+[ o ve a Fórmulas > Rastrear Precedentes. Esto dibuja flechas que muestran qué celdas alimentan esta fórmula. Verifica: (a) la fórmula está tomando datos de las pestañas correctas (pestaña Entradas para supuestos, pestaña Datos para referencias, pestaña Calc correcta para resultados intermedios), (b) no se están referenciando celdas inesperadas, (c) la fórmula no referencia celdas que deberían estar en una pestaña diferente.
-
Usar Rastrear Dependientes para comprobar qué alimenta una celda. Para celdas de salida clave en pestañas Calc, usa Fórmulas > Rastrear Dependientes para ver qué celdas posteriores referencian esta salida. Esto confirma: (a) que las celdas de salida están siendo recogidas correctamente por la pestaña Salidas, (b) que ninguna fórmula posterior inesperada está referenciando una celda que debería ser un resultado intermedio local.
-
Encontrar todos los números codificados en celdas de fórmula. Este es el paso de auditoría más importante para la integridad del modelo. Todos los números codificados (excepto en la pestaña Entradas) son errores del modelo porque no se pueden cambiar fácilmente sin abrir cada fórmula.
Paso: Presiona Ctrl+H (Buscar y Reemplazar). Haz clic en Opciones. Establece "Buscar en" en Fórmulas. Busca números específicos para verificar (prueba con "0.2", "0.3", "0.15", "1000", "12"). Cualquier fórmula que contenga estos números fuera de la pestaña Entradas debe ser investigada.
Mejor enfoque: Usa la función Ir a Especial de Excel (Ctrl+G > Especial > Constantes > Números). Esto selecciona todas las celdas que contienen números codificados. Revisa: cualquier celda de este tipo en una pestaña Calc que no esté en la pestaña Entradas debe ser marcada.
Para cada número codificado encontrado en una fórmula Calc: mueve el supuesto a la pestaña Entradas, asígnale una etiqueta, aplica relleno azul y reemplaza el valor codificado con una referencia de celda a la pestaña Entradas.
-
Auditar la consistencia de IFERROR. Revisa todas las fórmulas de búsqueda (VLOOKUP, INDEX-MATCH, XLOOKUP, MATCH) para verificar que estén envueltas en IFERROR. Una búsqueda sin envolver que devuelve #N/A puede propagarse silenciosamente a través de fórmulas posteriores, produciendo resultados incorrectos.
Para encontrar búsquedas no protegidas: Ctrl+F > busca "=VLOOKUP", "=INDEX(", "=XLOOKUP" sin el prefijo "IFERROR(". Para cada búsqueda no protegida: envuélvela en IFERROR(formula, "Check: [descripción] no encontrado") o IFERROR(formula, 0) según el contexto. Documenta cada búsqueda que fue envuelta.
-
Comprobar la integridad de los rangos con nombre. Ve a Fórmulas > Administrador de Nombres. Para cada rango con nombre: verifica que se refiera a la celda o rango correcto (sin #REF! en la columna "Se refiere a"), verifica que el nombre todavía se use en el modelo (busca el nombre en fórmulas usando Ctrl+F) y elimina cualquier rango con nombre huérfano (nombres que ya no tienen una referencia válida o que ya no se usan).
-
Verificar la convención de signos. Un error común del modelo es una convención de signos inconsistente: algunos elementos positivos, otros negativos, sin una regla establecida. Auditoría: todos los elementos del estado de resultados (ingresos, ganancia bruta, EBITDA) deben ser números positivos. Los costos pueden ser positivos (con resta en la fórmula) o negativos (con suma). Elige una convención y aplícala de manera consistente. Marca cualquier fila donde el signo parezca inconsistente con las filas circundantes.
-
Comprobar las tablas de sensibilidad para referencias correctas de celdas de entrada. En cualquier Tabla de Datos de Excel, la Celda de entrada de fila y la Celda de entrada de columna deben hacer referencia a las celdas exactas en la pestaña Entradas que se están variando. Un error común: las celdas de entrada hacen referencia a una celda diferente a la que realmente se usa en los cálculos del modelo. Para verificar: cambia manualmente el valor de la Celda de entrada de fila y comprueba que la salida del modelo cambie como se espera. Luego ejecuta la Tabla de Datos y verifica que los resultados cambien en consecuencia.
-
Auditar la complejidad de la fórmula. Las fórmulas anidadas largas con IF (más de 2 niveles) son un riesgo de auditoría porque son difíciles de leer, difíciles de depurar y propensas a errores de lógica. Marca cualquier fórmula de más de 150 caracteres o que contenga más de 2 niveles de anidamiento. Para cada fórmula marcada: divídela en filas de cálculo intermedias, nombra claramente cada paso intermedio y reemplaza la fórmula anidada con una secuencia paso a paso más limpia.
-
Completar el registro de auditoría. Registra todos los hallazgos en una tabla de registro de auditoría estructurada en la pestaña Documentación o en una pestaña separada de Registro de Auditoría:
ID de Hallazgo | Pestaña | Celda | Tipo de Problema | Gravedad | Descripción | Estado (Abierto/Corregido) | Corregido por | Fecha de corrección.
Tipos de problema: Valor codificado, Referencia circular, Error de fórmula, IFERROR faltante, Inconsistencia de signo, Rango con nombre roto, Error de tabla de sensibilidad, Complejidad de fórmula.
Gravedad: Crítico (producirá resultados incorrectos en el estado actual), Mayor (producirá resultados incorrectos en ciertas condiciones), Menor (no afecta los resultados pero viola las convenciones).
Resume al inicio del registro: total de hallazgos, cantidad de críticos, cantidad de mayores, cantidad de menores y estado general de la auditoría (Aprobado si cero Críticos, Aprobado Condicional si solo Menores, Reprobado si algún Crítico o Mayor no resuelto).
Entradas
- El modelo de Excel a auditar (nombres de pestañas, descripción del propósito y dónde se encuentran las salidas clave)
- Cualquier problema o inquietud conocido señalado por el constructor del modelo
- La audiencia para el modelo (determina qué tan rigurosa debe ser la auditoría)
- La fecha límite para completar la auditoría
Formato de salida
Una especificación de auditoría completa con:
- Lista de verificación de auditoría: Todos los 12 pasos de auditoría como una lista de verificación verificable con la ruta de navegación de Excel para cada uno.
- Instrucciones de detección de valores codificados: Enfoque paso a paso con Ctrl+H e Ir a Especial.
- Instrucciones de auditoría de IFERROR: La cadena de búsqueda para encontrar búsquedas no protegidas.