image 6

Análisis Excel desviaciones entre los importes presupuestados y los que se han producido en la realidad nos va a ayudar a conocer aquellas partidas que no se ajustan a lo planificado.

A partir de ahí podemos tomar decisiones y poder rectificar para volver a lo presupuestado en su caso o fijar si es necesario cifras diferentes para la evolución presupuestaria.

Este es un ejemplo inicial y a partir de el avanzar con cada uno de los casos concretos.

Vamos a crear una hoja de Excel en donde reflejaremos los conceptos de ingresos y gastos, las cifras presupuestadas y los importes obtenidos en la realidad.

A partir de estos datos calcularemos las desviaciones en una nueva columna.

También usaremos formato condicional para ver la informacion de manera más visual.

Incorporaremos segmentadores tras dar el formato Tabla a los datos que nos ayudaran a filtrar los datos.

Para terminar con la creación de un gráfico Excel para representar las desviaciones del Análisis Excel desviaciones.

Con todo ello podremos tener información suficiente para poder tomar decisiones y en tiempo necesario para que puedan tener efecto.

Análisis Excel desviaciones: crear hoja Importes presupuestados y reales

Abrimos un libro Excel. (Prueba gratis Excel).

En la fila1 como encabezado escribimos empezando en A1, siguiendo B1, C1:

Concepto Importe Presupuestado Importe Real

Y los datos a introducir para el Análisis Excel desviaciones son los que vemos a continuación:

Compras 10.000,00 12.000,00
Sueldos y Salarios 3.000,00 3.000,00
Alquiler 1.200,00 1.200,00
Reparaciones 500,00 800,00
Servicios Profesionales 350,00 400,00
Transportes 200,00 150,00
Seguros 100,00 100,00
Suministros 400,00 800,00

Formato Tabla en Excel

A continuación vamos a dar formato Tabla a los datos, que no es un mero cambio estético sino que de esta forma estamos indicando a Excel que trate esta información como un conjunto y optimiza su uso y sus cálculos (esto explicado de forma sencilla).

Seleccionamos los datos vamos al menú Insertar y después clic en donde pone Tabla, tenemos que obtener un aspecto como el que vemos en la imagen siguiente:

image

Podemos cambiar el formato, haz clic en los datos y en la pestaña del menú, arriba a la derecha, hacer clic en Diseño de tabla y luego elegir un Estilos de tabla. Esto lo recomiendo para no hacer los Excel iguales «a los de todo el mundo» ya que hemos de tratar de dar nuestro toque personal y así diferenciarnos profesionalmente.

Vamos ahora a escribir un encabezado nuevo a la derecha de Importe Real, D1, y ponemos Desviaciones.

Y en D2 hacemos la operación resta como lo haces normalmente en Excel si bien al estar en formato Tabla la expresión que se genera es esta: =[@[Importe Presupuestado]]-[@[Importe Real]] Si te das cuenta en formato Tabla la referencia no la hace Excel a Celdas sino que lo hace a las Columnas.

Al pulsar Intro, todas las celdas de la columna Desviaciones se autocalculan automáticamente.

Segmentar en Excel

Ahora para mejorar el Análisis Excel desviaciones introduce el Segmentador para realizar filtros rápidamente, esto es desde, Diseño de tabla y después en Insertar segmentador de datos vamos a marcar en Concepto y a continuación en Aceptar.

Aparece el segmentador con los diferentes conceptos y al pulsar sobre uno u otro se muestra la informacion del seleccionado.

Puedes leer más sobre Segmentadores en el post de este blog siguiente: Excel filtrar datos en las tablas dinámicas.

Segmentación en Estilos de segmentación, cambia el color para personalizar tu trabajo.

Seguidamente introduciremos Formato Condicional a la columna de las desviaciones, primero Barra de color y luego probaremos con Conjunto de iconos.

Seleccionamos los datos de la columna D, a continuación en Inicio, vamos a Estilos y luego hacemos clic en Formato condicional según vemos en la imagen, se despliegan varias opciones:

Análisis Excel desviaciones

Elegimos Barras de datos y después podemos visualizar una vista previa a medida que nos situamos en las diferentes posibilidades.

Seguimos en Formato condicional y buscamos en Conjuntos de iconos aquel que nos guste.

El resultado final es similar al siguiente:

Añadir un gráfico Excel

Ahora vamos a añadir un gráfico seleccionando a la vez los conceptos y las importes de las Desviaciones, vamos a Insertar, después a Gráficos y elegimos el que mas nos guste en mi caso he elegido el denominado Cascada y este es el resultado:

image 3

Así ya tenemos nuestra hoja Análisis Excel desviaciones y podemos tenerla actualizada mes a mes para ver si estamos cumpliendo lo fijado en el presupuesto , analizar los principales puntos que no cumplen y tomar las decisiones necesarias para corregirlo.

image 5

Espero te haya sido de interés y estaré encantada de leer tus comentarios al pie de este post, sobre este tema y también sobre otros relacionados con la temática de esta web Excel, contabilidad y TIC.

Si quieres puedes seguirme a través de tus redes sociales favoritas y así estar al día de todas las novedades:

FACEBOOK – TWITTER – LINKEDIN

Comparte con tus redes

Por Yolanda Cuesta

Economista especializada en Contabilidad y formadora en Excel, Power BI y Power FX a todos los niveles. MVP Excel por Microsoft desde 2014 a 2024.

Deja una respuesta

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

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Aviso sobre Cookies en WordPress por Real Cookie Banner