Concepto Básicos de Análisis de Datos

1.    Análisis con Datos / Tabla

Una tabla de datos es un grupo de celdas de una hoja tal que al realizar cambios en algunos valores constantes se observ
efecto en otras celdas dependientes.

Estamos ante una herramienta útil para el análisis de la sensibilidad en Excel.

1. TABLA DE UNA VARIABLE: Utilizada cuando se quiere comprobar cómo afecta un valor a una o varias fórmulas

2. TABLA DE DOS VARIABLES: Utilizada cuando se quiere comprobar cómo afecta dos valores a una o varias fórmulas.

       Calculo del valor de la cuota, según la variación del monto prestado

       Variación de la cuota para distintos importe y diferentes cantidades de cuotas

       Variación de la cuota para diferente cantidad de cuotas y variaciones de la tasa de interés

2.    Buscar Objetivo

En determinadas ocasiones se persigue buscar un valor de una celda específica de la que depende una fórmula con el f
obtener un resultado concreto.

Con Buscar Objetivo Excel permite ir variando el valor de una celda de entrada específica hasta que una fórmula dependien
una celda consiga elo resultado deseado.

       Cantidad de Monto para una cuota determinada

       Cantidad de Monto para un Número de plazos.

3.    Escenarios

Un escenario es un conjunto de valores que Excel guarda y puede sustituir automáticamente en la hoja de cálculo. Puede cr
guardar diferentes grupos de valores en una hoja de cálculo y después pasar a cualquiera de estos nuevos escenarios par
distintos resultados
PASOS PARA REALIZAR EJERCICIOS
PERIODO AQUÍ SE COLOCA A QUE TIEMPO EN AÑOS SE VA A REALIZAR CREDITO
MONTO AQUÍ SE COLOCA EL VALOR DEL MONTO A REALIZAR CREDITO
INTERES NOMINAL AQUÍ SE COLOCA LA TASA NOMINAL ANUAL DEL CREDITO
CUOTA MES AQUÍ SE APLICA LA FORMULA DE PAGO EXPRESADA EN MESES

LUEGO SE TRANSCRIBE LA SIGUIENTE TABLA DE UNA SOLA VARIABLE

AQUÍ SE APLICA LA
FORMULA DE PAGO
EXPRESADA EN MESES
10.5%
12.50%
15.0%
16.5%
11.50%

LUEGO, SOMBREAMOS LA TABLA COMPLETA , SEGUIDAMENTE SE VA AL MENU DATOS

LUEGO ANALISIS Y SI Y LUEGO CLIC EN TABLA DE DATOS

LUEGO EN LA OPCION CELDA DE ENTRADA (COLUMNA)

SE DA CLIC EN LA CELDA QUE CORRESPONDE AL INTERES NOMINAL
Y ACEPTAR

LUEGO SE TRANSCRIBE LA SIGUIENTE TABLA DE DOS VARIABLES

CUOTA POR MES A UNA TASA ESTABLECIDA

AQUÍ COLOCE LA FUNCION PAGO 1 2 3 4 5

1,000,000
2,500,000
3,000,000
4,000,000
5,000,000
 6,000,000
7,500,000
8,000,000
9,000,000

LUEGO, SOMBREAMOS LA TABLA COMPLETA , SEGUIDAMENTE SE VA AL MENU DATOS

LUEGO ANALISIS Y SI Y LUEGO CLIC EN TABLA DE DATOS

LUEGO EN LA OPCION CELDA DE ENTRADA (FILA) SE DA CLIC EN LA CELDA

LUEGO EN LA OPCION CELDA DE ENTRADA (COLUMNA)SE DA CLIC EN LA C
Y ACEPTAR
DA (COLUMNA)
NDE AL INTERES NOMINAL
DA (FILA) SE DA CLIC EN LA CELDA QUE CORRESPONDE A LOS MESES TOTALES(60)
DA (COLUMNA)SE DA CLIC EN LA CELDA QUE CORRESPONDE AL MONTO DEL PRESTAMO
PRESTAMOS BANCARIOS
AL BANCO CARIBEÑO SE REALIZA UN PRESTAMO DE $8.500.000 A 5 AÑOS CON UN INTERE
PERIODO Tablas de datos de dos variable
MONTO CUOTA POR MES A UNA TASA ESTABLECID
INTERES NOMINAL
CUOTA ANUAL

$ - 01 años
$ 4,500,000

MONTO DE PRESTAMO
Tablas de datos de una variable $ 5,000,000
Cuota mensual a un periodo de...AÑOS $ 5,500,000
TASA INTERES CUOTA MES $ 6,000,000
- $ 6,500,000
10.5% $ 7,000,000
12.50% $ 7,500,000
15.0% $ 8,000,000
16.5% $ 8,500,000
11.50%

