Solver y Buscar Objetivo2

EXCEL PARA RRHH www.professionalpersonal.
com
MODULO 2: ANÁLISIS DE DATOS PARA LA TOMA DE DECISIONES

7. BUSCAR OBJETIVO Y ANÁLISIS DE SENSIBILIDAD (SOLVER)
7.1 BUSCAR OBJETIVO
7.2 SOLVER
Vídeos
Video tutorial 25: Buscar Video tutorial 26: Solver

objetivo
Todos los derechos de este documento son propiedad de Professional & Personal. Cualquier copia o publicación del mismo
requerirá su autorización previa. Página 1
EXCEL PARA RRHH www.professionalpersonal.com
7. BUSCAR OBJETIVO Y ANÁLISIS DE SENSIBILIDAD (SOLVER)
Excel nos da dos opciones muy interesante para realizar cálculos de objetivos. Estos cálculos
nos permitirán calibrar cualquier dato que este referenciado con otro, y SOLVER llegará a la
solución más adecuada.
Las dos opciones para realizar esto son: 1. Análisis Y si 2. Solver
Veamos el primero, que es más simple para entender el concepto del segundo.
7.1 BUSCAR OBJETIVO
Imaginemos que tenemos en un Excel los datos de coste de un producto, y nuestra pretensión
es conocer qué margen debemos usar para llegar a un objetivo establecido de beneficio bruto:
El cálculo es el siguiente: partiendo de un coste de 1250 €, y calculando el precio final (sin iva)
del producto como (coste/100%-margen),
(coste/100% margen), y el beneficio final que nos queda, como Precio
Final-Coste de producto,
ucto, ¿qué margen necesitamos para tener un beneficio de 375 euros por
producto?
Para hacer este ejercicio, debemos hacer clic en DATOS/ANALISIS Y SI/BUSCAR OBJETIVO
En el listado que nos aparece nos pide la siguiente información:
1. Definir la celda objetivo, es decir, la celda que queremos que nos dé el resultado. En nuestro
ejercicio, queremos que nos de 375 € en la celda de beneficio, por tanto la celda objetivo es
F4.
2. Con el valor: 375 €
3. Para cambiar la celda: es decir, que celda debe modificar

modificar o generar para crear el resultado
esperado. La celda debe estar vincula de alguna forma con la celda objetivo. En nuestro caso,
será el margen a aplicar al producto.
Como vemos, Excel nos da la solución: 23,08%
Veamos otro ejemplo
¿Qué coste debe tener ner un producto para que al aplicar un 20% de margen nos dé un precio
final (sin iva) de 1500 €?
Imaginemos ahora que tenemos muchos más productos:
Y queremos saber qué cantidad de unidades tenemos que vender con esos márgenes para
llegar un nivel dee beneficio bruto de 100.000 €
En este caso, utilizaremos la opción SOLVER.
7.2 SOLVER
Para tener esta opción debemos descargarla, de la misma forma que hicimos con la
herramienta de análisis de datos: OPCIONES EXCEL/COMPLEMENTOS/IR/SOLVER
Realicemos ahora el cálculo
Los pasos a seguir son:
1. Escoger la celda objetivo: hemos creado una celda I14 que es la suma de los totales.
2. Crear el valor objetivo al que debe llegar la celda objetivo, es decir, 100.000 €
3. Señalar las celdas que deben cambiar o calcularse: en nuestro ejemplo, el número de
unidades de cada producto.
De esta forma Excel nos calculará la acción más adecuada para llegar al objetivo definido de
100,000 €:
Veamos Otro ejemplo de Solver, esta vez incluyendo restricciones.
Imaginemos que
ue vamos a realizar una entrega de un bonus discrecional a un conjunto de
empleados. Disponemos de 16000 € para repartir, y otorgaremos un bonus porcentual sobre el
salario. Veamos la tabla:
Nos hemos creado una tabla con las formulas creadas:
• Columna salario: Aparecen los salarios actuales

• Columna Subida%: es la columna donde queremos sacar los datos %
• Total bonus: el valor de esta celda está determinada por una fórmula, siendo esta la
de multiplicar el salario (d2) por el % (e2), y así respectivamente en todas las demás.
• Celda G1: está creado el sumatorio de todos los bonus, que recordemos, no debe
exceder de 16000 €.
La pregunta que nos hacemos es: ¿Cuál será una opción de reparto que no exceda de los
16000?
Para eso usaremos Solver. Para que nos ayude.

