La última entrega para Excel 2007 de teclas abreviadas. Recordemos que muchas de las teclas vistas corresponden a la misma combinación para versiones anteriores de Ms-Excel.
Tabla para Excel 2007:
Tabla para Excel 2007:
jueves, 25 de junio de 2009
Teclas de método abreviado Excel 2007 - II.
Continuamos con algunas de las teclas que podremos 'pulsar' para acelerar nuestro trabajo con Excel 2007:
Tabla para Excel 2007:
Tabla para Excel 2007:
Teclas de método abreviado Excel 2007 - I.
Un clásico en cualquier blog es un listado con algunas de las teclas de método abreviado, para los amantes de la funcionalidad y rapidez.
Mostraremos en este caso algunas de ellas para Excel 2007, en posteriores post entregaremos otras de esa versión y para Excel 2003 (la mayoría de ellas coninciden entre las diferentes versiones de Excel, pero algunas son distintas!!):
Tabla para Excel 2007:
Mostraremos en este caso algunas de ellas para Excel 2007, en posteriores post entregaremos otras de esa versión y para Excel 2003 (la mayoría de ellas coninciden entre las diferentes versiones de Excel, pero algunas son distintas!!):
Tabla para Excel 2007:
miércoles, 24 de junio de 2009
Imprimir Comentarios en Excel.
Brevemente indicaremos cual es la solución para imprimir los comentarios que hayamos incluido en nuestra hoja de cálculo. Diremos que se nos ofrecen dos posibilidades. Bien que en la impesión aparezcan tal cual los vemos en pantalla o bien que todos los comentarios se impriman en una última hoja, a modo de notas y referenciado sobre la celda 'comentada'.
Supongamos que tenemos dos celdas con Comentario, y que deseamos imprimir el documento con esos comentarios visibles. Nos iremos entonces a Menú Archivo -Configurar página - Hoja - Comentarios
y seleccionaremos la opción deseada, obteniendo en nuestro papel impreso bien:
o bien en otra hoja:
Supongamos que tenemos dos celdas con Comentario, y que deseamos imprimir el documento con esos comentarios visibles. Nos iremos entonces a Menú Archivo -Configurar página - Hoja - Comentarios
y seleccionaremos la opción deseada, obteniendo en nuestro papel impreso bien:
o bien en otra hoja:
Rangos dinámicos: función DESREF.
No podemos pasar por alto una de las utilidades más empleadas a la hora de trabajar en Excel: los 'rangos dinámicos'.
Definiremos primero qué entendemos por rango dinámico en Excel; sabemos que podemos nombrar los rangos que utilizamos, pero lo habitual es que estos rangos no sean fijos, es decir, no tenga siempre (a lo largo de nuestro trabajo con la Base de datos en cuestión) la misma dimensión, i.e., el mismo número de registros. Es en este punto cuando Excel nos proporciona la posibilidad de crear un rango dinámico. Y lo haremos desarrollando la función DESREF, anidando otra función importante como CONTARA en ella.
Para visualizar el resultado que obtendremos combinando ambas funciones, ejercitaremos otra herramienta de Excel como es la validación (se explicará en su momento).
Supongamos un listado de paises, que no tenemos aún finalizado, i.e., se incrementa (o decrece) con el tiempo. Lo primero que tenemos que realizar es el crear un nombre, al que asignaremos la característica de dinámico mediante la función:
=DESREF(Hoja1!$A$2;;;CONTARA(Hoja1!$A:$A)-1;1)
En esta ocasión empleamos DESREF para determinar un rango que empieza en la celda A2 y que tendrá un alto dado por la función CONTARA(A:A)-1, es decir, cuenta todas las celdas no vacias de la columna A, y le resta Uno para discriminar el rótulo de la columna.
Una vez generado el nombre 'pais', ya podremos emplearlo como se comentó anteriormente con la herramienta Validación con la característica Lista:
que aplicado en la celda C4 nos permite desplegar el listado de paises:
nada nuevo hasta aquí. La verdadera ventaja de esta función es visible cuando incrementamos el listado de paises; situación por la que no nos tendremos que procupar a la hora de redefinir rangos ni funciones, ya que tal cual quedó configurada reconocerá los nuevos elementos del listado.
Cuenta el número de elementos existentes en la columna A (le resta uno), o que nos dá a altura del rango dinámico a contar desde la celda de inicio A2.
Nuestra función DESREF, aplicada en esta forma, es igualmente válida si pretendiéramos ampliar dinámicamente tanto el alto como el ancho de nuestro rango, ya que los argumentos de la función son:
=DESREF(celda inicio rango;num filas; num columnas; alto; ancho)
Veremos en posteriores entradas otros usos de la función DESREF.
Definiremos primero qué entendemos por rango dinámico en Excel; sabemos que podemos nombrar los rangos que utilizamos, pero lo habitual es que estos rangos no sean fijos, es decir, no tenga siempre (a lo largo de nuestro trabajo con la Base de datos en cuestión) la misma dimensión, i.e., el mismo número de registros. Es en este punto cuando Excel nos proporciona la posibilidad de crear un rango dinámico. Y lo haremos desarrollando la función DESREF, anidando otra función importante como CONTARA en ella.
Para visualizar el resultado que obtendremos combinando ambas funciones, ejercitaremos otra herramienta de Excel como es la validación (se explicará en su momento).
Supongamos un listado de paises, que no tenemos aún finalizado, i.e., se incrementa (o decrece) con el tiempo. Lo primero que tenemos que realizar es el crear un nombre, al que asignaremos la característica de dinámico mediante la función:
=DESREF(Hoja1!$A$2;;;CONTARA(Hoja1!$A:$A)-1;1)
En esta ocasión empleamos DESREF para determinar un rango que empieza en la celda A2 y que tendrá un alto dado por la función CONTARA(A:A)-1, es decir, cuenta todas las celdas no vacias de la columna A, y le resta Uno para discriminar el rótulo de la columna.
Una vez generado el nombre 'pais', ya podremos emplearlo como se comentó anteriormente con la herramienta Validación con la característica Lista:
que aplicado en la celda C4 nos permite desplegar el listado de paises:
nada nuevo hasta aquí. La verdadera ventaja de esta función es visible cuando incrementamos el listado de paises; situación por la que no nos tendremos que procupar a la hora de redefinir rangos ni funciones, ya que tal cual quedó configurada reconocerá los nuevos elementos del listado.
Cuenta el número de elementos existentes en la columna A (le resta uno), o que nos dá a altura del rango dinámico a contar desde la celda de inicio A2.
Nuestra función DESREF, aplicada en esta forma, es igualmente válida si pretendiéramos ampliar dinámicamente tanto el alto como el ancho de nuestro rango, ya que los argumentos de la función son:
=DESREF(celda inicio rango;num filas; num columnas; alto; ancho)
Veremos en posteriores entradas otros usos de la función DESREF.
martes, 23 de junio de 2009
Asignar nombres a rangos.
Estudiaremos hoy las formas de dar nombre a los rangos de celdas en Excel. Esta tarea muy sencilla de realizar nos proporcionará multitud de ventajas (iremos viéndolas en posteriores entradas). Lo primero que necesitamos para 'Definir nombres' es un rango, i.e., una o muchas celdas (continuas o dispersas por nuestra hoja). Supondremos que tenemos un listado de diferentes clientes con alguna información adjunta; ya que posteriormente en el ejemplo trabajaremos sobre este rango, nos decidimos por Dar nombres al rango en cuestión.
Vemos que en nuestro listado tenemos varias columnas, por lo que podremos definir varios rangos. Para ejercitar las distintas maneras de dar nombre a rangos comenzaremos por la más 'pura', es decir, desde la barra de menús:
asignamos el nombre 'Datos' (el que queramos, obviamente) y si no lo habíamos hecho antes el rango al que hace referencia ese nombre.
De esta forma podremos crear todos los rangos que deseemos o necesitemos, así como modificar los ya existentes. Es importante remarcar que sólo desde esta opción del menú es modificable un Nombre.
La siguiente forma que veremos de Asignar nombre a un rango es la más habitual; haremos uso del 'Cuadro de nombres' que encontramos en la Barra de fórmulas:
Unicamente tendremos que seleccionar el rango de celdas al que deseemos asignar un nombre, y una vez marcado, iremos a este cuadro de nombres y escribiremos el nombre elegido. en nuestro ejemplo seleccionaremos el rango de celdas C1:D10 y desde el cuadro de nombre le asignaremos el de 'pais_saldo'.
Veremos una forma más de Dar nombre a un rango, y es utiizando la herramienta de 'Crear nombres' (Ctrl + Mayus + F3 ó también Menú Insertar-Nombre-Crear)
Seleccionamos en la ventana dónde se encuentran los rótulos o cabecera que queremos emplear para asignar nombres, esdecir, si queremos que elija la primera fila para dar nombre a los rangos por columnas, entonces marcamos 'Crear nombres en fila superior'. Con esta operación disponemos de tantos nombres como columnas tuviera el rango marcado, con el nombre que hubiera en la cabecera del rango o matriz.
Hay que mencionar que el nombre que asignemos a nuestros rangos deben cumplir algunas normas 'ortográficas', ya que no admite determinados caracteres, como por ejemplo espacios en blanco, barras invertidas, asterístos, etc. Excel nos avisará de este incumplimiento.
Siempre podremos ver los nombres existentes, y movernos entre ellos, desplegando el 'Cuadro de nombres' de la barra de fórmulas.
En otra entrada aprovecharemos este ejemplo, con los nombres asignados para realzar un ejercicio usando éstos en diferentes fórmulas o funciones. De momento nos podemos dirigir a una entrada anterior donde ver una aplicación ejemplo buscarv.
Vemos que en nuestro listado tenemos varias columnas, por lo que podremos definir varios rangos. Para ejercitar las distintas maneras de dar nombre a rangos comenzaremos por la más 'pura', es decir, desde la barra de menús:
asignamos el nombre 'Datos' (el que queramos, obviamente) y si no lo habíamos hecho antes el rango al que hace referencia ese nombre.
De esta forma podremos crear todos los rangos que deseemos o necesitemos, así como modificar los ya existentes. Es importante remarcar que sólo desde esta opción del menú es modificable un Nombre.
La siguiente forma que veremos de Asignar nombre a un rango es la más habitual; haremos uso del 'Cuadro de nombres' que encontramos en la Barra de fórmulas:
Unicamente tendremos que seleccionar el rango de celdas al que deseemos asignar un nombre, y una vez marcado, iremos a este cuadro de nombres y escribiremos el nombre elegido. en nuestro ejemplo seleccionaremos el rango de celdas C1:D10 y desde el cuadro de nombre le asignaremos el de 'pais_saldo'.
Veremos una forma más de Dar nombre a un rango, y es utiizando la herramienta de 'Crear nombres' (Ctrl + Mayus + F3 ó también Menú Insertar-Nombre-Crear)
Seleccionamos en la ventana dónde se encuentran los rótulos o cabecera que queremos emplear para asignar nombres, esdecir, si queremos que elija la primera fila para dar nombre a los rangos por columnas, entonces marcamos 'Crear nombres en fila superior'. Con esta operación disponemos de tantos nombres como columnas tuviera el rango marcado, con el nombre que hubiera en la cabecera del rango o matriz.
Hay que mencionar que el nombre que asignemos a nuestros rangos deben cumplir algunas normas 'ortográficas', ya que no admite determinados caracteres, como por ejemplo espacios en blanco, barras invertidas, asterístos, etc. Excel nos avisará de este incumplimiento.
Siempre podremos ver los nombres existentes, y movernos entre ellos, desplegando el 'Cuadro de nombres' de la barra de fórmulas.
En otra entrada aprovecharemos este ejemplo, con los nombres asignados para realzar un ejercicio usando éstos en diferentes fórmulas o funciones. De momento nos podemos dirigir a una entrada anterior donde ver una aplicación ejemplo buscarv.
lunes, 22 de junio de 2009
Formato personalizado de celdas.
Dedicaremos unos minutos a explicar el formato personalizado de celdas en Excel. Seguro que todos hemos dado en alguna ocasión algún tipo de formato a algún número (por porcentaje, con el símbolo de alguna moneda, con uno o dos decimales, con el separador de miles...) y en alguna ocasión hemos necesitado un formato muy especial, tipo teléfono o tipo algún código...
Para estas ocasiones es para lo que se definió el formato de celda - número - personalizado; con esta herramienta podremos añadir o formatear cualquier celda con un valor sin perder su esencia de número.
Ejemplos:
Es decir,podemos incluir en cualquier parte del número un texto entrecomillado que lo acompañe, sin modificar su condición de número; por lo que esa celda es operable en todos los sentidos. Y por supuesto le daremos el formato de decimales, colores, etc que deseemos.
Para estas ocasiones es para lo que se definió el formato de celda - número - personalizado; con esta herramienta podremos añadir o formatear cualquier celda con un valor sin perder su esencia de número.
Ejemplos:
- Estamos controlando el consumo eléctrico, y los datos recogidos queremos tenerlos identificados con sus magnitudes, i.e., Kw/hora. Para ello definiremos un frmato personalizado de número:
- Tomamos la lectura del contador del agua:
- Si pretendemos dar una máscara de entrada tipo número de teléfono, escribiríamos en el campo tipo: 00 000 00 00 (son importantes los espacios)
- si queremos que los números con dos decimales positivos sean de color azul y los negativos en rojo(sin el signo -) escibiremos: [Azul]#.##0,00;[Rojo]#.##0,00
Es decir,podemos incluir en cualquier parte del número un texto entrecomillado que lo acompañe, sin modificar su condición de número; por lo que esa celda es operable en todos los sentidos. Y por supuesto le daremos el formato de decimales, colores, etc que deseemos.
viernes, 19 de junio de 2009
Ejemplo TIR y VF: Inversiones mantenidas hasta el vencimiento en el NPGC 2007.
Antes de comenzar a desarrollar el asunto de esta entrada quería explicar (para los usuarios no españoles) el significado de las siglas NPGC, ya que supongo que puede llegar a resultar algo confuso. En España con fecha 01/01/2008 entró en vigor el Nuevo Plan General de Contabilidad (NPGC) según REAL DECRETO 1514/2007, de 16 de noviembre, con el que se pretende acercar formas y métodos de trabajo a un modelo único europeo. Este cambio ha supuesto en nuestras contabilidades una serie de ajustes y nuevos criterios de valoración, que es lo que tratamos de aclarar con algunos ejemplos en este blog, especialmente cuando se requiere la ayuda de Excel (base de nuestro Foro). Bien, aclarado este aspecto, comenzaramos con otra aplicación de Excel a nuestra vida laboral.
En este ocasión nos valdremos de las funciones financieras TIR y VF para obtener la forma correcta de registrar y valorar la Inversión en activos financieros clasificados como mantenidos hasta el vencimiento (Inversiones a largo plazo en obligaciones, bonos u otros valores representativos de deuda, incluidos aquellos que fijan su rendimiento en función de índices o sistemas análogos).
Supongamos la compra de 100 obligaciones en su emisión de fecha 01/01/2009 con las siguientes condiciones de emisión:
Lo primero a calcular es , sabiendo la normativa contable a aplicar, el tipo de interés efectivo, i.e., la TIR de la operación. En nuestro caso lo podemos calcular de manera sencilla con la función TIR aplicada sobre el rango $E$6:$H$6 en el que figuran los flujos de efectivo esperados:
una vez conocida la TIR que tendremos que aplicar en nuestro operación procedemos a cálculo de la vida de este empréstito. Recordar sólo que debemos valorarlo siguiendo el método de Coste amortizado, o lo que es lo mismo, actualizando los flujos con la TIR obtenida; lo que haremos empleando la función de Excel VF(tasa, núm periodos, pagos, valor actual). Obtendremos entonces la siguiente tabla de la vida del empréstito:
Vemos que al emplear la función VF hemos indicado sólo UN periodo de actualización, ya que entendemos la tasa como anual-al igual que los peridos de la operación-.
Viendo la totalidad de la operación de forma gráfca quedaría algo parecido a lo siguiente:
Quedando por último el registro contable de la operación, en nuestro caso sólo mostraremos la contabilización del primer año.
Para una mayor comprensión de lo explicado adjuntamos un link donde poder descargarse el fichero de Excel con la solución, además de un complemento normativo.
Finalmente mencionar que este mismo ejemplo serviría para calcular la misma operación de emisión de obligaciones pero entendiéndola como un pasivo financiero mantenido hasta el vencimiento. Sólo cambiaría la forma de valorararlo inicialmente, ya que en este caso habría que restar al valor de la contraprestación entregada los gastos ocasionados en la transacción.
En este ocasión nos valdremos de las funciones financieras TIR y VF para obtener la forma correcta de registrar y valorar la Inversión en activos financieros clasificados como mantenidos hasta el vencimiento (Inversiones a largo plazo en obligaciones, bonos u otros valores representativos de deuda, incluidos aquellos que fijan su rendimiento en función de índices o sistemas análogos).
Supongamos la compra de 100 obligaciones en su emisión de fecha 01/01/2009 con las siguientes condiciones de emisión:
Lo primero a calcular es , sabiendo la normativa contable a aplicar, el tipo de interés efectivo, i.e., la TIR de la operación. En nuestro caso lo podemos calcular de manera sencilla con la función TIR aplicada sobre el rango $E$6:$H$6 en el que figuran los flujos de efectivo esperados:
una vez conocida la TIR que tendremos que aplicar en nuestro operación procedemos a cálculo de la vida de este empréstito. Recordar sólo que debemos valorarlo siguiendo el método de Coste amortizado, o lo que es lo mismo, actualizando los flujos con la TIR obtenida; lo que haremos empleando la función de Excel VF(tasa, núm periodos, pagos, valor actual). Obtendremos entonces la siguiente tabla de la vida del empréstito:
Vemos que al emplear la función VF hemos indicado sólo UN periodo de actualización, ya que entendemos la tasa como anual-al igual que los peridos de la operación-.
Viendo la totalidad de la operación de forma gráfca quedaría algo parecido a lo siguiente:
Quedando por último el registro contable de la operación, en nuestro caso sólo mostraremos la contabilización del primer año.
Para una mayor comprensión de lo explicado adjuntamos un link donde poder descargarse el fichero de Excel con la solución, además de un complemento normativo.
 |