APLICA VARIABLE:
APLICA VARIABLE: COLUMNA
COLUMNA

Tablas de datos de dos variable

PLAZO
CUOTA POR MES A UN MONTO DETERMIN
$ - 01 años
TASA DE INTERES

10.50%
12.00%
15.00%
16.5%

18.50%

APLICA VARIABLE:
COLUMNA
A 5 AÑOS CON UN INTERES DEL 12 % NOMINAL ANUAL
atos de dos variables
S A UNA TASA ESTABLECIDA 00.0% anual APLICA VARIABLE:
FILA

PLAZOS EN AÑOS
02 años 03 años 04 años 05 años

APLICA VARIABLE:
FILA

atos de dos variables

PLAZOS EN AÑOS
S A UN MONTO DETERMINADO $ -
02 años 03 años 04 años 05 años
RIABLE:
BUSCAR OBJETIVO
PERIODO- MESES UN PRESTAMO DE $ 25.000.000 EN 5 AÑOS
DEUDA AL BANCO CARIBEÑO
INTERES NOMINAL
CUOTA MES

EJERCICIO

SI LA PERSONA SOLAMENTE PUEDE PAGAR UNA CUOTA DE $ 500,000, MANTENIEND

DEL PRESTAMO, SE DEBE RESOLVER CUAL ES EL MONTO IDEAL A PRESTAR AL BANC

IR A MENU DATOS - ANALISIS DE HIPOTESIS , LUEGO BUSCAR OBJETIVO

DEFINIR CELDA: DONDE ESTA LA CUOTA DEL MES CON FORMULA DE PAGO
CON EL VALOR : SE COLOCA EL VALOR QUE DEBE TOMAR LA CUOTA MES
PARA CAMBIAR LA CELDA: ES DONDE ESTA LA CELDA DEL MONTO DEL PRESTAMO A CAMBIAR

AQUÍ ENCONTRO UNA SOLUCION EN EL VA

SE DEBE DE PRESTAR UN VALOR DE $ 17,380

PAGANDO $ 500,000 MENSUALES

EJERCICIOS
Para que el margen neto de utilidad se eleve al 15% cuanto se deberia de tener e
% de ventas
$$ netas
Ventas netas 2,498,300.00 100.00%
Menos: costo de ventas 2,165,000.00 86.66%
Inventarios de mercancías 2,165,000.00 86.66%
Más compras 0.00%
Menos inventario final 0.00%
Utilidad bruta 333,300.00 13.34%
Gastos de administración 116,800.00 4.68%
Gastos de ventas 37,400.00 1.50%
Otros gastos operacionales 0.00%
Utilidad operacional 179,100.00 7.17%
Gastos financieros 98,100.00 3.93%
Otros ingresos 16,900.00 0.68%
Rendimientos Financieros 40,100.00 1.61%
Corrección monetaria 99,230.00 3.97%
Utilidad antes de impuestos 237,230.00 9.50%
Provisión para impuesto de renta 83,031.00 3.32%
Utilidad neta 154,199.00 6.17%

Para que la participación de la categoria Galletas se eleve al 80% del total general
cuanto se deberia de tener en ventas de las mismas?

% de ventas
CATEGORIAS $$ netas
DULCES BLANDOS 2,498,300.00 9.06%
REPOSTRERIA - PANADERIA 1,984,000.00 7.20%
BEBIDAS 3,755,000.00 13.62%
GALLETAS 10,752,000.00 39.01%
CARNES FRIAS 6,780,000.00 24.60%
ENLATADOS 1,795,000.00 6.51%
GRAN TOTAL 27,564,300.00 100.00%
000.000 EN 5 AÑOS (60 CUOTAS) AL 24 % NOMINAL MV

00, MANTENIENDO LAS CONDICIONES

PRESTAR AL BANCO …...ENTONCES :

TAMO A CAMBIAR

SOLUCION EN EL VALOR DEL MONTO DEL PRESTAMO A SOLICITAR AL BANCO

VALOR DE $ 17,380,443 PARA DEVOLVERLO EN 5 AÑOS (60 CUOTAS)

ENSUALES
eberia de tener en ventas netas ?
 ESCENARIOS
ES UN GRUPO DE VALORES PARA CELDAS, DENOMINADAS CELDAS CAMBIANTES QUE S
GUARDA CON UN NOMBRE
ES UNA FORMA DE OBTENER RESULTADOS CAMBIANTES EN FUNCIÓN DE LOS DATOS
PRESENTES EN LAS CELDAS

NOTA : CALCULAR LOS PAGOS MENSUALES CAMBIANDO EL PERIODO,MONTO E INTERES DEL PRES
( IR A LA HOJA DE BUSCAR OBJETIVO)

