Construir un presupuesto anual completo en Excel significa reunir en un solo archivo las 12 columnas de meses, tus fórmulas de variación, tablas dinámicas y gráficas con formato profesional listo para presentar.
El momento en que todo se conecta
Imagina que es lunes por la mañana en la oficina. Tu jefe te pide el presupuesto anual del área para la reunión del mediodía. Tienes los datos dispersos en cinco archivos distintos. El pánico llega antes que las cifras.
Eso le pasa a mucha gente que aprendió las piezas por separado pero nunca las armó juntas. Esta lección es exactamente eso: el ensamblaje final. Vas a construir un archivo que funcione de principio a fin, sin parches ni atajos.
El Sistema de Presupuesto Anual Integrado
Llama a tu método el Sistema PAI (Presupuesto Anual Integrado). Tiene cuatro capas que van de lo más general a lo más detallado.
- Capa 1 – Estructura base: las 12 columnas de meses con categorías de gasto.
- Capa 2 – Motor de fórmulas: SUMA, SI.ERROR, porcentajes de variación.
- Capa 3 – Panel de control: tabla dinámica con resumen ejecutivo.
- Capa 4 – Visual profesional: gráfica de barras agrupadas con formato de pesos.
Cada capa se apoya en la anterior. Si cambias un dato en la Capa 1, las Capas 2, 3 y 4 se actualizan solas.
Paso 1: Construye la estructura base
Abre un libro nuevo. Renombra la primera hoja como Detalle y la segunda como Resumen.
En la hoja Detalle, escribe esta estructura en la fila 1:
A1: Categoría
B1: Enero Presupuesto
C1: Enero Real
D1: Enero Variación %
(repite el patrón para cada mes hasta Diciembre)
Tendrás 37 columnas en total: 1 de categoría más 3 columnas por cada uno de los 12 meses. Congela la columna A con Vista → Inmovilizar paneles para siempre ver las categorías al desplazarte.
En la columna A escribe las categorías reales de tu área. Por ejemplo, para el área de ventas de una empresa como Liverpool:
A2: Nómina
A3: Publicidad digital
A4: Fletes y distribución
A5: Comisiones
A6: Gastos de oficina
A7: Capacitación
A8: TOTAL
Paso 2: Captura los datos de presupuesto y real
En las columnas B (Enero Presupuesto) y C (Enero Real) captura los montos. Usa números simples, sin formato todavía. Por ejemplo:
B2: 85000 C2: 91200
B3: 12000 C3: 9800
B4: 8500 C4: 8500
En la columna D calcula la variación porcentual con esta fórmula en D2:
=SI.ERROR((C2-B2)/B2,0)
Esta fórmula devuelve cero si el presupuesto es cero, evitando el error #¡DIV/0!. Copia D2 hacia abajo hasta D7.
Repite el mismo patrón para los meses de febrero a diciembre. Si ya tienes los datos de Enero listos, selecciona las columnas B, C y D, cópialas y pégalas en las columnas E, F, G (Febrero), H, I, J (Marzo) y así sucesivamente.
Paso 3: Las fórmulas de totales anuales
Al final de la tabla agrega dos columnas extra:
Columna AK: Total Presupuesto Anual
Columna AL: Total Real Anual
En AK2 escribe:
=SUMA(B2,E2,H2,K2,N2,Q2,T2,W2,Z2,AC2,AF2,AI2)
Esta fórmula suma la columna de Presupuesto de cada uno de los 12 meses. Copia hacia abajo para cada categoría. Haz lo mismo en AL2 pero sumando las columnas de Real.
La fila 8 (TOTAL) debe tener:
B8: =SUMA(B2:B7)
Copia esa fórmula en todas las columnas de datos. Así el total de cada mes se calcula automáticamente.
Paso 4: Aplica formato profesional en un solo paso
Selecciona toda la tabla desde A1 hasta la última columna con datos. Presiona Ctrl + T para convertirla en tabla inteligente. Elige un estilo azul o gris oscuro. Ponle el nombre TblDetalle en el cuadro de nombre.
Ahora formatea los números. Selecciona todas las celdas con montos y aplica el formato personalizado:
$#,##0
Eso muestra $85,000 sin decimales, exactamente como se lee en México. Las celdas de variación porcentual aplícales el formato 0.0%.
Usa formato condicional en las celdas de variación. Selecciona D2:D7 y aplica:
- Verde si el valor es menor a 0 (gastaste menos de lo planeado).
- Rojo si el valor es mayor a 0.05 (superaste el presupuesto por más del 5%).
Repite para cada mes.
Paso 5: Construye el panel de control en la hoja Resumen
En la hoja Resumen vas a crear una tabla dinámica conectada a TblDetalle.
Haz clic en cualquier celda de TblDetalle. Ve a Insertar → Tabla dinámica y selecciona que se coloque en la hoja Resumen, celda B3.
Configura la tabla dinámica así:
- Filas: Categoría.
- Valores: Total Presupuesto Anual (suma) y Total Real Anual (suma).
Esto da un resumen limpio con dos columnas: lo que planeaste y lo que gastaste en todo el año.
Debajo de la tabla dinámica agrega tres celdas de indicadores clave:
B15: Variación total anual
C15: =C12-B12 (Real menos Presupuesto del TOTAL)
B16: % de variación
C16: =C15/B12
B17: Estatus
C17: =SI(C16>0.05,"Fuera de control",SI(C16<0,"Bajo presupuesto","En rango"))
Esos tres indicadores son lo que cualquier director o gerente quiere ver en 10 segundos.
Paso 6: Inserta la gráfica de barras agrupadas
Selecciona la tabla dinámica del Resumen. Ve a Insertar → Gráfico recomendado → Barras agrupadas.
Edita la gráfica:
- Título:
Presupuesto vs. Real — Anual [Área de Ventas]. - Eje vertical: empieza en cero, formato
$#,##0. - Leyenda: Presupuesto en azul, Real en naranja.
- Elimina las líneas de cuadrícula secundarias.
Coloca la gráfica directamente en la hoja Resumen, a la derecha de la tabla dinámica. Cuando actualices datos en la hoja Detalle, actualiza la tabla dinámica con clic derecho → Actualizar y la gráfica cambia automáticamente.
Errores comunes al armar el presupuesto anual
Muchos archivos fallan en la última etapa. Estos son los errores más frecuentes:
Error 1: Mezclar datos en la misma hoja. Poner el Detalle y el Resumen en la misma hoja hace que el archivo se vea caótico y sea difícil de proteger por secciones.
Error 2: No usar tabla inteligente. Sin Ctrl + T, agregar una fila nueva de categoría no actualiza la tabla dinámica automáticamente. La tabla inteligente expande todo sola.
Error 3: Capturar montos con texto. Si escribes $85,000 con el signo y la coma directamente en la celda, Excel lo trata como texto. Captura solo el número 85000 y aplica el formato desde la cinta.
Error 4: Olvidar proteger las fórmulas. Antes de compartir el archivo, selecciona las celdas de fórmulas, ve a Formato de celdas → Proteger → Bloqueada y luego activa Revisar → Proteger hoja. Así nadie borra una fórmula por accidente.
Error 5: No guardar como .xlsx. Si guardas como .xls (formato antiguo), pierdes algunas funciones de tabla dinámica y el formato condicional puede comportarse diferente. Siempre usa .xlsx o .xlsm si tienes macros.
Lo que aprendiste en este curso
En nueve lecciones pasaste de una hoja en blanco a un sistema completo. Esto es lo que ya sabes hacer:
- Estructurar un presupuesto con categorías reales de México.
- Usar SUMA, SI.ERROR y fórmulas de variación para automatizar cálculos.
- Aplicar formato condicional para detectar alertas en segundos.
- Crear tablas dinámicas conectadas a datos en vivo.
- Diseñar gráficas de barras agrupadas con criterio profesional.
- Integrar todo en un archivo de dos hojas listo para presentar.
Ese archivo que acabas de construir no es un ejercicio escolar. Es el mismo tipo de herramienta que usan analistas financieros en empresas como FEMSA, Bimbo o Mercado Libre para revisar su desempeño mensual.
Tus próximos pasos
Usa este archivo en tu trabajo esta semana. Sustituye las categorías de ejemplo por las categorías reales de tu área. Captura los datos reales de enero a la fecha. Comparte el Resumen con tu jefe antes de que él te lo pida.
Si quieres ir más lejos, aprende a conectar tu archivo con Power Query para importar datos automáticamente desde otros sistemas. Ese es el siguiente nivel: presupuestos que se actualizan solos sin captura manual.
El mejor presupuesto no es el más bonito, sino el que usas cada semana para tomar decisiones reales.