Tutorial GRATIS Cómo hacer con Excel una Cuenta Explotación Analítica automatizada desde cero

Tutorial GRATIS Cómo hacer con Excel una Cuenta Explotación Analítica automatizada desde cero.

Construye tu propia Cuenta de Explotación Analítica automatizada con Excel desde cero en pocas horas.

Tal vez hayas realizado algún curso de Excel y te ha ayudado a mejorar la técnica y a aplicarlo en tu trabajo del día a día.

Hoy en día internet está plagado de cursos para aprender Excel, además de vídeos, foros, tutoriales…. Muchos gratuitos y otros de pago.

Excel ofrece tantas posibilidades de uso como uno pueda imaginar, cientos de fórmulas, una inimaginable cantidad de código para las automatizaciones, todo tipo de configuraciones de tablas dinámicas….

Pero la realidad es que la mejor forma de aprender Excel es enfrentarse a un proyecto o trabajo de gran envergadura que ponga a prueba no solo la capacidad de aplicar técnicas (fórmulas, macros o código, tablas dinámicas…) sino también un diseño, una estructura y un formato profesional que permita ahorrar tiempo de elaboración y de mantenimiento.

El tutorial que dura 5,5 horas no es la clásica formación de Excel a través del cual aprenderás fórmulas, macros o tablas dinámicas sin una aplicación en un caso real, útil y de alto valor para tu empresa o tu carrera profesional.

Con mi sistema de formación aprenderás a construir tus propias herramientas profesionales con Excel a través de un caso práctico avanzado.

 

En este tutorial construirás desde cero una Cuenta de Explotación Analítica con un proceso de datos automatizado que permita el control de la información y la comparación anual de resultados y medibles a través de la selección de fechas.

Esta aplicación tiene muchas de las fórmulas, macros y tablas dinámicas que podrás utilizar en otro tipo de herramientas.

 

Vídeos Tutoriales Excel Gratis

Aquí te dejo unas imágenes de la herramienta que vas a construir desde cero. ¿Te animas?

 

Cómo elaborar Cuenta Explotación Analítica Excel desde cero

 

Cómo elaborar Cuenta Explotación Analítica Excel desde cero

 

Cómo elaborar Cuenta Explotación Analítica Excel desde cero

 

Cómo elaborar Cuenta Explotación Analítica Excel desde cero

¿Estás preparado/a para aprender esta técnica?

Más abajo encontrarás el tutorial completo GRATIS que te permitirá construir desde cero y que te ayudará a aprender Excel de forma profesional para aplicarlo de forma práctica y generar el alto valor que tu empresa necesita.

¿Te imaginas mostrarle a tu jefe o profesor esta herramienta que has aprendido a realizar por tu cuenta?

¿Necesitas demostrar en tu curriculum que eres capaz de construir herramientas con Excel de alto valor añadido para las empresas?

¿Qué aprenderás en este tutorial?

  • Utilizar formatos lógicos, claros y orientados al usuario final.
  • Crear una estructura optimizada para tu libro Excel.
  • Entender la relación entre tablas de la estructura optimizada.
  • Automatizar la elaboración del libro Excel para reducir los tiempos de desarrollo.
  • Procesar la información registrada de forma automática a través de macros para ahorrar tiempo y asegurar la fiabilidad de los informes.
  • Elaborar informes profesionales como los que utilizan habitualmente Controllers en multinacionales y grandes empresas.

Si haces siempre lo mismo, tus resultados también serán los mismos, prueba con una formación distinta y conseguirás mejorar tus habilidades.

 

Aquí te explico qué te vas a encontrar en cada capítulo del Tutorial gratuito “Cómo hacer con Excel una Cuenta Explotación Analítica automatizada desde cero”

 

Cuenta Explotación Analítica Excel Capítulo 1

Empezamos la elaboración de nuestro libro Excel, partimos de cero, así que es muy importante tener en cuenta el diseño que queremos realizar.

En primer lugar, hay que tener claro el objetivo, que en este caso es un informe comparativo anual a través de una estructura analítica flexible compuesta por cuentas analítica asociadas a cuentas contables, informe que podremos filtrar mediante la selección de meses cerrados.

Como cualquier sistema de Control de Gestión, tendremos un input o entrada de datos, una relación y procesamiento de datos y un output o salida de datos procesados. Y todo esto lo realizaremos con la mayor cantidad de automatizaciones posibles para ahorrar tiempo.

