Modelos financieros

con EXCEL
Herramientas para mejorar la
toma de decisiones empresariales
Segunda edicin

Jairo Gutirrez Carmona

Tabla de contenido

PRESENTACIN . . . . . . . . . . . . . . . . . . . . . . . . . . . IX
Primera parte. Aspectos tericos de los modelos
financieros . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1.
1.1
1.2
1.3
1.4

PLANEACIN FINANCIERA. . . . . . . . . . . . . . . . .
PROCESO DE PLANEACIN. . . . . . . . . . . . . . . . .
OBJETIVOS DE LA EMPRESA. . . . . . . . . . . . . . . .
FUNCIN DE LA GERENCIA FINANCIERA . . . . . . . .
OBJETIVO DE LA GERENCIA FINANCIERA. . . . . . . .
RESUMEN DEL CAPTULO. . . . . . . . . . . . . . . . . .
CUESTIONARIO DE AUTOEVALUACIN . . . . . . . . .
EJERCICIOS PROPUESTOS . . . . . . . . . . . . . . . . .
TALLER DE APLICACIN Y SEGUIMIENTO. . . . . . . .

3
4
5
6
7
8

9
9
10

2.
MODELOS FINANCIEROS . . . . . . . . . . . . . . . . . .
2.1
CONCEPTO DE MODELOS. . . . . . . . . . . . . . . . . .
2.2
CLASIFICACIN DE LOS MODELOS FINANCIEROS. . .
2.2.1 Modelos segn su propsito . . . . . . . . . . . . . . .
2.2.2 Modelos segn el horizonte de tiempo
involucrado. . . . . . . . . . . . . . . . . . . . . . . . . .
2.2.3 Modelos segn la metodologa de solucin. . . . . .
2.2.4 Modelos segn la forma de cuantificar las
variables. . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.2.5 Modelos segn el grado de detalle. . . . . . . . . . .
2.3
MODELOS FINANCIEROS CON ENFOQUE DE
SISTEMAS. . . . . . . . . . . . . . . . . . . . . . . . . . .

RESUMEN DEL CAPTULO. . . . . . . . . . . . . . . . . .

CUESTIONARIO DE AUTOEVALUACIN . . . . . . . . .

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

TALLER DE APLICACIN Y SEGUIMIENTO. . . . . . . .

APNDICE: ETAPAS PARA LA CREACIN DE

UN MODELO FINANCIERO . . . . . . . . . . . . . . . . .

15
16
18
19

3.
HERRAMIENTAS DEL EXCEL PARA EL

MODELAJE FINANCIERO . . . . . . . . . . . . . . . . . .
3.1 FUNCIONES . . . . . . . . . . . . . . . . . . . . . . . . . .
3.2
TABLAS DE DATOS. . . . . . . . . . . . . . . . . . . . . .
3.3
BUSCAR OBJETIVO. . . . . . . . . . . . . . . . . . . . . .
3.4 ESCENARIOS. . . . . . . . . . . . . . . . . . . . . . . . . .
3.5 SOLVER. . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.5.1 Escenarios con Solver . . . . . . . . . . . . . . . . . . .
3.5.2 Informes de Solver . . . . . . . . . . . . . . . . . . . . .

20
20
21
21
22
25
25
26
27
30

35
38
41
44
46
50
53
56

VI

Jairo Gutirrez Carmona

3.6 MACROS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

RESUMEN DEL CAPTULO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

CUESTIONARIO DE AUTOEVALUACIN . . . . . . . . . . . . . . . . . . . . . .

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

TALLER DE APLICACIN Y SEGUIMIENTO. . . . . . . . . . . . . . . . . . . . .

59
61
61
64
65

Segunda parte. Uso de las herramientas del excel en modelos

financieros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
4.
4.1
4.1.1




4.1.2
4.2
4.2.1
4.2.2
4.3

ANLISIS DE SENSIBILIDAD. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
ANLISIS DE SENSIBILIDAD DE VALOR. . . . . . . . . . . . . . . . . . . . . . .
Anlisis 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 . . . . . . . . . . . . . . .
Anlisis de sensibilidad de valor factible. . . . . . . . . . . . . . . . . . . .
ANLISIS DE SENSIBILIDAD DE RANGO . . . . . . . . . . . . . . . . . . . . . .
Sensibilidad de rango con Buscar objetivo. . . . . . . . . . . . . . . . . . .
Sensibilidad de rango con Solver. . . . . . . . . . . . . . . . . . . . . . . . .
ANLISIS DE SENSIBILIDAD DE HIPTESIS . . . . . . . . . . . . . . . . . . . .
RESUMEN DEL CAPTULO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
CUESTIONARIO DE AUTOEVALUACIN . . . . . . . . . . . . . . . . . . . . . .
EJERCICIOS PROPUESTOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
TALLER DE APLICACIN 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

