Empresarial y Financiero NIVEL AVANZADO

(1)

Rosa Rodríguez

Curso de Excel

Empresarial y Financiero

NIVEL AVANZADO

SESION 2: INDICE

¨ 

ANALISIS DE SENSIBILIDAD (3h)

¤ 

Validación de datos

n 

Restricciones a la entrada de datos

n 

Lista Dependiente

n 

Administrador de nombres

¤ 

Análisis de Sensibilidad

n 

Tabla de Datos

n 

Administrador de Escenarios

n 

Generación de Informes

(2)

Validación de Datos

¨  Por ejemplo, es posible que desee

restringir la entrada de datos a números de 5 cifras ( código postal) un intervalo determinado de fechas, limitar las opciones con una lista, asegurarse de que sólo se escriben números enteros positivos o establecer valores máximos a una celda

¨  Si no se cumplen las restricciones nos

enviará un mensaje de error.

La validación de datos permite establecer restricciones respecto a los datos que se pueden o se deben escribir en una celda e impedir que los usuarios escriban datos no válidos.

25/02/14

Validación de datos

¨  La validación de datos es

sumamente útil cuando desea compartir un libro con otros miembros de la organización y desea que los datos que se escriban en él sean exactos y

Las opciones de validación de datos se encuentran en la ficha Datos, en el grupo Herramientas de datos. Se configura en el cuadro de diálogo Validación de datos.

(3)

Validación de Datos

Puede usar la validación de datos entre otras aplicaciones

para:

¨  Restringir la entrada de datos a un número entero dentro de límites.

Por ejemplo, limitar la entrada a números enteros entre 1 y 100.

¨  Restringir la entrada de datos a un número decimal dentro de

límites. Por ejemplo, limitar la Altura entre 0,5 m y 2,5 m.

¨  Restringir la entrada de datos a valores de una lista desplegable.

Por ejemplo, seleccionar el color de ojos : Azul, Negro, Marrón y Otros.

¨  Restringir la entrada de datos a una fecha dentro o fuera de un

período de tiempo,Por ejemplo, puede especificar un período de tiempo entre la fecha actual y los 3 días siguientes.

25/02/14

Validación de Datos

Puede usar la validación de datos entre otras

aplicaciones para:

¨  Restringir la entrada de datos a texto de una longitud específica.

Por ejemplo, puede limitar el texto permitido en una celda a 10 caracteres o menos.

¨  Calcular qué se permite según el contenido de otra celda

¨  Usar una fórmula para calcular qué se permite, Por ejemplo, puede

especificar un límite mínimo de deducciones de dos veces el número de hijos en una celda específica.

25/02/14

(4)

Restringir la entrada de datos a un

número entero dentro de límites

¨  Necesitamos completar las 3 fichas del cuadro de dialogo:

Para limitar la entrada de datos a números enteros entre 1 y 100

Obligatoria (para establecer los criterios )

Opcional

(establecer el Mensaje de Entrada )

Opcional ( Mensaje de Error )

25/02/14

Restringir la entrada de datos a un

número entero dentro de límites

¨  Necesitamos completar las 3 fichas del cuadro de dialogo:

(5)

Ejercicio: Restringir la entrada de datos a un

número decimal dentro de límites

¨ 

Valide los datos de entrada de una celda en la que se

indique la Altura en metros del usuario.

¨ 

Valores mínimos 0,5 m y máximo y a 2.30 m de altura.

25/02/14

Restringir la entrada de datos a

valores de una lista desplegable

¨  Para crear la lista desplegable, tiene

que introducir los elementos de la lista en una columna.

¨  En el cuadro de diálogo Validación de

datos, haga clic en la pestaña Configuración. En el cuadro Permitir, seleccione Lista.

Probablemente la creación de listas desplegables es uno de de los usos más comunes de la Validación de datos.

25/02/14

(6)

Restringir la entrada de datos a

valores de una lista desplegable

