Modelado financiero en Excel para principiantes Hoja de referencia: imitaciones

Por Danielle Stein Fairhurst

Cuando construyes modelos financieros en Microsoft Excel, las funciones son el nombre del juego. También es necesario que revise su trabajo y vuelva a verificarlo para asegurarse de que no haya errores. Finalmente, para hacer que su trabajo sea rápido y fácil, los atajos de teclado son un salvavidas.

Funciones esenciales de Excel para crear modelos financieros

Hoy, más de 400 funciones están disponibles en Excel, y Microsoft sigue agregando más con cada nueva versión del software. Muchas de estas funciones no son relevantes para su uso en finanzas, y la mayoría de los usuarios de Excel solo usan un porcentaje muy pequeño de las funciones disponibles. Si está utilizando Excel para el modelado financiero, necesita una comprensión firme de las funciones más comúnmente utilizadas, como mínimo.

Aunque hay muchos, muchos más que le resultarán útiles al crear modelos, aquí hay una lista de las funciones más básicas de las que no puede prescindir.

Función	Qué hace
SUM	Suma, o suma, un rango de celdas.
MIN	Calcula el valor mínimo de un rango de celdas.
MAX	Calcula el valor máximo de un rango de celdas.
PROMEDIO	Calcula el valor promedio de un rango de celdas.
ROUND	Redondea un solo número al valor más cercano especificado, generalmente a un número entero.
ROUNDUP	Redondea hasta un número único al valor especificado más cercano, generalmente a un número entero.
REDONDO	Redondea hacia abajo un número único al valor especificado más cercano, generalmente a un número entero.
IF	Devuelve un valor especificado solo si se cumple una condición individual.
IFS	Devuelve un valor especificado si se cumplen las condiciones complejas.
CONTEO	Cuenta el número de valores en un rango que cumple un determinado criterio único.
COUNTIFS	Cuenta el número de valores en un rango que cumplen con múltiples criterios.
SUMA	Suma los valores en un rango que cumple un determinado criterio único.
RESÚMENES	Suma los valores en un rango que cumplen los múltiples criterios.
BUSCARV	Busca un rango y devuelve el primer valor correspondiente en una tabla vertical que coincide exactamente con la entrada especificada.
HLOOKUP	Busca un rango y devuelve el primer valor correspondiente en una tabla horizontal que coincide exactamente con la entrada especificada. Se devuelve un error si no puede encontrar la coincidencia exacta.
ÍNDICE	Funciona como las coordenadas de un mapa y devuelve un valor único basado en los números de columnas y filas que ingresa en los campos de funciones.
MATCH	Devuelve la posición de un valor en una columna o una fila. Los modeladores a menudo combinan MATCH con la función INDEX para crear una función de búsqueda, que es mucho más robusta y flexible y usa menos memoria que VLOOKUP o HLOOKUP.
PMT	Calcula el total de pagos anuales de un préstamo.
IPMT	Calcula el componente interest de un préstamo.
PPMT	Calcula el componente principal de un préstamo.
VAN	Tiene en cuenta el valor temporal del dinero al dar el valor actual neto de los flujos de efectivo futuros en dólares de hoy, en función del monto de la inversión y la tasa de descuento.

Hay mucho más para ser un buen modelador financiero que simplemente saber muchas funciones de Excel. Un modelador habilidoso puede seleccionar qué función es mejor usar en qué situación. Por lo general, puede encontrar varias formas diferentes de lograr el mismo resultado, pero la mejor opción es siempre la función o solución que sea la más simple, clara y fácil de entender para los demás.

Qué buscar al verificar o auditar un modelo financiero

Si ha estado usando Excel por un tiempo, probablemente prefiera crear sus propias hojas de cálculo o modelos financieros desde cero. En un entorno corporativo, sin embargo, las personas rara vez tienen esta oportunidad. En cambio, se espera que se hagan cargo de un modelo existente que alguien más ha creado.

Tal vez esté asumiendo un rol que está asumiendo de otra persona y existe un modelo de informe financiero existente que deberá actualizar cada mes. O le han pedido que calcule comisiones de ventas cada trimestre en función de una monstruosa hoja de cálculo de 50 pestañas que realmente no le gusta. No solo hereda los modelos de otros junto con las entradas, supuestos y cálculos que ha introducido el modelador original, sino que también hereda los errores del modelador.

