• Author / Uploaded
• Cesar Echevarria

• Categories
• Interés
• Tasas de interés
• Costo de oportunidad
• Capital (Economía)
• Bancos

Citation preview

2013

EXCEL FINANCIERO El MS Excel hoy en día es la herramienta más utilizada en las finanzas Desarrollar mediante casos prácticos las funciones y herramientas Financieras de mayor importancia. Aplicar casos integrales de Financiamiento, que faciliten la aplicación y análisis de las funciones. Analizar resultados que permitan el Plan eamiento Financiero de la Empresa. Elaborar presupuestos y Estados Financieros, permitiendo el análisis de diversos escenarios. Realizar cálculos de costos y depreciación, analizando y graficando el punto de equilibrio. Resolver casos de simulación de Negocios.

Universidad Autónoma del Perú Docente: Ing. Johnny Pacheco Contreras 01/01/2013

Centro de Informática y Sistemas I.

DATOS GENERALES 1.1. Carrera Profesional 1.2. Ciclo 1.3. Créditos 1.4. Sesiones semanales 1.5. Duración 1.6. Docente

II.

: : : :

Administración y Contabilidad IX Ninguno 01 (04 horas cronológicas) 08 Semanas : Ing. Johnny Pacheco Contreras

FUNDAMENTACIÓN: El MS Excel hoy en día es la herramienta más utilizada en las finanzas, las cuales afectan de manera crítica las operaciones diarias dentro de la empresa. Sin embargo, según estudios sólo es aprovechada en un 30% en el mejor de los casos. Dado esta premisa surge la necesidad de explotar y/o aprovechar el conocimiento y dominio de las características especiales del Excel que faciliten y ayuden a la optimización de las decisiones financieras, a lo cual apunta este curso desde su concepción, ya que se enfatizará en una enseñanza práctica lo cual complementará los conocimientos en finanzas de los participantes. Para ello es indispensable que los alumnos tengan un conocimiento básico del Excel, y conocimientos de matemáticas financieras.

III.

OBJETIVOS

• Desarrollar mediante casos prácticos las funciones y herramientas Financieras de mayor importancia. Aplicar casos integrales de Financiamiento, que faciliten la aplicación y análisis de las funciones. • Analizar resultados que permitan el Planeamiento Financiero de la Empresa. Elaborar presupuestos y Estados Financieros, permitiendo el análisis de diversos escenarios. Realizar cálculos de costos y depreciación, analizando y graficando el punto de equilibrio. Resolver casos de simulación de Negocios. IV.

PRE REQUISITO

• Tener conocimientos previos de Excel a nivel intermedio Excel Básico y Avanzado

Elaborado por: Ing. Johnny Pacheco Contreras

Página 1 de 131

Centro de Informática y Sistemas

ÍNDICE Capítulo 1 Tasa de Interés ............................................................................................................................4 1.1.

El Interés ..........................................................................................................................................5

1.1.1.

El Interés Simple .........................................................................................................................6

1.1.2.

Interés Compuesto .....................................................................................................................9

1.2.

Las Tasas de Interés Efectiva y Nominal ..................................................................................12

1.3.

Tasa adelantada o descontada...................................................................................................17

1.4.

Funciones financieras en Excel ..................................................................................................21

1.4.1.

TASA ..........................................................................................................................................21

1.4.2.

TASA.NOMINAL .......................................................................................................................22

1.4.3.

INT.EFECTIVO..........................................................................................................................22

Capítulo 2 Valor del dinero en el tiempo: Uso de factores vencidos y anticipados .............................25 2.1.

Factores de Pago Único (F/P y P/F) ..........................................................................................25

2.2.

Factores de V.P. y de Capital en Series Uniformes (P/A y A/P) ............................................25

2.3.

Factores F. Amortización y Cantidad Compuesta Serie Uniforme (A/F y F/A) ....................26

2.4.

Valor Futuro (VF) ..........................................................................................................................27

2.5.

Valor Actual (VA) ..........................................................................................................................28

2.6.

PAGO (Pago) ................................................................................................................................29

2.7.

INTERÉS PAGADO (PAGOINT) ................................................................................................30

2.8.

INTERES PAGADO (PAGO.INT.ENTRE) .................................................................................30

2.9.

PAGO DEL CAPITAL (PAGOPRIN)...........................................................................................31

2.10.

PAGO DEL CAPITAL ACUMULADO (PAGO.PRINC.ENTRE) ..........................................32

2.11.

AMORTIZACIÓN DE PRÉSTAMOS ......................................................................................33

2.11.1.

El sistema Francés ...............................................................................................................33

2.11.2.

El sistema Alemán (amortización constante) ....................................................................34

2.11.3.

El sistema Americano (amortización al final del servicio de la deuda) ..........................35

2.11.4.

Amortización de leasing .......................................................................................................36

Capítulo 3 Financiamiento Empresarial.....................................................................................................40 3.1.

Con diferentes tasas de interés ..................................................................................................40

3.2.

Cuota uniforme vencida con período de gracia para capital...................................................41

Elaborado por: Ing. Johnny Pacheco Contreras

Página 2 de 131

Centro de Informática y Sistemas 3.3.

Cuota uniforme vencida con período de gracia para capital e intereses ..............................42

3.4.

Cuota uniforme vencida con cuotas extras ...............................................................................42

3.5.

Tasa real de financiamiento ........................................................................................................44

3.6.

Función TIR ...................................................................................................................................44

3.7.

TIR.NO.PER ..................................................................................................................................46

Capítulo 4 Presupuestos Operativos y Estados financieros ...................................................................49 4.1.1.

Método Lineal SLN ...................................................................................................................49

4.1.2.

Método de Saldo Decreciente DB ..........................................................................................50

4.1.3.

Método de Doble Saldo Decreciente DDB ............................................................................51

4.1.4.

Depreciación por el método del Fondo de Amortización e Intereses (NIC16),SYD ........52

Capítulo 5 Evaluación de Proyectos de Inversión. ..................................................................................66 5.1.

Valor Actual Neto: VAN ................................................................................................................66

5.2.

VNA.NO.PER ................................................................................................................................67

5.3.

Tasa Interna de Retorno: TIR .....................................................................................................68

5.4.

Coeficiente Beneficio/Costo: Análisis de resultados. ...............................................................70

5.5.

Período de recuperación..............................................................................................................72

Capítulo 6 Elaboración y Análisis de Proyectos de Inversión. ...............................................................75 6.1.

Análisis de Sensibilidad de los proyectos..................................................................................75

6.1.1.

Tabla de Datos ..........................................................................................................................75

6.1.2.

Buscar Objetivo .........................................................................................................................80

6.1.3.

Administrador de Escenarios ..................................................................................................82

6.2.

Análisis de Proyectos de Inversión ............................................................................................91

Capítulo 7 Simulación de escenarios de un Proyecto ...........................................................................106 7.1.

Valoración de inversiones en situación de riesgo ..................................................................106

7.2.

Simulación de Montecarlo .........................................................................................................108

7.3.

Configuración de Excel 2010 para la realización de simulaciones ......................................113

7.4.

Aplicación del método de Montecarlo a la valoración de una inversión ..............................121

Bibliografía ....................................................................................................................................................131

Elaborado por: Ing. Johnny Pacheco Contreras

Página 3 de 131

Centro de Informática y Sistemas

Capítulo 1 Tasa de Interés Si tuviéramos la alternativa de disponer de 1,000 nuevos soles hoy día frente a la posibilidad de disponer de la misma cantidad dentro de 1 año definitivamente la gran mayoría de nosotros salvo raras excepciones elegirían disponerlos en el momento presente. Obviamente esta elección se da en razón de la oportunidad de obtener liquidez hoy y que esta pueda ser destinada a variados requerimientos que prestaran una utilidad presente de consumo o inversión. Esta oportunidad de acceso y de satisfacer necesidades o inversiones presentes es lo que diferencia los 1,000 nuevos soles de hoy día y las que recibiríamos dentro de 1 año. Asimismo esto nos sugiere que debe existir alguna forma de equilibrar nuestra decisión y que compense el sacrificar esta oportunidad en el presente por un beneficio en el futuro.

Comparación y combinación de valores Solo es posible comparar o combinar valores que estén en el mismo punto del tiempo. ¿Por qué 10,000 Nuevos Soles hoy no valen lo mismo que 10,000 Nuevos Soles dentro de un año?

•

Pierden valor: Inflación

•

No sé si los voy a tener de vuelta (riesgo)

•

Prefiero comprar cosas hoy o invertirlos

Elaborado por: Ing. Johnny Pacheco Contreras

Página 4 de 131

Centro de Informática y Sistemas La tasa de interés es el precio del dinero, el cual se debe pagar/cobrar por tomarlo prestado/cederlo en préstamo en una situación determinada.

1.1.

El Interés

Interés es un índice utilizado para medir la rentabilidad de los ahorros o el coste de un crédito. Se expresa mediante un porcentaje. Es la manifestación del valor del dinero en el tiempo. El interés se paga cuando una persona u organización pide dinero prestado (obtiene un préstamo) y paga una cantidad mayor.

La tasa de interés que refleja la variación de un capital el que se ha convertido en un monto después de un periodo de tiempo.

I:

Interés

S:

Monto

P:

Capital

i:

Tasa de Interés

Factores que afectan al costo del dinero 

Oportunidad de producción Rendimientos disponibles dentro de una economía, provenientes de la inversión en activos productivos. Rendimiento sobre la inversión.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 5 de 131

Centro de Informática y Sistemas 

Preferencia de tiempo por el consumo Consumir ahora en vez de ahorrar para el futuro



Riesgo Probabilidad de que el reembolso no se de en el contexto del mercado financiero.



Inflación Alza generalizada de los precios en el tiempo.

1.1.1. El Interés Simple En una operación de INTERÉS SIMPLE el capital que genera los intereses, permanece constante durante el tiempo de vigencia de la transacción.

I:

Interés

P:

Principal, Capital inicial, valor presente

I:

Tasa de interés simple por unidad de tiempo

t:

Numero de periodos de tiempo (días, meses, etc.)

Se llama “Monto Final” o “Capitalización” o “Valor Futuro” a la suma del capital inicial y el interés ganado, es decir:

Ó

Debe entenderse que si “i” es anual “t” es el número de años, del mismo modo si “i” es mensual, “t” es el número de meses.

Ejemplo: Johnny Pacheco pide prestado un capital inicial de 1000 nuevos soles a una tasa de interés del 20% anual, ó 5% trimestral, el monto de interés por pagar en el año es de:

Elaborado por: Ing. Johnny Pacheco Contreras

Página 6 de 131

Centro de Informática y Sistemas I = 1000 x 0.2 x 1 I = 200 Nuevos Soles/año

Trimestre

Capital Inicial

Interés

Monto Final

1

1000

50

1050

2

1050

50

1100

3

1100

50

1150

4

1150

50

1200

200

Nuevos Soles

Ejercicio1 Al vencimiento de la deuda, se abona la cantidad de S/.10,000 luego de haber pactado inicialmente un préstamo de S/. 8,500 por un espacio de 12 meses. Cuál es el interés generado por la operación.

Ejercicio2 Calcular el interés simple producido por S/.30,000 durante 90 días a una tasa de interés anual de 5%.

Ejercicio3 ¿Cuál es la tasa de interés anual si invierto hoy S/.500 y recibo en un año S/.800?

Ejercicio4 Calcule el interés simple de un depósito de ahorros del banco Prestadito de S/.10,000 colocado a la tasa del 8% anual, durante a 30 días.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 7 de 131

Centro de Informática y Sistemas Ejercicio5 ¿Qué interés simple se obtendrá de S/.7,000 que se mantuvieron en una cuenta de ahorros en el Banco Prestadito, durante 5 meses, a la tasa del 6.5% anual?

Ejercicio6 Por un préstamo de S/. 1,500 a treinta días la señorita Gina debe pagar S/. 1,867.50 al final del plazo establecido; evaluar el crédito.

Ejercicio7 Un pequeño empresario obtuvo de un banco un avance en cuenta corriente de S/. 8,900 por 30 días, al final de lo cual su administrador le indicó que debía depositar en su cuenta corriente la suma de S/. 9,239.75 para cancelar tal operación de crédito. Calcular el costo mensual del sobregiro.

Ejercicio8 Evaluar el costo de las siguientes líneas de crédito: Línea 1:

Recibir S/. 1,200 hoy día y devolver S/. 1,260 dentro de un mes.

Línea 2:

Recibir S/. 3,500 hoy día y devolver S/. 3,640 dentro de un mes.

Cuál de las líneas es más barato.

Nota: El costo de un crédito, es igual a la tasa de interés a la cual crece el dinero en la operación. De ninguna manera debemos hacer referencia al interés cobrado como medida del costo de un crédito, aunque ello constituye un error muy común.

Ejercicio9 Un capital de S/. 2,000 estará sujeto al régimen de interés simple por 4 meses, a una tasa de 5% mensual; hallar el monto generado al final de dicho plazo.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 8 de 131

Centro de Informática y Sistemas Ejercicio10 Calcular el tiempo en el cual un capital se duplica en el régimen de interés simple si ganará una tasa de 2.5% mensual.

Ejercicio11 Un capital de S/. 3,000 generó un monto o valor futuro de S/. 3,960 luego de 8 meses. Si este capital inicial estuvo sujeto al régimen de interés simple, hallar la tasa porcentual de interés mensual de la operación.

Ejercicio12 Un capital de S/. 5,000 estuvo depositado bajo el régimen de interés simple en una institución financiera durante un plazo de 9 meses. Si la tasa de interés tuvo el siguiente comportamiento. 

3% mensual por los primeros 4 meses.



6% mensual por los siguientes 3 meses.



3% por los últimos 2 meses.

Hallar: a) El monto generado al final de los 9 meses. b) La tasa porcentual promedio mensual que se ganó.

Ejercicio13 Un padre de familia ha depositado en una cuenta de ahorros la suma de S/.7,500 en el banco Prestadito, del día 1 de agosto al 15 de Noviembre del 2012, a una tasa de interés simple de 4.5%. Posteriormente esta disminuyo a 3.2% a partir del 16 de septiembre y a partir del 01 de noviembre esta incremento a 3.6%. Cuál es el interés generado en la cuenta de ahorro.

1.1.2. Interés Compuesto Es la base de muchas operaciones comerciales. En el medio financiero es usual pagar intereses sobre el interés y sobre el capital inicial. Esto constituye el proceso de capitalización denominado “Interés Compuesto”.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 9 de 131

Centro de Informática y Sistemas En una tasa anual, la cual puede ser capitalizada en forma continua, diaria, mensual, bimensual, trimestral o semestral.

Calculo del Monto Si tenemos un capital (P) que gana una tasa (i) por periodo durante “n” periodos capitalizables, tendríamos al final del horizonte temporal el monto “S” siguiente:

Interés Compuesto

S:

Valor futuro o monto final.

P:

Valor presente o capital inicial.

n:

Numero de periodos.

i:

Tasa de interés por pagar en el periodo.

Es importante tener en cuenta que el interés efectivo anual que resulta bajo la modalidad del interés compuesto depende del interés por periodo y del número de estos.

“n” e “y” necesariamente deben estar expresadas en la misma unidad de tiempo (años, trimestres, meses, días, etc.)

El factor (1 + i)^n se conoce como el FACTOR SIMPLE DE CAPITALIZACIÓN COMPUESTO “FSC”.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 10 de 131

Centro de Informática y Sistemas Ejemplo: Se tienen 1000 Nuevos Soles en una cuenta de ahorros al 6% anual capitalizable trimestralmente ¿Cuál es el monto a cobrar al final del año?

Solución: i nominal trimestral = 6/4 = 1.5%

Trimestre

Capital Inicial

Interés

Monto Final

1

1,000.00

15.00

1,015.00

2

1,015.00

15.23

1,030.23

3

1,030.23

15.45

1,045.68

4

1,045.68

15.69

1,061.36

61.364

S=P(1+i)^n S = 1000 ( 1 + 0.015 ) ^ 4 S = 1061.36 Nuevos Soles

