Blog Cursos Excel para Empresas - Página 8 de 15

La herramienta Administrador de escenarios nos permite obtener en una tabla resumen un cálculo determinado en función de celdas cuyo valor es cambiante.

Administrador de escenarios en Excel. Análisis y si.

Escrito por Iván Antona en 18 mayo, 2016. Publicado en Herramientas Integradas.

Administrador de escenarios en Excel análisis y si.

La herramienta Administrador de escenarios en Excel nos permite obtener en una tabla resumen el resultado de un cálculo determinado en función de celdas cuyo valor es cambiante. Normalmente se definen 3 hipótesis (Pesimista, Realista, Optimista) y se varían los valores de las celdas cambiantes desde las tres perspectivas anteriormente indicadas.

Supongamos la compra de un vehículo nuevo. El concesionario nos ofrece la compra de nuestro vehículo viejo. Para ello debe tasarlo. Hemos realizado una búsqueda previa por Internet y hemos visto que actualmente ese modelo de vehículo se está vendiendo en el mercado de segunda mano entre 5000€ y 8000€. Entendiendo que el concesionario lo revenderá y como mínimo sacará 1000€ en la operación nos planteamos tres escenarios posibles de oferta que nos realizará el concesionario y que resolveremos con el Administrador de escenarios en Excel.

El concesionario nos oferta 2000€ por el vehículo.
El concesionario nos oferta 4500€ por el vehículo.
El concesionario nos oferta 6000€ por el vehículo.

Con estos datos y con el tipo de interés conocido de 7% y precio del vehículo nuevo de 20.000€ vamos a realizar mediante la herramienta Administrador de Escenarios de “Análisis de Hipótesis” un cálculo aproximado de la cuota que pagaremos a la financiera. Haremos el estudio a 72 meses (6 años).

Preparamos una tabla como la mostrada a continuación con los datos anteriores. El cálculo de la cuota se realiza restando al precio del vehículo la tasación, dividiendo entre el número de plazos y aplicando un interés del 7%.

Una vez preparada nuestra tabla de entrada haremos clic en la herramienta de “Administrador de escenarios”.

Nos aparecerá el asistente de escenarios. Haremos clic en “Agregar”

En el nombre de escenario escribiremos nuestro primer punto de vista “Optimista” y seleccionaremos la celda C4 que es donde se encuentra el valor de tasación que nos oferta el concesionario. Haremos clic en “Aceptar”. Seguidamente rellenamos el valor que queremos dar en el estudio para este punto de vista. En este caso 6000€.

Realizaremos la misma operación para los otros dos puntos de vista. Realista y pesimista. Finalmente tendremos completadas las distintas hipótesis para nuestro estudio.

Una vez cargados todos los datos haremos clic en aceptar. Y seleccionaremos la celda que contiene los cálculos de la cuota.

Finalizamos haciendo clic en “Resumen”. En una nueva hoja del libro se configura un resumen como el que se muestra a continuación.

Modificaremos sobre el informe los títulos de “Celdas cambiantes y Celdas resultado” para que la apariencia final sea más atractiva.

Función Contar en Excel.

Escrito por Iván Antona en 18 mayo, 2016. Publicado en Funciones, Funciones Estadísticas.

Función Contar en Excel. Para qué sirve.

La función contar en Excel permite contabilizar aquellas celdas de un determinado rango que contienen datos de tipo numérico. Esta función pertenece a la familia de funciones estadísticas. Esta función es muy utilizada dentro de otras funciones (anidada) como argumento en aquellas funciones en las que un determinado argumento se refiere a un número de filas que contenga un rango.

Argumentos de la función.

La función contar en Excel admite tantos argumentos como elementos tenga el rango que se desea evaluar. También puede recibir un único argumento que sea el rango completo a evaluar.

Valor 1: Es un valor del rango a evaluar
Valor 2: Es un valor del rango a evaluar
Valor n: Es un valor del rango a evaluar

Ejemplo de uso de la función.

En el siguiente ejemplo vamos a evaluar tres columnas que forman tres rangos.

El rango A1:A6, el rango C1:C6 y el rango E1:E6

