Excel Solver: qué es y cuándo debe usarlo

Las herramientas de hoja de cálculo de Excel, como Goal Seek, que cambian una sola variable, son útiles, pero desafortunadamente la mayoría de los problemas en los negocios no son tan fáciles. Por lo general, enfrentará fórmulas con al menos dos y, a veces, decenas de variables. A menudo, un problema tendrá más de una solución, y su desafío será usar Excel para encontrar la solución óptima (es decir, la que maximice las ganancias, minimice los costos o coincida con otros criterios). Para estos desafíos más grandes, necesita una herramienta más musculosa. Excel tiene la respuesta: Solver. Excel Solver es un sofisticado programa de optimización que le permite encontrar soluciones a problemas complejos que de otro modo requerirían un análisis matemático de alto nivel.

Entendiendo Excel Solver

Solver, como Goal Seek , utiliza un método iterativo para realizar sus cálculos. El uso de la iteración significa que Excel Solver prueba una solución, analiza los resultados, prueba otra solución, etc. Sin embargo, esta iteración cíclica no es solo una conjetura por parte de Solver. Eso sería una tontería. No, Excel Solver examina cómo cambian los resultados con cada nueva iteración y, a través de algunos procesos matemáticos sofisticados (que, afortunadamente, ocurren en segundo plano y pueden ignorarse), generalmente puede decir en qué dirección debe dirigirse hacia la solución.

Las ventajas de Excel Solver

Sí, Goal Seek y Solver son iterativos, pero eso no los hace iguales. De hecho, Excel Solver aporta una serie de ventajas a la mesa:

• Excel Solver le permite especificar varias celdas ajustables. Puede utilizar hasta 200 celdas ajustables en total.
• Excel Solver le permite configurar restricciones en las celdas ajustables. Por ejemplo, puede decirle a Solver que busque una solución que no solo maximice las ganancias, sino que también satisfaga ciertas condiciones, como lograr un margen bruto entre el 20 y el 30 por ciento o mantener los gastos por debajo de $ 100,000. Se dice que estas condiciones limitan la solución.
• Excel Solver busca no solo el resultado deseado (el "objetivo" en Goal Seek) sino también el óptimo. Por ejemplo, buscar un resultado óptimo puede significar que puede encontrar una solución que sea la máxima o mínima posible.
• Para problemas complejos, Solver puede generar múltiples soluciones . A continuación, puede guardar estas diferentes soluciones en diferentes escenarios.

¿Cuándo debería utilizar Excel Solver?

Bien, disparemos directamente aquí: Excel Solver es una herramienta poderosa que la mayoría de los usuarios de Excel no necesitan. Sería excesivo, por ejemplo, usar Solver para calcular la ganancia neta dadas las cifras de ingresos y costos fijos. Sin embargo, muchos problemas requieren nada menos que el enfoque de Solver. Estos problemas cubren muchos campos y situaciones diferentes, pero todos tienen las siguientes características en común:

• Tienen una sola celda de objetivo (también llamada celda de destino ) que contiene una fórmula que desea maximizar, minimizar o establecer en un valor específico. Esta fórmula podría ser un cálculo como los gastos totales de transporte o la ganancia neta.
• La fórmula de la celda objetivo contiene referencias a una o más celdas variables (también llamadas incógnitas o celdas cambiantes ). Solver ajusta estas celdas para encontrar la solución óptima para la fórmula de la celda objetivo. Estas celdas variables pueden incluir elementos como unidades vendidas, costos de envío o gastos de publicidad.
• Opcionalmente, hay una o más celdas de restricción que deben satisfacer ciertos criterios. Por ejemplo, puede requerir que la publicidad sea menos del 10 por ciento de los gastos totales, o que el descuento para los clientes sea una cantidad entre el 40 y el 60 por ciento.

Por ejemplo, la imagen a continuación muestra un modelo de datos de hoja de trabajo que está todo configurado para Excel Solver. El modelo muestra los ingresos (precio multiplicado por las unidades vendidas) y los costos de dos productos, la ganancia producida por cada producto y la ganancia total. La pregunta que debe responderse aquí es la siguiente: ¿Cuántas unidades de cada producto deben venderse para obtener una ganancia total de $ 0? Esto se conoce en los negocios como un análisis de equilibrio.

El objetivo de este modelo de datos es encontrar el punto de equilibrio (donde la ganancia total es $ 0).

Suena como una tarea sencilla de búsqueda de objetivos, pero este modelo tiene un aspecto complicado: los costos variables. Normalmente, los costos variables de un producto son su costo unitario multiplicado por el número de unidades vendidas. Si producir el producto A cuesta $ 10 y vende 10,000 unidades, los costos variables de ese producto son $ 100,000. Sin embargo, en el mundo real, estos costos a menudo se mezclan entre varios productos. Por ejemplo, si ejecuta una campaña publicitaria conjunta para dos productos, esos costos corren a cargo de ambos productos. Por lo tanto, este modelo asume que los costos de un producto están relacionados con las unidades vendidas del otro. Aquí, por ejemplo, está la fórmula utilizada para calcular los costos del tablero de dardos inflable (celda B8):

= B7 * B4 - C4

En otras palabras, los costos variables del tablero de dardos inflable se reducen en un dólar por cada unidad vendida del pulidor de perros. Los costos variables de este último usan una fórmula similar (en la celda C8):

= C7 * C4 - B4

Tener los costos variables relacionados con múltiples productos coloca este modelo de datos fuera de lo que puede hacer Goal Seek, pero Solver está a la altura del desafío. Aquí están las celdas especiales en el modelo que usará Solver:

• La celda objetivo es C14; el beneficio total y la solución objetivo para esta fórmula es 0 (es decir, el punto de equilibrio).
• Las celdas cambiantes son B4 y C4, que contienen el número de unidades vendidas de cada producto.
• Para las restricciones, es posible que desee agregar que las dos celdas de beneficio del producto (B12 y C12) también deben ser 0.

Cargando el complemento Excel Solver

Un complemento es un software que agrega una o más funciones a Excel. La instalación de complementos le brinda características adicionales de Excel que no están disponibles en la cinta de forma predeterminada. El software complementario incluido se incluye con Excel, pero no se instala automáticamente al instalar Excel . Varios complementos vienen de serie con Excel, incluido Solver, que le permite resolver problemas de optimización.

Los complementos incluidos se instalan mediante el cuadro de diálogo Opciones de Excel; puede encontrarlos en la sección Complementos. Una vez instalados, los complementos están disponibles de inmediato. Suelen aparecer en una pestaña relacionada con su función. Por ejemplo, Solver aparece en la pestaña Datos.

Estos son los pasos a seguir para cargar el complemento Excel Solver:

Elija Archivo → Opciones.
Aparece el cuadro de diálogo Opciones de Excel.

Elija complementos.

En la lista Administrar, seleccione Complementos de Excel y luego seleccione Ir.
Excel muestra el cuadro de diálogo Complementos.

Seleccione la casilla de verificación Complemento Solver

Haga clic en Aceptar.
Excel agrega un botón Solver al grupo Analizar de la pestaña Datos.

Eche un vistazo para descubrir cómo utilizar Excel Solver .