Costo de oportunidad Recibir S/.1,000 hoy no es igual que recibirlo después de un año ¿solamente por la inflación? El prestar dinero hoy implica dejar de hacer otras cosas con ese dinero (producir, inflación, riesgo de no recuperar el dinero y sobre todo no consumir hoy) y ese “sacrificio” debe ser compensado. Se está dejando de lado alguna oportunidad.

¿Cuál es el sacrificio que se está haciendo por no invertir dichos fondos hoy? Puede ser precio o costo que pagamos o el costo en el que incurrimos por no invertir en una opción en vez de otra, esto es, el costo de oportunidad. Costo de oportunidad del dinero es el Interés(I)

Elaborado por: Ing. Johnny Pacheco Contreras

Página 11 de 131

Centro de Informática y Sistemas

0 días

360 días

Po

1.2.

Po + I

Las Tasas de Interés Efectiva y Nominal

La Tasa de Interés Nominal y su relación con la Tasa de Interés Efectiva La tasa de interés efectiva es aquella que se utiliza en las fórmulas de la matemática financiera. En otras palabras, las tasas efectivas son aquellas que forman parte de los procesos de capitalización y de actualización.

En cambio, una tasa nominal, solamente es una definición o una forma de expresar una tasa efectiva. Las tasas nominales no se utilizan directamente en las fórmulas de la matemática financiera. En tal sentido, las tasas de interés nominales siempre deberán contar con la información de cómo se capitalizan. Por ejemplo, tenemos una Tasa Nominal Anual (TNA) que se capitaliza mensualmente, lo que significa que la tasa efectiva a ser usada es mensual. Otro caso sería contar con una TNA que se capitaliza trimestralmente, lo que significa que la tasa efectiva será trimestral. Ahora bien, ¿cómo se halla el valor de la tasa de interés efectiva? Las tasas nominales pueden ser divididas o multiplicadas de tal manera de convertirla en una tasa efectiva o también en una tasa proporcional.

Por ejemplo, se tiene una TNA del 24% que se capitaliza mensualmente, entonces la Tasa Efectiva Mensual (TEM) será:

TEM 

24%  0.02  2% 12

Elaborado por: Ing. Johnny Pacheco Contreras

Página 12 de 131

Centro de Informática y Sistemas Como conclusión de este análisis, las tasas nominales siempre deberán ir acompañadas de su forma de capitalización. La tasa nominal puede ser convertida a una tasa proporcional, sin afectar la forma de capitalización.

La Tasa de Interés Efectiva Las tasas efectivas son las que capitalizan o actualizan un monto de dinero. En otras palabras, son las que utilizan las fórmulas de la matemática financiera. Ahora bien, las tasas de interés efectivas pueden convertirse de un periodo a otro, es decir, se pueden hallar sus tasas de interés efectivas equivalentes. En otras palabras, toda tasa de interés efectiva de un periodo determinado de capitalización tiene su tasa de interés efectiva equivalente en otro periodo de capitalización. Una diferencia notoria con la tasa de interés nominal es que la efectiva no se divide ni se multiplica. Las tasas nominales pueden ser transformadas a otras proporcionalmente pero el periodo de capitalización sigue siendo el mismo. Un capital puede ser capitalizado con diferentes tasas efectivas las mismas que se relacionan con diferentes periodos de capitalización, pero el horizonte de capitalización puede ser el mismo.

Por ejemplo, si tenemos un capital hoy de 1,000.00 nuevos soles, y se desea capitalizar durante un año, entonces se puede efectuar la operación con una TEA, o también con su equivalente mensual, que vendría a ser una TEM pero que capitaliza doce veces en un año. También sería igual utilizar una TES como tasa equivalente de una TEA, teniendo en consideración que la TES capitaliza dos veces en un año. En el caso de las tasas nominales, se pueden transformar independientemente de la capitalización tal como se señalara anteriormente. En tal sentido, la tasa nominal se podría definir como “una presentación de cómo se va a capitalizar o actualizar un monto de dinero en un horizonte de tiempo”. Para la conversión de una tasa efectiva a otra tasa efectiva deberá tenerse en cuenta que el horizonte de tiempo de la operación financiera deberá ser el mismo mas no así el periodo capitalizable.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 13 de 131

Centro de Informática y Sistemas Hay que calcular una TEQuiero (Tasa Efectiva que quiero calcular): TEQuiero: Tasa Efectiva que se quiere hallar. TEDato: Tasa Efectiva que se tiene como dato. #Quiero: Periodo de Capitalización que quiero (Número de días del Periodo) #Dato: Periodo de Capitalización que tengo como dato (Número de días del Periodo)

TEQuiero

#Quiero   # Dato  (1  TEDato )  1  

Lo importante es que “#Quiero” y “#Dato” estén en días.

Ejemplo: Supongamos que tenemos un capital de S/.1.00 y se deposita en una cuenta de ahorros que paga una tasa efectiva mensual del 2%. Se desea hallar el valor futuro de este capital dentro de un año.

Solución: TEM: 2% ===> TEDato = 2% #Dato: Periodo de capitalización es un mes = 30 días TEQuiero = TEA #Quiero: como se desea calcular la TEA (anualidad que hay 12 meses) = 360 días

360   TEA  (1  2%) 30   1  26.82%  

El horizonte de tiempo de la operación financiera es de un año por lo que el coeficiente “#Quiero” es 360 días; y el coeficiente “#Dato” es 30 días porque la capitalización es cada mes. Luego para obtener la TEA simplemente se despeja de la ecuación cuyo resultado es 26.82%. Este resultado se interpreta de la siguiente manera: “si un capital se invierte ya sea en un depósito bancario o en un préstamo, y si este capital se capitaliza. mensualmente a una tasa efectiva mensual del 2%, a lo largo de un año, la ganancia será del 26.82%”.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 14 de 131

Centro de Informática y Sistemas La Tasa de Interés Efectiva a partir de una Tasa Nominal Una tasa de interés efectiva puede ser hallada a partir de una tasa nominal. Si se cuenta con una Tasa Nominal Anual que se capitaliza mensualmente entonces se puede hallar su respectiva Tasa Efectiva Anual. Por ejemplo, si se recibe la siguiente información: “una Tasa Nominal Anual (TNA) que se capitaliza mensualmente”. Esta es una forma de decir que la tasa efectiva es mensual. Si la TNA es del 24% capitalizable mensualmente entonces se deduce que la TEM es 2%. Si la TNA se capitaliza mensualmente entonces el coeficiente “m” es 12, porque son los meses que tiene un año. La letra “j” sería 0.24 (24%). Luego la tasa efectiva mensual TEM, será el ratio de “”j” y “m”:

j  0.02  2%  TEM m Ejercicios Ejercicio1 A partir de una tasa nominal anual del 10%, calcular las siguientes tasas nominales proporcionales. a. Diaria b. Mensualmente c. Trimestralmente d. 46 días e. 124 días f. 5 días g. 23 días Ejercicio2 Los certificados de depósito en moneda extranjera del Banco ofrece una tasa efectiva anual (TEA) del 3.5% anual. ¿Cuál es la tasa nominal anual que se debe aplicar si la capitalización fuera mensual?

Elaborado por: Ing. Johnny Pacheco Contreras

Página 15 de 131

Centro de Informática y Sistemas Ejercicio3 Usted ha solicitado un préstamo por S/.10,000 a una institución financiera, cobra una tasa efectiva anual máxima (TEA) del 12%, si la capitalización que utiliza el banco para este tipo de operaciones es semestral, ¿Cuál es la tasa nominal anual que debe aplicar a dicha operación?

Ejercicio4 El banco VES ofrece en sus depósitos de ahorros a la vista una tasa nominal anual de 4.5%. Si la capitalización es diaria ¿Cuál es la tasa efectiva anual (TEA)?

Ejercicio5 Usted cuenta con un certificado de depósitos por S/. 6,000, que ofrece una tasa nominal anual (TNA) de 2.8%. Si la capitalización de los intereses es cada treinta días, ¿Cuál es la tasa efectiva anual (TEA)?

Ejercicio6 ¿Cuál es el importe de interés que debe recibir en un periodo de un año, por un depósito de S/.30,000. Si la tasa nominal anual ofrecida es del 6%, con capitalización diaria?

Ejercicio7 ¿A cuánto asciende los intereses de un depósito de ahorros por S/,50,000,. Si la tasa nominal anual es de 4.5%, con capitalización diaria, si este se retira después de 42 días?

Ejercicio8 Usted ha solicitado un préstamo por S/.15,600, a una tasa nominal anual del 16% con capitalización diaria. ¿Cuál es el importe a cancelar al vencimiento, si este se vence dentro de 30 días?

Elaborado por: Ing. Johnny Pacheco Contreras

Página 16 de 131

Centro de Informática y Sistemas Ejercicio9 Usted cuenta con un certificado en moneda extranjera por US$ 10,000 el mismo que vence dentro de 90 días, la tasa nominal que ofrece el banco es de 3% con capitalización diaria ¿Cuál es el importe de interés a cobrar al vencimiento?

Ejercicio10 Una empresa ha solicitado un préstamo por US$.25,000 a una tasa nominal anual del 18%, con capitalización cada 60 días. ¿Cuál es el importe a cancelar si este se vence dentro de un año?

Ejercicio11 El banco VES ofrece en sus cuentas de ahorros a la vista una tasa nominal anual del 4.6%, con capitalización diaria. Si usted deposita hoy US$.1,400 ¿Cuál es el importe que tendrá dentro de 63 días?

1.3.

Tasa adelantada o descontada

Tasa adelantada o descontada es aquella tasa que se aplica cuando los intereses de una operación financiera son cobrados por adelantado.

Son tasas adelantadas o descontadas, dado que se aplican sobre valores nominales de títulos y valores. Se obtienen a partir de una tasa efectiva vencida que es descontada, es decir traída al valor presente. La fórmula descuenta (trae al punto cero) el interés vencido, dado que cualquier monto de hoy vale menos que uno en el futuro. Es una aplicación del concepto del valor presente.

i id  1 i

i

id 1  id

i: tasa de interés vencida. id: tasa adelantada o descontada.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 17 de 131

Centro de Informática y Sistemas Intereses compensatorio Representada por la tasa activa para las colocaciones y la tasa pasiva para las captaciones que compensar los intereses de los días después del vencimiento original.

Intereses moratorio Cuando el deudor no cumple con cancelar lo que debe en la fecha acordada, este incurre en mora al día siguiente de vencimiento. Se calcula sobre el principal adeudado. Esta tasa es normada por el BCR en términos efectivos, de acuerdo con el código civil.

Retención El banco retiene un porcentaje del préstamo como un colateral y crea un depósito de ahorro. El dinero de la retención es devuelto a la cancelación del préstamo. Al colateral se le considera como una garantía, pero en realidad es una forma de incrementar la rentabilidad del préstamo.

Comisión Flat Gastos que no están relacionados directamente con la tasa de interés, el tiempo, ni con el monto del préstamo.

Ejercicios Ejercicio1 Determinar cuál es la tasa efectiva anual, por un depósito de S/. 850 sujeto a una tasa nominal anual del 42% que se capitaliza mensualmente. Luego de 6 meses.

Ejercicio2 Tenemos una tarjeta de crédito cuya tasa de interés es 2.5% mensual. Determine la tasa anual que realmente cuesta.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 18 de 131

Centro de Informática y Sistemas Ejercicio3 Calcule la tasa de interés efectiva anual que se recibe de un depósito bancario si la tasa Nominal es de 24% y se capitaliza: a) Anualmente b) Semestralmente c) Trimestralmente d) Mensualmente e) Diariamente

Ejercicio4 Que tasa nominal anual, capitalizable mensualmente resulta equivalente a una tasa nominal anual de 16% que se capitaliza trimestralmente.

Ejercicio5 ¿A qué tasa se habrán invertido S/. 350,00, si al cabo de tres años se han obtenido S/. 393,70 y la capitalización se hizo anualmente?

Ejercicio6 El Banco A paga un interés del 8% anual capitalizado semestralmente. El Banco B paga 7.9% anual capitalizable mensualmente, y el Banco C paga una tasa de 7.8% anual capitalizable diariamente. Si usted tiene S/. 500 para invertir. Qué Banco elegiría si el período de depósito es de, al menos un año?

Ejercicio7 Una casa comercial anuncia "Compre cualquier artículo de esta tienda con un cargo de interés nominal del 15% anual. Para su comodidad salde su deuda en cómodos pagos semanales iguales". Cuál es la tasa de interés efectiva anual que cobra la tienda?.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 19 de 131

Centro de Informática y Sistemas Ejercicio8 Se tiene una tasa efectiva del 32% capitalizable por mes adelantado Cuál es la tasa nominal actual?

Ejercicio9 ¿Qué tasa adelantada (descontada) equivalente a una tasa efectiva trimestral vencida del orden del 3.5% se debe aplicar en una operación a 90 días?

Ejercicio11 Cierto banco otorga un préstamo a una empresa a una tasa nominal del 24% anual capitalizable por trimestre adelantado. Se pide hallar la tasa efectiva anual.

Ejercicio12 Calcular el factor de descuento (tasa adelantada o descontadas) que se debe aplicar a un pagare de S/.20,000, cuyo vencimiento será dentro de 43 días, si la TEA es de 16%.

Ejercicio13 Calcular el factor de descuento que se debe aplicar a un préstamo con pago de interés adelantado a 120 días, si la TEA acordada es del 18%

Ejercicio14 Una institución de financiamiento local anuncia su club "Playa Sur". Una persona puede pedir S/.2,000 prestado y pagar S/. 51 durante los próximos 50 meses, comenzando 30 días después de recibir el dinero. Calcúlese la tasa de interés nominal para este préstamo ¿Cuál es la tasa efectiva de interés?

Elaborado por: Ing. Johnny Pacheco Contreras

Página 20 de 131

Centro de Informática y Sistemas 1.4.

Funciones financieras en Excel

1.4.1. TASA Devuelve la tasa de interés por período de una anualidad. TASA se calcula por iteración y puede tener cero o más soluciones. Si los resultados sucesivos de TASA no convergen dentro de 0,0000001 después de 20 iteraciones, TASA devuelve el valor de error #¡NUM!

Sintaxis

=TASA(núm_per; pago; va; [vf]; [tipo]; [estimar]) La sintaxis de la función TASA tiene los siguientes argumentos: •

Núm_per Obligatorio. El número total de períodos de pago en una anualidad.

•

Pago

Obligatorio. El pago efectuado en cada período, que no puede variar durante la vida

de la anualidad. Generalmente el argumento pago incluye el capital y el interés, pero no incluye ningún otro arancel o impuesto. Si se omite el argumento pago, deberá incluirse el argumento vf. •

Va

Obligatorio. El valor actual, es decir, el valor total que tiene actualmente una serie de

pagos futuros. •

Vf

Obligatorio. El valor futuro o un saldo en efectivo que se desea lograr después de

efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0 (por ejemplo, el valor futuro de un préstamo es 0). •

Tipo Opcional. El número 0 ó 1 e indica cuándo vencen los pagos.

•

Estimar

Opcional. La estimación de la tasa de interés. si el argumento estimar se omite se

supone que es 10%

Figura 2.8 En la celda C8 la Función Tasa calcula la tasa de interés mensual, que en la celda C8 al multiplicarla por 12 periodicidad anual devuelve la tasa anual.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 21 de 131

Centro de Informática y Sistemas 1.4.2. TASA.NOMINAL Devuelve la tasa de interés nominal anual si se conocen la tasa efectiva y el número de períodos de interés compuesto por año.

Sintaxis

=TASA.NOMINAL(tasa_efectiva;núm_per_año) La sintaxis de la función TASA.NOMINAL tiene los siguientes argumentos: •

Tasa_efectiva Obligatorio. La tasa de interés efectiva.

•

Núm_per_año Obligatorio. El número de períodos de interés compuesto por año.

Nota: TASA.NOMINAL está relacionado con INT.EFECTIVO como se indica a continuación:

