Www.fcervantes.es © 2014 Francisco Guillermo Cervantes Medina.

www.fcervantes.es

© 2014 Francisco Guillermo Cervantes Medina

Claves financieras a través de Excel

24 y 26 de marzo y 7, 9, 14, 16, 21 y 23 de abril de 2015


Objetivos generales

1. Aprender a utilizar una potente herramienta – Excel – para construir modelos dinámicos capaces de cuantificar las principales variables económico-financieras de una empresa bajo distintos escenarios.

2. Ilustrar cómo la información obtenida de dichos modelos contribuye a la gestión facilitando la toma de decisiones informadas y un óptimo seguimiento de los objetivos.

© 2014 Francisco Guillermo Cervantes Medina3

Objetivos específicos

1. Aprender a plantear, estructurar y desarrollar un modelo que cuantifique las principales variables económico financieras de una empresa;

2. Saber qué hacer y qué evitar a la hora de confeccionar un modelo a través de consejos prácticos;

3. “Aprender a aprender” ¿cómo llenar los vacíos de información que inevitablemente surgen a la hora de confeccionar un modelo?



“La práctica hace al maestro”

“Practice makes perfect”

Qué esperar y qué no esperar de este curso

6

Lo que podéis esperar:

- Un curso de aplicación práctica de técnicas de Excel al modelaje económico-financiero.

- Un esquema de trabajo para abordar la confección de un modelo económico-financiero.

- Ideas y consejos prácticos.

- La introducción a una poderosa herramienta de análisis y gestión.

- Una base para continuar el aprendizaje.

Lo que no debéis esperar:

- Una plantilla para rellenar datos.

- Una panacea.

- Salir hechos unos expertos: “la práctica hace al maestro”.



En muchas ocasiones, hay varias formas de hacer bien una cosa…

…y el modelaje financiero es una de ellas.

Lo que este curso exigirá de vosotros

• Un ejercicio de organización y esquematización de ideas pasos a seguir para obtener el resultado que buscamos.

• Buscar analogías; es imposible que un caso hipotético – por más complejo que sea – englobe la casuística de todo tipo de empresas.

• Participación: vuestras experiencias, dudas y reflexiones ¡son muy valiosas!

• No perderos en los detalles.

8 © 2014 Francisco Guillermo Cervantes Medina

Enfoque

1. Identificar los elementos de Excel (funciones, objetos, macros) más útiles dada su recurrencia en la confección de un modelo económico financiero.

2. Realizar ejercicios y demostraciones prácticas para aprender o afianzar el manejo de estos elementos.

3. Confeccionar modelos tipo aplicando dichos los elementos.



+ +

=

Elementos básicos Planificación Trabajo (horas)

Práctica,práctica,práctica,y más práctica…

Elementos básicos – los “ladrillos” para construir un modelo económico-financiero

Por su recurrencia / importancia:

1. 11 funciones básicas de Excel

2. El condicional

3. Funciones de búsqueda y texto

4. Funciones matriciales

5. Validación de datos

6. Macros

7. Algunas funciones financieras


Comencemos…


Estructura de una hoja Excel 2010


Barra de acceso rápido totalmente personalizable

Barra de menú (desplegable en la cinta de opciones); contiene opciones para manipular una hoja de cálculo.

Abre la vista “backstage”; contiene opciones para controlar y manejar todo el archivo en su conjunto.

Despliega la cinta de opciones

Despliega el menú de ayuda = F1

Despliega la dirección o el nombre de la celda o rango activo

Despliega la lista de funciones de Excel

Despliega el contenido de la celda activa (función, texto, valor, etc.)



Cinta de opciones

Vista “backstage”

Aprender a aprender


Estos recursos los tendréis que usar MUCHO.

Para realmente aprender a manejar esta herramienta…

Aprender a aprender


2 maneras de introducir funciones en Excel


1. Ayudante de Excel

Sencillo pero demasiado automático. No facilita el entendimiento, la deducción lógica, el aprendizaje o la manipulación de funciones y fórmulas.

2. Introducir funciones manualmente en cada celda

Un poco más complicado al principio pero facilita el entendimiento, la deducción lógica, el aprendizaje y la manipulación de funciones para tareas complicadas.

Funciones: estructura básica


= SUMA (número1; [número 2]; …)

Función: operación que se va a realizar.

Signo “ = ”: indica a Excel que lo que vamos a introducir en esa celda es una función o una operación.

Argumentos:a. Información que necesita la

función para realizar la operación y/o

b. Datos sobre los que se va a realizar la operación.

Siempre van entre paréntesis.

Argumento necesario

Argumento opcional

Funciones básicas


1. PROMEDIO

2. CONTAR

3. CONTARA

4. MAX

5. MIN

6. PRODUCTO

7. DESVEST.M

8. DESVEST.P

9. SUMA

10. VAR.S

11. VAR.P

¿Por qué estas 11 funciones básicas?

1. Porque su uso es bastante frecuente en el cálculo de variables relevantes en la gestión; las iremos aplicando a lo largo de los ejercicios del curso.

2. Porque las utilizaremos como punto de partida y ejercicios para familiarizarnos con:

a. La sintaxis de las funciones de Excel y

b. Con su introducción directa en la hoja de cálculo.

3. Porque son las funciones que realizan:a. La función SUBTOTALES

b. La herramienta de SUBTOTALES

c. Las Tablas Dinámicas


Funciones básicas


9. SUMA:

Sintaxis:

= SUMA (número1; [número2]; ...)

Devuelve la suma todos los números especificados como argumentos.

Cada argumento puede ser un rango.

Argumentos:a. Información que necesita la función para realizar la operación

y/ob. Datos sobre los que se va a realizar la operación.

Rango: dos o más celdas de una hoja. Pueden ser adyacentes o no.

Funciones básicas


1. PROMEDIO:

2. CONTAR: Devuelve la cantidad de celdas que contienen números. Es decir, cuenta las celdas que contienen un valor numérico.

Sintaxis:

= CONTAR (valor1; [valor2]; ...)

𝑃𝑟𝑜𝑚𝑒𝑑𝑖𝑜=𝑋=∑𝑖=1

𝑛

𝑋𝑖

𝑛

Sintaxis:

= PROMEDIO (número1; [número2]; ...)

Devuelve la media simple de una serie de números.

Funciones básicas


3. CONTARA: Devuelve la cantidad de celdas no vacías en un rango. Es decir, en un rango, cuenta las celdas que contienen algún valor.

Sintaxis:

= CONTARA (valor1; [valor2]; ...)

4. MAX: Devuelve el valor máximo de un conjunto de valores o rango.

Sintaxis:

= MAX (número1; [número2]; ...)

Funciones básicas


5. MIN: Devuelve el valor mínimo de un conjunto de valores o rango.

Sintaxis:

= MIN(número1; [número2]; ...)

6. PRODUCTO: Devuelve el producto de todos los números proporcionados como argumentos.

Sintaxis:

= PRODUCTO(número1; [número2]; ...)

Funciones básicas – Mini caso DemvrekDul


DemvrekDul, S.L. es una empresa fabricante de dulces y golosinas.

Su línea de producto más importante es la de barritas de chocolate de 20 gramos.

Aunque es imposible que todas las barritas pesen exactamente 20 gramos, los últimos controles de calidad muestran que el peso medio de las barritas sí = 20 gramos.

Sin embargo la empresa sabe que una gran variación en el peso de las barritas ocasiona 2 problemas:

1. Incremento de costes Erosión de márgenes de rentabilidad;2. Quejas de clientes: En las últimas 2 semanas ha recibido varias sobre el

tamaño de las barritas aduciendo que algunas salen demasiado pequeñas.

Aunque no ha recibido quejas al respecto, los equipos de producción y gestión de la empresa sospechan que también muchas barritas salen demasiado grandes distorsionando así el cálculo de costes y los márgenes de rentabilidad para esta importante línea de producto.

Los equipos de producción y gestión necesitan:A. Corroborar estas quejas y sospechasB. Implementar medidas de solución.



DemvrekDul produce 10.000 barritas de chocolate al día.

¿Cómo proceder?

Escenario 1: Se toma una muestra de 10 barritas y se pesa cada una obteniendo los siguientes resultados:

05

10152025303540

1 2 3 4 5 6 7 8 9 10

gram

os p

or b

arrit

a

Número de observación en la muestra

