06 guia
10
2013 [SEPARATA N. 06] Copyright – Ing. Carlos Lon Kan Prado EXCEL AVANZADO PARA LOS NEGOCIOS
Transcript of 06 guia
2013
[SEPARATA N. 06] Copyright – Ing. Carlos Lon Kan Prado
EXCEL AVANZADO PARA LOS NEGOCIOS
1
CURSO : Excel Avanzado para los negocios
PROFESOR : Ing. Carlos Lon Kan Prado
LABORATORIO DIRIGIDO 06 INDICACIONES
Leer todos los pasos preliminares antes de empezar a desarrollar el presente laboratorio.
La carpeta principal se llama: 06-excel-avanzado
Ejemplo Ruta-
C:/Alumno.Utp
06-excel-avanzado
Apoyo
06-Guia.docx
Objetivo: Función SOLVER
La opción Solver de EXCEL sirve para resolver problemas de optimización lineal y no lineal; también se
pueden indicar restricciones enteras sobre las variables de decisión.
Con Solver es posible resolver problemas que tengan hasta 200 variables de decisión, 100 restricciones
explícitas y 400 simples (cotas superior e inferior o restricciones enteras sobre las variables de decisión).
Si no encuentra la opción SOLVER en la ficha DATOS, entonces proceda a realizar lo siguiente:
ARCHIVO / OPCIONES / COMPLEMENTOS y finalmente:
Seleccione Solver y herramientas para análisis.
2
SOLVER aparecerá en la ficha DATOS
Al dar clic sobre el botón Solver aparecerá la ventana con los parámetros tal y como se muestra a continuación:
Pasos para completar los datos en la ventana Parámetros de Solver - El campo Celda objetivo contiene la celda (una sola posición) donde se encuentra la función objetivo
correspondiente al problema en cuestión (Recuerde que esta celda contiene a la función con las
variables de análisis). Si desea hallar el máximo o el mínimo, seleccione Máximo o Mínimo o
Valores.
- Si la casilla Valores de está seleccionada, Solver tratará de iterar varias veces hasta hallar un valor de la
función igual al valor del campo Valor.
- Cambiando las celdas contiene el rango o endrá la ubicación de las variables de decisión para el
problema. Por último, las restricciones se deben especificar en el campo Sujetas a las siguientes
restricciones haciendo clic en Agregar. El botón Cambiar permite modificar las restricciones recién
introducidas y Eliminar sirve para borrar las restricciones precedentes.
- Restablecer todo borra todos los parámetros ingresados del ejercicio y los regresa a sus valores por
defecto.
- Con el botón Opciones se accede a las opciones de Solver (ver más adelante).
- El botón Estimar carece de interés para nuestros fines y no se tratará en estas instrucciones.
3
Recomendaciones: Para todos los ejercicios se tienen que identificar:
Las variables que participan en el problema
La función objetivo
Las restricciones
Y aprenda a utilizar la función SUMAPRODUCTO
ABRIR el archivo Excel SOLVER:
Actividad 1
Utilice la hoja Ejemplo 1
Silvia Morales dispone de 10,000 soles para producir prendas de vestir entre blusas, vestidos y faldas.
La empresa tiene tres tiendas ubicadas en San Isidro, Miraflores y La Molina.
El costo unitario de blusas, vestidos y faldas son 18, 45 y 26 soles respectivamente.
El distrito de Miraflores requiere al menos 90 blusas, San Isidro requiere 92 vestidos y La Molina requiere 160
faldas.
a. Determine la máxima cantidad de prendas de vestir que Silvia puede producir, especifique blusas,
vestidos y faldas.
b. El gasto total para producir la máxima cantidad de prendas.
Solución:
90 blusas, 92 vestidos y 163 faldas
Un gasto total de 9,998 soles.
Actividad 2
Utilice la hoja Ejemplo 2
Creamos el siguiente escenario empresarial:
Se definen dos variables x, y:
La cantidad de kilos de papa: “x”
La cantidad de kilos de yuca: “y”
El ámbito de los kilos de papas y yucas se rige por las siguientes inecuaciones.
Blusas Vestidos Faldas
Costo/Unidad 18 45 26
Cantidad Prendas 1 1 1
4
Gráfico con la zona factible para las variables “x” e “y”
Notar también que por tratarse de papas y yucas la zona real será la parte positiva.
La utilidad por cada kilo de papa es de 2 soles y de 1 sol por cada kilo de yuca.
Restricciones:
Las variables x e y son números naturales.
La suma de kilogramos entre papas y yucas no debe superar los 1000 kilos.
Interrogantes:
a. Determine la máxima utilidad.
b. Determine la cantidad de kilos de papa y yuca para la máxima utilidad.
Respuesta:
Utilidad máxima = 1603 soles
603 kilos de papa y 397 kilos de yuca
Actividad 3
Utilice la hoja solver1
1. Se dispone de 120 refrescos de cola con cafeína y de 180 refrescos de cola sin cafeína. Los refrescos se
venden en paquetes o empaques de dos tipos diferentes. Los paquetes del tipo “A” contienen tres refrescos
X Y
Papa Yuca
Utilidad/kilo 2 1
Cantidad kilos 1 1
5
con cafeína y tres sin cafeína, y los empaques del tipo “B” contienen dos refrescos con cafeína y cuatro
refrescos sin cafeína.
Se sabe que el vendedor gana 7 dólares por cada paquete que venda de tipo “A” y 5 dólares por cada
paquete que venda del tipo “B”.
a. Maximizar el beneficio.
b. Calcular cuántos paquetes de cada tipo se deben vender para maximizar los beneficios.
c. Calcular cuántos paquetes de cada tipo se deben vender para obtener un beneficio de 250 dólares.
Importante: Los valores encontrados para cada paquete deben ser enteros
positivos.
Variables:
A = Cantidad de paquetes “A” a vender.
B = Cantidad de paquetes “B” a vender.
Función Objetivo:
Z = 7A + 5B (utilidad a maximizar)
Restricciones:
3A + 2B ≤ 120 (con cafeína)
3A + 4B ≤ 180 (sin cafeína)
Respuesta:
Se deben vender 20 paquetes del tipo “A” y 30 paquetes del tipo “B” generando un beneficio máximo
de 270 dólares.
Actividad 4
Utilice la hoja solver2
2. Una empresa va a lanzar al mercado un nuevo producto. Los planes de promoción para el próximo mes
están en marcha. Los medios alternativos para realizar la publicidad, así como los costos y la audiencia
estimada por unidad de publicidad se muestran a continuación
Para lograr un uso balanceado de los medios, la publicidad en radio debe ser igual al 50% de unidades de
publicidad autorizadas. Además la cantidad de unidades solicitadas en televisión debe ser al menos 10% del
total autorizado. El presupuesto total para publicidad se ha limitado a $. 18,500.00
a. Se necesita determinar el plan óptimo para maximizar la audiencia total o cantidad de personas que
vean la publicidad.
Television Radio Prensa
Audiencia por unidad de publicidad 100000 18000 40000
Costo por unidad de publicidad 2000 400 600
6
b. Determine el número de unidades de contratación por cada medio de publicidad (Televisión, Radio,
Prensa)
Variables de decisión:
T = Unidades de publicidad a contratar en televisión.
R = Unidades de publicidad a contratar en radio.
P = Unidades de publicidad a contratar en prensa.
Función Objetivo:
Maximizar la audiencia total o cantidad de personas que vean la publicidad.
Z = 100000 T + 18000 R + 40000 P
Restricciones:
- Presupuesto total para promociones se ha limitado a $ 18,500
2000 T + 300 R + 600 P ≤ 18500
- La publicidad en radio debe ser igual al 50% de unidades de publicidad autorizadas.
R = 0,50 (T+R+P) simplificando queda así: – 0,50 T + 0,50 R – 0,50 P = 0
- La cantidad de unidades solicitadas en televisión debe ser al menos 10% del total autorizado.
T ≥ 0,10 (T+R+P)
Restricción que al ser simplificada quedará expresada como: 0,90 T – 0,10 R – 0,10 P ≥ 0
Respuesta:
Se contratarán tres unidades de publicidad en Televisión (T = 3), catorce unidades de publicidad en
Radio (R = 14) y once unidades de publicidad en Prensa (P = 11)
La audiencia máxima será de 992,000 personas (Zmáxima).
Actividad 5
Utilice la hoja solver3
3. La empresa Colorado Cattle Company (CCC) puede comprar dos tipos de alimentos P y Q a un distribuidor
mayorista. Además el ganado de la empresa tiene ciertas necesidades alimenticias con respecto a las grasas,
calcio y hierro. Cada día una vaca requiere al menos 7 kilos de calcio, al menos 8 kilos de hierro, y no más
de 9 kilos de grasa.
La tabla siguiente indica la cantidad de grasa, calcio y hierro por cada kilogramo de alimento “P” o
alimento “Q”. Se sabe que el alimento P cuesta 5 soles y el alimento Q cuesta 6 soles por kilogramo. El
ganado se alimentará con una mezcla de los dos tipos de alimentos sin procesar.
CCC está interesada en alimentar al ganado del modo más cómodo posible.
Datos de Colorado Cattle Company
Proporción de Ingredientes por cada alimento
Variables:
Notar que consideramos alimento P y alimento Q como las variables decisorias en este análisis.
X = cantidad de kilogramos diarios del alimento P (en kilos) utilizada en alimentar una vaca
Alimento P Alimento Q
Calcio 0.26 0.36
Hierro 0.39 0.33
Grasa 0.35 0.31
7
Y = cantidad de kilogramos diarios del alimento Q (en kilos) utilizada en alimentar una vaca
Función Objetivo:
Minimizar el costo
5 X + 7 Y
Restricciones:
Sujeto a las siguientes restricciones:
0.26 X + 0.36 Y ≥7 (Calcio)
0.39 X + 0.33 Y ≥ 9 (Hierro)
0.35 X + 0.31 Y ≤ 8 (Grasa)
X, Y ≥ 0 (ambas cantidades tienen que ser números positivos)
a. Minimice el costo.
b. Determine la cantidad de kilogramos exactos (números naturales) por cada alimento.
Respuesta:
El mínimo costo es 130 soles
La cantidad de kilogramos del alimento P es de 20 y la del alimento Q es de 5 kilogramos.
Actividad 6
Utilice la hoja trabajo 1
4. Una persona para recuperarse de una cierta enfermedad tiene que tomar en su alimentación dos clases de
componentes que llamaremos A y B. Necesita tomar 70 unidades de A y 120 unidades de B. El médico le
da dos tipos de dietas en las que la concentración de dichos componentes es:
dieta D1: 2 unidades de A y 3 unidades de B
dieta D2: 1 unidad de A y 2 unidades de B.
a. Minimizar el costo si se sabe que el precio de la dieta D1 es de S/. 2.5 y el de la dieta D2 es S/. 1.45.
b. Determinar la cantidad de cada tipo de dieta para el mínimo costo. Las dietas son completas no existen
fracciones de dietas.
Variables:
D1 = Cantidad de dieta D1 a consumir.
D2 = Cantidad de dieta D2 a consumir.
Función Objetivo:
Z = 2.5 D1 + 1.45 D2 (costo a minimizar)
Restricciones:
8
Respuesta:
El mínimo costo es de 93.50 y se debe consumir 20 dietas D1 y 30 dietas D2
Actividad 7
Utilice la hoja trabajo 2
5. Se pretende cultivar en un terreno dos tipos de olivos: M y N. No se puede cultivar más de 8 hectáreas. con
olivos de tipo M, ni más de 10 hectáreas. con olivos del tipo N. Cada hectárea de olivos de tipo M necesita
4 m3 de agua anuales y cada una de tipo N, 3 m
3. Se dispone anualmente de 44 m
3 de agua. Cada hectárea
de tipo M requiere una inversión de $ 500 y cada una de tipo N, $ 225. Se dispone de $ 4500 para realizar
dicha inversión. Si cada hectárea de olivar de tipo M y N producen, respectivamente, 600 y 400 litros
anuales de aceite:
a. Obtener razonadamente las hectáreas de cada tipo de olivo que se deben plantar para maximizar la
producción de aceite.
b. Obtener la producción máxima de aceite.
c. Determine la inversión.
Variables:
M = Cantidad de hectáreas de olivo del tipo M
N = Cantidad de hectáreas de olivo del tipo N
Función Objetivo:
Z = 600M + 400N (producción a maximizar)
Restricciones:
4M + 3N ≤ 44 (agua)
500M + 225N ≤ 4500 (inversión)
No se puede cultivar más de 8 hectáreas con olivos de tipo M: M ≤ 8
Ni más de 10 hectáreas con olivos de tipo N: N ≤ 10
Respuesta:
Se debe cultivar 4 hectáreas con olivos del tipo M y 10 hectareas del tipo N, generando una
producción máxima de 6400 litros de aceite.
Actividad 8
Utilice la hoja trabajo 3
6. REALSEC SA produce dos modelos de artículos electrónicos, donde se usan resistores, capacitores y chips.
La tabla siguiente es un resumen de los datos en este caso:
9
a. Maximizar el beneficio.
b. Determine la cantidad de unidades de cada modelo para maximizar el servicio.
c. Determine la cantidad de unidades por cada recurso.
Variables:
Comenzamos por definir las dos variables de decisión del problema:
x1 la cantidad de unidades a producir del modelo 1
x2 la cantidad de unidades a producir del modelo 2.
Función objetivo:
La función objetivo consiste en obtener el mayor beneficio posible:
max z = 3x1+ 4x2
Restricciones:
Las restricciones estarán dadas por el producto de la matriz de los coeficientes y el vector de las variables
de decisión, de la siguiente forma:
2 x1 + 3 x2 ≤ 1200 Restricción relativa a los Resistores
2 x1 + 1 x2 ≤ 1000 Restricción relativa a los Capacitores
0 x1 + 4 x2 ≤ 800 Restricción relativa a los Chips
xi ≥ 0 con i=1,2 Restricciones de no negatividad
Respuesta:
El máximo costo es 1750 dólares
La cantidad de unidades del modelo 1 es 450 y la cantidad de unidades del modelo 2 es 100.
La cantidad de recursos son:
- 1200 resistores
- 1000 capacitores
- 400 chips