1.4.3. INT.EFECTIVO Devuelve la tasa de interés anual efectiva, si se conocen la tasa de interés anual nominal y el número de períodos de interés compuesto por año.

Sintaxis

=INT.EFECTIVO(int_nominal; núm_per_año) La sintaxis de la función INT.EFECTIVO tiene los siguientes argumentos: •

Int_nominal Obligatorio. La tasa de interés nominal.

•

Núm_per_año Obligatorio. El número de períodos de interés compuesto por año.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 22 de 131

Centro de Informática y Sistemas Casos: 1. La empresa Soluciones JPC SAC (www.todoexcel.edu.pe) ha emitido la factura N° 01-007002013 por S/.6,000 a DataRop SAC dicho documento será “canjeado” (financiado) con tres letras de cambio, a una tasa efectiva anual del 14%. Los vencimientos de cada documento será: 30, 45 y 60 días. Calcule el valor de cada letra de cambio. 

Cuando una factura será financiada, se utiliza la palabra “canje”, dado que el valor legal de la factura es transferido al documento “letra de cambio”.



Asimismo, cuando no se ha especificado el número de documento a financiar, es “práctica común” dividir el importe entre el número de letras de cambio a canjear.

2. La factura N° ° 01-007-002014, por USD.3,250 será canjeada (financiada) con dos letras de cambio, con vencimiento a 30 y 60 días. Se ha negociado que la primera letra sea emitido por 30% del valor de la factura y la segunda por el saldo. La tasa pactada es equivalente a una TEA del 17% por intereses que genere el canje de la factura (gastos financieros) se debe emitir una nota de contabilidad, donde se especifique los intereses cobrados más el 18% correspondiente al Impuesto General a los Bienes y servicios (IGV). a. Determinar el valor de las letras de cambio, asumiendo que el IGV, será cancelada por el cliente al momento de aceptar las letras. b. Determinar el valor de las letras de cambio, asumiendo que el importe correspondiente a los impuestos de los gastos financieros serán parte de la financiación.

3. La empresa Soluciones JPC SAC (www.todoexcel.edu.pe) ha emitido la factura N° 01-007002019 por S/.15,600 a su cliente DataRop SAC. El documento será canjeado con tres letras de cambio, con vencimiento a 45, 60 y 90 días, la empresa Soluciones JPC SAC cobra a sus clientes una tasa efectiva de anual (TEA) del 22% cada vez que realiza un canje de letras. a. Calcule el valor de cada letra incluyendo los intereses vencidos correspondientes. b. Asume que el impuesto general a las ventas y servicio (IGV 18%), debe ser incluido en las letras de cambio.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 23 de 131

Centro de Informática y Sistemas 4. La factura N° 01-007-002021 US$. 9,000, sera canjeada con tres letras de cambio, con vencimiento a 35, 65 y 75 días, a una tasa efectiva anual del 19%. Dichos documentos, una vez que sean aceptados, se enviaran a un banco local para su respectivo descuento y abono en cuenta corriente bancaria. El banco por operaciones de decuento, ofrece una TEA del 21%. a. Calcular el valor de cada letra. b. Determinar cuál es el importe neto que debe abonar el banco por las tres letras de cambio en la cuenta corriente de la empresa.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 24 de 131

Centro de Informática y Sistemas

Capítulo 2 Valor del dinero en el tiempo: Uso de factores vencidos y anticipados 2.1.

Factores de Pago Único (F/P y P/F)

Los factores de pago único, consideran el tiempo y la tasa de interés. Luego, ellos constituyen el camino adecuado para la transformación de alternativas en términos de una base temporal común. Estos factores son deducidos con base a la generación del interés compuesto para determinar la cantidad futura o presente en un momento dado del tiempo.

Es el que determina la cantidad de dinero F que se acumula después de “n” periodos, a partir de un único valor presente P con interés compuesto una vez por período.

(

)

Si se invierte la situación para calcular el valor “P” para una cantidad dada “F” que ocurre “n” periodos en el futuro, entonces hallamos “P”.

(

2.2.

(

)

)

Factores de V.P. y de Capital en Series Uniformes (P/A y A/P)

Es utilizado para calcular el valor presente P equivalente en el año 0 para una serie uniforme de final de periodo de valores A, que empiezan al final del período 1 y se extiende durante “n” periodos.

( (

Elaborado por: Ing. Johnny Pacheco Contreras

) (

)

)

Página 25 de 131

Centro de Informática y Sistemas

Con él se calcula el valor anual uniforme equivalente “A” durante “n” años de una “P” dada en el año “0”, cuando la tasa de interés es “i”.

( (

2.3.

(

) )

)

Factores F. Amortización y Cantidad Compuesta Serie Uniforme (A/F y F/A)

El Factor (A/F) es el factor de fondo de amortización o A/F, el cual determina la serie de valor anual uniforme que sería equivalente a un valor futuro F.

(

(

( (

Elaborado por: Ing. Johnny Pacheco Contreras

)

)

)

)

Página 26 de 131

Centro de Informática y Sistemas Notaciones y ecuaciones:

Formula Factor

Notación Factor simple

de

capitalización Factor

simple

de

actualización Factor de recuperación del capital Factor de actualización de una serie Factor de capitalización de una serie Factor de Deposito del Fondo de Amortización

2.4.

FSC

FSA

FRC

FAS

FCS

FDFA

Formula

(1+i)^n 1 (1+i)^n i (1+i)^n (1+i)^n-1 (1+i)^n-1 i (1+i)^n (1+i)^n-1 i i (1+i)^n-1

Función Excel

F= P (1+i)^n

P=

A=P

P= A

F= A

A=F

F (1+i)^n i (1+i)^n (1+i)^n-1 (1+i)^n-1 i (1+i)^n (1+i)^n-1 i i (1+i)^n-1

VF(tasa;núm_per;[va])

VA(tasa;núm_per;[vf])

PAGO(tasa;nper;va)

VA(tasa;núm_per;pago)

VF(tasa;núm_per;pago)

PAGO(tasa;nper;vf)

Valor Futuro (VF)

Devuelve el valor futuro de una inversión basándose en pagos periódicos constantes y en una tasa de interés constante. Sintaxis

=VF(tasa;núm_per;pago;[va];[tipo])  Tasa Obligatorio. La tasa de interés por período.  Núm_per Obligatorio. El número total de períodos de pago en una anualidad.  Pago Obligatorio. El pago que se efectúa cada período y que no puede cambiar durante la vigencia de la anualidad. Generalmente, el argumento pago incluye el capital y el interés pero ningún otro arancel o impuesto. Si se omite el argumento pago, se deberá incluir el argumento va.  Va Opcional. El valor actual o el importe total de una serie de pagos futuros. Si se omite el argumento va, se considerará 0 (cero) y se deberá incluir el argumento pago.  Tipo Opcional. El número 0 ó 1. Indica cuándo vencen los pagos. Si se omite el tipo, se considera que es 0. Elaborado por: Ing. Johnny Pacheco Contreras

Página 27 de 131

Centro de Informática y Sistemas

DEFINA TIPO COMO

SI LOS PAGOS VENCEN

0

Al final del período

1

Al inicio del período

Ejemplo:

Observaciones  Mantenga uniformidad en el uso de las unidades con las que especifica los argumentos tasa y núm_per. Si realiza pagos mensuales sobre un préstamo de 4 años con un interés anual del 12 por ciento, use 12%/12 para el argumento tasa y 4*12 para el argumento núm_per. Si realiza pagos anuales sobre el mismo préstamo, use 12 por ciento para el argumento tasa y 4 para el argumento núm_per.  En todos los argumentos el efectivo que paga, por ejemplo depósitos en cuentas de ahorros, se representa con números negativos; el efectivo que recibe, por ejemplo cheques de dividendos, se representa con números positivos. 2.5.

Valor Actual (VA)

Devuelve el valor actual de una inversión. El valor actual es el valor que tiene actualmente la suma de una serie de pagos que se efectuarán en el futuro. Sintaxis

=VA(tasa; núm_per; pago; [vf]; [tipo])  Tasa Obligatorio. La tasa de interés por período.  Núm_per Obligatorio. El número total de períodos de pago en una anualidad.  Pago Obligatorio. El pago efectuado en cada período, que no puede variar durante la anualidad. Si se omite el argumento pago, deberá incluirse el argumento vf.  Vf Opcional. El valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si se omite el argumento vf, deberá incluirse el argumento pago. Elaborado por: Ing. Johnny Pacheco Contreras

Página 28 de 131

Centro de Informática y Sistemas  Tipo Opcional. El número 0 ó 1 e indica cuándo vencen los pagos. Ejemplo:

2.6.

PAGO (Pago)

Calcula el pago de un préstamo basándose en pagos constantes y en una tasa de interés constante. Sintaxis

= PAGO(tasa;nper;va;vf;tipo)    

Tasa Obligatorio. Es el tipo de interés del préstamo. Nper Obligatorio. Es el número total de pagos del préstamo. Va Obligatorio. Es el valor actual, o la cantidad total de una serie de futuros pagos. Vf Opcional. Es el valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0 (es decir, el valor futuro de un préstamo es 0).  Tipo Opcional. Es el número 0 (cero) o 1 e indica cuándo vencen los pagos. Ejemplo:

Elaborado por: Ing. Johnny Pacheco Contreras

Página 29 de 131

Centro de Informática y Sistemas 2.7.

INTERÉS PAGADO (PAGOINT)

Devuelve el interés pagado en un período específico por una inversión basándose en pagos periódicos constantes y en una tasa de interés constante. Sintaxis

= PAGOINT(tasa; período; núm_per; va; [vf]; [tipo])  Tasa Obligatorio. La tasa de interés por período.  Período Obligatorio. El período para el que se desea calcular el interés; debe estar entre 1 y el argumento núm_per.  Núm_per Obligatorio. El número total de períodos de pago en una anualidad.  Va Obligatorio. El valor actual o la suma total de una serie de futuros pagos.  Vf Opcional. El valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0  Tipo Opcional. El número 0 ó 1; indica cuándo vencen los pagos. Si tipo se omite, se considera que es 0. Ejemplo:

2.8.

INTERES PAGADO (PAGO.INT.ENTRE)

Devuelve la cantidad de interés pagado de un préstamo entre los argumentos per_inicial y per_final. Sintaxis

= PAGO.INT.ENTRE(tasa; núm_per; vp; per_inicial; per_final; tipo)

Elaborado por: Ing. Johnny Pacheco Contreras

Página 30 de 131

Centro de Informática y Sistemas    

Tasa Obligatorio. La tasa de interés. Núm_per Obligatorio. El número total de períodos de pago. Vp Obligatorio. El valor actual. Per_inicial Obligatorio. El primer período del cálculo. Los períodos de pago se numeran comenzando por 1.  Per_final Obligatorio. El último período del cálculo.  Tipo Obligatorio. El momento del pago. Ejemplo:

2.9.

PAGO DEL CAPITAL (PAGOPRIN)

Devuelve el pago sobre el capital de una inversión durante un período determinado basándose en pagos periódicos y constantes, y en una tasa de interés constante. Sintaxis

= PAGOPRIN(tasa; período; núm_per; va; [vf]; [tipo])    

Tasa Obligatorio. La tasa de interés por período. Período Obligatorio. El período, que debe estar entre 1 y el valor de núm_per. Núm_per Obligatorio. El número total de períodos de pago en una anualidad. Va Obligatorio. El valor actual, es decir, el valor total que tiene actualmente una serie de pagos futuros.  Vf Obligatorio. El valor futuro o un saldo en efectivo que se desea lograr después de efectuar el último pago. Si el argumento vf se omite, se supone que el valor es 0 (es decir, el valor futuro de un préstamo es 0).  Tipo Opcional. El número 0 ó 1 e indica cuándo vencen los pagos.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 31 de 131

Centro de Informática y Sistemas Ejemplo:

2.10. PAGO DEL CAPITAL ACUMULADO (PAGO.PRINC.ENTRE) Devuelve la cantidad acumulada de capital pagado de un préstamo entre los períodos (per_inicial y per_final). Sintaxis

= PAGO.PRINC.ENTRE(tasa; núm_per; vp; per_inicial; per_final; tipo)    

Tasa Obligatorio. La tasa de interés. Núm_per Obligatorio. El número total de períodos de pago. Vp Obligatorio. El valor actual. Per_inicial Obligatorio. El primer período del cálculo. Los períodos de pago se numeran comenzando por 1.  Per_final Obligatorio. El último período del cálculo.  Tipo Obligatorio. El momento del pago. Ejemplo:

Elaborado por: Ing. Johnny Pacheco Contreras

Página 32 de 131

Centro de Informática y Sistemas 2.11. AMORTIZACIÓN DE PRÉSTAMOS La obligación de devolver un préstamo recibido de un banco es un pasivo, cuyo importe se va reintegrando en varios pagos diferidos en el tiempo. La parte de capital (o principal) que se cancela en cada uno de esos pagos es una amortización. Los métodos más frecuentes para repartir el importe en el tiempo y segregar principal de intereses son el Francés, Alemán y el Americano. Todos estos métodos son correctos desde el punto de vista contable y están basados en el concepto de interés compuesto. Las condiciones pactadas al momento de acordar el préstamo determinan cual de los sistemas se utilizará. 2.11.1.

El sistema Francés

En el sistema de amortización Francés las cuotas (capital + intereses) son iguales y consecutivas. En este sistema al comienzo del crédito se paga una proporción mayor de interés y menor de capital, esta proporción se va invirtiendo a lo largo de la cancelación del crédito pasando a abonar desde una determinada cuota en adelante más capital que interés. Cuando más alta sea la tasa de interés menor será la proporción de capital que se cancele en la primera cuota. Ejemplo con los datos en la figura mostrada ingresar los siguientes valores: 1. Ingresar el Valor de 1 en la celda C12 y 2 en la celda C13 2. Seleccionar el Rango C12:C13 y arrastrar modo copiar hasta C19. 3. Para el cálculo del saldo en la celda D12 escribir =C2 4. En la celda D13 escribir =D12-F12 y luego seleccionarlo y arrastrar modo copiar hasta D19. 5. En la celda E12 escribir =-PAGOINT($C$3,C12,$C$4,$C$2) 6. Seleccionar E12 y arrastrar modo copiar hasta E19. 7. En la celda F12 escribir =-PAGOPRIN($C$3,C12,$C$4,$C$2) 8. Seleccionar F12 y arrastrar modo copiar hasta F19. 9. En la celda G12 escribir =-PAGO($C$3,$C$4,$C$2) 10. Seleccionar G12 y arrastrar modo copiar hasta G19. Una vez ingresado las funciones tenemos un cuadro con la amortización francés.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 33 de 131

Centro de Informática y Sistemas

2.11.2.

El sistema Alemán (amortización constante)

El sistema Alemán determina que la amortización de capital sea fija. Por lo tanto los intereses y la cuota total serán decrecientes. Ejemplo con los datos en la figura mostrada ingresar los siguientes valores: 1. Ingresar el Valor de 1 en la celda C12 y 2 en la celda C13 2. Seleccionar el Rango C12:C13 y arrastrar modo copiar hasta C19. 3. Para el cálculo del saldo en la celda D12 escribir =C2 4. En la celda D13 escribir =D12-F12 y luego seleccionarlo y arrastrar modo copiar hasta D19. 5. En la celda E12 escribir =D12*$C$3 6. Seleccionar E12 y arrastrar modo copiar hasta E19. 7. En la celda F12 escribir =$C$2/$C$4 8. Seleccionar F12 y arrastrar modo copiar hasta F19. 9. En la celda G12 escribir =E12+F12 10. Seleccionar G12 y arrastrar modo copiar hasta G19. Una vez ingresado las funciones tenemos un cuadro con la amortización alemán.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 34 de 131

Centro de Informática y Sistemas

2.11.3.

El sistema Americano (amortización al final del servicio de la deuda)