El rango A1:A6

En el primer rango tiene datos en seis filas. Y podemos observar como el resultado de la función contar es 6. Todos los datos son de tipo numérico. El cero también se considera un valor numérico y en consecuencia se contabiliza también en la función contar.

=CONTAR(A1:A6)

El rango C1:C6

Vemos en este segundo rango que el resultado de la función es 3. Aunque hay datos en las seis celdas solamente tres de ellos son valores numéricos. La función contar solo toma en cuenta valores de tipo numérico. Existe una función capaz de evaluar todas aquellas celdas que contienen información independientemente del tipo de dato sea. Esta función es la función Contara()

El rango E1:E6

Finalmente en este rango vemos que la función devuelve como resultado 1. Esto es debido a que como en el caso anterior los valores de tipo “texto” no se evalúan. Además tampoco se evalúan aquellas celdas cuyo contenido es “vacío”

Función contar en Inglés.

La función en inglés se puede escribir como

=Count(Value1, Value2…ValueN)

La función O en Excel Comprueba el resultado lógico de cada uno de los argumentos. Si alguno es verdadero entonces el resultado final será verdadero.

Función O en Excel. Funciones lógicas.

Escrito por Iván Antona en 17 mayo, 2016. Publicado en Funciones, Funciones Lógicas.

Función O en Excel. Para qué sirve.

La función O en Excel Comprueba el resultado lógico de cada uno de los argumentos. Si alguno es verdadero entonces el resultado final será verdadero. Si todos los argumentos son falsos entonces el resultado final será FALSO. La función O pertenece a la familia de funciones lógicas.

La particularidad de la función “O” es que siempre que alguna de las comprobaciones que se evalúen dentro de ella se cumpla el resultado será “True”. Aunque a primera vista parece un poco enrevesado en realidad lo utilizamos en nuestra vida cotidiana constantemente. Por ejemplo en expresiones como “Si te llaman por teléfono o te envían un mail con relación a este tema por favor avísame”. Basta con que se cumpla una de las dos posibilidades para que se dispare la acción de avisar.

Una tabla resumen para dos argumentos se presenta a continuación. En la primera opción nos encontramos con dos resultados favorables. Podríamos haber recibido una llamada y un email. Ambas cuestiones se han cumplido y en consecuencia el resultado es “Verdadero” también denominado “True” o “Cierto”.

En el siguiente caso ni se ha recibido una llamada de teléfono ni un correo electrónico. En consecuencia el resultado final es “Falso” o también denominado “False”.

Para los dos casos siguientes se cumple alguna de las dos comprobaciones. O bien se ha recibido una llamada telefónica o bien se ha recibido un correo electrónico. Gracias a la particularidad de esta función el resultado final será “Verdadero”.

Se presenta un ejemplo numérico en el que se evalúan dos números. La condición aplicada es que ambos sean mayores o iguales a 5. Como se puede apreciar siempre que exista un 5 el resultado de la función devolverá “Verdadero”.

Función O en Excel. Argumentos.

La función O admite tantos argumento como comprobaciones lógicas queramos realizar. Hay que matizar que el resultado de esta función es lógico luego todos los argumentos deben dar como resultado “Verdadero” o “Falso”.

Prueba_lógica_1: Comprueba si se cumple la condición establecida.

Prueba_logica_2: Comprueba si se cumple la condición establecida.

Prueba_logica_n: Comprueba si se cumple la condición establecida.

Al igual que ocurre con el resto de funciones de tipo lógico hay que tener muy en cuenta el funcionamiento de los operadores lógicos que se presentan a continuación.

Ejemplo de uso de la función O en Excel.

En el ejemplo presentamos una tabla de estudiantes con las notas de dos exámenes parciales. Para poder aprobar la asignatura los alumnos deben haber aprobado ambos exámenes. Así que la comprobación lógica que se realizará será que la nota del primer parcial sea menor a cinco y la segunda comprobación que la nota del segundo parcial sea menor a cinco. Si en alguno de los casos se cumple alguna de las dos condiciones entonces la asignatura estará suspensa.

Función O en inglés.

