Calendario perpetuo con Excel

La realización de un calendario anual con Excel tiene la ventaja de que se puede actualizar fácilmente, convirtiéndolo en perpetuo, simplemente modificando  algún dato.

En la aplicación siguiente, introduciendo el nº de año en la hoja de cálculo, Excel ajusta automáticamente los días del año elegido con los de la semana que correspondan, dado como resultado un calendario anual.

Calendario Excel 2012

El calendario habitual, donde registramos datos en cada día del año para llevar un control o contabilidad de algún asunto importante, se vuelve más práctico si utilizamos una hoja de cálculo que nos permita, además de llevar un registro electrónico, realizar operaciones con los datos como pueden ser: totales por mes, promedios, etc.

Para ello se publica la hoja Excel siguiente, que a modo de sugerencia puede utilizarse para:

Revisión de préstamos a tipo de interés variable

En los préstamos hipotecarios y personales a tipo de interés variable se realiza una modificación anual o semestral (según esté pactado en el contrato), del tipo interés, que implica una variación de la cuota. La modificación se realiza sumando a un índice de referencia determinado el diferencial que tienen pactado entre la entidad de crédito y el prestatario. La nueva cuota se calcula en función del tipo de interés revisado, el n.º de pagos pendientes  y el saldo a la fecha de revisión.

Tasa Anual Equivalente (TAE)

Siguiendo la definición del Banco de España "La TAE es el indicador que, en forma de tanto por ciento anual, revela el coste o rendimiento efectivo de un producto financiero, ya que incluye el interés y los gastos y comisiones bancarias".

Cuando una operación tiene periodos de liquidación de interés inferiores al año, y bajo la hipótesis de que se reinvierte el importe de dichas liquidaciones al mismo tipo de interés y en régimen compuesto, se incrementará el coste o rendimiento (según se trate de un préstamo o un depósito, respectivamente), dando como resultado la TAE.

El calculo de la tasa anual equivalente se realiza mediante la fórmula TAE=(1+i/n)^n-1, donde i=tipo de interés nominal anual en tanto por uno, n=número de periodos de liquidación en el año.

Con Excel se puede calcular utilizando las funciones INT.EFECTIVO(int.nominal; num_per_año). Para incluir las comisiones y gastos hay que calcular previamente el int.nominal a través de la función

TASA(nper;pago;va;vf;tipo;estimar), en la que el va=(principal de la operación - comisiones y gastos).

La aplicación Excel siguiente calcula la TAE para operaciones de préstamo de cuota fija sistema francés y depósitos bancarios. Además se añade una hoja para calcular la equivalencia entre TIN (tipo de interés nominal) y TIE (tipo de interés efectivo), en donde sólo intervienen tipos, sin comisiones ni gastos.

Free Download: Cálculo de TAE

Introducción al análisis contable

Equilibrio, indicadores y ratios, es el título de una recopilación de aplicaciones publicadas en este Blog, realizada por el profesor de la Universidad de la Laguna: D. José Ignacio González Gómez.

La publicación está en formato PDF y constituye en sus 21 páginas una pequeña introducción al análisis contable. La contribución del Profesor aporta un valor añadido importante, por su  selección, agrupación y ordenación adecuada. Además la publica en su web www.jggomez.eu, junto con numerosas herramientas y utilizades empresariales, modelos Excel, sofware de contabilidad, enlaces de interés, notas doctrinales sobre contabilidad, proyectos de investigación,  etc. etc., muy recomentable para estudiantes de Economía y ADE.

Free Download: Equilibrio, indicadores y ratios

Estimación de la tendencia lineal en ingresos y gastos

Conocer la dirección que toman nuestros ingresos y gastos a lo largo de año es importante para obtener previsiones sobre  nuestra economía familiar. Esto se puede determinar mediante la estimación de la tendencia lineal de la serie (ingresos o gastos) objeto del análisis.

La tendencia es un movimiento regular no recurrente que presentan las series temporales según el análisis clásico (otros componentes son la estacionalidad, ciclos y variaciones irregulares). La estimación de la tendencia lineal se puede realizar por diversos procedimientos (MCO, medias móviles, primeras diferencias, etc). En esta aplicación, la tendencia, se estima a partir del gráfico Excel agregando la linea  de tendencia lineal con su función y el coeficiente de determinación (R cuadrado) que representa la bondad del ajuste. Para cambiar de serie, sobre el gráfico se selecciona el concepto de ingreso o gasto a representar y el periodo con datos de la mismo.

El objetivo de la  aplicación es el seguimiento de los conceptos más importantes de ingresos y gastos de nuestra economía personal y estimación de sus tendencias lineales, que al menos nos facilita una idea intuitiva de su movimiento en el tiempo como ayuda a nuestras previsiones.
.

Free download: Estimación de tendencia en ingresos y gastos

