Officefull.es

Excel, word, powerpoint, android

Conciliaciones en Excel abril 24, 2015

 

CONCILIACIONES EN EXCEL

Por Hector Miguel Orozco Diaz

Conciliaciones en Excel

Conciliar (cifras y documentos) es un término aplicado administrativamente a la revisión de dos documentos cuyos registros deben ser equilibrados y correspondientes entre sí, ejemplo: los cheques y movimientos relacionados en una cuenta contable versus un reporte de la cuenta de cheques emitido por el banco.

Para este caso de cotejos “uno a uno”, la conciliación es sencilla ya que los reportes generalmente se vinculan por el número de referencia al cheque (expedido por una entidad y presentado al cobro en el banco). Como resultado de esta revisión, señalar en uno de los documentos (o en ambos) permite identificar si quedan “partidas a conciliación”, es decir, los movimientos que se encuentran registrados (in)debidamente en uno de los documentos pero no en el otro, para corregir (donde y como corresponda) y equilibrar los documentos.

Una conciliación se puede volver +/- compleja, cuando el caso es de cotejos “uno a varios”, donde se requiere de una inequívoca identificación (p.ej.) de un importe abonado en el estado de cuenta del banco, a cuantos importes de cargo (y a cuales) se corresponde en el estado de cuenta de un cliente y similar en el caso de un pago a proveedor por dos o más facturas.

En este caso, para efectuar una búsqueda de importes (“sumandos”) para identificar aquellos cuya suma sea igual a la del importe pretendido (“objetivo”), es conveniente trasladar a algún modelo o tabla (de preferencia identificando antes -si es posible-) SOLAMENTE aquellos registros que sean viables para su cotejo, es decir, si en el estado de cuenta del cliente algunos registros han sido plenamente identificados como ya liquidados, NO TIENE SENTIDO su traslado.

También es importante trasladar a esa tabla o modelo algunos “elementos de análisis” adicionales (fechas y referencias a documentos e identificación del cliente, etc.) que permitan una identificación inequívoca de que la “colección de sumandos” que se encuentre puede ser confirmada como “la correcta”.

 

Conciliaciones “uno a uno”:

 

Si el cotejo “uno a uno” no es tan sencillo como conciliar cheques entre reportes contabilidad-banco, o donde la referencia (número de cheque) es un poco más engorrosa, se pueden utilizar Tablas Dinámicas haciendo un “arreglo” previo de los reportes a conciliar, para no ahondar mucho en este asunto (digamos) “sencillo”, puedes consultar un artículo (con archivo-ejemplo para su descarga) desde el blog de Alejandro Quiceno

 

Conciliaciones “uno a varios”:

 

Este caso empieza a ponerse interesante, cuando consideramos que la ecuación para saber el número de combinaciones posibles en las que hay que buscar cuales suman un objetivo es:  k = 2n (incluyendo la posibilidad de que con ninguna cifra o combinación se alcance el objetivo) siendo‘n’ el número de “sumandos” a considerar, motivo por el que se sugiere trasladar al modelo SOLO aquellos registros viables para su cotejo.

 

Un ejemplo de esta ecuación; si suponemos 3 cantidades a considerar para sumar un objetivo “c”, que nos indica que las combinaciones posibles son: 23=8 y que puedes visualizar (p.ej. en base 2) como sigue:

conciliación en excel

Si las cifras que pones a consideración del modelo fueran 95, la ecuación resultante (295) concluye que las combinaciones a evaluar son del orden de… (Imagina un ‘4’ y 28 ceros por delante).

Una vez encontradas ‘n’ combinaciones posibles de valores con los que se satisface la suma del importe buscado, será necesario tener en cuenta los otros “elementos de análisis” (fechas y referencias a documentos e identificación del cliente, etc.) para validar cuál de entre las combinaciones encontradas (si es que alguna) ES “la correcta”, puesto que aun si solo se encontrara UNA combinación entre todas las cifras consideradas, sin un análisis de los elementos de soporte adicionales, NADA puede asegurar que la combinación localizada ERA/ES aplicable para considerar “conciliados” tales registros.

“uno a varios” para lo que es conveniente tener en cuenta los lineamientos mencionados en los próximos párrafos.

región actual” (.CurrentRegion) en un modelo sobre el que se han trasladado los “registros viables” (previa depuración) en dos tablas distribuidas conforme a la siguiente muestra:

