Modelos-financieros-con-Excel.pdf

Modelos nancieros con EXCEL Herramientas para mejorar la toma de decisiones empresariales Segunda edición

Jairo Gutiérrez Carmona

o d i n e t n o c e d a l b a

T

PRESENTACIÓN . . . . . . . . . . . . . . . . . . . . . . . . . .

IX

Primera parte. Aspectos teóricos de los modelos nancieros . . . . . . . . . . . . . . . . . . . . . . . . . . .

1

1. 1.1 1.2 1.3 1.4

PLANEACIÓN FINANCIERA . . . . . . . . . . . . . . . . PROCESO DE PLANEACIÓN. . . . . . . . . . . . . . . . OBJETIVOS DE LA EMPRESA . . . . . . . . . . . . . . . FUNCIÓN DE LA GERENCIA FINANCIERA . . . . . . . OBJETIVO DE LA GERENCIA FINANCIERA . . . . . . . RESUMEN DEL CAPÍTULO. . . . . . . . . . . . . . . . . CUESTIONARIO DE AUTOEVALUACIÓN . . . . . . . . EJERCICIOS PROPUESTOS . . . . . . . . . . . . . . . . TALLER DE APLICACIÓN Y SEGUIMIENTO . . . . . . .

3 4 5 6 7 8 9 9 10

2. 2.1 2.2 2.2.1 2.2.2

15 16 18 19

2.2.3

MODELOS FINANCIEROS . . . . . . . . . . . . . . . . . CONCEPTO DE MODELOS . . . . . . . . . . . . . . . . . CLASIFICACIÓN DE LOS MODELOS FINANCIEROS . . Modelos según su propósito . . . . . . . . . . . . . . Modelos según el horizonte de tiempo involucrado . . . . . . . . . . . . . . . . . . . . . . . . . Modelos según la metodología de solución . . . . .

2.2.4

Modelos según la forma de cuanticar las

2.2.5 2.3

3. 3.1 3.2 3.3 3.4 3.5 3.5.1 3.5.2

variables . . . . . . . . . . . . . . . . . . . . . . . . . . . Modelos según el grado de detalle . . . . . . . . . . MODELOS FINANCIEROS CON ENFOQUE DE SISTEMAS. . . . . . . . . . . . . . . . . . . . . . . . . . . RESUMEN DEL CAPÍTULO. . . . . . . . . . . . . . . . . CUESTIONARIO DE AUTOEVALUACIÓN . . . . . . . . EJERCICIOS PROPUESTOS . . . . . . . . . . . . . . . . TALLER DE APLICACIÓN Y SEGUIMIENTO . . . . . . . APÉNDICE: ETAPAS PARA LA CREACIÓN DE UN MODELO FINANCIERO . . . . . . . . . . . . . . . . HERRAMIENTAS DEL EXCEL PARA EL MODELAJE FINANCIERO . . . . . . . . . . . . . . . . . FUNCIONES . . . . . . . . . . . . . . . . . . . . . . . . . TABLAS DE DATOS . . . . . . . . . . . . . . . . . . . . . BUSCAR OBJETIVO. . . . . . . . . . . . . . . . . . . . . ESCENARIOS . . . . . . . . . . . . . . . . . . . . . . . . . SOLVER . . . . . . . . . . . . . . . . . . . . . . . . . . . . Escenarios con Solver . . . . . . . . . . . . . . . . . . Informes de Solver . . . . . . . . . . . . . . . . . . . .

20 20 21 21 22 25 25 26 27 30 35 38 41 44 46 50 53 56

VI

3.6


MACROS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . RESUMEN DEL CAPÍTULO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . CUESTIONARIO DE AUTOEVALUACIÓN . . . . . . . . . . . . . . . . . . . . . EJERCICIOS PROPUESTOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . TALLER DE APLICACIÓN Y SEGUIMIENTO . . . . . . . . . . . . . . . . . . . .

Segunda parte. Uso de las herramientas del excel en modelos nancieros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4. 4.1 4.1.1

59 61 61 64 65

67

