19
4solver 4.3) Programacion lineal con SOLVER de Excel: La función Solver puede estar en Herramientas (o a instalar desde el CD del Office, junto con las muestras.xls) Esta función es en sí muy pequeña, y se utiliza copiándola a los casos de Simplex como en las sig. muestras que trae Excel SOLVER se usa para resolver problemas diversos (además de programación Simplex) como este: CITI $ 30 FRANCES $ 20000 ? capital 12500 375000 interés 5.00% 3% plazo 10 20 >> 20361 677292 697653 <<<<< celda objetivo Formulas >>>>> citi frances Ej. cómo optimizar $50.000 de inversion en el Citi o en Frances ? Haga la fórmula capital x interes x tiempo en B6 y C6 y la suma de todos en d6 Luego Herramientas SOLVER Solver / parametros de Solver / C.Objetivo D6 Maximizar CAMBIAN las celDas.... B3 y C3 PERO TAMBIEN PUEDO CAMBIAR LOS INTERESES O BIEN LOS PLAZOS.....!!!!! Restriccion / agregar restriccion: que citi <= 50.000 menos Frances y que citi > 0 Frances >0 Aceptar / resolver (y me dira que invierta solo en el Frances....) Es posible introducir valores, modificarlos, agregar o quitar variables, etc Luego se va a Herramientas/ Solver / y en opciones se adaptan los parametros deseados. Luego ejecutar y automaticamente maximiza o minimiza. ---- 0 ---- EJEMPLOS DE APLICACION DE SOLVER PARA PROGRAMACION LINEAL METODO SIMPLEX: (estos ejemplos estan en archivos en el Office) Ejemplo1: Problema de la mezcla de productos combinado con la disminución del margen de ganancias Su compañía fabrica TVs, estéreos y parlantes usando piezas en común del inventario, tales como generadores de electricidad y altavoces. Debido a que las piezas son limitadas, se debe determinar la mezcla óptima de productos a fabricar. Pero la ganancia por unidad disminuye al aumentar el volumen fabricado puesto que se necesitan más incentivos de precio para producir un incremento de la demanda. Televisores EstéreosParlantes Page 1

4solver Manual

Embed Size (px)

Citation preview

Page 1: 4solver Manual

4solver

����������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������

4.3) Programacion lineal con SOLVER de Excel: La función Solver puede estar en Herramientas (o a instalar desde el CD del Office, junto con las muestras.xls) Esta función es en sí muy pequeña, y se utiliza copiándola a los casos de Simplex como en las sig. muestras que trae Excel

SOLVER se usa para resolver problemas diversos (además de programación Simplex) como este:

CITI $ 30 FRANCES $ 20000 ?

capital 12500 375000interés 5.00% 3%plazo 10 20

>> 20361 677292 697653 <<<<< celda objetivoFormulas >>>>>

citi frances

Ej. cómo optimizar $50.000 de inversion en el Citi o en Frances ?

Haga la fórmula capital x interes x tiempo en B6 y C6 y la suma de todos en d6Luego Herramientas SOLVERSolver / parametros de Solver / C.Objetivo D6MaximizarCAMBIAN las celDas.... B3 y C3 PERO TAMBIEN PUEDO CAMBIAR LOS INTERESES O BIEN LOS PLAZOS.....!!!!!Restriccion / agregar restriccion:que citi <= 50.000 menos Francesy que citi > 0Frances >0Aceptar / resolver (y me dira que invierta solo en el Frances....)

Es posible introducir valores, modificarlos, agregar o quitar variables, etcLuego se va a Herramientas/ Solver / y en opciones se adaptan los parametrosdeseados. Luego ejecutar y automaticamente maximiza o minimiza.

---- 0 ----

EJEMPLOS DE APLICACION DE SOLVER PARA PROGRAMACION LINEAL METODO SIMPLEX:(estos ejemplos estan en archivos en el Office)

Ejemplo1: Problema de la mezcla de productos combinado con la disminución del margen de ganancias

Su compañía fabrica TVs, estéreos y parlantes usando piezas en común del inventario, tales comogeneradores de electricidad y altavoces. Debido a que las piezas son limitadas, se debedeterminar la mezcla óptima de productos a fabricar. Pero la ganancia por unidad disminuye al aumentar el volumen fabricado puesto que se necesitan más incentivos de precio para producirun incremento de la demanda.

Televisores EstéreosParlantes

Page 1

Page 2: 4solver Manual

4solver

�������������������������������������������������������������������������������������������������������������������

������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������

���������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������

Cantidad a fabricar-> 160 200 80

Nombre denventariodad usada

Bastidor 450 360 1 1 0