Establece una sola amortización al final de un período, en el cual solo se pagan intereses. Al no haber pagos de capital, los intereses son fijos. Ejemplo con los datos en la figura mostrada ingresar los siguientes valores: 1. Ingresar el Valor de 1 en la celda C12 y 2 en la celda C13 2. Seleccionar el Rango C12:C13 y arrastrar modo copiar hasta C19. 3. Para el cálculo del saldo en la celda D12 escribir =$C$2 4. Seleccionar la celda D12 y arrastrar modo copiar hasta D19. 5. En la celda E12 escribir = D12*$C$3 6. Seleccionar E12 y arrastrar modo copiar hasta E19. 7. En el rango F12:F18 escribir cero. 8. Seleccionar F19 escribir =C2. 9. En la celda G12 escribir =E12+F12 10. Seleccionar G12 y arrastrar modo copiar hasta G19. Una vez ingresado las funciones tenemos un cuadro con la amortización Americano.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 35 de 131

Centro de Informática y Sistemas

2.11.4.

Amortización de leasing

El leasing es un contrato de arrendamiento financiero que incluye una opción de compra para el arrendatario sobre el bien recibido en leasing, que podrá ejercitar al final del contrato por un precio que se denomina valor residual y que, obligatoriamente, debe figurar en el contrato de arrendamiento financiero. ¿Por qué es una forma de financiación a largo plazo? La respuesta es que permite la utilización de bienes (normalmente inmovilizado) sin realizar una alta inversión y, por consiguiente, procura al arrendatario una agilización de su tesorería. Ejemplo con los datos en la figura mostrada ingresar los siguientes valores: 1. Ingresar el valor de cero en la celda A9. 2. Ingresar el valor de 1 en la celda A10. 3. Seleccionar el rango A9:A10 y modo copiar arrastrar hasta la celda A23 4. Ingresar la formula =B3, en el campo Pendiente en la celda E8. 5. Ingresar la función =PAGO($B$4,$B$5+1,-$B$3,,1)en la celda B9. Seleccionar la celda B9 modo copiar y arrastrar hasta la celda B23. 6. Ingresar la formula =B9-C9 en la celda D9. Seleccionar la celda D9 modo copiar y arrastrar hasta la celda D23.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 36 de 131

Centro de Informática y Sistemas 7. Ingresar la formula =E8-D9 en la celda E9. Seleccionar la celda E9 modo copiar y arrastrar hasta la celda E23. 8. Ingresar la formula =E8-D9+B9 en la celda F9. Seleccionar la celda F9 modo copiar y arrastrar hasta la celda F23. 9. Ingresar la formula =G8+D9 en la celda G9. Seleccionar la celda G9 modo copiar y arrastrar hasta la celda G23. 10. Ingresar la formula =B9*$H$6 en la celda H9. Seleccionar la celda H9 modo copiar y arrastrar hasta la celda H23. 11. Ingresar la formula =B9+H9 en la celda I9. Seleccionar la celda I9 modo copiar y arrastrar hasta la celda I23. 12. Ingresar la formula =E9*$B$4 en la celda C9. Seleccionar la celda C9 modo copiar y arrastrar hasta la celda C23.

Ejercicios Ejercicio1 Elaborar un cuadro de amortización de Préstamos por el sistema francés para el siguiente préstamo: Importe del préstamo S/. 60,000.00 Duración en años 5 Nº de pagos por año 4 TEA 22.00%

Elaborado por: Ing. Johnny Pacheco Contreras

Página 37 de 131

Centro de Informática y Sistemas Ejercicio2 Elaborar un cuadro de amortización de Préstamos por el sistema alemán para el siguiente préstamo: Importe del préstamo S/. 60,000.00 Duración en años 5 Nº de pagos por año 4 TEA 22.00% Ejercicio3 Elaborar un cuadro de amortización de Préstamos por el sistema americano para el siguiente préstamo: Importe del préstamo S/. 60,000.00 Duración en años 5 Nº de pagos por año 4 TEA 22.00% Ejercicio4 Realizar un cuadro de amortización mediante el método francés que calcule de manera automática cuando modificamos la tasa, numero de periodo o el valor del prestamo. Solución:

1. 2. 3. 4. 5. 6.

En la Celda F11 escribir: En la Celda B11 escribir: En la Celda C12 escribir: En la Celda D12 escribir: En la Celda E12 escribir: En la Celda F12 escribir:

=C7 0 =PAGO(C5;C6;-C7) =F11*$C$5 =C12-D12 =F11-E12

Elaborado por: Ing. Johnny Pacheco Contreras

Página 38 de 131

