Excel Avanzado

Excel Avanzado herramientas imprescindibles.

Logra el nivel de Excel avanzado ya conociendo estas 3 Herramientas imprescindibles Solver, Escenarios y Buscar objetivo en un nivel alto de la hoja de cálculo Excel.

1. Escenarios.
2. Buscar objetivo.
3. Solver.

Escenarios. Excel avanzado.

Un Escenario es una herramienta de Excel avanzado dentro del apartado de análisis del programa, lo que hace es calcular diferentes valores en función de las premisas de los posibles escenarios o situaciones hipotéticas que puedas plantear.

Son análisis realizados en Excel avanzado del tipo “¿Qué pasa si?” o “¿Qué pasaría si?, donde un escenario refleja:

• Ejemplo 1: Si cambia el tipo de interés de mi hipoteca cuál será el nuevo capital a devolver o en el mismo ejemplo cuál será la nueva cuota mensual a pagar.

• Ej. 2:cuál es la previsión de gastos dadas unas condiciones de producción.

• Ejemplo 3: cuál es el coste de producir un bien teniendo en cuenta la variación que pueden sufrir los precios de las materias primas.

Creando Escenarios con el programa, Excel avanzado lo que hace es partiendo de la información de los mismos, realizar un informe resumen de escenarios que muestran de forma agrupada las soluciones a las diferentes situaciones planteadas.

En Excel versiones 2007 y superiores la herramienta Escenarios se encuentra en la pestaña Datos, en el grupo de trabajo Herramientas de datos, Análisis Y si.

Desplegando la opción: Administrador de escenario.

Ahora tu puedes hacer este ejercicio sencillo y calcular cómo afecta a la cuota mensual de un préstamo un cambio en el tipo de interés.

 Los valores de partida del escenario son:

• Capital concedido por el banco: 100.000.
• Tipo de interés: 4%.
• Plazo en años 25.
• Cuota mensual a pagar: Será el resultado de una fórmula.

Ahora abre una hoja de Excel nueva y escribe lo siguiente en las celdas que te voy indicando:

A1: Capital inicial

A2: Tipo de interés

A3: Plazo en años

A4: Cuota mensual a pagar.

En la columna B escribe:

B1: 100.000

B2: 4%

B3:25

B4: la fórmula =PAGO(B2/12;B3*12;B1), el resultado 527,84 es la cuota mensual a pagar.

Excel 2019 a 2007 y desde Administrador de escenario, en la pestaña Datos, Herramientas de datos, Análisis Y si.

Resolución del ejemplo de Escenarios.

Desde el Administrador de escenarios, a la derecha  pulsa en el botón Agregar.

Aparece una nueva ventana para añadir el escenario, vas a suponer tres hipótesis,

Real: con los datos que tienes.

Optimista: el tipo de interés baja al 2%.

Pesimista: sube el tipo al 6%.

Real:

Nombre del escenario: Real.

Celdas cambiantes pulsa con el ratón en B2, que es donde está el tipo de interés, variable que cambia.

Todo lo demás puedes dejarlo igual.

Aceptar.

En la ventana siguiente indica los valores cambiantes, para este escenario escribe 4% porque es el supuesto Real.

Aceptar.

Optimista:

Pulsa de nuevo Agregar.

Escribe Optimista.

Análogamente al escenario anterior excepto en el paso de indicar los valores cambiantes que ahora has de poner 2%.

Pesimista:

Repite los pasos y en el último indica 6%.

A continuación pulsa Cerrar en la ventana Administrador de Escenarios.

Puedes añadir otras hipótesis según necesites siguiendo los mismos pasos.

Verás que en tu hoja de cálculo no ha cambiado nada.

Para ver los resultados de los Escenarios puedes verlos uno a uno o realizar el resumen de escenarios.

Escenarios Uno a uno.

Abre de nuevo la ventana Administrador de escenarios, sitúate en el llamado Optimista y pulsa en el botón Mostrar, en la hoja electrónica los valores cambian y te muestra los correspondientes a ese escenario, haz igual para el resto.

