martes, 17 de noviembre de 2009

Conciliación Bancaria

En esta entrada .expongo una de las soluciones que .comentaba en la entrada Optimizar es sacar el máximo partido a lo que se tiene. Consiste en la realización de una Conciliación Bancaria.

Para los que no estén habituados a este proceso, la conciliación bancaria consiste básicamente en comparar de forma periódica (por lo general mensualmente), la información que nos ofrece el extracto de nuestro banco con la que reflejan los cobros y pagos de nuestra contabilidad. El objetivo es asegurarnos que no hay ningún cobro o pago que se queda fuera de nuestra contabilidad y evitar duplicidades o errores en la misma.

La opción más elemental para hacerla consistiría en imprimirse mes a mes los extractos bancarios que nos proporciona la web de nuestro banco, y compararlos con la impresión del mayor de la cuenta de bancos, tickeando (a mano) importe por importe ambos listados.

Esta operación no es difícil pero es muy laboriosa y agotadora, lo que es del todo injustificable por el tiempo que requiere y por la posibilidad de error que genera no solo las operaciones manuales sino también el cansancio que provoca la tarea. Esperando no ofender a los que aún hacen así esta operación (que sorprendentemente según mi experiencia aún son muchos), es como si el resto de la contabilidad se registrase en la pared con pinturas.

La solución que propongo es muy elemental, y manifiestamente mejorable, pero que en la empresa en la que trabajo ya supuso una diferencia significativa respecto a la alternativa que empleaban. Con la base de los cálculos propuestos y unas pequeñas macros, podemos construir una aplicación que nos diga automáticamente los descuadres y omisiones a investigar, siendo la única operación manual necesaria la acción de copiar-pegar en nuestro Excel la información del banco y nuestra contabilidad, pero sería bastante más largo de explicar por lo que detallo la solución más simple.

Para empezar, podemos indicar que tanto de nuestro banco (la mayoría tienen la opción de descargar a Excel la información que muestra el extracto) como nuestra contabilidad (los ERP’s tienen la opción de descarga en Excel de la información) podemos transformarla en pocas y simples operaciones a algo parecido a lo que muestra la siguiente imagen.
.
Este sería un ejemplo de la información tratada de nuestra contabilidad.


Mientras que esta sería la información del banco una vez preparada.



En la información del banco nos quedaremos con la fecha valor, la descripción del movimiento (generalmente contiene el emisor o receptor del cobro/pago) y el importe, mientras que para la información de nuestra contabilidad nos quedaremos con la fecha de registro, el número de cobro o pago, la descripción (si contiene igualmente emisor o receptor del cobro/pago) y el importe. Como vemos en ambas imágenes, hemos ordenado ambas tablas en función de la columna importe de menor a mayor.

Podemos dejar mas columnas en función de la información disponible y la utilidad en cuanto a la investigación de descuadres, pero en principio, esta herramienta está pensada para indicarnos dónde están las diferencias a investigar, no para determinar las causas. Para eso tendremos que investigar en la contabilidad, los extractos bancarios detallados, hablando con los clientes o el banco,…

Una vez que tenemos la materia prima sobre la que vamos a comparar, utilizaremos solamente dos fórmulas que ya hemos visto en este blog; el condicional SI visto en Basics 5; Condicional SI y la fórmula COINCIDIR, visto en BASICS 9; COINCIDIR.

Primero determinaremos de la lista de importes contenidos en la tabla cuántos se repiten; esto es importante ya que como vimos en su momento, la fórmula coincidir nos da la posición en la que se encuentra el primer valor coincidente que encuentra. Debemos estar al tanto de si determinado valor se repite más de una vez, ya que en ese caso podría aparecer 3 veces en la tabla del banco y una sola en nuestra contabilidad y no nos percataríamos de que faltan dos en nuestra contabilidad.

Básicamente lo que hace este condicional es avisarnos de si el valor de la celda perteneciente a la línea en la que está la fórmula es igual al siguiente, con lo que nos avisará poniendo en la celda OJO, o es diferente con lo que dejará la celda en blanco.

El siguiente paso es averiguar si los valores de cada una de las tablas se encuentran presentes en la otra tabla, y viceversa. Es decir, comprobar que todos los apuntes que refleja el extracto del banco se encuentran presentes en nuestra contabilidad (para cerciorarnos de que no nos hemos dejado ninguno) y viceversa, que todos los valores que hay en nuestra contabilidad tienen su justificación en el extracto del banco (para evitar que por error hayamos incluido alguno inexistente). Para ello utilizamos la fórmula coincidir según se puede apreciar en las imágenes:


Análisis de los resultados:
Una vez hecho todo lo anterior, tenemos información suficiente como para verificar la conciliación bancaria con total seguridad. Para ello nos fijamos en:

Columna de los Repetidos:

