sábado, 6 de marzo de 2010

Actualización automática de una Tabla Dinámica

Descargar el fichero: tdauto.xlsm

Automatizar la actualización de una Tabla Dinámica es posible creando una pequeña macro, que se lanza cuando se produce un evento. Esto es lo que se denomina programación por eventos. En este caso el evento consiste en actualizar alguna cifra de un campo concreto de la base de datos. Si se modifica cualquier otro dato de la base de datos la tabla dinámica no se actualiza, aunque sería fácil programarlo para que se actualizara al cambiar cualquier dato.


Elegimos la columna D que corresponde en la base de datos al campo "VENTAS".  Cualquier modificación en las ventas de alguno de los comerciales automáticamente quedará reflejada en la Tabla Dinámica.

El código utilizado ha sido el siguiente.

Código:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
   ActiveSheet.PivotTables("Tabla dinámica1").PivotCache.Refresh
End If
End Sub


Si lo que deseas es que la Tabla Dinámica se actualice ante cualquier variación en el rango B4:E39, podrías indicárselo a Excel de esta forma.
Código:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B5:E39")) Is Nothing Then
   ActiveSheet.PivotTables("Tabla dinámica1").PivotCache.Refresh
End If
End Sub


Existe otra alternativa para cuando el Target es un rango. Se trata de indicar las filas y columnas que deseamos que se controlen.
Código:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column >= 2 And Target.Column <= 5 And _
Target.Row >= 5 And Target.Row <= 39 Then
   ActiveSheet.PivotTables("Tabla dinámica1").PivotCache.Refresh
End If
Cuando el Target es una única celda el condicional se puede escribir así:

If Target.Address="$A$1" Then

Al escribir la celda incluir los dólares y escribir en mayúsculas.

martes, 2 de marzo de 2010

Saldo Financiero y Equivalencia Financiera

Descargar el fichero: reserva.xlsx
Otro caso para practicar: reserva_matematica.xlsx

Reserva Matemática de la una operación financiera o Saldo Financiero. Vamos a establecer una operación financiera de préstamo recíproco entre dos empresas y vamos a estudiar cómo evoluciona el Saldo Financiero o Reserva Matemática, tanto por la derecha como por la izquierda. Estableceremos la Equivalencia Financiera entre Prestación y Contraprestación en t=0, en t=n, y en un instante intermedio, comprobando que en cualquier momento que se establezca, a nuestra elección, siempre se cumple que la Prestación es igual a la Contraprestación valoradas ambas en dicho instante.



Las celdas amarillas son datos. Nos indican las cuantías componentes de la Prestación (Empresa A) y de la Contraprestación (Empresa B). En toda operación financiera al primero que realiza una entrega de capital (en t=0) se le llama Prestamista, y a todas sus entregas se las denomina Prestación. Al otro se le llama Prestatario, y todas sus entregas constituyen la Contraprestación. El saldo puede cambiar de signo, esto es en lugar de estar a favor del prestamista, puede estar a favor del prestatario, pero aún cambiando de signo al que comenzó siendo prestamista le seguiremos llamando así, y el que comenzó siendo prestatario seguirá denominándose prestatario.

Existen dos tipos de Reserva Matemática o Saldo Financiero, que se denominan Reserva por la izquierda y Reserva por la derecha. La reserva por la izquierda en un instante dado es el saldo en ese punto antes de considerar la cuantía que vence en ese instante. La reserva por la derecha ya considera que se ha pagado la cuantía que vence en ese instante. Pensemos en un ejemplo bancario. Supongamos que en nuestra cuenta bancaria tenemos un saldo de 100 €, y que estamos esperando que nos ingresen hoy un importe de 2.000 €. Estamos impacientes, y a las 9 de la mañana consultamos el saldo, pero sigue siendo de 100 €. Volvemos a consultar el saldo a las 13 horas, y vemos que ya nos han realizado el ingreso, puesto que nuestro saldo es de 2.100 €. Por tanto, en el día de hoy tenemos dos saldos:

  • Reserva Matemática por la Izquierda, o Saldo Financiero antes de que venza la cuantía. El saldo es 100 €.
  • Reserva Matemática por la Derecha, o Saldo Financiero después de vencida la cuantía. El saldo es de 2.100 €.
La Reserva Matemática por la Izquierda en el instante t=5, y la Reserva Matemática por la Derecha en t=5 se representan con los siguientes símbolos, respectivamente.

En nuestro ejemplo calculamos la Reserva por la Izquierda (columna F) y la Reserva por la derecha (columna G). La Reserva por la Izquierda en t=0 siempre es cero, y si la operación está saldada (está cuadrada) la Reserva por la Derecha en t=n debería ser también cero. Esto es importante, ya que nos permite aplicar Solver, y pedirle que nos cuadre una operación de la que se desconoce un dato. Muchos problemas se solucionan así.

La fórmula de la celda G12 nos proporciona la reserva por la derecha, y se obtiene sumando a la reserva por la izquierda el aporte neto, o cuantías (con su signo) que vencen en ese instante. Su expresión es la siguiente, y se copiará la fórmula hacia abajo a toda su columna.

=+F12+E12

La fórmula de la celda F13 nos proporciona la reserva por la izquierda, y se obtiene capitalizando la reserva por la derecha del periodo anterior. Su expresión es la siguiente, y se copiará la fórmula hacia abajo a toda su columna.

=+G12*(1+$G$9)

Principio de Equivalencia Financiera

Debajo de la tabla principal hemos calculado las Equivalencias Financieras entre Prestación y Contraprestación en diferentes momentos (t=0, t=7 y t=3) por dos métodos. O bien capitalizando o descontando cuantía a cuantía, o bien usando la fórmula VNA. Trabajar con las cuantías individuales no es un método adecuado, ya que es totalmente manual. Es mucho mejor trabajar con la función VNA y luego capitalizar hasta el instante deseado.

Reserva Matemática

En las columnas J y K hemos calculado la Reserva Matemática en t=3 tanto por la derecha, como por la izquierda, utilizando los tres métodos clásicos:


  • Método Recurrente
  • Método Retrospectivo
  • Método Prospectivo
El método recurrente es el del cuadro, el que nos permite ir calculando una reserva en función de la anterior.
El método Retrospectivo tienen en cuenta las cuantías del pasado.
El método Prospectivo considera únicamente las cuantías del futuro.