Calcular depreciación con Excel – Segunda nota

En la primer nota del tema vimos las distintas funciones que Excel pone a nuestra disposición para calcular la depreciación de un bien.
En esta nota veremos cómo construir una tabla, o cédula, de depreciación para todos los bienes de una empresa imaginaria. Esta tabla debe asistirnos en el cálculo de total del monto de depreciación que será reconocido como gasto en el cuadro de ganancias y pérdidas de la empresa del período.
Nuestro modelo, que calcula la depreciación de los bienes sobre una base mensual usando el método lineal, es el siguiente




La celda B1 contiene la fecha en base a la cual queremos calcular la depreciación. Aquí ponemos el último día del mes en cuestión. El modelo calcula el total de la depreciación a reconocer para el mes en la celda B2. Esta celda contiene la fórmula =SUMA(Depreciación_del_período)

Donde "Depreciación_del_período" es el nombre que contiene el rango dinámico

=DESREF('con auxiliares'!$F$5,0,0,CONTARA('con auxiliares'!$F:$F)-1,1)

Esto nos permite que el total tome en cuenta los bienes que vayamos agregando en la tabla.

Los campos Descripción, Fecha de adquisición, % de depreciación anual y valor residual son los datos de nuestro modelo. Hay que prestar atención que el porcentaje de depreciación es introducido en términos anuales, pero los cálculos serán realizados por mes.

Para realizar los cálculos podemos adoptar dos técnicas: con o sin tablas auxiliares. En esta nota mostraremos las dos soluciones, pero sin entrar a considerar cuál es el método más apropiado.

Dado que usamos el método lineal, calculamos la depreciación del período (mes) con la función SLN. Para calcular los períodos transcurridos desde la adquisición de los bienes, usaremos la función SIFECHA.
Aquí tenemos que tomar en cuenta que existe la posibilidad de que uno o más bienes hayan sido depreciados (amortizados) completamente. Para evaluar esta posibilidad creamos una tabla auxiliar



En la columna J (Total de meses) usamos la fórmula =1/D5*12. Esto nos da el total de meses de vida útil del bien.

En la columna K (Transcurridos) usamos la fórmula =SIFECHA(B5,$B$1,"m"), que calcula la cantidad de meses transcurridos desde la adquisición del bien, incluido el mes del cálculo.

La columna L (Restantes) nos da la diferencia entre J y K. Este resultado será nuestro indicador si el bien debe ser depreciado o no. En caso de ser negativo, el bien ha sido depreciado en su totalidad.

Una vez construida la tabla auxiliar, ponemos las fórmulas en para nuestros cálculos:

Depreciación del período (F): =SI(L5>0,SLN(C5,E5,1/D5*12),0)

Depreciación acumulada (G): =SI(L5>0,SIFECHA(B5,$B$1,"m")*F5,C5-E5)

Saldo (H): obviamente =C5-G5

Si queremos construir nuestro modelo sin tablas auxiliares, lo que hacemos es crear fórmulas que incluyas las auxiliares. En la hoja "sin auxiliares" del archivo con el ejemplo pueden ver la aplicación de estas fórmulas

Depreciación del período (F): =SI((1/D5*12-SIFECHA(B5,$B$1,"m"))>0,SLN(C5,E5,1/D5*12),0)

Depreciación acumulada (G): =SI((1/D5*12-SIFECHA(B5,$B$1,"m"))>0,SIFECHA(B5,$B$1,"m")*F5,C5-E5)



Un punto a tomar en cuenta es que hacemos los cálculos por meses enteros. Es decir, la antigüedad de un bien es la misma sin importar en que día del mes haya sido adquirido.
En algunos sistemas se considera el primer mes como completo sólo si el bien ha sido adquirido (o puesto en marcha) antes del día 15 del mes.
En la hoja "con auxiliares (2)" hemos modificado la fórmula de la columna K (Transcurridos) de manera que la cuenta de meses se haga de acuerdo a esta regla:

=SI(DIA(B5)<15,SIFECHA(B5,$B$1,"m"),SIFECHA(B5,$B$1,"m")-1)

Otro punto a tomar en cuenta es que si la fecha en B1 es anterior a la fecha de adquisición de un bien, las fórmulas que se refieren a el darán un resultado #NUM!. Esta situación podría darse si queremos calcular el total de la depreciación en un período del pasado.
En este modelo he optado por el uso de Formato condicional para volver "invisible" el contenido de las celdas que dan un resultado de error




También tenemos que modificar la fórmula en la celda B2, para obtener el total sin errores. Para esto usaremos la técnica mostrada en la nota sobre operaciones con rangos que contienen errores. En nuestro caso ponemos esta fórmula matricial

={SUMA(SI(ESERROR(Depreciación_del_período),0,Depreciación_del_período))}



Todo esto se puede ver en la hoja "con auxiliares (3)" del cuaderno con el ejemplo.



Technorati Tags: MS Excel

Calcular depreciación con Excel – Primera nota

Una de las consultas que recibo con cierta frecuencia es como construir una tabla de depreciación de bienes con Excel.
.
Empecemos por definir que depreciación, que en algunos países de habla castellana se llama también amortización. Depreciación es la pérdida de valor de un bien a lo largo del tiempo. Del punto de vista económico, la depreciación expresa la reducción de la vida útil económica del bien. Del punto de vista contable, la depreciación es la asignación del costo del bien al período contable en que ha sido usado.
De acuerdo a los países y a las prácticas contables, la depreciación es calculada de distinta manera. Excel nos provee con funciones para calcular la depreciación de acuerdo a los métodos más aceptados:

SLN: calcula la depreciación por método directo de un bien en un período dado. La sintaxis es

SLN(costo;valor_residual;vida)

SYD: calcula la depreciación por suma de dígitos de los años de un bien durante un período específico. La sintaxis es

SYD(costo;valor_residual;vida;período)

DB: calcula la depreciación de un bien durante un período específico usando el método de depreciación de saldo fijo. La sintaxis es

DB(costo;valor_residual;vida;período;mes)

DDB: calcula la depreciación de un bien en un período específico con el método de depreciación por doble disminución de saldo u otro método que se especifique. La sintaxis es

DDB(costo,valor_residual,vida,período,factor)


Con estas funciones podemos construir una tabla de depreciación (o amortización) de un bien. Demos un ejemplo para comparar los distintos métodos, que serán calculados con las funciones de Excel. Supongamos estos datos

En este ejemplo hemos supuesto un valor residual de 1, y la depreciación está calculada en términos de años. Naturalmente, la depreciación puede calcularse en términos de meses o cualquier otra unidad de tiempo y el valor residual puede ser cualquier número positivo.

Esta tabla nos muestra los resultados de los distintos métodos (he omitido el método DDB)



Al representar gráficamente los resultados, podemos apreciar claramente las diferencias entre los distintos métodos



El archivo con el ejemplo se puede descargar aquí.

En la próxima nota veremos cómo construir un pequeño modelo para manejar la depreciación de los bienes de una empresa imaginaria.


Technorati Tags: MS Excel

Gráfico interactivo según valor de celda

Una nueva entrada en mi blog sobre gráficos y presentación de datos, muestra cómo crear un gráfico interactivo cuyos datos dependen de la celda que hayamos seleccionado dentro de un determinado rango.
Los valores representado en el gráfico, dependen de qué celda en el rango A3:A12 (Departamentos) hayamos elegido



Este modelo usa nombres para determinar los rangos del gráfico y un evento para que el cambiose produzca automáticamente al seleccionar alguna de las celdas del rangi-

Technorati Tags: MS Excel

Crear gráficos tipo pirámide en Excel

