39203952 manual de excel avanzado[1]

MANUAL DE EXCEL AVANZADOPARA ESTUDIANTES DE INGENIERIA

PROF. CARLOS LABBE OPAZO

CURSO DE EXCEL AVANZADO

2

MANUAL DE EXCEL AVANZADO............................................................................4 Introduccin.............................................................................................................4 Grficos Especiales................................................................................................5 Grficos de Lnea vs. Grficos de Dispersin XY ..............................................5 Grficos de Dispersin XY...................................................................................7 Esquemas.............................................................................................................11 Descripcin de Esquemas.................................................................................11 Creacin de un Esquema..................................................................................13 Funciones financieras...........................................................................................16 Introduccin.......................................................................................................16 Funciones Financieras.......................................................................................17 NPER..............................................................................................................17 PAGOINT........................................................................................................18 PAGOPRIN.....................................................................................................18 VA...................................................................................................................19 VNA.................................................................................................................19 VF....................................................................................................................20 Funciones para calcular la tasa de rendimiento................................................20 Introduccin....................................................................................................20 TASA...............................................................................................................21 TIR..................................................................................................................21 TIRM...............................................................................................................22 Funciones para calcular depreciaciones...........................................................22 Introduccin....................................................................................................22 DB...................................................................................................................23 DDB.................................................................................................................23 DVS.................................................................................................................24 SLN.................................................................................................................24 SYD.................................................................................................................24 Solver....................................................................................................................25 Descripcin........................................................................................................25 Optimizacin......................................................................................................25 Herramienta Solver............................................................................................26 Instalacin del Solver.........................................................................................27 Ejercicios.........................................................................................................27 Problema N 1..............................................................................................27 Problema N 2..............................................................................................32 Informe de Respuestas ...............................................................................40 Informe de sensibilidad ..............................................................................42 Informe de Lmites ......................................................................................43 Conclusiones...............................................................................................44 Opciones de Solver......................................................................................45 Opciones para modelos no-lineales............................................................47 Introduccin a Estadstica Aplicada a travs de Excel.........................................49 Distribuciones de Frecuencia e Histogramas....................................................49 Finalidad de las distribuciones de frecuencias..................................................50 Interpretacin de las distribuciones de frecuencias..........................................50 Prof. Carlos Labb O. 12-10-10


3

Formalizacin de las distribuciones de frecuencia............................................51 Distribuciones de frecuencias con la funcin FRECUENCIA del Excel...............52 Introduccin.......................................................................................................52 Sintaxis...............................................................................................................52 Observaciones...................................................................................................52 Ejemplo N 1......................................................................................................53 Ejemplo N 2:.....................................................................................................55 Distribuciones de frecuencia e histogramas con herramientas de anlisis......64 Herramientas de anlisis estadstico.................................................................64 Funciones de hojas de clculo relacionadas ...............................................65 Acceder a las herramientas de anlisis de datos .........................................65 Varianza de dos factores con varias muestras por grupo ...........................67 Varianza de dos factores con una sola muestra por grupo............................67 Correlacin......................................................................................................67 Covarianza......................................................................................................68 Prueba t para varianza de dos muestras. ......................................................68 Anlisis de Fourier .........................................................................................68 Histograma .....................................................................................................69 Media mvil ....................................................................................................69 Generacin de nmeros aleatorios................................................................69 Jerarqua y percentil ......................................................................................69 Regresin .......................................................................................................69 Muestreo ........................................................................................................70 Prueba t..............................................................................................................70 Prueba t para dos muestras suponiendo varianzas iguales..........................70 Prueba t para dos muestras suponiendo varianzas desiguales ...................70 Prueba t para medias de dos muestras emparejadas .................................70 Prueba z.............................................................................................................70 Histograma ........................................................................................................71 Introduccin....................................................................................................71 Descripcin.....................................................................................................71 Distribuciones de frecuencia e histogramas con tablas dinmicas................74 Ejercicio N 1:..............................................................................................74 Ejercicio N 2:..............................................................................................77 Ejercicio N 3...............................................................................................80 Ejercicio N 4...............................................................................................94 GLOSARIO DE TERMINOS.................................................................................99

Prof. Carlos Labb O. 12-10-10


4

MANUAL DE EXCEL AVANZADOIntroduccinComo su ttulo lo sugiere estos apuntes son de tcnicas avanzadas de Excel, es decir, que no corresponden a un excel bsico ni a un excel intermedio, en general estn dirigidas a la gestin. Estos apuntes se han hecho pensando en usuarios con vasta experiencia en Excel, que ya han superado el segundo grado en manejo de planillas. Se supone que quien estudia en estos apunte ya sabe como construir una planilla simple, como escribir frmulas y que pasa cuando se copian. Como se imprime una planilla y como se graba. Como se imprime una planilla y como se graba. Saben como definir, usar e interpretar tablas dinmicas. Como crear, definir e interpretar escenarios. En estos apuntes se seleccionaron las tcnicas que se estima necesita un ingeniero o un ejecutivo para la gestin, es decir, estos apuntes profundizan en todos aquellos comandos u opciones que son poco usados, no porque no sean tiles sino porque casi nadie los conoce, pero que se estima son necesarios para el ejecutivo moderno en la toma de decisiones o en el control. El Manual de Excel Bsico para Estudiantes de Ingeniera se public el ao 2004 en este mismo Portal: www.lawebdelprogramador.com (lleva hasta ahora ms de 60.000 visitas), y durante el ao 2006 se public el Manual de Excel Intermedio para Estudiantes de Ingeniera. Este proyecto intenta tratar el Excel completo en cinco Manuales: Manual de Excel para Estudiantes de Ingeniera, ya publicado en LWP1, Manual de Excel Intermedio para Estudiantes de Ingeniera, ya publicado en la LPW, Manual de Excel Avanzado para Estudiantes de Ingeniera, que es este texto, Manual de Herramientas Estadsticas de Excel para Estudiantes de Ingeniera, que se espera su publicacin para este ao 2007, y Tpicos de Excel para Estudiantes de Ingeniera, se espera su publicacin para el ao 2008, que tratar fundamentalmente de funciones de Excel poco conocidas y por lo tanto poco usadas. Este manual trata las siguientes materias: Grficos especiales, Esquemas, Funciones financieras,11

