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