ANÁLISIS DE SENSIBILIDAD. . . . . . . . . . . . . . . . . . . . . . . . . . . . . ANÁLISIS DE SENSIBILIDAD DE VALOR . . . . . . . . . . . . . . . . . . . . . . Análisis de sensibilidad de valor puntual . . . . . . . . . . . . . . . . . . . Ejemplo sensibilidad puntual relativa . . . . . . . . . . . . . . . . . . . . . Ejemplo de sensibilidad puntual absoluta . . . . . . . . . . . . . . . . . . Ejemplo de sensibilidad puntual mixta . . . . . . . . . . . . . . . . . . . . Signo y valor del grado de sensibilidad puntual . . . . . . . . . . . . . . Análisis de sensibilidad de valor factible . . . . . . . . . . . . . . . . . . . ANÁLISIS DE SENSIBILIDAD DE RANGO . . . . . . . . . . . . . . . . . . . . . Sensibilidad de rango con Buscar objetivo . . . . . . . . . . . . . . . . . . Sensibilidad de rango con Solver . . . . . . . . . . . . . . . . . . . . . . . . ANÁLISIS DE SENSIBILIDAD DE HIPÓTESIS . . . . . . . . . . . . . . . . . . . RESUMEN DEL CAPÍTULO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . CUESTIONARIO DE AUTOEVALUACIÓN . . . . . . . . . . . . . . . . . . . . . EJERCICIOS PROPUESTOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . TALLER DE APLICACIÓN Y SEGUIMIENTO . . . . . . . . . . . . . . . . . . . .

69 72 73 73 77 78 80 80 83 84 86 87 90 91 91 92

5. 5.1 5.2 5.3 5.3.1 5.3.2

SIMULACIÓN DE RESULTADOS . . . . . . . . . . . . . . . . . . . . . . . . . . . SIMULACIÓN DE MONTECARLO . . . . . . . . . . . . . . . . . . . . . . . . . . ANÁLISIS DE RIESGO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SIMULACIÓN CON CRYSTAL BALL . . . . . . . . . . . . . . . . . . . . . . . . . Ejemplo No. 1 de simulación con Crystal Ball . . . . . . . . . . . . . . . . Ejemplo No. 2 de simulación con Crystal Ball . . . . . . . . . . . . . . . . RESUMEN DEL CAPÍTULO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . CUESTIONARIO DE AUTOEVALUACIÓN . . . . . . . . . . . . . . . . . . . . . EJERCICIOS PROPUESTOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . TALLER DE APLICACIÓN Y SEGUIMIENTO . . . . . . . . . . . . . . . . . . . .

93 96 109 112 112 118 120 122 122 123

6. 6.1 6.2 6.3 6.4 6.5 6.5.1 6.5.2

OPTIMIZACIÓN DE RESULTADOS . . . . . . . . . . . . . . . . . . . . . . . . . PROGRAMACIÓN LINEAL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . PROGRAMACIÓN ENTERA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . PROGRAMACIÓN NO LINEAL. . . . . . . . . . . . . . . . . . . . . . . . . . . . VARIABLES BINARIAS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . PROGRAMACIÓN POR OBJETIVOS . . . . . . . . . . . . . . . . . . . . . . . . Solución con restricciones duras . . . . . . . . . . . . . . . . . . . . . . . . Solución con restricciones blandas . . . . . . . . . . . . . . . . . . . . . . .

125 129 133 136 139 142 143 145

4.1.2 4.2 4.2.1 4.2.2 4.3

Tabla de Contenido

6.6

VII

PROGRAMACIÓN DE OBJETIVOS MÚLTIPLES . . . . . . . . . . . . . . . . . . RESUMEN DEL CAPÍTULO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . CUESTIONARIO DE AUTOEVALUACIÓN . . . . . . . . . . . . . . . . . . . . . EJERCICIOS PROPUESTOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . TALLER DE APLICACIÓN Y SEGUIMIENTO . . . . . . . . . . . . . . . . . . . . APÉNDICE: OPCIONES Y MENSAJES DEL SOLVER . . . . . . . . . . . . . . .

148 152 155 155 156 158

Tercera parte. Aplicación del excel en modelos nancieros . . . . . .

165

7. 7.1 7.2 7.2.1

7.2.3

