01 / 10El Informe Decía 21.950 £ por Encima

Análisis de DesviacionesInformesFormato Condicional

Presupuesto frente a real en Excel: desviación favorable

El Informe de Octubre Decía que las Clínicas Iban 21.950 £ por Encima del Presupuesto y Estaban 15.750 £ por Debajo, Porque Real Menos Presupuesto Convierte un Sobrecoste en una Buena Noticia

1 nov 20268 min de lectura

UsaSISIGNOABSSUMAR.SI.CONJUNTO

El informe de octubre de Larkfield Vets decía que el mes había cerrado 21.950 £ por encima del presupuesto, en verde. Había cerrado 15.750 £ por debajo. Una sola fórmula, real menos presupuesto, había puntuado cada sobrecoste como una buena noticia.

Octubre de 2026Cifra
Desviación neta en el informe+21.950
Desviación real del beneficio−15.750
Sobrecoste de sustitutos en verde18.400
Líneas con el color equivocado6 de 10
Beneficio presupuestado87.700
Beneficio real71.950

Alcance. SI, ABS, SIGNO, SUMAR.SI.CONJUNTO y SUMAPRODUCTO funcionan igual en todas las versiones de Excel para Windows, Mac y la web.

En todo el artículo la hoja está en A1:G12: línea en A, tipo en B, presupuesto en C y real en D. La columna B dice Revenue o Cost, tal como sale del sistema contable. Las filas 2 a 11 son las diez líneas y la 12 es el total.

01El Informe Decía 21.950 £ por Encima

La gerente construyó la columna de desviación una vez y la rellenó hacia abajo. Restaba el presupuesto del real en todas las líneas, y un formato condicional pintaba de verde los positivos y de rojo los negativos.

=D2-C2

El total del pie, =SUMA(E2:E11), marcaba +21.950 en verde. Nadie preguntó cómo un mes con cirugía por debajo de lo previsto podía superar tanto su presupuesto.

Diez Líneas, Una Resta, Seis Colores Equivocados

La hoja de presupuesto frente a real de octubre de Larkfield Vets, en A1:G12. La columna B indica si cada línea es de ingresos (Revenue) o de costes (Cost), C guarda el presupuesto y D el real. La columna E es lo que mostraba el informe, real menos presupuesto, y G esa cifra como porcentaje del presupuesto. La columna F es la desviación con signo según el tipo de línea, positiva cuando la línea ayudó al beneficio. La fila 12 suma E y F.

ABCDEFG
1
Line
Type
Budget
Actual
Actual − Budget
Signed variance
Reported %
2
Consultations
Revenue
142000
148600
6600
6600
4.6%
3
Surgery
Revenue
96000
89200
-6800
-6800
-7.1%
4
Pharmacy sales
Revenue
54000
57300
3300
3300
6.1%
5
Vet salaries
Cost
118000
116500
-1500
1500
-1.3%
6
Locum cover
Cost
12000
30400
18400
-18400
153.3%
7
Drugs & consumables
Cost
41000
44900
3900
-3900
9.5%
8
Rent & rates
Cost
22500
22500
0
0
0.0%
9
Utilities
Cost
6800
6100
-700
700
-10.3%
10
Marketing
Cost
4000
0
-4000
4000
-100.0%
11
Equipment repairs
Cost
0
2750
2750
-2750
#DIV/0!
12
Net variance
21950
-15750

fxLas celdas con fórmulas están resaltadas en verde

Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas

Los socios leyeron el total verde y mantuvieron la agencia de sustitutos durante noviembre. La línea de sustitutos, 18.400 £ por encima de su presupuesto, era la cifra más verde de la página.

02Una Resta No Puede Puntuar Ambos Tipos de Línea

Real menos presupuesto no opina. Que una cifra positiva sea buena depende de qué línea sea. Más ingresos de los previstos ayudan al beneficio; más costes de los previstos lo dañan.

LíneaReal − presupuestoEfecto en el beneficio
Consultas (ingreso)+6.600Ayuda
Sustitutos (coste)+18.400Daña
Sueldos (coste)−1.500Ayuda
Cirugía (ingreso)−6.800Daña