La función O en inglés se denomina OR(logical01;logical02;….logicaln)

Descarga los ejemplos usados en esta página.

[easy_media_download url=»https://tecnoexcel.es/wp-content/uploads/2016/05/Funci%C3%B3n-O-en-Excel.xlsx» text=»Función O» color=»green_light»]

La función Si en Excel evalúa una condición. Si el resultado de la condición es “Verdadero” realizará una tarea si es falso realizará otra tarea.

Función Si en Excel. Funciones condicionales.

Escrito por Iván Antona en 17 mayo, 2016. Publicado en Funciones, Funciones Lógicas.

Función Si en Excel. Para qué sirve.

La función Si en Excel evalúa una condición si el resultado de esta evaluación es “Verdadero” entonces realizará una tarea. Sin embargo si el resultado de la evaluación es falso entonces realizará otra tarea diferente. Esta función pertenece a la familia de funciones lógicas. Si el precio de una determinada acción baja un 5% con respecto a un precio de compra determinado entonces realizaremos una venta del valor bursátil.

Es importante conocer determinados operadores en Excel para poder construir la función condicional “Si”.

Argumentos de la función.

La función si admite tres argumentos. El primero es estrictamente obligatorio y el resultado de la función si no se utilizan los otros dos argumentos entonces será “TRUE”/“VERDADERO” o “FALSE”/”FALSO”

Comprobación_lógica: Realiza una comprobación que como resultado me devolverá un “Verdadero” o un “Falso”

Valor_si_verdadero: Acciones que queremos desencadenar en caso de cumplirse la condición. Puede ser un mensaje de texto que introduciremos entre comillas o la llamada a otra función o un cálculo.

Valor_si_falso: Acciones que queremos desencadenar en caso de NO cumplirse la condición. Puede ser un mensaje de texto que introduciremos entre comillas o la llamada a otra función o un cálculo.

Ejemplo de uso de la función Si.

Volvemos al ejemplo anterior. Tenemos una inversión sobre un determinado valor bursátil “Acme SA”. Sabemos que las acciones se compraron al precio de 10€. Además tenemos marcados los siguientes objetivos:

Si el valor supera en un 10% el precio de compra lo venderemos para ganar un margen.
Si el valor pierde un 5% con respecto al precio de compra lo venderemos para minimizar la pérdida.

Con estas premisas se realiza un chequeo diario de los valores bursátiles para conocer su precio de cierre y evaluar el resultado de nuestra inversión y así poder mediante la condición “SI” tomar una decisión.

El análisis inicial será el siguiente:

El resultado esperado debe ser una tabla como la que se muestra a continuación. En donde aquellas cifras que cumplan la condición desencadenan un mensaje o bien de venta por beneficio o bien de venta por pérdida.

En este caso “anidaremos” una función Si dentro de otra función Si. Anidar una función dentro de otra significa que uno de los argumentos del Si inicial será otra función Si.

SI( Prueba lógica; “Valor si se cumple”; “Valor si no se cumple”). Valor si no se cumple = SI(Prueba lógica; “Valor si se cumple”; ”Valor si no se cumple”)

Si (Prueba lógica; Valor si se cumple;SI(Prueba lógica;Valor si se cumple;Valor si no se cumple)

Y traducido con nuestras condiciones podemos escribir lo siguiente:

Si(Precio de cierre >= 10€+10€*10%; “Venta por Beneficio”;SI(Precio de cierre <= 10€ – 10€*5%; “Venta por pérdida”;”Mantener”))

Traducido en Excel tenemos la siguiente expresión:

=SI(E2<=C2-(C2*5%);"Vender por pérdida";SI(E2>=C2+(C2*10%);"Vender por Beneficio";"Mantener"))

Función Si en inglés

La función Si en inglés se denomina

IF (Logical_test;Value_if_true;Value_if_false)

Descarga el ejemplo de uso de la función Si.

Puedes descargar el ejemplo aquí:

Función-Si-en-Excel

El cuadro de amortización en Excel primero se modela la hoja y después se hace uso de la función Pago de Excel. Pago Mensual.Número de Pago. Interés.

Cuadro de amortización en excel de interés fijo

Escrito por Iván Antona en 16 mayo, 2016. Publicado en Excel Avanzado, Funciones, Funciones Financieras.

Crear el cuadro de amortización en Excel.

En primer lugar se modela la hoja creando los siguientes encabezados para poder crear el cuadro de amortización en Excel.

Pago Mensual (Mes).
Número de Pago.
Interés Principal (Intereses).
Cuota Mensual.
Monto Restante.

A parte crearemos encabezados adicionales que posteriormente necesitamos para completar la tabla.

Monto del préstamo (Préstamo).
Interés nominal anual (Interés).
Interés nominal mensual.
Mensualidades o periodos (Periodos).

Bajo el encabezado “Mes” rellenaremos las dos primeras celdas con los meses uno y dos del préstamo. Dicho de otra manera serán los meses en los que nos pasarán los dos primeros recibos. En este ejemplo el primer recibo se pasa en el mes de marzo-2015 y el segundo recibo en el mes de abril-2015. Copiaremos el valor de las dos celdas y arrastrando completamos las siguientes celdas hasta llegar al mes de finalización del préstamo. En el ejemplo que puedes descargar al final de esta entrada finaliza febrero-2022.

Bajo el encabezado “Núm.Pago” se rellena con los valores desde 1 hasta 84 y corresponde con los vencimientos de las cuotas del préstamo. Como el préstamo del ejemplo tiene una duración de siete años y realizamos pagos mensuales o dicho de otra manera los vencimientos son mensuales entonces en total tenemos 12×7=84 vencimientos para nuestro cuadro de amortización en Excel.

Bajo el encabezado “Intereses” figuran los intereses que pagamos en cada cuota. En este ejemplo se utiliza el sistema de amortización francés para generar el cuadro de amortización en Excel y en la hoja podemos utilizar la “Función PagoInt” para averiguar el valor de cada celda. La “Función PagoInt” la completamos como se indica en la imagen.

El primer argumento de la función pagoint de Excel responde al interés mensual, el segundo argumento de la función es el número de vencimiento que para el ejemplo correspondería con 1 o lo que es lo mismo el primer recibo, el tercer argumento se refiere al número total de cuotas que previamente habíamos calculado como 84. Finalmente el último argumento es el monto total del préstamo que debe llevar signo negativo.

Para calcular los valores referidos al encabezado “Principal” vamos a utilizar la “Función PagoPrin de Excel” muy similar a la “Función PagoInt”. Esta función nos permite averiguar de cada cuota que pagamos al banco que cantidad es la destinada a amortizar el capital prestado sin tener en cuenta los intereses ya que los hemos calculado con la función PagoInt de Excel previamente.

El primer argumento de la “Función PagoPrin de Excel” corresponde con el interés nominal mensual. El segundo argumento se refiere al vencimiento o mes, para este ejemplo nos encontramos en primer recibo luego seleccionamos el número de pago 1.

El cuarto argumento se refiere al número total de periodos, meses o cuotas del préstamos que como hemos indicado anteriormente se trata de 84. Para finalizar, el último argumento, se trata del monto total del préstamo solicitado al banco o entidad financiera y que igual que en la función PagoInt debe ir con signo negativo.

El encabezado referido a “Cuota” nos indica la cantidad total que debemos abonar en el recibo que nos domicilia la entidad financiera o banco. Podemos conseguir este valor con la suma de los valores anteriores los intereses de la cuota y el principal de la cuota. Como se trata de un interés fijo el valor de todas las cuotas será el mismo pero al amortizar mediante el sistema francés vemos como cada vez destinamos menos cantidad de la cuota en el pago de intereses. Es decir, los intereses relativos al préstamo, se pagan de más a menos. Por el contrario el dinero destinado en la cuota a amortizar el proyecto es creciente y se paga de menos a más.

Para averiguar los valores del encabezado “Monto restante”. Simplemente restaremos al capital del préstamo la cantidad destinada de nuestras cuotas a su amortización en nuestro cuadro de amortización en Excel.

Cuadro_Amortizacion_Excel