SIMULACIN DE RESULTADOS. . . . . . . . . . . . . . . . . . . . . . . . . . . .
SIMULACIN DE MONTECARLO. . . . . . . . . . . . . . . . . . . . . . . . . . .
ANLISIS DE RIESGO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
SIMULACIN CON CRYSTAL BALL. . . . . . . . . . . . . . . . . . . . . . . . . .
Ejemplo No. 1 de simulacin con Crystal Ball. . . . . . . . . . . . . . . . .
Ejemplo No. 2 de simulacin con Crystal Ball. . . . . . . . . . . . . . . . .
RESUMEN DEL CAPTULO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
CUESTIONARIO DE AUTOEVALUACIN . . . . . . . . . . . . . . . . . . . . . .
EJERCICIOS PROPUESTOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
TALLER DE APLICACIN 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

OPTIMIZACIN DE RESULTADOS . . . . . . . . . . . . . . . . . . . . . . . . . .
PROGRAMACIN LINEAL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
PROGRAMACIN ENTERA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
PROGRAMACIN NO LINEAL. . . . . . . . . . . . . . . . . . . . . . . . . . . . .
VARIABLES BINARIAS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
PROGRAMACIN POR OBJETIVOS . . . . . . . . . . . . . . . . . . . . . . . . .
Solucin con restricciones duras . . . . . . . . . . . . . . . . . . . . . . . . .
Solucin con restricciones blandas. . . . . . . . . . . . . . . . . . . . . . . .

125
129
133
136
139
142
143
145

Tabla de Contenido

6.6

PROGRAMACIN DE OBJETIVOS MLTIPLES. . . . . . . . . . . . . . . . . . .

RESUMEN DEL CAPTULO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
CUESTIONARIO DE AUTOEVALUACIN . . . . . . . . . . . . . . . . . . . . . .
EJERCICIOS PROPUESTOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
TALLER DE APLICACIN Y SEGUIMIENTO. . . . . . . . . . . . . . . . . . . . .
APNDICE: OPCIONES Y MENSAJES DEL SOLVER. . . . . . . . . . . . . . . .

VII

148
152
155
155
156
158

Tercera parte. Aplicacin del excel en modelos financieros . . . . . . . 165

7.
EVALUACIN DE PROYECTOS. . . . . . . . . . . . . . . . . . . . . . . . . . . .
7.1
MODELOS PARA LA EVALUACIN FINANCIERA DE PROYECTOS . . . . . . .
7.2
SENSIBILIDAD, OPTIMIZACIN Y SIMULACIN DE PROYECTOS . . . . . . .
7.2.1 Modelo de inversin en una tractomula. . . . . . . . . . . . . . . . . . . . .

Anlisis de sensibilidad de valor . . . . . . . . . . . . . . . . . . . . . . . . .

Anlisis de escenarios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Optimizacin. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Simulacin con macros . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Simulacin con Crystal Ball. . . . . . . . . . . . . . . . . . . . . . . . . . . . .
7.2.2 Modelo de inversin en producto para exportacin. . . . . . . . . . . . .

Anlisis de sensibilidad puntual. . . . . . . . . . . . . . . . . . . . . . . . . .

Anlisis de sensibilidad factible. . . . . . . . . . . . . . . . . . . . . . . . . .

Anlisis de escenarios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Anlisis de sensibilidad de rango. . . . . . . . . . . . . . . . . . . . . . . . .
Simulacin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
7.2.3 Modelo de inversin en una fbrica de tapetes. . . . . . . . . . . . . . . .

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

8.
PROYECCIN Y VALORACIN DE EMPRESAS. . . . . . . . . . . . . . . . . . .
8.1 IN_PLANNER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
8.1.1 Organizacin de In_planner . . . . . . . . . . . . . . . . . . . . . . . . . . . .
8.1.2 Planeacin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
8.1.3 Proyeccin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
8.1.4 Evaluacin. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
8.1.5 Control. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
8.2
MODELOS DE PROYECCIN DE ESTADOS FINANCIEROS. . . . . . . . . . . .

197
198
201
202
203
204
204
205

9.
CASOS DE MODELOS FINANCIEROS CON EXCEL. . . . . . . . . . . . . . . . .
9.1
MODELO DE PUNTO DE EQUILIBRIO. . . . . . . . . . . . . . . . . . . . . . . .

Anlisis de sensibilidad puntual. . . . . . . . . . . . . . . . . . . . . . . . . .

Anlisis de escenarios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Simulacin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
9.2
MODELO DE LANZAMIENTO DE UN PRODUCTO. . . . . . . . . . . . . . . . .