En mi blog sobre gráficos y presentación de datos acabo de publicar una nota sobre cómo crear gráficos de tipo piramide en Excel.



El uso más corriente de estos gráficos es representar y comparar datos de dos poblaciones distintas.

Technorati Tags: MS Excel

Consolidar datos de hojas Excel con MS Query

Supongamos que tenemos una hoja en un cuaderno de Excel con los siguientes datos



En otra hoja tenemos estos datos



Nuestra tarea consiste en consolidar los datos de ambas hojas en una sola hoja, como esta



No podemos hacerlo con Datos—Consolidar o con Tablas Dinámicas. Una posibilidad es agregar el campo Cliente en la segunda hoja usando la función BUSCARV basándonos en el número de factura.
Este método se vuelve muy ineficiente cuando tenemos que manejar una gran cantidad de datos. No sólo que BUSCARV puede ser muy lenta cuando hacemos una búsqueda exacta sino que también tendríamos que estar actualizando las fórmulas cada vez que agregamos datos.

En esta nota mostraremos un método mucho más eficiente usando un programa adjunto de Excel, y poco conocido, el MS Query, sobre el cual ya hemos escrito en el pasado.

Supongamos que las hojas que hemos mostrada más arriba se encuentran en el cuaderno Datos.xls.

Empezamos por abrir un cuaderno en blanco y en este cuaderno abrimos el menú Datos-Obtener Datos externos-Nueva consulta de base de datos



En el diálogo que se abre elegimos la opción Excel Files



Ubicamos la carpeta donde guardamos el archivo (en nuestro caso Datos.xls) y elegimos el archivo



En el diálogo Elegir Columna veremos las dos hojas que contiene nuestro cuaderno



Para ver las tablas, que de hecho son las hojas del cuaderno, debemos asegurarnos que la opción Tablas del sistema esté seleccionada. Para hacer esto apretamos el botón Opciones



Nuestro objetivo es agregar los campos Fecha y Cliente a la hoja Ventas. Apretamos el signo + de Facturas para ver los campos, y elegimos Fecha y Cliente



Ahora señalamos la tabla Ventas y pasamos todos los campos apretando la flecha correspondiente



Al hacer esto veremos este mensaje



MsQuery nos pide que creemos un vínculo entre ambas tablas (unión). Haremos esto más adelante. Por ahora apretamos Aceptar y seguimos



Al apretar Aceptar se abre la interfaz del MS Query. En la parte superior vemos las tablas de datos que hemos importado al MS Query. En la zona de datos, vemos todos los datos de ambas tablas. Como podrán comprobar, en esta tabla hay 90 registros. Esto se debe a que no hemos creado ningún vínculo específico entre las dos tablas y por lo tanto se crean registros redundantes.
Existen distintos tipo de vínculos entre tablas. Quienes tengan conocimientos de SQL, podrán resolver el caso fácilmente. Para quien no conozca SQL, MS Query ofrece un asistente, que es lo que emplearemos en este ejemplo.
En el menú de MS Query elegimos Tablas-Uniones



En la ventana de diálogo del asistente nos aseguramos que en Izquierda y Derecha figure el campo Factura, en una ventanilla el campo de la tabla Facturas y en la otra él de Ventas. Elegimos la opción 3 de las opciones de unión (Left Outer Join, en SQL)



Apretamos Agregar y luego Cerrar. A continuación apretamos el botón de Ejecutar consulta ahora



El resultado es la tabla que estamos buscando. Nótese la forma gráfica en la que MS Query indica el tipo de unión que hemos creado entre las tablas



Todo lo que nos queda por hacer es transferir los datos al cuaderno de Excel que hemos abierto para el caso. Esto lo hacemos con el menú Archivo-Devolver datos a Excel



Excel activa la hoja de la cual hemos partido y abre un diálogo donde podemos elegir desde que celda importar los datos. Existen varias opciones que son visibles al apretar los botones Propiedades y Modificar consulta, y que invito a que investiguen por vuestra cuenta.