ayude. Pero en este caso concreto tenemos ciertas
restricciones que darle:
1. Que intente darnos % sin decimales
2. Todos deben tener un mínimo del 3% y un máximo del 7%
3. El empleado cuyo salario es actualmente de 10000 € le vamos a dar un 5%, y al de 14500 un

4%.
Usemos SOLVER
1. Celda OBJETIVO
2. Valor al que debe llegar esa celda
3. Cambiando las celdas…
4. Agregar restricciones
4.1 Número enteros
Una vez creada la primera regla, si queremos seguir incluyendo restricciones, le daremos a
Agregar. Si no, le daremos a Aceptar. En nuestro caso le damos a Agregar.
4.2 Todos mayor del 3%
4.3 Todos menor del 7%
4.4 El empleado con 10000 € que sea igual al 5%
4.5 El empleado con 14500 € que sea igual al 4%
Como ya hemos terminado todas las restricciones, podemos dar a ACEPTAR.
Solo nos queda dar a RESOLVER
Lo que podemos observar es que ha cumplido todos las restricciones menos la de darnos
número sin decimales. Es imposible para Solver llegar a 16000 sin usar decimales.
decima Si nos vale
esta solución la usaremos, si no, podemos usar un segundo modelo que se ajuste más a
nuestros requerimientos:
1. Uso de modelo lineal (en Excel 2010 se denomina Simplex LP, y se encuentra justo debajo
de la pantalla de restricciones). En Excel
Excel 2007 los haremos de la siguiente forma:
• Volver a entrar en Solver y hacer clic en el botón OPCIONES
• Adoptar modelo lineal y aceptar
Si ahora resolvemos:
Podremos ver que para llegar a la solución de 16000 euros, hay uno de los valores que
q debe
incluirle decimales. El resto están perfectamente. Como nos está dando al final lo que
queremos, que son bonus totales sin céntimos de por medio, podríamos coger esta solución
como válida.
VER TUTORIAL

Solver y Buscar Objetivo2

Cargado por

Copyright:

Formatos disponibles

Solver y Buscar Objetivo2

Cargado por

Información del documento

Título original

Derechos de autor

Formatos disponibles

Compartir este documento

Compartir o incrustar documentos

Opciones para compartir

¿Le pareció útil este documento?

¿Este contenido es inapropiado?

Copyright:

Formatos disponibles

Solver y Buscar Objetivo2

Cargado por

Copyright:

Formatos disponibles

EXCEL PARA RRHH www.professionalpersonal.

MODULO 2: ANÁLISIS DE DATOS PARA LA TOMA DE DECISIONES

7.1 BUSCAR OBJETIVO

Video tutorial 25: Buscar Video tutorial 26: Solver

7. BUSCAR OBJETIVO Y ANÁLISIS DE SENSIBILIDAD (SOLVER)

Las dos opciones para realizar esto son: 1. Análisis Y si 2. Solver

7.1 BUSCAR OBJETIVO

En el listado que nos aparece nos pide la siguiente información:

2. Con el valor: 375 €

3. Para cambiar la celda: es decir, que celda debe modificar

Como vemos, Excel nos da la solución: 23,08%

Veamos otro ejemplo

Imaginemos ahora que tenemos muchos más productos:

En este caso, utilizaremos la opción SOLVER.

Realicemos ahora el cálculo

Los pasos a seguir son:

Veamos Otro ejemplo de Solver, esta vez incluyendo restricciones.

Nos hemos creado una tabla con las formulas creadas:

• Columna salario: Aparecen los salarios actuales

Para eso usaremos Solver. Para que nos ayude.

1. Que intente darnos % sin decimales

2. Todos deben tener un mínimo del 3% y un máximo del 7%

3. El empleado cuyo salario es actualmente de 10000 € le vamos a dar un 5%, y al de 14500 un

2. Valor al que debe llegar esa celda

3. Cambiando las celdas…

4.1 Número enteros

4.2 Todos mayor del 3%

4.3 Todos menor del 7%

4.4 El empleado con 10000 € que sea igual al 5%

4.5 El empleado con 14500 € que sea igual al 4%

Como ya hemos terminado todas las restricciones, podemos dar a ACEPTAR.

Solo nos queda dar a RESOLVER

• Volver a entrar en Solver y hacer clic en el botón OPCIONES

• Adoptar modelo lineal y aceptar

También podría gustarte