| TIR-VF Inv fras ma... |
| Hosted by eSnips |
Finalmente mencionar que este mismo ejemplo serviría para calcular la misma operación de emisión de obligaciones pero entendiéndola como un pasivo financiero mantenido hasta el vencimiento. Sólo cambiaría la forma de valorararlo inicialmente, ya que en este caso habría que restar al valor de la contraprestación entregada los gastos ocasionados en la transacción.
jueves, 18 de junio de 2009
Un ejemplo de BUSCARV sobre dos tablas de busqueda.
Nos podría ocurrir que necesitaramos realizar una busqueda de elementos de un campo de nuestras base de datos sobre distintas tblas auxiliares de donde recuperar algún otro tipo de información relacionada. El problema en este caso es dirigir el BUSCARV a la matriz de busqueda adecuada en cada caso. Para solucionar este pequeño inconveniente emplearemos la funciones BUSCARV, INDIRECTO y SI.
En nuestro ejemplo disponemos de dos tablas auxiliares con los listados de clientes y proveedores, rangos a los que tenemos que asignar un nombre:
cliente := $C$9:$D$13
proveedor := $C$16:$D$20
la clave del correcto funcionamiento de este ejercicio es, precisamente, el buen uso de esta definición de nombres sobre los rangos de las tablas auxiliares. Vemos las tablas en cuestión.
Una vez definidos los rangos y dados los nombres, procedemos a realizar la relación entre tablas mediante la función BUSCARV. Aplicamos BUSCARV sobre la tabla principal donde aparecen los códigos a relacionar:
y la función a aplicar sobre cada celda de la columna F sería:
=BUSCARV(F2;INDIRECTO(SI(IZQUIERDA(F2;2)="Cl";"cliente";"proveedor"));2;0)
donde la explicación sería la siguiente. En primer lugar determinamos con la función SI(IZQUIERDA(código;2)="Cl";"cliente";"proveedor") si el código a buscar es un 'cliente' o un 'proveedor', lo hacemos observando que los códigos de Cliente comienzan siempre por "Cl", es decir, que IZQUIERDA(código;2)="Cl"; lar referencias que nos interesa que nos devuelva esta función SI son los nombres previamente definidos ("cliente" y "proveedor"), ya que mediante la función INDIRECTO se convierten en un rango entendible por Excel como la 'matriz de busqueda' (argumento de BUSCARV), por lo que sólo nos queda indicarle el número de columna de estas matrices de busqueda, i.e., el campo de las tablas auxiliares que nos interesaba conocer. Para aplicar este ejemplo es necesario que las tabls auxiliares (cliente y proveedor) tengan una estructura de campos similar -al menos que el campo a relacionar se encuentre en el mismo orden de columna.
En nuestro ejemplo disponemos de dos tablas auxiliares con los listados de clientes y proveedores, rangos a los que tenemos que asignar un nombre:
cliente := $C$9:$D$13
proveedor := $C$16:$D$20
la clave del correcto funcionamiento de este ejercicio es, precisamente, el buen uso de esta definición de nombres sobre los rangos de las tablas auxiliares. Vemos las tablas en cuestión.
Una vez definidos los rangos y dados los nombres, procedemos a realizar la relación entre tablas mediante la función BUSCARV. Aplicamos BUSCARV sobre la tabla principal donde aparecen los códigos a relacionar:
y la función a aplicar sobre cada celda de la columna F sería:
=BUSCARV(F2;INDIRECTO(SI(IZQUIERDA(F2;2)="Cl";"cliente";"proveedor"));2;0)
donde la explicación sería la siguiente. En primer lugar determinamos con la función SI(IZQUIERDA(código;2)="Cl";"cliente";"proveedor") si el código a buscar es un 'cliente' o un 'proveedor', lo hacemos observando que los códigos de Cliente comienzan siempre por "Cl", es decir, que IZQUIERDA(código;2)="Cl"; lar referencias que nos interesa que nos devuelva esta función SI son los nombres previamente definidos ("cliente" y "proveedor"), ya que mediante la función INDIRECTO se convierten en un rango entendible por Excel como la 'matriz de busqueda' (argumento de BUSCARV), por lo que sólo nos queda indicarle el número de columna de estas matrices de busqueda, i.e., el campo de las tablas auxiliares que nos interesaba conocer. Para aplicar este ejemplo es necesario que las tabls auxiliares (cliente y proveedor) tengan una estructura de campos similar -al menos que el campo a relacionar se encuentre en el mismo orden de columna.
Ejemplo de INDIRECTO: La conversión en referencias.
Probablemente en alguna ocasión hayamos oído hablar de esta función INDIRECTO(ref;a1), aunque es más probable que no le hayamos encontrado una utilidad para nuestras hojas de cálculo. En esta entrada nos limitaremos a explicar su funcionamiento, dando un pequeño ejemplo de su uso, dejando aplicaciones algo más 'empleables' para futuras entradas.
En definitiva la función INDIRECTO convierte la referencia (una celda, un rango, un nombre, etc) en formato de texto en algo entendible por Excel, es decir, en una referencia en sí misma.
Por ejemplo, disponemos de un pequeño cuadro con algunas de las posibilidades con las que podemos trabajar:
todas las celdas de este cuadro son valores,texto o celdas en blanco; si quisiéramos recuperarlas empleando la función INDIRECTO, construiríamos un nuevo cuadro del siguiente modo:
Observamos como hemos contruido una cadena de texto con CONCATENAR para obtener una 'forma' de referencia, que tras aplicar INDIRECTO sobre ella conseguimos nuestro objetivo, i.e., una referencia válida, y por tanto el valor asociado a esa referencia construida.
En definitiva la función INDIRECTO convierte la referencia (una celda, un rango, un nombre, etc) en formato de texto en algo entendible por Excel, es decir, en una referencia en sí misma.
Por ejemplo, disponemos de un pequeño cuadro con algunas de las posibilidades con las que podemos trabajar:
todas las celdas de este cuadro son valores,texto o celdas en blanco; si quisiéramos recuperarlas empleando la función INDIRECTO, construiríamos un nuevo cuadro del siguiente modo:
Observamos como hemos contruido una cadena de texto con CONCATENAR para obtener una 'forma' de referencia, que tras aplicar INDIRECTO sobre ella conseguimos nuestro objetivo, i.e., una referencia válida, y por tanto el valor asociado a esa referencia construida.
miércoles, 17 de junio de 2009
Ejemplo VA y VNA: Arrendamientos financieros en el NPGC 2007.
En esta ocasión repasaremos el concepto de Valor actual poniendo como ejemplo la contabilización, según el NPGC 2007, de un arrendamiento financiero (por parte del arrendatario. Emplearemos las funciones ya vistas en una entrada anterior (Ejemplo VA y VNA:Partidas a cobrar) de VA y VNA, ambas necesarias para calcular el Valor actual de un flujo.
Nuestro contrato de Arrendamiento financiero (suponemos cumple todos los requisitos legales para definirlo así) tiene las siguientes condiciones:
Siguiendo la normativa contable debemos proceder al cálculo del Valor actual de la operación aplicando a los flujos de pago firmados el tipo de interés implicito en la operación. Y es aquí donde aplicamos alguna de las funciones financieras comentadas:
=VA(tasa;num periodos;pago) para pagos constantes
=VNA(tasa;rango pagos)
Una vez conocido el Valor actual de la operación de Arrendamiento financero, sólo deberemos seguir la Norma de Registro y Valoración 8ª del NPGC 2007, en este caso para determinar la valoración inicial compararemo este Valor actual con el Valor razonable del bien arrendado, activando e nuestr balance dicho bien por el menor de ambos valores. Posteriormente habrá que obtener una tabla de amortización financiera, que realizaremos utilizando las ya conocidas funciones PAGO, PAGOINT y PAGOPRIN(Hipotecas:PAGO,PAGOPRIN y PAGOINT).
Podemos ver este detalle en el siguiente fichero:
Los asientos a realizar por la valoración inicial y por el pago de una cuota intermedia serían:
Nuestro contrato de Arrendamiento financiero (suponemos cumple todos los requisitos legales para definirlo así) tiene las siguientes condiciones:
Siguiendo la normativa contable debemos proceder al cálculo del Valor actual de la operación aplicando a los flujos de pago firmados el tipo de interés implicito en la operación. Y es aquí donde aplicamos alguna de las funciones financieras comentadas:
=VA(tasa;num periodos;pago) para pagos constantes
=VNA(tasa;rango pagos)
Una vez conocido el Valor actual de la operación de Arrendamiento financero, sólo deberemos seguir la Norma de Registro y Valoración 8ª del NPGC 2007, en este caso para determinar la valoración inicial compararemo este Valor actual con el Valor razonable del bien arrendado, activando e nuestr balance dicho bien por el menor de ambos valores. Posteriormente habrá que obtener una tabla de amortización financiera, que realizaremos utilizando las ya conocidas funciones PAGO, PAGOINT y PAGOPRIN(Hipotecas:PAGO,PAGOPRIN y PAGOINT).
Podemos ver este detalle en el siguiente fichero:
|
| VA-VNA Arrendamien... |
| Hosted by eSnips |
Los asientos a realizar por la valoración inicial y por el pago de una cuota intermedia serían:
martes, 16 de junio de 2009
Ejemplo SI: La función condicional-Introducción.
Seguro que todos hemos oido alguna vez de esta función SI, y quizá sepamos para qué puede servir. Dedicaremos unos segundos a explicar y analizar, en esta introducción, algo sobre el SI(prueba_lógica;valor_si_verdadero;valor_si_falso), la función condicional.
Vemos que realmente es una función muy sencilla en cuanto a argumentos, lo que hacemos con ésta es preguntar una prueba_lógica y le indicamos que ejecutar en el caso que se cumpla la prueba lógica o no. Entendemos que por definición cualquier slonos devuelve dos posibles resultados, o se cumple o no, o es cierto o es falso, i.e., prueba_lógica verdadera o prueba_lógica falsa; es decir, nuestros dos restantes argumentos.
Visto así es muy fácil pensar que sirve para poco, nada más lejos de la realidad. Esta función SI es, sin duda alguna, una de las funciones fundamentales de Excel, ya que nos permite infinidad de combinaciones y posibilidades, así como incluir otras funciones dentro de ella (concepto de anidar funciones). Como curiosidad mencionar que podremos 'anidar' siete funciones SI cada una de ellas como argumento de verdadero y/o falso de al anterior.
Hoy expondremos un ejemplo sencillo de SI (aunque sin duda en posteriores entradas profundizaremos e ésta).
Tenemos un listado de cuentas contables con sus respectivos saldos (un balance de comprobación) y dseamo conocer cuál es el saldo de las cuentas de tesorería (cuentas del subgrupo 57):
Pra resolver nuestro sencillo ejemplo aplicaremos en la columna contigua la siguiente función:
=SI(IZQUIERDA(A2;2)="57";C2;0)
en la que hemos anidado en el argumento principal, i.e., como prueba lógica, una función de texto que nos extrae de una celda dada el número de caracteres que le indiquemos, en nuestro caso 2 caracteres por la izquierda (ya que nos interesan aquellas cuentas que comiencen por 57), mencionar que estas funciones de texto nos devuelven texto, por lo que al aplicar posteriormente sobre este resultado parcial una nueva función, habrá que enerlo en cuenta.
Podemos ver en el documento adjunto explicado en los dos pasos como llegamos al resultado buscado.
Excel nos proporciona algunas funciones adicionales que podremos utilizar para analizar los datos basándose en una condición. Ya hemos visto alguna de éstas en nuestro blog, CONTAR.SI para contar el número de veces que aparece una cadena de texto o un número dentro de un rango de celdas (CONTAR.SI); o la función SUMAR.SI para calcular una suma basándose en una cadena de texto o un número dentro de un rango (SUMAR.SI).
Vemos que realmente es una función muy sencilla en cuanto a argumentos, lo que hacemos con ésta es preguntar una prueba_lógica y le indicamos que ejecutar en el caso que se cumpla la prueba lógica o no. Entendemos que por definición cualquier slonos devuelve dos posibles resultados, o se cumple o no, o es cierto o es falso, i.e., prueba_lógica verdadera o prueba_lógica falsa; es decir, nuestros dos restantes argumentos.
Visto así es muy fácil pensar que sirve para poco, nada más lejos de la realidad. Esta función SI es, sin duda alguna, una de las funciones fundamentales de Excel, ya que nos permite infinidad de combinaciones y posibilidades, así como incluir otras funciones dentro de ella (concepto de anidar funciones). Como curiosidad mencionar que podremos 'anidar' siete funciones SI cada una de ellas como argumento de verdadero y/o falso de al anterior.
Hoy expondremos un ejemplo sencillo de SI (aunque sin duda en posteriores entradas profundizaremos e ésta).
Tenemos un listado de cuentas contables con sus respectivos saldos (un balance de comprobación) y dseamo conocer cuál es el saldo de las cuentas de tesorería (cuentas del subgrupo 57):
Pra resolver nuestro sencillo ejemplo aplicaremos en la columna contigua la siguiente función:
=SI(IZQUIERDA(A2;2)="57";C2;0)
en la que hemos anidado en el argumento principal, i.e., como prueba lógica, una función de texto que nos extrae de una celda dada el número de caracteres que le indiquemos, en nuestro caso 2 caracteres por la izquierda (ya que nos interesan aquellas cuentas que comiencen por 57), mencionar que estas funciones de texto nos devuelven texto, por lo que al aplicar posteriormente sobre este resultado parcial una nueva función, habrá que enerlo en cuenta.
Podemos ver en el documento adjunto explicado en los dos pasos como llegamos al resultado buscado.
|
| Función SI: condic... |
| Hosted by eSnips |
Excel nos proporciona algunas funciones adicionales que podremos utilizar para analizar los datos basándose en una condición. Ya hemos visto alguna de éstas en nuestro blog, CONTAR.SI para contar el número de veces que aparece una cadena de texto o un número dentro de un rango de celdas (CONTAR.SI); o la función SUMAR.SI para calcular una suma basándose en una cadena de texto o un número dentro de un rango (SUMAR.SI).
lunes, 15 de junio de 2009
Función directa:SUMAR.SI.
Echaremos un vistazo a una de las funciones, a mi entender, fundamentales en el uso de Excel. Se trata de la función SUMAR.SI(rango;criterio;rango_suma) que suma las celdas del 'rango_suma' que coinciden con el argumento 'criterio', comparado con el 'rango evaluar', i.e, busca las coincidencias del criterio en el rango y acumula el saldo del rango_suma; es por tanto una suma condicionada a un criterio dado. Por supuesto hay otras funciones más potentes que esta (BDSUMA o la SUMA condicionada-matricial, las veremos en otras entradas), pero esta SUMAR.SI por su sencillez de uso es la que nos abrirá las puertas a resultados directos.
Como siempre lo veremos con un ejemplo. Tenemos un listado de compras realizados en distintas fechas sobre diferentes productos, del que disponemos información sobre las unidades compradas y de los precios unitarios, así de como los descuentos obtenidos, de cada compra. Trataremos de calcular cuál ha sido el precio final total de lo comprado por cada tipo de producto:
Determinamos cuál es el listado de productos, y sobre este aplicamos la función SUMAR.SI:
vemos como el resultado obtenido corresponde con la suma acumulada por cada producto del campo precio final.
Otra manera de calcular con esta función sería obtener un resultado sobre un único rango, es decir, nuestro rango a evaluar y el rango a sumar fueran el mismo. Si necesitaramos conocer cuál es el importe acumulado de todas las compras que individualmente superen la cifra de 1.000 eur, haríamos:
donde observamos que no es necesario indicar el argumento 'rango_suma' si lo que pretendemos sumar es el mismo 'rango a evaluar'. También comprobamos que el criterio a aplicar puede ser cualquier prueba lógica.
Como siempre lo veremos con un ejemplo. Tenemos un listado de compras realizados en distintas fechas sobre diferentes productos, del que disponemos información sobre las unidades compradas y de los precios unitarios, así de como los descuentos obtenidos, de cada compra. Trataremos de calcular cuál ha sido el precio final total de lo comprado por cada tipo de producto:
Determinamos cuál es el listado de productos, y sobre este aplicamos la función SUMAR.SI:
vemos como el resultado obtenido corresponde con la suma acumulada por cada producto del campo precio final.
Otra manera de calcular con esta función sería obtener un resultado sobre un único rango, es decir, nuestro rango a evaluar y el rango a sumar fueran el mismo. Si necesitaramos conocer cuál es el importe acumulado de todas las compras que individualmente superen la cifra de 1.000 eur, haríamos:
donde observamos que no es necesario indicar el argumento 'rango_suma' si lo que pretendemos sumar es el mismo 'rango a evaluar'. También comprobamos que el criterio a aplicar puede ser cualquier prueba lógica.
viernes, 12 de junio de 2009
Hipotecas: PAGO, PAGOPRIN y PAGOINT.
Veremos hoy las funciones financieras de Excel que debemos usar para replicar las condiciones de un préstamo hipotecario tradicional, que sigue el método francés de amortización, es decir, en las primeras cuotas se pagan más intereses que principal.
Para entender el ejemplo que veremos sólo hay que comprender cuáles son las condiciones de cualquier hipoteca, y estas son tres:
Recordemos la importancia, a la hora de emplear las funciones financieras de Excel, de aplicar correctamente los tipos de interés acordes a los periodos utilizados.
Supondremos en nuestro ejemplo que hemos firmado una hipoteca de 150.000 eur (media española) a pagar en 15 años (para facilitar la vista) a un tipo de interés del EURIBOR + diferencial del 0.50%(Tipo inicial 1,60% + 0,50% = 2,10%):
Para el cálculo de la cuota anual (ya que hemos tomado una tasa anual y un plazo en años) utilizamos la función PAGO(tasa;plazo;importe) según vemos en la tabla adjunta. Este punto es muy sencillo de obtener; para conocer en cada momento de la vida de la hipoteca que importe queda pendiente de amortizar, o cuál es la parte de interés o capital pagado en alguna cuota, construiremos el plan de amortización de la hipoteca:
Lógicamente hemos adecuado nuestro cuadro de amortización para la situaciones cambiantes del EURIBOR en cada periodo, así como de posibles amortizaciones anticipadas en momentos concretos de la vida de nuestra hipoteca.
Destacar las funciones
PAGOPRIN(tasa;periodo; plazo total;importe) y
PAGOINT(tasa;periodo; plazo total;importe).
En el fichero adjunto se puede ver el correcto uso para el cálculo de nuestra hipoteca adecuada en cada instante a las condiciones existentes.
Para entender el ejemplo que veremos sólo hay que comprender cuáles son las condiciones de cualquier hipoteca, y estas son tres:
- Importe concedido de principal.
- Tipo de interes (EURIBOR + diferencial pactado).
- Plazo en el que pagar el préstamo, i.e., el número de cuotas firmadas.
Recordemos la importancia, a la hora de emplear las funciones financieras de Excel, de aplicar correctamente los tipos de interés acordes a los periodos utilizados.
Supondremos en nuestro ejemplo que hemos firmado una hipoteca de 150.000 eur (media española) a pagar en 15 años (para facilitar la vista) a un tipo de interés del EURIBOR + diferencial del 0.50%(Tipo inicial 1,60% + 0,50% = 2,10%):
Para el cálculo de la cuota anual (ya que hemos tomado una tasa anual y un plazo en años) utilizamos la función PAGO(tasa;plazo;importe) según vemos en la tabla adjunta. Este punto es muy sencillo de obtener; para conocer en cada momento de la vida de la hipoteca que importe queda pendiente de amortizar, o cuál es la parte de interés o capital pagado en alguna cuota, construiremos el plan de amortización de la hipoteca:
Lógicamente hemos adecuado nuestro cuadro de amortización para la situaciones cambiantes del EURIBOR en cada periodo, así como de posibles amortizaciones anticipadas en momentos concretos de la vida de nuestra hipoteca.
Destacar las funciones
PAGOPRIN(tasa;periodo; plazo total;importe) y
PAGOINT(tasa;periodo; plazo total;importe).
En el fichero adjunto se puede ver el correcto uso para el cálculo de nuestra hipoteca adecuada en cada instante a las condiciones existentes.
|
| Hipotecas.xls |
| Hosted by eSnips |
jueves, 11 de junio de 2009
Ejemplo VA y VNA: Partidas a cobrar NPGC 2007.
Los instrumentos financieros de activo, con valoración a 'coste amortizado',con vencimiento superor a un año se mostrarán en nuestros Balances por su Valor actual; en este apartado del NPGC 2007 se incluyen las deudas comerciales. No olvidemos que hablamos de vencimientos mayores que el año, es decir, deudas comerciales a largo plazo.
La realidad de la nueva norma nos exigiría la actualización y ajuste de cada factura o grupo de éstas de igual vencimiento, pero con el uso de Excel evitaremos el engorroso trabajo contable, realizando un único asiento corrector de nuestras partidas a cobrar (el ejercicio es exactamente igual para las partidas a pagar - pasivos financieros).
Es muy importante tener claro los conceptos finanieros de actualizacón de valores, así como del tratamiento de los tipos de interes para cada periodo de estudio.
Supongamos, a modo de ejemplo, que tenemos a fecha de cierre 31/12/2009 el siguiente detalle de clientes con vencimiento superior al año:
Obtendremos de diferentes formas el valor actualizado detallado por cada cliente.
Repetimos la importancia de saber operar y entender correctamente los tipos de interes o tasas. Recordamos la equivalencia para los tipos de interés compuesto:
Vemos que la clave de este ejercicio es precisamete la conversión de la tasa al periodo concreto para cada vencimiento del listado de clientes. Mencionar que el NPGC 2007 nos permite elegir una tasa de actualización equivalente a una tasa de inversión sin riesgo (es decir, tipos de Deuda pública, por ejemplo), aunque otro valor más prudente a emplear podría ser el EURIBOR.
En el fichero adjunto se desarrollan las diferentes formas indicadas con anterioridad.
Vemos la diferencia entre el uso de VA y de VNA; y es que para la función VA actualizamos un importe, utilizando una tasa convertida para el periodo concreto e indicándole precisamente que corresponde a un sólo periodo; sin embargo para la función VNA sólo vinculamos a la celda del importe a actualizar la celda donde se indica la tasa ya convertida.
Por último, por curiosidad, el asiento de ajuste a realizar sería:
La realidad de la nueva norma nos exigiría la actualización y ajuste de cada factura o grupo de éstas de igual vencimiento, pero con el uso de Excel evitaremos el engorroso trabajo contable, realizando un único asiento corrector de nuestras partidas a cobrar (el ejercicio es exactamente igual para las partidas a pagar - pasivos financieros).
Es muy importante tener claro los conceptos finanieros de actualizacón de valores, así como del tratamiento de los tipos de interes para cada periodo de estudio.
Supongamos, a modo de ejemplo, que tenemos a fecha de cierre 31/12/2009 el siguiente detalle de clientes con vencimiento superior al año:
Obtendremos de diferentes formas el valor actualizado detallado por cada cliente.
- A partir de la fórmula financiera de actualización de flujos (flujo/(1 + ti)^plazo)
- A partir de la función excel VA(tasa,num periodos, pago)
- Empleando la función excel VNA(tasa, valor)
Repetimos la importancia de saber operar y entender correctamente los tipos de interes o tasas. Recordamos la equivalencia para los tipos de interés compuesto:
(1 + i)=(1 + im)^m
Vemos que la clave de este ejercicio es precisamete la conversión de la tasa al periodo concreto para cada vencimiento del listado de clientes. Mencionar que el NPGC 2007 nos permite elegir una tasa de actualización equivalente a una tasa de inversión sin riesgo (es decir, tipos de Deuda pública, por ejemplo), aunque otro valor más prudente a emplear podría ser el EURIBOR.
En el fichero adjunto se desarrollan las diferentes formas indicadas con anterioridad.
 |
| Partidas a cobrar ... |
| Hosted by eSnips |
Vemos la diferencia entre el uso de VA y de VNA; y es que para la función VA actualizamos un importe, utilizando una tasa convertida para el periodo concreto e indicándole precisamente que corresponde a un sólo periodo; sin embargo para la función VNA sólo vinculamos a la celda del importe a actualizar la celda donde se indica la tasa ya convertida.
Por último, por curiosidad, el asiento de ajuste a realizar sería:
miércoles, 10 de junio de 2009
Ejemplo TIR: Préstamos en el NPGC 2007
Los préstamos con vencimiento superior a un año, a valorar según 'Coste amortizado', se valorarán de acuerdo a su realidad económica-financiera por delante de su contenido jurídco-contractual; sabemos que al concederse un préstamo a nuestras sociedades lleva asociado unos gastos inicales (comisiones de apertura, gastos notariales,etc), por lo que la cantidad realmente dispuesta no coincidirá conel nominal o principal solicitado; principal sobre el que se calculan los intereses devengados.
Siguiendo el nuevo criterio del NPGC 2007 tenemos que reconocer como pasivo financiero el importe efectivamente percibido (principal menos gastos asociados) y calcular los intereses devengados y la deuda reconocida en todo instante en base a este criterio; por ello deberemos calcular una tasa de interés implícita(que logicamente no coincidirá con el tipo nominal firmado) que igualará los flujos, positivos y negativos, de tesorería a cero. Es decir, esta tasa de interés implícita será la TIR de nuestra inversión.
Y es en este punto donde entra nuestro Excel, ya que empleando la función TIR(rango;estimado) obtendremos la buscada 'tasa de interés' para conseguir los intereses dvengadosde nuestra operación de préstamo.
Supondremos un ejemplo con las siguientes condiciones contractuales:
Planteamos nuestra operación para calcular la TIR del préstamo:
Con lo que una vez aplicada la función TIR(D3:I3) sobre los flujos de caja obtendríamos un interés del 5,74%, calculando entonces el siguiente cuadro de amortización:
Con lo que sólo nos quedaría contabilizar la operación de préstamo. A modo de ejercicio práctico de contabilidad realizaremos un par de asientos.
El asiento de reconocimiento del préstamo y otro de un periodo intermedio sería:
En entradas futuras explicaremos las funciones PAGO, PAGOPRIN y PAGOINT empleadas en este ejemplo.Hipotecas
Siguiendo el nuevo criterio del NPGC 2007 tenemos que reconocer como pasivo financiero el importe efectivamente percibido (principal menos gastos asociados) y calcular los intereses devengados y la deuda reconocida en todo instante en base a este criterio; por ello deberemos calcular una tasa de interés implícita(que logicamente no coincidirá con el tipo nominal firmado) que igualará los flujos, positivos y negativos, de tesorería a cero. Es decir, esta tasa de interés implícita será la TIR de nuestra inversión.
Y es en este punto donde entra nuestro Excel, ya que empleando la función TIR(rango;estimado) obtendremos la buscada 'tasa de interés' para conseguir los intereses dvengadosde nuestra operación de préstamo.
Supondremos un ejemplo con las siguientes condiciones contractuales:
Planteamos nuestra operación para calcular la TIR del préstamo:
Con lo que una vez aplicada la función TIR(D3:I3) sobre los flujos de caja obtendríamos un interés del 5,74%, calculando entonces el siguiente cuadro de amortización:
Con lo que sólo nos quedaría contabilizar la operación de préstamo. A modo de ejercicio práctico de contabilidad realizaremos un par de asientos.
El asiento de reconocimiento del préstamo y otro de un periodo intermedio sería:
En entradas futuras explicaremos las funciones PAGO, PAGOPRIN y PAGOINT empleadas en este ejemplo.Hipotecas
martes, 9 de junio de 2009
Funciones de Búsqueda: BUSCARV y BUSCARH
Hablaremos hoy de algunas de las funciones básicas de búsqueda: BUSCARV y BUSCARH. ¿Por qué necesitamos conocer estas funciones?, la respuesta es obvia si has decidido buscar por la red información sobre esto; necesitas relacionar valores de un campo de una base de datos en excel con otro campo de otra base de datos. La relación entre campos es, entonces, el objetivo de estas funciones de búsqueda.
Propondremos algunos ejemplos.
Supongamos un listado con operaciones de compra de productos:
en los que tendremos que calcular el precio de compra y obtener el nombre del producto; datos que se encuentran disponibles en una tabla distinta (que se puede localizar en la misma hoja de cálculo o en otro Libro). Por comodidad de trabajo asignaremos un nombre a esta tabla de productos (:=Pdtos)
Queda por calcular, conociendo las unidades compradas de cada producto, el precio total de cada compra, además de conocer el nombre del producto; para ello empleando la función BUSCARV relacionaremos con la tabla de productos aprovechando la coincidencia de campos entre ambas tablas de información, i.e., en ambas tablas existe un campo que contiene elementos coincidentes.
La función de busqueda vertical BUSCARV(valor_buscado; matriz donde buscar; número columna a devolver;tipo coincidencia) trabaja buscando el valor indicado en el primer argumento (sólo podemos buscar UN elemento, nunca un rango) en la matriz determinada en el segundo (la función busca el elemento buscado en la primera columna de la matriz de busqueda), y una vez encontrada la coincidencia devuelve el elemento que corresponda de la columna indicada en el tercer argumento de la función; el último argumento nos dirige hacia una coincidencia exacta o no del valor buscado en la primera columna de busqueda.
La función de busqueda horizontal BUSCARH opera de la misma forma, con los mismos argumentos pero con una orientación horizontal; es decir busca por filas en lugar de por columnas. Por tanto busca el valor buscado en la primera fila de la matriz de busqueda, devolviendo la correspondencia encontrada del número de fila indicado en el tercer argumento.
Puedes descargarte el ejemplo si lo deseas.
Propondremos algunos ejemplos.
Supongamos un listado con operaciones de compra de productos:
en los que tendremos que calcular el precio de compra y obtener el nombre del producto; datos que se encuentran disponibles en una tabla distinta (que se puede localizar en la misma hoja de cálculo o en otro Libro). Por comodidad de trabajo asignaremos un nombre a esta tabla de productos (:=Pdtos)
Queda por calcular, conociendo las unidades compradas de cada producto, el precio total de cada compra, además de conocer el nombre del producto; para ello empleando la función BUSCARV relacionaremos con la tabla de productos aprovechando la coincidencia de campos entre ambas tablas de información, i.e., en ambas tablas existe un campo que contiene elementos coincidentes.
La función de busqueda vertical BUSCARV(valor_buscado; matriz donde buscar; número columna a devolver;tipo coincidencia) trabaja buscando el valor indicado en el primer argumento (sólo podemos buscar UN elemento, nunca un rango) en la matriz determinada en el segundo (la función busca el elemento buscado en la primera columna de la matriz de busqueda), y una vez encontrada la coincidencia devuelve el elemento que corresponda de la columna indicada en el tercer argumento de la función; el último argumento nos dirige hacia una coincidencia exacta o no del valor buscado en la primera columna de busqueda.
La función de busqueda horizontal BUSCARH opera de la misma forma, con los mismos argumentos pero con una orientación horizontal; es decir busca por filas en lugar de por columnas. Por tanto busca el valor buscado en la primera fila de la matriz de busqueda, devolviendo la correspondencia encontrada del número de fila indicado en el tercer argumento.
Puedes descargarte el ejemplo si lo deseas.
|
| Consulta5 BUSCARV-... |
| Hosted by eSnips |
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
Suscribirse a:
Comentarios (Atom)