Las tres líneas de ingresos salieron bien por suerte: en los ingresos, real menos presupuesto apunta en la dirección correcta. Cada línea de coste que se movió salió al revés.

Escenario: Pon las diez líneas en A2:D11 y escribe =D2-C2 en E2, rellenándolo hacia abajo. Lee E6, la de sustitutos, y E5, la de sueldos, y decide cuál de las dos debería haber preocupado a los socios.

Pruébalo en la cuadrícula

03Da Signo a Cada Desviación Según su Tipo

Dale a cada desviación un único significado: positiva ayuda al beneficio, negativa lo daña. En ingresos es real menos presupuesto; en costes, presupuesto menos real. Un SI lee el tipo de la columna B y elige el orden:

=SI(B2="Cost";C2-D2;D2-C2)

Lo mismo escrito como multiplicador de signo es =(D2-C2)*SI(B2="Cost";-1;1). De cualquiera de las dos formas, la línea de sustitutos pasa a −18.400 y la de sueldos a +1.500.

LíneaReal − presupuestoDesviación con signo
Sueldos−1.500+1.500
Sustitutos+18.400−18.400
Fármacos y consumibles+3.900−3.900
Marketing−4.000+4.000

La contabilidad de gestión tiene nombre para esto: favorable para una desviación con signo positiva y desfavorable para una negativa. Pon las palabras en el encabezado de la columna para que nadie tenga que adivinar.

Escenario: Escribe =SI(B2="Cost";C2-D2;D2-C2) en F2 y rellénalo hasta F11. Comprueba que F6 da −18.400 y F5 da 1.500; después escribe "Revenue" sobre "Cost" en B5 y mira cómo F5 cambia de signo.

Pruébalo en la cuadrícula

04Con Signo, Suman la Desviación del Beneficio

La prueba de fuego de una columna de desviaciones es su total. Las desviaciones con signo suman el beneficio real menos el presupuestado. Las brutas suman ganancias de ingresos y sobrecostes, y dan un número que no significa nada.

=SUMA(F2:F11)                                     → −15.750
=SUMAR.SI.CONJUNTO(D2:D11;B2:B11;"Revenue")
 -SUMAR.SI.CONJUNTO(D2:D11;B2:B11;"Cost")          → 71.950
=SUMAR.SI.CONJUNTO(C2:C11;B2:B11;"Revenue")
 -SUMAR.SI.CONJUNTO(C2:C11;B2:B11;"Cost")          → 87.700
ComprobaciónCifra
Desviación de ingresos+3.100
Desviación de costes−18.850
Total con signo−15.750
Beneficio real − presupuestado−15.750
Total bruto del informe+21.950

El +21.950 del informe es la suma de unos ingresos 3.100 por encima y unos costes 18.850 por encima. Suma una ganancia y una pérdida como si las dos fueran ganancias.

Escenario: Escribe =SUMA(F2:F11) en F12 y las dos fórmulas de beneficio con SUMAR.SI.CONJUNTO en celdas libres. Resta el beneficio presupuestado del real y confirma que coincide con F12 hasta la libra.

Pruébalo en la cuadrícula

05Cuenta las Líneas con el Color Equivocado

Antes de rehacer un informe, averigua cuánto de él estaba mal. SIGNO devuelve 1, −1 o 0, así que comparar el signo de la columna bruta con el de la columna con signo encuentra cada línea cuyo color se invirtió.

=SUMAPRODUCTO(--(SIGNO(E2:E11)<>SIGNO(F2:F11)))

En la hoja de octubre devuelve 6. Entran todas las líneas de coste que se movieron; alquiler y tasas cuadró exactamente con el presupuesto y no entra.

Las tres líneas de ingresos coinciden, y por eso el informe parecía creíble.

Escenario: Escribe =SUMAPRODUCTO(--(SIGNO(E2:E11)<>SIGNO(F2:F11))) junto a la hoja y comprueba que devuelve 6. Después cambia el real de alquiler y tasas a 23.000 y mira cómo el recuento sube a 7.

Pruébalo en la cuadrícula

06Los Porcentajes Necesitan ABS y Comprobar el Cero

Una desviación porcentual divide la desviación con signo entre el presupuesto. Hay dos líneas que rompen la división simple: una sin presupuesto y otra con presupuesto negativo.