Al apretar Aceptar Excel importa los datos a la hoja.



Como ven, Excel también pone la barra de comandos Datos Externos. Esta barra nos permite renovar los datos, con el icono "!" y también editar la consulta. Una de las grandes ventajas de este método es que Excel guarda un vínculo con las tablas de datos lo que nos permite actualizar con comodidad y eficiencia la hoja de Excel cada vez que cambiemos o agreguemos datos en las tablas de origen.

Quien quiera practicar el ejercicio, puede descargar el archivo datos.xls aquí.

Technorati Tags: MS Excel

Formato condicional por bloques – segunda nota

Uno de lo usos de formato condicional es facilitar la lectura de tablas, señalando con un fondo de color determinadas filas o columnas.
En la primer nota sobre cómo facilitar la lectura de tablas con formato condicional vimos algunas técnicas para señalar filas en forma alternada (por ejemplo, poner un fondo gris a todas las filas impares, o por intervalos determinados).
En la primer nota sobre formato condicional por bloques, vimos como señalar la primer fila de un bloque de datos




En realidad lo que estaba buscando cuando empecé a escribir la nota era como poner un fondo en forma alternada a todo el bloque, es decir, esto



Partiendo de la premisa que nuestra tabla está siempre ordenada por sucursal, queremos poner un fondo alternado que nos ayude a visualizar cada bloque de datos. Queremos que esta funcionalidad sea dinámica, es decir, si agregamos o quitamos líneas a la tabla, el fondo del bloque se adaptará automáticamente.

El problema que debemos resolver es encontrar un criterio que se pueda evaluar como VERDADERO para el primer bloque, FALSO para el segundo, nuevamente VERDADERO para el tercero y así sucesivamente.

Para facilitar la explicación, mostraremos varias columnas auxiliares, pero la solución definitiva utilizará una única columna auxiliar.

Empezamos por crear una columna auxiliar donde evaluamos si el contenido de una celda es distinto del de la celda inmediata superior



El doble signo "-" en la fórmula fuerza a Excel a convertir el valor VERDADERO en 1 y el resultado FALSO en 0. Lo mismo puede hacer multiplicando la expresión por 1.

En la segunda columna auxiliar ponemos esta fórmula en la celda E2

=CONTAR.SI($D$2:D2;1)

y la copiamos a lo largo del rango hasta E16



Como pueden ver, todas las celdas relacionadas a la Sucursal 1, dan como resultado 1, las de Sucursal 2 dan 2 y así sucesivamente.
Así tenemos un número que identifica cada bloque. Pero lo más importante es que tenemos una serie donde se alternan los números pares y los impares. De esta manera podemos usa la función ES.IMPAR (ISODD en inglés) para generar una serie de valores VERDADERO y FALSO que se alternan con cada bloque de sucursales



Podemos prescindir de las columnas auxiliares E, F y G usando esta fórmula

=ISODD(CONTAR.SI($D$2:$D2;1))



Para mayor comodidad encapsulamos esta fórmula en un nombre, "fcc"



Ahora, seleccionamos la tabla y aplicamos formato condicional usando nuestra fórmula, que hemos puesto en el nombre "fcc" como criterio



Como ven, sólo hemos dejado la columna auxiliar D, que usamos en nuestra fórmula.



El archivo con el ejemplo se puede descargar aquí.

Technorati Tags: MS Excel

Formato Condicional por bloques.

En un comentario a la nota sobre Formato condicional en Excel – Facilitando la lectura de tablas, un lector proponía un método para señalar filas por bloques. En la nota mostrábamos como poner un fondo de color en una fila basándonos en algún criterio. Por ejemplo, poner un fondo gris en las filas impares, o en cada quinta o sexta fila, etc.
Pero supongamos que tenemos una tabla de ventas ordenada por sucursales y queremos señalar cada fila dónde cambia la sucursal. Es decir, nuestro criterio no sigue un patrón constante. Por ejemplo