La web del programador.


CURSO DE EXCEL AVANZADO Solver, Estadsticas aplicadas a travs de Excel.

5

Todos estos puntos son desarrollados en forma Terica y prctica y con ejemplos que les puedan servir a los estudiantes de Ingeniera, a los ingenieros y a los ejecutivos en la gestin.

Grficos EspecialesGrficos de Lnea vs. Grficos de Dispersin XY Una PYME2 fabrica solamente tres tipos de muebles: Escritorios, Sillas y Estantes. Mediante un gran esfuerzo reinvirtiendo las utilidades y capacitando a su personal ha logrado ir duplicando la produccin. La produccin en los ltimos aos se muestra en la siguiente tabla:

PRODUCCION DE UNA PYMEAOS ESCRITORIOS SILLAS ESTANTES 1980 268 323 194 1990 536 646 388 1996 804 969 582 2000 1072 1292 776 Si esta tabla se grafica mediante un grfico de Lneas3 el resultado se muestra en la pgina siguiente:

2 3

PYME: Abreviatura de Pequea y Mediana Empresa Grficos se vio con todo detalle en la publicacin: Manual de Excel para Estudiantes de Ingeniera, de este mismo Portal: La web del programador. Prof. Carlos Labb O. 12-10-10

CURSO DE EXCEL AVANZADOPRODUCCION DE UNA PYME 1400 1200 1000 PRODUCTOS 800 600 400 200 0 1980 1990 AOS 1996 2000 ESCRITORIOS SILLAS ESTANTES

6

Como se puede observar este grfico est con graves errores, ya que el aumento de la produccin es el mismo para todos los aos indicados, sin embargo, la diferencia entre los aos no es la misma, por lo tanto debera salir una curva exponencial. Esto se soluciona usando grficos tipo de Dispersin XY. Basta con cambiar el tipo de grfico para que aparezcan las curvas correctas, como se muestra en la figura siguiente:PRODUCCION DE UNA PYM E 1400 1200 1000 PRODUCTOS 800 600 400 200 0 1975 ESCRITORIOS SILLAS ESTANTES

1980

1985

1990 AOS

1995

2000

2005



7

Los grficos Dispersin XY son los indicados cuando la variable del eje de las X no representa incrementos constantes. Grficos de Dispersin XY Usando los grficos de dispersin se puede tener grficos como el siguiente:

Esta roseta se llama figura de Lissajous, en honor del fsico del siglo XIX que las estudio por primera vez. Estas figuras aparecen al superponer movimientos oscilatorios. Lissajous usaba un aparato muy complejo, con dos diapasones y espejitos que reflejaban la luz. Ahora se pueden obtener las mismas figuras en el computador usando grficos de Dispersin XY. Para construir este tipo de grficos se usa una tabla como la figura siguiente:



8

Los pasos para hacer esta tabla son los siguientes: En la columna A se generan los nmeros del 1 al 100, La columna B debe quedar libre, En la celda C1 se escribe la frmula: =SENO(2*G$1*PI()*A1/10) En la celda D1 se escribe la frmula: =COS(2*G$1*PI()*A1/10) Se extiende el rango C1:D1 hasta la fila 100 En la celda G1 se escribe el valor 2 En la celda G2 se escribe el valor 5 Se grafica el rango C1:D100

Para hacer este tipo de grficos hay unas diferencias con los grficos normales, por lo tanto lo detallamos paso a paso. Se coloca el cursor en D1 o en cualquier celda del rango anterior, Se toman las opciones Insertar/Grfico, entonces aparece el Asistente para Grficos. En el primer paso se indica el tipo de grfico Dispersin XY y el subtipo de la segunda fila, segunda columna. Se da un clic en Siguiente. En el segundo paso del asistente indicamos Series en columnas. Se da un clic en Siguiente para pasar a la etapa de Opciones de grfico. En la ficha Eje se desmarcan todas las opciones. En la ficha Lneas de divisin, tambin se desmarcan todas las opciones. En la ficha Leyenda se desmarca la opcin Mostrar leyenda. Se da un clic en Siguiente. Se marca la opcin Colocar grfico en una hoja nueva. Prof. Carlos Labb O. 12-10-10

CURSO DE EXCEL AVANZADO Se da un clic en Finalizar.

9

El resultado ser similar al de la figura siguiente:

Este grfico se puede optimizar un poco, por ejemplo, eliminndole el fondo gris, esto se hace de la siguiente forma: Se da un clic sobre el fondo del grfico, usando el botn derecho del mouse. Del men contextual que aparece, se toma la opcin Formato de rea de trazado, aparece el cuadro que se muestra a continuacin:



10

Dentro de rea se da la opcin Ninguna. Hacemos un clic en Aceptar.

La figura queda como se muestra a continuacin:



11

Las frmulas de la tabla fueron escritas de forma tal que variando el contenido de G1 y/o G2, las curvas pueden variar de inmediato, por ejemplo si coloco 5 en G1 y en G2, aparece la curva que se muestra en la pgina siguiente:

En cambio la Figura de Lissajous, se obtiene colocando un 5,1 en G1 y un 5 en G2, al efectuar este cambio queda esta figura:

Lo importante de este captulo es que mediante el estudiante de Excel comprenda que mediante el Excel se pueden simular los resultados de efectos fsicos de cualquier orden: por ejemplo: Las curvas resultantes del sonidos de dos diapasones, cadas de cuerpos, clculo de trayectorias espaciales, situaciones econmicas, etc

Esquemas.Descripcin de Esquemas



12

Muchas hojas de clculos estn diseadas en jerarquas de celdas. Aplicar un esquema a una hoja consiste en asociar una relacin de subordinacin entre las diferentes celdas. Para explicar los esquemas podemos apoyarnos en la hoja de la figura siguiente, que muestra el desglose de la produccin de un ao en meses y en trimestres. Cada trimestre suma los valores de los meses que componen el trimestre, y se entregan como totales las sumas de los trimestres: Cada trimestre es un esquema, por lo tanto en una figura como la siguiente debe haber cuatro esquemas, cada uno con sus totales. La lnea horizontal que se observa en la figura siguiente, indica que hayan esquema que abarca el primer trimestre del ao.

En la figura siguiente se puede observar la esquematizacin de una hoja de Excel, en que se muestran solo los tomates de los cuatro trimestres y el total general del ao. Los signos ms que se muestran en la parte superior de los trimestres indican que se ocult la parte de detalle y slo se muestran los totales de cada trimestre.



13

Los nmeros 1 y 2 que se muestran en la parte superior, indican que un nivel de esquemas y datos de una hoja de clculo. A su vez esta hoja se puede volver a esquematizar, dejando como un esquema los totales trimestrales, y al ocultar stos, queda como se muestra en la figura siguiente:

Los nmeros 1, 2 y 3 que se muestran en la parte superior izquierda indican que hay un nivel de datos (3), un primer nivel de esquemas que resume esos datos (2) y un segundo nivel de esquemas que resume el nivel anterior (3). Al igual que en el caso anterior el signo + indica que es un resumen de datos esquematizados. Creacin de un Esquema



14

Antes de esquematizar es importante asegurarse de que estn introducidos todos los datos y las frmulas en la zona de la hoja que se va a esquematizar. Adems los datos deben estar jerarquizados. Se pueden crear esquemas de forma automtica o con la posibilidad de incluir modelos con el comando Configurar o con la barra de herramientas. N este ejemplo se va a esquematizar unos datos de ventas que necesita la gerencia para la gestin; se trata de las ventas anuales mes por mes con la siguiente informacin: Artculos producidos, Precio unitario, Valor total de produccin, Precio de venta, Comisin al vendedor, Precio de venta neto, IVA4 Precio de venta al pblico (PVP). El primer paso es construir la planilla: colocarle un ttulo: por ejemplo (Ventas ao 2006), y a partir de la FILA 1, crear las siguientes ttulos de filas: Descripcin, Enero, Febrero, Marzo Trimestre 1, Abril, Mayo, Junio, Trimestre 2, Julio, Agosto, Septiembre, Trimestre 3, Octubre, Noviembre, Diciembre; Trimestre 4, Total Ao. Si lo ha hecho correctamente, debera quedarle algo como la tabla de la pgina siguiente:4

IVA: Impuesto al valor agregado, impuesto sobre las ventas y servicios, que en este momento est en el 19% sobre el precio de venta. Prof. Carlos Labb O. 12-10-10


15



16

en la cual puede resumir y dejar slo los subtotales, o resumir ms y dejar slo el total general. Como tambin puede ampliarlos hasta llegar a los datos originales.

Funciones financieras.Introduccin Algunas de las funciones financieras tales como Pago se vieron en Manuales anteriores5 Las funciones financieras NPER, PAGO6, PAGOINT, PAGOPRIN, VA, VNA Y VF tienen en comn los argumentos: tasa : Porcentaje de inters nper : Plazo de la inversin o prstamo pago : Dividendo o cuota mensual75

La funcin Pago se vio en el Manual de Excel para Estudiantes de Ingeniera pginas 16 a 31 del mismo autor y se volvi a insistir sobre ella el Manual de Excel Intermedio para Estudiantes de Ingeniera pginas 47 a 49 del mismo autor. 6 Ya vista anteriormente. 7 El pago tiene, al menos en Chile, dos nombres: Si se trata de prstamos personales o a corto plazo se llama cuota, si se trata de prstamos hipotecarios o a largo plazo se llama dividendo. Pero, en ambos casos es el abono o cuota que se paga mensualmente. Prof. Carlos Labb O. 12-10-10

CURSO DE EXCEL AVANZADO va vf Tipo

17

: Valor actual que se percibe o desembolsa al principio de la operacin, tambin se denomina Capital o monto del prstamo. : Valor futuro que se percibe o desembolsa al final de la operacin. Si se omite se supone que el valor futuro es 0. : Indica el tipo de la operacin. Si toma el valor:

0 se omite. Indica que los pagos se efectuarn al final del perodo (mes, trimestre, semestre o ao, etc.) 1: Indica que los pagos se realizan al principio del perodo. Si en la funcin que aparece en Excel cuando se va a ejecutar, si el argumento aparece entre parntesis cuadrados indica que es opcional. Lo argumentos tasa y nper debe referirse al mismo perodo de tiempo, es decir, por ejemplo, no puede colocarse una tasa de inters anual y para perodo mensual. A fin de simplificar los clculos la tasa mensual se calcula dividiendo por 12 la tasa anual. Aunque esto est incorrecto la diferencia es mnima con la frmula de clculo real:tasa = (1 + i )1 / k 1

i: Tipo de inters expresado en tanto por 1 k: Nmero de los nuevos perodos que hay en un ao Por ejemplo para transformar una tasa anual de 15% en una tasa mensual, la frmula a aplicar es: =POTENCIA((1+0.15);(1/12))-1 Lo que nos da por resultado: 1.01, en cambio, si dividimos 15/12 nos da: 1.25, por lo cual, para las siguientes frmulas para reducir de una tasa anual a una tasa mensual, para simplificar los clculos se dividir la tasa anual por 12, ya que la diferencia es mnima para cantidades pequeas. Funciones Financieras NPER Calcula el nmero de perodos necesarios para amortizar un prstamo, dadas las cantidades a para, la tasa de inters, el valor actual y el valor futuro (si hay). Su formato es:= NPER (tasa ; pago ; va ; [vf ]; [tipo ])



18

El argumento pago debe ser igual o superior al producto de los argumentos tasa por va, en caso contrario NPER devuelve: #NUM! Ejemplo: Se desea saber en cuanto tiempo se amortiza un prstamo de $ 10.000.000 al 11% anual si se desea pagar una cantidad mensual de $ 120.000: =NPER(11%/12;-120.000;10000000) Excel devuelve 158,18 meses. PAGOINT Calcula la cantidad a pagar por intereses sobre un prstamo en un perodo determinado de tiempo con unos pagos y un tipo de inters constantes. Su formato es:= PAGOINT (tasa ; perodo ; nper ; va ; [vf ]; [tipo ])

Perodo: Perodo para el que se desea calcular el pago de intereses. Debe ser un nmero comprendido entre 1 y nper. Ejemplo: Se desea saber cual es la cantidad a pagar por concepto de intereses en el primer mes correspondiente al pago de un prstamo de $ 10.000.000, a veinte aos, si la tasa de inters es del 11% anual: La frmula es: =PAGOINT(11%/12;1;20*12;10000000) Excel entrega como resultado: -$ 91.666,67 PAGOPRIN Calcula la cantidad amortizada de un prstamo en un perodo determinado de tiempo, con unos pagos y un tipo de inters constante. La suma de las funciones PAGOINT y PAGOPRIN devuelve la cantidad total a pagar determinada por la funcin pago. Su formato es:= PAG OPRIN (tasa ; perodo ; nper ; va ; [vf ]; [tipo ])

perodo: Perodo para el que se desea calcular los pagos de intereses. Debe ser un nmero comprendido entre 1 y nper. Ejemplo: Se desea saber cual es la cantidad amortizada en el primer mes que corresponde al pago de un prstamo de $ 10.000.000 a 20 aos y a una tasa de inters del 11% anual.


CURSO DE EXCEL AVANZADO =PAGOPRIN(11%/12;1;20*12;10000000) Excel entrega como resultado: $ -11.552,17 VA

19

Determina el valor actual de una inversin en base a una serie de pagos peridicos iguales o el de un pago global. Si el valor devuelto por la funcin es superior al coste de la inversin, sta es buena. Su sintaxis es:=VA (tasa ; nper ; pago ; [vf ]; [tipo ])

Ejemplo: Se desea saber si es rentable invertir US$ 4.000, si se espera recibir US$ 1.000, durante los prximos 7 aos. Como tasa se considera un inters bancario de 10% anual: =VA(10%;7;1000) Excel devuelve el valor US$ 4.868,42. Esto significa que deberamos estar dispuestos a invertir ahora US$ 4.868,42 para recibir US$ 7.000 durante los prximos 7 aos. Al ser la inversin inicial de US$ 4.000, sta es una buena inversin. Nota: Si se omite un argumento en la mitad de la frmula para usar e argumento vf, se debe escribir un punto y coma por el argumento omitido. Ejemplo: Supongamos que en lugar de los US$ 1.000 anuales, nos proponen pagarnos los US$ 7.000 al final de los 7 aos Es bueno el negocio? La frmula a utilizar es: =VA(10%;7;;7000) Excel devuelve el valor US$ 3.592,11. Esto quiere decir que deberamos desembolsar ahora US$ 3.592,11 para recibir US$ 7.000 al cabo de 7 aos. Al ser la inversin inicial de US$ 4.000, esta no es una buena inversin. VNA Calcula el valor neto actual de una serie de flujos de caja descontados a un tipo de inters. VNA es otra funcin para determinar si una inversin es buena. La inversin se considera rentable cuando VNA da un nmero positivo. Su sintaxis es: =VNA(tasa;valor1;valor2;) Prof. Carlos Labb O. 12-10-10


20