Tubo de i 250 160 1 0 0 Factor

Altavoz 800 800 2 2 1 exponencial

Generador 450 360 1 1 0 de disminución

Piezas el 600 600 2 1 1 0.9

Ganancias:

Por producto $7,220 $5,887 $1,811

Total $14,917

Ejemplo 2: Problema de transporte.Minimizar el costo de envío de mercancías desde las plantas de producción hasta los almacenescercanos a los centros de demanda regionales, sin exceder las existencias disponiblesen cada planta y satisfaciendo la demanda de cada almacén regional.

Cantidad a enviar de la planta "x" al almacén "y' (en la intersección):Plantas Total Sevilla MadridBarcelonaSantander Bilbao

Galicia 5 1 1 1 1 1La Rioja 5 1 1 1 1 1Murcia 5 1 1 1 1 1

--- --- --- --- ---TOTAL: 3 3 3 3 3

das por almacén--> 180 80 200 160 220Plantas: xistencia Costos de envío de la planta "x" al almacén "y" (en la intersección):

Galicia 310 10 8 6 5 4La Rioja 260 6 5 4 3 6Murcia 280 3 4 5 5 9

Envío: $83 $19 $17 $15 $13 $19

Ejemplo 3: Planificación del horario para el personal de un parque de diversiones.Cada empleado trabaja cinco días consecutivos, seguidos por dos días de descanso. Se trata de averiguarel horario adecuado de manera que el parque cuente con personal suficiente, reduciendo los costos salariales.

Horarios \ DíasEmpleados Dom Lun Mar Mié Jue Vie Sáb

A Domingo, lunes 4 0 0 1 1 1 1 1B Lunes, martes 4 1 0 0 1 1 1 1C Martes, miérco 4 1 1 0 0 1 1 1D Miércoles, jue 6 1 1 1 0 0 1 1E Jueves, vierne 6 1 1 1 1 0 0 1

Page 2

Page 3: 4solver Manual

4solver

������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������

���������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������

������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������

F Viernes, sábad 4 1 1 1 1 1 0 0G Sábado, doming 4 0 1 1 1 1 1 0

tales por horario: 32 #¡VALOR! #¡VALOR! #¡VALOR! #¡VALOR! #¡VALOR! #¡VALOR! #¡VALOR!

Demanda total: 22 17 13 14 15 18 24

Sueldo/empleado/d $40Sueldo semanal: $1,280

Ejemplo 4: Administración del capital de trabajo.Determinar cómo invertir los excedentes de efectivo en certificados de depósito (CDs) a plazo

se conservan fondos suficientes para cubrir los gastos (más un margen de seguridad).

Tasa Plazo Meses de compra de CDs:CDs a 1 m 1.0% 1 1, 2, 3, 4, 5 y 6 InterésCDs a 3 m 4.0% 3 1 y 4 obtenido:

CDs a 6 m 9.0% 6 1 Total $0

Mes: Mes 1 Mes 2 Mes 3 Mes 4 Mes 5 Mes 6 FinEfectivo $400,000 $205,000 $215,000 $235,000 $155,000 $105,000 $120,000CDs vencidos: 100,000 100,000 110,000 100,000 100,000 120,000Interés: 0 0 0 0 0 0CDs a 1 m 100,000 100,000 100,000 100,000 100,000 100,000

CDs a 3 m 10,000 10,000CDs a 6 m 10,000Efectivo 75,000 -10,000 -20,000 80,000 50,000 -15,000 60,000Efectivo $205,000 $215,000 $235,000 $155,000 $105,000 $120,000 $180,000

-290000

Ejemplo 5: Cartera de valores rentable.Hallar la ponderación de acciones en una cartera de valores rentable que permita incrementar la rentabilidadpara un determinado nivel de riesgo. En esta hoja se utiliza el modelo de índice simple de Sharpe.Se puede también utilizar el método de Markowitz si existen términos de covarianza.

Tasa libre de ries 6.0% Var. del mercado 3.0%Tasa del mercado 15.0% Ponderación máxima 100.0%

Beta r. residual Ponderación *Beta *Var.

Acción A 0.80 0.04 20.0% 0.160 0.002

Acción B 1.00 0.20 20.0% 0.200 0.008

Acción C 1.80 0.12 20.0% 0.360 0.005

Acción D 2.20 0.40 20.0% 0.440 0.016

Page 3

Page 4: 4solver Manual

4solver

���������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������������

Pagarés d 0.00 0.00 20.0% 0.000 0.000

Total 100.0% 1.160 0.030

Rentabilidad Varianza

TOTAL cartera: 16.4% 7.1%

Maximizar rentabilidad: A21:A29 Minimizar riesgos: D21:D290.00 0.00