Muestra 1 - Alta Dispersión

Promedio ObservacionesVar M =VAR.S(C2:C11)Desv. Est. M =STDEV.S(C2:C11)

=E12/(B11-1)=E14^0.5



Escenario 2: Se toma una muestra de 10 barritas y se pesa cada una obteniendo los siguientes resultados:

05

10152025303540

1 2 3 4 5 6 7 8 9 10

gram

os p

or b

arrit

a


Muestra 2 - Baja Dispersión

Promedio Peso barrita (gramos)Var M =VAR.S(H2:H11)Desv. Est. M =STDEV.S(H2:H11)

=J12/(G11-1)=J14^0.5



05

10152025303540

1 2 3 4 5 6 7 8 9 10

gram

os p

or b

arrit

a


Muestra 1 - Alta Dispersión

Promedio Peso barrita (gramos)

05

10152025303540

1 2 3 4 5 6 7 8 9 10

gram

os p

or b

arrit

a


Muestra 2 - Baja Dispersión

Promedio Peso barrita (gramos)

¿En qué situación preferiría estar el equipo de producción y gestión de DemvrekDul?

Funciones básicas


10. VAR.S:

Sintaxis:

= VAR.S (número1; [número2]; ...])

Devuelve el cálculo de la varianza de una muestra.

La varianza es una medida de la dispersión de un número de observaciones – una muestra – respecto a su media simple.

𝑉𝑎𝑟𝑖𝑎𝑛𝑧𝑎=𝑠𝑥2=

∑𝑖=1

𝑛

( 𝑋𝑖−𝑋 )2

𝑛−1

Donde:Xi = Observación iX = Media aritmética de la muestran = número de observaciones de la muestra.

Funciones básicas


7. DESVEST.M:

Sintaxis:

= DESVEST.M (número1; [número2];...])

Devuelve la desviación estándar de una muestra.

𝐷𝑒𝑠𝑣𝑖𝑎𝑐𝑖ó𝑛𝑒𝑠𝑡 á𝑛𝑑𝑎𝑟=𝑠𝑥=√∑𝑖=1𝑛

(𝑋 𝑖−𝑋 )2

𝑛−1

La desviación estándar es una medida de dispersión de un número de observaciones – una muestra – respecto a su media simple.

Funciones básicas


11. VAR.P:

Sintaxis:

= VAR.P (número1;[número2];...])

Devuelve el cálculo de la varianza de toda una población

𝑉𝑎𝑟𝑖𝑎𝑛𝑧𝑎=𝜎 𝑥2=

∑𝑖=1

𝑁

(𝑋 𝑖−𝜇 )2

𝑁

Donde:Xi = Observación i= Media aritmética de la poblaciónN = número de elementos de la población.

Funciones básicas


8. DESVEST.P:

Sintaxis:

= DESVEST.P (número1; [número2]; ...])

Calcula la desviación estándar de una población.

La desviación estándar es una medida de la dispersión del total de las observaciones – una población – respecto a su media simple.

𝐷𝑒𝑠𝑣𝑖𝑎𝑐𝑖ó𝑛𝑒𝑠𝑡 á𝑛𝑑𝑎𝑟=𝜎 𝑥=√∑𝑖=1𝑁

(𝑋 𝑖−𝜇)2

𝑁

Ejercicio 0a: Aplicaciones prácticas

Temas financieros

• Rentabilidad:– Control de costes y márgenes– Control y gestión de stocks

• Solvencia• Liquidez• Gestión: Control de calidad gestión de clientes política comercial

Técnicas de Excel

• Funciones estadísticas básicas: – VAR.S– VAR.P– DESVEST.M– DESVEST.P


Funciones básicas


1. PROMEDIO

2. CONTAR

3. CONTARA

4. MAX

5. MIN

6. PRODUCTO

7. DESVEST.M

8. DESVEST.P

9. SUMA

10. VAR.S

11. VAR.P


Los abordaremos en el siguiente orden:

1. 11 funciones básicas de Excel

2. El condicional





7. Macros


Condicional: SI


Si se cumple una condición A, entonces realizo la acción X; si no se cumple esa condición, entonces realizo la acción Y.

= SI ( condición A; acción X; acción Y)

Condición que ha de cumplirse.

Acción a realizarse si la condición se cumple.

Acción a realizarse si la condición NO se cumple.

Hagamos un ejemplo sencillo…

Condicional: SI


= SI ( prueba_lógica; [valor_si_verdadero]; [valor_si_falso] )

La condición evaluada, que puede ser:• Una comparativa sencilla. Ejemplos:

A10=250A1>B2

• El resultado de una función. Ejemplos:SUMA (A1:A:20) < 80ESERROR ( BUSCARV (32;A1:B10;2;FALSO))

La acción que queremos realizar si la condición a evaluar se cumple (“VERDADERO”) o no (“FALSO”). Puede ser:

• Ninguna, en cuyo caso la función SI únicamente devuelve VERDADERO o FALSO según la condición evaluada se cumpla o no.

• Otra función. Ejemplo:PRODUCTO ( A1; A20 )

• Un texto. Ejemplo: “Se cumple la condición”

• Un número

Trabajados en el ejemplo anterior

Funciones básicas + el condicional: Suma


Sumamente útil, pero limitada:

• Sólo admite una condición• Dicha condición no admite al operador “Y” ni al operador “O”.

= SUMAR.SI ( rango; criterio; [rango_suma] )

Grupo de celdas (“rango”) en el que hay que evaluar si se cumple una condición.

Condición a verificar si se cumple o no.

Grupo de celdas que hay que sumar si y sólo si se cumple la condición definida en el argumento “criterio”.

Funciones básicas + el condicional: Suma


Sumamente útil, pero también limitada:

• El rango de celdas que se suman y el/los rangos de celdas que han de cumplir las condiciones deben tener el mismo número de columnas y filas.

• El argumento de “criterios” (las condiciones a evaluar) no admiten a los operadores “Y” ni “O”.• No es compatible con versiones de Excel anteriores a 2007.

= SUMAR.SI.CONJUNTO( rango_suma; rango_criterios1; criterio1; [rango_crit2]; [crit2] …)

Grupo de celdas (“rango”) en el que hay que evaluar si se cumple una condición.

Condición a verificar si se cumple o no.

Grupo de celdas que hay que sumar si se cumplen todas las condiciones definidas en los argumentos “criterio1”, “criterio2”, etc.

Veamos un ejemplo sencillo…

Suma y el condicional


Suma simple:• Una sola dimensión;• Ninguna discriminación (ningún criterio);• Mínima flexibilidad;• Máxima sencillez;• Información general: ¿cuánto nos hemos

gastado?

Suma condicional:• 2 dimensiones:

− Condición− Números a sumar

• Discriminación por un solo criterio;• Muy poca flexibilidad• Relativa sencillez• Información más específica: ¿cuánto nos

hemos gastado en frutas?



Suma condicional conjunta:• n dimensiones del mismo

tamaño− Condición1, condición2 …

condiciónn− Números a sumar

• Discriminación por n criterios;• Cierta flexibilidad;• Relativa sencillez;• Información más específica.



Función matricial:• n dimensiones dentro de una matriz m x n• Discriminación por n criterios contenidos tanto en las filas o columnas cabecera como en la

matriz de los números objeto de la operación;• Mayor flexibilidad: permite realizar más operaciones aparte de la suma;• Mayor especificidad en la información;• Mayor complejidad.

Otras funciones básicas + el condicional


Todas tienen una sintaxis similar y funcionan bajo el mismo principio.

1. CONTAR.SI

2. CONTAR.SI.CONJUNTO

3. PROMEDIO.SI

4. PROMEDIO.SI.CONJUNTO

1. Cuenta los números de un rango si se cumple una condición;

2. Cuenta los números de un rango si se cumplen 2 o más condiciones simultáneamente;

3. Devuelve la media simple de los números de un rango que cumplan una condición;

4. Devuelve la media simple de los números de un rango que cumplan 2 o más condiciones simultáneamente.

Condicional: SI en estructura anidada


• Si se cumple la condición A, entonces realizo la acción X;• Si se cumple la condición B, entonces realizo la acción Y;• Si se cumple la condición C, entonces realizo la acción Z;• Si no se cumple ninguna de estas condiciones, entonces realizo la acción N.

= SI ( cond.A; acc.X; SI ( condB; acc.Y; SI ( condC; acc.Z; acc.N )))