Anlisis de escenarios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Simulacin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
9.3
MODELO DE ANLISIS DE RENTABILIDAD - RIESGO DE UN BONO. . . . . .

Anlisis de sensibilidad y volatilidad del bono . . . . . . . . . . . . . . . .
9.4
MODELO DE COSTO VOLUMEN - UTILIDAD. . . . . . . . . . . . . . . . . . .

209
210
212
213
214
216
217
219
222
225
226

VIII

9.5


9.6
9.7


9.8

Jairo Gutirrez Carmona

Optimizacin maximizando la utilidad . . . . . . . . . . . . . . . . . . . . .

Punto de equilibrio con optimizacin. . . . . . . . . . . . . . . . . . . . . .
Sensibilidad del punto de equilibrio. . . . . . . . . . . . . . . . . . . . . . .
Simulacin del punto de equilibrio. . . . . . . . . . . . . . . . . . . . . . . .
MODELO PARA SELECCIONAR ALTERNATIVAS . . . . . . . . . . . . . . . . . .
Evaluacin de alternativas con modelos determinsticos . . . . . . . . .
Evaluacin de alternativas con modelos probabilsticos. . . . . . . . . .
MODELO DE EXPLOTACIN DE RECURSOS NATURALES. . . . . . . . . . . .
MODELO DE OPCIONES REALES. . . . . . . . . . . . . . . . . . . . . . . . . . .
Anlisis de sensibilidad de las opciones reales . . . . . . . . . . . . . . . .
Simulacin con opciones reales. . . . . . . . . . . . . . . . . . . . . . . . . .
MODELO DE OPTIMIZACIN DE UN PORTAFOLIO. . . . . . . . . . . . . . . .
Distribucin de probabilidades de la rentabilidad. . . . . . . . . . . . . .
Covarianza de dos inversiones. . . . . . . . . . . . . . . . . . . . . . . . . . .
Determinacin de las inversiones por el mercado. . . . . . . . . . . . . .
Rentabilidad de un portafolio. . . . . . . . . . . . . . . . . . . . . . . . . . .
Riesgo de un portafolio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Optimizacin de un portafolio por objetivos mltiples. . . . . . . . . . .

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

Cuarta parte. Complementos . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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 conversin de tasas . . . . . . . . . . . . . . . . . . . . . . .
Funciones de series uniformes. . . . . . . . . . . . . . . . . . . . . . . . . . .
Funciones de evaluacin de proyectos . . . . . . . . . . . . . . . . . . . . .
FUNCIONES ESTADISTICAS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Funciones de tendencia central. . . . . . . . . . . . . . . . . . . . . . . . . .
Funciones de dispersin. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Funciones de forma. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Funciones de regresin y correlacin . . . . . . . . . . . . . . . . . . . . . .
FUNCIONES MATEMTICAS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

269
270
270
272
279
284
286
291
293
295
299

11.
MANUAL DE VBA PARA EXCEL. . . . . . . . . . . . . . . . . . . . . . . . . . . .
11.1 MACROS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
11.1.1 Cmo grabar una macro. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
11.1.2 Cmo ejecutar una macro . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
11.1.3 Cmo editar una macro. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
11.2
ELEMENTOS DEL VBA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
11.2.1 Objetos, propiedades y mtodos. . . . . . . . . . . . . . . . . . . . . . . . .
11.2.2 Variables y constantes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
11.2.3 Instrucciones y estructuras. . . . . . . . . . . . . . . . . . . . . . . . . . . . .
11.2.4 Algunos mtodos del VBA. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

305
306
307
309
311
314
314
316
317
325

12.
12.1

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

FUNCIONES PERSONALIZADAS . . . . . . . . . . . . . . . . . . . . . . . . . . . 330

IX

Tabla de Contenido

12.2
FUNCIONES FINANCIERAS PERSONALIZADAS . . . . . . . . . . . . . . . . . .
12.2.1 Funciones para conversin de tasas . . . . . . . . . . . . . . . . . . . . . . .
12.2.2 Funciones de gradientes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
12.3
CMO INCORPORAR LAS FUNCIONES PERSONALIZADAS A SU
EQUIPO. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
13.

334
334
338
354

EJERCICIOS PROPUESTOS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357

BIBLIOGRAFA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365

PRESENTACIN