4 4VERDADERO VERDADERO

VERDADERO VERDADERO

VERDADERO VERDADERO

VERDADERO VERDADERO

VERDADERO VERDADERO

FALSO FALSOVERDADERO FALSO

Ejemplo 6: Valor de la resistencia en un circuito eléctrico.Hallar el valor de la resistencia en un circuito eléctrico que disipará una descarga equivalente al uno por ciento de su valor inicial en una vigésima de segundo desde el momentoen que se mueve el interruptor.

Interruptor-> |----- \ --------- ----| q0 = 9 voltios | \ | q[t] = 0.09 voltios | | | t = 0.05 segundosBatería Condensador (C) Inductor (L) L = 8 henrios | | | C = 0.0001 faradios | | | |----- ----|---- Resistencia ----| R = 300 ohmios

(R)

1/(L_*C_) #¡REF! q[t] = #¡REF!(R_/(2*L_))^2 #¡REF!

RAIZ(D16-D17) #¡REF!

COS(t_*D18) #¡REF!

-R_*t_/(2*L_) #¡REF!q0*EXP(D20) #¡REF!

Page 4

Page 5: 4solver Manual

4solver

EJEMPLO SIMPLEX CON SOLVER PARA MAXIMIZAR (ver archivo en curso)

Calidad A Calidad B Totales Ejemplo de maximizar con el Solver. Precio unitar $7.00 $11.00 Una empresa fabrica dos calidades de producto, que se diferencian por% Aditivo 1 3% 5% el porcentaje de dos aditivos que lleva cada una.% Aditivo 2 8% 3%Cantidad 10 10 La calidad A lleva un 3% de Aditivo 1 y 5 % de Aditivo 2.Aditivo 1 0.3 0.5 0.8 La calidad B lleva un 8% de Aditivo 1 y 8 % de Aditivo 2.Aditivo 2 0.8 0.3 1.1 El producto de calidad A se vende a 7$ el litro y el de calidad B aVentas $70.00 $110.00 180$ 11$/litro.

Hay una disponibilidad de 7 litros de Aditivo 1 y 17 litros de Aditivo 2.StockAditivo 1 7 Preguntamos cuánto producto de cada calidad se debe fabricar para maximizarAditivo 2 17 las ventas.

En la fila 5 tenemos la cantidad fabricada de cada producto (que, enrealidad, todavía no conocemos).En la fila 6 multiplicamos la cantidad de cada producto por su respectivoporcentaje de Aditivo 1. Entonces en D6 tenemos el consumo total de Aditivo1.En la fila 7 hacemos lo mismo con los porcentajes de Aditivo 2. Entonces enD7 tenemos el consumo total de Aditivo 2.En la fila 8 multiplicamos la cantidad de cada producto por su respectivoprecio. Entonces en D8 tenemos las ventas totales.

El problema consiste en calcular los valores de B5 y C5 para maximizar elvalor de D8, con las siguientes restricciones:

El valor de D6 debe ser menor o igual al de B11 (limitación por cantidad deAditivo 1).El valor de D7 debe ser menor o igual al de B12 (limitación por cantidad deAditivo 2).B5 y C5 deben ser mayores o iguales de cero (por que no tienen sentidocantidades negativas de producto).

Opciones Herramientas/Solver. (si no aparece ver en Complementos...)Donde dice Celda objetivo, marcamos D8 (celda a maximizar).Marcamos la opción Máximo.Donde dice Cambiando las celdas, seleccionamos B5:C5.Para indicar las restricciones hacemos un clic en Agregar. Aparece un cuadrodonde indicamos las cuatro restricciones.Referencia de la celda, B5.>=0 (cero).Hacemos un clic en Agregar.Referencia de la celda, C5.>=0 (cero).Hacemos un clic en Agregar.Referencia de la celda, D6.<=B11.Hacemos un clic en Agregar.

Page 5

Page 6: 4solver Manual

4solver

Referencia de la celda, D7.<=B12.Hacemos un clic en Aceptar. Vuelve el cuadro original.Hacemos un clic en Resolver.Hacemos un clic en Aceptar.

Según el Solver hay que fabricar 206 litros de producto A y 16 de productoB. Las ventas totales serán de 1622$.

Page 6

Page 7: 4solver Manual

4solver

4.4) RESEÑA de aplicaciones en Excel:Para la estimación de funciones (unidades 3, 4, y el resto del programa) se aplican conceptos teóricos y diversas aplicaciones que tiene Excel (además del Solver):

Reseña de algunas FUNCIONES de uso ECONOMICO con EXCEL