Hagamos un ejemplo sencillo…

Ejercicio 3: Observaciones


Todos estos números también son menores que 3.

Entonces ¿por qué en dichas celdas no aparece el mensaje “menor que 3”?

Ejercicio 3: Corolarios

• La valoración del condicional es secuencial; es decir, cuando una condición se cumple, Excel devuelve un resultado y no va más allá.

• Por lo tanto, hay que tener en cuenta:– El orden o prioridad de las condiciones que queremos

evaluar;– Si algunas pueden entrar en conflicto o estar relacionadas

con otras puede inducir a error.


Condicional: SI en estructura anidada con la función O


Es decir:

• Si de las condiciones A o B se cumple una, entonces realizo la acción X;• Si se cumple la condición C, entonces realizo la acción Y;• Si no se cumplen A ó B, ni tampoco C, entonces realizo la acción Z.

= SI ( O ( condA; condB ); acc.X; SI ( condC; acc.Y; acc.Z ))

Vayamos al Ejercicio 4…

= O ( valor_lógico1; [valor_lógico2]; … )

Condición o condiciones de las cuales sólo una ha de cumplirse.

Ejercicio 4: Aplicaciones prácticas

Temas financieros

• Liquidez: gestión de cobros• Solvencia: gestión de

riesgo• Rentabilidad: costes de

financiación por cliente beneficio por cliente

• Gestión: segmentación y gestión de clientes política comercial

Técnicas de Excel

• Función SI en estructura anidada con la función O

• Función CONTAR.SI• Otras funciones:

– CONTAR– REDONDEAR


Condicional: SI en estructura anidada con la función Y


Es decir:

• Si de las condiciones A y B se cumplen las dos, entonces realizo la acción X;• Si se cumple la condición C, entonces realizo la acción Y;• Si no se cumplen A y B, ni tampoco C, entonces realizo la acción Z.

= SI ( Y ( condA; condB ); acc.X; SI ( condC; acc.Y; acc.Z ))

= Y ( valor_lógico1; [valor_lógico2]; … )

Condición o condiciones que han de cumplirse simultáneamente

Vayamos al Ejercicio 5…

Ejercicio 5: Aplicaciones prácticas

Tema financiero - Liquidez

• Periodos medios de cobro y pago gestión de tesorería

Técnicas de Excel

• Función SI en estructura anidada con la función Y

• Otras funciones:– SUBTOTALES– SUMAPRODUCTO


Principales conceptos hasta ahora


“La ciencia es, simplemente, el método que usamos para postular unos supuestos mínimos que nos permitan explicar a través de una sencilla deducción lógica la existencia de muchos fenómenos naturales.”

“Sinceramente, soy de la opinión de que la única forma que tenemos de aprender es a través de nuestras deducciones lógicas.”

“Nuestros libros de texto no deberían presentarnos una serie de resultados finales sino una trama que encamine el proceso de deducción lógica del lector.”

“No es necesaria una inteligencia superior para crear una ciencia nueva o expandir una existente. Es necesaria la valentía para hacer frente a las inconsistencias” [entre los métodos existentes, nuestro entorno y nuestras circunstancias].

Eliyahu Goldratt, La Meta, (introducción). Traducción y énfasis propios.

Los elementos básicos (“ladrillos”) para construir nuestros modelos:

1. 11 funciones básicas

2. Condicional:



Función: Es una instrucción que pide a Excel realizar una operación (SUMA; CONTAR.SI; SUMAPRODUCTO) o una acción (SI; Y; O; BUSCARV) y que nos devuelve un resultado.

Condicional simple: = SI ( condición A; [ acción X ]; [ acción Y ] )

Devuelve:a. El resultado de la acción u operación que le pedimos si la

condición se cumple.b. El resultado de la acción u operación que le pedimos si la

condición NO se cumple.



Condicional simple: = SI ( condición A; [ acción X ]; [ acción Y ] )

La condición evaluada, que puede ser:

• Una comparativa sencilla. Ejemplos:A10=250A1>B2

• El resultado de una función. Ejemplos:SUMA ( A1:A:20 ) < 80O ( A23 < -1000; A23 > 3000 )Y ( A23 > 3000; B23 > 60 )ESERROR ( BUSCARV(32; A1:B10; 2;FALSO))

Operadores lógicos

Las acciones a realizar pueden ser:

• Que la función nos devuelva un valor:SI ( A5 = 0; 0 ; A5 )

• Que la función nos devuelva un texto:SI ( A5 < 0; “negativo”; A5 )

• Que la función nos devuelva el resultado de otra función:SI ( A5 >= 2; SUMA ( B6:B10 );

PROMEDIO ( C14:C21 ))

• Cualquier combinación de las anteriores.

Argumento necesario

Argumentos opcionales

Funciones anidadas



Funciones anidadas: “Función dentro de otra función”. Ejemplo: Condicional anidado con la función (operador lógico) “O”:

= SI ( O ( condA; condB ); acción X; acción Y)

= SI ( condición A; [ acción X ]; [ acción Y ] )

En lugar de evaluar una sola condición, evaluamos si se cumple una de 2 o más.

Si NO se cumple ninguna de las primeras condiciones evaluadas, entonces podemos realizar otras dos acciones (en lugar de una) cuya elección, a su vez, depende de otra condición.

= SI ( O ( condA; condB ); acc.X; SI ( condC; acc.Y; acc.Z ))



= O ( valor_lógico1; [valor_lógico2]; … )

= Y ( valor_lógico1; [valor_lógico2]; … )

Operadores lógicos:Evalúa si se cumple cuando menos una condición.

Evalúa si se cumplen todas las condiciones.

Funciones condicionales:• SUMAR.SI• SUMAR.SI.CONJUNTO• CONTAR.SI

Es común que tengamos que realizar operaciones básicas aplicando un condicional. Excel tiene funciones propias para ello.

• CONTAR.SI.CONJUNTO• PROMEDIO.SI• PROMEDIO.SI.CONJUNTO



Otras funciones utilizadas:• CONTAR• REDONDEAR

Sintaxis:

= FUNCION ( argumento1; argumento2;…argumenton )

= FUNCION ( argumento1; FUNCION2 (argumentoA; argumentoB); argumento3; …argumenton )

• SUMAPRODUCTO• SUBTOTALES

= SUMAR.SI ( A4:A9; “Frutas”; B4:B9 )

= SUMAR.SI ( A4:A9; 2 ;B4:B9 ) = SUMAR.SI (A4:A9; A1;B4:B9)= SUMAR.SI ( A4:A9; “>3”; B4:B9 )

= SUMAR.SI ( A4:A9; “<>”&A1; B4:B9 )

Ejercicio 5a – Tablas dinámicas


1. Emular la segunda parte del ejercicio 2b usando una tabla dinámica.

Tablas dinámicas


1. ¿Qué es una tabla dinámica?

Es una herramienta para manipular fácilmente una base de datos para su análisis.

Una imagen vale más que mil palabras…

Tablas dinámicas


1. Posicionar el cursor en cualquier sitio de la tabla que queramos dinamizar

2. Verificar que el rango de la tabla es correcto3. Elegir el posicionamiento de la tabla dinámica

(opción sugerida: nueva hoja de cálculo).

Tablas dinámicas


Tablas dinámicas


• Información presentada en formato muy resumido

• Nos permite visualizar los criterios de búsqueda

• Útil cuando el cálculo o filtrado es parte de un modelo más grande y lo que nos interesa es únicamente el resultado final (ejemplo: lo usamos para realizar un cálculo y el visualizar el criterio nos indica los parámetros para los que realizamos dicho cálculo)

• Máximo detalle de información

• Máxima flexibilidad para elegir parámetros de filtrado

PERO…

• Sólo permite visualizar la información en el mismo formato de la tabla original

• No aplica ningún cálculo a los datos originales a menos que dichos cálculos se añadan manualmente como campos (columnas) en la tabla o base de datos.

• Permite visualizar la información de distintas maneras y desde distintas perspectivas.

• PERO

• A la vez restringe la información que se podemos visualizar según dónde tengamos puestos los campos de filtrado

• Poca flexibilidad a la hora de elegir los parámetros de filtrado

• Sólo muestra la información numérica aplicándole una de las 11 operaciones básicas.

Tablas dinámicas - Ejercicio


