Me preguntaba un lector del blog que significaba la expresión “estimar” dentro de la sintaxis de la formula de TIR en Excel. Repasaremos los argumentos de la función TIR: =TIR(valores;estimar) La función TIR devuelve la tasa interna de retorno de los flujos de caja representados por los importes del argumento valores. Estos flujos de caja no tienen por que ser constantes; pero sí deben ocurrir en intervalos regulares o periódicos. Para obtener una TIR con flujos no regulares deberemos emplear la función TIR.NO.PER =TIR.NO.PER(valores; fechas) Por tanto ya sabemos cuáles serán los valores que determinan nuestro primer argumento, los importes de los flujos futuros (al menos uno de éstos debe ser negativo y el resto positivos). ¿Y el segundo argumento 'estimar'?. Efectivamente el segundo argumento de la función TIR ayuda a Excel a encontrar ese valor de la tasa interna buscada. Por lo que yo se, Excel emplea métodos de prueba y error para encontrar un valor de TIR en unos flujos dados, devolviendo un error si no fuera posible hallar una solución válida. En todo caso siempre se puede omitir esa estimación, tomando Excel como predeterminado una rentabilidad del 10%. La realidad, independientemente del valor agragado a estimar, es que prioriza en busca de un resultado de una TIR que haga igual el VAN de los flujos planteados a cero (piensa en términos financieros). Podríamos probar a aplicar la función TIR en un mismo flujo con diferentes estimaciones, y concluiríamos que siempre devuelve el mismo valor; en todo caso es un tema de precisión en la busqueda del resultado de la TIR. Hay varios ejemplos del uso de esta función en la etiqueta de Financieras
Daremos esta vez una nueva aplicación a la función CONTAR.SI. Nuestro pequeño comerciante decidió en su momento llevar un registro de los anuncios o campañas de publicidad que llevaba a cabo en distintos medios (TV, radio y prensa); ya entonces tenía claro que necesitaría cruzar esa inversión en marketing con el número de clientes y manuales vendidos. Este comerciante tiene la información de sus manuales vendidos cada día y de la caja o ingresos que realizó, por otro lado en una tabla diferente tiene los días en los que en cualquiera de los medios anunció su producto. Ahora necesita analizar el efecto de su inversión en publicidad con sus ventas. Para ello primero añadió una nueva columna 'Publicidad' a su origen de datos principal, de donde se nutre su Tabla dinámica, en la cual mediante la función =CONTAR.SI(rango; criterio) en nuestro caso =CONTAR.SI(Publicidad!$A$2:$A$25;"<="&A2) determina el acumulado de anuncios hasta cada fecha, con la idea de cruzar este dato con sus ventas. Mediante otra nueva columna 'Ventas2' calculamos el número de ventas acumuladas por fechas, empleando la función SUMA sobre las ventas de cada día. =SUMA($B$2:B6) Por último un campo nuevo como cociente entre esas unidades vendidas acumuladas y esos anuncios también acumulados, que nos dirá la relación o influencia que han tenido todos los anuncios en publicidad realizados por el comerciante sobre el número de manuales vendidos.
Para determinar una correlación aplicaremos sobre nuestro rango final, incluyendo los nuevos campos, una Tabla dinámica, de tal forma que podamos obtener un promedio por mes de ese ratio unidades vendidas/publicidad. Entendemos que existe un efecto acumulado de todos los anuncios realizados/emitidos desde el comienzo de la campaña publicitaria.
Podemos interpretar que en enero, donde se han lanzado 8 anuncios en total, y se han vendido 414 manuales, el ratio inversión en publicidad ha sido de 25,15, i.e., por cada anuncio en circulación se vendieron 25 manuales. Para febrero, en cuyo mes se vendieron 2.049 manuales y ya se habían emitido 13 anuncios publicitarios, el ratio se ha incrementado hasta 116. Y la misma interpretación para cada mes restante. Observamos un crecimiento del ratio a lo largo del tiempo, aunque sería un error determinar que el impacto de la publicidad es el único efecto sobre el incremento en las unidades vendidos, es claro que si es un factor importante.
Dando un repaso por lo ya explicado sobre las tablas dinámicas he echado de menos un apartado importante: los campos calculados. Supongamos un listado con varios campos, y por hablar de algo ya conocido, supondremos que hablamos de nuestro pequeño comerciante, del que hablamos en entradas anteriores. Nuestro comerciante ahora necesita saber cual ha sido su precio medio de cada unidad vendida en los meses que lleva abierto el negocio. Para ello decide emplear las tablas dinámicas, en concreto la utilidad de los campos calculados. Partimos de una tabla dinámica que recoge el rango de datos donde se hallan todos nuestros registros, configurada de tal forma que en el área de columnas veamos el número de unidades vendidas y además el importe en euros de lo vendido; cruzado por el campo agrupado en meses y años del área de filas, tenemos la siguiente estructura de Tabla dinámica:
Desde la barra de herramientas 'Tabla dinámica' desplegamos el listado de utilidades y seleccionamos Fórmulas - campo calculado:
En esta ventana diálogo es donde configuramos el nuevo campo calculado; le damos un nombre y posteriormente definimos la fórmula que queremos aplicar sobre ese campo (en general sólo admite operaciones sencillas). Una vez realizada la operación Aceptamos y ya podemos ver en nuestro Listado de campos ese nuevo campo calculado. Remarcar que sólo existe en la memoria interna de la Tabla dinámica, y que nuestro origen de datos permanece intacto. Quedaría entonces nuestra Tabla dinámica como sigue:
La interpretación de los resultados de este campo calculado sería que para enero de 2008 el precio medio obtenido por cada manual que se vendió fue de 204,11 eur, puesto que en todo ese mes se ingresó 84.500 eur y se vendieron un total de 414 manuales (84.500/414 = 204,11 eur/udad). Para descargar el fichero
Solucionaremos hoy una posible situación en la que nos podríamos encontrar en nuestras empresas. Una sociedad realiza una emisión de empréstitos convertibles, de la cual una segunda suscribe una parte de esta emisión. Nos centraremos en la manera que la sociedad compradora analiza este instrumento financiero (para la compradora un activo financiero). Ejemplo: Una sociedad suscribe 100.000 obligaciones convertibles en las siguientes condiciones: fecha de emisión 31/12/2008, valor nominal 2,50 €, valor de reembolso 2,60 €, tipo de interés 2,7185% anual (cupón) pagadero por anualidades vencidas cada 31 de diciembre y una TIR del 4% anual y fecha de vencimiento 3 años. El tipo de interés que generan títulos de naturaleza similar sin el componente de instrumento patrimonio es del 4,50% anual.
La particularidad de ser obligaciones convertibles en acciones nos obliga a tratarlas de una manera algo diferente, ya que no empleamos la TIR de la operación (4% anual - vemos dos formas de calcularla: TIR(valores;estimado) y TIR.NO.PER(valores; fechas)), si no que empleamos la tasa usada para el cálculo de 'componente de pasivo', i.e., la de obligaciones simples para el desarrollo del valor del activo a lo largo de los tres años de vida (recordemos que se clasificó como mantenido hasta el vencimiento). Mediante la función VNA(tasa;valores) conocemos cuál es ese valor de componente de 'pasivo/activo', importe desde el cual calculamos el resto de nuestra tabla de valoración posterior de este instrumento:
Los asientos de la suscripción y del cobro del primer año serían entonces:
Tocaremos hoy otro de los temas novedosos del NPGC 2007 en España, las Obligaciones convertibles en acciones. Definidos en el Plan General de Contabilidad como un instrumento financiero compuesto, ya que es un instrumento financiero no derivado que incluye componentes de pasivo y de patrimonio simultáneamente. Nos dice el PGC: 'Si la empresa hubiese emitido un instrumento financiero compuesto, reconocerá, valorará y presentará por separado sus componentes. La empresa distribuirá el valor en libros inicial de acuerdo con los siguientes criterios que, salvo error, no será objeto de revisión posteriormente: a) Asignará al componente de pasivo el valor razonable de un pasivo similar que no lleve asociado el componente de patrimonio. b) Asignará al componente de patrimonio la diferencia entre el importe inicial y el valor asignado al componente de pasivo. c) En la misma proporción distribuirá los costes de transacción.'
¿Y en que forma nos puede ayudar Excel para calcular estos importes a asignar al componente de pasivo?. Pondremos el siguiente ejemplo. Una sociedad emite una obligacion convertible en las siguientes condiciones: fecha de emisión 31/12/2008, valor nominal 250.000 €, valor de reembolso 262.500 €, tipo de interés 2% anual (cupón) pagadero por anualidades vencidas cada 31 de diciembre y una TIR del 3,6080% anual y fecha de vencimiento 3 años. Los gastos ocasionados por la emisión fueron de 15.000 eur. El tipo de interés que generan títulos de naturaleza similar sin el componente de instrumento patrimonio es del 4% anual.
Para registrar contablemente esta operación, i.e., nuestro pasivo financiero, necesitaremos descontar los flujos de caja esperados de este instrumento financiero, empleando el tipo de interés que tengan títulos de naturaleza similar sin componente de patrimonio, por ejemplo de alguna emisión de obligaciones simples que conozcamos. Para descontar estos flujos utilizaremos la ya conocida función financiera =VNA(tasa, valores)
A partir de este cálculo obtenemos la parte a asignar al componente de patrimonio neto; obteniendo la ponderación de estos componentes sabremos cómo distribuimos los gastos de la operación. El asiento contable que nos quedaría entonces será:
En una entrada anterior vimos cómo con la versión Excel 2003 podíamos obtener o comprobar qué registros de una base de datos estaban duplicados; para lo cual desarrollabamos dentro de la herramienta Formato condicional la opción de Fórmula, empleando la función CONTAR.SI. Veremos hoy que Excel 2007 nos habilita algunas utilidades más dentro de la herramienta, entre otras la de distinguir los valores duplicados. Disponemos de la misma base de datos:
Marcamos el rango de datos del campo DNI y ejecutamos desde el Menú Inicio > Estilos > Formato condicional
Tras aceptar vemos que el resultado es idéntico al obtenido mediante fórmula con la versión Excel 2003. Detallaremos todas las nuevas funcionalidades de la herramienta Formato condicional en Excel 2007 en entradas posteriores.
Nos introduciremos hoy en una herramienta de Excel no excesivamente conocida, los ESCENARIOS. Esta herramienta avanzada nos permitirá analizar las variaciones de determinadas variables dentro de un informe sobre unos resultados finales o intermedios. Un escenario es un conjunto de valores que Excel guarda y sustituye en nuestro informe. Utilizaremos los escenarios para observar la variación en el resultado de nuestro modelo. Tenemos la posibilidad de crear y guardar diferentes grupos de valores, en distintos escenarios y, a continuación, pasar a cualquiera de estos nuevos escenarios para ver distintos resultados. En definitiva es el análisis normal de un modelo, financiero o no, con varios escenarios posibles. Activaremos esta herramienta avanzada escenarios desde el menú Herramientas > Escenarios, activando así el administrador de escenarios, desde el cual crearemos todos nuestros escenarios. Lógicamente, el paso previo será disponer de un modelo en nuestra hoja de cálculo sobre el que analizar la variación de algunas celdas resultantes según la variabilidad de otras celdas vinculadas. Propondremos el siguiente modelo presupuestario, sencillo, para explicar el uso de los escenarios.
En este ejemplo disponemos de una distribución de gastos calsificados como fijos y variables, así como unos ingresos que dependen del número de unidades vendidas y su precio unitario, todos ellos estimados para el ejercicio 2010. Seguramente cualquier controller financiero necesitara analizar en qué forma varían el total de gastos o ingresos, o simplemente el resultado final de acuerdo a la modificación de algunas de las premisas establecidas en este primer modelo. En primer lugar activamos el Administrador de Escenarios y creamos un Escenario que llamaremos normal, y que resume la situación tal cual la hemos planteado, es decir, consideramos los valores asignados en nuestro informe como una Situación inicial. Es muy importante tener muy claro desde un principio cuáles serán las variables a estudiar que en uno u otro escenarios de los que en un futuro se vayan a generar; ya que es muy útil para realizar las comparaciones entre los diferentes escenarios.
Desde el administrador podremos agregar todos los Escenarios que necesitemos (podremos ejecutar este administrador de manera independiente en cada Hoja de cálculo de nuestro libro). Los pasos son dar un nombre al Escenario. Identificar las celdas cambiantes, es decir, cuáles son las variables de estudio. Detallar, si es necesario, una descripción del escenario creado. Y por último asignar valores a esas variables.
y desde este cuadro diáologo cambiaremos los valores de las variables a analizar.
Del mismo modo agregaremos a nuestro Administrador tantos Escenarios como sean necesarios. Para nuestro ejemplo crearemos tres escenarios, un Escenario Normal, un Escenario Optimista y un Escenario Pesimista; en todos ellos analizaremos cómo cambian nuestros resultados en función a la variación de los Costes Fijos del 3T/2010 y del precio unitario de cada unidad vendida del mismo trimestre, ya que entendemos será un trimestre complicado en cuanto a costes estructurales y sabemos que de alguna manera afectará al precio unitario final de cada unidad vendida. Una vez generados nuestros escenarios podremos visualizar sobre nuestro modelo la variabilidad definida. En el fichero adjunto podemos ver los escenarios generados.
Por último mencionar la posibilidad de ver en una hoja resumen todos los escenarios con sus distintos valores por cada variable definida, y cómo afecta a las celdas de resultado seleccionadas. Hemos aprovechado la funcionalidad de Asignar nombres a rangos para dar nombre a las celdas cambiantes (variables) y las celdas de resultado, con el fin de identificar en esta hoja resumen qué corresponde a que.
Existen dos tipos de informes, sin embargo, el más clarificador de las situaciones, a mi modo de ver, es el tipo Resumen, en vez del tipo Tabla dinámica.
Explicaremos cómo desarrollar y cuáles son las ventajas del filtro normal o autofiltro. Propondremos un ejemplo para analizar las posibilidades de esta herramienta. Supongamos un listado de compras realizadas en distintas fechas, en las que adicionalmente disponemos información del código de producto, nombre de éste, nombre del comercial responsable de la compra, unidades del producto comprado, precio unitario firmado y descuento obtenido.
Para aplicar el Autofiltro sobre nuestra pequeña base de datos lo mejor es seleccionarla completamente y luego desde el menú Datos > Filtro > Autofiltro activar este herramienta.
Desde este momento podremos aplicar los filtros sobre los distintos elementos de nuestra base de datos que deseemos. Sin duda las ventajas de disponer de un Filtro sobre nuestra base de datos son muchas. En primer lugar podremos hacer búsquedas directas sobre diferentes elementos de distintos campos al tiempo, es decir, aplicar un filtrado múltiple. Podremos también aplicar ordenados (en sentido ascendente o descendente) sobre un campo determinado sin preocuparnos de desconfigurar la paridad por registros existente. Otra ventaja es la de filtrar de manera personalizada sobre los campos de la base de datos, con la única restricción de aplicar un máximo de dos condiciones sobre cada campo, empleando para ello los distintos operadores lógicos y matemáticos. Por último, para campos con elementos númericos, el autofiltro nos permite seleccionar una muestra de un número a definir por nosotros de elementos que representen un porcentaje de los elementos, ya sea entre los más elevados o los más pequeños, esta es la opción de Autofiltro de los diez mejores. Veamos un breve vídeo con algunos ejemplos sencillos del autofiltro:
En futuras entradas analizaremos las posibilidades del Filtro avanzado.
Desarrollaremos distintas opciones dentro de las validaciones en celdas de Excel. ¿Cuál es la utilidad de esta herramienta?, supongamos una hoja de trabajo que debe circular por distintos departamentos de la empresa y que cada usuario cumplimentará de acuerdo con su función; en estos casos de multiusuario se hace más necesario que nunca el poder discriminar la introducción de datos en nuestras hojas de cálculo. Excel nos proporciona la Validación, en sus distintas variedades, para poder controlar qué tipo de datos se puede registrar en nuestras celdas. Desde el menú datos > Validación activamos la ventana diálogo:
Al desplegar las opciones que nos permite observamos:
Numero entero: permite exclusivamente números enteros.
Decimal: números reales, i.e., enteros y decimales.
Lista: permite desplegar un listado de opciones. Sólo admite valores de esta lista.
Fecha: datos en formato fecha.
Hora: datos en formato hora.
Longitud: celdas con un número de caracteres alfanuméricos determinado.
Personalizada: nos habilita en base a una formula definida por nosotros el poder introducir un dato en la celda.
Combinando esta información con los operadores lógicos (entre, igual, mayor que, menor que, etc) determinados con los valores definidos por el usuario podemos configurar la validación. Algunos ejemplos simples. Ejemplo 1: permitir valores en una celda entre 10 y 100.
Ejemplo 3: Condicionar la introducción de datos en una celda a valores de otra. Por ejemplo, sólo registrar valores en la Celda D2 si y sólo sí en la celda A1 el valor es 1.
Realmente lo explicado hasta ahora es lo que nos permite configurar la entrada de datos en nuestras celdas, sin embargo existen dos pestañas más (Mensaje entrante y Mensaje de error) que ayudan al usuario final de la hoja de cálculo. El mensaje entrante nos habilita para poder describir cuales son las condiciones de validación; pero sólo es descriptivo, no actua sobre las reglas de validación. Podremos dar un título y un detalle textual de estas reglas. Con el mensaje de error podremos determinar el tipo de error que deseamos ver cuando el valor introducido no cumpla con las reglas o condiciones de validación configuradas. Existen tres tipos de error: -Límite: el más restrictivo. Sólo admite valores que cumplan la regla de validación para esa celda. -Advertencia: nos pregunta si a pesar de no cumplir con las condiciones de la validación queremos continuar con el registro del dato. -Información: el más flexible. Permite aceptar el valor erróneo después de informarnos del no cumplimiento. en todos ellos incluiremos, si lo deseamos, un título y un mensaje que aparecerá en caso de incumplimiento de las reglas de validación en una ventana de error.
En ocasiones merece la pena crear un Menú personalizado, adicional a cualquier otro estándar dado por Excel, simplemente por comodidad. Imaginemos que siempre empleamos las mismas herramientas o las mismas macros, entonces será más rápido disponer de todos los accesos directos a ellas en un mismo lugar, en nuestro Menú personalizado. La clave para realizar esta acción es trabajar con la opción de Personalizar dentro del menú Herramientas, ya que es aquí donde encontraremos las diferentes posibilidades para personalizar nuestro menú. Lo vemos en este video.
Lo primero a realizar es colocar un Nuevo menú donde lo necesitemos, para después ir completandolo con los distintos accesos directos existentes en todas las categorías y también aquellas macros, previamente creadas por nosotros. Todo ello sin cerrar la herramienta Personalizar. Mencionar la opción de agregar y personalizar iconos a los distintos elementos del nuevo menú, iconos predeterminados o creados en base a un cuadro pixelado de colores.
Crearemos hoy una macro de Tecla abreviada para ejecutar 'Configurar página' (en realidad podremos ejecutar casi cualquier herramienta o utilidad de Excel). Nos centraremos en la manera fácil de generar una macro, es decir, desde el asistente del Menú Herramientas > Macro > Grabar nueva macro. Desde esta opción podremos replicar tareas de cualquier tipo tantas veces como deseemos, ya que habremos creado una 'nueva' utilidad personalizada.
Esta opción nos desplegará o habilitará la siguiente ventana diálogo, desde la cual definiremos qué teclado abreviado asignar.
Vemos que podemos nombrar nuestra macro, así como asignar cualquier combinación de teclas para su ejecución abreviada (preferiblemente buscar una combinación del teclado que no esté en uso por defecto entre las predefinidas por Excel, ya que nuestras macro personales se superponen a las estándar). También podremos elegir entre guardar nuestra Macro 'Conf_pagina' únicamente en este libro, en otro libro a elegir o bien en nuestras Macros personales (explicaremos en una entrada posterior las diferencias). Una vez completada esta ventana y tras aceptar se abrirá la barra de herramientas 'Detener grabación', desde este momento estamos grabando cualquier hecho que realicemos sobre nuestro libro de excel; ya sea introducir datos sobre celdas o navegar por los distintos menús, en general cualquier click que hagamos reflejará una acción (que se grabará) en nuestra macro. En nuestro ejemplo, y puesto que deseamos poder ejecutar la herramienta 'Configurar página' sin necesidad de navegar por los distintos menús, hemos definido en primer lugar el teclado abreviado que nos permitirá abrir 'Configurar página', mediante la combinación de 'Ctrl+u'; para posteriormente ir clickeando por el Menú Archivo > Configurar página y una vez abierto, definir la configuración predeterminada que normalmente utilicemos; por ejemplo, orientación horizontal y Ajuste de escala de 1 página de alto por 1 página de ancho. Tras finalizar la configuración sólo deberemos Detener la grabación de la macro... y listo. Tenemos nuestra Macro, asociada al teclado abreviado Ctrl+u para configurar en el futuro nuestras páginas (dentro de este libro - dependerá de la elección del lugar de grabación de la macro) con los criterios descritos. Para ejecutarla tenemos varias opciones. La primera es mediante el teclado Ctrl+u. Otra es desde la barra de herramientas de Visual basic con el botón ejecutar macro; o bien accediendo a la misma ventana diálogo de todas nuestras macros con Alt+F8. O generando un menú personalizado, etc.
El ejercicio propuesto en el post se podría haber planteado mediante tablas dinámicas de las siguiente forma: 1. asignar nombre al rango de datos - el listado de proveedores con sus facturados (por ejemplo, 'datos'). 2. crear la Tabla dinámica, incluyendo en el área de datos los campos 'Importe' y 'Código'. Desde la 'configuración' de cada uno de ellos nos aseguramos que nos Resuma por Suma el campo Importe, y por Cuenta el campo Código. Incluiremos, también, en el área de filas el campo 'Importe'. 3. una vez completada la estructura de la Tabla dinámica, realizamos el último paso, que es Agrupar el campo Importe del área de filas. Botón derecho el ratón, Agrupar y mostrar detalles - Agrupar (y agrupamos por ejemplo por 15.000). Veamos los pasos en el siguiente video:
La interpretación es la misma dada que en el ejemplo de Funciones de bases de datos. El número de proveedores cuya facturación anual estuvo entre 0 y 15.000 eur es 120, y el acumulado de todos ellos fue 269.653,91 eur.