Resumen de escenarios.

Pulsa en el botón Resumen…

En la ventana que se abre deja todo como esta.

Pulsa Aceptar.

Se abrirá una nueva hoja de Excel en tu libro con el resumen de los datos obtenidos.

En la primera columna se llama valores actuales que son los del punto de partida y que en este ejemplo coinciden con los del escenario llamado Real.

Por otro lado también puedes obtener el resumen de escenarios en formato tabla dinámica, para ello en el paso anterior pulsa Informe de tabla dinámica de escenario y esta se abrirá en otra hoja nueva.

Así que aprovecho la ocasión para comentarte que tienes el libro ebook Tablas dinámicas entre mis artículos estrella.

Buscar objetivo.

Buscar objetivo en Excel te sirve para encontrar un resultado deseado mediante la modificación del valor de una determinada celda de la que va a depender el valor deseado.

Ejemplo sencillo.

Imagina que quieres comprar nueva maquinaria para la empresa que vale 1.200 euros y se ha decido el pago a plazos.

El número de plazos de pago que estableces con el vendedor es de 24 meses, a un tipo de interés del 10% te sale una cuota a pagar de 55.

Pasado un tiempo resulta que no puedes pagar esa cantidad mensual, solo puedes hacer frente al pago de 30 euros al mes, el número de los pagos a realizar por tanto ahora aumentan.

Así que con Buscar objetivo encontrarás rápidamente respuesta  a la siguiente pregunta ¿cuantos plazos mas son necesarios para terminar de pagar la maquinaria?

Otro ejemplo:

• Recibes un sueldo bruto al año de 20.000 euros y la retención por impuestos es del 15% (no hay otro tipo de descuentos).
• Imagina que puedes pedir una reducción del impuesto para que aumente la cantidad líquida a percibir, si quisieras recibir 18.500 euros líquidos al mes con Buscar Objetivo en Excel podrás calcular que tipo de retención a realizar.

Uso de Buscar Objetivo.

Ejemplo sencillo: Vendes una determinada mercancía que cuesta 20 euros , la cantidad vendida en el último año ha sido de 500 unidades, con ello has tenido unas ventas de 10.000 euros , que es el resultado de multiplicar 20×500=10.000.

Así que ahora quieres saber cuántas unidades has de vender para obtener unas ventas de 15.000 euros .

Pasos.

• Abre una hoja de cálculo Excel en blanco.

• Escribe en la columna A lo siguiente: En A1: Precio. En A2: Cantidad. En A3: Ventas. ·

• Escribe en la columna B: En B1: 20. En B2: 500. En B3: La formula B1xB2, te tiene que aparecer el resultado que es 10.000.

• A continuación has de hacer clic en la pestaña Datos.

• Después en el grupo de trabajo llamado Herramientas de análisis, haz clic en el triángulo negro al lado de Análisis Y si.

• Se despliegan una serie de opciones, pulsa en Buscar objetivo.

En la nueva ventana escribe los datos de la siguiente manera:

Definir la celda: haz clic sobre B3 que es la que quieres que se modifique y alcance la cifra de 15.000 dólares de ventas.

Con el valor: Escribe 15.000.

Para cambiar la celda: Pulsa en B2, que es donde está el dato sobre el número de unidades a vender. Aceptar.

La ventana cambia de aspecto, al igual que los datos de la hoja de cálculo, ahora puedes comprobar que para obtener 15.000 euros de ventas las ventas han de ser de 750 unidades, siendo constante el precio en 20 euros la unidad.

Además puedes hacer este otro ejemplo, (te recuerdo que puedes usar la acción de Deshacer para volver a la situación inicial).

Para ello con los mismos datos calcula cuánto tendrías que subir el precio para obtener la cifra de ventas de 15.000 euros , donde en este caso las unidades vendidas no cambian; resultado: 30 euros la unidad.