Lo que queremos lograr es esto:



Cada vez que cambia el nombre de la sucursal, la fila recibe un fondo de color de manera que sea fácil visualizar dónde sucede el cambio.

Como ya hemos explicado, podemos usar formato condicional con la opción "Fórmula" a condición que esta sea una expresión lógica. Es decir, que de cómo resultado VERDADERO o FALSO. Si el resultado es VERDADERO, se aplica el formato.
Mi lector proponía una fórmula que incluía la función FRECUENCIA. Este función debe utilizarse en fórmulas matriciales y no me parece que pueda funcionar en Formato Condicional (si, mea culpa!, no me tomé el trabajo de comprobar la fórmula).
En esta nota propongo una método más sencillo, usando la función CONTAR.SI.
Ponemos esta fórmula =CONTAR.SI($A$2:A2;A2) en el rango D2:D16



Como pueden apreciar, cada vez que cambia el nombre de la sucursal el resultado es 1. Si convertimos nuestra fórmula a

=CONTAR.SI($A$2:A2;A2)=1

obtenemos VERDADERO cada vez que el nombre de la sucursal aparece por primera vez en nuestra lista.



Esta fórmula la usamos en Formato Condicional para lograr nuestro objetivo



Tenemos que asegurarnos que las referencias a las celdas sean las correctas

=CONTAR.SI($A$2:$A2;$A2)=1

El resultado es el esperado

Technorati Tags: MS Excel

Encontrar una fecha a partir de la semana del año

Excel pone a nuestra disposición todo un arsenal de funciones para extraer información a partir de fechas. Por ejemplo, si tomamos la fecha de hoy, 14 de abril del 2008 podemos extraer el número de día (que es más bien obvio), el día de la semana (lunes, martes, etc), el mes y al año



Quien quiera más información sobre funciones y cálculos con fechas y horas, puede apretar el enlace Fechas y Hora en el área de etiquetas del blog (en la columna a la izquierda).

Un lector me consulta cómo obtener los días de una semana si sólo sabemos el año y el número de semana.

Supongamos que tenemos el año en la celda B1 de la hoja y el número de semana en la celda B2



Empezamos por calcular el primer día del año con la fórmula =FECHA(B1;1;1)



Cada semana tiene necesariamente 7 días, así que podemos calcular el número de días transcurridos hasta el fin de la semana que aparece en la celda B2, con la fórmula =B2*7



Como pueden apreciar, el resultado es incorrecto. Hemos agregado el control Calendario, para poder controlar el resultado de nuestra fórmula.

Lo que queremos obtener es el primer lunes de la semana, para lo cual tendremos que hacer algunas correcciones a nuestra fórmula.

La corrección la calculamos con esta fórmula

=(1>=DIASEM(FECHA(B1;1;1))*7)+1-DIASEM(FECHA(B1;1;1))

Sumamos este resultado al de nuestra fórmula original y obtenemos el último día de la semana buscada



Ahora tenemos que llevar el resultado al primer día, para lo cual todo lo que tenemos que hacer es restar 6 del resultado obtenido

Nuestra fórmula final será

=FECHA(B1;1;1)+(B2*7)+(1>=DIASEM(FECHA(B1;1;1))*7)+1-DIASEM(FECHA(B1;1;1))-6



Esta fórmula siempre nos dará el primer lunes de la semana. Por ejemplo, si buscamos la primer semana del 2008, el primer día es, obviamente, el 01/01/2008. Pero el primer lunes de esa semana es el 31/12/2007



Si queremos extraer las fechas por semanas a partir del primer día del año, es decir, por intervalos de 7 días, tenemos que usar esta fórmula

=FECHA(B1;1;1)+(B2*7)-7




El archivo con el ejemplo se puede descargar aquí

Technorati Tags: MS Excel