Centro de Informática y Sistemas 7. En la Celda B13 escribir: =SI(CODIGO(B12)=32;" ";SI(B12+1>$C$6;" ";B12+1)) 8. Arrastrar la formula hasta la celda B132 9. En la Celda C13 escribir: =SI(B13 Nueva Regla

23. Seleccionar la opción: Utilice una fórmula que determine las celdas para aplicar formato a. En el Campo: Dar Formato a los valores donde está la formula sea verdadera: =$B13> Grupo Herramientas de Datos >> Análisis Y si >> seleccionar Tabla de Datos...

3. Mostrar el cuadro de dialogo Tabla de Datos y luego seleccionar en el campo Celda de entrada (Columna) D1 (seleccionamos en columna porque los datos de los precios se encuentran en columna) y luego hacer clic en aceptar.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 76 de 131

Centro de Informática y Sistemas 4. El resultado final es:

En el rango D11:F11, el Beneficio, los Ingresos y el Costo Variable son calculados para un precio de S/. 1.00. En las celdas D12:F12, el Beneficio, Los Ingresos y el Costo Variable son calculados para un precio de S/. 1.25 y a través del rango de precios. El precio que maximiza el beneficio de todos los precios listados es S/. 3.75 debería producir un beneficio anual de S/. 58,125.00 un ingreso anual de S/. 117,187.00 y un costo variable anual de S/. 14,062.50.

Supongamos que queremos determinar cómo varia el beneficio anual así como el precio varía de S/. 1.5 a S/. 5.0 (incremento de S/. 0.25) y el costo unitario variable varia de S/. 0.30 a S/. 0.60 (con incremento de S/. 0.05). Debido a que estamos cambiando dos entradas, necesitamos una tabla de datos bidireccional. Listo los valores para la entrada hacia abajo en la primera columna del rango de la tabla (H1:H25 para los valores de Precio) y para los valores del costo variable es en la fila (I10:O10). Una tabla de datos bidireccional puede tener solamente una celda de salida y la fórmula para la salida debe ser colocada en la esquina superior izquierda del rango de la tabla. Por lo tanto en la celda H10 se escribe la fórmula para el cálculo del Beneficio.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 77 de 131

Centro de Informática y Sistemas

1. Seleccionar el Rango C10:F23 2. Hacer clic en la ficha Datos >> Grupo Herramientas de Datos >> Análisis Y si >> seleccionar Tabla de Datos... 3. Mostrar el cuadro de dialogo Tabla de Datos y luego seleccionar en el campo Celda entrada (fila) D3 (seleccionamos en fila porque los datos de los costó unitario se encuentran en fila) en el campo Celda de entrada (Columna) D1 (seleccionamos en columna porque los datos de los precios se encuentran en columna) y luego hacer clic en aceptar.

4. El resultado es:

Elaborado por: Ing. Johnny Pacheco Contreras

Página 78 de 131

Centro de Informática y Sistemas Ejercicio Don José Casas va a construir una nueva casa en Miraflores. La cantidad de dinero que necesita como préstamo con un periodo de pago de 15 años depende del precio en el que venda su casa actual (el precio está entre 300,000 y 650,000). Pero como es época de elecciones está un poco inseguro sobre la tasa de interés anual que recibirá cuando cierre (la tasa puede varias entre 5% y 8%). ¿Puede determinar cómo dependerán los pagos mensuales de la cantidad prestada y la tasa de interés anual?

Ejercicio Una gran compañía de internet está pensando comprar un minorista en línea. Los ingresos anuales del minorista con de 100 millones de euros, con gastos de 150 millones. Las proyecciones actuales indican que los ingresos del minorista están creciendo a un 25% por año y sus gastos están creciendo a 5% por año. Sabemos que las proyecciones deben tener un error, sin embargo, y nos gustaría saber, para una variedad de suposiciones acerca del ingreso anual y del crecimiento anual, el número de años antes de que el minorista muestre beneficio.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 79 de 131

Centro de Informática y Sistemas

6.1.2. Buscar Objetivo La característica Buscar Objetivo en Microsoft Office Excel le permite calcular un valor para una entrada en la hoja de cálculo que hace coincidir el valor de una formula dada al objetivo que especifica.

Si sabe qué resultado desea obtener de una fórmula, pero no está seguro de qué valor de entrada necesita la fórmula para obtener ese resultado, use la característica Buscar objetivo. Por ejemplo, imagine que debe pedir prestado dinero. Sabe cuánto dinero desea, cuánto tiempo va a tardar en saldar el préstamo y cuánto puede pagar cada mes. Puede usar Buscar objetivo para determinar qué tipo de interés deberá conseguir para cumplir con el objetivo del préstamo.

Para un precio dado, ¿Cuántos vasos de limonada necesita vender una tienda de limonada por año para cubrir los gastos?

Elaborado por: Ing. Johnny Pacheco Contreras

Página 80 de 131

Centro de Informática y Sistemas He asumido un costo anual de S/. 45,000 y un costo unitario variable de S/. 0.45. Asumimos el precio de S/. 3.00. Para comenzar a insertar cualquier número para la demanda en la celda D2. Hacer clic en la Ficha datos y en el grupo Herramientas de datos haga clic en Análisis Y Si y seleccionar Buscar Objetivo. Como se muestra en la siguiente figura.

El cuadro de dialogo indica que queremos cambatir la celda D2 hast que la celda D7 (benefios) alcence el valor de cero. despues hacer clic en Aceptar, conseguimos un resultado que es mostrado en la imagen (parte superior del parrafo). Si vendemos aproximadamente 17,647 vasos de limonada por año o 48 vasos al dia, lograremos el punto de equilibrio. Para encontrar el valor que estamos buscando, Excel varia la demanda en la celda D2 hasta encontrar un valor que haga el beneficio igual a cero. Si un problema tiene mas de una solucion, Buscar Objetivo mostrara una sola respuesta.

Ejercicio Johnny Pacheco quiere pagar su hipoteca en 15 años. La tasa de interés anual es de 6%. El banco nos dice que podemos afrontar pagos mensuales de 2000 ¿Cuánto nos puede prestar el banco?

Elaborado por: Ing. Johnny Pacheco Contreras

Página 81 de 131

Centro de Informática y Sistemas

Llenando el cuadro de dialogo buscar objetivo como se muestra en la figura se calcula la cantidad prestada que resulta de pagos mensuales iguales a 2000.

Con límite de 2000 para pagos mensuales, nos pueden prestar hasta 237,007.03

6.1.3. Administrador de Escenarios

Los escenarios son parte de una serie de comandos a veces denominados herramientas de análisis Y si. Un escenario es un conjunto de valores que Microsoft Excel guarda y puede sustituir automáticamente en la hoja de cálculo. Puede utilizar los escenarios para prever el resultado de un modelo de hoja de cálculo. Puede crear y guardar diferentes grupos de valores en una hoja de cálculo y, a continuación, pasar a cualquiera de estos nuevos escenarios para ver distintos resultados.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 82 de 131

Centro de Informática y Sistemas Ejemplo: Se prepara en una hoja de cálculo que contenga los cálculos del VNA de beneficios después de impuestos para un producto que espera estar disponible por el fabricante por cinco años (véase la figura líneas abajo) en el rango de C1:C8 se encuentran los parámetros de valores asumidos para el análisis.

En la celda B10 escribir: =C2 En la celda C10 escribir =B10*(1+$C$3), seleccionar la celda C10 modo copiar y arrastrar hasta la celda F10. En la celda B11 escribir: =C4 En la celda C11 escribir: =B11*(1+$C$8), seleccionar la celda C11 modo copiar y arrastrar hasta la celda F11. En la celda B12 escribir: =C5 En la celda C12 escribir: =B12*(1+$C$7), seleccionar la celda B12 modo copiar y arrastrar hasta la celda F12. En la celda B13 escribir: =B11*B10, seleccionar la celda B13 modo copiar y arrastrar hasta la celda F13. En la celda B14 escribir: =B10*B12, seleccionar la celda B14 modo copiar y arrastrar hasta la celda F14. En la celda B15 escribir: =B13-B14, seleccionar la celda B15 modo copiar y arrastrar hasta la celda F15. En la celda B16 escribir: =$C$1*B15, seleccionar la celda B16 modo copiar y arrastrar hasta la celda F16. En la celda B17 escribir: =B15-B16, seleccionar la celda B17 modo copiar y arrastrar hasta la celda F17.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 83 de 131

Centro de Informática y Sistemas

Asignar nombre a las siguientes celdas: C1

TasaImpuesto

C2

Ventas1Año

C3

CrecimientoVenta

C4

Precio1Año

C5

Costo1Año

C6

TasaInteres

C7

CrecimientoCosto

C8

CrecimientoPrecio

Se quiere crear tres escenarios relacionados al valor presente anual (VNA) de un auto.

Ventas Año 1

Crecimiento Anuales

Precio Venta año1

Mejor Caso

20.000

20%

10,00

Caso más Probable

10.000

10%

7,50

Peor Caso

5.000

2%

5,00

Pasos para definir el mejor escenario

Elaborado por: Ing. Johnny Pacheco Contreras

Página 84 de 131

Centro de Informática y Sistemas 1. Hacer clic en la ficha Datos, en el grupo Herramientas de datos hacer clic en Análisis Y si luego seleccionar Administrador de escenarios.

2. Excel muestra la ventana Agregar Escenario, hacer clic en Agregar.

3. Luego Ingresar nombre del escenario, las celdas cambiantes. Hacer clic en el botón aceptar. Nombre Escenario: Optimista Celdas Cambiantes: $C$2,$C$3,$C$4

Elaborado por: Ing. Johnny Pacheco Contreras

Página 85 de 131

Centro de Informática y Sistemas

4. Excel muestra la ventana Valores del escenario, ingresar los siguientes valores y hacer clic en el botón agregar:

5. Ingresar los siguientes datos y hacer clic en el botón aceptar.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 86 de 131

Centro de Informática y Sistemas

6. Excel muestra la ventana Valores del escenario, ingresar los siguientes valores y hacer clic en el botón agregar:

7. Ingresar los siguientes datos y hacer clic en el botón aceptar.

8. Excel muestra la ventana Valores del escenario, ingresar los siguientes valores y hacer clic en el botón aceptar:

Elaborado por: Ing. Johnny Pacheco Contreras

Página 87 de 131

Centro de Informática y Sistemas

9. Excel muestra el cuadro de dialogo Administrador de escenarios y hacer clic en el botón resumen.

10. Excel muestra el cuadro de dialogo Resumen Escenario y seleccionar las celdas $B$17:$F$17,$B$19. Luego hacer clic en el botona aceptar.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 88 de 131

Centro de Informática y Sistemas

11. Excel muestra el resultado en una nueva hoja

12. Comentarios: 

La opción d informe de tabla dinámica de escenario en el cuadro de dialogo resumen del escenario presenta el resultado del escenario en formato de tabla dinámica.



Suponga que seleccionamos un escenario en el cuadro de diálogo Administrador de escenarios y luego hacemos clic en el botón Mostrar. Los valores de las celdas de entradas para el escenario seleccionado aparecen en la hoja de cálculo, y Excel recalcula las fórmulas.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 89 de 131

Centro de Informática y Sistemas 

Es duro crear muchos escenarios con el Administrador de escenarios debido a que necesita introducir cada valor individual para los escenarios. La simulación de Monte Carlo, hace esto fácil para crear muchos escenarios. Puede encontrar información tal como la probabilidad de que el VNA de un flujo de caja de un proyecto sea no negativo, una importante medida debido a que esto es la probabilidad de que el proyecto agregue valor a la compañía.



Haciendo clic en el signo menos (-) en la fila 5 del resumen de escenario el informe ocultará las celdas de suposición y sólo mostrará resultados. Haciendo clic en el símbolo de más (+) se restaurará el informe.



Suponga que envía un archivo a muchas personas y cada uno agrega su propio escenario. Después cada persona devuelve el archivo conteniendo el escenario creado, puede combinar todos los escenarios dentro de una hoja de cálculo abriendo la versión de cada persona del libro de trabajo. Luego se hace clic en el botón Combinar en el cuadro de diálogo Administrador de escenarios en el libro de trabajo original y seleccionar todos los libros de trabajo conteniendo los escenarios que quiere combinar. Excel combinará todos los escenarios seleccionados en el libro de trabajo original.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 90 de 131

Centro de Informática y Sistemas 6.2.

Análisis de Proyectos de Inversión

CASO PEPITO S.A.C La Administración de la Escuela Militar sacó a licitación pública la concesión del rancho diario del personal a su cargo, el que fluctúa entre 600 y 800 personas diarias, y que consta de raciones de alimentos distribuidas en dos turnos: a la 1:00 p.m. para el caso del almuerzo y las 7:00 p.m. para la cena, durante los 22 días laborables por mes. La empresa PEPITO S.A.C. muestra mucho interés en participar; por lo que Humberto Coveñas –su Gerente General-, mandó identificar los costos que representarán la preparación de los alimentos mencionados en las bases de la licitación. Para tal fin, Don Humberto tomó como premisa, que el número de personas que debía atender diariamente sería de 800 personas, distribuidas en dos turnos de Lunes a Viernes (lo que equivale a 33,600 raciones mensuales); sin embargo, el prospecto informativo de la licitación indicaba que en casos excepcionales, el número de raciones podría ser menor, pero que cuando ello ocurra, le sería comunicado por escrito con 48 horas de anticipación, pero que en ningún caso el número de personas a atender por día sería menor a 600 personas. En este punto del proyecto, Don Humberto contrató a Johnny Pacheco Contreras –especialista en Industrias Alimentarias- con la finalidad de que le estructure la información necesaria y poder postular. Luego de una semana de recolectar la información necesaria, Johnny le entrega a Don Humberto la información solicitada y le explica las etapas del procedimiento que se sigue en estos casos y que se detalla a continuación:

 Recolección de información: Recolectar de un especialista, todos los componentes que participen directa o indirectamente en la fabricación (o comercialización) del producto.  Cotización de ítems: Pedir cotizaciones de empresas proveedoras de todos los productos requeridos y optar por la más conveniente.  Clasificar los ítems: Para cada uno de los componentes, indicar si es un Costo fijo o Costo variable, así como subclasificarlo, es decir, verificar si será un Costo de Administración, Costo de Ventas, Costo Financiero, Materia Prima, Mano de Obra o Gasto de Fabricación.  Cuantificar inversión por ítem: Calcular el monto en dinero requerido por cada ítem de acuerdo al número de unidades presupuestadas.  Resumir costos: Resumir en un cuadro la suma de montos por cada ítem.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 91 de 131

Centro de Informática y Sistemas  Proyectar EE.GG.PP.: Proyectar el Estado de Ganancias y Pérdidas, en el supuesto de que se venderá todas las unidades producidas. A continuación se muestra información presentada por Johnny Pacheco Contreras a Don Humberto:

Presentación Comercial del Producto Nº

Detalle

Tipo de Empaque

Unidades

Nº Unidades x

Unidades en

Costo por

requeridas

Empaque S/.

presupuestadas

Empaque

Empaque

1 Agua oficinas

Mes

1

Mes

150.00

1

2 Agua planta

Mes

1

Mes

1,100.00

1

3 Alquiler Oficinas

Mes

1

Mes

350.00

1

4 Alquiler Planta

Mes

1

Mes

1,050.00

5 Armado de accesorios al destajo

Unidad

1

Unidad

6 Armado de raciones al destajo

Unidad

1

Caja

10

8 Azúcar

Paquete

5

Kilogramo

9 Bolsas

Millar

1,000

Unidad

7 Atun

1

0.05

33,600

Unidad

0.08

33,600

Unidad

35.00 9.00 80.60

8,400 368 33,600

10 Café

Caja

12

Unidad

50.00

1,512

11 Cajas

Caja

100

Unidad

100.00

5,040

12 Caramelo

Paquete

5

Kilogramo

17.50

13 Carne

Paquete

10

Kilogramo

100.00

14 Chocolate

Paquete

5

Kilogramo

20.00

15 Cocineros al destajo

Unidad

1

Unidad

16 Contrato de Mant. Oficinas

Mes

1

Mes

250.00

17 Contrato de Mant. Planta

Mes

1

Mes

1,250.00

18 Cubiertos descartables

Paquete

100

Unidad

19 Depreciación de Planta

Mes

1

Mes

5,000.00

1

20 Energía Eléctrica Oficinas

Mes

1

Mes

280.00

1

21 Energía Eléctrica planta

Mes

1

Mes

1,300.00

Millar

1,000

Unidad

20.00

22 Etiquetado al destajo

0.15

2.00

389 3,360 1,176 33,600 1 1 33,600

1 33,600

23 Fletes para entregas

Unidad

1

Unidad

8.00

80

24 Galletas

Paquete

6

Kilogramo

1.90

16,800

25 Kerosene industrial

Galón

1

Galón

26 Latas

Caja

100

Unidad

57.40

67,200

92.00

16,800

27 Leche

4.50

1,838

Caja

48

Unidad

Paquete

5

Kilogramo

29 Leyes y Beneficios Sociales

Mes

1

Mes

7,747.00

30 Licencia de funcionamiento

Año

12

Mes

500.00

31 Mangas para raciones

Millar

1,000

Unidad

30.00

32 Mobilidad

Unidad

1

Unidad

3.00

84

33 Operarios al destajo

Unidad

1

Unidad

0.12

33,600

34 Otros alimentos

28 Lentejas

7.50

7,613 1 1 33,600

Unidad

1

Unidad

6,720.00

35 Piña

Caja

20

Unidad

15.00

16,800

36 Pollo

Kilogramo

1

Kilogramo

4.80

5,954

Paquete

5

Kilogramo

5.00

67

38 Seguridad de Planta

Mes

1

Mes

950.00

2

39 Seguridad Oficinas

Mes

1

Mes

950.00

2

40 Sueldo Conserje

Mes

1

Mes

700.00

1

41 Sueldo de Almacenero

Mes

1

Mes

1,500.00

1

42 Sueldo de Jefe de Planta

Mes

1

Mes

4,500.00

1

43 Sueldo Gerente General

Mes

1

Mes

8,500.00

1

44 Sueldo Secretaria

Mes

1

Mes

1,500.00

1

45 Teléfono

Mes

1

Mes

240.00

1

46 Utiles de escritorio

Mes

1

Mes

180.00

1

37 Sal

Elaborado por: Ing. Johnny Pacheco Contreras

1

Página 92 de 131

Centro de Informática y Sistemas Johnny Pacheco sabe que Don Humberto necesita invertir S/. 300,000.00 en activos (depreciables) –el cual se depreciarán en 5 años-, por lo que lo involucró en la elaboración de la estructura de costos para PEPITO S.A.C. y el cálculo del punto de equilibrio y punto de cierre en unidades y en Nuevos Soles, así como para la Proyección del Estado de Resultados, siguiendo los siguientes pasos:

Parte 1. 1.

Grabe la hoja de trabajo mostrada, en su USB de trabajo con el nombre de Costos.xls

2. Defina nombre a los siguientes rangos y celdas de datos: 

Asignar al rango A2:A3 de la hoja clasificación, el Nombre de Rango: Tipo



Asignar al rango C2:C7 de la hoja clasificación, el Nombre de Rango: SubTipo



Asignar al rango E2:E16 de la hoja clasificación, el Nombre de Rango: Empaque



Asignar al rango G2:G10 de la hoja clasificación, el Nombre de Rango: Unidad



Asignar a la celda C3 de la hoja Costos, el Nombre de Rango: Presupuestada



Asignar a la celda C4 de la hoja Costos, el Nombre de Rango: Ejecutada



Asignar a la celda C5 de la hoja Costos, el Nombre de Rango: Factor



Asignar a la celda C6 de la hoja Costos, el Nombre de Rango: ValorVenta



Asignar al rango I11:I56 de la hoja Costos, el Nombre de Rango: SubTipos



Asignar al rango J11:J56 de la hoja Costos, el Nombre de Rango: Fijos



Asignar al rango L11:L56 de la hoja Costos, el Nombre de Rango: Variables



Asignar al rango M11:M56 de la hoja Costos, el Nombre de Rango: Desembolsables



Asignar a la celda C11 de la hoja Resumen, el Nombre de Rango: CFT



Asignar a la celda D13 de la hoja Resumen, el Nombre de Rango: CUVT



Asignar a la celda C14 de la hoja Resumen, el Nombre de Rango: CFD



Asignar a la celda C17 de la hoja Resumen, el Nombre de Rango: PE



Asignar a la celda C18 de la hoja Resumen, el Nombre de Rango: PC



Asignar a la celda B4 de la hoja EERR, el Nombre de Rango: Utilidad



Asignar a la celda B5 de la hoja EERR, el Nombre de Rango: Renta

Elaborado por: Ing. Johnny Pacheco Contreras

Página 93 de 131

Centro de Informática y Sistemas 3. Utilice la herramienta: Datos, Validación, para ingresar el Empaque en el rango C11:C56 de la hoja Costos. Tome en cuenta las siguientes características: 

Configuración, Permitir: Lista



Configuración, Origen: =Empaque



Mensaje entrante, Título: TIPO DE EMPAQUE



Mensaje entrante, Mensaje de entrada: Seleccione el tipo de empaque



Mensaje de Error, Estilo: Límite



Mensaje de Error, Título: ERROR



Mensaje de Error, Mensaje de error: Empaque no válido

4. Utilice la herramienta: Datos, Validación, para ingresar la Unidad Métrica en el rango E11:E56 de la hoja Costos. Tome en cuenta las siguientes características: 

Configuración, Permitir: Lista



Configuración, Origen: =Unidad



Mensaje entrante, Título: TIPO DE UNIDAD MÉTRICA



Mensaje entrante, Mensaje de entrada: Seleccione el tipo de unidad



Mensaje de Error, Estilo: Límite



Mensaje de Error, Título: ERROR



Mensaje de Error, Mensaje de error: Unidad no válida

5. Utilice la herramienta: Datos, Validación, para ingresar el Tipo de Costo a las celdas del rango H11:H56 de la hoja Costos. Tome en cuenta las siguientes características: 

Configuración, Permitir: Lista



Configuración, Origen: =Tipo



Mensaje entrante, Título: TIPO DE COSTO



Mensaje entrante, Mensaje de entrada: Seleccione el tipo de costo a consignar



Mensaje de Error, Estilo: Límite



Mensaje de Error, Título: ERROR



Mensaje de Error, Mensaje de error: Tipo de costo no válido

Elaborado por: Ing. Johnny Pacheco Contreras

Página 94 de 131

Centro de Informática y Sistemas 6. Utilice la herramienta: Datos, Validación, para ingresar el Sub-Tipo de Costo a las celdas del rango I11:I56 de la hoja Costos. Tome en cuenta las siguientes características: 

Configuración, Permitir: Lista



Configuración, Origen: =SubTipo



Mensaje entrante, Título: SUB-TIPO DE COSTO



Mensaje entrante, Mensaje de entrada: Seleccione el sub-tipo de costo a consignar



Mensaje de Error, Estilo: Límite



Mensaje de Error, Título: ERROR



Mensaje de Error, Mensaje de error: Sub-tipo de costo no válido

7. Utilice la herramienta: Datos, Validación, para ingresar el Desembolsable a las celdas del rango M11:M56 de la hoja Costos. Tome en cuenta las siguientes características: 

Configuración, Permitir: Lista



Configuración, Origen: D,ND



Mensaje entrante, Título: ¿DESEMBOLSABLE?



Mensaje entrante, Mensaje de entrada: Seleccione D si es un costo desembolsable, de lo contrario ND.



Mensaje de Error, Estilo: Límite



Mensaje de Error, Título: ERROR



Mensaje de Error, Mensaje de error: Dato no válido

8. De acuerdo a la información proporcionada por Johnny, ingrese los valores la producción a presupuestar, así como su respectivo Valor de Venta planeado: C3

33600

C4

33600

C5

=Ejecutada/Presupuestada

C6

9.40

Elaborado por: Ing. Johnny Pacheco Contreras

Página 95 de 131

Centro de Informática y Sistemas 9. De acuerdo a la información mostrada, defina el tipo de empaque, número de unidades por empaque, unidades en empaque, costo por empaque (columnas C, D, E y F) de cada componente, así como el número de unidades (columna G) requeridas para el nivel de producción presupuestada (33,600 unidades).

10. Utilizando su mejor criterio, defina cada uno de los ítems de la estructura de costos como Costo Fijo o Costo Variable (columna H) y si además estos son: Materia Prima, Mano de obra, Gastos de Fabricación, Gastos de Administración, Ventas o Financieros (Columna I). Si en algún caso considera que debe de dar comportamiento mixto a un componente de la estructura de costos, deberá ingresar dos veces el ítem y prorratear su monto en dos parte que sumen el total.

11. De acuerdo a su mejor criterio, identifique en la columna M con una “D” si el costo correspondiente a cada ítem es Desembolsable y “ND” si no lo es.

12. Ingrese la fórmula que permite calcular el Costo Fijo por ítem (Columna J): J11 =SI($H11=J$9,F11/D11*G11,0) Marque la celda J11 y utilizando la opción del menú: Edición, Pegado especial..., Fórmulas, Aceptar, cópiela en el rango J12:J56.

13. Ingrese la fórmula que permite calcular el Costo Variable por ítem (columna K): K11 =SI($H11=K$9,F11/D11*G11,0) Marque la celda K11 y utilizando la opción del menú: Edición, Pegado especial..., Fórmulas, Aceptar, cópiela en el rango K12:K56.

14. Ingrese la fórmula que permite calcular el Costo Variable Ejecutado por ítem (columna L): L11 =K11*Factor Marque la celda L11 y utilizando la opción del menú: Edición, Pegado especial..., Fórmulas, Aceptar, cópiela en el rango L12:L56.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 96 de 131

Centro de Informática y Sistemas

15. Ahora definamos en la hoja Resumen los montos que por Costos Fijos o Variables, se tendrán por cada centro de costos: C4 =REDONDEAR(SUMAR.SI(SubTipos,$B4,Fijos),2) D4 =REDONDEAR(SUMAR.SI(SubTipos,$B4,Variables),2) E4 =C4+D4 Marque el rango de celdas C4:E4 y utilizando la opción del menú: Edición, Pegado especial..., Fórmulas, Aceptar, cópiela en los rangos C5:E6 y C8:E10.

16. Ingrese en la celda C7 el Costo de Producción como: C7 =SUMA(C4:C6) Marque la celda C7 y utilizando la opción del menú: Edición, Pegado especial..., Fórmulas, Aceptar, cópiela en el rango D7:E7.

17. Ingrese en la celda C11 el Costo de Total como: C11 =SUMA(C7:C10) Marque la celda C11 y utilizando la opción del menú: Edición, Pegado especial..., Fórmulas, Aceptar, cópiela en el rango D11:E11.

18. Ingrese en la celda C12 el Costo Unitario de Producción como: C12 =REDONDEAR(C7/Ejecutada,2) Marque la celda C12 y utilizando la opción del menú: Edición, Pegado especial..., Fórmulas, Aceptar, cópiela en el rango D12:E12.

19. Ingrese en la celda C13 el Costo Unitario Total como: C13 =REDONDEAR(C11/Ejecutada,2) Marque la celda C13 y utilizando la opción del menú: Edición, Pegado especial..., Fórmulas, Aceptar, cópiela en el rango D13:E13.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 97 de 131

Centro de Informática y Sistemas 20. Ingrese en la celda C14 el Costo Fijo desembolsable (todos aquellos gastos o costos que implican desembolso de dinero al final de cada período de análisis) como la suma de los costos desembolsables. Para nuestro caso asumir como no desembolsable a la Depreciación y la Licencia de Funcionamiento. C14 =REDONDEAR(SUMAR.SI(Desembolsables,"D",Fijos),2)

21. Defina las celdas C17 y D17 de los Punto de Equilibrio en unidades y en S/. como: C17 =REDONDEAR.MAS(CFT/(ValorVenta-CUVT),0) D17 =PE*ValorVenta

22. Defina las celdas C18 y D18 de los Punto de Cierre en unidades y en S/. como: C18 =REDONDEAR.MAS(CFD/(ValorVenta-CUVT),0) D18 =PC*ValorVenta

23. Ahora construiremos el Estado de Resultado utilizando el método de Costeo por Absorción. Para lo cual definimos las siguientes celdas:  % de Reparto de Utilidades: B4

10%

 % de Impuesto a la Renta:

B5

30%

 Ventas:

B9

=Ejecutada*ValorVenta

 Costo de Ventas:

B10 =-Resumen!E7

 Utilidad bruta:

B11 =B9+B10

 Gastos de Administración:

B12 =-Resumen!E8

 Gastos de Ventas:

B13 =-Resumen!E9

 Utilidad Operativa:

B14 =SUMA(B11:B13)

 Gastos financieros:

B15 =-Resumen!E10

 Utilidad antes de imp. y util.: B16 =B14+B15  Impuesto a la renta:

B17 =SI(B16>0,REDONDEAR(-B16*Renta,2),0)

 Utilidad antes de Util.:

B18 =B16+B17

 Reparto de Utilidades:

B19 =SI(B18>0,REDONDEAR(-B18*Utilidad,2),0)

Elaborado por: Ing. Johnny Pacheco Contreras

Página 98 de 131

Centro de Informática y Sistemas  Utilidad neta:

B20 =B18+B19

24. Complete la hoja Resumen, con las siguientes fórmulas: C21

=EEGGPP!B20

C22

=C21/Ejecutada

25. Finalmente, complete la hoja Sensibilidad, con lo siguiente:  Utilizando la opción del menú: Edición, Rellenar, Series..., complete las celdas A4:A33 con una serie por columna, lineal con incremento de 1000 en 1000.  Complete las siguientes fórmulas: B4

=Resumen!$C$11

C4

=Resumen!$C$14

D4

=A4*Resumen!$D$13

E4

=B4+D4

F4

=D4+C4

G4

=A4*ValorVenta

H4

=G4-E4

I4

=G4-F4

 Marque el rango de celdas B4:I4 y utilizando la opción del menú: Edición, Pegado especial..., Fórmulas, Aceptar, cópiela en el rango B5:I33.  Utilizando el rango B3:I33 construya un gráfico del tipo Dispersión con puntos de datos conectados por líneas sin marcadores de datos.

Análisis e Interpretación.

¿Es conveniente para los intereses de PEPITO S.A.C. participar con bajos niveles de riesgo en este nuevo negocio? ¿Por qué Sí/No?

Elaborado por: Ing. Johnny Pacheco Contreras

Página 99 de 131

Centro de Informática y Sistemas ¿Qué pasaría si por cuestiones de reducción de personal y permitiéndolo el contrato, la Escuela Militar reduce sus pedidos mensuales a 600 persona diarias o 25,200 mensuales, le seguiría conviniendo el negocios? ¿Por qué? ¿Si planea una utilidad de S/. 8,000, cual podría ser el valor de venta?, ¿Y Si en lugar de modificar el precio, disminuye el nivel de producción, cual sería este valor? ¿Qué pasa si incrementa su producción en un 50%? La utilidad sube de manera lineal o geométrica. Comente.

Parte 3 Planes de Pago. En el supuesto de que el 50% de los requerimientos en activos se financiaran con las alternativas mostradas a continuación, ¿Con cuál de ellas se quedaría?

Alternativa 1: Banco de los Emprendedores (Hoja Francés)  Método de pago

: Francés

 Periodicidad en el pago

: Mensual

 Forma de pago

: Cuotas vencidas

 Número de años a pagar

: 1 año

 Tasa efectiva anual

: 14%

 Comisiones

: S/. 5.00 por cuota

 Portes

: S/. 7.00 por cuota

 Gastos Administrativos

: S/. 24.00 por cuota

 Seguro

: Está obligado a tomar un seguro cuyo valor es del 0.3% anual del monto del bien, prorrateado de acuerdo al número de cuotas al año y pagadero conjuntamente con la cuota

Alternativa 2: Banco de los Emprendedores (Hoja Alemán)  Método de pago

: Alemán

 Demás condiciones iguales Alternativa 3: Banco de los Emprendedores (Hoja Americano)

Elaborado por: Ing. Johnny Pacheco Contreras

Página 100 de 131

Centro de Informática y Sistemas  Método de pago

: Americano

 Demás condiciones iguales

Proyecto Nº2 Universidad Autónoma, se encuentra planeando la conveniencia de la apertura de una carrera Técnica, la que se dictará en las mismas instalaciones de la Universidad, utilizando para ello, el horario de 6 p.m. a 10 p.m. en las aulas nuevas.

En este momento, su problema se centra en conocer cual deberá ser el punto de equilibrio, expresado tanto en número de participantes captados como en valor monetario en Nuevos Soles, que se generará como producto de la actividad, si se sabe que cuenta con la siguiente información:

1.

Proyecto: Carrera en Administración de Negocios y Marketing.

2.

Perfil Objetivo: Alumnos egresados del 5to de secundaria, pertenecientes al distrito de Villa el Salvador y San Juan de Miraflores.

3.

Descripción del proyecto: La nueva carrera técnica comprenderá la enseñanza de 20 cursos durante un año académico, el cual se dividirá en 4 bimestres, a razón de 9 semanas lectivas de clase y 1 de exámenes finales por bimestre, con descanso de 2 semanas entre bimestre y bimestre.

Cada bimestre comprenderá el dictado de 5 cursos, cada uno de los cuales tendrá cuatro horas de clase, de lunes a viernes de 6:00 a 10:00 p.m. durante 9 semanas, más 1 semana de exámenes.

Las aulas de las que se dispone tienen una capacidad para albergar a 50 alumnos como máximo.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 101 de 131

Centro de Informática y Sistemas Cada curso comprende el dictado de dos horas de teoría, dos horas de práctica a la semana, así como cuatro horas de seminario y dos horas de exámenes por bimestre, con las siguientes características: 

Las dos horas de clases teóricas estarán a cargo de un profesor, y comprenderá a las primeras 8 semanas.



Las dos horas de clases prácticas estarán a cargo de un profesor, el que contará con la ayuda de un asistente, y comprenderá a las primeras 8 semanas.



Las cuatro horas de seminario al bimestre, estarán dictadas por un profesional con experiencia del medio y se dará en forma continua en un solo día por vez en la semana 9.



Los exámenes finales se desarrollarán a lo largo de la semana 10 y comprenderá un examen teórico/práctico de 2 horas por curso y estará a cargo del asistente del profesor.



Para apoyar el dictado de los cursos, la promotora entregará una separata teórica/práctica de a lo más 50 hojas (impreso por la promotora) por curso y un CD con la información concerniente a los cursos que se dictarán por bimestre, los cuales se entregarán al delegado del curso para la entrega a todos los compañeros de clase.



Para cada examen se entregará cuadernillos de a lo más 2 hojas (4 caras impresas) por alumno.



Las clases comprenderán horas académicas de 50 minutos y comenzarán siempre a las 6:10 p.m. y 8:10 p.m.

Dentro del personal de apoyo, se requiere de: 

Un coordinador académico a tiempo parcial (Lunes a Viernes de 6 p.m. a 10 p.m.), cargo que podrá ser ejercido por un profesor del mismo colegio u otro profesional contratado para este fin, al cual se le pagará S/. 700.00 mensuales.



Una secretaria a tiempo parcial (Lunes a Viernes de 6 p.m. a 10 p.m.), encontrándose dentro de sus obligaciones las de coordinación administrativa de la carrera, digitación y reproducción de material, entre otras funciones, a la cual se le pagará S/. 300.00 mensuales.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 102 de 131

Centro de Informática y Sistemas 

Un personal de limpieza a tiempo parcial (Lunes a Viernes de 6 p.m. a 10 p.m.), entre otras funciones, al cual se le pagará S/. 275.00 mensuales.



Un profesor por cada curso dictado, con carga de 4 horas semanales por 8 semanas por bimestre, al cual se le pagará S/. 20.00 por hora.



Un asistente por cada curso dictado, con carga de 2 horas semanales por 8 semanas y 2 horas en la semana de exámenes, por bimestre, al cual se le pagará S/. 10.00 por hora.



Un profesional a cargo del seminario de 4 horas por curso por bimestre, al cual se le pagará S/. 45.00 por hora.



En todos los casos deberá tomarse en cuenta las leyes sociales u otras obligaciones exigidas por Ley para el personal participante.

Al finalizar el año de estudios se le entregará al participante un diploma que lo distingue como Técnico en Administración de Negocios y Marketing, en una pequeña ceremonia de graduación en las mismas instalaciones del colegio, la cual será organizada por la promotora, y requerirá lo siguiente:

4.



Contratación de un toldo y accesorios para la ceremonia.



Contratación de un presentador de la ceremonia.



Contratación de un equipo de sonido.



Contratación de 5 sillas por participante.



Buffet y brindis para los participantes y 4 invitados por participante.



Confección, reproducción y entrega de un diploma por participante.



Alquiler de toga y birrete por participante.

Precio por participante: Un análisis comparativo con el sector, nos ha permitido fijar el precio mensual de la pensión a cobrar en S/. 200.00 mensuales como máximo por alumno. Además, por ser una institución educativa sin fines de lucro, no se cobra el IGV.

El alumno que elija este estudio, deberá cancelar por derecho a matrícula el monto equivalente a un mes de pensión, así como también el monto correspondiente a la pensión durante los 10

Elaborado por: Ing. Johnny Pacheco Contreras

Página 103 de 131

Centro de Informática y Sistemas meses efectivos que dure el estudio. Al finalizar el proceso el alumno deberá abonar S/. 150.00 por derechos de emisión de Diploma.

5.

Publicidad y Marketing: En la parte pre-operativa se han incurrido en los siguientes gastos:



Publicidad: Se presupuestaron 2 avisos publicitarios en el diario El Comercio durante los dos domingo anteriores a la fecha de inicio, los cuales tienen un costo unitario de S/. 2,850.00



Brochures informativos: Se mandaron reproducir 4,000 brochures informativos, los que costaron S/. 175.00 el millar y se repartieron a los centros educativos cercanos, mediante el servicio de un courier, el cual cobro S/. 0.70 por cada uno de los 500 envíos que se realizaron (4 brochure por envío). Habrá que tomar en cuenta que se pagó a un Diseñador Gráfico la suma de S/. 150.00 por el diseño de dicha publicidad.



Afiches promocionales: Se mandaron reproducir 500 afiches a un costo de S/. 125.00, los que se remitieron a los colegios conjuntamente con los 4 brochures informativos.



Estudio de Mercado: Se contrató un pequeño estudio de mercado, el cual costo S/. 2,550.00

6.

Estudio de Mercado: El estudio de mercado hecho en la zona de influencia, nos ha permitido conocer que podríamos captar a no menos de 200 participantes.

7.

Gastos de Administración: Se deberá tomar en cuenta que el Área de Administración y Contabilidad del colegio requerirá de nuevos recursos para atender la nueva demanda creada por este fin, por lo que deberá incluirse nuevos costos por energía eléctrica, agua, material de oficina, uso de oficinas, mobiliario y equipos de cómputo, movilidad, accesorios para el salón

Elaborado por: Ing. Johnny Pacheco Contreras

Página 104 de 131

Centro de Informática y Sistemas de clase (mota, plumones, TV y DVD), entre otros, en este nuevo proyecto, los que deberá costear y tomar en cuenta para sus cálculos.

Preguntas: Cuáles serían las nuevas condiciones del proyecto, si se desea tener una utilidad deseada de S/. 25,000.00 Cuál sería el plan de pagos que se deberá elegir, si se considera que el 50% de los requerimientos iniciales serán financiados por un crédito en las siguientes condiciones:  Fuente de financiamiento : Banco de los Emprendedores  Periodicidad en el pago

: Mensual

 Forma de pago

: Cuotas vencidas

 Número de años a pagar

: 2 años

 Tasa efectiva anual

: 12% el primer año y 10% el último.

 Comisiones

: S/. 8.50 por cuota

 Portes

: S/. 7.00 por cuota

 Gastos Administrativos

: S/. 24.00 por cuota

 Seguro

: Debe tomar un seguro cuyo valor es del 0.3% anual del monto a financiar, prorrateado de acuerdo al número de cuotas al año y pagadero conjuntamente con la cuota

 Fecha de la adquisición

: 1º de agosto de 2010

 Métodos posibles de pago : francés, alemán y americano vencidos.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 105 de 131

Centro de Informática y Sistemas

Capítulo 7 Simulación de escenarios de un Proyecto 7.1.

Valoración de inversiones en situación de riesgo

En capítulos anteriores, hemos visto cómo valorar las inversiones y determinar, tanto su rentabilidad económica como su viabilidad financiera. Hemos supuesto de partida que los valores estimados para cada una de las variables definitorias de la inversión y su financiación eran perfectamente conocidas. La situación anterior se puede producir cuando el proyecto de inversión ya ha finalizado, y por tanto, se conocen los valores que han tenido todas las variables en el pasado.

Se estarían utilizando los modelos, por tanto, para valorar la rentabilidad que ha tenido un proyecto ya realizado. Sin embargo, resulta mucho más interesante evaluar la rentabilidad de un proyecto antes de realizarlo. En este caso, los valores que las variables van a tomar en el futuro (ventas, costes, duración del proyecto, etc.) no suelen ser conocidos, ya que estamos tratando con hechos que aún no han ocurrido. Así, el carácter futuro de estos valores hará que no puedan ser conocidos con seguridad en la mayoría de los casos.

En este nuevo contexto nos vamos a encontrar con los siguientes casos:

1. El conocimiento pleno de los valores futuros de las variables, al que llamaremos certidumbre. 2. El caso extremo opuesto al anterior, es decir, sería el desconocimiento total tanto de los valores que van a tomar las variables como de la probabilidad de ocurrencia de dichos valores. Esta sería una situación de plena incertidumbre. Al igual que la certidumbre completa, es prácticamente imposible que al evaluar un proyecto de inversión estemos en tal situación. 3. Lo más frecuente es un estado intermedio al que denominaremos riesgo.

En esta última situación, dispondremos de cierta información sobre el valor futuro de las variables:

Elaborado por: Ing. Johnny Pacheco Contreras

Página 106 de 131

Centro de Informática y Sistemas 1. Es posible que conozcamos el valor exacto de alguna variable en concreto (por ejemplo, podemos tener facturas pro-forma de las inversiones a realizar, precontratos para la financiación, podemos tener decidido el precio de venta al que vamos a lanzar nuestro producto...). 2. Otra información la conoceremos en términos de variables aleatorias (por ejemplo, la demanda de nuestra empresa se distribuirá de forma normal), o simplemente un intervalo de variación para las variables (por ejemplo, que nuestras ventas oscilarán entre 2.000 y 3.000 productos al año, que el tipo de interés se moverá entre el 3 y el 7%, etc.).

En cualquiera de los casos anteriores, las variables cuyos valores no son seguros para el futuro vendrán representadas por variables aleatorias a las que corresponderán sus funciones de densidad de probabilidad. Dado que la medida que empleemos de rentabilidad será una función de las variables anteriores, esta medida será también una variable aleatoria, y por tanto, ser tratada como tal. En este sentido, la mayor información que podremos obtener sobre la rentabilidad de un proyecto determinado en condiciones de riesgo será su función de densidad, si bien, como veremos a continuación, puede bastarnos con conocer algunos de sus momentos, como la esperanza o la varianza. Vamos a distinguir en primer lugar dos tipos de riesgo: por un lado, el riesgo proveniente de las inversiones que realiza la empresa, al que denominaremos riesgo económico. Este riesgo vendrá dado por las distintas variabilidades o riesgos asociados a las variables que definen la rentabilidad económica de un proyecto (es decir, aquellas que intervienen en el cálculo de la TIR). Así podemos citar los siguientes:

1. La duración del proyecto. 2. La variabilidad en las ventas. 3. La variabilidad en los costes. 4. La variabilidad en los valores residuales...

Elaborado por: Ing. Johnny Pacheco Contreras

Página 107 de 131

Centro de Informática y Sistemas Por otro lado, tenemos el riesgo que soporta el accionista como consecuencia, tanto del riesgo económico de su empresa, como de la forma de financiación utilizada, en lo que denominaremos riesgo financiero. De esta forma, al riesgo financiero le afectarán tanto las variabilidades vistas para el riesgo económico como, además, el riesgo adicional aportado como consecuencia del endeudamiento de la empresa. En segundo lugar, diferenciaremos los conceptos de riesgo específico y riesgo incremental. Así, el riesgo de una inversión puede medirse de forma específica, es decir, individualizada y sin tener en cuenta que dicha inversión se lleva a cabo por una empresa. Sin embargo, lo normal es que estas inversiones se realicen en el marco de una empresa, la cual dispondrá de otras inversiones en marcha. La empresa en conjunto, vista como un único proyecto de inversión que engloba a otros subproyectos, nos ofrecerá un riesgo determinado. Por tanto, resultaría más interesante calcular el riesgo incremental del nuevo proyecto, es decir, el riesgo que este proyecto aporta a la empresa.

7.2.

Simulación de Montecarlo

Para afrontar el problema anterior, vamos a introducir un método, basado en la técnica de simulación de Montecarlo, y que es conocido como el método de Hertz. Pretendemos, en consecuencia, determinar la función de densidad de la rentabilidad partiendo como datos de base de las diversas funciones de densidad de cada una de las variables determinantes de la evaluación. De esta forma, estudiamos la función de densidad de probabilidad en su totalidad, y la obtenemos de forma bastante exacta.

METODOLOGÍA DE LAS SIMULACIONES La metodología a seguir se va a basar en la simulación, es decir, en simular el proyecto de inversión un número suficientemente elevado de veces, para así tener una muestra de posibles valores de la rentabilidad de esa inversión. Cuando el número de observaciones así obtenidas sea lo suficientemente grande, podremos estudiar todas las características de la variable rentabilidad.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 108 de 131

Centro de Informática y Sistemas Para ello, iniciaremos el proceso viendo cómo se realizan las simulaciones. Este proceso comprende los siguientes pasos:

1. Identificar las variables que conforman la función de rentabilidad y señalar aquéllas que son variables aleatorias. Como ya hemos señalado, en este modelo puede ser variable aleatoria cualquier variable explicativa de la rentabilidad (desembolso inicial, flujos netos de caja de explotación, valor residual, cualquier componente de los anteriores, tiempo, coste de capital o sus componentes...). 2. Estimación de las funciones de densidad o de distribución de probabilidad de las variables aleatorias. A partir de las funciones de densidad se deben de construir las funciones de distribución o las de probabilidad acumulada para las variables discretas. 3. Una vez construidas las funciones de distribución o de probabilidad acumulada, se inicia el proceso de simulación. Este método simula una situación determinada (conjunto de valores para las variables integrantes de la fórmula de la rentabilidad) y, a través de su reiteración un número suficientemente grande de veces, permite determinar la función de densidad de la rentabilidad empleando las frecuencias de aparición de cada valor de la rentabilidad. Para realizar cada una de estas simulaciones, se genera un conjunto de números aleatorios (uno por cada variable a simular) y se calcula qué valor de la variable obtendría una probabilidad acumulada igual a dicho número aleatorio, tal y como se muestra en la siguiente figura. En esta figura hemos representado la función de distribución de una normal. Hemos generado un número aleatorio entre O y 1, que lo hemos hecho coincidir con un valor determinado de la función de distribución F(X). Seguidamente, hemos de hallar qué valor de X nos daría como probabilidad acumulada dicho valor F(X). Ese X es el valor que estamos buscando para la variable aleatoria. Estamos, por tanto, realizando el proceso inverso de la función de distribución, la cual nos da la probabilidad para un valor. Sin embargo, en las simulaciones nosotros damos la probabilidad (indicada por el número aleatorio) y debemos buscar qué valor de la variable tiene esa probabilidad acumulada. Esto se repetiría para todas y cada una de las variables aleatorias, obteniendo un conjunto de valores que, introducidos en la fórmula de la rentabilidad, nos darían un valor de rentabilidad determinado. Con esto obtendríamos un valor simulado de la rentabilidad.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 109 de 131

Centro de Informática y Sistemas

Proceso de Simulación

4. Repetiríamos el proceso de simulación un número suficientemente elevado de veces, con lo que tendríamos un número elevado de simulaciones de la rentabilidad. 5. Si queremos estimar la esperanza de la rentabilidad, lo podemos hacer simplemente realizando la media aritmética de todos los valores obtenidos en la simulación. 6. Si lo que queremos estimar es la desviación típica de la rentabilidad, lo podemos hacer simplemente obteniendo la desviación típica muestral (o más correctamente, cuasi-desviación típica muestral) de los valores obtenidos en la simulación. 7. Si lo que queremos es estimar alguna probabilidad, lo podremos hacer calculando la razón casos favorables/casos posibles sobre los valores obtenidos en las simulaciones. 8. Si, finalmente, deseamos conocer la función de densidad de la rentabilidad, podremos hacernos una idea aproximada estudiando el histograma de los valores obtenidos en las simulaciones. 9. Para un conocimiento más exacto, se podrán aplicar los tests estadísticos correspondientes.

Observaciones al Modelo de Hertz Dependencia de variables Hemos diseñado el modelo afirmando que, para cada variable aleatoria, se debe generar un número aleatorio que nos conduzca hacia un valor de dicha variable. Sin embargo, no hemos comentado en ningún momento un tema tan importante como sería la dependencia o independencia de las variables aleatorias del sistema. El modelo anterior, supone implícitamente la

Elaborado por: Ing. Johnny Pacheco Contreras

Página 110 de 131

Centro de Informática y Sistemas independencia de todas las variables aleatorias, ya que generamos los valores de las mismas sin tener en cuenta en ningún momento los valores generados para el resto. Sin embargo, esta situación puede ser poco real, porque, por ejemplo, precio de venta y cantidad vendida puede esperarse que estén inversamente relacionados. Si introducimos la dependencia/independencia de variables aleatorias, estamos complicando más el proceso, pero se ajustaría mejor a la realidad. Veamos cómo podemos hacerlo. Supondremos tres casos diferenciados: dependencia positiva, independencia y dependencia negativa. Para el primer caso, que lo consideraremos cuando el coeficiente de correlación sea notablemente grande y positivo, lo que haremos será utilizar el mismo número aleatorio por simulación para todas y cada una de las variables correlacionadas. De esta forma, aseguramos que los valores de las variables covaríen en el mismo sentido a través de las distintas simulaciones. Para el segundo caso, es decir, cuando son independientes, simplemente generaremos un número aleatorio diferente para cada variable aleatoria. Para el último caso, cuando son dependientes de forma negativa, utilizaremos un número aleatorio para una variable y, para la otra, el complemento a uno del número aleatorio (es decir 1 – n aleatorio) aseguramos que covaríen en sentido inverso.

Número de simulaciones Para la obtención de la función Para la obtención de la función de densidad del VAN necesitaríamos realizar infinitas simulaciones. No obstante, es imposible conseguir tal número de simulaciones. Por tanto, debemos acudir a las herramientas estadísticas para calcular el número adecuado de simulaciones a realizar. En cualquier caso, para obtener datos fiables, este número resulta siempre elevado. No obstante, podemos hacer uso de determinadas técnicas estadísticas para calcular el tamaño óptimo de una muestra para calcular determinados parámetros. Así, vamos a utilizar el tamaño óptimo de la muestra para estimar proporciones poblacionales (es decir, probabilidades) para calcular el número de simulaciones necesarias. Veámoslo en un ejemplo en el que vamos a calcular la probabilidad de que un proyecto sea rentable.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 111 de 131

Centro de Informática y Sistemas Sea la variable X la binomial que nos indica qué proporción de simulaciones son rentables. Llamaremos P a la proporción poblacional de rentables, y p a la proporción muestral, como vemos en la siguiente Figura.

Proporción poblacional y proporción muestral.

La proporción muestral es un buen estimador de la proporción poblacional, siendo insesgado, consistente, eficiente y suficiente. Su esperanza es P y su varianza P(l - P)/n. En virtud del Teorema Central del Límite, este estadístico p se distribuye según una normal de media P y varianza P(l - P)/n. Veamos cuánto debe valer n para que la estimación del parámetro P no difiera de p en más de un error determinado á con una probabilidad k. Tipificando, como se muestra en la siguiente Figura (Tipificación de la probabilidad) Supongamos que el valor de probabilidad deseado k sea del 99,7%. Buscando en la tabla de la normal tipificada, el valor del valor absoluto de la expresión que aparece en la siguiente Figura.

Tipificación de la probabilidad

Despejando n, obtenemos que vale n = 9p(l-p)/e2. Ahora bien, parece que nos encontramos en un círculo vicioso, pues el tamaño óptimo de la muestra n depende del propio parámetro P. Podemos salvar esta contradicción sustituyendo el valor p(l-p) por su máximo valor: Elaborado por: Ing. Johnny Pacheco Contreras

Página 112 de 131

Centro de Informática y Sistemas Max p(l-p)= Max (p-p2)=l/4 (máximo alcanzado cuando p = 0,5) Sustituyendo, obtenemos una cota superior para el número óptimo de simulaciones a realizar: n ? 9/4á2. Así, por ejemplo, para un error máximo deseado del 1%, obtendríamos un tamaño muestral de n=22.500 simulaciones (con una probabilidad del 99,7%). O, por ejemplo, si hemos realizado 10.000 simulaciones, el error máximo que podemos esperar (con una probabilidad del 99,7%) sería de á = 1,5%. La siguiente tabla da ejemplos de número de simulaciones y los errores máximos que se pueden esperar con la probabilidad del 99,7%:

N 1 10 100 1000 10000 100000

E 150.00% 47.43% 15.00% 4.74% 1.50% 0.47%

Así, por ejemplo, supongamos que hemos hecho 10 simulaciones y hemos obtenido que la probabilidad de que el proyecto sea rentable, sea del 50%. Dado que con 10 simulaciones podemos tener un error del 47,43%, con un 99,7% de probabilidad, la verdadera probabilidad de que sea rentable podría estar entre un 2,17% (50%-47,43%) y un 97,93% (50%+47,43%). Sin embargo, si hemos obtenido ese valor de 50% con 100.000 simulaciones, sabremos que, con un 99,7% de probabilidad, la verdadera probabilidad de que el proyecto sea rentable estará entre el 49,53% y el 50,47%. Por tanto, para poder disponer de unas buenas estimaciones de la probabilidad, se necesita realizar un número elevado de simulaciones, lo cual requiere a su vez el uso de computadoras.

7.3.

Configuración de Excel 2010 para la realización de simulaciones

La utilidad de Excel que nos permite realizar simulaciones, requiere que, previamente, la configuremos adecuadamente, realizando lo siguiente: Ficha de Archivo > Opciones de Excel >

Elaborado por: Ing. Johnny Pacheco Contreras

Página 113 de 131

Centro de Informática y Sistemas Complementos > Administrar complementos de Excel > lr...> Herramientas de análisis>Aceptar, tal como se puede observar en la siguiente secuencia de figuras:

Opciones de Excel.

Habilitar y deshabilitar complementos. Elaborado por: Ing. Johnny Pacheco Contreras

Página 114 de 131

Centro de Informática y Sistemas Uso de la utilidad Generación de números aleatorios Una vez configurado Excel para habilitar las opciones de Herramientas de análisis, ya podemos comprobar cómo, en la pestaña de Datos, en las opciones de Análisis, aparecerá la opción: Análisis de datos que, al ser elegida, nos ofrecerá toda una serie de opciones, de entre la que elegiremos Generación de números aleatorios, tal como lo podemos observar en la Figura.

Opciones de Análisis de datos.

Una vez que hayamos elegido generar números aleatorios mediante la siguiente secuencia de la ficha: Datos > Grupo Análisis>Análisis de datos> Generación de números aleatorios> Aceptar, nos aparecerá la ventana del asistente que se muestra en la siguiente Figura.

Asistente para la generación de números aleatorios.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 115 de 131

Centro de Informática y Sistemas El cuadro de diálogo Generación de números aleatorios, consta de los siguientes datos que tendremos que cumplimentar:

1. Número de variables: Aquí se nos pide el número de columnas de valores que deseamos incluir en la tabla de resultados. Si no especifica ningún número, Microsoft Office Excel rellenará todas las columnas del rango de salida que se haya especificado. 2. Cantidad de números aleatorios: Especificaremos el número de puntos de datos que deseamos ver. Cada punto de datos aparecerá en una fila de la tabla de resultados. Si no escribe ningún número, Excel rellenará todas las columnas del rango de salida que se haya especificado. 3. Distribución: Se nos ofrece una serie de distribuciones, que deberemos elegir para crear los valores aleatorios: a) Uniforme: Se caracteriza por los límites inferior y superior. Se extraen las variables con probabilidades iguales de todos los valores del rango. Una aplicación normal utilizará una distribución uniforme entre O y 1. b) Normal: Se caracteriza por una media y una desviación estándar. Una aplicación normal utilizará una media de O y una desviación estándar de 1 para la distribución estándar normal. c) Bernoulli: Se caracteriza por la probabilidad de éxito (valor p) en un ensayo dado. Las variables aleatorias de Bernoulli tienen el valor O ó 1. Por ejemplo, puede extraerse una variable aleatoria uniforme entre O y 1. Si la variable es menor o igual a la probabilidad de éxito, se asignará el valor 1 a la variable aleatoria de Bernoulli; en caso contrario, se le asignará el valor 0. d) Binomial: Se caracteriza por una probabilidad de éxito (valor p) durante un número de pruebas. Por ejemplo, se pueden generar variables aleatorias Bernoulli de número de pruebas, cuya suma será una variable aleatoria binomial. e) Poisson: Se caracteriza por un valor lambda, igual a 1/media. La distribución de Poisson se usa con frecuencia para caracterizar el número de eventos por unidad de tiempo, por ejemplo, el ritmo promedio al que llegan los vehículos a una zona de peaje. f) Frecuencia relativa: Se caracteriza por un límite inferior y un límite superior, un incremento, un porcentaje de repetición para valores y un ritmo de repetición de la secuencia.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 116 de 131