Es recomendable tener un mapa de procesos mental de la estructura a elaborar o también se puede definir un diagrama o mapa de procesos en una herramienta sencilla como Power Point que nos sirva como guía de lo que queremos construir.

Cada hoja tiene su función, algunas servirán para la configuración de datos, otras para la entrada de datos, otras actuarán como menús, otras para mostrar los informes, y también habrá hojas que no queremos que estén a la vista ya que servirán para procesar datos o para seleccionar datos que no queremos que estén a la vista.

Por esta razón la navegación a través de menús con botones y vínculos es importante para dirigir al usuario (ya sea nosotros mismos u otro usuario) a realizar las acciones y los pasos a seguir para el correcto funcionamiento de la herramienta.

A lo largo de mis años de experiencia he visto multitud de hojas de cálculo desorganizadas, sin ningún tipo de criterio que seguir, las cuales se convierten en sistemas complejos de manejar y difíciles de interpretar.

La mayoría de las veces el formato de las hojas de cálculo no sigue un criterio estandarizado, por lo que es difícil saber en qué celdas hay que registrar datos, qué celdas contienen fórmulas y qué celdas podemos editar o no.

En este tutorial verás que utilizo un formato estándar muy fácil de interpretar y que podrás trasladar a futuros proyectos o trabajos que tengas en tu empresa.

 

Cuenta Explotación Analítica Excel Capítulo 2

En este capítulo elaboramos una de las hojas más importantes del libro, es la hoja base para el registro de datos contables que tendrá relaciones con algunos datos de la tabla de la hoja “DATM” que hemos empezado a construir en el capítulo anterior y que seguimos trabajándola en este capítulo.

Digo que es una hoja base ya que será la que utilicemos para elaborar de forma automática a través de una macro las hojas de los 24 meses para el registro de datos contables (la macro la veremos en el siguiente capítulo)

En esta hoja base utilizaremos una fórmula muy importante que nos permitirá extraer el nombre de la hoja en una celda (la fórmula la tienes disponible en el material es este capítulo)

Esta fórmula es muy importante ya que nos permitirá relacionar datos de la hoja con otras hojas de forma automática.

Conforme avancemos en los capítulos ya verás la importancia que tiene.

Desde hace mucho tiempo utilizo la fórmula si(eserror…) para no mostrar valores erróneos de algunas fórmulas, sin embargo ahora me he acostumbrado a utilizar una fórmula más simple como es “si.error”, ambas son válidas y realizan la misma función, puedes utilizar la que prefieras.

En el vídeo de apoyo muestro ambas fórmulas.

 

Cuenta Explotación Analítica Excel Capítulo 3

En este capítulo veremos cómo utilizar una macro para elaborar hojas o crear hojas similares únicamente cambia el nombre de la hoja de forma automática.

La macro es tan simple que únicamente tendremos que indicar el número de hojas a crear, que en este caso serán 24.

Al igual que utilizamos una macro para crear hojas similares a la base de forma automática, también utilizaremos una macro para eliminarlas, es frecuente que tengamos que volver a modificar la hoja base debido a un error o que se nos haya olvidado incluir algo, o simplemente que queramos realizar una prueba del proceso. Sería un arduo trabajo tener que eliminar manualmente las 24 hojas, lo mismo que crearlas, por lo que la macro de eliminación de hojas también nos facilitará mucho el trabajo y nos ahorrará tiempo. Esto lo explico en el vídeo del capítulo.

También utilizaremos una macro para crear un índice automático de las hojas de registro de datos contables. Cuántas veces hemos perdido tiempo creando un índice y enlazando las hojas una a una. Un trabajo muy pesado cuando hay que enlazar muchas hojas como es este caso. Esta macro también nos ahorrará un tiempo muy valioso.

En este capítulo además veremos cómo utilizar la fórmula “INDIRECTO” para devolver a la hoja “DATM” los datos que queramos de las hojas de registros de datos contables.

Como verás el sistema que estamos construyendo se asemeja bastante a la estructura que puede tener un programa de gestión o un ERP.

 

Cuenta Explotación Analítica Excel Capítulo 4

En este capítulo realizamos la preparación para construir una matriz que será clave para la generación del informe de la Cuenta de Explotación Analítica.

La matriz que construiremos nos permitirá relacionar las cuentas contables que tenemos previamente definidas con las cuentas analíticas que también tendremos definidas, pero esta relación no será de 1 a 1, es decir, no será una relación de una cuenta analítica por una cuenta contable, sino que a través de porcentajes podremos asignar la relación de la cuenta contable a varias cuentas analíticas.

Por ejemplo, la cuenta contable 1 la podemos repartir un 50% a la cuenta analítica 3 y un 50% a la cuenta analítica 5.

Esto es habitual en el ámbito de Control de Gestión ya que muchas veces solemos agrupar apuntes contables en una misma cuenta, pero a nivel analítico nos interesa desglosarlo en varias cuentas analíticas.

Por ejemplo el caso de la cuenta contable de “Sueldos y Salarios” cuyo saldo nos puede interesar desglosarlo en “Costes directos”, “Costes de administración”, “Costes comerciales”….

Además incluiremos elementos de control para evitar que errores en la asignación de relaciones entre las cuentas contables y las cuentas analíticas.

Estos elementos de control es importante incluirlos no solo en esta herramienta, sino en cualquier trabajo que realicéis, la confianza en los datos es fundamental en cualquier hoja de trabajo de la empresa. Sin los elementos de control se pueden cometer errores no detectables y por lo tanto la información trabajada puede que no sea fiable y en consecuencia el trabajo realizado puede que no genere la confianza necesaria por parte de los receptores de la información.

 

Cuenta Explotación Analítica Excel Capítulo 5

En este capítulo vamos a utilizar una técnica que seguro que a muchos os puede resultar muy útil en los próximos trabajos que realicéis.

Vamos a utilizar una función poco conocida por los usuarios habituales de Excel (aunque tal vez ya la conozcas) como es una tabla dinámica de rangos de consolidación múltiple.

Esta función nos va a permitir poner en modo tabla dinámica la matriz que hemos construido previamente para la relación entre cuentas contables y cuentas analíticas.

Construiremos una hoja base al igual que hemos hecho para el registro de los datos contables, pero con la tabla de rangos de consolidación múltiple y la clonaremos para los 24 meses que necesitamos crearla.

Algunas de las fórmulas que utilizaremos para elaborar esta hoja base serán de las más complejas aplicadas a este proyecto.

Una vez construida la hoja base, utilizaremos la macro para clonarla 24 veces, es decir crearemos 24 hojas iguales a la base pero con nombre diferentes.

 

Cuenta Explotación Analítica Excel Capítulo 6

Una vez creadas de forma automática las 24 hojas de rango de consolidación múltiple con las fórmulas que he indicado en el capítulo anterior, ahora tendremos que agrupar la información de estas hojas en una única hoja, por supuesto este proceso no lo vamos a hacer de forma manual sino que utilizaremos una macro para hacerlo de forma automática.

Esta macro es muy útil en muchos aspectos ya que te va a permitir recopilar información de hojas similares, agrupándola y filtrándola.

De esta forma la información de varias hojas, ya sean 24 o 500 hojas, la podremos agrupar en una única hoja por lo que luego podremos tratarla fácilmente para construir los informes que queramos o darle cualquier otro tipo de utilidad.

También utilizaremos una macro para actualizar todas las tablas dinámicas con rangos de consolidación múltiple.

Aprenderemos una forma sencilla de crear botones para ejecutar macros.

Además, crearemos una macro que ejecute varias macros independientes.

 

Cuenta Explotación Analítica Excel Capítulo 7

En este capítulo empezaremos a dar forma al informe Cuenta de Explotación Analítica creando primero una función para poder seleccionar meses en ambos años de forma intuitiva y que además nos indique si hay datos disponibles en los meses.

También empezaremos a construir la estructura del informe de la Cuenta de Explotación Analítica, creando su armazón.

 

Cuenta Explotación Analítica Excel Capítulo 8

En este capítulo creamos la fórmula para mostrar los valores de forma dinámica en el informe de la Cuenta de Explotación Analítica, solo tendremos que crear una fórmula en la primera celda del informe para el primer año, para el resto de las celdas únicamente tendremos que copiar y pegar esa fórmula.

También crearemos el resto de fórmulas para los medibles importantes del informe, aunque estas fórmulas serán mucho más sencillas.

Y lo mismo que hemos realizado para el primer año, lo haremos para el segundo año.

 

Cuenta Explotación Analítica Excel Capítulo 9

En este capítulo terminaremos de calcular con fórmulas los medibles clave del informe Cuenta Explotación Analítica así como los formatos.

Cuando en un informe tenemos filas y columnas vacías, puede que nos interese ocultarlas cuando no hay datos y mostrarlas cuando sí que hay datos.

Así que utilizaremos unas macros que nos permitan ocultar de forma automática filas y columnas vacías y otras macros para mostrar todas las filas y columnas.

Para realizar esto primero necesitamos crear unas celdas con fórmulas para las filas y otras celdas con fórmulas para las columnas tal como muestro en el vídeo.

Y para facilitar el trabajo al usuario final (ya sea nosotros mismos u otro usuario) nunca viene mal realizar un ajuste del tamaño de las columnas de forma automática simplemente pulsando un botón, de esta forma también ahorraremos tiempo.

 

Cuenta Explotación Analítica Excel Capítulo 10

En este capítulo construimos los gráficos de los medibles clave de la Cuenta de Explotación Analítica.

 

Cuenta Explotación Analítica Excel Capítulo 11

Ya hemos realizado lo más importante de la herramienta para generar los informes de la Cuenta de Explotación Analítica, lo único que nos falta es dejarla perfecta y añadir algunas funciones para facilitar la usabilidad y ahorrar tiempos de trabajo de mantenimiento.

En este capítulo añadiremos una macro en cada una de las hojas de registros de saldos contables para borrar los datos automáticamente, pero incluyendo una contraseña de seguridad que evite la eliminación de los datos por error. Con esta macro podremos borrar los datos de las hojas de forma individual.

También crearemos unas hojas para controlar que las cuentas contables registradas en las hojas de saldos contables coincidan con las que hemos configurado previamente en la hoja “CTAS”, de esta forma si se ha añadido alguna cuenta nueva en el plan contable y se ha utilizado en algún apunte contable podremos identificarla y así añadirla al plan de cuentas de la herramienta para asociarla a las cuentas analíticas que le corresponda.

Para automatizar este proceso utilizaremos una macro que permite actualizar una tabla dinámica y copiar los datos en otra hoja.

Además añadiremos una macro que permita borrar todos los datos de las hojas de registros de saldos contables de forma automática.

Esto puede ser muy útil cuando cambiemos de ejercicio y necesitemos borrar los datos de todas las hojas de saldos, así evitamos ir borrando de forma individual y manual los datos de cada hoja.

Y ya por último nos queda añadir los botones correspondientes a todas las funciones asignándoles las macros correspondientes.

Como el archivo que habremos creado tendrá un peso algo considerable muestro un truco para comprimir el archivo y reducir notablemente su tamaño.

Con todo esto hemos construido una herramienta completamente automatizada, fácil e intuitiva de utilizar y con muy poco mantenimiento, una herramienta perfecta para el control, el análisis y la toma de decisiones de los resultados de forma analítica en la empresa.

Y todo esto utilizando técnicas Excel en un caso real y de aplicación práctica para generar un alto valor a la empresa.

Como te habrás dado cuenta muchas de las técnicas aplicadas en esta formación son fácilmente replicables a otras herramientas que puedas necesitar para tu empresa.

La metodología es similar, tan solo hay que adaptarla a lo que necesites construir.

 

Vídeo Tutorial gratuito de 5,5 horas Cómo hacer con Excel una Cuenta Explotación Analítica automatizada desde cero

 


⭐ ¿De verdad tu empresa no necesita un SISTEMA DE CÁLCULO Y CONTROL DE COSTES?

⭐ Las mayoría de empresas competitivas funcionan con un SISTEMA DE CONTROL PRESUPUESTARIO ¿Te apuntas al cambio?

⭐ ¿Seguro que no necesitas saber las PREVISIONES DE TESORERÍA de los próximos meses?

⭐ Mejora el CONTROL, la PLANIFICACIÓN, el ANÁLISIS, y los RESULTADOS de tu empresa con métodos CONTROLLER

Más información sobre Gestión, Control y Análisis de Presupuestos

 

Visita todos los vídeos publicados en mi Canal de YouTube 

Formas de Contacto

Deja un comentario

Este sitio web utiliza cookies para que usted tenga la mejor experiencia de usuario. Si continúa navegando está dando su consentimiento para la aceptación de las mencionadas cookies y la aceptación de nuestra política de cookies, pinche el enlace para mayor información.plugin cookies

ACEPTAR
Aviso de cookies