Vemos cuantos importes se repiten y el número de veces que lo hacen, obviamente los mismos importes se deben repetir el mismo número de veces en ambas tablas. En el caso del ejemplo se puede ver como hay dos importes que se repiten 330,6 y 1698 pero observamos que el 330,6 aparece 3 veces en el banco mientras que en el mayor aparece solamente 2 veces. Esto significa que hemos dejado uno por contabilizar. Suponiendo que tanto la descripción del banco como de nuestros asientos contables proporcionen la información que tenemos en el ejemplo, es fácil de determinar que el cobro que falta por contabilizar es el cobro del 23/10 del CLIENTE 65455. En cuanto al segundo valor repetido, vemos que se repite dos veces tanto en el banco como en nuestra contabilidad por lo que es correcto.

Columna de los Presentes en la otra Tabla:
Como ya dijimos esta columna nos indica si cada uno de esos importes se encuentra presente en la otra tabla. Una vez tenida en cuenta la consideración sobre los repetidos, vemos que en nuestro ejemplo hay un cobro que refleja el extracto del banco que falta por contabilizar en nuestro sistema, el pago del cliente CLIENTE 66855 que se produjo el 17/08.

Esta es una solución básica para la conciliación, su bondad reside en que su construcción no requiere de conocimientos avanzados de Excel, simplemente las dos fórmulas indicadas que son realmente sencillas. Por supuesto, es posible construir soluciones similares con otras fórmulas también vistas como por ejemplo =BUSCARV(…;…;…;…). Incluso se pueden construir aplicaciones mucho más elegantes y automáticas utilizando macros, pero como solución primaria considero que es bastante eficaz y aceptable.


6 comentarios:

Jose Manuel dijo...

Muchas gracias.

El tiempo que he perdido conciliando bancos y lo fácil que era hacerlo rápido...

Muy buen blog. Las explicaciones son claras y tienes cosas muy útiles, como ésta de la conciliación bancaria. Me va a permitir ir más desahogado a final de mes...

Te sugeriría que hicieses otra entrada para calcular así de fácil el periodo medio de cobro. El sistema que utilizo no lo da y sólo tengo los mayores de cada cliente para calcularlo que es muy engorroso.

Voy a ver más cosas...

excel n' cia dijo...

Hola José Manuel,

Gracias a ti, es muy gratificante saber que aportas algo a alguien. Por eso agradezco mucho cuando lo comentáis.

Y también agradezco tu sugerencia sobre nuevo contenido. Me apunto una entrada para el cálculo del Periodo Medio de Cobro, o también DSO (Days Sales Outstanding).

Como el cálculo es bastante intuitivo, entiendo que la dificultad está en la disposición de los datos. Imagino que en el mayor tienes una línea con los datos relevantes para cada movimiento contable y el problema es cómo casar la facturación con el cobro, quitando el resto de situaciones como abonos, paso a efectos,...

No obstante, si me mandas un archivo (ficticio, por supuesto), con un ejemplo de cómo te da los datos tu sistema, podré afinar más para tu caso concreto.

Saludos.

Anónimo dijo...

Hola,

Hace unos meses descubrí tu entrada sobre conciliación bancaria en Excel y realmente me ha ayudado mucho. Siempre iba pillado en el cierre por éste y otros temas y nunca podía mirar cómo hacer las cosas de forma más eficiente. Con el tiempo (mucho) que ahorré en la conciliación he podido ir más desahogado en el cierre y descubrir otras mejoras en otros procesos. Fue el punto de inflexión que me ha permitido llegar a casa antes de las 10 durante el cierre...

Hace poco leí que los comentarios os beneficiaban a los blogueros así que me parecía que estaba en deuda con tu generosidad de compartir trucos como éste con todo el mundo.

Pues eso, que aunque un poco tarde, ahí tienes mi comentario de agradecimiento. Espero que continúes mostrando cómo hacer las cosas más eficientemente y ahorrar tiempo en el trabajo administrativo. He visto en tu perfil que eres Controller, en el área financiera hay muchos procesos para los que ofrecer mejoras como te comentó José Manuel.

Gracias otra vez y mucha suerte.

excel n' cia dijo...

Muchísimas gracias,

Lo digo siempre pero es cierto, me alegra mucho saber que os he aportado algo. Por eso agradezco mucho vuestros comentarios, más que por lo que beneficie al blog, (que no controlo mucho en qué medida lo hace).

Últimamente he podido dedicarle poco tiempo al blog, acabo de ser papá por primera vez y como comprenderéis me dedico en exclusiva a la peque. En cuanto disponga de un poco de tiempo haré la entrada del DSO y presentaré una nueva aplicación que dejaré disponible en Xcl NT Docs que estoy a punto de terminar pero que con mi nueva principal ocupación está pendiente del finishing touch.

Tengo muchas ideas pendientes, pero poco tiempo...

Saludos.

Anónimo dijo...

Muy buen aporte.

Muchas gracias por compartir cómo conciliar bancos con Excel. La forma manual es una tortura. Se un poco de Excel pero voy a seguir leyendo tu blog, porque veo que necesito más.

Saludos.

excel n' cia dijo...

Gracias a ti por tu comentario.
Saludos.