A. Emular las tablas creadas en el ejercicio 2b haciendo uso de una tabla dinámica.

B. Jugar con varias maneras de confeccionar una tabla dinámicai. Arrastrar campos a distintos sitios de la tablaii. Incluir subtotales y totales generalesiii. Mostrar información haciendo uso de las opciones de cálculo que nos ofrece

una tabla dinámica

Tablas dinámicas – Principales herramientas


Menú temático


1. El condicional





6. Macros


Funciones de texto

• = DERECHA (texto; [núm_de_caracteres])– Devuelve el número de caracteres especificado desde la

derecha (final) de una cadena de texto.

• = IZQUIERDA (texto; [núm_de_caracteres])– Devuelve el número de caracteres especificado desde la

izquierda (principio) de una cadena de texto.

• = EXTRAE (texto; posición_inicial; núm_caracteres)– Devuelve el número de caracteres especificado a partir de

un punto medio de una cadena de texto.


Funciones de texto

• = ESPACIOS (texto)– Quita todos los espacios innecesarios de una cadena de texto; es

decir, sólo deja los espacios individuales entre palabras.

• = “texto1” & [referencia]– Añade el contenido de una celda (texto o numérico) a una o varias

cadenas de texto.

• = CONCATENAR (texto1; [texto2]; …)– Une dos o más cadenas de texto en una sola.

• = TEXTO (valor; formato)– Transforma un valor numérico en texto con el formato especificado.


Función SUBTOTALES


= SUBTOTALES (núm_función; ref1; ref2 …)– Suma, promedia, cuenta…los elementos de una columna

según la función elegida. Ajusta el cálculo al aplicar filtros.

La diferencia entre la función del 1 al 11 y la 101 a la 111 es que en las segundas, el cálculo obvia los valores de las filas ocultas.

Funciones de referencia: BUSCARV (vertical)


=BUSCARV(valor_buscado; matriz_buscar_en; indicador_columnas; [ordenado])

¿Qué buscamos? ¿En dónde? ¿El dato de qué columna queremos que nos devuelva?

Puede ser un número o una referencia.

¿Búsqueda exacta o aproximada?

• Busca un valor dentro de la primera columna de una matriz (tabla) de datos y devuelve:• El valor encontrado = al valor buscado• Un valor que se encuentre en la misma fila que el valor encontrado

pero varias columnas a la derecha.

• Si existen 2 o más valores dentro de la primera columna que coincidan con el valor buscado, entonces la función devuelve el dato correspondiente al primer valor encontrado.

Funciones de referencia: BUSCARH (horizontal)

• Funciona exactamente igual que BUSCARV, sólo que busca un valor dentro de la primera fila de una matriz de datos y devuelve un valor que se encuentre en la misma columna que el valor encontrado pero varias filas hacia abajo.

• En la gran mayoría (si no es que en todos) los casos utilizaremos BUSCARV.


valor devuelto

Zona de búsquedaBUSCARH

valor devuelto

Zona

de

búsq

ueda

BUSCARV

Ejercicio 6b: Relación de datos

• Una empresa requiere consolidar en un solo listado los datos de contacto y saldos de sus proveedores a una fecha determinada.

• Su programa de gestión solamente puede generar los siguientes listados:– Una lista exhaustiva de los proveedores de la empresa– Los saldos del grupo 400 de la contabilidad

• Los últimos 2 a 5 dígitos de las cuentas del grupo 400 coinciden con el número de proveedor en el listado de proveedores.


clave

Ejercicio 6b: Pasos para su solución


a) ¿Qué información necesitamos?

b) ¿De qué información disponemos?• De dos listados:

• Datos de contacto• Saldo del grupo 400

• Un solo listado que contenga:• Los datos de contacto (y potencialmente otros datos de gestión) de proveedores;• El saldo de proveedores a una fecha determinada.

Ejercicio 6b: Pasos para su solución


c) ¿Cómo tenemos que manipular la información de la que disponemos para obtener la información que necesitamos?

d) Y para esto, a su vez ¿qué necesitamos?

• Encontrar un elemento común a los 2 listados;• Utilizar este elemento común para automatizar la búsqueda y

concatenación de datos según nuestras necesidades.

• Necesitamos reunir la información en un solo listado.

Ejercicio 6b: Aplicaciones prácticasTema financiero – Control de gestión

• Mejora de procesos eficiencia administrativa

Posibles aplicaciones:• Circularizaciones

(auditorías)• Plan de tesorería• Gestión de proveedores y

optimización de compras• Análisis de costes

Técnicas de Excel

• Funciones de búsqueda– BUSCARV

• Funciones de texto– DERECHA

• Condicional anidado• Otras funciones:

– VALOR– ESERROR– CONTARA

• Conciliación: “Chk Sum”• Agrupar y desagrupar


Ejercicio 6c: BUSCARV búsqueda aproximada


Veamos un ejemplo…

Ejercicio 6d: la función DESREF


Veamos un ejemplo…

Ejercicio 6e: Gestión de stocks


Cálculo de Stock de seguridad según el método de la Tasa de Servicio

Este modelo considera las variaciones estadísticas de la demanda y del Plazo de Suministro.

Plazo de Suministro o “Lead time”: el tiempo que transcurre desde que se hace un pedido hasta que la mercancía entra en el almacén.

𝑺𝒕𝒐𝒄𝒌𝑺𝒆𝒈𝒖𝒓𝒊𝒅𝒂𝒅=𝜇×√ (𝐷𝑚×𝜎𝐷2 )+(𝐷×𝜎𝐷𝑚2 ): “Tasa de Servicio”; un coeficiente de probabilidad de NO tener rupturas de stock.: Demanda media: Varianza del Plazo de Suministro

: Varianza de la demanda

En donde:

𝜎𝐷𝑚2




Este modelo considera las variaciones estadísticas de la demanda y del Plazo de Suministro.

Plazo de Suministro o “Lead time”: el tiempo que transcurre desde que se hace un pedido hasta que la mercancía entra en el almacén.

𝑺𝒕𝒐𝒄𝒌𝑺𝒆𝒈𝒖𝒓𝒊𝒅𝒂𝒅=𝜇×√ (𝐷𝑚×𝜎𝐷2 )+(𝐷×𝜎𝐷𝑚2 ): “Tasa de Servicio”; un coeficiente de probabilidad de NO tener rupturas de stock.: Demanda media: Varianza del Plazo de Suministro

: Varianza de la demanda

En donde:

Ejemplo reproducido con el permiso del autor, Javier Pérez de Isla GonzálezIngeniero Industrial y Socio Director de J.P. Isla Logística, S.L.




Si consideramos un plazo de suministro estable y fiable, entonces:

𝑺𝒕𝒐𝒄𝒌𝑺𝒆𝒈𝒖𝒓𝒊𝒅𝒂𝒅=𝜇×√ (𝐷𝑚×𝜎𝐷2 )+(𝐷×𝜎𝐷𝑚2 )

Ejemplo reproducido con el permiso del autor, Javier Pérez de Isla GonzálezIngeniero Industrial y Socio Director de J.P. Isla Logística, S.L.

0

𝑺𝒕𝒐𝒄𝒌𝑺𝒆𝒈𝒖𝒓𝒊𝒅𝒂𝒅=𝜇×√ (𝐷×𝜎𝐷𝑚2 )𝑺𝒕𝒐𝒄𝒌𝑺𝒆𝒈𝒖𝒓𝒊𝒅𝒂𝒅=𝜇×√𝐷×𝜎𝐷𝑚

𝑺𝒕𝒐𝒄𝒌𝑺𝒆𝒈𝒖𝒓𝒊𝒅𝒂𝒅=𝜇×𝐷×𝜎𝐷𝑚

Si D>1Si D<=1


1. El condicional




5. funciones matriciales

6. Macros


2 aplicaciones en la confección de modelos para la gestión de empresas:

1. Categorizaciones

2. Análisis de escenarios


Validación de datos


Caso Bodegas DemvrekEjercicio 7: contribución a rentabilidad

• La principal actividad de Bodegas Demvrek es producir y comercializar su propia marca de vinos.

• Sin embargo, recientemente tomó la decisión estratégica de diversificar sus operaciones y amortizar sus instalaciones creando una “división servicios” desde la que:

– Imparte cursos de enología mediante un convenio con la universidad de su comunidad así como de cata y maridaje para sumilleres;