EVALUACIÓN DE PROYECTOS . . . . . . . . . . . . . . . . . . . . . . . . . . . MODELOS PARA LA EVALUACIÓN FINANCIERA DE PROYECTOS . . . . . . SENSIBILIDAD, OPTIMIZACIÓN Y SIMULACIÓN DE PROYECTOS . . . . . . Modelo de inversión en una tractomula. . . . . . . . . . . . . . . . . . . . Análisis de sensibilidad de valor . . . . . . . . . . . . . . . . . . . . . . . . Análisis de escenarios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Optimización . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Simulación con macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Simulación con Crystal Ball . . . . . . . . . . . . . . . . . . . . . . . . . . . . Modelo de inversión en producto para exportación . . . . . . . . . . . . Análisis de sensibilidad puntual . . . . . . . . . . . . . . . . . . . . . . . . . Análisis de sensibilidad factible . . . . . . . . . . . . . . . . . . . . . . . . . Análisis de escenarios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Análisis de sensibilidad de rango . . . . . . . . . . . . . . . . . . . . . . . . Simulación . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Modelo de inversión en una fábrica de tapetes . . . . . . . . . . . . . . .

167 168 178 178 182 183 184 185 187 189 189 190 191 192 192 194

8. 8.1 8.1.1 8.1.2 8.1.3 8.1.4 8.1.5 8.2

PROYECCIÓN Y VALORACIÓN DE EMPRESAS . . . . . . . . . . . . . . . . . . IN_PLANNER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Organización de In_planner . . . . . . . . . . . . . . . . . . . . . . . . . . . Planeación . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Proyección . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Evaluación. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . MODELOS DE PROYECCIÓN DE ESTADOS FINANCIEROS . . . . . . . . . . .

197 198 201 202 203 204 204 205

9. 9.1

CASOS DE MODELOS FINANCIEROS CON EXCEL. . . . . . . . . . . . . . . . MODELO DE PUNTO DE EQUILIBRIO . . . . . . . . . . . . . . . . . . . . . . . Análisis de sensibilidad puntual . . . . . . . . . . . . . . . . . . . . . . . . . Análisis de escenarios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Simulación . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . MODELO DE LANZAMIENTO DE UN PRODUCTO . . . . . . . . . . . . . . . . Análisis de escenarios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Simulación . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . MODELO DE ANÁLISIS DE RENTABILIDAD - RIESGO DE UN BONO . . . . . Análisis de sensibilidad y volatilidad del bono . . . . . . . . . . . . . . . MODELO DE COSTO – VOLUMEN - UTILIDAD . . . . . . . . . . . . . . . . . .

209 210 212 213 214 216 217 219 222 225 226

7.2.2

9.2 9.3 9.4

VIII

9.5 9.6 9.7 9.8


Optimización maximizando la utilidad . . . . . . . . . . . . . . . . . . . . Punto de equilibrio con optimización . . . . . . . . . . . . . . . . . . . . . Sensibilidad del punto de equilibrio . . . . . . . . . . . . . . . . . . . . . . Simulación del punto de equilibrio . . . . . . . . . . . . . . . . . . . . . . . MODELO PARA SELECCIONAR ALTERNATIVAS . . . . . . . . . . . . . . . . . Evaluación de alternativas con modelos determinísticos . . . . . . . . Evaluación de alternativas con modelos probabilísticos . . . . . . . . . MODELO DE EXPLOTACIÓN DE RECURSOS NATURALES . . . . . . . . . . . MODELO DE OPCIONES REALES . . . . . . . . . . . . . . . . . . . . . . . . . . Análisis de sensibilidad de las opciones reales . . . . . . . . . . . . . . . Simulación con opciones reales . . . . . . . . . . . . . . . . . . . . . . . . . MODELO DE OPTIMIZACIÓN DE UN PORTAFOLIO . . . . . . . . . . . . . . . Distribución de probabilidades de la rentabilidad . . . . . . . . . . . . . Covarianza de dos inversiones . . . . . . . . . . . . . . . . . . . . . . . . . . Determinación de las inversiones por el mercado . . . . . . . . . . . . . Rentabilidad de un portafolio . . . . . . . . . . . . . . . . . . . . . . . . . . Riesgo de un portafolio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Optimización de un portafolio por objetivos múltiples . . . . . . . . . .