La creciente competencia en los negocios exige un proceso de toma de decisiones cada vez ms complejo, pero el
conocimiento cientfico que facilita esta
labor no se est incorporando al mismo
ritmo en el trabajo diario de las organizaciones; este desequilibrio, de no poder agregar tecnologa 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
financiera cuente con herramientas giles y precisas que le permitan analizar
una situacin y evaluar los resultados,
antes de tomar una decisin.
Ante esta necesidad de automatizar los
clculos para apoyar de manera eficiente la toma de decisiones, la hoja de clculo Excel ha ganado un espacio como
herramienta bsica de trabajo en el
rea financiera de las empresas. Por lo
tanto, la orientacin central de este libro ser presentar las herramientas que
la hoja de clculo Excel pone a disposicin de la gerencia para construir modelos financieros que permitan analizar el
impacto de una decisin en los objetivos
y resultados de la empresa.
El libro se ha dividido en cuatro partes:
La primera parte trata los aspectos tericos de los modelos financieros con el
fin de apreciar su utilidad para facilitar
el trabajo del rea financiera y presenta
las herramientas tcnicas que ofrece el
Excel para trabajar estas ideas;

La segunda parte da a conocer, de manera prctica, el uso de las herramientas bsicas del Excel en el modelaje financiero;
La tercera parte se concentra en presentar casos de modelos financieros 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 financieros
que vienen con el Excel.
Los modelos utilizados en el libro se explican grfica y conceptualmente, sin
embargo, para seguir adecuadamente el
desarrollo de los casos la mejor forma
de hacerlo es estudiando simultneamente los archivos que se incluyen en el
Complemento Virtual del SIL (Sistema
de Informacin en lnea). En el texto
del libro se anuncia cul archivo se est
utilizando en cada momento y en cada
archivo se incluye, adems del modelo
resuelto, ejercicios para complementar
el anlisis de la situacin que se est
estudiando. Es importante realizar los
ejercicios como una auto-evaluacin de
las herramientas utilizadas.
Para completar este trabajo se presenta un caso de aplicacin y seguimiento
que se desarrolla a lo largo del libro
aplicando las herramientas que se vean
en cada captulo; al final, se agrega el
enunciado de unos problemas, para que
se ejecute el trabajo de disear y construir los modelos financieros que ayudan
a resolverlos.

PRIMERA PARTE

ASPECTOS TERICOS
DE LOS MODELOS
FINANCIEROS

Esta primera parte expone el trabajo que debe realizar la gerencia para
asegurar el futuro de la empresa y muestra cmo los modelos financieros facilitan este trabajo, al permitirle analizar mejor los resultados
antes de tomar una decisin. Finalmente presenta las herramientas tcnicas que ofrece el Excel para trabajar los modelos financieros
Se incluyen los siguientes captulos:
1. PLANEACIN FINANCIERA
Expone la responsabilidad que tiene el rea financiera de las empresas y por lo tanto la importancia de contar con herramientas que
faciliten el anlisis de las decisiones.
2. MODELOS FINANCIEROS
Su objetivo es proponer un enfoque sistmico en la construccin de
modelos financieros que permite efectuar anlisis ms 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 financieros.

CONTENIDO
1.1 PROCESO DE PLANEACIN
1.2 OBJETIVOS DE LA EMPRESA
1.3 FUNCIONES DE LA GERENCIA FINANCIERA
1.4 OBJETIVO DE LA GERENCIA FINANCIERA
OBJETIVOS DEL CAPTULO
General
Ubicar al lector en la responsabilidad que tiene el rea financiera
de las empresas en lo relacionado con la supervivencia del negocio y por lo tanto la importancia de contar con herramientas que
faciliten el anlisis de las consecuencias futuras que traern las
decisiones que se adopten hoy.
Especficos
Presentar la empresa como un sistema integrado por elementos
interdependientes.
Insistir en que la planeacin requiere de planes y estrategias para
llevar la empresa al cumplimiento de objetivos posibles.
Plantear los objetivos genricos de las empresas y el papel que
juega la gerencia financiera para que dichos objetivos sean alcanzados.
Manifestar el aporte de las herramientas informticas para facilitar
el trabajo del administrador financiero.
De aprendizaje
Al estudiar este captulo se estar en capacidad de comprender la
interdependencia de las decisiones empresariales, por lo tanto se
adquirirn las bases para entender los fundamentos tericos de la
planeacin y se tomar conciencia de la necesidad de contar con
herramientas informticas para facilitar estos procesos.

1
Planeacin financiera

En este captulo se presentan los conceptos fundamentales de la

planeacin financiera, haciendo nfasis en el proceso de planeacin
como medio para alcanzar los objetivos de la empresa y en el papel
que juega la gerencia financiera en este proceso.
Se concluye que el gerente financiero tiene la difcil tarea de evaluar el impacto que tendrn 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 financieros.

Modelos Financieros
con EXCEL

Coleccin: Ciencias empresariales

rea: Contabilidad y finanzas
ISBN 978-958-648-549-4