seminario: 2º sesión academico/02 master/contenidos... · 2015-06-22 · •aspectos conceptuales...

• Aspectos conceptuales de la PL y el Solver de Excel

• CASO 1 : Oferta Comercial ASEBOR, versión simplificadaDos condiciones comerciales exclusivas

• CASO 2 : Oferta Comercial ASEBOR, versión desarrolladaDiversas condiciones comerciales, margen de ganancia mínimo, precio medio del mix por debajo de la competencia

Caso oferta comercial de bodega aplicando SOLVER PARA EXCEL

SEMINARIO: 2º Sesión

Planteamiento de una oferta comercial con técnicas de programación lineal

Jose Ignacio González Gómez Aranda de Duero 25 y 26 de junio 2015

Introducción a la PL con SOLVER. Caso diseño de una oferta comercial en bodega (I)

¿Qué es la optimización? Optimización: encontrar una alternativa de decisión con la propiedad de ser mejor

que cualquier otra en algún sentido

Componenteso Función objetivo

Medida cuantitativa del funcionamiento (de la bondad) de un sistema que se

desea maximizar o minimizar

o VariablesDecisiones que afectan el valor de la función objetivo

Independientes o dependientes

o RestriccionesConjunto de relaciones que las variables están obligadas a satisfacer

Resolver: encontrar el valor que deben tomar las variables para hacer óptima lafunción objetivo satisfaciendo el conjunto de restricciones.

Introducción a la PL con SOLVER. Caso diseño de una oferta comercial en bodega (II)

¿Dónde se aplica y para que?

Área Comercial y marketing

Asignación de recursos

Combinación optima de recursos

Selección y mezclas

Otras decisiones….

Tipo de decisión

Área Producción y costes

Combinación optima de recursos

Asignación de recursosSelección y mezclas

Planificación de la producción

Otras decisiones…

Área Recursos Humanos, Asignación de

Tareas - Turnos

Asignación de tareasAsignación de turnos-horarios

Programación de Personal Otras decisiones…

Área Finanzas

Asignación de recursosSelección de carteras

Otras decisiones…

Área Distribución, logística y transportes

Asignación de recursosProblemas de Transportes

Otras decisiones…

Otros ámbitos: Dietas , mochila, etc

Dietas Selección y mezclas Otras decisiones…

Introducción a la PL con SOLVER. Caso diseño de una oferta comercial en bodega (III)

Ejemplos:• Él agricultor está interesado en saber que cantidad de hectáreas debe sembrar de cada producto,

a fin de obtener el máximo ingreso posible por el uso de sus recursos.• Cubrir turnos de trabajo al menor coste posible. El jefe cafetería y terrazas se plantea determinar

cuántos camareros de tiempo completo y cuántos de tiempo parcial debe contratar en cadaturno para cumplir con los requisitos a un coste mínimo.

• Planificar Horarios y de turnos por caja• Calcular qué cantidad debe fabricarse de cada tipo, de forma que se maximice el margen total

bruto para el fabricante• Elaborar el calendario de trabajo semanal, minimizando el coste del servicio ajustándose a las

restricciones de los capacidades y a las necesidades del servicio y de la demanda.• Determinar la cantidad a producir de vino joven y crianza que maximiza el beneficio y la cantidad

que deben comprar de cada variedad de uva (Caso Bodega Aceviño)• BONOLOTO. Diseñar la cartera que nos de la máxima rentabilidad tomando en consideración las

preferencias y restricciones señaladas.• Determinar cuántas unidades se tienen que enviar de un producto tipo desde cada almacén a

cada centro comercial para minimizar el coste total• ECONOMISTAS ASOCIADOS: Asignar tareas al personal especializado• Determinar el plan óptimo para maximizar el presupuesto de publicidad en términos de

audiencia totalPuede ver estos casos de ejemplo propuestos en: www.jggomez.eu sección Hoja de Calculo, apartado Apoyo a la toma de decisiones con Solver

Consejo: Contratar a un profesional o contar con un alumno en practicas con perfil matemáticas, estadísticas, físicas, ingeniería, ADE,

ECO, etc..

http://www.jggomez.eu/

Introducción a la PL con SOLVER. Caso diseño de una oferta comercial en bodega (IV)

En todos los casos anteriores es necesario modelizar (que lo haremos a través de hoja de calculo)

La representación de un problema dela vida real a través de un modelomatemático (representado en hoja decalculo o no) exige el desarrollo de untrabajo metódico y disciplinado paralo cual es recomendable seguir unaserie de pautas:

Paso 1 El entendimiento del problema

Paso 2 La definición de variables

Paso 3 La formulación del modelo

Paso 4 La solución del modelo ycomprobación de la solución

… lo difícil de la programación lineal es modelizar el problema…

Introducción a la PL con SOLVER. Caso diseño de una oferta comercial en bodega (IV)

Introducción a la PL con SOLVER. Caso diseño de una oferta comercial en bodega (V)

Cuando lo intento resolver me puedo encontrar con:

Tipo de solución que podemos obtener en la resolución de un problema de PL(especial referencia al caso de dos variables)

Factibles No Factibles

Si existe el conjunto de

soluciones o valores que

satisfacen las restricciones

Cuando no existe el conjunto de

soluciones que cumplen las

restricciones, es decir, las

restricciones son inconsistentes.

Con solución

única

Con solución

múltiple

Con solución

no acotadaSi existe más de una

solución

Cuando no existe límite

para la función objetivo

Introducción a la PL con SOLVER. Caso diseño de una oferta comercial en bodega (VI)