Distribucion Tabla

  1. La tabla izquierda contiene los registros del auxiliar contable y la derecha los del extracto del banco
  2. La primera fila contiene los títulos y el número de columnas puede ser distinto siempre y cuando…
    1. La primera columna de las tablas (A y J) contiene las fechas de los registros en orden ascendente
    2. La penúltima columna de las tablas (G y R) contiene los importes a considerar (sumandos u objetivos)
    3. La última columna de cada tabla (H y S) la utilizan los procedimientos para Auto-Filtrar los resultados
  3. Debe existir (al menos) una columna -y una fila al final- de separación entre las tablas y otros datos (I y T)
  4. Las demás columnas deben ser representativas y contener datos de los otros “elementos de análisis” (obviamente, los datos aleatorios que contiene el ejemplo son meramente “ilustrativos”)

La celda [U1] tiene incrustadas 3 imágenes cuyos objetivos son:

Objetivos

⇔ Paso 1 de 5 ⇔

Uno de cinco

 

Las opciones para los Auto-Filtros no requieren de mayores explicaciones. Al iniciar la macro de localización se muestran 5 diálogos/pasos (cancelables en cualquier momento, antes de seguir con el procedimiento):

Para seleccionar la celda con el importe “objetivo” que se busca concertar con la combinación de algunos importes de la tabla “contraria” a aquella donde se encuentra la celda seleccionada (si seleccionas de la tabla del banco, en el siguiente paso deberás seleccionar alguna celda de la tabla con el auxiliar contable -o viceversa-).

 

⇔ Paso 2 de 5 ⇔

Paso 2 de 5

En este paso, debes seleccionar alguna celda (cualquiera) que se encuentre dentro del rango de la “otra” tabla (si en el paso anterior seleccionaste de la tabla del extracto bancario, en este paso seleccionas de la tabla del auxiliar contable -o viceversa-).

⇔ Paso 3 de 5 ⇔

Paso 3 de 5

Ahora se necesita seleccionar alguna celda en un área “libre” de tu hoja de cálculo, en la que se depositará una lista con todas las combinaciones posibles encontradas por el procedimiento (si las hay). Recuerda mantener una columna/fila de separación entre las tablas (para no obstaculizar otras búsquedas para otros objetivos)

Puesto que las búsquedas pueden requerir que el procedimiento se ejecute durante un tiempo prolongado (recuerda la ecuación), en los siguientes dos pasos tienes opciones para restringir el proceso (en número de combinaciones a buscar o en tiempo de ejecución):

⇔ Paso 4 de 5 ⇔

Paso 4 de 5

En este paso tienes oportunidad de indicar si prefieres que el procedimiento se interrumpa luego de alcanzar un número específico de combinaciones posibles, introduciendo un valor mayor a 0 (cero) o, si prefieres que “vaya por todas”, indica un 0 (cero), puedes usar esta restricción en combinación con el siguiente paso…

⇔ Paso 5 de 5 ⇔

Paso 5 de 5

En este último paso puedes limitar el tiempo de ejecución para el proceso, indicando EN SEGUNDOS el tiempo máximo que estás dispuesto a “esperar”. Si no has trasladado a las tablas muchos/demasiados importes a escrutinio, indica un 0 (cero) y “siéntate a esperar” Silbando (considera tus opciones de combinación de estos dos últimos pasos).

  1. en el paso 1, la cifra-objetivo fue seleccionada de la celda [R18] (de la tabla del extracto bancario)
  2. en el paso 2 se eligió cualquier celda de la tabla del auxiliar contable (sumandos en la columna “G”)
  3. en el paso 3 fue seleccionada la celda [U3] para depositar los resultados
  4. al proceso se le dio un tiempo límite de 15 segundos
  5. encontró y listó 10 combinaciones posibles con los importes de la columna “G” (tabla auxiliar contable)

Como dato adicional, haciendo esta misma selección, pero dándole un tiempo más amplio (90 segundos) devolvió la nada despreciable cantidad de CIENTO VEINTE combinaciones posibles, por ello (insisto en que) ES MUY IMPORTANTE incluir en el modelo los demás “elementos de análisis” para evitar “dar por conciliados” (incluso “desaparecer”) registros ambiguos, confusos o (definitivamente) improcedentes.

En caso de que para los pasos 1 y 2 llegues a seleccionar celdas de la misma tabla para objetivo y sumandos, o que decidas cancelar (en cualquier momento) la ejecución del procedimiento obtendrás avisos como…

 

Mensajes

 