Si vas a asumir la responsabilidad del modelo de otra persona, debes estar preparado para asumirlo y hacerlo tuyo. Debe ser responsable del funcionamiento de este modelo y de estar seguro de que está funcionando correctamente. Aquí hay una lista de verificación de cosas que debe verificar al heredar el modelo financiero de otra persona:

• Familiarícese con su apariencia. Mire a través de cada hoja para ver qué combinaciones de colores se han usado. Lea toda la documentación. ¿Hay alguna clave para ayudar a ver qué células son cuáles? ¿Ha diferenciado el modelador entre fórmulas y suposiciones rígidas?
• Eche un buen vistazo a las fórmulas. ¿Son consistentes? ¿Contienen valores codificados que no se actualizarán automáticamente y, por lo tanto, causarán errores?
• Ejecute una comprobación de error. Pulse el botón Comprobación de errores en la sección Auditoría de fórmulas de la pestaña Fórmulas en la cinta para ver de un vistazo si hay algún error de Excel en la hoja que pueda causar problemas.
• Buscar enlaces a archivos externos. Los enlaces externos pueden ser una parte válida del proceso operativo en funcionamiento, pero necesita saber si este archivo recibe entradas de libros de trabajo externos para asegurarse de que nadie cambie inadvertidamente las hojas o los nombres de los archivos, lo que ocasionará errores en su modelo.Busque enlaces externos presionando el botón Editar enlaces en la sección Conexiones de la pestaña Datos en la cinta de opciones.
• Revise los rangos nombrados. Los rangos designados pueden ser útiles en un modelo financiero, pero a veces albergan errores debido a nombres redundantes, así como a enlaces externos. Revise los rangos nombrados en el Administrador de nombres, que se encuentra en la sección Nombres definidos de la pestaña Fórmulas en la cinta de opciones. Elimine los rangos nombrados que contengan errores o que no se estén utilizando, y si contienen enlaces a archivos externos, tome nota y asegúrese de que sean necesarios.
• Verificar cálculos automáticos. Las fórmulas deben calcularse automáticamente, pero a veces, cuando un archivo es muy grande o un modelador desea controlar los cambios manualmente, el cálculo se ha establecido en manual en lugar de automático. Si ve la palabra Calcular en la barra de estado inferior izquierda, esto significa que el cálculo se ha configurado en manual, por lo que probablemente se encuentre en una investigación compleja. Presione el botón Opciones de cálculo en la sección Cálculo de la pestaña Fórmulas en la Cinta para cambiar entre el cálculo manual y el automático.

Además de estos pasos, estas son algunas prácticas herramientas de auditoría en Excel que puede usar para verificar, auditar, validar y, de ser necesario, corregir un modelo heredado para que pueda confiar en los resultados de su modelo financiero:

• Inspeccionar libro de trabajo. Conozca las características ocultas de su modelo e identifique características potencialmente problemáticas que de otro modo serían muy difíciles de encontrar con esta herramienta poco conocida. Para usarlo, abra el libro de trabajo, haga clic en el botón Archivo en la Cinta; en la pestaña Información, haga clic en el botón Comprobar problemas.
• F2: Si las celdas fuente de una fórmula están en la misma página, el atajo F2 pone la celda en modo de edición, por lo que este atajo es una buena forma de ver visualmente de dónde provienen los datos de origen.
• Precedentes de seguimiento / Dependientes: Las herramientas de auditoría de Excel rastrean visualmente las relaciones con las flechas de la línea trazadora. Puede acceder a estas herramientas en la sección Auditoría de fórmulas de la pestaña Fórmulas en la cinta de opciones.
• Evaluar fórmula: Desmonte fórmulas largas y complejas utilizando la herramienta Evaluar fórmula, en la sección Auditoría de fórmulas de la pestaña Fórmulas en la cinta de opciones.
• Herramientas de comprobación de errores: Si comete un error, o lo que Excel cree que es un error, aparecerá un triángulo verde en la esquina superior izquierda de la celda. Esto sucederá si omite celdas adyacentes, o si ingresa una entrada como texto, que parece debería ser un número.
• Ventana de observación: Si tiene celdas de salida que le gustaría vigilar, esta herramienta mostrará el resultado de las celdas especificadas en una ventana separada. Puede encontrar esta herramienta en la sección Auditoría de fórmulas de la pestaña Fórmulas en la cinta de opciones. Es útil para probar fórmulas para ver el impacto de un cambio en las suposiciones en una celda o células separadas.
• Mostrar fórmulas: Para ver todas las fórmulas de un vistazo en lugar de los valores resultantes, presione el botón Mostrar fórmulas en la sección Auditoría de fórmulas de la pestaña Fórmulas en la cinta (o use el atajo Ctrl + ').Mostrar fórmulas también es una manera muy rápida y fácil de ver si existe algún valor codificado.

Métodos abreviados de teclado de Excel para modeladores financieros

Si está usando mucho tiempo modelando en Excel, puede ahorrar tiempo aprendiendo algunos métodos abreviados de teclado. Gran parte de las habilidades del modelador se refieren a la velocidad y la precisión, y al practicar estos atajos hasta que se conviertan en memoria muscular, serás un modelador más rápido y preciso.

Aquí hay una lista de los accesos directos más útiles que deberían ser parte de su uso diario del teclado si es un modelador financiero:

Edición
Ctrl + S	Guardar libro de trabajo.
Ctrl + C	Copiar.
Ctrl + V	Pegar.
Ctrl + X	Cortar.
Ctrl + Z	Deshacer.
Ctrl + Y	Rehacer.
Ctrl + A	Seleccionar todo.
Ctrl + R	Copia la celda más a la izquierda en todo el rango. (Primero debe resaltar el rango.)
Ctrl + D	Copie la celda superior en el rango. (Primero debe resaltar el rango.)
Ctrl + B	Negrita.
Ctrl + 1	Cuadro de formato.
Alt + Tab	Cambiar de programa.
Alt + F4	Cerrar programa.
Ctrl + N	Nuevo libro de trabajo.
Shift + F11	Nueva hoja de trabajo.
Ctrl + W	Cerrar hoja de trabajo.
Ctrl + E + L	Eliminar una hoja.
Ctrl + Tab	Cambiar libros de trabajo.
Navegación
Mayús + barra espaciadora	Resaltar fila.
Ctrl + barra espaciadora	Resaltar columna.
Ctrl + - (guión)	Eliminar celdas seleccionadas.
Teclas de flecha	Mover a celdas nuevas.
Ctrl + Pg Arriba / Pg Abajo	Cambiar hojas de trabajo.
Ctrl + teclas de flecha	Ir al final del rango continuo y seleccionar una celda.
Shift + teclas de flecha	Seleccionar rango.
Mayús + Ctrl + teclas de flecha	Seleccionar rango continuo.
Inicio	Mover al comienzo de la línea.
Ctrl + Inicio	Mover a la celda A1.
En fórmulas
F2	Editar fórmula, que muestra las celdas precedentes.
Alt + Intro	Comience una nueva línea en la misma celda.
Shift + teclas de flecha	Resalta dentro de las celdas.
F4	Cambiar la referencia absoluta ("$").
Esc	Cancele una entrada de celda.
ALT + = (signo igual)	Suma las celdas seleccionadas.
F9	Recalcular todos los libros de trabajo.
Ctrl + [	Resalta las celdas precedentes.
Ctrl +]	Resalta celdas dependientes.
F5 + Intro	Volver a la celda original.

Para encontrar el acceso directo para cualquier función, presione la tecla Alt y las teclas de acceso directo mostrarán la cinta de opciones. Por ejemplo, para ir al Administrador de nombres, presione Alt + M + N y aparecerá el cuadro de diálogo Administrador de nombres.

En la esquina superior izquierda, encontrará la barra de herramientas de acceso rápido. Puede cambiar los accesos directos que aparecen en la barra de herramientas de acceso rápido haciendo clic en la pequeña flecha a la derecha de la barra de herramientas y seleccionando lo que desea agregar en el menú desplegable que aparece. Por ejemplo, si agrega Pegado especial a la Barra de herramientas de acceso rápido, se puede acceder a Pegado especial con el atajo Alt + 4. Tenga en cuenta que esto solo funciona cuando la barra de herramientas de acceso rápido se ha personalizado, y lo que sea que coloque en la cuarta posición se accederá mediante el atajo Alt + 4.