1. SELECCIONAR EL RANGO DESDE LA CELDA : B3 HASTA B5

2. AHORA IR AL MENU DATOS - ANALISIS Y SI , LUEGO ADMINISTRADOR DE ESCENARI

3. LE DAMOS CLIC EN AGREGAR …

4. LUEGO LE DAMOS UN NOMBRE AL ESCENARIO : PAGOS1 Y ACEPTAR

5. LUEGO EN VALORES DEL ESCENARIO COLOCAMOS LOS DIFERENTES VALORES ASI:

(B3) PLAZO DEL PRESTAMO ; (B4) MONTO DEL PRESTAMO; (B5) TASA DE INTERES % - ANUAL

6. SI LE DAMOS CLIC EN ACEPTAR …. CREAMOS EL ESCENARIO ACTUAL , LUEGO

SI QUEREMOS CREAR OTRO LE DAMOS CLIC EN AGREGAR …..

7. POR AHORA VAMOS A CREAR CINCO (5) ESCENARIOS MAS

8. CUANDO YA SE HAYAN CREADO LOS 5 ESCENARIOS , PULSAMOS CLIC EN RESUMEN
PARA ASI PODER COMPARAR LOS RESULTADOS

9. LUEGO DAMOS CLIC EN EL BOTON DE RESUMEN DEL CUADRO DE DIALOGO RESUME

10. LUEGO DAMOS EN LA CASILLA DE CELDAS DE RESULTADO : B6

QUE ES DONDE ESTA LA FUNCION FINANCIERA PAGO , QUE MUESTRA EL VALOR DE M
Y LUEGO DAMOS CLIC EN ACEPTAR

11. VEREMOS QUE SE INSERTA UNA HOJA CON EL TITULO DE RESUMEN DE ESCENARIO
PODREMOS MIRAR COMPARATIVAMENTE LOS DIFERENTES VALORES INTRODUCIDOS CON
MENSUALES
AS CAMBIANTES QUE SE

CIÓN DE LOS DATOS

ONTO E INTERES DEL PRESTAMO

STA B5

TRADOR DE ESCENARIOS

NTES VALORES ASI:

DE INTERES % - ANUAL

CTUAL , LUEGO
S CLIC EN RESUMEN ….

O DE DIALOGO RESUMEN

LTADO : B6
STRA EL VALOR DE MENSUALIDADES $ A PAGAR

ESUMEN DE ESCENARIO , DONDE

ES INTRODUCIDOS CON SUS RESPECTIVAS CUOTAS
 ANALISIS DE DATOS - BUSCAR OBJETIVO - TABLAS Y ESCENARIOS

MONTO PRESTAMO
TIPO DE INTERES (ANUAL)
PLAZO (MESES)
MENSUALIDAD

15%
11%
9%

ANALIZAR DATOS
CONSISTE EN ESTUDIAR LAS CONSECUENCIAS QUE LOS CAMBIOS EN LOS DATOS DE
ENTRADA TIENE RESULTADOS

BUSCAR OBJETIVO
CONSISTE EN HALLAR EL VALOR DE UNA CELDA ESPECIFICA QUE HAGA QUE UNA
FORMULA ARROJE UN VALOR DESEADO

DEFINIR CELDA: SE COLOCA LA CELDA DONDE ESTA LA FORMULA

CON EL VALOR : SE COLOCA EL VALOR QUE QUIERE QUE ARROJE LA FORMULA

PARA CAMBIAR LA CELDA : SE INTRODUCE LA CELDA QUE CONTENGA EL VALOR QUE

DESEE AJUSTAR

EN EL CUADRO DE DIALOGO ESTADO DE LA BUSQUEDA : OBSERVE POSIBLES CAMBIOS Y

CONFIRME SI ACEPTA O NO LA SOLUCIÓN
1. REALIZA LA SIGUIENTE TABLA TAL COMO DICE ESTE TALLER (COMIENZA DESDE ANALISIS)

2. EN LA CELDA B7:

3. DAR CLIC EN EL ICONO DE LA BARRA DE FORMULAS

4. SELECCIONAR CATEGORIA : FINANCIERAS , LUEGO ESCOJE : PAGO Y ACEPTAR

DEFINICION : CALCULA EL PAGO DE UN PRESTAMO CON UN INTERES Y PAGOS CONSTANTES

5. AHORA EN LA CASILLA DE TASA COLOCAMOS : B4/12 *** (ES LA TASA DE INTERES)

EN LA CASILLA NPER COLOCA : B5 *** (NUMERO TOTAL DE PAGOS - MESES)
EN LA CASILLA VA COLOCA : -B3 **** (ES EL VALOR ACTUAL , SE COLOCA NEGATIVO)

