Balance de Excel

Esta publicación ilustra cómo crear un estado financiero, como un balance, con características y funciones integradas de Excel. Nuestro objetivo principal es crear un libro de trabajo que extraiga de manera eficiente los valores del balance de comprobación al balance general.

Descripción general

Para los fines de esta publicación, supongamos que tenemos una hoja de datos que contiene los datos de origen para el informe; a estos datos los llamaremos balance de comprobación. A continuación, asumiremos que necesitamos obtener los datos de la hoja de datos en la hoja de informe de forma automática. Para recuperar de manera eficiente los montos o saldos de cuentas en el informe (balance general), usaremos la función de suma condicional incorporada de Excel SUMIFS. Si no ha explorado esta increíble función, escribí una publicación de blog que analiza la función SUMIFS , así que no dude en consultarla si desea revisar la sintaxis de la función.

Echemos un vistazo a la hoja de datos que contiene los datos del informe, nuestra balanza de comprobación simplificada.

Saldo de prueba de Excel con saldos de cuentas

Ahora, echemos un vistazo al diseño de nuestro balance deseado.

Estructura del balance general de Excel

Un desafío que enfrentan los usuarios de Excel al crear un informe como un balance es el hecho de que las etiquetas del informe no coinciden con las etiquetas de datos. Por ejemplo, el nombre de la cuenta en el balance de comprobación es Cheque , pero el valor debe reflejarse en el balance como Efectivo . Necesitamos proporcionar a Excel estas traducciones de etiquetas para que podamos usar la función SUMIFS para recuperar los saldos de cuenta correctos para cada línea del informe.

En la captura de pantalla siguiente, actualicé el saldo de comprobación y asigne los nombres de las cuentas a las etiquetas de los estados financieros.

Balanza de comprobación con saldos de cuentas asignados a las líneas del estado financiero

También tenga en cuenta que, en algunos casos, muchas cuentas se asignan a una línea de informe; por ejemplo, AR y AR Allowance se asignan a la línea del balance de Cuentas por cobrar .

Una vez que hayamos definido el mapeo, podemos calcular fácilmente los valores de los estados financieros con la función SUMIFS. Por ejemplo, podríamos utilizar la siguiente fórmula en la celda C7 de nuestro balance:

=SUMARSIFS(TB!D:D,TB!C:C,B7)

Dónde:

• TB!D:D es el rango de suma, la columna de saldo de cuenta en la hoja de cálculo del balance de comprobación de TB
• TB!C:C es el rango de criterios, la columna de etiqueta del informe en la hoja de cálculo del balance de comprobación de TB
• B7 es nuestra etiqueta de informe

Podemos completar la fórmula a lo largo del informe para completar el balance. El informe completo se muestra a continuación.

Balance de Excel con fórmulas de importes

¡Y ahí lo tienes!

Nota: para los libros de uso recurrente, puede configurar el mapeo en una hoja de trabajo dedicada y luego usar funciones de búsqueda para recuperar las etiquetas del informe en el balance de comprobación o, por el contrario, usar SUMIFS para recuperar los valores del balance de comprobación en el mapa mismo.

consideraciones adicionales

Además de lograr el objetivo clave, que era agrupar y extraer automáticamente los saldos de las cuentas en el balance general, aquí hay algunos puntos más importantes a considerar al crear libros de informes de uso recurrente.

• Cree encabezados de informes dinámicos basados ​​en fórmulas
• Sangrar con el comando sangría, no con espacios iniciales ni en una nueva columna
• Utilice una hoja de verificación de errores para garantizar que todos los datos del balance de comprobación lleguen al balance general.
• Utilice una hoja de inicio aquí para almacenar la configuración global
• Utilice la función SUBTOTAL en lugar de SUMA para calcular los totales del balance
• Utilice filas delgadas entre la última fila de datos y la fila de fórmula

Notas

• Si no ha jugado antes con la función SUBTOTAL, inscríbase en el curso en línea GRATUITO Better Summing para que pueda conocer los detalles de esta maravillosa función.
• La función SUBTOTAL y las filas delgadas se trataron en el Volumen 1 de Excel University .
• Conceptos de publicaciones adicionales que incluyen tablas de mapeo, encabezados dinámicos, error, comenzar aquí y sangría se derivan del Volumen 2 de Excel University. Para obtener más información o detalles, no dude en obtener una copia del libro o inscribirse en el curso en línea .

Conclusión

No dudes en descargar el archivo que se utilizó para crear las capturas de pantalla anteriores, que incluye las fórmulas, y recuerda, ¡las reglas de Excel!

ExcelBalance