andreworia/tablas-sensibilidad

Tablas de Sensibilidad (Una y Dos Variables)

Cuándo usar

Usar cuando un modelo de estrategia o finanzas tiene una salida clave y se quiere mostrar cómo esa salida responde al variar uno o dos conductores a lo largo de un rango. Los conductores típicos son precio, volumen, tasa de descuento, crecimiento, churn o costo por unidad. Este skill CONSTRUYE un .xlsx descargable desde cero en la aplicación de Claude (openpyxl más numpy), con fórmulas en vivo y una pasada de recálculo y verificación. No es el complemento Claudia para Excel.

Lo que construye

Una sola pestaña Sensitivity que se adjunta a un modelo existente. Hace referencia a las celdas de entrada y la celda de salida del modelo, dispone cuadrículas de una y dos variables y escala los colores del bloque de resultados. Si el modelo fuente está en otra hoja, la cuadrícula se enlaza a ella con referencias entre hojas para que se mantenga viva.

Flujo de construcción

1. Identificar la celda de salida (por ejemplo Model!B40 que contiene el VAN) y una o dos celdas de entrada que la conducen (por ejemplo Model!B6 tasa de descuento, Model!B7 crecimiento).
2. Confirmar que la salida es una cadena de fórmulas en vivo que vuelve a esas entradas. Si la salida está escrita con valor fijo, detenerse y cablear primero el modelo; una Tabla de Datos sobre una constante devuelve la constante.
3. Elegir rangos alrededor del caso base (por ejemplo tasa de descuento base 10 %, barrer de 6 % a 14 % en pasos de 2).
4. Construir la estructura nativa de Tabla de Datos de Excel: fórmula de esquina, ejes de entrada y la matriz {=TABLE(r,c)} (ver especificación).
5. Construir ADEMÁS la cuadrícula computada por código como respaldo robusto: reevaluar el modelo en Python para cada combinación de entrada y escribir los valores resultantes como una matriz etiquetada.
6. Aplicar una escala de color de 3 tonos al bloque de resultados y etiquetar ambos ejes con los nombres de las celdas de entrada.
7. Agregar el bloque de Verificaciones.
8. Recalcular el libro en un motor real (LibreOffice sin interfaz gráfica).
9. Verificar cero errores de fórmula (#REF!, #DIV/0!, #VALUE!, #NAME?), confirmar que las verificaciones se cumplen, luego entregar el .xlsx.

Especificación pestaña por pestaña

Pestaña Sensitivity, bloque de una variable

Elegir una dirección de diseño. Para entradas que bajan por una COLUMNA:

• B3: la fórmula de salida, referenciando la salida en vivo: =Model!B40. Esta es la celda de esquina.
• A4:A10: los valores de entrada que se barren (entradas azules), por ejemplo 6 %, 8 %, 10 %, 12 %, 14 %.
• B4:B10: la columna de resultados de la Tabla de Datos.
• Seleccionar A3:B10, luego Datos > Análisis de hipótesis > Tabla de datos, dejando vacía la celda de entrada de fila y estableciendo la celda de entrada de columna en Model!B6. Excel escribe {=TABLE(,Model!B6)} en B4:B10.
• openpyxl puede escribir la cadena de fórmula de matriz =TABLE(,Model!B6) en el bloque y el =Model!B40 de esquina, pero los valores solo se llenan después del recálculo en un motor real.

Respaldo computado por código para el mismo bloque (recomendado al generar mediante código), escrito en una matriz etiquetada debajo, por ejemplo comenzando en A14:

• A14: etiqueta "Tasa de descuento". B14: "VAN (computado)".
• Para cada valor de entrada en A15:A21, Python establece Model!B6 a ese valor, recalcula la lógica del modelo en numpy y escribe el VAN resultante como un valor simple en la columna B.
• Mantener ambos: la Tabla de Datos nativa demuestra que está en vivo; la matriz computada garantiza números poblados incluso antes de que el usuario abra Excel.

Pestaña Sensitivity, bloque de dos variables

• Celda de esquina E3: =Model!B40 (la salida en vivo).
• E4:E10: valores de entrada de fila BAJANDO por el borde izquierdo (primer conductor, por ejemplo crecimiento 0 %, 1 %, ... 6 %).
• F3:L3: valores de entrada de columna CRUZANDO la fila superior (segundo conductor, por ejemplo tasa de descuento 6 % ... 14 %).
• Seleccionar E3:L10, Datos > Análisis de hipótesis > Tabla de datos, Celda de entrada de fila = Model!B7 (el conductor horizontal), Celda de entrada de columna = Model!B6 (el conductor vertical). Excel llena F4:L10 con {=TABLE(Model!B7,Model!B6)}.
• Cuidado con la orientación: la celda vinculada a la FILA superior va en Celda de entrada de fila; la celda vinculada a la COLUMNA izquierda va en Celda de entrada de columna. Intercambiarlas transpone silenciosamente el resultado.
• Respaldo computado por código: un bucle doble en Python sobre ambos ejes, escribiendo la matriz de valores en un bloque etiquetado (encabezados de eje en verde si toman del modelo, azul si son valores literales barridos).

Cómo elegir rangos de ejes

• Centrar cada eje en el valor del caso base para que la base quede en la fila y columna centrales, lo que hace significativa la verificación de la celda central.
• Usar un número impar de pasos (5, 7 o 9) para que una sola celda central coincida exactamente con el caso base en lugar de quedar entre dos celdas.
• Mantener tamaños de paso uniformes en cada eje (incremento constante) para que la cuadrícula se lea como un gradiente limpio y la escala de color sea interpretable.
• Para tasas, barrer en puntos absolutos (8 %, 10 %, 12 %), no en porcentajes relativos de la base, para evitar confundir a los lectores.

Notas de construcción con openpyxl

• Escribir la esquina como una cadena de fórmula real: ws["B3"] = "=Model!B40". No pegar el valor numérico actual de la salida.
• Para la matriz nativa de Tabla de Datos, openpyxl puede almacenar el texto =TABLE(...), pero un motor real debe recalcular para llenar el bloque; tratar la matriz como un marcador de posición hasta que se ejecute el paso de recálculo.
• Al escribir el respaldo computado, establecer los valores de datos con Python (ws.cell(row=r, column=c, value=float(npv))) y mantenerlos visualmente distintos del bloque en vivo con una nota de encabezado como "instantánea computada".
• Aplicar la escala de color con openpyxl.formatting.rule.ColorScaleRule sobre el bloque de resultados, mapeando el mínimo a un color y el máximo a otro a través de un punto medio.

Convenciones de fórmula y formato

• Fuente azul para entradas (los valores de eje barrido que se escriben manualmente).
• Fuente negra para fórmulas dentro de la pestaña.
• Fuente verde para enlaces entre pestañas como =Model!B40.
• Sin valores fijos donde corresponde una fórmula: la celda de esquina debe ser =Model!B40, nunca el número del caso base escrito manualmente.
• Una fórmula consistente por fila o columna: todo el bloque de Tabla de Datos es una sola matriz; el respaldo computado usa una expresión de Python aplicada a toda la cuadrícula.
• Formatos de número: tasas como 0.0%, salidas monetarias como #,##0, ratios como 0.00. Los valores de los ejes coinciden con la unidad de la celda de entrada que reemplazan.

Verificaciones

• La celda de esquina es igual a la salida en vivo: =(B3=Model!B40) devuelve VERDADERO.
• Los ejes etiquetados con los nombres de las celdas de entrada (una etiqueta de texto encima o al lado de cada eje que indica el conductor y su dirección de celda).
• El centro de la cuadrícula coincide con el caso base: la celda de la cuadrícula en la fila y columna del caso base es igual a la salida actual del modelo, =(<celda_centro>=Model!B40) VERDADERO.
• Para el respaldo computado, la celda en las entradas base es igual a la celda de la Tabla de Datos nativa en las mismas entradas (dentro del redondeo).

Recalcular y verificar

Guardar con openpyxl, luego recalcular sin interfaz gráfica (por ejemplo libreoffice --headless --convert-to xlsx) para que =TABLE(...) y los enlaces entre hojas se llenen. Recargar y escanear cada celda en busca de #REF!, #DIV/0!, #VALUE!, #NAME?. Confirmar que las tres verificaciones devuelvan VERDADERO. Si aparece algún error o FALSO, corregir la referencia o el rango y repetir hasta que esté limpio, luego entregar.

Entradas a recopilar

• Ruta al modelo fuente y la dirección exacta de la celda de salida.
• Una o dos direcciones de celda de entrada para barrer.
• Valores del caso base y el rango y paso deseados para cada eje.
• Unidades y formatos de número preferidos para los ejes y la salida.

Ejemplo

Modelo hipotético: Model!B40 = VAN del proyecto, impulsado por Model!B6 tasa de descuento (base 10 %) y Model!B7 crecimiento de ingresos (base 3 %). El bloque de una variable barre la tasa de descuento del 6 % al 14 % en incrementos del 2 %, dando un VAN de (por ejemplo) 1.210 / 980 / 760 / 560 / 380 en unidades hipotéticas. El bloque de dos variables cruza el crecimiento del 0 % al 6 % contra la tasa de descuento del 6 % al 14 %; la celda central en crecimiento 3 % y tasa de descuento 10 % es igual a Model!B40 de 760, confirmando que el caso base está en el medio de la cuadrícula.