Reconciliación de débitos y créditos utilizando el complemento Solver

Hola fans de Excel

Hoy quiero hablar de un problema difícil y muy frecuente que se encuentran los contables o contadores y profesionales de las finanzas.

Digamos que tenemos 5 clientes y cada uno de ellos tienen que pagarnos algo de dinero. En lugar de pagar el total de una sola vez, nos lo pagan en 30 pequeñas transacciones. El importe total de estas transacciones coincide con la cantidad que han de pagar pero nosotros no sabemos qué cliente paga qué cantidad. ¿Y entonces cómo cuadramos las cuentas?

Si lo intentamos realizar de una forma manual, se puede tardar una eternidad - después de todo, hay más de 931 trillones de combinaciones (5 ^ 30).

Aquí es donde Solver puede ser útil ya que puede encontrar la solución óptima para este tipo de problemas sin molestarse demasiado.

Tutorial de cómo reconciliar los débitos y créditos utilizando Solver

1. Configurar nuestro modelo de Solver


En una hoja de Excel en blanco, introducimos todos los créditos de la lista a lo largo de una columna y los débitos totales en la parte superior en 5 columnas, como se muestra más a continuación:

Las celdas en blanco, Solver las llenará con 0 ó 1, lo cual indicará si el crédito en esa fila corresponde al débito en esa columna o no.

Esta zona (C6:G35 en mi libro) se conoce como área de celdas de variables en el modelo de Solver.

Hay 2 reglas que se deben seguir cuando se emparejan los débitos de créditos:

• Un crédito puede ser emparejado con un solo débito - es decir, la suma de cualquier fila de en C6:G35 puede ser máximo 1.

• La cantidad total reconciliada debe ser menor o igual al total de los créditos - es decir, la suma de cualquier columna en C6:G35 debe ser inferior a los valores en C5:G5 (débitos).

Para facilitar estas reglas, también conocidas como restricciones en el lenguaje de Solver, vamos a usar la columna H & y la fila 36.

Empezamos por escribir =SUMA(C6:G6) en la celda H6 y hacemos lo mismo hasta H35.

A continuación se escribe =SUMPRODUCTO($B$6:$B$35,C$6:C$35) en C36 y se arrastra hacia la derecha para rellenar la fórmula en resto de las columnas. (También te puede interesar Usos prácticos de la función SUMAPRODUCTO?)

Nuestro modelo de Solver quedaría así:

2. Configurar la celda objetivo


Para poder hacer su trabajo, SOLVER necesita una celda objetivo. Nuestro objetivo es maximizar la cantidad de cantidad a reconciliarse. Así, la celda objetivo, en mi caso J5, escribimos =SUMA(C6:G36). 

------------------------------------------------------------------------------------------------------------------------

RECUERDA QUE:

Tú puedes colaborar para seguir manteniendo este blog de una manera gratuita haciendo varios clics en los anuncios de la página. Como referencia, 2 clics por artículo. Muchas gracias por querer colaborar.

------------------------------------------------------------------------------------------------------------------------

3. Abrimos SOLVER

Selecciona la celda objetivo (en mi caso J5) y vamos a Datos> Solver. (si no tienes configurado Solver, ve el siguiente tutorial : Tutorial sobre Excel Solver)

Configure modelo solucionador de la siguiente manera:

• Establecer objetivo: celda J5.
• Células variables son C6:G35
• Restricciones 

1. El rango C6:G35 debe ser binario (0 o 1)
2. C36:G36 debe ser <= C5:G5
3. H6:H35 debe ser <= 1

• El método de resolución es Simplex LP (nuestro problema es lineal)

Cuando esté listo, hacemos clic en Resolver. Solver tardará unos minutos en encontrar la solución.



4. Examinamos el resultado

Una vez Solver encuentra una respuesta, se mostrará el cuadro de diálogo Resultados de Solver. Hacemos clic en Aceptar (también puede ver el informe de sensibilidad). Esto carga la solución de Solver al rango de celdas variables.

Y ahora analizamos los números..

¿Y si la solución de Solver no es la óptima?

De vez en cuando, Solver no encuentra solución óptima para problemas lineales con restricciones con números enteros. En tales casos, habrá que intentarlo de nuevo ajustando las restricciones y la precisión.

Te ha gustado el artículo?

No te olvides de mostrar tu gratitud hacia el autor (yo) mediante una de las 3 siguientes acciones:

1. Puedes invitarme a un café pinchando en la siguiente imagen.

2. Puedes hacer clic en los anuncios mostrados en la página

3. Puedes recomendar el artículo en Google haciendo clic en g+1 (justo después del artículo), también puedes compartirlo en Facebook, y/o dejar comentarios.

Descarga:

Para descargarse el archivo con el ejemplo ve al siguiente enlace:

Reconciliar Solver

Quieres aprender más en Excel o tienes algún problema que necesita solución?

Puedo ofrecer servicios de consultoría así como cursos personalizados online. Mírate el siguiente enlace si estás interesado/a.

Consultoría Excel / Cursos de Excel

Subscríbete y recibe todos las entradas por email

Para ello regístrate suministrando tu email en el sitio "Recibe los trucos en tu email" del menú de la derecha.

O sígueme por las redes sociales

Facebook, Twitter, Youtube y Google+