– Organiza eventos– Alquila instalaciones


Ejercicio 7: contribución a rentabilidad

• Las operaciones de la división servicios están totalmente integradas en la contabilidad de las bodegas.

• La impresión es que la división servicios “va bien” pero es pequeña y no está claro si contribuye al beneficio de la empresa: la rentabilidad total de Demvrek sigue cayendo año tras año.

• El último ejercicio cerrado ha sido el primer ejercicio completo de actividad de la “división servicios” y el equipo directivo desea saber cuál ha sido la contribución de esta división al beneficio total de la empresa para tomar una decisión: potenciarla o cerrarla.


Ejercicio 7: Pasos para su solución



Rentabilidad por línea de negocio

• Líneas de negocio de la empresa• Ingresos y gastos por línea de negocio

b) ¿De qué información disponemos?• Líneas de negocio de la empresa• Ingresos y gastos agregados (contabilidad grupos 6 y 7)

Ejercicio 7: Pasos para su solución


c) ¿Cómo tenemos que manipular la información de la que disponemos para obtener la información que necesitamos?

• Categorizar ingresos y gastos por línea de negocio.

d) Y para esto, a su vez ¿qué necesitamos?

• Establecer un criterio de categorización.

Ejercicio 7: criterios de categorización

88

Líneas de negocio:

i. Vinoii. Serviciosiii. Ambos

Procedamos…


Ejercicio 7: Consejos prácticos


– Si tenéis que usar un condicional, podéis referenciarlo a una celda en lugar de introducir un texto en una función (“código duro”) que si luego lo queréis cambiar, tendréis que hacerlo en cada función que lo utilice con la ineficiencia y la posibilidad de error que ello implica.

– Podéis saber a golpe de vista qué valores pueden tomar las celdas validadas (estas cosas se olvidan fácilmente).

1. Aunque podáis introducir los valores para la validación de datos de forma manual, yo os sugiero meterlos en celdas visibles porque:

Resultado: un modelo más manejable y flexible

Ejercicio 7: Consejos prácticos


2. Colocar los totales encima de cada categoría y no al final de la misma;

3. Utilizar la función SUBTOTALES y filtros;

4. En lo posible, utilizar la función de agrupar en lugar de esconder líneas o columnas;

5. Codificar celdas por colores, por ejemploa) Las que se pueden cambiar

b) Las que no se deben tocar

pero siempre manteniendo un criterio consistente y cuidando de no abusar de los colores.

Ejercicio 7

Tema financiero – Rentabilidad

• Contribución al beneficio por línea de negocio

• Análisis de costo-volumen-beneficio– Cálculo del margen de

contribución por línea de negocio;

– Cálculo del punto de equilibrio por línea de negocio.

– Cálculo del GAO / estimación del riesgo operativo.

Técnicas de Excel

• Validación de datos simple• Suma condicional• Gráfica simple• Color de celdas


Ejercicio 8: Validación de datos dependiente o condicional


1. Dar nombre a cada rango que contenga una lista de validación; (el rango nombrado únicamente debe incluir la lista de validación)

2. El nombre de los rangos dependientes debe ser exactamente igual al dato de validación que les da origen

3. Para validar el rango dependiente, usar la función INDIRECTO4. Si dato a valida contiene espacios, usar la función SUSTITUIR

Ejercicio 8: Validación de datos dependiente


Técnicas de Excel

• Nombrar un rango Administrador de nombres• Funciones utilizadas

– INDIRECTO– SUSTITUIR

Referencia:

http://www.contextures.com/xlDataVal02.html


1. El condicional





6. Macros


El valor del dinero en el tiempo


Es un concepto económico que básicamente establece que 1 € en mi bolsillo hoy no tiene el mismo valor que 1 € en mi bolsillo en un futuro y viceversa.

¿Qué es?

1. Inversión: Para obtener 1 € en el futuro, yo hoy podría invertir un importe menor puesto que con el rendimiento de esa inversión podría tener 1 € en el futuro.

2. Inflación: En un entorno inflacionario – que es lo que se considera normal – 1 € en el futuro tendrá un poder adquisitivo menor que hoy. Es decir, tener 1 € mañana equivaldría a tener menos de 1 € hoy.

3. Consumo: En teoría económica, si un sujeto ha de diferir su consumo de hoy a mañana, es razonable que espere una recompensa o un rendimiento por ello. Por lo tanto, 1 € de consumo mañana equivale a menos de 1 € de consumo hoy. Dicho de otra manera, un sujeto económico preferirá consumir un poco menos de 1 € hoy que esperar a consumir 1 € mañana.

3 razones básicas:

Fórmulas financieras básicas


𝑽𝑭=𝑽𝑨 (𝟏+𝒓 )

Valor futuro (VF) que tendrá un importe invertido hoy (VA) a un interés compuesto de r durante n periodos de tiempo:𝑽𝑭=𝑽𝑨(𝟏+𝒓 )𝒏 Valor actual (VA) de un importe a recibir en el futuro(VF) con un interés compuesto de r durante n periodos de tiempo.

Valor futuro (VF) que tendrá un importe invertido hoy (VA) a un interés de r durante 1 periodo de tiempo:𝑽𝑭=𝑉𝐴 (1+𝑟 ) (1+𝑟 )=𝑽𝑨(𝟏+𝒓)𝟐

𝑽𝑭=𝑉𝐴 (1+𝑟 ) (1+𝑟 ) (1+𝑟 )=𝑽𝑨(𝟏+𝒓)𝟑

Valor futuro (VF) de un importe invertido hoy (VA) al mismo interés durante 2 periodos:Valor futuro (VF) de un importe invertido hoy (VA) al mismo interés durante 3 periodos:

𝑽𝑨=𝑽𝑭

(𝟏+𝒓 )𝒏

4 variables clave: VA, r, n, “inicio” o “fin”

Funciones financieras básicas en Excel


= VF ( tasa; nper; pago; [va]; [tipo] )

Tipo de interés r Número de periodos n de la inversión

Cuota invertida durante cada periodo

Importe invertido en el momento presente

Pago de interés al final del periodo = 0;Pago de interés al inicio del periodo = 1

0 321

300 € 646 €315 €

300 € 300 €

993 €

0 € 315 €300 €

300 € 946 €300 €

1.000 € 1.103 €1.050 € 1.158 €

Cuota invertida y pago del interés al inicio del periodo

Cuota invertida y pago del interés al final del periodo

Importe invertido en momento actual

Tasa 5% 5% 5%periodos 3 3 3pago (300 €) (300 €) 0 €valor actual 0 € 0 € (1,000 €)Inicio /Fin del periodo

Inicio Fin

Valor Futuro 993 € 946 € 1,158 €

646 €



= VA ( tasa; nper; pago; [vf]; [tipo] )

Tipo de interés r Número de periodos n de la inversión

Pago devengado durante cada periodo

Importe a recibir al final del plazo

Pago de interés al final del periodo = 0;Pago de interés al inicio del periodo = 1

0 321

300 € 300 €300 €

285 € 272 €

817 €

300 €300 €

272 € 259 €

300 €

864 € 952 €907 € 1.000 €

VA de una anualidad con pago del interés al inicio del periodo

VA de una anualidad con pago de interés al final del periodo

VA = importe a invertir hoy al 5% anual para recibir 1.000 € dentro de 3 años

858 €

285 €

Tasa 5% 5% 5%periodos 3 3 3pago (300 €) (300 €) 0 €valor futuro 0 € 0 € (1,000 €)Inicio /Fin del periodo

Inicio Fin

Valor Actual 858 € 817 € 864 €

Aplicaciones


1. Análisis de inversiones

2. Análisis y simulación de deuda



¿Cuál sería el valor actual (VA) no de un pago, sino de una serie de pagos iguales o anualidad en la que cada pago devenga un interés compuesto de r durante n periodos?𝑉𝐴=

𝑃𝑎𝑔𝑜(1+𝑟 )1

+𝑃𝑎𝑔𝑜(1+𝑟 )2

+𝑃𝑎𝑔𝑜(1+𝑟 )3

+…+𝑃𝑎𝑔𝑜(1+𝑟 )𝑛

𝑉𝐴=𝑃𝑎𝑔𝑜 [ 1− 1

(1+𝑟 )𝑛

𝑟 ]A través de una serie de manipulaciones matemáticas, esta fórmula se simplifica en:

Valor actual (VA) de un importe a recibir en el futuro(VF) con un interés compuesto de r durante n periodos de tiempo.𝑽𝑨=𝑽𝑭

(𝟏+𝒓 )𝒏

Financiación bancaria más común


Préstamo

Interés variable

Sin carencia

Carencia

Interés Fijo

Sin carencia

Carencia

Cap. Cte.

Francés

Interés variable

Interés Fijo

Interés variable

Interés Fijo

Interés variable

Interés Fijo

Amortización

Mensual

Trimestral

Cuatrimestral

Semestral

Anual

Periodo de revisión interés

Mensual

Trimestral

Cuatrimestral

Semestral

Anual

Leasing

Interés variable

Sin carencia

Carencia

Interés Fijo

Sin carencia

Carencia

Cap. Cte.

Francés

Interés variable

Interés Fijo

Interés variable

Interés Fijo

Interés variable

Interés Fijo

Amortización

Mensual

Trimestral

Cuatrimestral

Semestral

Anual

Periodo de revisión interés

Mensual

Trimestral

Cuatrimestral

Semestral

Anual

Ejercicio 9 Parte I – Modelo de préstamo con amortización de capital constante



• Saber cómo funciona un préstamo con amortización de capital constante

Ejercicio 9 Parte II – Modelo de préstamo con cuota constante



• Saber cómo funciona un préstamo con cuota constante o amortización de tipo francés.



𝑉𝐴=𝑃𝑎𝑔𝑜 [ 1− 1

(1+𝑟 )𝑛

𝑟 ] A la hora de modelar un préstamo sabemos:VA = Capital (importe) del préstamor = Tipo de interés correspondiente al periodo de amortización (es decir, si r es un interés anual, pero la amortización de este préstamo es mensual, entonces el interés que debemos utilizar = r / 12.n = Periodos del préstamo (meses, trimestres, años, etc.)Lo que nos interesará saber, y por tanto, modelar es el Pago…𝑃𝑎𝑔𝑜=𝐶𝑎𝑝𝑖𝑡𝑎𝑙 [𝑝𝑒𝑛𝑑𝑖𝑒𝑛𝑡𝑒 ] [ 𝑟

1−1

(1+𝑟 )𝑛 ] ¡¿Y cómo llevamos esto a Excel?!

La siguiente fórmula (vista anteriormente) guarda estrecha relación con un préstamo que devenga un interés r , cuyas cuotas o pagos son iguales y se realizan durante n periodos:



= PAGO ( tasa; nper; va; [vf]; [tipo] )

Tipo de interés r Número de periodos del préstamo n

Importe o capital del préstamo

Función PAGO aplicada calcular la cuota de un préstamo con cuota constante:

Valor futuro = 0 (no esperamos que quede ningún importe pendiente)

Pago al final del periodo = 0;Pago al inicio del periodo = 1 (leasing)

Las mismas variables de la fórmula anterior

Ejercicio 9

Temas financieros

Solvencia:• Capacidad de pago bajo

distintos escenarios de interés.

Liquidez:• Planificación de tesorería.

Técnicas de Excel

• Validación de datos simple• Otras funciones

– Funciones de fecha:• HOY• DIA• MES• AÑO

– Funciones financieras:• PAGO


Ejercicio 10: Riesgo operativo

El equipo directivo de Demvrek quiere ahondar en el análisis de la división “vino” y le interesaría calcular:

1. ¿Cuánto pueden variar los beneficios de la empresa si su volumen actual de ventas decrece?

2. ¿Hasta dónde puede caer su volumen de ventas o cuánto volumen de ventas necesita para alcanzar su punto de equilibrio?

3. ¿Cuánto pueden variar sus beneficios si para mantener su volumen actual de ventas ha de variar el precio medio de sus vinos?

Las respuestas dependen de la estructura de costes de Demvrek.


¿Qué son “estructura de costes”, “riesgo operativo” y apalancamiento operativo”?

Estructura de costes es el mix que tiene una empresa de gastos fijos y variables.

Riesgo operativo es el riesgo que tiene una empresa derivado del nivel de sus gastos fijos. A más gastos fijos, más dificultad para mantener la rentabilidad si las ventas decrecen.

Apalancamiento operativo es un indicador del nivel de gastos fijos de una empresa. Se dice que cuanto mayor es el nivel de gastos fijos de una empresa, mayor es su Apalancamiento operativo.


Grado de Apalancamiento Operativo (GAO)

• Es una medida de la sensibilidad del beneficio operativo de una empresa a una variación en la demanda de su producto o servicio.

• Es decir, mide en qué % varía el margen operativo de la empresa por cada 1% que varía su volumen de ventas partiendo de un volumen inicial.

• Por tanto, es una manera de cuantificar el riesgo operativo de una empresa.

¿Cómo podemos calcular todo esto?


Análisis Coste-Volumen-Beneficio (CVB)


𝑩𝒆𝒏𝒆𝒇𝒊𝒄𝒊𝒐 𝒕𝒐𝒕𝒂𝒍=𝑽𝒆𝒏𝒕𝒂𝒔𝒕𝒐𝒕𝒂𝒍𝒆𝒔−𝑪𝒐𝒔𝒕𝒆𝒔𝒕𝒐𝒕𝒂𝒍𝒆𝒔𝐵𝑒𝑛𝑒𝑓𝑖𝑐𝑖𝑜𝑡𝑜𝑡𝑎𝑙=𝑄𝑃−𝑄𝑉 −𝐹

𝐹𝑃−𝑉

=𝑄

𝐵𝑒𝑛𝑒𝑓𝑖𝑐𝑖𝑜𝑇𝑜𝑡𝑎𝑙=Q (P−V )− F

Q = Cantidad de unidades vendidas(y producidas)P = Precio por unidad (vendida y producida)V = Costes variables por unidad (que suponemos constantes)F = Costes fijos totalesP – V = Margen de contribución por unidad

Punto de equilibrio: Beneficio total = 00=Q (P−V )−F

𝐹+𝑄𝑉𝑄

=𝑃=𝐶𝑜𝑠𝑡𝑒𝑠 𝑇𝑜𝑡𝑎𝑙𝑒𝑠

𝑈𝑛𝑖𝑑𝑎𝑑𝑒𝑠𝑣𝑒𝑛𝑑𝑖𝑑𝑎𝑠

𝑮𝑨𝑶=𝑣𝑎𝑟𝑖𝑎𝑐𝑖ó𝑛% 𝑑𝑒𝑙𝐵𝑒𝑛𝑒𝑓𝑖𝑐𝑖𝑜 𝑡𝑜𝑡𝑎𝑙𝑣𝑎𝑟𝑖𝑎𝑐𝑖ó𝑛% 𝑑𝑒𝑢𝑑𝑠 .𝑣𝑒𝑛𝑑𝑖𝑑𝑎𝑠

𝑮𝑨𝑶=𝑄 (𝑃−𝑉 )

𝑄 (𝑃−𝑉 )−𝐹=𝑀𝑎𝑟𝑔 .𝐶𝑜𝑛𝑡𝑟 .𝑇𝑜𝑡 .

𝐵𝑒𝑛𝑒𝑓𝑖𝑐𝑖𝑜𝑇𝑜𝑡 .Q(P – V)= Margen de contribución total

Ejercicio 10: ¿Cómo trasladamos todo esto a Excel?


a) ¿Qué información necesitamos?• Gastos fijos y variables• Conocimiento de un modelo teórico

b) ¿De qué información disponemos?

• Ingresos y gastos agregados (contabilidad grupos 6 y 7)• Conocimiento de la operativa de la empresa

c) ¿Cómo manipular la información de la que disponemos para obtener la información que necesitamos?

• Categorizar gastos establecer un criterio• Trasladar las relaciones matemáticas del modelo teórico al modelo Excel

Procedamos…

Extensión del análisis CVB


0 €

1,000,000 €

2,000,000 €

3,000,000 €

4,000,000 €

5,000,000 €

6,000,000 €

7,000,000 €

8,000,000 €

9,000,000 €

0 500000 1000000 1500000 2000000 2500000 3000000 3500000

Unidades vendidas

Gráfica Coste - Volumen - BeneficioVentas Costes

Supuestos del análisis CVB

• Las funciones de ingresos y costes son lineales, lo cual implica que:– Los precios de venta e mantienen constantes independientemente