Optimización con hojas de calculo (Solver –Excel) Ventajas

Fáciles de usar Integración total con la hoja de cálculo Familiaridad con el entorno que facilita la explicación del modelo y de sus resultados Facilidad de presentación de resultados en gráficos

Inconvenientes No inducen una buena práctica de programación Presentan dificultades para verificación, validación, actualización y documentación de los

modelos No permiten modelar problemas complejos o de gran tamaño

Existen diversos programas comerciales concretos.Activar el modulo Solver en Excel… Ver documento de ayuda disponible.


Introducción a la PL con SOLVER. Caso diseño de una oferta comercial en bodega (VII)

Configuración básica de Solver : Primer contacto, pantalla de configuración

La ventana Parámetros de Solver se utiliza para describir el problema de optimización a

EXCEL.

Introducción a la PL con SOLVER. Caso diseño de una oferta comercial en bodega (VII)

Configuración básica de Solver : Otras cuestiones Configuración de las restricciones

Recodemos Posibles Soluciones

Tipo de solución que podemos obtener en la resolución de un problema de PL(especial referencia al caso de dos variables)

Factibles No Factibles

Si existe el conjunto de

soluciones o valores que

satisfacen las restricciones

Cuando no existe el conjunto de

soluciones que cumplen las

restricciones, es decir, las

restricciones son inconsistentes.

Con solución

única

Con solución

múltiple

Con solución

no acotadaSi existe más de una

solución

Cuando no existe límite

para la función objetivo


Presentación:

CASO GENERAL: Oferta Comercial ASEBOR


ASEBOR es una bodega familiar de notable éxito empresarial y recientemente ha

recibido la solicitud de un presupuesto por parte de un importante operador del

sector vinícola alemán para un primer pedido, pallet de 100 cajas es decir de 600

botellas de mix de vinos de nuestra bodega.

Ante el incremento de solicitudes de presupuestos y ante la incertidumbre con la

que estos se calculan y los criterios empleados, se ha decidido modelizar el proceso

de toma de decisiones relacionadas con la elaboración de ofertas comerciales a

través del diseño de una hoja de cálculo modelo que permita la optimización del

resultado sujeta a una serie de restricciones y condicionantes de nuestra empresa.

Oferta Comercial ASEBOR: Generalidades


Tras diversas reuniones con la gerencia se concretó los siguientes criterios a tomar

en consideración para el diseño del modelo presupuestario y que pasamos a

exponer a continuación.

Datos técnicos y económicos relacionados

Además de los costes directos unitarios por producto anteriores se ha estimado unos

costes de estructura o generales de 0.25 €/botella.

Oferta Comercial ASEBOR: Generalidades


Se ha acordado que los costes de logística corren

a nuestro cargo y suponen los siguientes importes.

Por la dirección comercial se ha estimado conceder a este cliente por primer pedido los siguientes

descuentos:

• Comercial: 2%

• Pronto Pago: 2 %

CASO 1: Oferta Comercial ASEBOR (Versión 1)


Presupuesto basado en precios unitarios para un mix de botellas de vino (versión simple)

Estrategia comercial y restricciones

Se han establecido dos condiciones:

a) El precio unitario de cada vino no puede ser superior al establecido en bodega (venta directa).

b) El precio medio de botella correspondientes al mix de ventas no puede superar 5,10 €/botella

(argumentario básico pactado)

SE PIDE:

1. Diseñar la hoja de cálculo que permita analizar y resolver el problema en términos de programación

lineal, identificando las Variables de Decisión, Función Objetivo y Restricciones.

2. Programar el Solver y determinar si tiene solución y en caso afirmativo analizar la misma.

CASO 1: Oferta Comercial ASEBOR (Versión 1)Presupuesto basado en precios unitarios para un mix de botellas de vino (versión simple)

Propuesta de modelo de planteamiento adaptado al caso


Restricciones básicas del modelo



Presupuesto basado en precios unitarios para un mix de botellas de vino (versión

desarrollada)

Estrategia comercial y restricciones:

En este caso la estrategia comercial es algo más compleja. En concreto tomando comoreferencia los precios medios de comercialización de los vinos ASEBOR (Nota 3) yrelacionándolos con su competidor de referencia (Nota 4) se han definido las estrategiascomerciales en la columna Condición (Nota 5) resumidas en el siguiente cuadro.




desarrollada)

Estrategia comercial y restricciones:

Además para reforzar nuestro argumentario comercial se ha establecido dos condiciones

añadidas:

• Que el precio promedio por botella del mix de la oferta debe ser como mínimo

inferior en 10 céntimos al precio promedio por botella del mix de competidores.

• Finalmente se requiere que el margen de contribución neto de la oferta debe ser como

mínimo un 15% de los ingresos netos

SE PIDE:

1. Diseñar la hoja de cálculo que permita analizar y resolver el problema en términos de programación

lineal, identificando las Variables de Decisión, Función Objetivo y Restricciones.

2. Programar el Solver y determinar si tiene solución y en caso afirmativo analizar la misma.

La relación de vinos “competidores” se ha seleccionado atendiendo a las preferencias de

nuestro cliente y a la posición competitiva de nuestros vinos en la DO según el binomio

calidad precio.




desarrollada)





desarrollada)


Restricciones básicas del modelo




desarrollada)

Fin …Gracias por su atención

Jose Ignacio González Gómez

Mas información en….. WWW.jggomez.eu

http://www.jggomez.eu/

seminario: 2º sesión academico/02 master/contenidos... · 2015-06-22 · •aspectos conceptuales...

Documents