SENA Regional Atlántico Centro de comercio y Servicios Coordinación de Comercialización y Finanzas Instructor: Fernando Rolón
MANEJO DE EXCEL CON APLICACIONES FINANCIERAS Intensidad: 30 horas presenciales 10 virtuales Objetivo: Profundizar en EXCEL, elaboración de modelos financieros con énfasis en análisis de sensibilidad, la simulación tanto determinantica como probabilística, manejo de escenarios, funciones especiales, base de datos, tablas dinámicas. Beneficio: Maximizar los conocimientos en el manejo de EXCEL con el fin de posibilitar la solución de la mayoría de los problemas que a diario se presentan en el uso de ésta herramienta de tal suerte que se apropie una destreza superior al usuario promedio. Metodología: Presentación de los conceptos, herramientas y técnicas mediante trabajos de grupo en el microcomputador. TEMARIO PARA EL CURSO DE EXCEL Fórmulas, funciones y vínculos Argumentos, conectores y símbolos utilizados Auditoría de fórmulas Funciones financieras predeterminadas: Valor Presente Neto TIR - TIR Múltiples Tablas de Amortización Anualidades Simuladores financieros Funciones definidas por el usuario Leasing Gradientes Convertidor de tasas Datos Tabla en 1 y 2 variables Validación: criterios y formas de validación Escenarios Buscar Objetivo Definición de Escenarios Análisis Y SI Mostrar resultados Generación de informes Sistemas de protección Fundamentos en VBA Desarrollo de aplicaciones y proyectos VBA
1
SENA Regional Atlántico Centro de comercio y Servicios Coordinación de Comercialización y Finanzas Instructor: Fernando Rolón
FORMULAS, FUNCIONES, ARGUMENTOS, VINCULOS Y CONCEPTOS BÁSICOS Nota Aclaratoria: Para que los participantes de este seminario obtengan el mayor provecho del mismo es conveniente tener en cuenta lo siguiente: Los participantes debe conocer, como mínimo, como funciona la aplicación. Las lecciones corresponden a Excel97 y versiones posteriores en ambiente windows95. Cada ejemplo intenta mostrar los beneficios de la aplicación, pero el tratamiento de los
mismos, en la práctica, varía de acuerdo a las necesidades de cada usuario. Fórmula: Una fórmula es una instrucción, u orden, que dada de manera lógica Excel entiende e interpreta para dar un resultado, para que esto se produzca es necesario que se establezca unos operandos y unos operadores, estos juntos constituirán unos argumentos y la lectura de los mismo arrojará como resultado una fórmula. a. Operadores matemáticos. Estos operadores ejecutan las operaciones matemáticas básicas, como son suma, resta, multiplicación o división. Necesitan operandos numéricos y por tanto su resultado también es numérico. Operador + * / % ^ (alt 94)
Descripción Suma Resta (negación) Multiplicación División Establece porcentaje Exponente
b. Operadores de comparación. Permiten comparar dos valores generando un resultado lógico: verdadero o falso Operador = > < >= <= <>
Descripción Igual a Mayor que Menor que Mayor o igual que Menor o igual que Diferente o distinto de
c. Operadores de texto. El operador de texto “&” une dos celdas con texto para obtener un único texto. Así, por ejemplo, “Cuenta de” & “explotación” genera una celda con cuenta de explotación. Operador &
Descripción Une o concadena dos valores textos
2
SENA Regional Atlántico Centro de comercio y Servicios Coordinación de Comercialización y Finanzas Instructor: Fernando Rolón
d. Operadores de referencia. Estos operadores permiten hacer referencias a rangos de celdas dentro de la fórmula. Operador : ; Un espacio { }
Descripción Indica que se debe tomar todas las celdas entre dos referencias Une varias referencias a celdas en una sola (operador de unión) Genera una referencia a las celdas comunes de dos referencias, es un operador de intersección Para la generación de matrices y resultados agrupados
Las fórmulas tienen unas características fundamentales: a. Siempre inician con el signo igual, esta instrucción le indica a Excel que debe arrojar un resultado, generalmente matemático, por lo que es necesario que la información que ella relacione conduzca a un resultado, de lo contrario el resultado será un error. b. Cada uno de los componentes de una fórmula recibe el nombre de argumento, y vienen a ser las indicaciones permiten a la aplicación arrojar un resultado. Cada fórmula resiste hasta 256 caracteres. Porqué? c. Los llamados administradores de listas son los símbolos que utiliza Excel para separar los argumentos, dependiendo de la configuración del equipo pueden ser punto y coma (;) o coma (,) d. Los nidos aparecen cuando dentro de una fórmula, o función, se genera o crea otra que por sí sola genera un resultado independiente. Excel permite todos los nidos posibles SIEMPRE Y CUANDO la escritura de estos no supere los 256 caracteres. e. Cuando las fórmulas tengan un valor numérico para establecer los cálculos NUNCA los digite con formato de comas, (por ejemplo 50,000) si van a ser decimales, utilice en punto (.) del teclado numérico y en lo posible siempre que vaya a digitar un número procure utilizar este teclado, verifique que se encuentre encendido el bombillo Num Lock en el teclado Función: Una función, por su parte, es una fórmula ya preparada por Excel, que permite ahorrar tiempo y cálculos, y que produce un resultado, están predeterminadas en la herramienta y se amplían con el uso de los complementos (parte del menú herramientas) en ellas los argumentos son más complejos que los de las fórmulas y deben se escritos de acuerdo a una sintaxis, que viene a ser la forma como DEBE escribirse, o indicarle a Excel una instrucción para que este la entienda y desarrolle un cálculo como debe ser, si la función, o fórmula, no se escribe como es, Excel arrojará un error. Los conceptos más utilizados en el planteamiento y solución de problemas relacionados con las finanzas, y que soportan el manejo del Excel financiero son: Interés: Se simboliza con I. Es el precio que se paga por el uso de los recursos tomados en préstamo durante un período de tiempo.. Valor Presente: Se simboliza por Vp. Es la cantidad de dinero que se invierte o se toma prestado, se denomina capital, monto o principal. Valor Futuro: Se representa por Vf, viene a ser el valor presente más el interés
3
SENA Regional Atlántico Centro de comercio y Servicios Coordinación de Comercialización y Finanzas Instructor: Fernando Rolón
Períodos: Son los intervalos durante el cual el capital gana unos rendimientos, pueden ser diarios, mensuales, trimestrales, semestrales o anuales. Se simboliza n. Tasa de interés: Se representa por i, es la relación entre el interés y el valor presente por unidad de tiempo se debe expresar en la misma unidad en la que está expresado n, es decir, meses con meses, trimestres con trimestres, etc. OPERACIONES DE CRÉDITO El déficit de caja en el borrador del presupuesto puede ser absorbido por cualquiera de estas posibilidades: a. En Dinero. Es financiación en efectivo, proveniente de acreedores institucionales o no. b. En Especie. Es crédito obtenido de un proveedor, se recibe un bien o servicio. c. Refinanciación. Contempla el replanteamiento de créditos vigentes, sobre los cuales se pactan nuevas condiciones, especialmente de mayores plazos para aliviar la carga del futuro flujo de caja. Las operaciones de crédito se pueden dar por las siguientes opciones: Anualidad (vencida o anticipada) Sistema Leasing Gradientes Aritméticos (ascendentes o descendentes) Gradientes geométricos (ascendentes o descendentes) Créditos de fomentos (abono fijo, cuota variable) Créditos de tesorería Para ellas se precisa de una serie de fórmulas que involucran las variables de tiempo, interés periódico, monto y modalidad vencida o anticipada. Cada una de ellas tiene unas características muy particulares y que le identifican y distinguen de las demás, pero para ello se precisa tener claro el concepto de tasa de interés, así como la modalidad y periodicidad de las mismas. Así, las tasas de interés presentan no una igualdad, sino una equivalencia entre ellas por lo tanto en su tratamiento se debe precisar la periodicidad, modalidad y convertibilidad de la misma, para, al final, tomar una decisión de crédito o inversión. Al comparar tasas para elegir entre varias, se requiere unificarlas en sus tres características como son: a. PRESENTACIÓN. Pueden pactarse en forma nominal o efectiva. b. PERIODICIDAD. Puede ser diaria, mensual, bimestral, trimestral, etc. c. VENCIMIENTO. El pago de intereses anticipados o vencidos. En la legislación colombiana la tasa referentes es la DTF, es calculada todos los viernes por el Banco de la República y tiene una vigencia de siete (7) días pero una aplicabilidad de noventa (90) días, se expresa en forma anticipada, pero su uso se da en términos vencidos bien sea mensual, trimestral o semestral dependiendo de lo pactado. Expresiones de las operaciones crediticias:
4
SENA Regional Atlántico Centro de comercio y Servicios Coordinación de Comercialización y Finanzas Instructor: Fernando Rolón
DTF + X% al mes (vencido o anticipado) DTF + X% al trimestre (vencido o anticipado) DTF + X% al semestre (vencido o anticipado) En los dos primero casos se precisa convertir la tasa pues no se pueden sumar planamente porque la expresión de las mismas están en tiempos diferentes (meses o semestres) versus la referencial (que siempre se expresa en trimestres) Para vencer una tasa anticipada se utiliza la siguiente fórmula: Tasa vencida = Tasa anticipada/(1 – tasa anticipada) Para cambiar la periodicidad de la tasa se precisa el uso de la siguiente fórmula: Nueva tasa = (1 + Tasa a cambiar)^(periodos de la tasa a cambiar/períodos de la nueva tasa) - 1 El comportamiento de la DTF, tasa que define el promedio ponderado de captación de recursos del sector financiero a 90 días, ha sido el siguiente para el segundo semestre del año:
SEMANA Diciembre 29/08 a Enero 04/09 Enero 05 a Enero 11 de 2009 Enero 12 a Enero 18 de 2009 Enero 19 a Enero 25 de 2009 Enero 26 a Febrero 01 de 2009 Febrero 02 a Febrero 08 de 2009 Febrero 09 a Febrero 15 de 2009 Febrero 16 a Febrero 22 de 2009 Febrero 23 a Marzo 01 de 2009 Marzo 01 a Marzo 08 de 2009 Marzo 09 a Marzo 15 de 2009 Marzo 16 a Marzo 22 de 2009 Marzo 23 a Marzo 29 de 2009 Marzo 30 a Abril 05 de 2009 Abril 06 a Abril 12 de 2009 Abril 13 a Abril 19 de 2009 Abril 20 a Abril 26 de 2009 Abril 27 a Mayo 03 de 2009 Mayo 04 a Mayo 10 de 2009 Mayo 11 a Mayo 17 de 2009 Mayo 18 a Mayo 25 de 2009 Mayo 25 a Mayo 31 de 2009 Junio 01 a Junio 07 de 2009 Junio 08 a Junio 14 de 2009 Junio 15 a Junio 21 de 2009
COMPORTAMIENTO DTF 2009 - 2010 DTF 90 E.A. % DTF 90 T.A. % 9,82% 9,26% 9,73% 9,18% 9,73% 9,18% 9,62% 9,08% 9,79% 9,23% 9,56% 9,03% 9,43% 8,91% 8,94% 8,47% 9,02% 8,54% 8,86% 8,40% 8,59% 8,16% 8,34% 7,93% 8,13% 7,74% 7,68% 7,33% 7,91% 7,54% 7,33% 7,01% 7,15% 6,85% 6,87% 6,59% 7,09% 6,79% 6,58% 6,32% 6,29% 6,05% 6,11% 5,89% 6,10% 5,88% 5,70% 5,51% 5,46% 5,28%
DTF TA 2,32% 2,30% 2,30% 2,27% 2,31% 2,26% 2,23% 2,12% 2,14% 2,10% 2,04% 1,98% 1,94% 1,83% 1,89% 1,75% 1,71% 1,65% 1,70% 1,58% 1,51% 1,47% 1,47% 1,38% 1,32%
DTF TV Efectiva 2,37% 2,35% 2,35% 2,32% 2,36% 2,31% 2,28% 2,16% 2,18% 2,15% 2,08% 2,02% 1,97% 1,87% 1,92% 1,78% 1,74% 1,68% 1,73% 1,61% 1,54% 1,49% 1,49% 1,40% 1,34%
5
SENA Regional Atlántico Centro de comercio y Servicios Coordinación de Comercialización y Finanzas Instructor: Fernando Rolón
COMPORTAMIENTO DTF 2009 - 2010 SEMANA DTF 90 E.A. % DTF 90 T.A. % Junio 22 a Junio 28 de 2009 5,54% 5,36% Junio 29 a Julio 05 de 2009 5,40% 5,22% Julio 06 a Julio 12 de 2009 5,32% 5,15% Julio 13 a Julio 19 de 2009 5,19% 5,03% Julio 20 a Julio 26 de 2009 5,11% 4,95% Julio 27 a Agosto 02 de 2009 5,24% 5,07% Agosto 03 a Agosto 09 de 2009 5,06% 4,91% Agosto 10 a Agosto 16 de 2009 5,04% 4,89% Agosto 17 a Agosto 23 de 2009 5,13% 4,97% Agosto 24 a Agosto 30 de 2009 5,10% 4,94% Agosto 31 a Septiembre 06 de 2009 5,02% 4,87% Septiembre 7 a Septiembre 13 de 2009 4,94% 4,79% Septiembre 14 a Septiembre 20 de 2009 4,99% 4,84% Septiembre 21 a Septiembre 27 de 2009 4,94% 4,79% Septiembre 28 a Octubre 04 de 2009 4,77% 4,63% Octubre 05 a Octubre 11 de 2009 4,75% 4,61% Octubre 12 a Octubre 18 de 2009 4,47% 4,35% Octubre 19 a Octubre 25 de 2009 4,40% 4,28% Octubre 26 a Noviembre 01 de 2009 4,45% 4,33% Noviembre 2 a Noviembre 8 de 2009 4,35% 4,24% Noviembre 9 a Noviembre 15 de 2009 4,33% 4,22% Noviembre 16 a Noviembre 22 de 2009 4,47% 4,35% Noviembre 23 a Noviembre 29 de 2009 4,47% 4,35% Noviembre 30 a Diciembre 06 de 2009 4,35% 4,24% Diciembre 07 a Diciembre 13 de 2009 4,21% 4,10% Diciembre 14 a Diciembre 20 de 2009 4,25% 4,14% Diciembre 21 a Diciembre 27 de 2009 4,14% 4,04% Diciembre 28 de 2009 a Enero 3 de 2010 4,11% 4,01% Enero 04 a Enero 10 de 2010 3.92% 3,83% Enero 11 a Enero 17 de 2010 4,00% 3,90% Enero 18 a Enero 24 de 2010 3,98% 3,88% Enero 25 a Enero 31 de 2010 4,10% 4,00% Febrero 01 a Febrero 07 de 2010 4,10% 4,00% Febrero 08 a Febrero 14 de 2010 4,02% 3,92% Febrero 15 a Febrero 21 de 2010 4.03% 3.93%
DTF TA 1,34% 1,31% 1,29% 1,26% 1,24% 1,27% 1,23% 1,22% 1,24% 1,24% 1,22%
DTF TV Efectiva 1,36% 1,32% 1,30% 1,27% 1,25% 1,28% 1,24% 1,24% 1,26% 1,25% 1,23%
Asociada a la tasa viene la operación, por lo tanto: Para anualidad: (cuota fija), viene a ser una serie de cuotas uniformes y periódicas. Se emplean mucho en créditos donde el sistema de amortización determina un pago fijo periódico, mediante el cual en la medida en que se abona al capital el interés disminuye. En la constitución de fondos de amortización se utiliza cuando se decide hacer depósitos periódicos de un mismo valor a fin de reunir una suma determinada al cabo de algún tiempo, su fórmula es:
6
SENA Regional Atlántico Centro de comercio y Servicios Coordinación de Comercialización y Finanzas Instructor: Fernando Rolón
A = (P +*i)/ *[1-(1+i)-n] Donde: P = Monto del crédito, i = Tasa de interés periódica y n = Períodos o plazo Excel trabaja esto des la función financiera PAGO así: = PAGO(Interés;Tiempo;Capital) Esta fórmula calculará el pago periódico, por tanto TODAS las variables deben estar expresadas, o acordes a la misma unidad de tiempo anualmente. Suponga que se tiene una necesidad de financiación de $15,000,000 y un banco puede satisfacerla a una tasa del 26.35% pagadera mensualmente durante un año. Para desarrollar esto digite la información TAL CUAL como se muestra a continuación: A2: Capital, B2: 15,000,000 A3: Interés B3: 26.33%, C3: B3/12 A4: Plazo B4: 12 C4: meses A5: Cuota, Así todas las variables quedan expresadas en los mismos términos, mensuales en este caso pues el crédito se amortizará en un período de un año pero pagadero mensualmente. Luego de esto ubíquese en la celda B5 y desde allí de clic en pegar función (fx) y elija, del grupo de funciones financieras, la llamada pago, y de respuesta a los interrogantes que ella le solicita. Si realizó correctamente las operaciones debe aparecer el siguiente pantallazo:
Tenga siempre la precaución de digitar un signo menos (-) cuando vaya a introducir el valor del capital, esto para evitar que el resultado sea negativo. Si el pantallazo es correcto de click en aceptar, la respuesta DEBE ser 1,435,362 como valor de la cuota y debe aparecer en la celda B5.
7
SENA Regional Atlántico Centro de comercio y Servicios Coordinación de Comercialización y Finanzas Instructor: Fernando Rolón
Con esta información se procede a calcular la tabla de amortización mediante las siguientes indicaciones: Períodos: son los meses de la operación, empiezan en el cero (0) Intereses: Valor del saldo aplicándole el % de interés periódico Cuota: es el valor calculado por la función pago Abono a Capital: Es la diferencia entre la cuota y los intereses Saldo: Al saldo anterior menos el abono a capital Digite estos conceptos de la celda A7 a la E7 SIN DEJAR ninguna celda en blanco, luego construya la tabla completa. Si realizó bien el ejercicio debe aparecer lo siguiente:
Determinación de la tasa efectiva: En todo ejercicio financiero se debe determinar la tasa efectiva de la operación, esta se da siempre en términos anuales y su fórmula es la siguiente:
Interés Efectivo = {(1 + tasa nominal)^(número de capitalizaciones) -1 * 100} Aquí se hace necesario crear una función pues usualmente Excel no la trae, para ello se debe insertar un módulo de visual. De clic derecho sobre la hoja en que esté trabajando y luego clic en ver código, aparecerá un nuevo pantallazo vaya a insertar, seleccione módulo y espere instrucciones. Todo lo anterior cuando se trate de tasas y operaciones vencidas, si la operación es anticipada, entonces la fórmula es la siguiente:
8
SENA Regional Atlántico Centro de comercio y Servicios Coordinación de Comercialización y Finanzas Instructor: Fernando Rolón
A = (P * i)/(1+i)*[1-(1+i)-n] Con las indicaciones anteriores, construya una tabla cuando la anualidad sea anticipada, de acuerdo con la fórmula anterior y a partir del siguiente pantallazo:
Para el cálculo de la anualidad anticipada también se utiliza la función pago y al momento de ingresar la información, se define el tipo de anualidad, colocando 1 en la opción tipo (que define la modalidad del vencimiento), en estos casos la construcción de la tabla de amortización difiere un poco frente a la anterior, pues al saldo del primer período se le debe descontar inmediatamente el valor de la primera cuota calculada en su totalidad, y la tabla no inicia en el período cer0 (0) sino en el 1 pues el período cero(0) viene a ser el del desembolso y primer pago al tiempo, es decir por un lado entra el monto o capital objeto de préstamo y enseguida se registra la salida, descuento o pago de la primera cuota.
9