Ejercicio Integrador: Dashboard de Control de Gestión

Al finalizar esta lección serás capaz de:

• Integrar atajos de teclado, formato condicional, validación y tablas estructuradas en un proyecto completo
• Construir un dashboard de control de gestión funcional desde cero
• Aplicar un flujo de trabajo profesional para proyectos de Excel empresariales
• Documentar y proteger tu trabajo para uso en equipo

Vas a construir un libro de Excel completo para una empresa ficticia "Distribuidora del Sur S.A." que necesita controlar su operación mensual. Este proyecto integra todo lo aprendido en el Módulo 1.

Especificaciones del Dashboard

Empresa: Distribuidora del Sur S.A.

• 4 departamentos: Ventas, Logística, Administración, Marketing
• 20 empleados
• 50 productos en 5 categorías
• Operaciones en 3 regiones: Buenos Aires, Litoral, Cuyo

Antes de abrir Excel, planificá la estructura. Un error común es empezar a escribir datos sin pensar en la arquitectura.

Estructura de Hojas

📊 Dashboard_Control_Gestión.xlsx
├── 📋 Dashboard        → Vista principal con KPIs y gráficos
├── 📋 Ventas           → Tabla de transacciones de ventas
├── 📋 Gastos           → Tabla de gastos operativos
├── 📋 Empleados        → Tabla de personal y rendimiento
├── 📋 Productos        → Catálogo de productos
├── 📋 Listas           → Valores para validación (departamentos, categorías, regiones)
├── 📋 Config           → Constantes (IVA, tipo de cambio, metas)
└── 📋 _Info            → Documentación del libro

Paso 1: Crear la Hoja "Config" con Nombres de Rango

Empezá siempre por las constantes. En la hoja Config:

Creá cada nombre con Fórmulas > Definir nombre. El ámbito debe ser "Libro" para que funcionen en todas las hojas.

¿Por qué esto es clave? Si la tasa de IVA cambia de 21% a 22%, modificás UNA celda y todas las fórmulas del libro se actualizan. Sin nombres de rango, tendrías que buscar y reemplazar en decenas de fórmulas.

Paso 2: Crear la Hoja "Listas"

Convertí cada lista en una tabla estructurada para que sean dinámicas:

Tabla: Departamentos

Ctrl + T → nombrar tabla ListaDepartamentos

Tabla: Regiones

Ctrl + T → nombrar tabla ListaRegiones

Tabla: CategoríasProducto

Ctrl + T → nombrar tabla ListaCategorias

Tabla: EstadosPedido

Ctrl + T → nombrar tabla ListaEstados

Tabla de Ventas

En la hoja "Ventas", creá la tabla Ventas (Ctrl + T) con estas columnas:

Cargá al menos 50 registros de ejemplo con datos realistas. Variá fechas, regiones, productos y montos para tener datos interesantes de analizar.

Truco para generar datos rápido: Usá fórmulas auxiliares temporales:

=ELEGIR(ALEATORIO.ENTRE(1,3),"Buenos Aires","Litoral","Cuyo")
=FECHA(2025,ALEATORIO.ENTRE(1,12),ALEATORIO.ENTRE(1,28))
=ALEATORIO.ENTRE(1,100)*100

Generá los datos, copialos y pegá como valores (Ctrl + Shift + V > Valores) para que no cambien.

Tabla de Gastos

En la hoja "Gastos", creá la tabla Gastos:

Tabla de Empleados

En la hoja "Empleados", creá la tabla Empleados:

Ahora aplicá formato condicional a las tablas para visualización inmediata.

En la Tabla de Ventas

1. Barras de datos en la columna Total → visualización rápida de magnitud

2. Semáforo en Estado:

   • 🟢 "Entregado" → fondo verde
   • 🟡 "En proceso" → fondo amarillo
   • 🔴 "Pendiente" → fondo naranja
   • ⚫ "Cancelado" → fondo gris, texto tachado

   Fórmula para colorear fila completa según estado:
   =$L2="Cancelado" → formato gris con tachado

3. Descuentos altos: Si descuento > 30%, resaltar en naranja (descuentos excesivos)
   Fórmula: =$H2>0.30

4. Fecha de entrega vencida: Si FechaEntrega < HOY() y Estado ≠ "Entregado":
   Fórmula: =Y($M2<HOY(),$L2<>"Entregado")
   Formato: borde rojo + texto rojo

En la Tabla de Gastos

1. Escala de color en Monto → mapa de calor de gastos
2. No aprobados: Si Aprobado = "No", toda la fila en rojo claro
3. Gastos grandes: Si Monto > 100000, negrita + borde

En la Tabla de Empleados

1. Antigüedad: Si FechaIngreso es hace más de 5 años, resaltar verde (empleados leales)
   Fórmula: =SIFECHA($E2,HOY(),"Y")>=5
2. Cumplimiento de meta: Para vendedores, comparar ventas acumuladas vs meta individual

La hoja Dashboard es donde todo se une. Acá van los KPIs y resúmenes.

KPIs Principales (Fila Superior)

Formato Condicional en KPIs

• % Meta Ventas: Verde si ≥100%, Amarillo si ≥80%, Rojo si <80%
• % Presupuesto: Verde si ≤100%, Amarillo si ≤110%, Rojo si >110% (en gastos, menos es mejor)
• Margen Bruto: Verde si ≥ MargenObjetivo, Rojo si <