del volumen de venta:• No necesariamente; depende de la elasticidad de la demanda

– Los costes variables unitarios se mantienen constantes• No necesariamente; depende de factores tales como el coste por volumen

de materia prima o intereses de cartera.– Los costes fijos totales se mantienen constantes

• No necesariamente; depende de la capacidad instalada: los costes fijos totales se mantendrán constantes en tanto que el volumen de ventas no exija mayores inversiones que incrementen la estructura de la empresa, es decir, sus costes fijos.


Supuestos del análisis CVB

• Unidades producidas = unidades vendidas– Totalmente cierto en servicios– No del todo cierto en empresas productivas– Muy poco cierto en el caso de una bodega (nada cierto para el caso

de Bodegas Demvrek)

• Producción y venta de un solo producto


El modelo CVB no es perfecto…

…pero sí nos da una idea de:

• La estructura de costes actual;

• Hasta dónde puede caer el volumen de ventas manteniendo la estructura de costes actual Punto de Equilibrio

• La sensibilidad del beneficio a una variación en el volumen de ventas partiendo del volumen y estructura de costes actual GAO

• Cómo y qué tanto aproximadamente se desplaza el punto de equilibrio si cambiamos la estructura de costes (convertimos un coste variable en fijo o viceversa) Análisis escenarios

Todas ellas, variables importantes para la gestión.


CVB un paso más allá


𝐵𝑒𝑛𝑒𝑓𝑖𝑐𝑖𝑜𝑇𝑜𝑡𝑎𝑙=Q (P−𝐕 )−F

Tal como lo hemos hecho, el análisis CVB supone la producción y venta de un solo producto.

Sería interesante conocer el la estructura de costes, el margen de contribución y los costes unitarios por producto o línea de producto. Ello nos permitiría:

• Analizar escenarios de variación en el mix de ventas Política comercial (precios mínimos, negociación con clientes, etc.)

• Saber qué costes tienen mayor incidencia en el coste de cada unidad y si podemos hacer algo para optimizarlos Activity Based Management

Además, el análisis CVB nos habla de costes variables unitarios.


1. El condicional





6. Macros


¿Qué es una matriz?

• Es, básicamente, un conjunto de números ordenados en columnas y filas.

• La “dimensión” de una matriz es su tamaño y su forma: (m filas por n columnas).

• El álgebra lineal o álgebra de matrices es la parte de las matemáticas que trata las operaciones con matrices.

• El álgebra de matrices se aplica en múltiples disciplinas que van desde la economía hasta la química.


Operaciones con matrices

120

Las operaciones con matrices dan como resultado otra matriz y obedecen a reglas específicas. Por ejemplo:• 2 matrices se pueden sumar únicamente si tienen la misma dimensión.• El resultado es una tercera matriz cuyos elementos son la suma de los

elementos de las otras dos guardando su misma posición.

1 3 62 4 15 8 9

A

5 2 83 6 45 7 9

B

+ =6 5 147 10 510 15 18

C

1 3 62 4 15 8 9

K

5 2 83 6 45 7 9

L

– =−4 1 −2−1 −2 −30 1 0

M


Operaciones con matrices

121

• 2 matrices se pueden multiplicar únicamente si el número de columnas de la primera coincide con el número de filas de la segunda.

• El resultado es una tercera matriz cuya dimensión es el número de filas de la primera y el número de columnas de la segunda.

[𝑎¿𝑏¿𝑐 ]

A

𝑥𝑦𝑧

B

x = [𝑎𝑥+𝑏𝑦+𝑐𝑧 ]

C

[𝑎¿𝑏¿𝑐 ]

R

𝑥 𝑚𝑦 𝑛𝑧 𝑝

S

x = [𝑎𝑥+𝑏𝑦+𝑐𝑧¿𝑎𝑚+𝑏𝑛+𝑐𝑝 ]

T


Ejercicio 11, idea clave:

• Hay funciones cuyo resultado se devuelve en varias celdas Funciones matriciales


Veámoslo en Excel…

Ejercicio 11, idea clave: Introducción de funciones matriciales en Excel


1. Introducir la función u operación en una celda

2. Seleccionar el rango al que queremos aplicar la función u operación

3. F2

4. Ctrl + Mayús. + Intro.

2. Ctrl + Mayús. + Intro.

Nota: Si no presionáis esta combinación de teclas, la función devolverá un código de error o un resultado erróneo.

Funciones matriciales


Operadores

Operador

* Y

+ O

<> Diferente a

= Igual a

Sintaxis del condicional en funciones matriciales


{ = (cond1.)*((cond.2)+(cond.3)) }

Devuelve una matriz compuesta por 1s y 0s: 1 para los elementos de la matriz original que cumplen esta condición y 0 para los que no.

Operador lógico “ Y ”

Operador lógico “O ”

{ = SUMA (cond1.)*((cond.2)+(cond.3)) }

Devuelve un número = a la suma de los elementos de la matriz anterior; es decir cuenta los elementos de la matriz original que cumplen una condición.

1. Esta “condición compuesta” puede tener tantas “condiciones simples” como necesitemos. máxima flexibilidad

y poder de cálculo2. A esta condición podemos aplicarle una función:

Sintaxis del condicional en funciones matriciales


{ = (cond1.)*((cond.2)+(cond.3))*A5:B9 }

Devuelve una matriz compuesta por:

a. los elementos de la matriz original que cumplen esa condición

b. y por 0s en el lugar de los elementos que no la cumplen.

Operador lógico “ Y ”

Operador lógico “ O ”

A este condicional también le podemos aplicar una función:

{ = SUMA (cond1.)*((cond.2)+(cond.3))*A5:B9 }

Devuelve un número = a la suma de los elementos de la matriz anterior, es decir, suma [el importe de] los elementos que cumplen esa condición.

“Condiciones de uso” de funciones matriciales

1. La función aplicada a un “condicional compuesto” opera sobre todos los elementos de la matriz resultante, por lo tanto…

2. Aunque en teoría podamos asignar cualquier función a un “condicional compuesto” y así transformar dicha función en una función matricial, hay que detenernos a pensar si el resultado que nos arroja es el deseado.


Ejercicio 12: Aplicación de las funciones matriciales

Al equipo directivo de Demvrek le gustaría tener el coste unitario y la estructura de costes unitarios de cada tipo de botella que comercializa. Es decir, sus costes unitarios desglosados por actividad y tipo de coste.

Con ello persigue:

• Analizar escenarios de variación en el mix de ventas de sus vinos para elaborar una política comercial coherente con sus objetivos y la situación actual del mercado (precios mínimos, negociación con clientes, etc.)

• Saber qué costes tienen mayor incidencia en el coste de cada unidad para intentar optimizarlos Activity Based Management (ABM).



• Dentro de sus operaciones de elaboración y comercialización de vino (“división vino”) Demvrek vendió 2 millones de botellas en el último ejercicio.

• La empresa comercializa 6 tipos de vino: Tinto Joven, Blanco, Rosado, Crianza, Reserva, y Especial (alta expresión).

• Su volumen de ventas ha crecido a un ritmo constante del 3% anual y la empresa prevé que dicho crecimiento continúe.

• El 36% de sus ventas son exportaciones.

• La empresa tiene 150 hectáreas de viñedo que producen uva tinta.



• El total de la producción de uva tinta se destina a la producción de Crianza, Reserva y Especial.

• El Tinto Joven se elabora con una mezcla de uva propia y uva tinta D.O. que la empresa compra cada año.

• El vino únicamente se embotella cuando se va a vender, por lo que los gastos de embotellado se consideran gastos de comercialización.


Reparto de costes del ejercicio por unidad de producto: Visión de coste del ABC

131

Materia Prima0,60 € 22%

Elaboración0,73 € 27%

Comercial1,23 € 45%

0,16 € 6%

2,71 € 100%

A invent.

Materia Prima0,60 € 21%

Elaboración0,73 € 25%

0,16 € 6%

Comercial1,38 € 48%

2,86 € 100% Crianza - Export

ABC + CVB: Estructura de costes por producto

132

VD, 0.93 €

VI, 0.48 €

FD, 0.37 €

FI, 0.18 €

0%10%20%30%40%50%60%70%80%90%

100%

Estructura de Costes

Joven Nacional

VD, 0.86 €

VI, 0.48 €

FD, 1.21 €

FI, 0.18 €