Cuarta parte. Complementos

...........................

228 230 232 233 235 237 240 242 248 251 253 255 255 256 257 258 260 269

267

10. 10.1 10.1.1 10.1.2 10.1.3 10.2 10.2.1 10.2.2 10.2.3 10.2.4 10.3

FUNCIONES UTILIZADAS EN MODELOS FINANCIEROS . . . . . . . . . . . . FUNCIONES FINANCIERAS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Funciones para conversión de tasas . . . . . . . . . . . . . . . . . . . . . . Funciones de series uniformes. . . . . . . . . . . . . . . . . . . . . . . . . . Funciones de evaluación de proyectos . . . . . . . . . . . . . . . . . . . . FUNCIONES ESTADISTICAS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Funciones de tendencia central . . . . . . . . . . . . . . . . . . . . . . . . . Funciones de dispersión . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Funciones de forma. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Funciones de regresión y correlación . . . . . . . . . . . . . . . . . . . . . FUNCIONES MATEMÁTICAS. . . . . . . . . . . . . . . . . . . . . . . . . . . . .

269 270 270 272 279 284 286 291 293 295 299

11. 11.1 11.1.1 11.1.2 11.1.3 11.2 11.2.1 11.2.2 11.2.3 11.2.4

MANUAL DE VBA PARA EXCEL . . . . . . . . . . . . . . . . . . . . . . . . . . . MACROS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Cómo grabar una macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Cómo ejecutar una macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . Cómo editar una macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ELEMENTOS DEL VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Objetos, propiedades y métodos . . . . . . . . . . . . . . . . . . . . . . . . Variables y constantes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Instrucciones y estructuras . . . . . . . . . . . . . . . . . . . . . . . . . . . . Algunos métodos del VBA . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

305 306 307 309 311 314 314 316 317 325

12. 12.1

FUNCIONES PERSONALIZADAS DEL EXCEL . . . . . . . . . . . . . . . . . . . FUNCIONES PERSONALIZADAS . . . . . . . . . . . . . . . . . . . . . . . . . .

329 330

IX

Tabla de Contenido

12.2 12.2.1 12.2.2 12.3

FUNCIONES FINANCIERAS PERSONALIZADAS . . . . . . . . . . . . . . . . . Funciones para conversión de tasas . . . . . . . . . . . . . . . . . . . . . . Funciones de gradientes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . CÓMO INCORPORAR LAS FUNCIONES PERSONALIZADAS A SU EQUIPO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

334 334 338

EJERCICIOS PROPUESTOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

357

BIBLIOGRAFÍA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

365

13.

354

PRESENTACIÓN La creciente competencia en los negocios exige un proceso de toma de decisiones cada vez más complejo, pero el conocimiento cientíco que facilita esta

labor no se está incorporando al mismo ritmo en el trabajo diario de las organizaciones; este desequilibrio, de no poder agregar tecnología a un trabajo de complejidad creciente, lleva a un deterioro permanente de la productividad y la competitividad en las empresas. Por lo anterior, es indispensable que el área nanciera cuente con herramientas ági les y precisas que le permitan analizar una situación y evaluar los resultados, antes de tomar una decisión. Ante esta necesidad de automatizar los cálculos para apoyar de manera ecien te la toma de decisiones, la hoja de cálculo Excel ha ganado un espacio como herramienta básica de trabajo en el área nanciera de las empresas. Por lo

tanto, la orientación central de este libro será presentar las herramientas que la hoja de cálculo Excel pone a disposición de la gerencia para construir modelos nancieros que permitan analizar el

impacto de una decisión en los objetivos y resultados de la empresa. El libro se ha dividido en cuatro partes: La primera parte trata los aspectos teóricos de los modelos nancieros con el n de apreciar su utilidad para facilitar el trabajo del área nanciera y presenta

las herramientas técnicas que ofrece el Excel para trabajar estas ideas;

La segunda parte da a conocer, de manera práctica, el uso de las herramientas básicas del Excel en el modelaje nanciero; La tercera parte se concentra en presentar casos de modelos nancieros en

