Complemento Solver de Excel II

Introducción

En esta entrada sobre el complemento Solver de Excel II vamos a complicar un poco el enunciado de nuestro problema para dar un paso más hacia las posibilidades de esta herramienta.

No vamos a indicar en esta entrada como dar de alta el complemento y su funcionamiento básico para no hacerla muy larga y porque puedes verlo en mi anterior post sobre este complemento, este es el enlace: Complemento Solver de Excel I

Enunciado para complemento Solver de Excel II

Tenemos un restaurante en el que ofrecemos menús de lunes a jueves, cada menú se compone de cuatro primeros, cuatro segundos, tres postres y tres bebidas, os lo dejo en la siguiente imagen, convendría leerse el enunciado antes de seguir:

Estos son los conceptos:

• Cuerpo del escandallo:

  • Coste por ración: coste de la elaboración según el escandallo individual de cada plato.
  • Estimación de venta: porcentajes iniciales para resolver el problema, desconocemos la aceptación de cada plato por lo que se ha optado por igualar el porcentaje de venta por cada una de las categorías. Podría haberse expresado también en unidades vendidas. Cuando usemos Solver, el complemento determinará las cantidades para cada uno de los dos supuestos.
  • Coste estimado: resultado de multiplicar el coste de cada plato por la estimación de venta.

• Resumen:

  • Coste: suma del coste estimado de cada palto por el porcentaje.
  • Coste deseado: se obtiene de nuestro presupuesto.
  • Coste del escandallo: resultado de dividir el coste entre PVP sin IVA,
  • Margen deseado: margen bruto, 100% – coste deseado.
  • Margen del escandallo: margen bruto, 100% – coste del escandallo.
  • PVP deseado sin IVA: precio recomendado por nuestro presupuesto, se calcula dividiendo el coste entre el % de coste deseado.
  • PVP sin IVA: PVP entre uno más el IVA, en este caso el IVA es el 10%, por lo tanto dividimos entre 1,10 o 110%
  • PVP: Lo fijamos en función del PVP deseado, procurando que el PVP sin IVA esté por encima del PVP deseado.

Bajo este supuesto inicial nuestro coste sería de 2,77 € y el precio recomendado 8,73 €, inferior a los 9,09 € fijados.

Estimación de venta para el coste mínimo con el complemento Solver de Excel I

Para poder aplicar correctamente el complemento solver de Excel necesitamos añadir algunas fórmulas a las ya explicadas, estas son que la suma de los porcentajes de venta estimados de cada categoría han de dar como resultado 100%, de esta manera nos aseguramos que categoría suma 100% y, por ejemplo, en el caso de el coste mínimo no nos ofrezca como resultado 15% de estimación de venta de cada plato.

Vamos a clicar en Solver y a cumplimentar el formulario de entrada de parámetros del complemento

Los datos introducidos son:

Objetivo: coste mínimo, por lo tanto, celda E28.

Cambiando las cedas variables: todas las estimaciones de venta, D14:D27.

Restricciones:

• Que la estimación de ventas sea inferior en cada caso a 50%: D14:D27<=50%
• Que la estimación de ventas sea superior en cada caso a 14%: D14:D27>=15%
• Que las estimaciones de ventas por categoría sume 100%: D30=100%, D31=100%, D32=100% y D33=100%

No es necesario introducir ningún dato adicional.

Resultado I

Clicamos en resolver y esperamos el resultado:

El coste mínimo según nuestras restricciones es 2,53 €, inferior a los 2,77 € del supuesto inicial, por lo que el precio deseado (recomendado) es 7,97 €, inferior a los 8,73 €, lo que nos proporcionaría una rentabilidad de 0,76 € sobre el supuesto inicial. Este debería ser el objetivo de venta de nuestra sala. El MB sobre el Precio deseado es 1,12 €.

Estimación de venta para el coste máximo con el complemento Solver de Excel II

Este segundo supuesto es mucho más fácil, solo tenemos que duplicar la hoja CosteMínimo, cambiarle el nombre, clicar en Solver, el complemento nos guarda los datos introducidos, lo único que tenemos que hacer es cambiar Mín por Máx y clicamos en Resolver y aceptamos:

Resultado II

El resultado obtenido es el siguiente:

Como podemos comprobar, el coste se ha incrementado hasta los 2,99 €, por lo que el precio de 10,00 € comprometería la rentabilidad de nuestro negocio llegado el caso.

Sería muy interesante que comparaseis el resultado anterior con este último para analizar las diferencias y concienciar al equipo que el objetivo de ventas debe ser el anterior y nunca este. Claro está, siempre que podamos podemos modificar los platos o subir el precio de la carta.

Os dejo el enlace al vídeo donde muestro todo el proceso: Complemento solver de Excel II.

Espero que os haya resultado interesante.