Interesa practicar la obtención de FUNCIONES, SU GRAFICO Y su ECUACION, pintando el rango de dato Igualmente, las ecuaciones se convierten en rangos de datos y se grafican para resolver problemas microec CORRELACION LINEAL SIMPLE Y MULTIPLE tambien son facilitadas por Excel con varias funciones SISTEMAS ECUACIONES NxN son resueltos instantáneamente DISTRIBUCION NORMAL y otras; PROBABILIDADES, etc. tiene funciones prediseñadas SOLVER con Herramientas/ Solver/ etc para programación lineal Simplex, .....ver archivos aparte @raiz(4) = 2 ... se escribe solo: @raiz(4) o bien =raiz(4) !!! =RAIZ(ABS(-25)) ... raiz valor absoluto @potencia(5;2) = 25 @potencia(98,6;3,2) = 2401077 ...con decimales: 98,6 elevado a la 3,2 .... @potencia(4;4/5) = 5,656854 ... con fracciones @MDETERM({3;6\1;1}) (resuelve ese determinante de 2 filas x 2colum. = 3x1-6x1 = -3) @Mdeterm(A1;C3) = A1(B2xC3 - B3xC2) + A2(B3xC1-B1xC3)+A3(B1xC2-B2xC4) @MDETERM({3;6;1\1;1;0\3;10;2}) = 1 calcula este determinante 3x3; ....idem para N x N !!!!! MATRIZ INVERSA: =MINVERSA(A1;C3) ...pinto A5:C7 y escrito ahí esto y calcula la matriz inversa de A1:C3

VAN Y TIR: =VNA(6%,B2:B11) y para no periodico =VNANOPER(6%;C2:C11;A2:A11) =TIR(B2:B11;1) =NPER(tasa;cuota;valor inicial;valor resifual;tipo) da la cantidad de cuotas para amortizar una inversion ...con la función Análisis (en Herramientas); con tipo1=pago al inicio; tipo 0=pago al final) =PAGO(tasa;cant.periodos;valor;incial;final;tipo) da el monto de la cuota =PAGO.INT.ENTRE(tasa;cant.periodos;valor;inicial;final;tipo) da el monto de los intereses desde hasta.... =pago.princ.entre( ; ; ; ; ; ) da el monto del capital amortizado desde hasta... =pagoint(tasa;periodo;cant.pagos;valor inicial;valor residual;tipo) da el monto de interes mes a mes =pagoprin( ; ; ; ; ; ) da el monto de capital amortizado mes a mes

=TENDENCIA(B2:B11;A2:A11;A13:A15) con nuevos valores en A13:A15 para proyectar en B12:B15 =PRONOSTICO(A13;B2:B12;A2:A12) con (valor X a pronosticar; impurezas; porduccion) =PEARSON(B2:B12;A2:A12).....da la correlación R

=coef.de.correl(rango1;rango2) =correl({3;2;4;5;6};{9;7;12;15;17}) =0,997 esta es la función correlación; otras formas es pintando rangos... =ESTIMAC.LINEAL(rangoX;rangoY; verdadero;falso) esta es la función; otras formas es pintando rangos =pendiente(B2:B11;A2:A11) esta es la función; otras formas es pintando rangos =ESTIMC.LOGARITMICA(B2:B11;A2:A11;verdadero;falso) con produccion en B2:B11y tiempo en A2:A11 e