los cuales se aplican las herramientas del Excel; La cuarta parte ofrece algunos complementos útiles para mejorar el uso de las herramientas de modelaje nancieros

que vienen con el Excel. Los modelos utilizados en el libro se explican gráca y conceptualmente, sin

embargo, para seguir adecuadamente el desarrollo de los casos la mejor forma de hacerlo es estudiando simultáneamente los archivos que se incluyen en el Complemento Virtual del SIL (Sistema de Información en línea). En el texto del libro se anuncia cuál archivo se está utilizando en cada momento y en cada archivo se incluye, además del modelo resuelto, ejercicios para complementar el análisis de la situación que se está estudiando. Es importante realizar los ejercicios como una auto-evaluación de las herramientas utilizadas. Para completar este trabajo se presenta un caso de aplicación y seguimiento que se desarrolla a lo largo del libro aplicando las herramientas que se vean en cada capítulo; al nal, se agrega el

enunciado de unos problemas, para que se ejecute el trabajo de diseñar y construir los modelos nancieros que ayudan

a resolverlos.

E T R A P A R E M I R P

ASPECTOS TEÓRICOS DE LOS MODELOS FINANCIEROS

Esta primera parte expone el trabajo que debe realizar la gerencia para asegurar el futuro de la empresa y muestra cómo los modelos nancieros facilitan este trabajo, al permitirle analizar mejor los resultados antes de tomar una decisión. Finalmente presenta las herramientas técnicas que ofrece el Excel para trabajar los modelos nancieros

Se incluyen los siguientes capítulos: 1. PLANEACIÓN FINANCIERA Expone la responsabilidad que tiene el área nanciera de las empre-

sas y por lo tanto la importancia de contar con herramientas que faciliten el análisis de las decisiones. 2. MODELOS FINANCIEROS Su objetivo es proponer un enfoque sistémico en la construcción de modelos nancieros que permite efectuar análisis más organizados

de las situaciones empresariales. 3. HERRAMIENTAS DE EXCEL PARA EL MODELAJE FINANCIERO Muestra el funcionamiento de las herramientas del Excel que se utilizan para analizar los modelos nancieros.

3

• En este capítulo se presentan los conceptos fundamentales de la planeación nanciera, haciendo énfasis en el proceso de planeación

como medio para alcanzar los objetivos de la empresa y en el papel

1

que juega la gerencia nanciera en este proceso. • Se concluye que el gerente nanciero tiene la difícil tarea de eval-

uar el impacto que tendrán las decisiones de los directivos de la empresa en los resultados futuros del negocio, lo cual realmente se facilita con el uso de los modelos nancieros.

CONTENIDO 1.1 1.2 1.3 1.4

PROCESO DE PLANEACIÓN OBJETIVOS DE LA EMPRESA FUNCIONES DE LA GERENCIA FINANCIERA OBJETIVO DE LA GERENCIA FINANCIERA

OBJETIVOS DEL CAPÍTULO General Ubicar al lector en la responsabilidad que tiene el área nanciera

de las empresas en lo relacionado con la supervivencia del negocio y por lo tanto la importancia de contar con herramientas que faciliten el análisis de las consecuencias futuras que traerán las decisiones que se adopten hoy. Especícos • Presentar la empresa como un sistema integrado por elementos interdependientes. • Insistir en que la planeación requiere de planes y estrategias para llevar la empresa al cumplimiento de objetivos posibles. • Plantear los objetivos genéricos de las empresas y el papel que juega la gerencia nanciera para que dichos objetivos sean alcanzados. • Manifestar el aporte de las herramientas informáticas para facilitar el trabajo del administrador nanciero.

De aprendizaje Al estudiar este capítulo se estará en capacidad de comprender la interdependencia de las decisiones empresariales, por lo tanto se adquirirán las bases para entender los fundamentos teóricos de la planeación y se tomará conciencia de la necesidad de contar con herramientas informáticas para facilitar estos procesos.

a r e i c n a n f n ó i c a e n a l P

Modelos Financieros con EXCEL

Colección: Ciencias empresariales Área: Contabilidad y ﬁnanzas

ISBN 978-958-648-549-4

Modelos-financieros-con-Excel.pdf

Recommend Documents