0%10%20%30%40%50%60%70%80%90%

100%


Crianza Nacional

VD, 0.92 €

VI, 0.64 €

FD, 0.37 €

FI, 0.18 €

0%10%20%30%40%50%60%70%80%90%

100%

Estructura de costes

Joven Export

VD, 0.85 €

VI, 0.64 €

FD, 1.20 €

FI, 0.18 €

0%10%20%30%40%50%60%70%80%90%

100%


Crianza Export


1. El condicional





6. Macros


¿Qué es un/una “Macro”?

Es una lista de instrucciones en lenguaje informático (Visual Basic for Applications - VBA) que el usuario puede programar dentro de Excel para crear procedimientos o funciones propias.


¿Cómo se programa una macro en Excel?

1. Grabadora de macros;

2. Copiar y pegar un código en un módulo dentro del editor de Visual Basic;

3. Escribir directamente un código en un módulo del editor de Visual Basic;

4. Una combinación de las anteriores.


Para acceder al editor de Visual Basic


1. Activar el menú de Programador: Archivo Opciones Personalizar Cinta de Opciones Activar categoría “Programador”

2. En la cinta de opciones: Programador Visual Basic (Código)

3. O bien, simplemente…

Alt + F11

Ejercicio 13: Grabar una macro


1. En la cinta de opciones: Grabadora de Macros(Código)

2. Combinación de acceso rápido

Alt + G+ R

Ejercicio 13: Grabar una macro


• Activar grabadora de macros• Desagrupar filas• Desagrupar columnas• Seleccionar columnas G y H• Botón derecho Insertar• Seleccionar el rango D5:D93• Copiarlo• Pegar valores en la celda G5• Ajustar ancho de columna• ESC y detener grabadora de macros

Pinchando en el 2

Muy importante:

NO guardar el ejercicio hasta que se os instruya hacerlo.

Información sobre “Macro 1” en el editor VBA


1. Procedimiento Sub

2. Nombre de la macro

3. Que el procedimiento no tiene argumentos

4. Ubicación del código

5. Código

Principales tipos de procedimientos o rutinas

1. Suba. Hace algo; ejecuta acciones es capaz de manipular / cambiar la apariencia

de una hoja de cálculo;

b. Se puede llamar y ejecutar desde un control en una hoja de cálculo, la lista de macros o desde otro macro.

2. Private Suba. Misma funcionalidad que un procedimiento Sub, excepto que únicamente se

puede llamar – y ejecutar – desde otros procedimientos en el mismo módulo;

b. No aparece en la lista de macros.

3. Functiona. Devuelve un valor o una matriz, igual que en las funciones integradas;

b. Se puede llamar desde la celda de una hoja de cálculo como si fuese una función integrada en Excel.


Manipular/optimizar un código grabado


Nota: Al introducir un apóstrofo antes de una línea de código, dicha línea deja de ser ejecutable (se convierte en un comentario) y se muestra en verde.

Ejercicio 14: Introducir un código VBA


1. En el editor de visual basic, insertar un módulo:

2. Escribir un código que arroje que devuelva una caja de texto con un mensaje.

Formas de ejecutar una macro


3. Mediante un control de forma, un control ActiveX o una caja de texto a la que se le asocia una macro.

1. A través de la lista de macros del menú “Programador”;

2. Mediante una combinación de teclas previamente asignada;

Diferencia entre control de forma, ActiveX y caja de texto

Control de forma

• Poco margen de personalización

• La macro reside en un módulo VBA y por tanto es accesible desde cualquier hoja del libro o archivo.

Control ActiveX

• Mayor margen de personalización

• La macro reside en el mismo control y por tanto, en la hoja donde reside ese control no accesible más que desde esa hoja.


Caja de Texto

• Máximas opciones de personalización

• Se le puede asignar cualquier macro sin importar en dónde resida esa macro.

Para nuestros propósitos, por su flexibilidad, sencillez y versatilidad, la caja de texto es suficiente en la mayoría de los casos para ejecutar un procedimiento.

Elementos básicos – los “ladrillos” para programar una rutina en VBA

1. Función MsgBox

2. El condicional

3. Construcciones– For-Each-Next– With-End With

4. Bucles– For-Each contador– Do Until o Do While


Ejercicio 15: el condicional en VBA

Varía un poco la sintaxis, pero la estructura es la misma que hasta ahora hemos visto:

If condición Then

[acción a realizar si se cumple la condición]

Else

[acción a realizar si no se cumple la condición]

End If



1. Función MsgBox

2. El condicional




Ejercicio 16: la construcción For Each – Next

Hace algo con o en cada elemento de un grupo (por ejemplo, un rango)

For Each elemento In grupo

[acción a realizar en o con cada elemento]

Next elemento


Ejercicio 16: la construcción With – End With

Realiza varias operaciones sobre un mismo objeto o manipula sus propiedades.

With elemento

[propiedades del objeto deseadas]

End With



1. Función MsgBox

2. El condicional




¿Qué es un bucle?

Es una o un conjunto de instrucciones que se repiten una y otra vez hasta que se cumple una condición o se llega al final de un contador.


Ejercicio 17: el bucle For-Next

Realiza operaciones repetitivas utilizando un contador

For contador = inicio To final [valor salto]*

instrucciones

Next contador

* valor salto se refiere a un número entero positivo o negativo que indica si la cuenta del contador es progresiva o regresiva y de cuánto en cuánto avanza o retrocede


Ejercicio 17: el bucle Do While

Realiza operaciones repetitivas mientras se cumple una condición:

Do While condición

instrucciones

Loop


Ejercicio 18: Juntándolo todo. Aplicación práctica.


Tenemos un balance de sumas y saldos con subtotales y necesitamos deshacernos de dichos subtotales.

¿Cómo hacer?


1. Función MsgBox

2. El condicional




Ejercicio 19: Formas de usuario

Es una herramienta de Excel que permite personalizar la interacción de una hoja de cálculo con el usuario.

Utiliza líneas de código similares a las de un procedimiento Sub.


Recapitulación final


6 elementos básicos para desarrollar un modelo dinámico capaz de cuantificar variables de gestión relevantes:

Condicional

Simple• Su funcionamiento• Estructura básica de una

función

Operaciones condicionales

• SUMAR.SI• SUMAR.SI.CONJUNTO • Rentabilidad: costes de

financiación por cliente• Solvencia: gestión de riesgo• Liquidez: gestión de cobrosAnidado

• Estructura básica de una función anidada

• Operadores lógicos “Y”, “O”



Búsqueda y texto

• BUSCARV• BUSCARH• IZQUIERDA• DERECHA• EXTRAE• ESPACIOS

• Mejora de procesos Eficiencia Rentabilidad

• Liquidez: gestión de tesorería• Rentabilidad: análisis de

costes financieros

Validación de datos

• Simple• Dependiente• INDIRECTO• SUSTITUIR

• Categorizaciones• Análisis de escenarios

modelos dinámicos• Rentabilidad por división/línea

de negocio• Análisis Coste-Volumen-

Beneficio, cálculo de punto de equilibrio y GAO Rentabilidad



Fórmulas y funciones financieras

• VF• VA• PAGO• Margen de

contribución• Punto de equilibrio

Solvencia / liquidez:• Cálculo de cuotas e intereses

pagados en un préstamo planificación de tesorería

• Análisis de inversiones riesgo operativo

Funciones matriciales

• Devolver, o contar elementos

• Suma condicional• Otras operaciones

Rentabilidad• Cálculo de escandallo de

costes y margen de contribución unitario

• Análisis de escenarios de variación en e mix de ventas

• Activity-Based-Management



Macros

• MsgBox• Condicional• Construcciones:

For-Each-Next y With-EndWith

• Bucles: For-Each (contador) y Do While o Do Until

Eficiencia operativa• Automatización de procesos y

tareas repetitivas

Formas de usuario

• Botón• Caja de Texto• Caja de Lista

• Interacción con el usuario

Bibliografía


• Excel 2010. Los mejores trucos, John Walkenbach, Edit. Anaya Multimedia, 2011.

• Excel 2010. Programación con VBA, John Walkenbach, Edit. Anaya Multimedia, 2010.

Www.fcervantes.es © 2014 Francisco Guillermo Cervantes Medina.

Documents

Transcript of Www.fcervantes.es © 2014 Francisco Guillermo Cervantes Medina.