El archivo de ejemplo contiene (aleatoriamente inventados) 222 datos/registros en la tabla del auxiliar contable y 134 en la tabla del extracto bancario, por si quieres calcular (con la ecuación conocida) el número de combinaciones posibles en las que habría que evaluar cuales sumandos satisfacen un objetivo (?).

Para reducir tiempo de proceso (en la medida de lo posible), el procedimiento determina, con base en la fecha correspondiente de la cifra/celda seleccionada para el importe-objetivo del paso 1, un “rango de fechas” en donde buscar posibles combinaciones de la taba “contraria”, es decir, si el objetivo es del extracto bancario, el rango de fechas en la tabla auxiliar-contable será desde fecha(s) anterior(es) a la fecha del objetivo y hasta (inclusive) la fecha del objetivo, si por el contrario, el objetivo es de la tabla del auxiliar-

contable, el rango de fechas en la tabla del extracto bancario será a partir de la fecha del objetivo en adelante.

Lo anterior obedece a que no se espera que (p.ej.) una cuenta por cobrar sea abonada con anticipación a la fecha del documento de cobro (pedido, aviso de embarque, factura, etc.).

Terminado el procedimiento, en el rango donde se haya depositado una lista con las combinaciones posibles, podrás seleccionar (una a la vez) las celdas con las direcciones de cada colección de sumandos (ver imagen de resultados) y tales referencias de celda serán identificadas por formatos condicionales para que se pueda verificar (después de considerar los otros “elementos de análisis”) cuál de las combinaciones reportadas es “la correcta” (si alguna lo fuera).

auxiliar

Fórmula en columna “I”: [I2] =0+ESNUMERO(HALLAR(DIRECCION(FILA(G2),COLUMNA(G2),4)&"+",posibles))

registros

=CELDA (“address”, $G2)=buscado ó ver imagen con los formatos condicionales.

Después de haber seleccionado celdas del listado obtenido (una a la vez), NO CAMBIES la selección de la “celda activa”. Si quieres “navegar” por la hoja para ver aquellas a las que se ha aplicado formato condicional, usa las barras o la rueda de desplazamiento con el puntero (mouse) o aplica los Auto-Filtros.

Nota: la navegación por la hoja se verá ralentizada por efecto de las funciones utilizadas en nombres para su aplicación en las reglas de formato condicional (en los dos casos) para los registros en cada tabla:

objetivo

 Los formatos condicionales aplicados para identificar sumandos y objetivo

formatos condicionales

 

Notarás que cada celda de la lista con la colección de sumandos tiene una presentación/formato (casi) “listo” para que compruebes que la suma de las celdas referidas es igual al objetivo buscado, con un estilo de: “g2+g3+g5+g17+g52” o sea, {+} la dirección de las celdas con las que se ha compuesto la suma.

Puedes editar “la celda” y anteponer el signo ‘=’ para realizar la operación de comprobación, o puedes copiar la celda con la serie de referencias a otro lugar y hacer la edición allí, o puedes usar el método “Evaluate” para que VBA solicite a Excel la operación de suma necesaria, o puedes definir otro nombre con la macro-función del (viejo?) Excel v4: =EVALUAR(… (o =EVALUATE(… si tu Excel es en inglés), o… (se te ocurre algo más? pensando)

Este ensayo se acompaña con un archivo de Excel para que puedas (primero) analizar y comprobar lo aquí expuesto y estés en condiciones de adaptar (después) a las necesidades específicas de alguna situación real que pudieras encarar.

El algoritmo que se encarga de hacer correr los ciclos que sean necesarios para el armado de la colección de posibles sumandos (una función que se llama y ejecuta de manera recursiva), está basado en el desarrollo publicado en este artículo de Tushar Mehta (yo solo le he puesto “una buena mexicanizada” por lo que) aprovechando que TM ya ha puesto los comentarios pertinentes en su artículo (aunque en inglés), me he permitido omitir comentar “que hace” la mencionada función.

Un detalle que me parece digno de mención, es el hecho de que TM haya logrado “hacer tanto con tan poco” (sintetizado en no más de 25 líneas de código entre dos funciones y la declaración de las variablesglobales”).

Por el resto de los códigos utilizados en el libro de ejemplo, las líneas con macro-instrucciones (en su gran mayoría) son para los efectos de “maquillaje” (puro y duro), por lo que solo he agregado unos pocos comentarios, confiando en que no será difícil entender el propósito y accionar de las líneas no comentadas.

Descarga del archivo

Conciliando con excel.zip (31.9 kB)

 

Como siempre decimos…, haz buen uso de la información que se os proporciona 😉 !!

 

Categorías: Excel

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *