Ediciones ENI Editorial | líder en informática
El cuadro de mando contable y financiero
Introducción
Empecemos hablando de la diferencia entre la visión del director de la empresa y la del director financiero. Se puede resumir en dos citas. Para el dirigente: "Es mejor tener más o menos razón que estar equivocado". (Warren Buffet); para el director financiero: "El diablo se esconde en los detalles". (proverbio suizo).
Quien fabrica el cuadro de mando está sujeto a la doble presión de la precisión de sus resultados y al aumento permanente de la complejidad y del volumen de datos para procesar. Esto se traduce en tablas de resumen de productos impresas, que a veces contienen imprecisiones (¿quién no ha imprimido alguna vez a mediodía la tabla dinámica de resumen del día anterior, olvidando las 10 novedades registradas por la mañana?). A menudo, esto provoca estrés a la persona que diseña la tabla, especialmente si está relacionado con los datos contables.
Esto es particularmente cierto para este primer capítulo de implementación real en la parte financiera de la empresa, pero también es cierto en todos los departamentos cuando se trata de la doble lectura "producción" vs. "control".
Intentaremos conciliar los dos puntos de vista, gracias a una organización minuciosa de nuestros libros de trabajo y a una conexión eficaz con el sistema existente.
Vamos a ver el lado contable...
Consolidar los datos financieros de varias entidades
Objetivo
Abra el libro de trabajo Cap. 5 parte A.xlsx.
El libro de trabajo contiene nueve hojas correspondientes a los datos de las tres entidades de enero a marzo. Además, contiene otras seis hojasinicio sints xxx T y fin sints xx T para cada una de las tres entidades que usaremos en los cálculos.
Entre cada una de estas hojas, se encuentran las hojas reservadas para la acumulación de valores de cada una de las estructuras teatrales. Estos valores se utilizarán para los cálculos de consolidación.
La pestaña Organización contiene una lista de las pestañas del libro de trabajo y contiene vínculos que permiten acceder rápidamente a cada hoja.
Queremos obtener el acumulado de cada entidad, como en el siguiente ejemplo:
También queremos consolidar los valores de las tres entidades:
Los datos
Las hojas, creadas mensualmente, se presentan de la siguiente manera:
Los beneficios operativos se colocan en la columna L de la fila 8 a la fila 13.
Los gastos de explotación que se utilizarán para calcular el IVA se encuentran en la columna G, entre las filas 8 y 19.
Los gastos de explotación para calcular el EBITDA se encuentran en la columna G, entre las filas 20 y 24.
Fórmulas "3D"
Para calcular el total del trimestre de la primera entidad, podríamos usar una fórmula como =’enero’C3+’febrero’C3+’marzo’C3 o, lo que viene a ser lo mismo, = SUMA(’Enero’C3;’ Febrero’C3;’ Marzo’C3).
Por lo tanto, la fórmula de la celda...
Simplificar la recuperación cíclica de datos contables
Volvamos al caso de la empresa de entretenimiento y veamos la recuperación de datos de la contabilidad.
Esta compañía gestiona 12 salas de espectáculos: 5 dedicadas al teatro y 7 al café-teatro. El personal se asigna de la siguiente manera: 5 directores para las primeras 5 salas, 5 técnicos para el resto, el electricista trabaja en 2 salas. Los honorarios de los artistas se cargan a las cuentas de la clase 604 porque la empresa es una "actriz" de su actividad (y no a la 611, porque no es un servicio).
Vamos a proceder a la implementación de una cuenta de resultados acumulada, de seis cuentas de resultados mensuales (será fácil añadir más) y vamos a establecer un desglose analítico que no existe en el sistema contable, utilizando Excel, sobre las dos familias de salas.
Abra el libro Cap 5 parte B.xlsx.
El libro consta de una hoja Acumulado cuyos valores se consolidan de las hojas entre inicio_acumulado y fin_acumulado. También contiene una primera cuenta de resultados mensual en la hoja enero.
Objetivo
Queremos poder añadir datos de la contabilidad y utilizarlos para tener un desglose actualizado tras seleccionar el balance mensual en la celda A1 de la hoja 01DESGL:
También queremos obtener una cuenta de resultados mensual duplicando el mes anterior, seleccionando el desglose pertinente en la casilla B2 del mes en cuestión (en este ejemplo, enero):
Estamos tratando de hacer que el acumulado sea móvil (usando el enfoque 3D) en función del mes elegido en la celda B2 de la hoja Acumulado:
Por último, queremos distinguir entre el desglose de las actividades de teatro y café-teatro y poder seleccionar una u otra actividad.
Todas las listas desplegables ya están en su lugar.
Los datos
El libro de trabajo tiene seis pestañas: los balances mensuales originales (aparte de los colores y filtros aplicados), que provienen de los libros principales de enero a junio, de 01BAL a 06BAL.
La pestaña 01DESGL se utiliza para establecer el procesamiento analítico. En esta hoja, los totales se muestran encima de los datos (fila 3):
La pestaña de configuración muestra el contenido de las listas desplegables que son útiles para el correcto funcionamiento del libro, así como la lista de cuentas:
Funciones utilizadas
BUSCARX()
BUSCARX(valor_buscado; VectorDeBúsqueda; VectorDevuelto;"")
La función se utiliza para buscar una cuenta entre las disponibles en la hoja de parámetros. También se utiliza para buscar la cuenta en el desglose mensual.
INDICE()
INDICE(matriz; num_lin; [num_col])
Esta función se utiliza para devolver la posición de línea (posiblemente columna) de un dato en una lista.
INDIRECTO()
INDIRECTO(ref_texto;[ a1])
Es la función más importante del método porque permite hacer referencia a datos en ubicaciones móviles o en pestañas futuras.
EQUIV()
EQUIV(valor_buscado tabla_busqueda;[ tipo])
Esta función se utiliza para buscar valores en listas que no están ordenadas alfabéticamente, como una lista de meses.
SIERROR()
SIERROR(valor; valr_si_error)
La función SIERROR() muestra un valor apropiado (en la secuencia (0) o nada ("")) en caso...
Recuperación cíclica de datos (importación)
Para esta tercera parte del cuadro de mando contable, nos vamos a centrar en la importación de datos de contabilidad y repasaremos algunas de las características estudiadas en el capítulo Elaboración del cuadro de mando.
Hay dos métodos principales para recuperar los datos. El primero consiste en pegar los datos recuperados de la exportación en una hoja fija del libro, a menudo llamada "importación". Pero entonces, ¿cómo se conservan los meses anteriores? El segundo consiste en pegar los datos en una nueva pestaña cuyo nombre se ha previsto, correspondiente al mes actual (por ejemplo: ExpCntaEnero o SegmtSalarios202201, etc.; todas las opciones son posibles pero algunas son más sencillas que otras).
Los datos que procesaremos se corresponden con el primer trimestre de un año. Las fórmulas están en su lugar, nos centraremos principalmente en el proceso de pegado de los datos recuperados.
Objetivo
Todavía en el negocio del entretenimiento, nos enfocamos ahora en una parte de la empresa que alquila y vende equipos de sonido e iluminación.
Queremos crear un modelo de análisis mensual que contabilice el número de facturas y el importe asociado, para cada uno de los vendedores por un lado y, por otro lado, el importe de facturas por vendedor y por tipo de venta.
Los formularios deben tener en cuenta la fecha especificada en B3 (o la fecha actual si no hay fecha especificada). Los datos, a través del nombre generado en D7, se extraen de la hoja del mes en cuestión (tan pronto como esté en su lugar).
Además, un vínculo de navegación en E7 debe proporcionar acceso directo a la hoja correspondiente.
El índice de contenidos debe permitir la navegación a las diferentes hojas de datos y a las pestañas de análisis. Debería estar listo a principios de año y los vínculos estarán activos tan pronto como las pestañas estén presentes. Los indicadores de color revelan la presencia (o ausencia) de pestañas.
Los datos
Las hojas de importación correspondientes a los tres primeros meses del año existen (de import cuenta enero a import cuenta marzo). Las pestañas están en rojo.
Cada mes, los datos recuperados se pegan en una sola hoja import cuenta, que luego se duplica sin ningún vínculo a los datos de origen.
Fórmulas
Abra el libro Cap 5, parte C.xlsx.
Los posibles nombres de pestañas, en nuestro ejemplo, son cuenta202201, cuentaenero y cuenta ener 22. La elección para la navegación, así como para el vínculo de datos, será el primer nombre posible cuenta202101. Esto hace que las fórmulas sean "más simples" y sobre todo, más escalables (con los años pero sin espacios innecesarios), esto no siempre es así en la realidad.
Fórmulas para comprobar la existencia de pestañas
Haga clic en la pestaña Índice de contenidos.
En E13, la fórmula es =@INDIRECTO(“cuenta"&$J$5&TEXTO(B13;"00")&"!A1")
Esta fórmula recupera el valor en A1 de la pestaña del mes en cuestión, si existe. De lo contrario, devuelve...