¨  Haga clic en el cuadro Origen y, a

continuación seleccione los elementos de la lista. También podría escribirlos separados por el carácter separador de listas de Microsoft Windows (comas de forma predeterminada).

25/02/14

Restringir la entrada de datos a

valores de una lista desplegable

¨  Asegúrese de que esté activada la casilla de verificación Celda con lista desplegable.

¨  Para especificar cómo desea administrar los

valores en blanco (nulos), active o desactive la casilla de verificación Omitir blancos. Permite entradas vacías.

REPITA EL EJERCICIO PARA LA CELDA COLOR DE PELO

(7)

Restringir la entrada de datos a

valores de una lista desplegable

25/02/14

Restringir datos a una fecha

dentro de un período de tiempo

¨  Creemos un campo Fecha de

Nacimiento,

¨  Para asegurarnos que es una

fecha valida, podemos decir al usuario que la fecha introducida sea inferior al día actual.

25/02/14

(8)

Usar fórmulas para las reglas de

validación

¨ 

Utilizar la Validación de

datos no es compleja. Pero

la autentica potencia de

esta función se hace

patente cuando emplea

fórmulas en la validación

de datos.

¨ 

Puede especificar la

formula en el cuadro de

dialogo seleccionando la

opción Personalizada de la

lista desplegable Permitir

NOTA: La fórmula que especifique debe ser una formula lógica que devuelva Verdadero o Falso. Si la Formula es Verdadero los datos se consideran validos. Si la formula es FALSO sale el mensaje de error.

25/02/14

Ejemplo: Aceptar solamente texto

¨ 

Para que una celda o

un rango sólo acepte

texto ( no valores ),

use la siguiente

fórmula de validación

(9)

Ejemplo: Limitar la longitud

¨  Para que una celda o un rango

sólo acepte un numero limitado de caracteres, por ejemplo 5, use la siguiente fórmula de validación

¨  =largo(B2)=5

25/02/14

Ejemplo: Unir varias condiciones

¨  Unimos varias condiciones con la Función Y. Por ejemplo

comprobemos que el NIF es válido, es decir tiene 9 caracteres o menos y el ultimo carácter es una letra.

Use la siguiente fórmula de validación

=+Y(LARGO(H10)=9;(EXTRAE(H10:H10;1;8)/1)>1)

En esta segunda parte coge los 8 caracteres primeros y comprueba

que es un numero

25/02/14

(10)

Aceptar valores que no excedan de un

total

¨  Veamos una sencilla hoja de

calculo de presupuesto, con las cantidades de los elementos del presupuesto B1:B4. El

presupuesto total está en la celda E3 y el usuario está intentando introducir un valor en la celda B4 .

¨  La siguiente fórmula de

validación asegura que la suma de los elementos del presupuesto no excedan el total disponible.

25/02/14

Crear una lista dependiente

¨  Hemos visto que la validación de datos se puede utilizar para crear una

lista desplegable en una celda.

¨  Ahora vamos a utilizar la lista desplegable para controlar las entradas

que aparecen en una segunda lista dependiente.

¨  Así, dada una lista de ciudades, y una lista de sedes pertenecientes a estas

ciudades. Una vez que seleccione una ciudad aparecerá la lista de las sedes de esa ciudad

(11)

Administrador de Nombres

25/02/14

¨  Necesitamos asignar nombres a un rango

¨  Vamos a la barra de formulas y al administrador de nombres

¨  Generamos la lista CIUDAD y

hacemos referencia a los elementos.

¨  Generamos la lista MADRID

¨  Generamos la lista BARCELONA etc.

Administrador de Nombres

25/02/14

¨ 

Creamos la lista de las

ciudades

¨ 

Creamos tres listas con las

(12)

Validamos datos para la lista Ciudad

¨ 

Para cargar una lista,

vamos a validar datos.

¨ 

Cargamos la lista

Ciudades

Ya solo queda validar la lista dependiente, en la sede, para ello necesitamos la funcion INDIRECTO( )

