En el mundo actual de datos y tecnología, la capacidad de manejar información dinámica se ha vuelto esencial. En este artículo, exploraremos algunas de las herramientas y conceptos clave para trabajar con datos en tiempo real. Desde la suma y el promedio de valores, hasta la creación de escenarios, descubriremos cómo aprovechar al máximo los datos dinámicos y hacerlos trabajar a nuestro favor. Si está interesado en aprender cómo convertirse en un experto en el análisis de datos y tomar decisiones informadas, ¡no se pierda esta fascinante aventura!
Utilice fórmulas avanzadas de Excel para realizar análisis financieros potentes y dinámicos
Más de 1,8 millones de profesionales utilizan CFI para aprender contabilidad, análisis financiero, modelado y más. Comience con una cuenta gratuita para explorar más de 20 cursos siempre gratuitos y cientos de plantillas financieras y hojas de trucos.
Datos dinámicos, suma, promedio y escenarios.
Para los analistas financieros en banca de inversión, investigación de acciones, FP&A y desarrollo corporativo, aprender habilidades avanzadas de Excel es beneficioso ya que los diferenciará de sus competidores.
En este artículo, exploraremos algunas de las mejores prácticas de la industria que lo ayudarán a acelerar significativamente el modelado financiero y realizar análisis poderosos y dinámicos. Esto también le ayudará a aumentar el uso del teclado en lugar del ratón para ahorrar tiempo en el análisis financiero a largo plazo. Aprenda a crear datos dinámicos, totales, promedios y escenarios en Excel.
Fechas y períodos de tiempo dinámicos con declaraciones IF
Al crear modelos financieros, siempre se comienza definiendo las fechas de inicio y finalización. En lugar de insertar los datos manualmente, puede hacerlos dinámicos utilizando declaraciones IF.
1. Establezca las fechas de inicio y finalización de su modelo. Cambie la fuente a azul para indicar que se trata de números codificados.
2. Calcula el número de períodos usando la fórmula. =AÑO(C2)-AÑO(C1)+1. Debe agregar un período porque los períodos de inicio y finalización están incluidos en el recuento.
3. Utilice fórmulas para configurar los datos dinámicos en la fila 5. En la celda B5, ingrese la fecha de inicio. =AÑO($C$1). Presione F4 para anclar la referencia de celda.
4. Ingrese la fórmula en la celda B6. =SI(B5 para insertar la fecha dinámica. Mantenga presionadas las teclas MAYÚS y flecha derecha, luego presione CTRL + R para llenar las celdas. Las celdas de la derecha se llenan hasta la fecha de finalización.
5. Ahora puedes cambiar fácilmente los datos sin tener que revisar cada celda manualmente. Por ejemplo, si cambia la fecha de finalización al 31/12/2030, las celdas de la derecha se completarán automáticamente hasta que se alcance la fecha de finalización de 2030.
6. Presione MAYÚS + CTRL + Flecha derecha para seleccionar todo el rango de fechas y presione CTRL + 1 para abrir la ventana Formato de celdas. Cambia el relleno a azul oscuro y la fuente a blanca, luego presiona OK. Presione CTRL + B para poner la fuente en negrita y aumentar el tamaño a 12. Ahora tiene un encabezado con todo su análisis financiero debajo.
Totales y promedios dinámicos con la función OFFSET
Ahora ingresaremos algunos datos y comenzaremos a construir el modelo. Puede copiar datos de informes financieros y pegar el valor presionando ALT + E + S y V para valores. Utilice ALT + H + F + C para cambiar la fuente a azul. En este conjunto de datos, el año inicial es 2017 y el año final es 2021.
7. Presione ALT + I + C dos veces para insertar dos columnas a la izquierda de los datos de 2017. Las usaremos como columnas de total y promedio. Ingrese «Total» y «Promedio» en las celdas G1 y G2 de arriba y coloque un 3 al lado de ambas celdas. Este es el número de períodos que queremos incluir en los cálculos. Para modelos con períodos más largos de datos históricos, es posible que desee utilizar solo unos pocos períodos de tiempo para calcular los totales y promedios.
8. Ahora usamos la función DESPLAZAMIENTO para configurar la columna general. Ingrese en la celda B8 =SUM(D8:DESPLAZAMIENTO(D8,0,$H$1-1)). La función OFFSET nos permite seleccionar un rango de datos relacionados con la cantidad de periodos que queremos incluir, los cuales ingresamos en la celda H1.
Necesitamos restar 1 del número de columnas porque solo queremos incluir dos columnas más (es decir, las columnas D a F). Puede cambiar el número codificado de períodos totales para cambiar el valor total.
9. La fórmula para el promedio es similar excepto que la referencia de celda utilizada en el número de períodos es H2: =PROMEDIO(D8:DESPLAZAMIENTO(D8,0,$H$2-1)). Presione SHFIT + Flecha hacia abajo y CTRL + D para llenar las celdas.
Escenarios con las funciones BUSCARV y ELEGIR
A continuación configuraremos algunos escenarios. En las celdas B1 a B3, establezca tres nombres de escenario: casos ascendentes, de destino y descendentes.
10. En la celda A1, presione ALT + A + V + V (A para Datos, V para Validación de Datos y V para Validación). Creamos una lista desplegable para los escenarios. Seleccione «Lista» y seleccione las celdas B1 a B3 como fuente. Presiona OK.
11. Presione CTRL + 1 para formatear la lista desplegable para que podamos encontrarla fácilmente. Formatee la fuente en negrita y cambie el color de la celda.
12. Numere los tres casos de escenario en las celdas C1 a C3. En la celda A2, use la función BUSCARV para hacer coincidir el nombre del escenario en la lista desplegable con el número de caso. Introduce la fórmula =BUSCARV(A1,B1:C3,2,FALSO).
13. Ahora agregamos algunas líneas debajo de las ventas para representar los tres escenarios. En la celda A9, presione ALT + I + R cuatro veces para insertar cuatro filas nuevas. Llámelos ingresos al alza, ingresos objetivo e ingresos a la baja. Ingresaremos algunos números para cada escenario en las celdas E8 a I10.
14. La línea 13 contiene nuestras cifras de ventas reales. En la celda E13, use la función ELEGIR =SELECCIONAR($A$2,E9,E10,E11). Esta característica nos permite crear una línea de ingresos dinámica que selecciona la opción correcta según el escenario seleccionado. Cambie la fuente a negra, presione MAYÚS + Flecha derecha y luego CTRL + R para copiar fórmulas a la derecha.
15. Complete las celdas B10 a C11 presionando CTRL + D. Copie fórmulas de las celdas B11 y C11 y péguelas en B13 y C13.
16. Ahora asociemos la designación total y promedio con el número de períodos especificados en las celdas I1 e I2. Ingrese en la celda B6 =I1&“Total del año“ Por lo tanto, la etiqueta del total mostrará «Total de 3 años», donde el número es dinámico. Ingrese para el nombre promedio =I2&“Promedio anual“.
17. Finalmente, formatea un poco el modelo. Cambie la ganancia bruta, la ganancia antes de impuestos y el ingreso neto a fórmulas para que estén todas vinculadas. Su modelo final podría verse así:
Resumen de las fórmulas más importantes
- Fórmula IF para crear una fecha dinámica: =SI(“año anterior”
- Fórmula OFFSET para calcular el total dinámico: =SUMA(“primera celda de datos” : OFFSET(“primera celda de datos”, 0, “número de períodos”-1))
- Fórmula OFFSET para calcular el promedio dinámico: =PROMEDIO(“primera celda de datos” : OFFSET(“primera celda de datos”, 0, “número de períodos”-1))
- Fórmula BUSCARV para buscar escenarios: =BUSCARV(“valor a buscar”, “rango de tabla”, “columna a buscar”, FALSO)
- ELEGIR fórmula para seleccionar una opción según el escenario: = ELEGIR(“Número de caso”, “Opción 1”, “Opción 2”, “Opción 3”)
Otros recursos
CFI es el proveedor oficial del programa de certificación global Financial Modeling & Valuation Analyst (FMVA)™, diseñado para ayudar a cualquier persona a convertirse en un analista financiero de clase mundial. Para avanzar aún más en su carrera, los siguientes recursos adicionales de CFI le resultarán útiles:
