1. MANUAL DE EXCELAVANZADOMg. JONTXU PARDO RODRIGUEZ-GACHS

2. 1MANUAL DE EXCEL AVANZADO3 Introduccin 3 Grficos Especiales3 Grficos de Lnea vs. Grficos de Dispersin XY3 Grficos de Dispersin XY5 Esquemas.9 Descripcin de Esquemas9 Creacin de un Esquema11 Funciones financieras.14 Introduccin14 Funciones Financieras 15 NPER15 PAGOINT 15 PAGOPRIN16 VA16 VNA 17 VF17 Funciones para calcular la tasa de rendimiento18 Introduccin18 TASA18 TIR 19 TIRM19 Funciones para calcular depreciaciones20 Introduccin20 DB20 DDB 21 DVS 21 SLN 22 SYD 22 Solver23 Descripcin 23 Optimizacin23 Herramienta Solver24 Instalacin del Solver24 Ejercicios25 Problema N 1 25 Problema N 2 30 Informe de Respuestas 38 Informe de sensibilidad 40 Informe de Lmites41 Conclusiones42 Opciones de Solver43 3. 2Opciones para modelos no-lineales 45Introduccin a Estadstica Aplicada a travs de Excel 47Distribuciones de Frecuencia e Histogramas47Finalidad de las distribuciones de frecuencias. 48Interpretacin de las distribuciones de frecuencias.48Formalizacin de las distribuciones de frecuencia 49Distribuciones de frecuencias con la funcin FRECUENCIA del Excel 50Introduccin50Sintaxis50Observaciones 50Ejemplo N 151Ejemplo N 2: 53Distribuciones de frecuencia e histogramas con herramientas de anlisis 62Herramientas de anlisis estadstico62Funciones de hojas de clculo relacionadas63Acceder a las herramientas de anlisis de datos 63Varianza de dos factores con varias muestras por grupo65Varianza de dos factores con una sola muestra por grupo 65Correlacin 65Covarianza66Estadstica descriptiva 66Suavizacin Exponencial 66Prueba t para varianza de dos muestras. 66Anlisis de Fourier 66Histograma67Media mvil 67Generacin de nmeros aleatorios. 67Jerarqua y percentil 67Regresin 67Muestreo68Prueba t68Prueba t para dos muestras suponiendo varianzas iguales 68Prueba t para dos muestras suponiendo varianzas desiguales68Prueba t para medias de dos muestras emparejadas68Prueba z68Histograma69Introduccin69Descripcin 69Distribuciones de frecuencia e histogramas con tablas dinmicas.72Ejercicio N 1: 73Ejercicio N 2: 76Ejercicio N 379Ejercicio N 493GLOSARIO DE TERMINOS98 4. 3MANUAL DE EXCEL AVANZADOIntroduccin Como 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, engeneral estn dirigidas a la gestin. Estos apuntes se han hecho pensando enusuarios con vasta experiencia en Excel, que ya han superado el segundo gradoen manejo de hoja de clculos.Se supone que quien estudia en estos apunte ya sabe como construir unahoja de clculo simple, como escribir frmulas y que pasa cuando se copian.Como se imprime una hoja de clculo y como se graba. Como se imprime unahoja de clculo y como se graba. Saben como definir, usar e interpretar tablasdinmicas. Como crear, definir e interpretar escenarios. En estos apuntes se seleccionaron las tcnicas que se estima necesita uningeniero o un ejecutivo para la gestin, es decir, estos apuntes profundizan entodos aquellos comandos u opciones que son poco usados, no porque no seantiles sino porque casi nadie los conoce, pero que se estima son necesarios parael ejecutivo moderno en la toma de decisiones o en el control. Este manual trata las siguientes materias: Grficos especiales, Esquemas, Funciones financieras, Solver, Estadsticas aplicadas a travs de Excel.Todos estos puntos son desarrollados en forma Terica y prctica y conejemplos que les puedan servir a los estudiantes de Ingeniera, a los ingenieros ya los ejecutivos en la gestin. Grficos EspecialesGrficos de Lnea vs. Grficos de Dispersin XYUna PYME fabrica solamente tres tipos de muebles: Escritorios, Sillas y Estantes.Mediante un gran esfuerzo reinvirtiendo las utilidades y capacitando a su personal 5. 4ha logrado ir duplicando la produccin. La produccin en los ltimos aos semuestra en la siguiente tabla: PRODUCCION DE UNA PYME AOS ESCRITORIOSSILLAS ESTANTES1980268323 1941990536646 3881996804969 5822000 1072 1292 776Si esta tabla se grafica mediante un grfico de Lneas el resultado se muestra enla pgina siguiente:PRODUCCION DE UNA PYME140012001000PRODUCTOS800 ESCRITORIOSSILLAS600 ESTANTES4002000 1980 199019962000 AOSComo se puede observar este grfico est con graves errores, ya que el aumentode la produccin es el mismo para todos los aos indicados, sin embargo, ladiferencia entre los aos no es la misma, por lo tanto debera salir una curvaexponencial. Esto se soluciona usando grficos tipo de Dispersin XY. Basta concambiar el tipo de grfico para que aparezcan las curvas correctas, como semuestra en la figura siguiente: 6. 5PRODUCCION DE UNA PYME 1400 1200 1000 PRODUCTOS 800 ESCRITORIOS SILLAS 600 ESTANTES 400 200 0 1975 1980 1985 1990 19952000 2005 AOSLos grficos Dispersin XY son los indicados cuando la variable del eje de las Xno representa incrementos constantes.Grficos de Dispersin XYUsando los grficos de dispersin se puede tener grficos como el siguiente: 7. 6Esta roseta se llama figura de Lissajous, en honor del fsico del siglo XIX que lasestudio por primera vez. Estas figuras aparecen al superponer movimientososcilatorios. Lissajous usaba un aparato muy complejo, con dos diapasones yespejitos que reflejaban la luz. Ahora se pueden obtener las mismas figuras en elcomputador usando grficos de Dispersin XY.Para construir este tipo de grficos se usa una tabla como la figura siguiente: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:D100Para 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. 8. 7 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. Se da un clic en Finalizar.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: 9. 8 Dentro de rea se da la opcin Ninguna. Hacemos un clic en Aceptar.La figura queda como se muestra a continuacin:Las frmulas de la tabla fueron escritas de forma tal que variando el contenido deG1 y/o G2, las curvas pueden variar de inmediato, por ejemplo si coloco 5 en G1 yen G2, aparece la curva que se muestra en la pgina siguiente: 10. 9En cambio la Figura de Lissajous, se obtiene colocando un 5,1 en G1 y un 5 enG2, al efectuar este cambio queda esta figura:Lo importante de este captulo es que mediante el estudiante de Excel comprendaque mediante el Excel se pueden simular los resultados de efectos fsicos decualquier orden: por ejemplo: Las curvas resultantes del sonidos de dosdiapasones, cadas de cuerpos, clculo de trayectorias espaciales, situacioneseconmicas, etc Esquemas.Descripcin de EsquemasMuchas hojas de clculos estn diseadas en jerarquas de celdas. Aplicar unesquema a una hoja consiste en asociar una relacin de subordinacin entre lasdiferentes celdas. 11. 10Para explicar los esquemas podemos apoyarnos en la hoja de la figurasiguiente, que muestra el desglose de la produccin de un ao en meses y entrimestres. Cada trimestre suma los valores de los meses que componen eltrimestre, y se entregan como totales las sumas de los trimestres:Cada trimestre es un esquema, por lo tanto en una figura como la siguientedebe haber cuatro esquemas, cada uno con sus totales. La lnea horizontal que seobserva en la figura siguiente, indica que hayan esquema que abarca el primertrimestre 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 delao. Los signos ms que se muestran en la parte superior de los trimestresindican que se ocult la parte de detalle y slo se muestran los totales de cadatrimestre. 12. 11Los nmeros 1 y 2 que se muestran en la parte superior, indican que un nivelde esquemas y datos de una hoja de clculo.A su vez esta hoja se puede volver a esquematizar, dejando como unesquema los totales trimestrales, y al ocultar stos, queda como se muestra en lafigura siguiente:Los nmeros 1, 2 y 3 que se muestran en la parte superior izquierda indicanque hay un nivel de datos (3), un primer nivel de esquemas que resume esosdatos (2) y un segundo nivel de esquemas que resume el nivel anterior (3). Al igualque en el caso anterior el signo + indica que es un resumen de datosesquematizados.Creacin de un EsquemaAntes de esquematizar es importante asegurarse de que estn introducidostodos 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 incluirmodelos con el comando Configurar o con la barra de herramientas.N este ejemplo se va a esquematizar unos datos de ventas que necesita lagerencia para la gestin; se trata de las ventas anuales mes por mes con lasiguiente informacin:Artculos producidos,Precio unitario,Valor total de produccin,Precio de venta,Comisin al vendedor, 13. 12Precio de venta neto,IVAPrecio de venta al pblico (PVP).El primer paso es construir la hoja de clculo: colocarle un ttulo: por ejemplo(Ventas ao 2006), y a partir de la FILA 1, crear las siguientes ttulos de filas:Descripcin,Enero,Febrero,MarzoTrimestre 1,Abril,Mayo,Junio,Trimestre 2,Julio,Agosto,Septiembre,Trimestre 3,Octubre,Noviembre,Diciembre;Trimestre 4,Total Ao.en la cual puede resumir y dejar slo los subtotales, o resumir ms y dejar slo eltotal general. Como tambin puede ampliarlos hasta llegar a los datos originales. 14. 13 15. 14Funciones financieras.IntroduccinAlgunas de las funciones financieras tales como Pago se vieron en Manualesanteriores Las funciones financieras NPER, PAGO, PAGOINT, PAGOPRIN, VA,VNA Y VF tienen en comn los argumentos:tasa : Porcentaje de intersnper : Plazo de la inversin o prstamopago : Dividendo o cuota mensualva : Valor actual que se percibe o desembolsa al principio de la operacin, tambin se denomina Capital o monto del prstamo.vf : Valor futuro que se percibe o desembolsa al final de la operacin. Si se omite se supone que el valor futuro es 0.Tipo : 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 argumentoaparece 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 perodomensual.A fin de simplificar los clculos la tasa mensual se calcula dividiendo por 12 la tasaanual. Aunque esto est incorrecto la diferencia es mnima con la frmula declculo 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 aoPor ejemplo para transformar una tasa anual de 15% en una tasa mensual, lafrmula a aplicar es:=POTENCIA((1+0.15);(1/12))-1 16. 15Lo que nos da por resultado: 1.01, en cambio, si dividimos 15/12 nos da: 1.25, porlo cual, para las siguientes frmulas para reducir de una tasa anual a una tasamensual, para simplificar los clculos se dividir la tasa anual por 12, ya que ladiferencia es mnima para cantidades pequeas.Funciones FinancierasNPERCalcula el nmero de perodos necesarios para amortizar un prstamo, dadas lascantidades a para, la tasa de inters, el valor actual y el valor futuro (si hay). Suformato es: NPER (tasa; pago; va; vf ; tipo )El argumento pago debe ser igual o superior al producto de los argumentos tasapor 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.PAGOINTCalcula la cantidad a pagar por intereses sobre un prstamo en un perododeterminado de tiempo con unos pagos y un tipo de inters constantes. Suformato 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 interesesen el primer mes correspondiente al pago de un prstamo de $ 10.000.000, aveinte 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 17. 16PAGOPRINCalcula la cantidad amortizada de un prstamo en un perodo determinado detiempo, con unos pagos y un tipo de inters constante. La suma de las funcionesPAGOINT y PAGOPRIN devuelve la cantidad total a pagar determinada por lafuncin pago. Su formato es: PAGOPRIN (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 quecorresponde al pago de un prstamo de $ 10.000.000 a 20 aos y a una tasa deinters del 11% anual.=PAGOPRIN(11%/12;1;20*12;10000000)Excel entrega como resultado: $ -11.552,17VADetermina el valor actual de una inversin en base a una serie de pagosperidicos iguales o el de un pago global. Si el valor devuelto por la funcin essuperior 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 4.000, si se espera recibir 1.000, durantelos prximos 7 aos. Como tasa se considera un inters bancario de 10% anual:=VA(10%;7;1000)Excel devuelve el valor 4.868,42. Esto significa que deberamos estar dispuestosa invertir ahora 4.868,42 para recibir 7.000 durante los prximos 7 aos. Al ser lainversin inicial de 4.000, sta es una buena inversin.Nota:Si se omite un argumento en la mitad de la frmula para usar e argumento vf, sedebe escribir un punto y coma por el argumento omitido.Ejemplo: 18. 17Supongamos que en lugar de los 1.000 anuales, nos proponen pagarnos los7.000 al final de los 7 aos Es bueno el negocio? La frmula a utilizar es:=VA(10%;7;;7000)Excel devuelve el valor 3.592,11. Esto quiere decir que deberamos desembolsarahora 3.592,11 para recibir 7.000 al cabo de 7 aos. Al ser la inversin inicial de4.000, esta no es una buena inversin.VNA Calcula el valor neto actual de una serie de flujos de caja descontados a un tipode inters. VNA es otra funcin para determinar si una inversin es buena. Lainversin se considera rentable cuando VNA da un nmero positivo. Su sintaxises: =VNA(tasa;valor1;valor2;)La funcin VNA se diferencia de la funcin VA, en que mientras VA considerasiempre la cantidad constante, VNA permite incluir cantidades variables tantopositivas como negativas.Ejemplo:Supongamos que se desea saber si es rentable invertir 250.000, si esperan unaprdida de 60.000 el primer ao, con ganancias en los siguientes aos de100.000, 150.000 y 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: 3.663,43 Al ser un nmero positivo, indica que la inversin esbuena.VFDetermina el valor futuro de una inversin consistente en una serie peridica depagos 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 a30 aos, si se desembolsan todos los meses $ 10.000 a un inters del 8%. Lafrmula es la siguiente: 19. 18=VF(8%/12;30*12;-10000;;1)Excel devuelve la cantidad de $ 15.002.524,75Ejemplo:Supongamos que se posee un capital acumulado de $100.000, la frmula tendrel siguiente aspecto:=VF(8%/12;30*12;-10000;-100000;1)Excel devuelve $ 16.096.524,75Funciones para calcular la tasa de rendimientoIntroduccinLas funciones TASA, TIR Y TIRM calculan las tasas de rendimiento. Utilizan unnuevo argumento: Estimacin: valor inicial para empezar los clculos. Por defecto toma el valor 10%.TASATasa determina el tipo de inters de una inversin que genera unos ingresos ogastos peridicos iguales. Su sintaxis es: TASA(nper ; pago; va; vf ; tipo ; estimacin )Excel calcula la tasa mediante un proceso iterativo hasta alcanzar el valordeseado o haya efectuado 20 iteraciones. Si tasa devuelve #NUM!, quiere decirque necesita ms iteraciones para llegar al resultado final. En este caso en elargumento 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 multiplicapor 12. 20. 19TIRLa tasa interna de rendimiento, TIR, es el tipo de inters que provoca que el valorneto actual de una inversin sea cero, VNA=0. En otras palabras, es el tipo deinters que provoca que el valor actual de todas las entradas sea igual a loscostos reembolsados en la inversin. Una inversin ser rentable cuando el Tirsea 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 argumentoestimacin al igual que suceda en Tasa.Ejemplo:Supongamos que en rango D10:D15 tenemos una serie numrica que representapor una parte la cantidad a invertir, 100.000 (se debe especificar como nmeronegativo y por otra, los beneficios que se esperan conseguir en los prximos aos:25.000, 33.000, 40.000, 50.000 y 55.000. El tipo de inters a superar es del10%. La frmula a aplicar es:=TIR(D10:D15)Excel devuelve el valor 25%, que es superior a tasa obstculo del 10%, por lo cualla inversin es altamente rentable.TIRMLa tasa interna de rendimiento modificado, TIRM, es similar a la funcin TIR, conla diferencia de que TIRM tiene en cuenta el costo del dinero prestado y el hechode 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: 21. 20Supongamos que en rango D10:D15 tenemos una serie numrica que representapor una parte la cantidad a invertir, 100.000 (se debe especificar como nmeronegativo y por otra, los beneficios que se esperan conseguir en los prximos aos:25.000, 33.000, 40.000, 50.000 y 55.000. El tipo de inters a superar es del10%. Adems debe considerarse una tasa de financiacin del 10% y una tasa dereinversin 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 depreciacionesIntroduccinLas depreciaciones son calculadas por la funciones: DB, DDB, DVS, SLN, y SYD,que utilizan, entre otros, los siguientes argumentos: 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, yasea, mensual, trimestral, semestral, anual, etc.DBCalcula 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 10.000 con unavida til de cinco aos y un valor residual de 200 La depreciacin al cuarto ao secalcula:=DB(10000;200;5;4)Excel devuelve 518 22. 21DDBCalcula la depreciacin de un activo durante un determinado perodo por elmtodo de depreciacin de doble disminucin de saldo, que considera una tasa dedepreciacin 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 unamquina que cuesta 10.000, con una vida til de 5 aos y un valor residual de200. La frmula a aplicar es:=DDB(10000;200;5*12;1)Excel devuelve 333.33DVSCalcula la depreciacin de un activo para un perodo parcial o completo por elmtodo de doble disminucin del saldo u otro factor decreciente acelerado. Susintaxis 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 devida, de una mquina que cuesta 10.000 con una vida til de 5 aos y un valorresidual de 200. La frmula a aplicar es la siguiente:=DVS(10000;200;5;0;1)Excel devuelve 400. 23. 22SLNCalcula la depreciacin de un activo para un perodo determinado suponiendo quela 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, usandodepreciaciones iguales, de una mquina que cuesta 10.000, y cuyo valor residuales de 200 al cabo de 5 aos. La frmula es:=SLN(10000;200;5)Excel devuelve 1.960SYDCalcula la depreciacin de un activo para un perodo determinado utilizando unmtodo regresivo variable, al igual que el mtodo decreciente doble, llamadodepreciacin 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 deuna mquina que cuesta 10.000 y cuyo valor residual es de 200 al cabo de 5aos, utilizando el mtodo de la suma de los dgitos del ao. La frmula a aplicares:=SYD(10000;200;5;2)Excel devuelve 213,33 24. 23 SolverDescripcinEl Solver es una herramienta del Excel que permite resolver problemas deoptimizacin, 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 comoherramienta de optimizacin.OptimizacinUn problema de optimizacin consiste en encontrar aquellos valores de ciertasvariables que optimizan (es decir, hacen mxima o mnima, segn el caso), unafuncin de estas variables. A las variables las llamaremos variables controlables ovariables de decisin.Matemticamente, significa encontrar los valores de x1, x2,..., xn, tales que hacenmxima (o mnima) a la funcin f (x1, x2,..., xn).El mtodo ms conocido para encontrar el ptimo de una funcin es a travs delanlisis de sus derivadas. Este mtodo tiene dos limitaciones: no siempre lafuncin es derivable, y, adems, no siempre el ptimo nos da una solucin quetenga sentido en la prctica.Debido a la primera limitacin, surgieron los mtodos numricos, que parten deuna solucin inicial, y mediante algn algoritmo iterativo, mejoran sucesivamentela solucin. Tal como se describe el diagrama siguiente: sSolucin EsFin Inicial ptima?no NuevaSolucinDebido a la segunda limitacin, surgieron los mtodos de optimizacin restringida.El nombre se debe a que podemos ponerle restricciones a las variables, de modoque cumplan una o ms condiciones.La restriccin ms comn que se da en la prctica es que las variables deben serno negativas. No tiene ningn sentido una "solucin" que implique producir 25. 24cantidades negativas, o sembrar un nmero negativo de hectreas, o llevar unnmero negativo de paquetes.Pero, adems, surgen naturalmente otras restricciones en el mundo real, debido alimitaciones de horas de trabajo, capital, tiempo, insumos, o a que, quizsdeseamos imponer ciertos mnimos o mximos de calidad, riesgo, etc.. Estasrestricciones pueden ser funciones de las variables controlables.Podramos resumir diciendo que en un problema de optimizacin restringidabuscamos 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 hacenmxima (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 deProgramacin Lineal, donde tanto la funcin objetivo como las restricciones sonfunciones lineales, las variables deben ser no negativas, y pueden tomar cualquiervalor real, no necesariamente entero.Herramienta SolverSolver es una herramienta para resolver y optimizar ecuaciones mediante el usode mtodos numricos.Con Solver, se puede buscar el valor ptimo para una celda, denominada celdaobjetivo, en donde se escribe la frmula de la funcin objetivo f (x1, x2, ..., xn).Solver cambia los valores de un grupo de celdas, denominadas celdascambiantes, y que estn relacionadas, directa o indirectamente, con la frmula dela celda objetivo. En estas celdas se encuentran los valores de las variablescontrolables x1, x2, ..., xn.Puede agregar restricciones a Solver, escribiendo una frmula g j (x1, x2, ..., xn) enuna celda, y especificando que la celda deber ser mayor o igual, igual, o menor oigual que otra celda que contiene la constante cj.Tambin puede especificar que los valores sean enteros, para evitar darresultados absurdos de algunos problemas, tales como que se necesitan 3,47empleados.Solver ajustar los valores de las celdas cambiantes, para generar el resultadoespecificado en la frmula de la celda objetivo.Instalacin del SolverLa herramienta Solver no se instala por defecto: 26. 25 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.EjerciciosProblema N 1En una tienda de electrodomsticos se quiere introducir al mercado unosfrigorficos y acondicionadores pequeos para oficinas a precios muy bajos. Losfrigorficos a 500 y los acondicionadores a 450.Cada venta de un frigorfico supone 10 minutos de tiempo de un vendedor y 5minutos del tiempo de una instalador. La venta de un acondicionador requiere 8minutos 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 20das 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 losingresos (celda E4).Abra un nuevo libro de Excel y en la Hoja1 escriba lo siguiente:Escriba en la celda E4 := 500 * d1 + 450 * d2Al dar enter aparece en la celda E4 un cero, ya que las celdas d1 y d2 no tienenvalores.Para la resolucin del problema tenemos una gran restriccin: se dispone de unpersonal y tiempo limitado.De momento se va a calcular el tiempo en minutos de los vendedores einstaladores: Escriba en la celda D15: =4*4*20 Escriba en la celda D16: =3*4*20 Celda E15: =D15*60 Celda E16: =D16*60 27. 26En otras palabras, se dispone de 320 horas (19.200 minutos) de trabajo por partede los vendedores y 240 horas (14.400 minutos) de trabajo por parte de losinstaladores.Ahora se trata de calcular ahora el tiempo (en minutos) de los vendedores einstaladores para un nmero indeterminado de frigorficos (celda D1) yacondicionadores (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) eltotal de minutos vendedor (que debe ser inferior a 19.200) y en la celdaD12 (momentneamente un cero) el total de minutos de instalador (quedebe ser inferior a 14.400).El problema que se tiene en pantalla es el tpico que resuelve la herramientaSolver del Excel:Se tiene un objetivo: Maximizar la celda E4Interesa calcular las celdas D1 y D2Se tiene, en principio, dos restricciones: C12 debe ser igual o inferior a 19200 D12 debe ser igual o inferior a 14400Ahora bien, si slo se dan esas dos restricciones Solver se complica mucho msya que considera tambin como solucin nmeros reales negativos y positivoshasta los valores indicados, por lo cual los clculos son muchos ms y daramuchas soluciones errneas al problema, por lo cual es conveniente darle msrestricciones 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 28. 27Aparece 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 AgregarAparece el siguiente cuadro de dilogo: 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: IntegerComo no hay ms restricciones que poner, se debe hacer Clic en AceptarNuestro cuadro de dilogo de Solver queda de la siguiente forma:Ahora se debe probar si este problema tiene solucin, y para eso se da un clic enResolver , entonces aparece el siguiente cuadro de dilogo indicando que elSolver encontr una solucin: 30. 29En este caso marcar: Utilizar solucin de Solver y dar un clic en Aceptarentonces aparece el siguiente cuadro de dilogo:Ahora hay que darle un nombre al escenario, en este caso escribir Optimizaciny dar un clic en Aceptar. Si todo se ha hecho correctamente deben aparecer losresultados siguientes en las celdas de resultados: Celda D1 correspondiente a Frigorficos: 1.440 Celda D2 correspondiente a Acondicionadores: 600 Celda E4 correspondiente a Objetivo: 99.000.000Tal como se muestra a continuacin: 31. 30Debe grabarse el escenario con un nombre adecuado, por si quiere realizarcambios u otras pruebas, se recomienda guardarlo.Problema N 2Problema 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 tienenuna disponibilidad limitada. El departamento A dispone de 150 horas mensuales; cada tonelada de E utiliza10 horas de este departamento, y cada tonelada de F, 15 horas. El departamento B tiene una disponibilidad de 160 horas mensuales. Cadatonelada de E precisa de 20 horas, y cada tonelada de F precisa de 10 horaspara su produccin. Para la produccin global de E y F, se debern utilizar al menos 135 horas deverificacin en el prximo mes; el producto E precisa de 30 horas y F de 10horas por tonelada de verificacin . La gerencia ha decretado que es necesario producir al menos una tonelada deF por cada 3 de E . 32. 31 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 nide F.Se trata de decidir, para el mes prximo, las cantidades a producir de cada uno delos 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 {Funcin objetivo: maximizar la utilidad global}Restricciones:Escribimos ahora las restricciones o requerimientos10 E + 15 F 150 {horas del departamento A}20 E + 10 F 160 {horas del departamento B}30 E + 10 F 135 {horas de verificacin}E-3F0{al menos una de F cada 3 E significa E 3 F}E +F5{al menos 5 toneladas}E 0, F 0{no negatividad}Antes de introducir este modelo en la hoja de clculo, conviene preparar una tablacon los coeficientes de las variables: 33. 32ProductosEF Utilidad Marginal5000 4000Restricciones Departamento A: 10 15 150 Departamento B: 20 10 160 Verificacin: 30 10 135 Al menos un E cada 3F: 1 -3 0 Al menos 5:11 5Las restricciones de no negatividad no se han incluido en la tabla, pero s setendrn muy en cuenta al poner restricciones en la hoja de clculo. De otro modo,se podra llegar a obtener soluciones absurdas.Introduccin de datosSe debe abrir una nueva hoja de clculo de clculo. Antes de introducir los datosen la hoja de clculo, conviene aumentar el ancho de la columna A para queaparezcan completos los rtulos de esta columna. Las dems columnas puedenquedar sin alterar.Se comenzar suponiendo que no se produce nada de E ni de F, por lo que seescribe 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