25/02/14

Validamos datos para la lista Ciudad

Ya solo queda validar la lista dependiente, en la sede, para ello necesitamos la funcion INDIRECTO( )

Cada vez que seleccionemos una ciudad , aparecerá el listado correspondiente en la SEDE

(13)

Cree un formulario

¨  usuario: valide que es menor de 8 caracteres

¨  email: valide que el correo contiene un @ y un .

¨  Nif: valide que contiene 9 caracteres y una letra

¨  fecha nacimiento: valide que es una fecha valida

¨  Idioma cree una lista de idiomas

¨  nombre y apellidos: valide que es texto

26/02/14

Análisis “Y si”, Análisis de Sensibilidad

¨  El análisis “Y si” análisis de Sensibilidad nos soluciona preguntas como:

¤  ¿qué ocurre si el tipo de interés del préstamo disminuye del 5% al 3%?

¤  ¿Y si elevamos el precio de nuestro producto un 5%.

¨  Excel proporciona prácticas herramientas para ayudarnos en el análisis de

escenarios.

¨  Para efectuar el análisis Y si utilizando Excel tenemos tres opciones:

¤  Y si Manual

¤  Tablas de Datos

¤  Administrador de escenarios

25/02/14

(14)

Y si Manual

PRESTAMO FINANCIACION COCHE CELDAS DE ENTRADA

Precio de Compra 35000

Plazo en Años 5

Tipo de Interes 4%

CELDAS DE RESULTADO

PAGO MENSUAL 644.58 €

PAGO INTERES 116.67 €

PAGO DE PRINCIPAL 527.91 €

PRESTAMO FINANCIACION COCHE CELDAS DE ENTRADA

Precio de Compra 35000

Plazo en Años 5

Tipo de Interes 7%

CELDAS DE RESULTADO

PAGO MENSUAL 693.04 €

PAGO INTERES 204.17 €

PAGO DE PRINCIPAL 488.88 €

El análisis manual no necesita muchas explicaciones. Se basa en la idea de que tiene una o más celdas de entrada que afectan a una o mas celdas de formulas.

25/02/14

Crear una tabla de datos de una entrada

Una tabla de datos de una entrada muestra los resultados de una o mas formulas para valores multiples en una celda de entrada sencilla.

¨ 

La tabla hay que configurarla

manualmente.

La columna de la izquierda contiene los distintos valores

La fila superior contiene referencias a formulas ubicadas en otro sitio de la

(15)

Crear una tabla de datos de una entrada

Una tabla de datos de una entrada muestra los resultados de una o mas formulas para valores multiples en una celda de entrada sencilla.

¨ 

La tabla hay que configurarla

manualmente.

La columna de la izquierda contiene los distintos valores de la celda de entrada sencilla.

La fila superior contiene referencias a formulas ubicadas en otro sitio de la hoja.

25/02/14

Crear una tabla de datos de una entrada

¨  Para crear la tabla seleccione el rango donde va a rellenar los datos y luego

selecciones Datos/Herramientas de datos/tabla de datos.

Debido a que los valores que varían están en la columna, situamos la variable en la celda de entrada columna. Como es la única que varía dejamos celda de

(16)

Crear una tabla de datos de una entrada

¨ 

Utilizando esta tabla puede ver rápidamente los valores

calculados del préstamo para diferentes tipos de interés.

Una vez construida puede modificar los valores

del tipo de interés 25/02/14

Tabla de datos de doble entrada

Como su nombre indica la Tabla de datos de dos entradas nos permite variar dos celdas a la vez.

Nos dará los resultados de una función cambiando 2 de sus argumentos.

La principal diferencia es que solamente nos mostrará los resultados de una formula a un tiempo.

La celda superior de la izquierda de la tabla contiene la referencia a la formula de resultado sencillo

Cómo varía el pago mensual en función del

tipo de interés y del plazo?

(17)

Tabla de datos de doble entrada