Solver.

Solver es una herramienta de análisis del programa Excel a nivel avanzado aplicado sobre todo en el mundo de las empresas.

Te sirve para calcular el valor de una celda que depende de diversos factores y donde a la vez existen unas restricciones que han de cumplirse.

La herramienta Solver de Excel realiza los cálculos para la resolución de problemas de programación lineal.

Así pues a partir de una función lineal a optimizar (encontrar el máximo o mínimo) y cuyas variables están sujetas a unas restricciones expresadas como inecuaciones lineales, el resultado que se busca es obtener valores óptimos bien sean máximos o mínimos.

Solver es un complemento de Excel por ello puede que no lo tengas activo en un primer momento, para tenerlo disponible basta con que sigas estos pasos:

Pasos en Excel 2019 a 2007.

1.- Botón de Office.

• Opciones de Excel.
• Complementos (en la parte izquierda).

2.- En la ventana que aparece, abajo en Administrar:

• Complementos de Excel, Ir.

Además puedes saber más sobre Excel avanzado leyendo el artículo sobre Complementos Excel.

Nota: A veces el complemento Solver ni siquiera está cargado en nuestro programa, según haya sido la configuración elegida al instalar inicialmente Excel, para activarlo tienes que hacerlo desde el CD de instalación de Office.

Ejemplo de problema a solucionar con Solver en Excel avanzado.

Una empresa fabrica dos artículos que reportan respectivamente un beneficio unitario, a maximizar, si bien has de tener en cuenta las restricciones por la cantidad de materia prima y la mano de obra disponible.

Así que de forma que no es posible sobrepasar determinadas cantidades, la empresa quiere conocer la cantidad que tiene que producir de cada uno de los artículos que fabrica para lograr la maximización del beneficio.

Pues bien tras especificar los datos en la hoja de cálculo:

Pasos solución Solver.

Los pasos en Excel avanzado para realizar un problema con Solver en Excel 2013, 2010 y 2007 son los siguientes:

• Realizar en la hoja de cálculo Excel el esquema del problema.
• Ir a Datos.
• Después en Análisis en la herramienta Solver.
• Al pulsar se abre una nueva ventana llamada Parámetros de Solver y es aquí donde tienes que indicar los diferentes datos.
• Celda objetivo, se desea lograr el máximo beneficio por eso este es el valor a maximizar.
• Hacer clic en Valor de la celda objetivo marcando la primera opción que pone Máximo.
• A continuación te pide las celdas a Cambiar o lo que es lo mismo especificar que celdas que son las variables del problema.
• Después te encuentras con la frase Sujetas a las siguientes restricciones, aquí has de introducir la restricciones a cumplir por las variables.
• Pulsa en el botón Agregar y vete especificando los datos necesarios.
• Sigue introduciendo el resto de restricciones.
• Si te equivocas puedes Cambiar o Eliminar las restricciones pulsando sobre la necesites y a continuación haciendo clic en el botón Cambiar… o Eliminar…
• Así que una vez están todos los datos informados pulsa en Resolver,  arriba a la derecha, Excel empieza a realizar los cálculos para resolver el problema, al encontrar la solución la muestra.
• Obtendrás la cantidad fabricada que lleva a la maximización del beneficio.
• Puedes Aceptar o volver a la situación de inicio.

Puedes tu mismo ampliar y completar la información una vez resuelto el problema, ya que por ejemplo puedes cambiar los valores de las restricciones para ver cómo esto afecta al resultado obtenido inicialmente.

Para finalizar este post sobre estas tres interesantes herramientas Excel avanzado me gustaría saber tu opinión sobre estas tres herramientas Excel y cual es la que te es más útil en tu día a día con Excel.

Si quieres contactarme no lo dudes, puedes escribirme al email de este site: hola@excelcontabilidadytic.com.

Finalmente por supuesto seguirme en las redes sociales:

FACEBOOK – TWITTER – LINKEDIN.