=aleatorio() da un numero al azar entre 0 y 1 (cada vez que recalculo con F9) =aleatorio.entre(inferior;superior) da numeros aleatorios, entre rangos que elija, con F9 =combinat(A1;A2) da las combianciones con 3 elementos entre 40; con 40 en A1 y 3 en A2 =exp(A1) da el resultado de elevar el numero e (2,71828183) a una potencia que tenga en A1 (ej: e a la 8 =8 =LN(A1) da el longaritmo natural (tomando 5 crece asintoticamente hacia la ordenada 1,6) = log10(A1) da el log.base10 (crece asintoticamente menos; tomando 5 crece solo hacia la ordenada 0,7) =percentil(A2:A12;0,2) da el percentil 20% (el quinto inferior) de la lista en A2 hasta A12

Page 7

Page 8: 4solver Manual

4solver

=PERMUTACIONES(10;3) (720 posibilidades de subir al podio (3) si hay 10 corredores) =720 =PROBABILIDAD(A2:A12;B2:B12;0,5) (valores;probabil.inferior;superio) ej. probab. 50% de fallas entre 0 y 5 DISTRIBUCION BINOMIAL: ej. probabilidad en cara o cruz = 0,5; ¿probab. de 6 caras en 10 tiradas? = DISTR.BINOM(6;10;0,5;FALSO) = 0,205 (20,5%) (Si la probab.éxito no es constante en todo el experimento se usan probab.acumuladas) DISTRIBUCION NORMAL con (valor; media; desvio estandar; verdadero): Ej. vida util maquina 450 hs. con desvio estandar 75; ¿Probabilidad fallas de 500 a 600 hs? =DISTR.NORM(500;450;75;VERDADERO) = 0,75 = distri.norm(600;450;75;verdadero)= 0,98 ; diferencia 0,98 - 0,75 = 0,23 ó 23% =intervalo.confianza(%significion;desvio;tamaño muestra) ... si la media=12 y el desvio estandar 5; con 10%siginificacion de el internavolo 12+y-1,84 =negbinomdist(A6;1;1/6) da la probabil. de obtener exitos luego de fracasos (con cant.de tiradas en A6, etc.) ej. probabilidad de obtener un as luego de la quinta tirada del dado = 8,04% =normalizacion(valor;media;desvio) la el valor normalizado (valor-media /desvio) =distr.log.norm(valor;media;desvio) da la probabilidad de un valor aleatorio dado con distribucion logarirmica =distr.log.inv(probabilidad;media;desvio) da el valor de la variable aleatoria para una probabilidad dada =DISTR.EXPONENCIAL(valor;lambda;acumulado) =DISTR.BETA(valor;alfa;beta;A;B) da la probabilidad acumulada para una variable que sigue esta distribució =DISTR.WEIBULL(valor;alfa;beta;acumulado) probabilidad acumulativa de fallas según aumenta el tiempo

=error.tipico.XY(rangoX;rangoY;verdadero;falso) da el error vs. la linea de ajuste =desvest(B2;B12) da el desvio estandar de los datos reales en ese rango (un % de los cuadrados de desvios =desvprom(B2:B12) da el desvio de los datos reales vs. el promedio

=VAR(B2:B12) muestra en la celda siguiente la variaza de los datos en ese rango. PRUEBA t DE STUDENT: ¿probab. que 2 maquinas tengan igual media? =PRUEBAT(A2:A16;B2:B16;1;1) con (prod.maquina1; prod.maquina2; colas; tipo "t") =DISTR.F( ; ; ) idem para ver si 2 grupos de examentes tienen similar rendimiento PRUEBA F (Snedecor/Fisher): probab. que 2 series tengan igual varianza ? =pruebaf(A2:A11;B2:B11) = 90% prob PRUEBA Z: probab. que Z pertenezca a una poblacion? =PRUEBAZ(A2:A11;C2;1,1) con(poblacion; valor; desvio estandar) =fisher(A2) da la transformacion Z de Fisher del valor en A2 (entre 1 y -1, excluidos)

=DISTR.CHI( ; ; ) de una cola; prob. proxima cosecha sea según la hipotesis PRUEBA CHI: indica el % confiabilidad de que una serie teorica sea como otra real: =PRUEBA.CHI(A2:A12;B2:B12) =0,97 datos teoricos confiables, casi 1; con (datos reales; teoricos) =prueba.chi.inv(probabilidad;grados) da la probabilidad de la variable aleatoria, con un grado libertad dado

TEORIA DE LAS COLAS: probab. de recibir solo 25 clientes diarios u otra cantidad ?... =POISSON(A7;30;verdadero) con clientes en A2:A11 y probab. en B2:B11 y promedio 30 =DISTr.GAMMA(valor o celda;alfa;beta;verdadero para acumulada o falso para densidad) da los minutos esp =DISTr.F(valor o celda;grados1;grados2) da la probabilidad según los grados de numerador y denominador

Page 8

Page 9: 4solver Manual

4solver

Aplicación de Excel para resolver sistemas y calcular funciones de demanda u otras con n variables independientes; graficar y analizar sus coeficientes

SOLUCION DE SISTEMAS DE ECUACIONES N x N con SOLVER (en archivo)

L= 2X2 + 2XY + 5Y2 + Z(2X+Y-500) + W(WY-30Resolver un sistema n x n , con Solver. Ver el archivo adjunto.Tomo cuatro celdas cualesquiera, inicialmente vacías, y las considero

L1 = 6X +2Y + 2Z + YW = 0 iguales, respectivamente a W, X, Y y Z. En mi ejemplo son A8:A11.L2 = 2X +10Y +Z + XW = 0 En otras cuatro celdas (en el ejemplo, A13:A16) escribo sus ecuaciones,L3 = 2X + Y - 500 = 0 igualadas a cero y en función de las cuatro celdas anteriores. Por ejemplo,L4 = XY - 300 = 0 si una ecuación fuera 2x+y=20, escribiría =2*A9+A10-20. Estoy suponiendo queResultado Solver: A9 es X y que A10 es Y.0.9589039 <--W a cam...Considero, Luego entro al Solver. Como celda objetivo pongo A13, una de las cuatro

249.399 <--X a cambarbitrariamente, donde escribí las ecuaciones. Cualquiera de ellas.1.20289 <--Y a cambque estas cuatro celdaComo valor de la celda objetivo indico 0.

-749.975 <--Z a cambson las variables. Como celdas a cambiar indico las cuatro (A8:A11) que consideré iguales a lasAcá escribo las cuatro ecuaciones, igualadasvariables.

0.00000 <--L1 OBJE... Con las formulas Dentro de las restricciones pongo las celdas de las otras tres ecuaciones,0.00000 <--L2 RESTc/u segun A3..A6 iguales a cero. Es decir: =A14=0; =A15=0 y =A16=0.0.00000 <--L3 RESTRICC0.00000 <--L4 RESTRICC Al hacer clic en Resolver obtengo, en el rango A8:A11 los valores de las

variables que satisfacen las cuatro ecuaciones. Me da W=0.96; X=249.4; Y=1.2no tienen los valores correctos. y Z=-750.

elegir VALORES DE y 0 en vez de maximizarPor otra parte, en su planilla obtenía una referencia circular porque comorango indicado como argumento de MINVERSA puso el rango donde estabaescribiendo la función. Ejemplo:Si quiere calcular la inversa de A1:C3 (una matriz de 3x3) selecciona elrango A5:C7 (otra matriz de 3x3) y escribe =MINVERSA(A1:C3).En cambio, usted había escrito, en A5:A7, la función =MINVERSA(A5:A7).De todas formas, el método de matrices no sirve para este tipo de ecuacionesque, como ya le adelanté, solamente resuelve sistemas lineales.

Ejemplo de resolución de dos ecuaciones con dos incógnitas. No vana tener problemas en generalizarlo a n x n.

gallina y cada cerdo?Primero armamos las ecuaciones. Sea "g" el precio de cada gallina y "c" elde cada cerdo:

20 g + 15 c = 20040 g + 10 c = 150

En un rango de 2 filas y 2 columnas escribimos la "matriz de coeficientes".20 15 200 Es decir, aquella que contiene los coeficientes de las 40 10 150 variables g y c, con su signo:

20 15

Page 9

Page 10: 4solver Manual

4solver

40 10En un rango de 2 filas y una columna escribimos el "vector de términosindependientes". Es decir, el formado por los términos a la derecha delsigno igual, en cada ecuación:

200150

matrizinversa: Matemáticamente, los valores de las incógnitas se obtienenn multiplicando la

inversa de la matriz de coeficientes por el vector de términos

-0.025 0.038 independientes. En la planilla adjunta, la matriz de coeficientes está en el

0.1 -0.05 rango A12:B13. Calculamos su inversa:

Seleccionamos un rango de 2 x 2. De las mismas dimensiones que la matriz

original.

Escribimos =MINVERSA(A12:B13).

Apretamos la combinación Control + Shift + Enter.

Esto último se debe a que MINVERSA es una "función matricial". No devuelve

un único valor, sino un rango de valores (una matriz). Supongamos que este

1/C (D) rango fue A17:B18 (como en el adjunto)

Ahora multiplicamos la matriz inversa por el vector de términos

0.625 independientes. Supongamos que este vector es D12:D13:

12.5 Seleccionamos un rango de una columna por dos filas.

Escribimos =MMULT(A17:B18;D12:D13)

Apretamos la combinación Control + Shift + Enter.El resultado es un vector de dos valores: 0.625 y 12.5. El primer valor esel precio de cada gallina y el segundo, el de cada cerdo.

Page 10

Page 11: 4solver Manual

4solver

DEMANDA OBTENIDA EMPIRICAMENTE CON REGRESION LINEAL MULTIPLE

y x1 x2 x3 EXCEL: demanda según correlacion multiple1991 278 139 100.0 181 Herramient/complement/analisis/regresion1992 524 262 94.5 2271993 684 342 93.2 253 entrada de Y b1:b12 (demanda de helados1994 818 409 93.8 270 entrada de X c1:e12 (calor, lluvia, turistas)1995 810 405 92.5 272 salida: marcar solo A301996 840 420 92.4 280 los coeficientes estan en B47:B501997 1070 450 93.1 300 y* = a + bx1 + c x2 + dx3 según la formula de 1998 1078 460 93.4 315 pronostico / ajuste que se armó en B521999 1022 450 94.1 325 y luego se copia a la zona de pronostico B15:B252000 1041 450 94.1 3352001 1061 450 94.1 345

pronostic =+$B$47+C13*$B$48+D13*$B$49+$B$50*E13fómula a copiar hacia abajo si adopto como ejemplo que las variables independ tendran en el

2022 0 139 100.0 181 futuro similar comportamiento que en2003 0 262 94.5 227 en el pasado2004 0 342 93.2 2532205 0 409 93.8 2702206 0 405 92.5 2722007 0 420 92.4 2802008 0 450 93.1 3002009 0 460 93.4 3152010 0 450 94.1 3252011 0 450 94.1 3352012 0 450 94.1 345

Ej.: pronosticar la venta de helados Y, dependiente de la temperatura X1, la lluvia caida X2 y los turistas llegados X3

Resumen

adísticas de la regresiónCoeficiente de correlación m 0.97729Coeficiente de determinación 0.9551 95% de ajuste es suficientemente buenoR^2 ajustado 0.93265Error típico 49.5031Observaciones 10

ANÁLISIS DE VARIANZAGrados de libea de cuadro de los cu F or crítico de F Pearson

Regresió 3 312752 104251 42.542 0.00019 42,542 > ,00195 No aleatorioResiduos 6 14703 2451Total 9 327456

t Student: 2,52 es mayor que el valor de tabla para 6 grados de libertad 1,9; Var. X1 es significCoeficiente Error típico Estadístico t Probabilidad Inferior 95%Superior 95% Inferior 95,0%perior 95,0%

Intercepc -2393 3368 -0.71 1 -10635 5849 -10635 5849139 2.132 0.847 2.52 0.045 0.059 4 0.059 4.2

1000 21.047 36.462 0.58 0.585 -68.174 110 -68.174 110.3

Page 11

Page 12: 4solver Manual

4solver

181 1.526 1.389 1.10 0.314 -1.872 5 -1.872 4.9 formula a copiar al rango de proyccion de las X (c15:e25) para que calcule Y estimad/ajust (fijar la referencia absoluta a los coeficientes b45:b50 para poder copiar la formula bien)

y ajustado -2393 + 2,132 X1 + 21,047 X2 + 1,526 X3

Análisis de los residuales Resultados de datos de probabilidad

Observacióonóstico 2Residuosuos estándares Percentil 278

1 500.77 23.23 0.57 5 5242 683.67 0.33 0.01 15 6843 865.10 -47.10 -1.17 25 8104 832.26 -22.26 -0.55 35 8185 874.35 -34.35 -0.85 45 8406 983.58 86.42 2.14 55 10227 1034.11 43.89 1.09 65 10418 1042.79 -20.79 -0.51 75 10619 1058.05 -17.05 -0.42 85 1070

10 1073.32 -12.32 -0.30 95 1078

Page 12

Page 13: 4solver Manual

4solver

Ej. Estimación de demanda mediante correlacion lineal simple con Excel:HERRAMIENTAS / ANALISIS DE DATOS / REGRESION

Q P QY X ESTIMADA CORRELACION PRECIO CANTIDAD2 20 1.884 16 4.57 Y = 15,3 - 0,67 P7 13 6.588 11 7.939 9 9.27

10 6 11.283 19 2.56

12 5 11.9615 2 13.97

Resumen

adísticas de la regresiónCoeficiente de correlación m 0.99Coeficiente de determinación 0.98R^2 ajustado 0.97Error típico 0.71Observaciones 9.00

ANÁLISIS DE VARIANZAGrados de libea de cuadro de los cu F Valor crítico de F

Regresió 1.00 144.06 144.06 288.20 0.00000Residuos 7.00 3.50 0.50Total 8.00 147.56

Coeficientes Error típico Estadístico t Probabilidad Inferior 95% Superior 95%Inferior 95,0%Superior 95,0%

Intercepc 15.31 0.50 30.47 0.00 14.12 16.50 14.12 16.50Variable X -0.67 0.04 -16.98 0.00 -0.76 -0.58 -0.76 -0.58

Análisis de los residuales Resultados de datos de probabilidad

Observaciónóstico paResiduosuos estándares Percentil Y1.00 1.88 0.12 0.18 5.56 2.002.00 4.57 -0.57 -0.86 16.67 3.003.00 6.58 0.42 0.63 27.78 4.004.00 7.93 0.07 0.11 38.89 7.005.00 9.27 -0.27 -0.41 50.00 8.006.00 11.28 -1.28 -1.94 61.11 9.007.00 2.56 0.44 0.67 72.22 10.008.00 11.96 0.04 0.07 83.33 12.009.00 13.97 1.03 1.56 94.44 15.00

Variable X 1 Gráfico de los residuales

0.501.001.50 Variable X 1 Curva de regresión ajustada

20 Gráfico de probabilidad normal

Page 13

Page 14: 4solver Manual

4solver

1ro)temperatuconsumo

10 34.420 58.725 70.930 80.732 74.135 107.840 104.445 138.0

COEFICIENTES:pendiente 2.76ordenada 1.90pronostico 139.86R cuadrado 0.92

-1.50-1.00-0.500.000.50

0 5 10 15 20 25

0

5

10

15

20

0 10 20 30

YPronóstico para Y

Gráfico de probabilidad normal

0.00

5.00

10.00

15.00

20.00

0.00 20.00 40.00 60.00 80.00 100.00

y = 2.7592x + 1.9015R2 = 0.9185

0

20

40

60

80

100

120

140

160

0 10 20 30 40 50

Page 14

Page 15: 4solver Manual

4solver

2do) REGRESION LINEAL MULTIPLE - APLICACION DE A UNA INMOBILIARIA PARA VALORAR VIVIENDASREGRESION LINEAL MULTIPLE INMOBILIARIA .....CON ALEATORIEDAD F ...... Y T STUDEN CON LA IMPORTACIA DE Cada COEFICIENTE

MUESTRA DE 11 DEPARTAMENTE ENTRE 1500 (ALGUNO CON ENTRADAS Y MEDIA ENTRADA PARA SERVICIO)con Herramientas / Analisis / Regresion / pinto rango Y sin titulos / rango de las X / rango salida afuera...

X1 = M2 X2= OFICINX3= ENTRAX4 = ANOS Y= $ VALOR2310 2 2 20 1420002333 2 2 12 1440002356 3 1.5 33 1510002379 3 2 43 1500002402 2 3 53 1390002425 4 2 23 1690002448 2 1.5 99 1260002471 2 2 34 1429002494 3 3 23 1630002517 4 4 55 1690002540 2 3 22 149000

Resumen

Estadísticas de la regresiónCoeficiente 0.9983727 ALTA CORRELACIONCoeficiente 0.996748 alta determinacionR^2 ajustad 0.99458 Función según los coeficientes :Error típico 970.57846 $ Valor dep. Y = 52317,8 +27,6 X1 + 12529,7 X2 + 2553,2 X3 -234,2 X4Observacio 11

ANÁLISIS DE VARIANZA aleatoriedad posible en esta correlacion ?Grados de liberma de cuadradio de los cua F crítico de F

Regresión 4 1.732E+09 433098330 459.75367 1.372E-07Residuos 6 5652135.3 942022.55 459 ES MAYOR QUE 1,37 O SEA QUE NO ES ALEATORIO O CASUAL

Total 10 1.738E+09Studen s/ ponderacion cada X

Coeficientes Error típico Estadístico tProbabilidadInferior 95%Superior 95%nferior 95,0%uperior 95,0%Intercepción 52317.831 12237.362 4.2752541 0.0052328 22374.063 82261.598 22374.063 82261.598Variable X 1 27.641387 5.429374 5.0910818 0.002241 14.356178 40.926597 14.356178 40.926597Variable X 2 12529.768 400.06684 31.319187 7.039E-08 11550.839 13508.697 11550.839 13508.697Variable X 3 2553.2107 530.66915 4.8113041 0.0029663 1254.7091 3851.7122 1254.7091 3851.7122Variable X 4 -234.2372 13.268011 -17.65428 2.121E-06 -266.7028 -201.7715 -266.7028 -201.7715

EL valor critico de T es = 1,9 En una tabla se ve el T para 6 = 1,9 y aquí 6 es = 11 - 5variables +1 que Excel lo calculo como 1,9 T indica si cada Variable X es importante en la prediccion: Ej anos = -234,24 dividido por 13,268 error tipico = -17,7 que es mayor que 1,94 o 1,37; o sea, importante. Idem para las otras variables X , algunas mas importantes que otras aqui (T en valor absoluto)

--- 0 ---

CASO DE CORRELACION CUADRATICA / PARABOLICA (COSTO MEDIO) Y REGRESION LINEAL SIMPLE (DEMANDA ) : PARA EQUILIBRIO EMPIRICO

A) REGRECION CUADRATICA B) REGRESION LINEAL

Page 15

Page 16: 4solver Manual

4solver

SPIEGEL, ESTADISTICA : CORRELACION PARABOLICA; TAMBIEN EN CECIL MILL, ESTADISTICA, ED.AGUILAR 1970

Page 16

Page 17: 4solver Manual

4solver

Page 17

Page 18: 4solver Manual

4solver

Page 18

Page 19: 4solver Manual

4solver

Page 19