El ejemplo de

esta hoja nos muestra el pago mensual de prestamo para distintas combinaciones de tipo de interes y de plazo.

La principal diferencia es que solamente nos mostrara los resultados de una formula a un tiempo. La celda superior de la izquierda de la tabla contiene la referencia a la formula de resultado sencillo

25/02/14

Repetir el proceso para

26/02/14

(18)

Administrador de escenarios

¨  Las tablas de datos son útiles, pero tienen limitaciones:

¤  Pueden variar sólo una o dos celdas de entrada a un tiempo

¤  El proceso de configuración de una tabla no es intuitivo

¤  Una tabla de doble entrada muestra únicamente los resultados de una

celda de fórmula.

¨  El administrador de escenarios facilita automatizar los modelos ¿qué

pasaría si?. Puede almacenar distintos conjuntos de valores de entrada (celdas cambiantes) para cualquier numero de variables y darle un nombre a cada conjunto.

¨  Por ejemplo, sus previsiones de ventas pueden depender de muchos

factores. Podremos definir 3 escenarios: optimista, normal, pesimista. Veamos……

Definir escenarios

¨  Suponga el siguiente ejemplo sencillo de modelo de producción:

¨  La hoja contiene 2

celdas de entrada : coste hora y coste materiales

¨  La empresa produce

tres productos y cada uno requiere un numero de horas diferentes y una cantidad de material diferente.

(19)

Definir escenarios

Las variables que tiene que calcular son: - Coste producción

- Beneficio por unidad - Beneficio total por producto - Beneficio total

25/02/14

Definir escenarios

¨ 

La junta directiva está intentando predecir el beneficio total

pero no sabe cuales serán los costes de producción , ha

identificado tres escenarios

¤ El mejor de los casos: costes más bajos

¤ El peor de los casos: costes más altos

¤ El más probable

Escenario Coste Hora Coste Materiales

Mejor 30 57

Peor 38 62

Probable 34 59

25/02/14

(20)

Administración de escenarios

¨ 

El administrador de escenarios

se encuentra en la pestaña

Datos.

¨ 

Si abre el cuadro de dialogo

Administrador de Escenarios por

primera vez le dice que no hay

escenarios definidos

¨ 

Para añadir un escenario haga

clic en Agregar.

26/02/14

Definir escenarios

¨  El cuadro de Agregar Escenario

tiene cuatro partes:

1)  Nombre de escenario, puede

darle cualquier nombre

2)  Celdas cambiantes ( Conviene

asignarle un nombre a las celdas Si ha creado un nombre es posible escribir el nombre)

3)  Comentario, por defecto viene el

nombre de quien lo creo, se puede cambiar

4)  Protección, proteger un escenario

(21)

Nombre Celdas

¨ 

Recuerde que para

dar nombre a las

celdas le tiene que

escribir el nombre en

el cuadro de nombres

26/02/14

Nombre Celdas

¨ 

O bien utilizar “Asignar nombre”

26/02/14

(22)

Definimos cada escenario

25/02/14

Definir el escenario implica introducir los valores para cada celda

cambiante

Mostrar escenarios

¨ 

Después de definir todos

los escenarios , seleccione

uno de ellos y haga clic en

el botón Mostrar.

¨ 

Excel insertará los valores

correspondientes en las

celdas cambiantes y

calculará la hoja para

mostrar los resultados.

(23)

Ver resultados

Generar un informe

Debe seleccionar las

celdas en las que está

interesado

Excel crea una nueva

hoja con el resumen

25/02/14

(24)

Informe Resumen

Ejercicio/ Análisis de Sensibilidad

¨  Suponga el siguiente proyecto (en miles de €) de 4 años de vida, desembolso

inicial 60, costes fijos anuales 30, Ingresos previstos para el primer año 400, costes variables para el primer año 300. Se estima un incremento anual ventas del 5% y de los costes variables del 3%. La tasa de descuento 10.56%