=SI(C2=0;"n/d";F2/ABS(C2))

Las reparaciones de equipos tenían presupuesto cero, así que cualquier división devuelve #¡DIV/0!. El SI escribe "n/d" en su lugar, y las 2.750 £ siguen visibles en la columna de desviación, que es donde deben estar.

ABS importa en una línea presupuestada como pérdida. Presupuesta una pérdida por venta de activos de −1.500, pierde −600, y la desviación es +900 favorable.

Dividida entre −1.500 da −60%; dividida entre el ABS del presupuesto da +60%.

LíneaDesviación con signoPresupuesto% con ABS
Sustitutos−18.40012.000−153,3%
Marketing+4.0004.000+100,0%
Reparaciones−2.7500n/d

Escenario: Escribe =SI(C2=0;"n/d";F2/ABS(C2)) en H2, rellénalo hacia abajo y dale formato de porcentaje. Comprueba que H11 dice n/d y que H6 da −153,3%.

Pruébalo en la cuadrícula

07Colorea la Columna con Signo, No la Bruta

Cuando todas las desviaciones significan lo mismo, una sola regla de formato condicional sirve para todas las líneas. Selecciona E2:H11 y añade una regla con fórmula que mire solo la columna con signo:

=$F2<0

Dale formato rojo y añade una segunda regla, =$F2>0, en verde. El $ delante de la F hace que cada celda de la fila mire su propia desviación con signo.

Una etiqueta de texto ayuda a quien lea el informe impreso en blanco y negro:

=SI(F2>0;"Fav";SI(F2<0;"Desf";"En presupuesto"))

Escenario: Borra la regla antigua de la columna E y añade =$F2<0 como regla roja sobre E2:H11. Comprueba que la fila de sustitutos se pone roja y la de sueldos no.

Pruébalo en la cuadrícula

08Seis Trampas

  1. Una sola resta para todas las líneas. =D2-C2 puntúa bien los ingresos y al revés los costes.
  2. Colorear la columna bruta. Un sobrecoste en verde es el número equivocado más convincente de una página.
  3. Sumar las desviaciones brutas. La suma junta ganancias y pérdidas y no coincide con ningún beneficio.
  4. Dividir entre un presupuesto cero. Las líneas sin presupuesto devuelven #¡DIV/0! y esconden su sobrecoste.
  5. Dividir entre un presupuesto negativo. Una pérdida presupuestada que se reduce muestra un porcentaje negativo.
  6. Tipos de línea tecleados. "Cost " con un espacio final no cumple B2="Cost" y se puntúa como ingreso.

09Cuatro Ejercicios

  1. Reprodúcelo. Construye la columna bruta y confirma que el total da +21.950.
  2. Dale signo. Añade la columna con SI y comprueba que F12 da −15.750.
  3. Demuéstralo. Cuadra F12 con el beneficio real menos el presupuestado usando SUMAR.SI.CONJUNTO.
  4. Audítalo. Cuenta con SIGNO las líneas invertidas y añade la columna de porcentaje con ABS.

Qué Llevarse

Cada número de la hoja de Larkfield era correcto. El presupuesto, los reales y la resta estaban bien. El signo significaba cosas distintas en líneas distintas, y el color se fió de él.

Decide una vez que positivo significa bueno para el beneficio, mételo en la desviación con el tipo de línea y demuestra la columna cuadrando su total con el beneficio.

Comparte este artículo:
Volver al Blog

Practícalo

SI

Un ejercicio en una cuadrícula real

Abrir el ejercicio

Las funciones que usa este artículo

Sintaxis, ejemplos resueltos y los errores que esperar: una página de referencia para cada una.

Sigue leyendo

  1. Promedio de promedios en Excel: por qué tu porcentaje fallaLeer el artículo
  2. Cómo detectar valores fijos en fórmulas de ExcelLeer el artículo
  3. Sumar horas en Excel: más de 24 y horas extraLeer el artículo
Reto diario · Día 107

Suma los kilómetros de un periodo de liquidación con SUMAR.SI.CONJUNTO

Un ejercicio nuevo cada día, resuelto en una cuadrícula real.

Resolver el reto de hoy