Cómo hacer un análisis de varianza en Excel: paso a paso

Para realizar un análisis de varianza en Excel, coloque las cifras del presupuesto y las reales una al lado de la otra para cada partida. Reste el presupuesto de lo real para obtener la varianza en dólares. Divida esa varianza por el presupuesto para obtener el porcentaje. Luego, marque cada línea como favorable o desfavorable y explique las brechas más grandes.
Esta guía recorre las fórmulas, el diseño de la hoja de trabajo y un ejemplo práctico completo. También cubre cómo graficar el resultado y cómo dividir una brecha de ingresos en precio y volumen. Los pasos de Excel provienen de las páginas de soporte de Microsoft leídas el 10 de noviembre de 2026.
Qué muestra un análisis de varianza
Una varianza es la brecha entre lo que planeó y lo que sucedió. Un análisis de varianza enumera esas brechas línea por línea y explica las que importan.
La mayoría de los equipos comparan los resultados reales con una de estas tres líneas base:
- Presupuesto. El plan establecido al inicio del año.
- Pronóstico. Un plan actualizado establecido durante el año.
- Periodo anterior. El mes pasado, el trimestre pasado o el mismo periodo del año anterior.
La propia habilidad de Copilot de Microsoft para esta tarea describe bien el trabajo. Su habilidad de análisis de varianza "Compara los resultados reales con presupuestos, pronósticos o periodos anteriores y explica las diferencias por precio, volumen, mezcla, tasa o tiempo".
El resultado suele ser una tabla más algunas notas escritas. La tabla muestra cada brecha. Las notas explican las tres o cuatro sobre las que los líderes deben actuar.
Las fórmulas principales
Tres fórmulas realizan la mayor parte del trabajo. Suponga que el presupuesto está en la columna B y lo real en la columna C.
| Medida | Fórmula | Qué le indica |
|---|---|---|
| Varianza en dólares | =C2-B2 | Qué tan lejos está lo real del presupuesto, en moneda |
| Varianza porcentual | =IFERROR((C2-B2)/ABS(B2),"") | Qué tan grande es la brecha en relación con el plan |
| Estado | =IF(D2=0,"En plan",IF(D2>0,"Sobre el plan","Bajo el plan")) | En qué dirección se movió la línea |
La función ABS mantiene el signo correcto cuando un presupuesto es negativo. IFERROR deja la celda en blanco cuando el presupuesto es cero, en lugar de mostrar un error.
La dirección no es lo mismo que bueno o malo. Los ingresos por encima del plan son buenas noticias. Los costos por encima del plan son malas noticias. Las siguientes secciones muestran cómo manejar eso.
Cómo configurar la hoja de trabajo
Un diseño limpio facilita la copia de las fórmulas. Use una fila por partida y estas columnas:
- Partida. Ingresos, costo de bienes vendidos, nómina, marketing, etcétera.
- Tipo. Ingrese "Ingreso" o "Costo" para que Excel sepa qué dirección es buena.
- Presupuesto. El monto planeado para el periodo.
- Real. El monto de su exportación contable.
- Varianza. Real menos presupuesto.
- % de Varianza. Varianza dividida por el presupuesto.
- Favorable o desfavorable. Una etiqueta basada en el tipo y la dirección.
- Explicación. Una nota corta para las líneas materiales.
Mantenga el presupuesto y lo real para el mismo periodo y el mismo mapeo de cuentas. Un análisis de varianza es tan bueno como esa coincidencia. Si la nómina se divide en dos cuentas en el presupuesto, divídala de la misma manera en los reales.
Formatee la tabla como una tabla de Excel con Ctrl+T. Las nuevas filas tomarán las fórmulas automáticamente.
Cómo calcular varianzas en Excel
Con el diseño en su lugar, los pasos son cortos. Suponga que las columnas anteriores comienzan en la fila 2, con Tipo en B, Presupuesto en C y Real en D.
- En E2, ingrese =D2-C2 para la varianza en dólares.
- En F2, ingrese =IFERROR((D2-C2)/ABS(C2),"") y dele formato de porcentaje.
- En la columna de Estado, ingrese la prueba de favorable que se muestra a continuación.
- Copie las fórmulas en cada fila.
- Agregue una fila de total y calcule su varianza a partir de los totales, no sumando porcentajes.
Aquí está la prueba de favorable para la fila 2 de la columna de Estado:
=IF(E2=0,"En plan",IF(B2="Ingreso",IF(E2>0,"Favorable","Desfavorable"),IF(E2<0,"Favorable","Desfavorable")))
Lee primero la columna Tipo. Para los ingresos, una varianza positiva es favorable. Para los costos, una varianza negativa es favorable porque gastó menos de lo planeado.
Los porcentajes requieren cuidado en la fila de total. Sumar los porcentajes de las líneas da una respuesta incorrecta. Divida la varianza total por el presupuesto total en su lugar.
Cómo marcar las varianzas que importan
No todas las brechas necesitan una nota. La mayoría de los equipos establecen una regla de materialidad, como "más del 5% y más de $2,000". Las líneas que pasan ambas pruebas reciben una explicación.
Una fórmula simple las marca. Agregue una columna Material e ingrese =AND(ABS(F2)>0.05,ABS(E2)>2000) en la fila 2. La celda mostrará TRUE para las líneas materiales.
El color hace que la tabla sea más rápida de escanear. El formato condicional de Excel puede agregar conjuntos de iconos. Microsoft dice que un conjunto de iconos puede "anotar y clasificar datos en tres a cinco categorías separadas por un valor de umbral".
Para agregar uno, seleccione la columna % de Varianza. Luego vaya a Inicio > Formato condicional > Conjuntos de iconos. Microsoft señala que en el conjunto de 3 flechas, "la flecha verde hacia arriba representa valores más altos". Para las líneas de costo, invierta el orden de los iconos para que un aumento se muestre como una advertencia.
Cómo explicar los impulsores
Los números muestran dónde está la brecha. Los impulsores explican por qué. La descripción de la habilidad de Microsoft enumera cinco impulsores comunes: precio, volumen, mezcla, tasa y tiempo.
Precio. Vendió o compró a un precio diferente al planeado. Varianza de precio = (precio real − precio presupuestado) × unidades reales.
Volumen. Vendió o usó una cantidad diferente. Varianza de volumen = (unidades reales − unidades presupuestadas) × precio presupuestado.
Mezcla. La participación de productos, regiones o clientes cambió. Un cambio hacia artículos de menor margen reduce las ganancias incluso si el volumen se mantiene.
Tasa. Una tasa de costo cambió, como un salario por hora o una tarifa de proveedor.
Tiempo. El dinero se movió entre periodos. Una factura de marzo pagada en abril se muestra como una brecha en ambos meses.
El precio y el volumen juntos explican la brecha total de ingresos. Vale la pena hacer esa comprobación cada vez, ya que confirma los cálculos.
Un ejemplo de análisis de varianza
Aquí hay un ejemplo práctico para una empresa ficticia, Cedar and Pine Coffee. Su responsable de finanzas compara los resultados reales del tercer trimestre con el presupuesto.
| Partida | Tipo | Presupuesto | Real | Varianza | % de Varianza | Estado |
|---|---|---|---|---|---|---|
| Ingresos | Ingreso | $250,000 | $236,500 | −$13,500 | −5.4% | Desfavorable |
| Costo de bienes vendidos | Costo | $100,000 | $97,200 | −$2,800 | −2.8% | Favorable |
| Nómina | Costo | $60,000 | $64,800 | $4,800 | 8.0% | Desfavorable |
| Marketing | Costo | $20,000 | $26,000 | $6,000 | 30.0% | Desfavorable |
| Alquiler | Costo | $15,000 | $15,000 | $0 | 0.0% | En plan |
| Utilidad operativa | $55,000 | $33,500 | −$21,500 | −39.1% | Desfavorable |
El total cuadra. Los ingresos perdieron $13,500, el costo de bienes vendidos ahorró $2,800, la nómina costó $4,800 más y el marketing costó $6,000 más. Juntos, eso es −$21,500, lo que coincide con la brecha de utilidad.
Tres líneas pasan la regla de materialidad de más del 5% y más de $2,000: ingresos, nómina y marketing. El costo de bienes vendidos se movió solo un 2.8%, por lo que no recibe nota.
La brecha de ingresos necesita una mirada más cercana. El presupuesto asumía 50,000 unidades a $5.00. El trimestre vendió 43,000 unidades a $5.50.
- Varianza de volumen. (43,000 − 50,000) × $5.00 = −$35,000.
- Varianza de precio. ($5.50 − $5.00) × 43,000 = +$21,500.
- Total. −$35,000 + $21,500 = −$13,500, lo que coincide con la línea de ingresos.
Así que el aumento de precio se mantuvo, pero costó volumen. La nota para los líderes dice exactamente eso. Marketing gastó un 30% más de lo planeado, pero las unidades cayeron. Eso apunta a una pregunta sobre qué campañas funcionaron.
La nómina necesita su propia comprobación. La brecha de $4,800 podría ser un impulsor de tasa, como un aumento salarial, o un impulsor de volumen, como horas extras. El responsable de finanzas pregunta al gerente de operaciones y se entera de que dos tiendas agregaron turnos de fin de semana. Eso lo convierte en una brecha de volumen ligada a las horas de apertura, no en un aumento salarial.
El resumen final para los líderes cabe en una línea. La utilidad quedó $21,500 por debajo del presupuesto, ya que un aumento de precio costó más volumen del que ganó, y el marketing y los turnos de fin de semana superaron el plan.
Cómo escribir las notas de varianza
La tabla es solo la mitad de un análisis de varianza. Los líderes leen las notas, por lo que cada una debe responder a tres preguntas: qué se movió, por qué se movió y qué sucede a continuación.
Una buena nota sigue un patrón simple:
- Qué. "Marketing gastó $6,000 sobre el presupuesto, un 30.0% por encima del plan".
- Por qué. "Dos campañas pagadas duraron más de lo planeado".
- Siguiente. "Pausamos una campaña el 3 de octubre y esperamos que el Q4 termine según el plan".
Mantenga cada nota en dos o tres oraciones. Use el mismo orden de líneas que la tabla, para que los lectores puedan relacionar las notas con los números.
Separe las brechas puntuales de las tendencias. Una factura de reparación única es diferente de un aumento salarial que se repetirá cada mes. Marque claramente las brechas recurrentes, ya que cambian el pronóstico.
Escriba las notas con el responsable de cada línea. El equipo de finanzas ve los números, pero el dueño del presupuesto conoce la razón. Una breve consulta con cada responsable convierte una suposición en una explicación.
Finalmente, agregue un resumen de una línea en la parte superior. Para el ejemplo anterior, diría: "La utilidad operativa del Q3 quedó $21,500 por debajo del presupuesto, principalmente por un menor volumen y un mayor gasto en marketing".
El tiempo importa tanto como la redacción. La mayoría de los equipos realizan un análisis de varianza cada mes, después del cierre de libros. Un ritmo mensual detecta problemas mientras aún hay tiempo para actuar.
Las revisiones trimestrales son más profundas. Agregan el desglose de precio y volumen, un nuevo pronóstico y una revisión de si el presupuesto en sí todavía tiene sentido. Las revisiones de fin de año analizan el año completo frente al plan original.
Mantenga la misma plantilla cada vez. Cuando las columnas y las reglas permanecen fijas, un análisis de varianza toma menos tiempo cada mes y las tendencias son más fáciles de ver a través de los periodos.
Cómo graficar las varianzas
Un gráfico ayuda a los líderes a ver el camino desde el presupuesto hasta lo real. El que mejor se adapta es un gráfico de cascada.
Microsoft dice que un gráfico de cascada "muestra un total acumulado a medida que se agregan o restan valores". Agrega que "los gráficos de cascada también se llaman gráficos de puente".
Para construir uno, enumere los pasos desde la utilidad presupuestada hasta la utilidad real. Comience con $55,000, luego agregue el efecto de cada línea en la utilidad y termine con $33,500. Seleccione los datos y elija Insertar > Insertar cascada > Cascada.
Establezca la primera y la última barra como totales. La página de Microsoft señala que las columnas de inicio y fin "a menudo comienzan en el eje horizontal", mientras que las barras intermedias flotan. Los aumentos y disminuciones están codificados por colores, por lo que los impulsores más grandes destacan.
Para el seguimiento mes a mes, un gráfico de columnas simple de % de varianza también funciona. Nuestra guía sobre cómo construir un informe de presupuesto vs. real cubre el informe mensual completo en torno a esta tabla.
Realice un análisis de varianza con AI
Las fórmulas toman minutos una vez que la tabla existe. La parte lenta suele ser mapear cuentas, encontrar impulsores y escribir las notas. Una herramienta de AI puede redactar esos pasos a partir de sus exportaciones.
Aquí está el método en tres pasos, usando Powerdrill Bloom.
Paso 1: Cargue su presupuesto y sus reales
Exporte el presupuesto y los reales de su herramienta de contabilidad o planificación. La AI Budget Analysis Tool acepta "exportaciones en Excel, CSV o PDF de contabilidad y FP&A".
Incluya datos de unidades y precios si desea un desglose de precio y volumen. Mantenga los nombres de las cuentas iguales en ambos archivos.
Paso 2: Pida las varianzas e impulsores en lenguaje natural
Describa el resultado que necesita. Por ejemplo: "Compara los reales del Q3 con el presupuesto por partida. Marca las líneas de más del 5% y $2,000, y divide la brecha de ingresos en precio y volumen".
La página de la herramienta enumera "Varianza por partida con umbrales de materialidad aplicados" como una vista. También enumera un libro de trabajo de nuevo pronóstico con "presupuesto original, reales acumulados al año y fin de año revisado".
Paso 3: Verifique los números y exporte el informe
Compare los totales con su propia hoja antes de compartir nada. Luego exporte el resultado. La página de Budget Analysis enumera un "Tablero de varianza con líneas materiales resaltadas e impulsores adjuntos".
Para una vista continua, el AI Finance Dashboard Builder cubre P&L, efectivo, capital de trabajo y pronóstico. Pruebe Powerdrill Bloom con el presupuesto y los reales del último trimestre.
Errores comunes a evitar
Algunos errores aparecen en casi todos los primeros análisis de varianza.
- Confundir el signo. Decida una vez si la varianza es real menos presupuesto y úselo en todas partes.
- Llamar buena a cada brecha positiva. Los costos más altos son desfavorables aunque el número sea positivo.
- Sumar porcentajes. Recalcule los porcentajes totales a partir de los dólares totales.
- Comparar periodos diferentes. Un real acumulado al año frente a un presupuesto mensual da una brecha falsa.
- Ignorar el tiempo. Una factura atrasada puede crear dos brechas opuestas en dos meses.
- Explicarlo todo. Las notas sobre brechas pequeñas ocultan las que importan.
- Escribir números a mano. Pegue o vincule los reales de la exportación, para que un error tipográfico no cree una brecha falsa.
- Omitir la comprobación del total. Las varianzas de las líneas deben sumar la varianza total. Si no es así, falta una fila o se mapeó dos veces.
Preguntas frecuentes
¿Cuál es la fórmula para la varianza en Excel?
Para la varianza presupuestaria, use real menos presupuesto, como =C2-B2. Para el porcentaje, use =IFERROR((C2-B2)/ABS(B2),"") y formatee la celda como porcentaje.
¿Cómo sé si una varianza es favorable?
Depende del tipo de línea. Para los ingresos, lo real por encima del presupuesto es favorable. Para los costos, lo real por debajo del presupuesto es favorable. Una columna Tipo y una fórmula IF pueden etiquetar cada fila.
¿Qué es una varianza material?
Es una brecha lo suficientemente grande como para ser explicada. Los equipos establecen su propia regla, como más del 5% y más de $2,000. Las líneas que pasan ambas pruebas reciben una nota escrita.
¿Qué gráfico es mejor para un análisis de varianza?
Un gráfico de cascada, también llamado gráfico de puente, muestra cómo cada línea mueve el total desde el presupuesto hasta lo real. Excel lo tiene en Insertar > Insertar cascada.
¿Puede Copilot en Excel hacer un análisis de varianza?
Microsoft enumera una habilidad de análisis de varianza para Copilot en Excel. Compara los reales con presupuestos, pronósticos o periodos anteriores y redacta explicaciones para su revisión. Requiere una licencia de Copilot.
Fuentes: Soporte de Microsoft, Use habilidades integradas con Copilot en Excel · Soporte de Microsoft, Crear un gráfico de cascada · Soporte de Microsoft, Usar formato condicional para resaltar información en Excel.