La funcin VNA se diferencia de la funcin VA, en que mientras VA considera siempre la cantidad constante, VNA permite incluir cantidades variables tanto positivas como negativas. Ejemplo: Supongamos que se desea saber si es rentable invertir US$ 250.000, si esperan una prdida de US$ 60.000 el primer ao, con ganancias en los siguientes aos de US$ 100.000, US$ 150.000 y US$ 190.000, o invertirlo en letras con un inters del 12% anual. La frmula es la siguiente: =VNA(12%;-60000;100000;150000;190000) Excel devuelve: US$ 3.663,43 Al ser un nmero positivo, indica que la inversin es buena. VF Determina el valor futuro de una inversin consistente en una serie peridica de pagos iguales o en una nica entrega a una tasa de inters fija. Su formato es:=VF (tasa ; nper ; pago ; [va ]; [tipo ])

Ejemplo: Supongamos que se desea saber cual es el capital final de un plan de pensiones a 30 aos, si se desembolsan todos los meses $ 10.000 a un inters del 8%. La frmula es la siguiente: =VF(8%/12;30*12;-10000;;1) Excel devuelve la cantidad de $ 15.002.524,75 Ejemplo: Supongamos que se posee un capital acumulado de $100.000, la frmula tendr el siguiente aspecto: =VF(8%/12;30*12;-10000;-100000;1) Excel devuelve $ 16.096.524,75 Funciones para calcular la tasa de rendimiento Introduccin Prof. Carlos Labb O. 12-10-10


21

Las funciones TASA, TIR Y TIRM calculan las tasas de rendimiento. Utilizan un nuevo argumento: Estimacin: valor inicial para empezar los clculos. Por defecto toma el valor 10%. TASA Tasa determina el tipo de inters de una inversin que genera unos ingresos o gastos peridicos iguales. Su sintaxis es:= TASA ( nper ; pago ; va ; [vf ]; [tipo ]; [estimacin

])

Excel calcula la tasa mediante un proceso iterativo hasta alcanzar el valor deseado o haya efectuado 20 iteraciones. Si tasa devuelve #NUM!, quiere decir que necesita ms iteraciones para llegar al resultado final. En este caso en el argumento estimacin ser necesario especificar un valor entre 10 y 100. Ejemplo: Por ejemplo supongamos que se desea saber el tipo de inters de un prstamo de $ 10.000.000, que genera unos gastos mensuales de $ 120.000 durante 20 aos. La frmula a aplicar es: =tasa(20*12;120000;10000000) Excel da como inters mensual el 1%. Para calcular el Inters anual se multiplica por 12. TIR La tasa interna de rendimiento, TIR, es el tipo de inters que provoca que el valor neto actual de una inversin sea cero, VNA=0. En otras palabras, es el tipo de inters que provoca que el valor actual de todas las entradas sea igual a los costos reembolsados en la inversin. Una inversin ser rentable cuando el Tir sea mayor que la tasa obstculo. Su formato es:= TIR (valores ; [estimacin

])

valores: Matriz o una referencia a un rango de celdas numricas. El rango debe incluir al menos u numero negativo y otro positivo. Excel ignora los valores no numricos. Si excel devuelve el valor de error #NUM! Es necesario incluir el argumento estimacin al igual que suceda en Tasa.


CURSO DE EXCEL AVANZADO Ejemplo:

22