Centro de Informática y Sistemas g) Discreta: Se caracteriza por un valor y el rango de probabilidades asociado. El rango debe contener dos columnas: la columna izquierda contendrá valores y la derecha contendrá probabilidades asociadas al valor de esa fila. La suma de las probabilidades debe ser 1.

4. Parámetros: Escribiremos valores para caracterizar la distribución seleccionada. 5. Iniciar con: Escribiremos un valor opcional a partir del cual se generarán números aleatorios. Podremos volver a utilizar este valor más adelante para generar los mismos números aleatorios. 6. Rango de salida: Especificaremos la referencia correspondiente a la celda superior izquierda de la tabla de resultados. Excel determinará el tamaño del área de resultados y mostrará un mensaje si la tabla de resultados reemplaza los datos existentes. 7. En una hoja nueva: Haremos clic en esta opción para insertar una hoja nueva en el libro actual y pegar los resultados comenzando por la celda Al de la nueva hoja de cálculo. Para darle un nombre a la nueva hoja de cálculo, escribiremos su nombre en el cuadro. 8. En un libro nuevo: Haremos clic en esta opción para crear un libro nuevo en el que los resultados se agregarán a una hoja nueva.

Para ilustrar un breve ejemplo de cómo sería el procedimiento de simulación de una variable cualquiera (Unidades físicas de venta, por ejemplo) que respondiera al comportamiento de una Normal, de media 25.000 y desviación típica, 13.600. Empezaremos por escribir estos valores en una hoja de cálculo, tal y como aparece en la Figura.