Tabla Resumen por Región

Usá SUMAR.SI.CONJUNTO con nombres de rango para crear una tabla resumen:

Selector de Mes

Usá la celda MesActual en Config como control:

1. En el Dashboard, agregá una celda con validación de lista: 1,2,3,4,5,6,7,8,9,10,11,12
2. Vinculá esa celda al nombre de rango MesActual
3. Todas las fórmulas del Dashboard se actualizan automáticamente al cambiar el mes

Esto crea un dashboard interactivo sin necesidad de macros.

Hoja _Info

Creá una hoja de documentación con:

DASHBOARD DE CONTROL DE GESTIÓN
Distribuidora del Sur S.A.

Creado por: [Tu nombre]
Fecha: Febrero 2025
Versión: 1.0

ESTRUCTURA DEL LIBRO:
- Dashboard: Vista principal de KPIs (NO editar directamente)
- Ventas: Ingreso de transacciones de venta
- Gastos: Ingreso de gastos operativos
- Empleados: Datos del personal (solo RRHH)
- Productos: Catálogo (solo Compras)
- Listas: Valores de referencia (solo Administrador)
- Config: Parámetros del sistema (solo Administrador)

INSTRUCCIONES:
1. Para cambiar el mes de análisis, modifique la celda B8 en Config
2. Ingrese ventas nuevas en la hoja Ventas, la tabla se expande automáticamente
3. Los KPIs del Dashboard se actualizan automáticamente

NOMBRES DE RANGO:
- TasaIVA: Tasa de IVA vigente
- MetaVentasMensual: Meta de ventas mensual
- PresupuestoGastos: Presupuesto de gastos mensual
- [etc.]

Protección

1. Protegé la hoja Dashboard (solo lectura)
2. Protegé Config y Listas (solo acceso admin con contraseña)
3. En Ventas y Gastos, dejá las columnas de datos editables pero protegé las columnas calculadas
4. Ocultá la hoja _Info (clic derecho > Ocultar)

1. Empezar por el Dashboard: El Dashboard es lo ÚLTIMO que se construye. Primero las tablas de datos y constantes.

2. No usar tablas estructuradas: Si tus datos no son tablas, las fórmulas SUMAR.SI.CONJUNTO necesitan rangos fijos que debés actualizar manualmente.

3. Hardcodear valores: Si escribís 0.21 directamente en una fórmula en lugar de usar TasaIVA, cuando cambie el IVA tendrás que encontrar y reemplazar en todas las fórmulas.

4. No validar los datos de entrada: Sin validación, un error tipográfico en la columna Región ("Buenso Aires") rompe los filtros y resúmenes.

5. Olvidar documentar: En 3 meses no vas a recordar qué hace cada fórmula compleja.

• Los atajos de teclado son la base de la productividad profesional en Excel
• El formato condicional transforma datos crudos en información visual
• La validación de datos previene errores antes de que ocurran
• Los nombres de rango y tablas estructuradas hacen fórmulas legibles y mantenibles
• Un buen proyecto de Excel empieza por la planificación y las constantes, no por los datos
• Siempre documentá y protegé tu trabajo

1. ¿Cuál es el orden correcto para construir un libro de Excel profesional?

   • a) Dashboard → Datos → Config → Listas
   • b) Config → Listas → Datos → Dashboard ✓
   • c) Datos → Fórmulas → Dashboard → Config
   • d) Cualquier orden funciona igual

2. ¿Por qué es mejor usar nombres de rango para constantes como la tasa de IVA?

   • a) Es más rápido de escribir
   • b) Se actualiza en un solo lugar y afecta todas las fórmulas ✓
   • c) Es obligatorio en Excel
   • d) Solo por estética

3. ¿Qué tipo de referencia necesitás para que la fórmula de formato condicional evalúe una columna específica para toda la fila?

   • a) Absoluta ($A$1)
   • b) Relativa (A1)
   • c) Mixta con columna fija ($A1) ✓
   • d) Mixta con fila fija (A$1)

Construí el Dashboard Completo

Usando todas las especificaciones de esta lección, construí el libro completo desde cero. Tu objetivo es que otro miembro de tu equipo pueda:

1. Abrir el libro y entender qué hace sin tu ayuda
2. Ingresar ventas y gastos nuevos sin errores gracias a la validación
3. Ver el estado del negocio en el Dashboard cambiando solo el mes
4. Confiar en que los datos son correctos gracias al formato condicional que resalta anomalías

Tiempo estimado: 60-90 minutos para un usuario con práctica en los temas del módulo.

Criterios de evaluación:

• ¿Las tablas son estructuradas con nombres descriptivos?
• ¿Las constantes usan nombres de rango?
• ¿Hay validación en TODAS las columnas de entrada?
• ¿El formato condicional resalta la información correcta?
• ¿El Dashboard se actualiza al cambiar el mes?
• ¿Hay documentación y protección?

Recurso en Video

📹 Cómo Hacer un Dashboard en Excel desde Cero — Excel Avanzado

Tutorial completo de creación de dashboard empresarial con las técnicas cubiertas en este módulo.