Buscar Objetivo

Por favor compra el curso antes de comenzar esta lección.

Buscar Objetivo – Ejemplo 1

La herramienta Buscar objetivo es un instrumento de Excel que resuelve ecuaciones de una variable. Se suele utilizar para hallar el valor que debe tomar una variable específica, incluida en una fórmula, para igualar esta última a un resultado determinado. De otra forma, nos va a servir para resolver ecuaciones con una incógnita.

Veamos su operativa a través de del ejemplo de la empresa de renting:

Una empresa financiera dedicada al renting desea calcular el importe del alquiler del autobús que debería cobrar, de forma que sea cual sea la periodicidad de cobro, esta renta constante le proporcione un tipo de interés anual del 8 por 100.

Empezaremos calculando el importe del alquiler anual. Para ello introducimos los siguientes datos en el modelo:

  • Para la celda B3: 27.200 (aunque su importe va a ser modificado, por lo que poco importa el valor).
  • La celda B4: 1
  • Celda B5: 0
  • La celda B6: 8
  • Celda B7: 3.000
  • Y por último, la celda B8: 8%
buscar-objetivo
¿Qué importe anual nos daría como resultado un valor actual de 170 mil euros?

Vamos a intentar averiguar qué renta anual sería la que correspondería para que obtuviéramos un valor actual igual a 170.000 euros, que imaginemos que es el precio que pagó por él la empresa de renting. Para ello nos situamos en la celda E3, y nos dirigimos a la pestaña “Datos”,  desplegamos el botón de “Análisis de hipótesis” y seleccionamos “Buscar objetivo”.

buscar-objetivo

A continuación, escribimos en el cuadro diálogo de Buscar Objetivo la ubicación de la celda en la que queremos que aparezca el valor actual de 170.000 (como el valor actual lo calcula en negativo, pondremos dicha cantidad precedida del signo menos para convertirla en negativa) y cuál será la celda dependiente que necesitamos que cambie de valor:

configuración-buscar-objetivo
Estableceremos el valor deseado en la celda y el valor a cambiar.

Con ello le estamos diciendo que queremos que la celda E3 tome el valor de -170.000 y que para ello la celda que debe buscar el valor adecuado para alcanzar este valor sea el valor contenido en la celda B3. Por supuesto debe existir una relación entre la celda cuyo valor queramos definir y la celda que tiene que cambiar de valor.

resultado-buscar-objetivo
Tendremos el valor de la renta para la cual el valor actual es de 170 mil euros.

Dado el resto de condiciones constantes, para obtener un valor actual de  170.000 euros la anualidad que se debe satisfacer es de 29.300,47 euros durante los próximos 8 años. Como el coste del autobús para la empresa de renting fue de 170.000 euros, la anualidad mínima (también denominado punto muerto o de no retorno) que deberá exigir a sus clientes es precisamente la que acabamos de calcular.

 

Buscar objetivo – Ejemplo 2

Supongamos que nuestra empresa quiere introducir una nueva línea de producto y quiere que el producto devuelva un margen del 30 por 100 durante el primer año. Supongamos también que se encuentra también en las siguientes circunstancias:

  • Las ventas durante el año serán de 60.000 unidades.
  • El coste total fijo será de 800.000 euros.
  • El coste por unidad será de 10,25 euros.

Teniendo en cuenta esta información, queremos saber qué punto de precio va a producir el 30 por 100 de margen.

La figura siguiente muestra una hoja definida para manejar esta situación. Introducimos un valor inicial de 1,00 euros en la celda Precio por unidad (C2).

buscar-objetivo
¿Cuánto deberemos vender para obtener un determinado beneficio o margen?

Para buscar el valor que tomará el precio de venta unitario para que el margen de beneficio sea del 30% usaremos la herramienta de “Buscar Objetivo”.

En primer lugar, nos situamos sobre la celda que indica el margen (C12)  para que automáticamente Excel introduzca esta celda en el campo de “Definir la celda”, si nos hubiéramos situado en otra celda antes de hacer clic sobre buscar objetivo, cambiaremos la celda a la deseada en la ventana de la herramienta.

parametros-buscar-objetivo
Estableceremos el porcentaje deseado y la variable a modificar

Completando el resto de los campos, obtendremos el siguiente resultado:

resultado-buscar-objetivo

Como vemos, el resultado que hemos obtenido no es exacto, pues el margen a encontrar era del 30% mientras que el que nos ha devuelto es del 29,99%.  Ello es debido al sistema de cálculo de Excel, que busca el resultado mediante iteraciones.

Podemos ver las condiciones de iteración a través de las “Opciones de Excel” en la pestaña “Fórmulas

calculo-iterativo

El cálculo iterativo lo vimos en la lección sobre referencias circulares

 

El “número máximo de iteracciones” indica cuantos valores va a introducir Excel. A mayor iteraciones, más afinado será nuestro resultado aunque más tiempo empleará Excel en la búsqueda.

Y el “cambio máximo” significa que Excel llegará a la solución, aunque no sea exacta, si la diferencia entre el valor buscado y el obtenido es menor que al cambio máximo. Si reducimos el valor, tendremos unos resultados más exactos, por el contrario, si estamos dispuesto a aceptar una mayor desviación podremos tolerar un cambio máximo más grande.

Descargar archivo adjunto
Podemos descargar el archivo Excel que hemos utilizado en esta lección desde el siguiente enlace:

 

Etiquetas de lecciones: buscar objetivo, escenarios, Excel
Volver a: Excel para Contables y Controllers > Escenarios y análisis de sensibilidad