Análisis DAFO aplicando técnicas cuantitativas

La matriz DAFO, (Debilidades, Amenazas, Fortalezas, y Oportunidades), constituye un resumen de todo análisis estratégico, tanto interno como externo, en el que se presentan de forma conjunta todas las variables relevantes que muestran una visión global de la situación de la empresa.

Esta herramienta puede mejorarse si añadimos técnicas cuantitativas sobre los factores incluidos en la matriz, que nos facilita la obtención de la posición de la empresa en un modelo bidimensional que relaciona competencia (nivel de bondad en lo que hacemos)  y atractividad (demanda de nuestros productos y servicios). Para ello seguiremos los siguientes pasos:

1. Dividimos la matriz DAFO en dos submatrices, una relacionada con el ámbito interno FD (Fortalezas y Debilidades), y otra el externo AO (Amenazas y Oportunidades).
2. En cada matriz FD y AO, ponderamos (de 0,0 a 1,0) los factores en función de su contribución al exito de la empresa. La suma de los pesos en cada matriz será uno. Además  les asignamos una calificación:  en el análisis interno FD, según la fortaleza de la estrategia actual de la empresa respecto al factor.  (1-Mayor debilidad, 2-Menor debilidad, 3-Menor fuerza, 4-Mayor fuerza), y para el análisis externo AO, según el grado de respuesta de la estrategia actual al factor: (1-Baja, 2-Media, 3-Superior a la media, 4-Alta).
3. Multiplicamos cada ponderación por la calificación correspondiente  para obtener las calificaciones ponderadas de los factores.
4. En cada matriz (FD y AO), sumamos las calificaciones ponderadas para obtener el total ponderado. El resultado, estará entre un mínimo de 1 y un máximo de 4. El punto medio será 2.5
5. Representamos  en un gráfico de dos dimensiones la posición de la empresa. El eje de abscisas  representa la competitividad y tomará el valor del resultado total de FD  y las ordenadas  representan la atractividad y tomará el valor del total de AO.  

Ambos resultados determinan el punto donde está situada la empresa, en función del cual formularemos las siguientes estrategias:

• Valor bajo de ambas variables:  reestructuración, cambio estratégico y operativo,
• Competitividad alta y atractividad baja: nuevos productos, cambio de mercados, diversificación, etc.
• Competividad baja y atractividad alta: rumbo correcto pero debe mejorarse la organización, productividad, costes, diferenciación, etc.
• Ambas altas: expansión, desarrollar al máximo los productos y posición en el mercado

Dado que se trata de un proceso cuantitativo, este  puede desarrollarse facilmente utilizando una aplicación Excel con las siguientes hojas:

• Hoja 1: Situación interna
• Hoja 2: Situación externa
• Hoja 3: Gráfico de la posición de la empresa respecto de la competitividad y atractividad y estrategias a formular.

Free download: Análisis DAFO cuantitativo

Simulación de préstamos con cuota creciente en progresión aritmética

Frente a los métodos habituales de amortización de préstamos (francés, americano, alemán), en ocasiones puede ser interesante establecer un sistema de cuota creciente en progresión aritmética, donde cada cuota es igual a la anterior más un cantidad fija determinada. El objetivo es obtener un calendario de pagos más adecuado a la capacidad de pago del prestatario.

La simulación de la tabla de amortización se puede realizar con Excel calculando los intereses de cada periodo en función del tipo interés y el saldo al inicio del mismo, y la amortización como la diferencia entre la cuota o pago total, menos los intereses correspondientes. La  peculiaridad en este método es que  cada cuota depende de la anterior en la suma de una cantidad fija, por lo que habrá que buscar la primera cuota que haga cero el saldo final en el último periodo. Esto se consigue con la herramienta Excel: Buscar objetivo, mediante los siguientes pasos:

• Introducir los datos del préstamo: importe, tipo de interés, plazo
• Introducir en la celda H6, el importe en que debe incrementarse cada cuota a partir de la primera.
• Ejecutar Herramientas / Buscar Objetivo
• Definir la celda:  introducir la columna H, y la fila correspondiente al último periodo de amortización.
• Con el valor: introducir 0
• Para cambiar la celda: $G$10
• Aceptar

Si todo está correcto, Excel calculará el cuadro que cumple las condiciones fijadas. No obstante, puede ocurrir que si indicamos en la celda H6 un importe (de incremento) demasiado alto aparecerán cantidades negativas en la amortización y/o cuota. En este caso habrá que introducir en dicha celda una cantidad menor y calcular de nuevo.

De forma similar se pueden confeccionar aplicaciones para préstamos en: progresión geométrica, cuotas decrecientes, interés anticipado, etc.  Alguna serán objeto de próximas publicaciones.

Free download: Préstamos cuotas crecientes en progresión aritmética