Supongamos que en rango D10:D15 tenemos una serie numrica que representa por una parte la cantidad a invertir, US$ 100.000 (se debe especificar como nmero negativo y por otra, los beneficios que se esperan conseguir en los prximos aos: US$ 25.000, US$ 33.000, US$ 40.000, US$ 50.000 y US$ 55.000. El tipo de inters a superar es del 10%. La frmula a aplicar es: =TIR(D10:D15) Excel devuelve el valor 25%, que es superior a tasa obstculo del 10%, por lo cual la inversin es altamente rentable. TIRM La tasa interna de rendimiento modificado, TIRM, es similar a la funcin TIR, con la diferencia de que TIRM tiene en cuenta el costo del dinero prestado y el hecho de considerar que se reinvierten los efectivos generados. Su sintaxis es: =TIRM(valores;tasa_financiacin;tasa_reinversin) tasa_financiacin: Tipo de inters a que se pide prestado el dinero tasa_reinversin: Tipo de inters al que se reinvierten los efectivos generados. Ejemplo: Supongamos que en rango D10:D15 tenemos una serie numrica que representa por una parte la cantidad a invertir, US$ 100.000 (se debe especificar como nmero negativo y por otra, los beneficios que se esperan conseguir en los prximos aos: US$ 25.000, US$ 33.000, US$ 40.000, US$ 50.000 y US$ 55.000. El tipo de inters a superar es del 10%. Adems debe considerarse una tasa de financiacin del 10% y una tasa de reinversin del 12%. La frmula a aplicar es: =TIR(D10:D15;10%;12%) Excel devuelve el valor 20% que es superior a la tasa obstculo del 10%. Funciones para calcular depreciaciones Introduccin Las depreciaciones son calculadas por la funciones: DB, DDB, DVS, SLN, y SYD, que utilizan, entre otros, los siguientes argumentos:



23

costo: Valor inicial del activo. valor_residual: Valor del activo cuando est amortizado en su totalidad. vida: Perodo de tiempo en que el activo est en servicio. perodo: Perodo de vida del activo, durante el cual se desea calcular los gastos de depreciacin. factor: Factor para la tasa de depreciacin. Por defecto toma el valor 2. Los argumentos vida y perodo debe representar el mismo perodo de tiempo, ya sea, mensual, trimestral, semestral, anual, etc. DB Calcula la depreciacin de un bien durante un determinado perodo a una tasa fija. Su sintaxis es:= DB (cos to; valor _ residual ; vida ; perodo ; [ mes ])

mes: Nmero de meses del primer ao. Si se omite, asume el valor 12. Ejemplo: Supongamos que una empresa compra una mquina por valor de US$ 10.000 con una vida til de cinco aos y un valor residual de US$ 200 La depreciacin al cuarto ao se calcula: =DB(10000;200;5;4) Excel devuelve US$ 518 DDB Calcula la depreciacin de un activo durante un determinado perodo por el mtodo de depreciacin de doble disminucin de saldo, que considera una tasa de depreciacin superior en los perodos iniciales e inferior al final. Su sintaxis es:= DDB (cos to; valor _ residual ; vida ; perodo ; [ factor ])

Ejemplo: Supongamos se desea calcular el valor de depreciacin en el primer mes de una mquina que cuesta US$ 10.000, con una vida til de 5 aos y un valor residual de US$ 200. La frmula a aplicar es: =DDB(10000;200;5*12;1) Excel devuelve US$ 333.33 Prof. Carlos Labb O. 12-10-10


24

DVS Calcula la depreciacin de un activo para un perodo parcial o completo por el mtodo de doble disminucin del saldo u otro factor decreciente acelerado. Su sintaxis es:= DVS (cos to; valor _ residual ; vida ; comienzo ; fin ; [ factor ]; [ sin_ cambio ])

comienzo: Perodo previo al momento del comienzo fin: Perodo final sin_cambio: si no se especifica toma el valor 2 y aplica el mtodo de doble disminucin del saldo. Cuando produce una depreciacin mayor que el factor especificado, Excel cambia el mtodo de depreciacin constante. Para evitar el cambio se debe especificar el valor 1. Ejemplo: Supongamos que se desea calcular el valor depreciado, durante el primer ao de vida, de una mquina que cuesta US$ 10.000 con una vida til de 5 aos y un valor residual de US$ 200. La frmula a aplicar es la siguiente: =DVS(10000;200;5;0;1) Excel devuelve US$ 400. SLN Calcula la depreciacin de un activo para un perodo determinado suponiendo que la depreciacin es constante y uniforme a lo largo de la vida til. Su sintaxis es:

=SLN(costo;valor_residual;vida)Ejemplo: Supongamos que se desea calcular el valor depreciado anualmente, usando depreciaciones iguales, de una mquina que cuesta US$ 10.000, y cuyo valor residual es de US$ 200 al cabo de 5 aos. La frmula es: =SLN(10000;200;5) Excel devuelve US$ 1.960 SYD Prof. Carlos Labb O. 12-10-10


25

Calcula la depreciacin de un activo para un perodo determinado utilizando un mtodo regresivo variable, al igual que el mtodo decreciente doble, llamado depreciacin de la suma de los dgitos del ao. Su sintaxis es:

=SYD(costo;valor_residual;vida_til;perodo)Ejemplo: Supongamos que se desea calcular el valor depreciado para el segundo ao de una mquina que cuesta US$ 10.000 y cuyo valor residual es de US$ 200 al cabo de 5 aos, utilizando el mtodo de la suma de los dgitos del ao. La frmula a aplicar es: =SYD(10000;200;5;2) Excel devuelve US$ 213,33

SolverDescripcin El Solver es una herramienta del Excel que permite resolver problemas de optimizacin, es decir, a partir de unos objetivos y estableciendo unas condiciones (restricciones), permite resolver problemas de cierta complejidad. En este Manual trabajo se procura dar una sencilla explicacin de su uso como herramienta de optimizacin. Optimizacin Un problema de optimizacin consiste en encontrar aquellos valores de ciertas variables que optimizan (es decir, hacen mxima o mnima, segn el caso), una funcin de estas variables. A las variables las llamaremos variables controlables o variables de decisin. Matemticamente, significa encontrar los valores de x1, x2,..., xn, tales que hacen mxima (o mnima) a la funcin f (x1, x2,..., xn). El mtodo ms conocido para encontrar el ptimo de una funcin es a travs del anlisis de sus derivadas. Este mtodo tiene dos limitaciones: no siempre la funcin es derivable, y, adems, no siempre el ptimo nos da una solucin que tenga sentido en la prctica. Debido a la primera limitacin, surgieron los mtodos numricos, que parten de una solucin inicial, y mediante algn algoritmo iterativo, mejoran sucesivamente la solucin. Tal como se describe el diagrama siguiente: Prof. Carlos Labb O. 12-10-10


26

Solucin Inicial

Es pti ma? no Nueva Solucin

s

Fin

Debido a la segunda limitacin, surgieron los mtodos de optimizacin restringida. El nombre se debe a que podemos ponerle restricciones a las variables, de modo que cumplan una o ms condiciones. La restriccin ms comn que se da en la prctica es que las variables deben ser no negativas. No tiene ningn sentido una "solucin" que implique producir cantidades negativas, o sembrar un nmero negativo de hectreas, o llevar un nmero negativo de paquetes. Pero, adems, surgen naturalmente otras restricciones en el mundo real, debido a limitaciones de horas de trabajo, capital, tiempo, insumos, o a que, quizs deseamos imponer ciertos mnimos o mximos de calidad, riesgo, etc.. Estas restricciones pueden ser funciones de las variables controlables. Podramos resumir diciendo que en un problema de optimizacin restringida buscamos los valores de ciertas variables que optimizan una funcin objetivo, sujetas a restricciones, dadas tambin en trminos de funciones. Matemticamente, significa encontrar los valores de x1, x2, ..., xn, tales que hacen mxima (o mnima) a f (x1, x2, ..., xn), sujeto a restricciones de tipo gj (x1, x2, ..., xn) , = cj , donde cj es una constante. Los modelos ms sencillos de optimizacin restringida corresponden a modelos de Programacin Lineal, donde tanto la funcin objetivo como las restricciones son funciones lineales, las variables deben ser no negativas, y pueden tomar cualquier valor real, no necesariamente entero. Herramienta Solver S olver es una herramienta para resolver y optimizar ecuaciones mediante el uso de mtodos numricos. Con Solver, se puede buscar el valor ptimo para una celda, denominada celda objetivo, en donde se escribe la frmula de la funcin objetivo f (x1, x2, ..., xn). Solver cambia los valores de un grupo de celdas, denominadas celdas cambiantes, y que estn relacionadas, directa o indirectamente, con la frmula de la celda objetivo. En estas celdas se encuentran los valores de las variables controlables x1, x2, ..., xn. Prof. Carlos Labb O. 12-10-10


27

Puede agregar restricciones a Solver, escribiendo una frmula gj (x1, x2, ..., xn) en una celda, y especificando que la celda deber ser mayor o igual, igual, o menor o igual que otra celda que contiene la constante cj. Tambin puede especificar que los valores sean enteros, para evitar dar resultados absurdos de algunos problemas, tales como que se necesitan 3,47 empleados. Solver ajustar los valores de las celdas cambiantes, para generar el resultado especificado en la frmula de la celda objetivo.

Instalacin del Solver La herramienta Solver no se instala por defecto: En primer lugar debe tener instalada la versin profesional del Office, la versin estndar no la considera, si no tiene instalada la versin profesional consulte con su proveedor habitual de software para que se la instale, Para saber si la tiene instalada mire el men de Herramientas si tiene una opcin de nombre Solver, Si no la tiene instalada, debe instalarla y para eso debe hacer lo siguiente: Men Herramientas Sub-Men Complementos Activar la opcin Solver y Aceptar. Ejercicios Problema N 1 En una tienda de electrodomsticos se quiere introducir al mercado unos frigorficos y acondicionadores pequeos para oficinas a precios muy bajos. Los frigorficos a $ 50.000 y los acondicionadores a $ 45.000. Cada venta de un frigorfico supone 10 minutos de tiempo de un vendedor y 5 minutos del tiempo de una instalador. La venta de un acondicionador requiere 8 minutos del vendedor y 12 minutos del instalador. Se dispone de 4 vendedores y 3 instaladores, que trabajan 4 horas diarias tiles. Cuntos frigorficos y acondicionadores interesa poner a la venta durante los 20 das hbiles de la campaa? El problema a resolver consiste en: Determinar el nmero de frigorficos (celda D1) y acondicionadores (celda D2). Con un objetivo claro, que es maximizar los ingresos (celda E4). Abra un nuevo libro de Excel y en la Hoja1 escriba lo siguiente: Prof. Carlos Labb O. 12-10-10


28

Escriba en la celda E4 := 50000 * d1 + 45000 * d2 Al dar enter aparece en la celda E4 un cero, ya que las celdas d1 y d2 no tienen valores. Para la resolucin del problema tenemos una gran restriccin: se dispone de un personal y tiempo limitado. De momento se va a calcular el tiempo en minutos de los vendedores e instaladores: Escriba en la celda D15: =4*4*20 Escriba en la celda D16: =3*4*20 Celda E15: =D15*60 Celda E16: =D16*60

En otras palabras, se dispone de 320 horas (19.200 minutos) de trabajo por parte de los vendedores y 240 horas (14.400 minutos) de trabajo por parte de los instaladores. Ahora se trata de calcular ahora el tiempo (en minutos) de los vendedores e instaladores para un nmero indeterminado de frigorficos (celda D1) y acondicionadores (celda D2). Escriba: Celda C10: =10*D1 Celda C11: =8*D2 Celda D10: =5*D1 Celda D11: =12*D2 Celda C12: =C10+C11 Celda D12: =D10+D11 Est claro que en la celda C12 tenemos (momentneamente un cero) el total de minutos vendedor (que debe ser inferior a 19.200) y en la celda D12 (momentneamente un cero) el total de minutos de instalador (que debe ser inferior a 14.400). El problema que se tiene en pantalla es el tpico que resuelve la herramienta Solver del Excel: Se tiene un objetivo: Maximizar la celda E4 Interesa calcular las celdas D1 y D2 Se tiene, en principio, dos restricciones: Prof. Carlos Labb O. 12-10-10

CURSO DE EXCEL AVANZADO C12 debe ser igual o inferior a 19200 D12 debe ser igual o inferior a 14400

29

Ahora bien, si slo se dan esas dos restricciones Solver se complica mucho ms ya que considera tambin como solucin nmeros reales negativos y positivos hasta los valores indicados, por lo cual los clculos son muchos ms y dara muchas soluciones errneas al problema, por lo cual es conveniente darle ms restricciones y estas podran ser: Celda D1 deben ser nmeros enteros positivos (nmero de refrigeradores) Celda D2 deben ser nmeros enteros positivos (nmero de acondicionadores) Ahora se entra a usar el Solver: Entrar a Men: Herramientas Solver Aparece el siguiente cuadro de dilogo:

Celda objetivo: Seleccionar o escribir: $E$4 Valor de la celda objetivo: Mximo Cambiando las celdas: Seleccionar o escribir: $D$1:$D$2 Se va a introducir las restricciones: Hacer clic en Agregar

Aparece el siguiente cuadro de dilogo:



30

Referencia de la celda: Seleccionar o escribir: $C$12 Seleccionar o escribir: = Restriccin: 0 Clic en Agregar Referencia de la celda: Seleccionar o escribir: $D$1 Seleccionar: Int Restriccin: Integer Clic en Agregar Referencia de la celda: Seleccionar o escribir: $D$2 Seleccionar o escribir: Int Restriccin: Integer

Como no hay ms restricciones que poner, se debe hacer Clic en Aceptar Nuestro cuadro de dilogo de Solver queda de la siguiente forma: Prof. Carlos Labb O. 12-10-10


31

Ahora se debe probar si este problema tiene solucin, y para eso se da un clic en Resolver , entonces aparece el siguiente cuadro de dilogo indicando que el Solver encontr una solucin:

En este caso marcar: Utilizar solucin de Solver y dar un clic en Aceptar entonces aparece el siguiente cuadro de dilogo:

Ahora hay que darle un nombre al escenario, en este caso escribir Optimizacin y dar un clic en Aceptar. Si todo se ha hecho correctamente deben aparecer los resultados siguientes en las celdas de resultados: Prof. Carlos Labb O. 12-10-10

CURSO DE EXCEL AVANZADO Celda D1 correspondiente a Frigorficos: 1.440 Celda D2 correspondiente a Acondicionadores: 600 Celda E4 correspondiente a Objetivo: 99.000.000 Tal como se muestra a continuacin:

32

Debe grabarse el escenario con un nombre adecuado, por si quiere realizar cambios u otras pruebas, se recomienda guardarlo. Problema N 2 Problema de PROQUIM (Productos Qumicos) La industria PROQUIM S.A., fabrica dos tipos de productos qumicos, E y F, cuya utilidad neta es de $ 5.000 y $ 4.000 por tonelada respectivamente. Ambos pasan por operaciones de 2 departamentos de produccin, que tienen una disponibilidad limitada. El departamento A dispone de 150 horas mensuales; cada tonelada de E utiliza 10 horas de este departamento, y cada tonelada de F, 15 horas. El departamento B tiene una disponibilidad de 160 horas mensuales. Cada tonelada de E precisa de 20 horas, y cada tonelada de F precisa de 10 horas para su produccin. Prof. Carlos Labb O. 12-10-10


33

Para la produccin global de E y F, se debern utilizar al menos 135 horas de verificacin en el prximo mes; el producto E precisa de 30 horas y F de 10 horas por tonelada de verificacin . La gerencia ha decretado que es necesario producir al menos una tonelada de F por cada 3 de E . Un cliente ha solicitado 5 toneladas, cualquiera sea su tipo, de E o F. Por otro lado, es evidente que no pueden producirse cantidades negativas de E ni de F. Se trata de decidir, para el mes prximo, las cantidades a producir de cada uno de los productos para maximizar la utilidad global. El Modelo: Variables controlables: E: toneladas de tipo E a producir; F: toneladas de tipo F a producir. Modelo: Max 5000 E + 4000 F Restricciones: Escribimos ahora las restricciones o requerimientos 10 E + 15 F 150 20 E + 10 F 160 30 E + 10 F 135 E-3F 0 E +F 5 E 0, F 0 {horas del departamento A} {horas del departamento B} {horas de verificacin} {al menos una de F cada 3 E significa E 3 F} {al menos 5 toneladas} {no negatividad} {Funcin objetivo: maximizar la utilidad global}

Antes de introducir este modelo en la planilla, conviene preparar una tabla con los coeficientes de las variables:



34

Productos Utilidad Marginal Restricciones Departamento A: Departamento B: Verificacin: Al menos un E cada 3F: Al menos 5:

E F 5000 4000 10 20 30 1 1 15 10 10 -3 1 150 160 135 0 5

Las restricciones de no negatividad no se han incluido en la tabla, pero s se tendrn muy en cuenta al poner restricciones en la planilla. De otro modo, se podra llegar a obtener soluciones absurdas. Introduccin de datos Se debe abrir una nueva planilla de clculo. Antes de introducir los datos en la planilla, conviene aumentar el ancho de la columna A para que aparezcan completos los rtulos de esta columna. Las dems columnas pueden quedar sin alterar. Se comenzar suponiendo que no se produce nada de E ni de F, por lo que se escribe 0 (cero) en las celdas B5 y C5. Se llenan los siguientes parmetros de Solver: Celda objetivo: $K$11 Marcar en Mnimo Cambiando las celdas: $B$9:$D$10 Restricciones: $B$11:$D$11=$H$5:$J$5 $B$9:$D$10 = Integer $B$9:$D$10 >= 0 $E$9:$E$10

39203952 manual de excel avanzado[1]

Documents