6. DAR CLIC EN ACEPTAR

NOTA: AHORA , SI SÓLO SE PUEDE PAGAR $170,000 AL MES .

¿CUANTOS MESES NECESITARIAS PARA AMORTIZAR EL PRESTAMO, MANTENIENDO
EL MONTO DEL PRESTAMO Y EL TIPO DE INTERES ?

7. AHORA IR AL MENU DATOS - ANALISIS Y SI , LUEGO BUSCAR OBJETIVO

8. COLOCAR EN DEFINIR CELDA : B7

COLOCAR EN CON EL VALOR : 170000
COLOCAR EN PARA CAMBIAR LA CELDA : B5

NOTA : INDICAMOS QUE VARIE EL VALOR DE LA CELDA B5 HASTA QUE LA FORMULA DE LA

CELDA B7 ALCANCE EL VALOR $ 170,000

9. LUEGO CUANDO SALGA EL CUADRO DE DIALOGO : ESTADO DE BUSQUEDA OBJETIVO

DAR ACEPTAR
 TABLA DE DATOS
DEF. = UNA TABLA ES UN RANGO DE CELDAS EN LAS QUE PODEMOS VER CÓMO CAMBIAN LOS
RESULTADOS DE UNA FORMULA SEGÚN LOS VALORES DE ALGUNOS DE SUS COMPONENTES

LAS TABLAS DE DATOS CONSTITUYEN UNA MANERA DE VER Y COMPARAR LOS RESULTADOS
DE TODA LAS VARIABLES DISTINTAS EN LA HOJA DE CALCULO
EJERCICIO

1. SELECCIONAR EL RANGO DE CELDAS DESDE : A7 HASTA B10

2. AHORA IR AL MENU DATOS - ANALISIS Y SI , LUEGO TABLA DE DATOS

3. EN LA CASILLA DE CELDA DE ENTRADA (COLUMNA) COLOCAR : B4

Y DAR CLIC EN ACEPTAR

4. VERAS CÓMO CAMBIAN LOS VALORES DE PAGOS MENSUALES DE ACUERDO A LOS

DISTINTOS TIPOS DE INTERESES
 ESCENARIOS
ES UN GRUPO DE VALORES PARA CELDAS, DENOMINADAS CELDAS CAMBIANTES QUE SE
GUARDA CON UN NOMBRE
ES UNA FORMA DE OBTENER RESULTADOS CAMBIANTES EN FUNCIÓN DE LOS DATOS
PRESENTES EN LAS CELDAS

NOTA : CALCULAR LOS PAGOS MENSUALES CAMBIANDO EL MONTO, PRESTAMO Y EL PLAZO

DE DEVOLUCION DEL MISMO.

1. SELECCIONAR EL RANGO DESDE LA CELDA : B3 HASTA B5

2. AHORA IR AL MENU DATOS - ANALISIS Y SI , LUEGO ADMINISTRADOR DE ESCENARIOS

3. LE DAMOS CLIC EN AGREGAR …

4. LUEGO LE DAMOS UN NOMBRE AL ESCENARIO : ESCENARIO1 Y ACEPTAR

5. LUEGO EN VALORES DEL ESCENARIO COLOCAMOS LOS DIFERENTES VALORES ASI:

(B3) MONTO $ DEL PRESTAMO ; (B4) TIPO DE INTERES % ; (B5) PLAZO PAGO EN MESES

6. SI LE DAMOS CLIC EN ACEPTAR …. CREAMOS EL ESCENARIO ACTUAL , LUEGO

SI QUEREMOS CREAR OTRO LE DAMOS CLIC EN AGREGAR …..

7. POR AHORA VAMOS A CREAR CUATRO (4) ESCENARIOS MAS

8. CUANDO YA SE HAYAN CREADO LOS 4 ESCENARIOS , PULSAMOS CLIC EN RESUMEN ….
PARA ASI PODER COMPARAR LOS RESULTADOS

9. LUEGO DAMOS CLIC EN EL BOTON DE RESUMEN DEL CUADRO DE DIALOGO RESUMEN

10. LUEGO DAMOS EN LA CASILLA DE CELDAS DE RESULTADO : B7

ES DONDE ESTA LA FUNCION FINANCIERA PAGO , CON EL VALOR DE MENSUALIDADES $ A PAGAR
Y LUEGO DAMOS CLIC EN ACEPTAR

11. VEREMOS QUE SE INSERTA UNA HOJA CON EL TITULO DE RESUMEN DE ESCENARIO , DONDE
PODREMOS MIRAR COMPARATIVAMENTE LOS CAMBIOS DE ACUERDO A SU VALOR POR NIVELES