Preparación de la hoja para realizar la simulación de las ventas.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 117 de 131

Centro de Informática y Sistemas Una vez preparada la hoja, procederemos como se indicó anteriormente, Datos > Grupo Análisis > Análisis de datos > Generación de números aleatorios > Aceptar, apareciendo el asistente que nos solicitará los datos necesarios:

1. Número de variables: Aquí indicaremos un 1, ya que solo queremos generar una lista de valores posibles de las ventas. 2. Cantidad de números aleatorios; Se nos solicita que introduzcamos cuántos valores simulados queremos generar. Nosotros pondremos en este campo, el número 1000. 3. Distribución: En este desplegable elegimos la distribución Normal, de forma que el asistente solicitará nuevos datos específicos de la función elegida, en nuestro caso la media y la desviación típica. 4. Parámetros: Se corresponde con los parámetros específicos de la función de distribución elegida en el campo anterior: a) Media: Escribiremos el valor que hemos propuesto anteriormente, 25.000 unidades físicas de venta. b) Desviación típica: Escribiremos el valor que hemos propuesto anteriormente, 13.600 unidades físicas de venta. 5. Iniciar con: Este campo es opcional, por lo que no escribiremos nada. 6. Opciones de salida: Aquí se nos ofrecen tres opciones, de las que sólo podemos optar por una de ellas. Elegiremos la celda C5, para que empiece a generar valores de venta a partir de esta posición y, hacia abajo.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 118 de 131

Centro de Informática y Sistemas

Asistente de la simulación de las ventas.

Vemos cómo los valores simulados de las ventas no son números enteros (Figura Valores simulados de la venta), luego estos valores no serían útiles para realizar cálculos como las ventas en unidades monetarias, por lo que necesitaríamos pasarlos a enteros. Para pasar a valores enteros las ventas simuladas bastará con utilizar la función de Excel (ENTERO). Para ello, nos posicionaremos a la derecha del primer valor simulado de las ventas, la celda D5 y escribiremos, =ENTERO(C5) Tal y como se muestra en la Figura (Aplicación de la función ENTERO).

Valores simulados de las ventas.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 119 de 131

Centro de Informática y Sistemas

Ventas en unidades físicas, simuladas.

Debemos ser conscientes de que los valores simulados, al ser solo 1.000, no reproducen fielmente la función de distribución normal, origen de nuestros cálculos. Basta para ello que realicemos el cálculo de la media y la desviación típica de las ventas, simuladas, para comprobarlo. En la Figura vemos cómo realizar el cálculo de la media de las ventas simuladas con =PROMEDIO(D5:D1004).

Aplicación de la función PROMEDIO, para el cálculo de la media de las ventas.

El valor que obtenemos como resultado es 24,969.01 que ni es un valor entero, ni coincide con al valor dé la media de las ventas total. El motivo es que nosotros hemos calculado la media sobre

Elaborado por: Ing. Johnny Pacheco Contreras

Página 120 de 131

Centro de Informática y Sistemas una muestra. Para calcular la desviación típica de las ventas, utilizaremos la función estadística de Excel, =DESVESTP(D5:D1004), como se ve en la siguiente Figura. Vemos, al igual que al calcular la media, que el resultado obtenido es diferente de la desviación típica de la función de distribución de origen. El motivo es el comentado anteriormente, la diferencia que existe entre la muestra generada y la población total que es la que proporcionaría una desviación típica de 13.600 unidades físicas de venta.

Aplicación de la función DESVESTP, para el cálculo de la desviación típica de las ventas.

7.4.

Aplicación del método de Montecarlo a la valoración de una inversión

Una vez que ya hemos visto cuál es el proceso a seguir en la simulación de variables, estamos en disposición de aplicar el método de Montecarlo a valoración.

Mediante el modelo de Hertz o Simulación de Montecarlo, trataremos de calcular: 1. Probabilidades de distintos valores de la rentabilidad. 2. La esperanza de rentabilidad. 3. El riesgo de un proyecto.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 121 de 131

Centro de Informática y Sistemas Para ello, si R es la variable aleatoria que muestra la rentabilidad del proyecto (el VAN, el TIR...), lo que haremos será obtener un número suficientemente elevado de casos de la variable R para poder conocer su distribución de probabilidad. Para realizarlo, es necesario conocer primero cómo está formada la variable aleatoria R, es decir, debemos descomponerla en las distintas variables aleatorias que la componen (por ejemplo, desembolso inicial, tasa de actualización, flujos de caja, precio de venta, unidades vendidas, costes fijos, costes variables, tipo impositivo, cuota de amortización, valor residual, duración del proyecto...). En el método de Hertz puede ser variable aleatoria cualquier componente de la fórmula de la rentabilidad (del VAN, del TIR...), pero con la exigencia de que dicha variable aleatoria debe ser totalmente conocida, es decir, no basta con saber que el número de unidades de producto vendidas para un año es normal, sino que hay que saber que es normal, con una esperanza de X unidades y una desviación típica de Y. Una vez que hayamos terminado de definir todas las variables aleatorias cuya función de densidad conocemos, y que hayamos establecido la relación funcional entre ellas que nos da como resultado la variable de rentabilidad, iremos obteniendo valores posibles de todas y cada una de las variables aleatorias. Al sustituir los citados valores en la función de rentabilidad, obtendremos un valor de la rentabilidad del proyecto. No obstante, es necesario señalar que el valor obtenido es nada más que un posible valor. No sabemos si es un valor muy representativo (como podría ser la esperanza o la moda) o si es un valor extremo, muy remoto de obtener. Un solo valor no nos da información alguna. Veamos ahora cómo podemos realizar una simulación con la hoja de cálculo Microsoft Excel de la rentabilidad de un proyecto de inversión. Para ello, supongamos un proyecto simple con los siguientes datos:

1. Desembolso inicial: se distribuye normal con esperanza de 1.000 euros y desviación de 200. 2. Flujos de caja: los flujos de caja anuales se calculan como el número de productos vendidos por el margen obtenido por cada producto. a) Las ventas de cada año se distribuyen uniformemente entre 500 y 800 productos.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 122 de 131

Centro de Informática y Sistemas b) El margen se distribuye normalmente, con media de 5 euros y desviación típica de 1 euro. 3. Duración: la duración es aleatoria según la siguiente tabla:

Duración de Años 2 3 4

Probabilidad 20% 50% 30%

Para poder aplicar la utilidad Generación de números aleatorios, de Excel, las variables anteriormente definidas deben ser todas ellas independientes.

Se pretende calcular la esperanza y la desviación típica del TIR y del VAN del proyecto, así como la probabilidad de que el proyecto resulte rentable para una tasa de actualización del 10%. Para realizar los cálculos solicitados, se generarán 1.000 simulaciones.

Primer paso en la aplicación del método de Montecarlo

El primer paso consiste en definir la variable de rentabilidad (VAN y TIR) y descubrir todas las variables aleatorias. En este caso, el TIR vendría dado por la expresión siguiente Figura:

Expresión del TIR en función de sus variables.

De igual forma, el VAN vendría dado por la expresión que aparece en la Figura.

Expresión del VAN en función de sus variables.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 123 de 131

Centro de Informática y Sistemas Las variables aleatorias que componen el VAN y el TIR, así como sus características, son las siguientes:

Variable Desembolso Inicial Ventas del año i Margen del año i Duración

Notación A Vi Mi n

Característica N(1000, 200) U{500, 800) N(5, 1) Discreta, según tabla

Llevaremos los datos anteriores a una hoja en blanco, como aparece en la Figura.

Paso 1, definición de variables

Segundo paso en la aplicación del método de Montecarlo El segundo paso consiste en ir obteniendo distintos valores para las distintas variables aleatorias obtenidas, para finalizar en el cálculo de un valor de la rentabilidad. Este proceso se denomina simulación. Para ello es necesario conocer las funciones de distribución o de probabilidad acumulada de las variables aleatorias (Paso 1) y obtener, mediante la generación de números aleatorios, un valor correspondiente a dichas variables. Una vez conocidos los valores de las distintas variables calcularemos el valor de la rentabilidad (VAN y TIR), tantas veces como simulación de las variables realicemos (1.000 en nuestro caso).

Elaborado por: Ing. Johnny Pacheco Contreras

Página 124 de 131

Centro de Informática y Sistemas Prepararemos la hoja de cálculo para realizar las simulaciones de las variables y de la rentabilidad, de forma ordenada, que nos sea más fácil la realización de los cálculos (Siguiente Figura)

Preparación de la hoja de cálculo para realizar las simulaciones.

Simulación del desembolso inicial Para realizar la simulación del desembolso inicial, nos situaremos en la celda B23, de nuestra hoja, desde donde deseamos que comience la generación de valores simulados de esta variable, y haremos lo siguiente:

1. Datos>Análisis de datos>Generación de números aleatorios>Aceptar, y nos aparecerá el cuadro de diálogo para la generación de números aleatorios. 2. En el campo Números de variables, introduciremos el valor 1. 3. En el campo Cantidad de números aleatorios, introduciremos el valor 1000, que se corresponde con el número de simulaciones que deseamos realizar. 4. En el campo Distribución, elegiremos en el desplegable la distribución Normal. 5. En el campo Parámetros, se nos solicita que introduzcamos el valor de la media y de la desviación típica. Nosotros introduciremos los valores 1.000 y 200, respectivamente. 6. El campo Iniciar con, lo dejaremos en blanco.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 125 de 131

Centro de Informática y Sistemas 7. En el campo Opciones de salida, haremos clic en la opción Rango de salida y allí introducimos la coordenada $B$23. 8. Al pulsar en Aceptar, se nos generarán los 1.000 valores simulados para el desembolso inicial. Una vez cumplimentado el cuadro de diálogo para la generación de números aleatorios, correspondiente al desembolso inicial, quedará tal y como se muestra en la Figura.

Cuadro de diálogo para la simulación del desembolso inicial.

El resultado obtenido, se muestra en la Figura, donde se habrán generado 1.000 valores del desembolso inicial.

Resultados de la simulación del desembolso inicial.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 126 de 131

Centro de Informática y Sistemas Simulación de las ventas Para realizar la simulación de las ventas, nos situaremos en la celda C23, de nuestra hoja, desde donde deseamos que comience la generación de valores simulados de esta variable, y haremos lo siguiente: 1. Datos>Análisis de datos>Generación de números aleatorios>Aceptar, y nos aparecerá el cuadro de diálogo para la generación de números aleatorios. 2. En el campo Números de variables, introduciremos el valor 1. 3. En el campo Cantidad de números aleatorios, induciremos el valor 1000, que se corresponde cnúmero de simulaciones que deseamos realiza! 4. En el campo Distribución, elegiremos en el desplegable la distribución Uniforme. 5. En el campo Parámetros, se nos solicita que induzcamos el valor entre y el valor y, que se corresponden con el valor pesimista y el valor optimista. Nosotros introduciremos los valores 500 y 800 respectivamente. 6. El campo Iniciar con, lo dejaremos en blanco. 7. En el campo Opciones de salida, haremos clic opción Rango de salida y allí introducimos la celda $C$23. 8. Al pulsar en Aceptar, se nos generarán los 1.00 lores simulados para las ventas.

Una vez cumplimentado el cuadro de diálogo para generación de números aleatorios, correspondiente ventas. que comience la generación de valores simulados de esta variable, y haremos lo siguiente:

1. Datos>Análisis de datos>Generación de números aleatorios>Aceptar, y nos aparecerá el cuadro de diálogo para la generación de números aleatorios. 2. En el campo Números de variables, introduciremos el valor 1. 3. En el campo Cantidad de números aleatorios, introduciremos el valor 1000, que se corresponde con el número de simulaciones que deseamos realizar. 4. En el campo Distribución, elegiremos en el desplegable la distribución Normal.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 127 de 131

Centro de Informática y Sistemas 5. En el campo Parámetros, se nos solicita que introduzcamos el valor de la media y de la desviación típica. Nosotros introduciremos los valores 5 y 1, respectivamente. 6. El campo Iniciar con, lo dejaremos en blanco. 7. En el campo Opciones de salida, haremos clic en la opción Rango de salida y allí introducimos la coordenada $D$23. 8. Al pulsar en Aceptar, se nos generarán los 1.000 valores simulados para el margen. Una vez cumplimentado el cuadro de diálogo para la generación de números aleatorios, correspondiente al margen.

Simulación de la duración Para realizar la simulación de la duración, nos situaremos en la celda E23, de nuestra hoja, desde donde deseamos que comience la generación de valores simulados de esta variable, y haremos lo siguiente:

1. Datos>Análisis de datos>Generación de números aleatorios>Aceptar, y nos aparecerá el cuadro de diálogo para la generación de números aleatorios. 2. En el campo Números de variables, introduciremos el valor 1. 3. En el campo Cantidad de números aleatorios, introduciremos el valor 1000, que se corresponde con el número de simulaciones que deseamos realizar. 4. En el campo Distribución, elegiremos en el desplegable la distribución Discreta. 5. En el campo Parámetros, se nos solicita que introduzcamos el rango de entrada de valores y probabilidades. Estos valores los tenemos situados en nuestra hoja, enB13:C15. 6. El campo Iniciar con, lo dejaremos en blanco. 7. En el campo Opciones de salida, haremos clic en la opción Rango de salida y allí introducimos la coordenada $E$23. 8. Al pulsar en Aceptar, se nos generarán los 1.000 valores simulados para la duración. Una vez cumplimentado el cuadro de diálogo para la generación de números aleatorios, correspondiente a la duración, quedará tal y como se muestra en la Figura.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 128 de 131

Centro de Informática y Sistemas

En la celda H23 escribir =-B23 y luego copiar la formula hasta la celda H5022. En la celda I23 escribir =SI($E23>=I$22,ENTERO($C23)*$D23,"") y luego arrastrar a la derecha hasta la celda L23. El rango I23:L23 esta seleccionado, en modo copiar arrastrar hasta copiar la formula en el rango I23:L5022. En la celda G23 escribir =TIR(H23:L23) y luego copiar la formula hasta la celda G5022. En la celda F23 escribir =VNA($B$17,I23:L23)+H23 y luego copiar la formula hasta la celda F5022.

En la celda F18 escribir =PROMEDIO(F23:F5022) En la celda G18 escribir =PROMEDIO(G23:G5022) En la celda F19 escribir =DESVESTP(F23:F5022) En la celda G19 escribir =DESVESTP(G23:G5022) En la celda F20 escribir =CONTAR.SI(F23:F5022,">=0")/5000 En la celda G21 escribir =CONTAR.SI(G23:G5022,">=10%")/5000

Elaborado por: Ing. Johnny Pacheco Contreras

Página 129 de 131

Centro de Informática y Sistemas Luego de completar el cuadro se muestra la siguiente figura.

Elaborado por: Ing. Johnny Pacheco Contreras

Página 130 de 131

Centro de Informática y Sistemas

Bibliografía 1. Excel 2007. Análisis de datos y modelos de negocio. Wayne L. Winston 2. Modelos económicos y financieros con Excel 2007. Fernando Moreno Bonilla y Manuel Cano Rodríguez. 3. Fundamentos de Finanzas, un enfoque peruano. Dr. Manuel Chu Rubio. 4. Finanzas aplicadas: teoría y práctica. Dr. Manuel Chu Rubio. 5. Excel Financiero 2010 – Editorial Macro. Ing. Johnny Pacheco C.

Paginas: www.todoexcel.edu.pe http://excelfinanciero.blogspot.com/ http://www.masterfinanciero.es/ http://simplementexcel.blogspot.com/

Elaborado por: Ing. Johnny Pacheco Contreras

Página 131 de 131