Confeccionar Controlar Presupuestos En Excel.pdf

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

Luis Muñiz

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel Aplicación en tablas dinámicas

Quedan rigurosamente prohibidas, sin la autorización escrita de los titulares del Copyright, bajo las sanciones establecidas por las leyes, la reproducción total o parcial de esta obra por cualquier medio o procedimiento, comprendidos la reprografía y el tratamiento informático y la distribución de ejemplares de ella mediante alquiler o préstamos públicos.

© Luis Muñiz, 2011 © Profit Editorial, 2011 (www.profiteditorial.com) Profit Editorial I., S.L., Barcelona, 2011 Diseño cubierta: XicArt Maquetación: www.eximpre.com ISBN: 978-84-15330-55-4 Depósito legal: B– Impreso por: Liberdúplex Impreso en España - Printed in Spain

Dedicatoria: Una vez más me enfrento a un nuevo reto, dar a conocer una nueva forma y manera de calcular los presupuestos y su seguimiento para distintas organizaciones y quisiera agradecer a todas aquellas personas que me han transmitido sus inquietudes e ideas para hacer este libro, clientes, otros profesionales, amigos, y alumnos todos ellos han contribuido a realizar un libro que ayudará a muchos responsables de pequeñas y medianas empresas a mejorar sus previsiones. Tampoco quiero olvidar a Maria, Marc y Anna que sin descanso me ayudan y me apoyan para que sea posible hacer mis libros con su paciencia y comprensión, y también quiero agradecer al editor y a su equipo la ayuda y apoyos recibidos sin duda, sin su esfuerzo y dedicación no hubiera sido posible publicar este libro. También quiero hacer una mención especial para José Lobete y Carlos Arratibel que gracias a su confianza y consejos me transmiten ilusión y ganas de superarme cada día, combustibles que son necesarios para que el mundo avance sin duda alguna.

Luis Muñiz González



[email protected]

Recuerde que la gestión está en sus manos: planifique sus ingresos y gastos, sus resultados y su tesorería y no olvide que sólo usted puede cambiar el futuro de su negocio: nadie lo hará por usted. Sólo usted puede tomar las mejores decisiones en cada momento con el presupuesto y la previsión de tesorería

Índice

Prólogo . . . . . . . . . . . . . . . . . . . . . . . . . . .

13

Introducción . . . . . . . . . . . . . . . . . . . . . . . .

17

Principales consejos para utilizar este libro . . . . . . .

23

Parte 1: Esquema y contenido del modelo . . . . . . . .

27

1. Descripción y contenido del modelo . . . . . . . . . .

29



1.1 Descripción del modelo . . . . . . . . . . . . . . .

30



1.2 Esquema del modelo . . . . . . . . . . . . . . . .

32



1.2.1 Contenido del modelo de forma global . . . .

32



1.2.2 Contenido del modelo de ingresos y gastos para realizar el presupuesto y su seguimiento . .

35

1.2.3 Contenido del modelo de cobros y pagos para realizar el presupuesto de tesorería . . . . .

48

1.2.4 Contenido del modelo del presupuesto de objetivos para realizar su seguimiento . . . . . .

57

2. Instalación y sistema de utilización del modelo . . . .

61



2.1 Instrucciones de instalación del modelo . . . . . . .

62



2.2 Forma de utilizar el modelo . . . . . . . . . . . . .

64



9

Parte 2: Confección del presupuesto de ingresos, gastos y de la previsión de tesorería . . . . . . . . . . .

67

3. Enunciado del caso práctico de una empresa de fabricación mediante cuentas y centros de coste: cálculo de ingresos y gastos previstos . . . . . . . . . .

69



3.1 Actividad y descripción de la empresa . . . . . . . .

70



3.2 Cálculo de los ingresos . . . . . . . . . . . . . . .

74



3.3 Cálculo de los gastos . . . . . . . . . . . . . . . .

77

4. Confección del presupuesto: ingresos, gastos presupuestados y reales . . . . . . . . . . . . . . . .

93

4.1 Confección de informes para los ingresos y gastos presupuestados . . . . . . . . . . . . . . .

94

4.2 Confección de informes para los ingresos y gastos reales . . . . . . . . . . . . . . . . . . . .

97



5. Análisis de desviaciones en ingresos, en gastos y en cuentas de explotación . . . . . . . . . . . . . . 101

5.1 Confección del análisis de desviaciones en ingresos y gastos . . . . . . . . . . . . . . . . . 102



5.2 Confección de las cuentas de explotación y análisis de desviaciones . . . . . . . . . . . . . . 106

6. Realización del presupuesto de tesorería . . . . . . . . 111

6.1 Enunciado del caso práctico: cobros y pagos previstos . 112



6.2 Confección del presupuesto de tesorería . . . . . . . 113



6.3 Análisis del presupuesto de tesorería . . . . . . . . . 117

7. Caso práctico para una empresa de transporte mediante cuentas contables . . . . . . . . . . . . . . . 123

7.1 Datos iniciales: ingresos y gastos presupuestados y reales . . . . . . . . . . . . . . . . . . . . . . . 124



7.2 Estados financieros y análisis de desviaciones . . . . . 126



7.3 Presupuesto de tesorería . . . . . . . . . . . . . . . 132

10

Índice

Parte 3: Confección de presupuestos por objetivos con diferentes tipos de variables . . . . . . . . . . . . . 139 8. Caso práctico: confeccionar la previsión de ingresos por ventas y por meses según objetivos por familias de productos . . . . . . . . . . . . . . . . . . . . . . 141 9. Caso práctico: confeccionar la previsión de ingresos por ventas y por meses según objetivos por vendedores . . . . . . . . . . . . . . . . . . . . . . . 147 Anexo 1: Herramientas de Excel para el modelo . . . . 153 Anexo 2: Esquema general del modelo . . . . . . . . . . 155

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

11

12

Índice

Prólogo

Antes de comentar los muchos usos y aplicaciones del libro, me gustaría resaltar algunos aspectos de la personalidad del autor que habitualmente no se recogen en un currículum y a veces son más propios de algunos test de personalidad. Para ello, me remontaré a la fecha de nuestro primer encuentro. En el año 2000 entré a formar parte de una «start up» que debía hacer historia en el sector de la distribución de automóviles tanto por el canal empleado como por el producto. Con esos alicientes y la firme constatación de que teníamos a todos los actores del sector (fabricantes y distribuidores) en contra de su desarrollo, el que suscribe, con muchas ganas de afrontar todos esos retos, decidió cambiar una cómoda posición de dirección general para formar parte de la escasa plantilla en los inicios de la compañía. Allí fue donde conocí a Luis, a quien le movieron idénticos retos. Cuando me lo presentaron estaba confeccionando unas estadísticas de ofertas y pedidos mediante unas tablas dinámicas, ¡cómo no! Los sistemas de gestión e información se estaban programando ad hoc para recoger todas las particularidades del negocio, y no se había llegado aún a la explotación de los datos. Luis, con su paciencia habitual e invir-

13

tiendo muchas horas para conocer con detalle la composición de los campos de las bases de datos, había confeccionado un sistema de información que utilizamos durante bastante tiempo y sirvió de base para el desarrollo final. ¡Y en Excel 2000!, con limitaciones hoy superadas por versiones posteriores… Allí, con el auxilio de Luis y la entrega de unas fotocopias de notas que él mismo había confeccionado sobre las funcionalidades y posibilidades de las tablas dinámicas, me introduje en un desconocido mundo donde pude comprobar su potencia y, gracias a sus buenos consejos, sigo utilizando cuando las aplicaciones que uso hoy en día no me facilitan el formato adecuado a mis necesidades. Debo añadir que en esa compañía donde conocí a Luis, invertimos muchas horas, compartimos dificultades, momentos muy duros y también felices por los éxitos obtenidos, ello nos permitió afianzar una sólida amistad y conocimiento entre todos, y aquí sería injusto no agradecer la colaboración de otros compañeros y colaboradores externos que nos ayudaron mucho en este proyecto y no menciono por riesgo de olvidar a alguno. Esa proximidad, unida a la cantidad de horas que requería el lanzamiento del proyecto, afianzó nuestro mutuo conocimiento como personas y me permitió ver en Luis otras muchas cualidades profesionales. A destacar de estas últimas, que es un luchador incansable, tiene la mente saturada de proyectos que a cualquiera le sería difícil priorizar y da la sensación de que los ejecuta simultáneamente. Es generoso, siempre está dispuesto a coger su moto para desplazarse, olvidándose de comer, y echar una mano a algún amigo despistado. No es extraño encontrarse con él a las 19:00 y pedir un café con leche y croissant como comida de mediodía. Su carácter expansivo le ayuda a dar conferencias y cursos y crear continuamente nuevas relaciones profesionales. Tampoco puedo dejar de aludir a los muchos correos electrónicos que recibo mencionando las actividades que promueve y seminarios que imparte, amén de otras novedades que afectan a diferentes sectores económicos y empresariales. Es constante y profundiza en todos los temas, ello unido a su conocimiento sobre herramientas informáticas, en especial los ERP, CRM y

14

Prólogo

por supuesto bases de datos y las tablas dinámicas de Excel, le permiten encontrar atajos y desarrollar los datos de salida hasta límites insospechados, incluso corrigiendo errores de programación. Por lo que he observado, bien haría Microsoft en disponer de su conocimiento para testar las versiones beta de las aplicaciones antes de sacarlas definitivamente al mercado. Respecto al libro Confeccionar y controlar los presupuestos y las previsiones de tesorería con Excel, hay que decir que es ameno, muy práctico y de fácil de lectura. Nos va guiando continuamente, partiendo de la introducción de datos básicos, sin necesidad ser un experto financiero para avanzar en el proceso de cálculo de ingresos y gastos, obteniendo el presupuesto y la previsión de tesorería, así como el posterior control de las desviaciones, facilitando la toma de decisiones más allá del corto plazo y anticipando posibles eventualidades en las variables de negocio, como las necesidades de tesorería ante un crecimiento de las ventas, por ejemplo. El autor, en la introducción, detalla de forma extensa los pormenores del libro, pero destacaría especialmente: El aplicativo Excel permite la importación, exportación y tratamiento por otras plataformas de datos adaptándolo a nuestras necesidades de forma muy rápida y flexible. Permite adecuarnos fácilmente a las necesidades concretas de cada negocio y sector. Nos indica cómo obtener diferentes niveles de detalle y de cuentas de ingresos y gastos tanto en importe como en porcentajes, optando a informes de tesorería ampliados o reducidos y centros de costes diferenciados. Es de una gran versatilidad al poder generar copias con múltiples composiciones para simular diferentes opciones en el negocio, compararlas para valorar su viabilidad financiera y la posibilidad de añadir gráficos que faciliten su comprensión. Por último, los casos prácticos incluidos nos permitirán ver tanto las posibilidades y potencia de la herramienta, como su uso en benchmarking, y ayudará a directivos y resto de usuarios no habituados a trabajar con aplicaciones sofisticadas, así como profesionales de las finanzas, donde el modelo puede ser fácilmente adaptado a complejas necesidades.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

15

En conclusión, Luis nos sorprende una vez más por su detalle, claridad de los textos, ejemplos empleados y su facilidad de comprensión, y ciertamente consolida su posición como escritor técnico, experto en tablas dinámicas y del que bajo mi humilde opinión pronostico un prolífico y prometedor futuro. Barcelona, julio 2011 Javier León Director General Bansabadell Renting Director Financiación Medio y Largo Plazo, Banc Sabadell Presidente del Consejo de Renting de la Asociación Española de Leasing y Renting

16

Prólogo

Introducción*

Es de todos conocida la necesidad de poder prever el futuro de una empresa en términos económico-financieros a corto plazo, es decir, poder, en definitiva, tener un presupuesto que nos ayude a calcular los ingresos y gastos futuros para obtener el resultado deseado en función de los objetivos empresariales. También es necesario prever la tesorería que nos permita afrontar los próximos doce meses sin ningún tipo de problema económico o, lo que es mejor, poder preverlo con la suficiente antelación. Por último, es necesario también que podamos controlar la evolución de los objetivos fijados como son las ventas previstas por cliente, por familia de productos o servicios y por vendedores, por ejemplo. Para conseguir todo lo anterior necesitamos algún tipo de herramienta que nos permita alcanzar los resultados posibles, para ello tenemos este libro: Confeccionar y controlar los presupuestos y las previsiones de tesorería con Excel. Este libro supone una profunda innovación y cambio en la forma de gestionar nuestras empresas, ya que nos permite: • Confeccionar un presupuesto de ingresos y gastos por meses para realizar el posterior análisis de desviaciones de una forma rápida y sencilla con Excel y tablas dinámicas.

Nota del Editor: Es importante que el lector tenga en cuenta que este manual reproduce capturas de pantalla del software adjunto y que el objetivo de estas reproducciones es explicar el funcionamiento del programa y no el detalle del contenido de las ilustraciones.

17

• Confeccionar el presupuesto de tesorería previsional que nos permita analizar la viabilidad financiera de la empresa en los próximos 12 meses, y tomar las decisiones oportunas. • Realizar diferentes análisis de las desviaciones entre los importes reales y los presupuestados y confeccionarlos con la herramienta de Excel más potente: las tablas dinámicas. • Fijar diferentes tipos de objetivos previstos y poder realizar la comparación con los datos reales según se van consiguiendo y el análisis de decisiones. En definitiva, el libro le permite mejorar la gestión de su organización, ya que: • Mejora en tiempo y forma la manera de realizar su presupuesto y así asegura la viabilidad futura de su organización. • Permite calcular el presupuesto de tesorería previsional, y esto es de vital importancia, ya que los ingresos y gastos e inversiones no nos aseguran la viabilidad financiera futura de una organización sino que nos hace falta, por descontado, conocer si tendremos suficiente tesorería para alcanzar los objetivos fijados. • Ayuda a confeccionar los diferentes análisis de desviaciones con numerosos informes y las cuentas de explotación adaptándolas a las necesidades de cada organización. • Conseguir poder poner los objetivos previstos a los diferentes responsables y hacer el seguimiento de una forma fácil y práctica. • Nos permite utilizar en todos los apartados el potencial de las tablas dinámicas en Excel como herramienta de reporting y sistema de información.

18

Introducción

Esquema del modelo

CONFECCIONAR EL PRESUPUESTO DE INGRESOS Y GASTOS CONFECCIÓN Y ANÁLISIS DEL PRESUPESTO

• CONFECCIÓN DE CUENTAS DE EXPLOTACIÓN OBTENER LOS INGRESOS Y GASTOS REALES

PRESUPUESTO DE TESORERÍA

INGRESOS Y GASTOS PREVISTOS

PREVISIONAL

COBROS Y PAGOS PENDIENTES

OBTENER LOS OBJETIVOS PREVISTOS FIJAR OBJETIVOS

• CONFECCIÓN DE INFORMES DE ANÁLISIS DE DESVIACIONES

Y OBTENER LOS RESULTADOS REALES

• CONFECCIÓN DE GRÁFICOS E INDICADORES

• PRESUPUESTOS DE TESORERÍA POR MESES • EVOLUCIÓN DE COBROS Y PAGOS POR MESES • ANÁLISIS DE LA VIABILIDAD FINANCIERA

• CONFECCIÓN DE INFORMES DE DESVIACIONES • CONFECCIÓN DE GRÁFICOS E INDICADORES

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

19

Contenido del libro PARTES

CAPÍTULOS

Parte 1: Esquema y contenido del modelo

1. Descripción y contenido del modelo

Parte 2: Confección del presupuesto de ingresos, gastos y de la previsión de tesorería

3. Enunciado del caso práctico empresa de fabricación mediante cuentas y centros de coste: cálculo de ingresos y gastos previstos.

2. Instalación y utilización del modelo

4. Confección del presupuesto: ingresos, gastos presupuestados y reales.

CONTENIDO

Esta parte contiene de una forma muy extensa el desarrollo del contenido del modelo y su funcionamiento, así como el proceso de instalación. Esta parte contiene dos casos prácticos que nos permiten conocer de una forma muy práctica todos los pasos necesarios para realizar los presupuestos de una empresa, su seguimiento, el control de las desviaciones, la tesorería prevista, todo ello sin restricciones, con herramientas de Excel sencillas y prácticas.

5. Análisis de desviaciones en ingresos, en gastos y en cuentas de explotación. 6. Realización del presupuesto de tesorería. 7. Caso práctico para una empresa de transporte mediante cuentas contables. Parte 3: Confección de presupuestos por objetivos con diferentes tipos de variables

20

8. Caso práctico: confeccionar la previsión de ingresos por ventas y por meses según objetivos por familias de productos. 9. Caso práctico: confeccionar la previsión de ingresos por ventas y por meses según objetivos por vendedores.

Estos dos casos prácticos nos permiten analizar si los objetivos de ventas se han cumplido y nos proporcionan las desviaciones en diferentes tipos de informes con gráficos e indicadores.

Introducción

Principales ventajas del modelo

 La facilidad en la introducción de los datos sigue un claro y práctico orden, guiando al usuario paso a paso.  El entorno en Excel nos permite adaptar los datos a cualquier otro formato e incluso utilizar los datos para darles la forma y contenido adaptado a nuestras necesidades.  La rapidez en obtener los resultados permite analizarlos y tomar rápidamente decisiones.  Permite desglosar los conceptos de ingresos y gastos para un mejor análisis e introducción de datos mediante cuentas contables y centros de coste.  Se pueden generar tantas versiones como se precise y compararlas.  Utilizar las tablas dinámicas como herramienta de análisis y obtención de datos.  Confeccionar un presupuesto de tesorería mensual teniendo en cuenta todas las variables, tanto los ingresos y gastos del presupuesto, las inversiones como otros cobros y pagos, así como el tipo de impuestos y los plazos de cobro o pago.  Posibilidad de poner indicadores y realizar gráficos interactivos de una forma rápida y fácil.

El modelo nos proporciona el análisis de los siguientes informes:

 Informes por cuentas a distintos niveles de las desviaciones de ingresos y gastos en importes y en porcentajes.  Cuentas de explotación por cuentas a distintos niveles de las desviaciones en importes y en porcentajes.  Informes por centros de coste y/o cuentas a distintos niveles de las desviaciones de ingresos y gastos en importes y en porcentajes.  Cuentas de explotación por centros de coste y/o cuentas a distintos niveles de las desviaciones en importes y en porcentajes.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

21

 Evolución mensual de ingresos y gastos presupuestados y reales.  Presupuesto de tesorería mensual ampliado.  Presupuesto de tesorería mensual resumido.  Informes de análisis de desviaciones entre los objetivos previstos y los reales alcanzados.  Es independiente su utilización del sistema informático o programa de gestión que tiene una empresa.

22

Introducción

Principales consejos para utilizar este libro

A continuación se van a describir para los diferentes apartados las tareas necesarias para que usted pueda optimizar su utilización, este apartado nos va a permitir:  Identificar las principales funcionalidades del modelo en Excel: así entenderemos cómo funcionan cada una de ellas y para qué sirven.  Conocer en que partes del libro están explicadas las principales funcionalidades  Conocer que se puede obtener en cada uno de los apartados según las funcionalidades utilizadas. Es necesario que usted siga de una forma estricta y metódica todos los pasos del modelo que este libro le van guiando para cada tipo de funcionalidad: presupuesto, previsión de tesorería y seguimiento de objetivos. Nota importante: Es importante que el lector tenga en cuenta que este manual reproduce capturas de pantalla del software adjunto y que el objetivo de estas reproducciones es explicar el funcionamiento del programa y no el detalle del contenido de las ilustraciones.

23

Primera funcionalidad: Confección y seguimiento del presupuesto SECCIÓN DEL LIBRO

SECCIÓN DEL LIBRO

1.2.2 Contenido del modelo de ingresos y gastos para realizar el presupuesto y su seguimiento

• Confeccionar el presupuesto por meses para las cuentas contables y por centros de coste/ingresos.

Plantilla a utilizar: 2_1_CALCULO_PR_CENTROS

• Obtener de sus sistemas contables los datos reales por meses para conseguir la plantilla de datos reales por importes según meses, cuentas contables y centros de coste/ ingresos.

Plantilla a utilizar: 2_2_PLANTILLA_CUENTAS_CENTROS

• Introducir los diferentes niveles de cuentas contables de uno a cuatro dígitos. • Copiar en una plantilla Excel el presupuesto calculado para realizar su seguimiento. • Introducir la plantilla de datos reales por meses calculada en el punto anterior en una hoja Excel para realizar el análisis de desviaciones. • Obtener mediante tablas dinámicas los diferentes análisis de importes de las desviaciones por cuentas contables o centros de coste/ingresos por meses o acumulado.

Plantilla a utilizar: 2_3_DATOS_PR_RE_CENTROS

• Obtener las cuentas de explotación presupuestadas y el análisis de desviaciones a través de las tablas dinámicas como orígenes de datos.

24

Plantilla a utilizar: 2_5_RESULTADOS_PR_RE_CENTROS

Principales consejos para utilizar este libro

Segunda funcionalidad: Confección de las previsiones de tesorería SECCIÓN DEL LIBRO

SECCIÓN DEL LIBRO

1.2.3

• Obtener los importes presupuestados por cuentas contables o centros de coste/ingresos.

Contenido del modelo de cobros y pagos para realizar el presupuesto de tesorería



Plantilla a utilizar: 2_3_DATOS_PR_RE_CENTROS

• Introducir los ingresos y gastos presupuestados del apartado anterior para obtener los cobros y pagos. • Calcular los diferentes cobros, pagos por otros conceptos y los pagos por inversiones. • Obtener el presupuesto de tesorería ampliado y resumido así como sus gráficos e indicadores correspondientes.

Plantilla a utilizar: 2_4_PR_TESORERIA_CENTROS

Tercera funcionalidad: Confección y seguimiento objetivos SECCIÓN DEL LIBRO 1.2.4 Contenido del modelo del presupuesto de objetivos para realizar su seguimiento

SECCIÓN DEL LIBRO • Introducir los objetivos previstos de las variables que se necesitan por ejemplo: previsión de ventas por clientes, vendedores, familias de artículos, etcétera. • Introducir los datos reales de los objetivos anteriores para realizar e análisis de las desviaciones. • Obtener las desviaciones ente los objetivos previstos y reales por periodos y acumulados.

Plantilla a utilizar: 3_1_FIJAR_OBJETIVOS

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

25

26

Principales consejos para utilizar este libro

Parte 1 Esquema y contenido del modelo

1 Descripción y contenido del modelo

Objetivos del capítulo

• Conocer el contenido del modelo sobre lo que nos proporciona: los presupuestos, las cuentas de explotación, los análisis de desviaciones, el presupuesto de tesorería previsional y poder fijar objetivos y analizar su seguimiento. • Permite conocer cómo estructurar modelo de ingresos y gastos para realizar el presupuesto y su seguimiento mediante el análisis de desviaciones con tablas dinámicas de Excel y la posibilidad de realizar diferentes cuentas de explotación y análisis utilizando cuentas contables y/o centros de costes e ingresos. • Ayuda a calcular los cobros y pagos para realizar el presupuesto de tesorería previsional permitiendo que se pueda introducir otro tipo de pagos y cobros como son inversiones, financiación, deudores y acreedores. • Contiene una herramienta que nos ayuda a fijar objetivos de ventas, de rentabilidad y cualquier otro tipo que nos permite hacer un seguimiento entre los objetivos previstos y los reales, mediante el análisis de desviaciones. 29

1.1 Descripción del modelo Este modelo de análisis está compuesto por las siguientes partes: • Una primera parte que nos permite confeccionar el presupuesto de una empresa y el posterior análisis de desviaciones tanto de los ingresos y gastos como de las cuentas de explotación que puede hacer el propio usuario. Esto nos permite una flexibilidad total, ya que se pueden realizar tantos tipos de informes como se requiera, sólo depende del tiempo y esfuerzo del usuario, aunque este libro le va a facilitar toda la ayuda necesaria para ello. Este modelo nos permite trabajar sólo con cuentas contables o con cuentas contables y centros de coste y/o ingresos. • La segunda parte nos permite, a partir de los datos anteriores del presupuesto de ingresos y gastos, calcular el presupuesto de tesorería teniendo en cuenta toda una serie de variables que iremos explicando a lo largo de este libro, pero permite introducir los datos iniciales y datos adicionales de forma manual, como pueden ser inversiones, préstamos e impuestos. • La tercera parte es muy importante, ya que son muchas las empresas que necesitan controlar los objetivos de ventas por clientes, vendedores, delegaciones, zonas comerciales o familias de productos/servicios, entonces con los diferentes excels que puede confeccionar el usuario, le permite cada mes ir controlando si los objetivos del presupuesto se están cumpliendo o no y tomar las medidas oportunas, también es muy importante que con este modelo, por ejemplo, se le puede enviar el análisis final al interesado para que pueda analizar por sí mismo cómo se van cumpliendo o no los diferentes objetivos.

30

Descripción y contenido del modelo

Esquema y contenido del modelo: 1. PRESUPUESTO DE INGRESOS Y GASTOS

2. PRESUPUESTOS DE TESORERÍA PREVISIONALES

• Cálculo de los ingresos y gastos previstos por mes y año. • Confección del esquema de ingresos y gastos reales por mes y año. • Confección de cuentas de explotación. • Seguimiento de desviaciones entre importes reales y previstos.

• Importar los datos de ingresos y gastos del presupuesto para calcular los cobros y pagos previstos. • Introducir los cobros y pagos previstos diferentes a ingresos y gastos. • Obtener el presupuesto de tesorería mensual previsional.

3. PRESUPUESTOS: FIJAR OBJETIVOS • Fijar objetivos de diferentes tipos por mes y año. • Realizar el seguimiento de objetivos, analizando las desviaciones entre los datos reales y previstos.

Objetivos del modelo

• Nos ayuda a confeccionar de una forma estándar y disciplinada los datos del presupuesto. • Nos aporta herramientas para calcular los datos reales por mes de una forma estándar sin importarnos el software de gestión que tengamos. • Permite, de forma mensual, obtener el control de los datos presupuestados y reales mediante el análisis de desviaciones de forma mensual y/o acumulada. • Ayuda a confeccionar los diferentes tipos de cuentas de explotación para realizar el análisis de desviaciones mensuales. • Nos permite combinar datos contables con datos de la contabilidad analítica según las necesidades de cada organización. • Al ser en Excel, el modelo puede ser adaptado a cualquier tipo de formato de informe utilizado. • Los datos se analizan y explotan en tablas dinámicas de Excel, lo cual permite una mejor prestación y utilización de los mismos. • Proporciona una previsión de tesorería mensual que el usuario puede adaptar a sus necesidades y verificar si el presupuesto de ingresos y gastos es coherente y razonable. • Permite, una vez calculados los objetivos de venta u otro tipo, realizar su seguimiento posterior mediante el análisis de desviaciones. Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

31

• Ayuda a confeccionar diferentes tipos de informes, gráficos e indicadores con Excel mediante la utilización de las tablas dinámicas. • Los casos prácticos y la teoría nos ayudan a entender el proceso de cálculo de presupuestos y su seguimiento.

1.2 Esquema del modelo 1.2.1 Contenido del modelo de forma global En este apartado se va a describir el contenido del modelo para que el usuario pueda conocer cómo funcionan los diferentes excels y la relación que tienen entre ellos en cada caso o no, dependiendo de los datos que estemos utilizando. Esquema de archivos de plantillas en Excel: Este apartado contiene las diferentes partes del modelo: los datos para calcular el presupuesto de ingresos y gastos, el presupuesto de tesorería y el modelo de control de objetivos.

Descripción del modelo • Modelos en Excel MODELOS

Descripción

1_MODELO_PRESUPUESTO_CUENTAS

Se incluye aquí el modelo para calcular el presupuesto, el control de desviaciones y la tesorería previsional utilizando sólo las cuentas contables.

2_MODELO_PRESUPUESTO_CENTROS

Se incluye aquí el modelo para calcular el presupuesto, el control de desviaciones y la tesorería previsional utilizando la combinación entre las cuentas contables y los centros de coste.

3_MODELO_FIJAR_OBJETIVOS

Se incluye aquí el modelo para calcular las desviaciones entre los objetivos previstos y reales.

32

Descripción y contenido del modelo

• Contenido de cada apartado

Modelo por cuentas contables 1_MODELO_PRESUPUESTO_CUENTAS

Esquema del modelo de presupuesto por cuentas contables y por meses: 1_1_CALCULO_PR_CUENTA

Plantilla para introducir los datos del presupuesto para un año y por meses sólo con cuentas contables.

1_2_PLANTILLA_CUENTAS

Plantilla que permite traspasar los datos por mes del sistema contable al Excel de control de presupuestos.

1_3_DATOS_PR_RE_CUENTA

Plantilla que permite analizar las desviaciones entre los datos reales y previstos.

1_4_PR_TESORERIA_CUENTAS

Plantilla que permite confeccionar la tesorería prevista en función del presupuesto.

1_5_RESULTADOS_PR_RE_CUENTAS

Plantilla que permite analizar los resultados reales y previstos utilizando los diferentes estados financieros o cuentas de explotación.

Modelo por cuentas contables y por centros de coste e ingresos: 2_MODELO_PRESUPUESTO_CENTROS

Esquema del modelo de presupuesto por cuentas contables y por meses: 1_1_CALCULO_PR_CENTROS

Plantilla para introducir los datos del presupuesto para un año y por meses combinando cuentas contables y centros.

1_2_PLANTILLA_CUENTAS_CENTROS

Plantilla que permite traspasar los datos por mes del sistema contable al Excel de control de presupuestos.

1_3_DATOS_PR_RE_CENTROS

Plantilla que permite analizar las desviaciones entre los datos reales y previstos.

1_4_PR_TESORERIA_CENTROS

Plantilla que permite confeccionar la tesorería prevista en función del presupuesto.

1_5_RESULTADOS_PR_RE_CENTROS

Plantilla que permite analizar los resultados reales y previstos utilizando los diferentes estados financieros o cuentas de explotación.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

33

Modelo para fijar objetivos: 3_MODELO_FIJAR_OBJETIVOS

Esta plantilla nos permite poder fijar objetivos de cifra de ventas, por ejemplo, por clientes, vendedores o familias de productos y servicios, y también cualquier otro tipo de objetivos monetarios o no. • Casos prácticos MODELOS

Descripción

CASO_PRACTICO_1_PRESUPUESTO_CENTROS

Se incluye un primer caso práctico en el que podemos analizar el presupuesto de ingresos y gastos, así como la previsión de tesorería para una empresa industrial combinando cuentas con centros de coste.

CASO_PRACTICO_2_PRESUPUESTO_CUENTAS

Se incluye un segundo caso práctico en el que podemos analizar el presupuesto de ingresos y gastos, así como la previsión de tesorería para una empresa de servicios utilizando las cuentas contables solamente.

CASO_PRACTICO_3_OBJETIVOS

Se incluye aquí el modelo para calcular las desviaciones entre los objetivos previstos y reales de ventas por familia de artículos.

Contenido de los casos prácticos: CASO_PRACTICO_1_ PRESUPUESTO_ CENTROS

CASO_PRACTICO_2_ PRESUPUESTO_ CUENTAS

CASO_PRACTICO_3_ OBJETIVOS

34

Descripción y contenido del modelo

• Contenido resumido de los diferentes excels

A continuación podemos observar el contenido del apartado desglosado del modelo propuesto, todos los excels son parecidos, la principal diferencia es si trabajamos con cuentas contables y/o con centros de coste. Por ejemplo, para la carpeta: Plantilla

Descripción

1_1_CALCULO_PR_CUENTA

El primer excel nos permite dejar los datos del presupuesto previamente calculados en un formato estandar y perfectamente explotable.

1_2_PLANTILLA_CUENTAS

Esta plantilla nos permite pasar los formatos de los balances de sumas y saldos utilizados a los formatos de nuestro modelo por cuentas y meses siendo los gastos negativos y los ingresos positivos.

1_3_DATOS_PR_RE_CUENTA

Esta plantilla de excel nos permite comparar los datos entre el presupuesto y los datos reales.

1_4_PR_TESORERIA_CUENTAS

El tercer excel nos permite confeccionar el presupuesto de tesoreria a partir de los datos anteriores, en los formatos que necesitemos y a nuestra medida.

1_5_RESULTADOS_PR_RE_CUENTAS

Y el útlitmo excel nos permite confeccionar los estados financieros en los formatos que necesitemos y a medida de nuestras necesidades.

Plantilla

Descripción Este Excel nos permite hacer previsiones entre objetivos de ventas por diferentes tipos: vendedores, familias, zonas geográficas y comparar con los datos reales posteriormente.

1.2.2 Contenido del modelo de ingresos y gastos para realizar el presupuesto y su seguimiento A continuación se va a describir el contenido y funcionamiento de cada uno de los archivos necesarios para calcular los ingresos y gastos por meses en un formato determinado. Partiremos del modelo que combina cuentas contables con centros de coste:

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

35

NOTA

ARCHIVOS DE EXCEL A DESCRIBIR

Los archivos descritos son iguales que el modelo que funciona sólo con cuentas contables, pero la diferencia es que se añade el centro de coste o ingreso como variable adicional.

• Descripción del archivo Excel: 2_1_CALCULO_PR_CENTROS

Este archivo sólo tiene dos etiquetas, y nos permite introducir los datos del presupuesto calculados previamente y verificar los datos.

‑ PRESUPUESTO_MES, esta etiqueta debe contener los siguientes datos: Nombre del campo

Explicación

Propiedades del campo

No_Cta

Número de la cuenta que se esté utilizando

Mejor que sea formato numérico, sin puntos siempre

Nombre_Cta

Nombre de la cuenta que se esté utilizando

Texto

C_Centro

Código del centro de coste que se esté utilizando

Texto o número depende de cada empresa. Puede ser alfanumérico

1

Importes en el mes correspondiente

Numérico

2

Importes en el mes correspondiente

Numérico

3

Importes en el mes correspondiente

Numérico

4

Importes en el mes correspondiente

Numérico

5

Importes en el mes correspondiente

Numérico

6

Importes en el mes correspondiente

Numérico

7

Importes en el mes correspondiente

Numérico

8

Importes en el mes correspondiente

Numérico

9

Importes en el mes correspondiente

Numérico

10

Importes en el mes correspondiente

Numérico

11

Importes en el mes correspondiente

Numérico

12

Importes en el mes correspondiente

Numérico

Suma total de los importes para los 12 meses

Numérico

Total

36

Descripción y contenido del modelo

Ejemplo:

Notas a tener en cuenta: • Esta hoja/etiqueta sólo debe ser rellenada con datos desde A2..O1001, es decir, soporta mil cuentas e importes para cada fila, y por doce meses. • Las cuentas de gastos pueden ser negativas o positivas, y las de ingreso también. Nosotros vamos a considerar que los ingresos son positivos y los gastos negativos, es la forma convencional de trabajar y es la que utilizaremos en el modelo y en los casos prácticos. Ejemplo:

− CONTROL, esta etiqueta nos permite verificar los datos utilizados y comprobar el resultado.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

37

• Descripción del archivo Excel: 2_2_PLANTILLA_CUENTAS_CENTROS

Este archivo tiene diferentes etiquetas, y nos permiten introducir los datos de nuestro sistema contable mediante un balance de sumas y saldos calculados para obtener los datos reales en el formato adecuado para ser comparados con los importes presupuestados. A continuación vamos a describir su contenido y funcionamiento. Es una plantilla de ayuda al usuario, pero lo puede obtener de cualquier otra forma.

Las pestañas 1 a 12 nos indican los meses que debemos utilizar para introducir los datos. Cada pestaña tiene este contenido:

Como puede observarse, la información se obtiene de la siguiente forma: ingresos en negativo y gastos en positivo, ya que se obtiene de los diferentes programas de contabilidad, entonces el modelo, de forma automática, hará los cambios correspondientes. La siguiente pestaña/etiqueta IMPORTES_REALES es una tabla dinámica, por tanto, cualquier actualización de datos introducidos se debe hacer mediante la actualización de la tabla dinámica tal como explicamos a continuación: Nos situamos sobre la tabla dinámica:

38

Descripción y contenido del modelo

Mediante el botón derecho pulsado sobre él hacemos actualizar y nos salen los nuevos datos:

Y nos añade otro importe en el mes 2, como se puede observar:

Por último, la pestaña/etiqueta de CONTROL nos permite, como siempre, analizar los resultados obtenidos de los registros contables que vamos introduciendo en esta plantilla de excel:

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

39

De forma resumida tenemos: 2_2_PLANTILLA_CUENTAS_CENTROS: nos permite poner los datos de nuestro sistema contable en un formato adecuado para ser analizado. El proceso es el siguiente: Actualizar la tabla dinámica y obtener los datos sumados por meses en la pestaña: IMPORTES_ REALES



Introducir los datos de los balances de sumas y saldos por mes, pestañas 1 a 12



Revisar que los resultados son los adecuados según nuestros registros contables: CONTROL

Muy importante: esta plantilla nos permite actualizar los datos ante cambios en nuestros registros contables en cualquier momento. • Descripción del archivo Excel: 2_3_DATOS_PR_RE_CENTROS

Este archivo tiene diferentes etiquetas, y nos permite introducir los datos del presupuesto calculados previamente y los datos reales de cada mes. A continuación vamos a describir su contenido y funcionamiento.

Descripción de las etiquetas: NOMBRE DE ETIQUETA O PESTAÑA

DESCRIPCIÓN

DATOS

Datos identificativos de la empresa y el período.

Etiquetas: 1D, 2D, 3D y 4D

Se corresponde con los dígitos de las cuentas contables establecidas por el usuario, se facilitan unas de ejemplo pero se pueden cambiar y adaptar.

CENTROS

Numero o código del centro que se introduce y nombre del mismo.

PR

Datos del presupuesto introducido por importes y meses.

RE

Datos reales introducidos por importes y meses.

TD1, TD2, TD3, TD4...

Ejemplos de tablas dinámicas para verificar los datos.

40

Descripción y contenido del modelo

- Descripción de etiqueta DATOS: Aquí podemos introducir los datos iniciales para identificar en qué versión, empresa o año estamos actuando.

- Descripción de etiquetas de cuentas de uno a cuatro dígitos:

Etiqueta de un dígito: 1D

1_Dígito

Control

Número del dígito correspondiente, en este caso de un digito. Este campo es numérico.

Descripción del grupo según el dígito correspondiente. Campo de tipo texto

Control de celda protegida, nos avisa si ponemos un digito inferior al siguiente.

Ejemplo del campo control: Situación correcta:

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

41

Situación incorrecta: Cuando detecta que el campo de la celda A4 con valor 8 es superior al de la celda A5 con valor 7 nos avisa con este mensaje: «Atención, número inferior», entonces se debe arreglar, porque si no nos da errores en el modelo.

Situación incorrecta:

Tampoco se deben repetir valores en las columnas numéricas, si no nos dará también errores cuando se analice el modelo en los textos de las cuentas.

La etiqueta 1D tiene sólo 20 filas para ser utilizadas. En cada columna se indica, mediante la opción de comentarios de celda de Excel, qué es cada campo y qué características tiene. Etiqueta de dos dígitos: Esta etiqueta funciona igual que la del grupo de un dígito, pero incluye el subgrupo de cuentas de dos dígitos.

Esta etiqueta 2D dispone de 150 filas.

42

Descripción y contenido del modelo

Etiqueta de tres dígitos: Esta etiqueta funciona igual que la del grupo de un dígito, pero incluye el subgrupo de cuentas de tres dígitos.

Esta etiqueta 3D dispone de 400 filas. Etiqueta de cuatro dígitos: Esta etiqueta funciona igual que la del grupo de un dígito, pero incluye el subgrupo de cuentas de cuatro dígitos.

Esta etiqueta 4D dispone de 650 filas. Nota: si le sale este mensaje al cambiar de pestaña sólo le sirve de recordatorio recomendando que evite el cortar y pegar, ya que puede generar un error en los cálculos cuando tenemos fórmulas vinculadas.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

43

- Descripción de etiquetas de centros de coste o ingreso: CENTROS

C_Centro Número del dígito correspondiente. Este campo es numérico o alfanumérico, pero es recomendable que tenga un determinado orden.

Centro_Nombre Descripción del centro correspondiente, campo de tipo texto.

Control Control de celda protegida. Nos avisa si ponemos un digito inferior al siguiente, sólo si el campo es numérico, si no lo es nos avisa de que no es un valor, pero no tiene efectos prácticos en los cálculos.

Situación normal en que son campos numéricos:

Dos ejemplos de campos de código centro que no son numéricos, son otras opciones pero se debe tener en cuenta luego en los cálculos:

44

Descripción y contenido del modelo

- Descripción de etiquetas de los datos presupuestados: PR

Esta etiqueta debe contener los siguientes datos: Nombre del campo

Explicación

Propiedades del Campo

Versión

El número de versión introducido anteriormente

Puede ser numérico y alfanumérico

Empresa

Nombre de la empresa abreviado

Campo texto

Año

Año en el que estamos haciendo el análisis

Importes numéricos

No_Cta

Número de la cuenta que se esté utilizando

Mejor que sea formato numérico, sin puntos siempre

Nombre_Cta

Nombre de la cuenta que se esté utilizando

Texto

C_Centro

Código del centro de coste que se esté utilizando

Texto o número, depende de cada empresa. Puede ser alfanumérico

Meses de 1 a 12

Importes en el mes correspondiente

Importes numéricos

- Descripción de etiquetas de datos reales: RE

Esta etiqueta debe contener los siguientes datos: Nombre del campo

Explicación

Propiedades del Campo

Versión

Estos campos vendrán automatizados de la etiqueta PR

Puede ser numérico y alfanumérico

Empresa

Estos campos vendrán automatizados de la etiqueta PR

Campo texto

Año

Estos campos vendrán automatizados de la etiqueta PR

Importes numéricos

No_Cta

Número de la cuenta que se esté utilizando

Mejor que sea formato numérico, sin puntos siempre

Nombre_Cta

Nombre de la cuenta que se esté utilizando

Texto

C_Centro

Código del centro de coste que se esté utilizando

Texto o número, depende de cada empresa. Puede ser alfanumérico

Meses de 1 a 12

Importes en el mes correspondiente

Importes numéricos

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

45

Tanto las etiquetas PR como RE son datos. Los primeros ya calculados, y los segundos que debemos actualizar cada mes, ya que pueden variar. Para una mejor comprensión del modelo ponemos un esquema para que se entienda cómo llegan los datos a estas dos pestañas: ORIGEN

DESTINO

2_1_CALCULO_PR_ CENTROS Desde la pestaña o etiqueta: PRESUPUESTO_MES



2_3_DATOS_PR_RE_CENTROS Se copia o vincula a la pestaña o etiqueta: PR



2_3_DATOS_PR_RE_CENTROS Se copia o vincula a la pestaña o etiqueta: RE

2_2_PLANTILLA_CUENTAS_ CENTROS Desde la pestaña o etiqueta: IMPORTES_REALES

- Descripción de etiquetas de ejemplos de tablas dinámicas: Estas etiquetas contienen los ejemplos que permiten verificar los datos, pero el usuario puede confeccionar tantos ejemplos como necesite con las funciones de las tablas dinámicas. Etiquetas

Descripción

TD1

Comparativo de datos presupuestados y reales por cuentas contables

TD2

Comparativo de datos presupuestados y reales por centros acumulado

TD3

Evolución de datos presupuestados por meses

TD4

Evolución de datos reales por meses

TD5

Evolución de desviaciones cuentas nivel 3

TD6

Evolución de desviaciones cuentas a máximo nivel

TD7

Evolución de desviaciones por centros a máximo nivel

• Descripción del archivo Excel: 2_5_RESULTADOS_PR_RE_CENTROS

Estas etiquetas contienen los ejemplos que permiten conocer el potencial de esta herramienta, pero el usuario puede confeccionar tantos ejemplos de cuentas como necesite con las funciones de las tablas dinámicas. 46

Descripción y contenido del modelo

Etiquetas

Descripción

TD1

Ejemplo de cuenta de explotación por cuentas contables, en % de las ventas, en importes y porcentajes

TD2

Ejemplo de cuenta de explotación por centros, en % de las ventas, en importes y porcentajes

En TD1, por ejemplo: a través de la vinculación entre las tablas dinámicas y las fórmulas de Excel podemos confeccionar una cuenta de explotación por cuentas contables de nivel 3 que permita hacer el análisis de desviaciones:

En TD2, por ejemplo: a través de la vinculación entre las tablas dinámicas y las fórmulas de Excel podemos confeccionar una cuenta de explotación por centros de coste que permita hacer el análisis de desviaciones:

MUY IMPORTANTE: CADA USUARIO PUEDE HACER LOS DIFERENTES INFORMES VINCULANDO LOS DATOS DE LAS TABLAS DINÁMICAS CON LAS FÓRMULAS DE EXCEL, AQUÍ SÓLO PONEMOS UNOS EJEMPLOS MUY CONCRETOS.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

47

1.2.3 Contenido del modelo de cobros y pagos para realizar el presupuesto de tesorería Este excel 2_4_PR_TESORERIA_CENTROS nos va a proporcionar un presupuesto de tesorería.

Descripción de las diferentes pestañas: PESTAÑAS DE EXCEL

DESCRIPCIÓN

DATOS1

Aquí se introducen los datos necesarios para gestionar los pagos y cobros de la tesorería.

DATOS2

Aquí se introducen los datos necesarios para gestionar las cuentas de pagos y cobros nuevas.

PR_INGRES

Se importan o vinculan sólo los ingresos del presupuesto.

PR_GASTO

Se importan o vinculan sólo los gastos del presupuesto.

IN_cobro

Introducción de tipos de IVA y plazos de cobro según el tipo de cuenta de ingresos.

GT_pago

Introducción de tipos de IVA, IRPF y plazos de cobro según el tipo de cuenta de gastos.

OTROS_COBROS

Posibilidad de introducir otros cobros de forma manual.

OTROS_PAGOS

Posibilidad de introducir otros pagos de forma manual.

INVERSIONES

Pagos de inversiones por adquisición de inmovilizado u otro tipo.

PT_1

Obtención de un primer listado de cobros y pagos desglosado en tablas dinámicas.

PT_2

Obtención de una primera lista de cobros y pagos desglosado en tablas dinámicas.

CONTROL_IVA

Permita analizar los saldos de IVA por si hay que efectuar algún ajuste al compensar el IVA.

A continuación se van a explicar las diferentes pestañas o etiquetas. • DATOS1

En primer lugar podemos poner el número de versión, nombre de la empresa abreviado y el año:

48

Descripción y contenido del modelo

En segundo lugar los datos necesarios para que el modelo haga cálculos e informes de la tesorería:

Sólo se pueden poner los datos en blanco. Estos campos permiten obtener los pagos por impuestos o, en el caso de impuestos a cobrar o compensar, hacer los oportunos ajustes. En tercer lugar, debemos crear tantos conceptos de ingresos y gastos o de cobros y pagos como tipos de agrupaciones queramos, siempre restringidos a la lógica de que sean lo más resumidos posibles. En los siguientes apartados se explicará su utilización.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

49

• DATOS2

En este apartado se crean las cuentas de todos aquellos conceptos que no provienen de los ingresos y gastos del presupuesto, es decir, los que el usuario introduce de forma manual en los apartados correspondientes. Los números de cuentas deben tener el mismos número de dígitos que las cuentas de ingresos y gastos.

Es importante respetar el número de dígitos en las cuentas contables. • PR_INGRES

Aquí se copian o vinculan las cuentas e importes por meses de ingresos que tenemos en el archivo de 2_3_DATOS_PR_RE_CENTROS, pestaña de PR:

• PR_GASTOS

Aquí se copian o vinculan las cuentas e importes por meses de gastos que tenemos en el archivo de 2_3_DATOS_PR_RE_CENTROS, pestaña de PR:

NOTA: SI SE COPIAN O VINCULAN LOS DATOS TENGA PRESENTE QUE NO TODOS LOS CONCEPTOS DE INGRESOS Y GASTOS SON AL FINAL COBRABLES O PAGABLES. EN LOS APARTADOS SIGUIENTES EXPLICAREMOS ESTO DE UNA FORMA MÁS AMPLIADA.

50

Descripción y contenido del modelo

• IN_cobro

En esta pestaña vamos a informar para cada tipo de cuenta de ingresos el tipo de IVA, el tipo de IRPF y el plazo de pago de los impuestos, mensual o trimestral.

Origen_Cobros IVA Plazo C_Centro No_Cta Nombre_Cta

Nos indica el origen el cobro de forma automática Indicar si la cuenta lleva un IVA incorporado. Indicar si queremos realizar los pagos de impuestos de forma trimestral o mensual según el tipo de cuenta. Nos indica el centro de forma automática. Nos indica el número de cuenta de forma automática. Nos indica el nombre de la cuenta de forma automática.

Campos para especificar los plazos de cobro:

En este caso cada C1 … C12, nos permite indicar el plazo al que podemos cobrar según el concepto de origen.Veamos algunos ejemplos: Si C2 es el 100% quiere decir que si el ingreso se produce el mes de junio el cobro con IVA será el mes de julio, es decir, a 30 días. Si queremos pagar a 60 días, entonces C3=100%, y si queremos pagar a 90 días del mes inicial del ingreso o cobro sería así: C4=100% Es muy importante entender que de C1 a C12 son los periodos donde empiezan a contar las formas de cobro o pago que pongamos. Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

51

Para pagos fraccionados debemos utilizar las fórmulas de la siguiente forma: Si C1=50% y C2=50%, quiere decir que si el ingreso se produce el mes de junio, el cobro con IVA será el mes de julio el 50% y el otro 50% en agosto. Si C1=25%, C2=25%, C3=25% y C4=25%, quiere decir que si el ingreso se produce el mes de junio, el cobro con IVA será un 25% desde el mes de junio hasta septiembre, es decir, dividimos el cobro en 4 partes. Observemos estos dos casos: en el primero, si no llegamos al 100% nos lo indica el modelo, y si nos pasamos del 100% nos indica error:

Quiere decir que hay que tener en cuenta esta situación y subsanarla. • GT_pago

En esta pestaña vamos a informar para cada tipo de cuenta de gastos el tipo de IVA, el tipo de IRPF y el plazo de cobro de los impuestos, mensual o trimestral.

Su funcionamiento es exactamente igual que la anterior opción de ingreso/cobros, pero hace falta tener en cuenta las siguientes particularidades: Hay conceptos que no son pagos e incluso tampoco son cobros.Veamos algunos ejemplos: − Dotaciones de amortizaciones. − Variaciones de existencias − Dotaciones de provisiones Etcétera. 52

Descripción y contenido del modelo

Es decir, todo aquellos que no genera movimientos monetarios en una organización de entradas o salidas en cuentas de tesorería no se debe considerar en el presupuesto de tesorería. En este caso, por ejemplo, debemos poner un porcentaje del 0%: Muy importante: los impuestos a nivel de IVA e IRPF pueden ser mensuales o trimestrales. Su pago siempre es en el período siguiente. Para ello tenemos que: Plazo

SI SE DEJA EN BLANCO POR DEFECTO EL PAGO ES AL MES SIGUIENTE

Plazo

T

SI PONEMOS LA T ENTONCES ES EL TRIMESTRE SIGUIENTE CONTANDO LOS PLAZOS INICIALES

• OTROS_COBROS

En este apartado podemos introducir los diferentes tipos de cobro o ajustes a realizar de forma manual, así se compone de los siguientes campos: DATOS

DESCRIPCIÓN

Origen

Seleccionar el dato de origen que nos permita identificar el cobro o pago.

No_Cta

Seleccionar el número de cuenta de origen.

Nombre_Cta

Seleccionar el nombre de la cuenta de origen.

Total

1

Introducir el importe en el mes o en los meses que correspondan.

2

Introducir el importe en el mes o en los meses que correspondan.

3

Introducir el importe en el mes o en los meses que correspondan.

4

Introducir el importe en el mes o en los meses que correspondan.

5

Introducir el importe en el mes o en los meses que correspondan.

6

Introducir el importe en el mes o en los meses que correspondan.

7

Introducir el importe en el mes o en los meses que correspondan.

8

Introducir el importe en el mes o en los meses que correspondan.

9

Introducir el importe en el mes o en los meses que correspondan.

10

Introducir el importe en el mes o en los meses que correspondan.

11

Introducir el importe en el mes o en los meses que correspondan.

12

Introducir el importe en el mes o en los meses que correspondan. Fórmula que totaliza la suma de importes por meses para comparar.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

53

Ejemplo de cobros:

NOTA: RECORDAR QUE LOS COBROS SE INTRODUCEN EN VALORES POSITIVOS.

• OTROS_PAGOS

En este apartado podemos introducir los diferentes tipos de pagos o ajustes a realizar de forma manual. Se compone de los mismos campos que el apartado anterior.Veamos un ejemplo a continuación:

NOTA: RECORDAR QUE LOS PAGOS SE INTRODUCEN EN VALORES NEGATIVOS.

• INVERSIONES

En este apartado podemos introducir los diferentes tipos de pagos por inversiones a realizar de forma manual. Se compone de los siguientes campos: DATOS

DESCRIPCIÓN

Origen

Seleccionar el dato de origen que nos permita identificar el cobro o pago.

IVA

Introducir el IVA para que nos calcule el importe correspondiente de forma automática.

Plazo

Determinar si el IVA es mensual –se deja vacío– o trimestral –se pone una T.

No_Cta

Seleccionar el número de cuenta de origen.

Nombre_Cta

Seleccionar el nombre de la cuenta de origen.

54

1

Introducir el importe en el mes o en los meses que correspondan.

2

Introducir el importe en el mes o en los meses que correspondan.

3

Introducir el importe en el mes o en los meses que correspondan.

Descripción y contenido del modelo

Total

4

Introducir el importe en el mes o en los meses que correspondan.

5

Introducir el importe en el mes o en los meses que correspondan.

6

Introducir el importe en el mes o en los meses que correspondan.

7

Introducir el importe en el mes o en los meses que correspondan.

8

Introducir el importe en el mes o en los meses que correspondan.

9

Introducir el importe en el mes o en los meses que correspondan.

10

Introducir el importe en el mes o en los meses que correspondan.

11

Introducir el importe en el mes o en los meses que correspondan.

12

Introducir el importe en el mes o en los meses que correspondan. Fórmula que totaliza la suma de importes por meses para comparar.

Veamos un ejemplo a continuación:

NOTA: LAS INVERSIONES SE INTRODUCEN EN VALORES NEGATIVOS.

• PT1

Esta pestaña contiene el presupuesto de tesorería desglosado al máximo detalle. En él se pueden comprobar los datos y los saldos finales. Como se puede observar, su funcionamiento es muy sencillo. Al inicio nos encontramos con los saldos iniciales de cada período que acumula el saldo anterior con el saldo del período actual.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

55

• PT2

Esta pestaña contiene el presupuesto de tesorería resumido. En él se pueden analizar los datos y los saldos finales. Como se puede observar, su funcionamiento es muy sencillo. Al inicio nos encontramos con los saldos iniciales de cada período que acumula el saldo anterior con el saldo del período actual.

• CONTROL_IVA

Este apartado nos sirve sólo para verificar que si tenemos en un período un saldo positivo hay que ponerlo de forma manual como un pago en el período en que estamos y como un cobro en el siguiente. Ejemplo de control de IVA:

RECUERDE QUE EL IVA SOPORTADO SI NO SE CONSIDERA UN COBRO A DEVOLVER SE DEBE AJUSTAR MANUALMENTE.

56

Descripción y contenido del modelo

Nota: para actualizar los datos en PT_1, PT_2 y en CONTROL_IVA, se debe realizar estando situado el cursor sobre la tabla dinámica y con el botón derecho del ratón pulsar sobre actualizar.

1.2.4 Contenido del modelo del presupuesto de objetivos para realizar su seguimiento En este apartado: 3_MODELO_FIJAR_OBJETIVOS

3_1_FIJAR_OBJETIVOS

3_FIJAR_OBJETIVOS es la plantilla de Excel que nos permite poner objetivos previos y hacer su posterior seguimiento:

APARTADOS DATOS

DESCRIPCIÓN Nos permite introducir los datos iniciales del modelo.

PR

Permite poner los objetivos previstos o presupuestados.

RE

Permite copiar los datos reales para hacer los comparativos.

TD1

Análisis de las desviaciones.

TD2

Evolución de datos presupuestados por meses.

TD3

Evolución de datos reales por meses.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

57

• DATOS

Introducir los datos iniciales: el nombre del análisis, la versión, nombre de la empresa y el año.

• PR

Aquí se deben introducir los datos de los objetivos previstos por meses, pueden ser, por ejemplo, ventas por vendedor, por familia o por clientes.

• RE

Aquí se deben introducir los datos de los objetivos conseguidos por meses, pueden ser, por ejemplo, ventas por vendedor, por familia o por clientes.

58

Descripción y contenido del modelo

• TD_1

Aquí tenemos un ejemplo de objetivos de ventas por familia que compara los datos entre los objetivos reales y los previstos, así como sus desviaciones.

• TD_2 y TD_3

Presentan la evolución por meses de los objetivos previos fijados y los datos reales.

Nota: para actualizar los datos en TD_1, TD_2, TD_3, se debe realizar estando situado el cursor sobre la tabla dinámica y con el botón derecho del ratón pulsar sobre actualizar.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

59

60

Descripción y contenido del modelo

2 Instalación y sistema de utilización del modelo

Objetivos del capítulo

• Conocer el contenido del software y las condiciones de uso. • Conocer cómo se instala el software y los diferentes tipos de datos. • Obtener una visión general del contenido y relaciones entre los diferentes tipos de datos del modelo.

61

2.1 Instrucciones de instalación del modelo Este modelo viene definido en formato Excel (hay dos modelos uno para Excel 2003 y otro para Excel 2007-10), que permite una mayor flexibilidad a la hora de trabajar, ya que nos permite adaptar los informes a nuestras necesidades. Adicionalmente se deben actualizar los diferentes datos con la herramienta de Excel de tablas dinámicas. Este modelo que vamos a describir y utilizar está basado en Excel 2003 como punto de partida, pero puede ser utilizado por versiones de Excel 2007 y 2010. El modelo está sostenido por los cálculos, que se actualizan cuando actualizamos las tablas dinámicas, herramienta que está incorporada en todas las versiones de Excel 2003 y posteriores. Se explicará a lo largo de este libro qué función cumplen para calcular y actualizar los datos. Para la instalación se debe acceder al CD que se adjunta en el libro y copiar el directorio en el disco C: de su ordenador, donde se desee utilizar. Elegir antes la versión a utilizar. El contenido del modelo es el siguiente:

Los archivos Excel aquí utilizados tienen las siguientes características: − Se pueden utilizar en cualquier ordenador, pero deben tener instalada una versión de Excel 2003 o superior. − Cómo son archivos Excel poseen todas las características de este software y se pueden utilizar en cada uno de los ejemplos que se pretenda realizar, pero partiendo siempre de la plantilla original. − Es importante siempre realizar una copia de la información original y trabajar con ella, ya que así, ante cualquier contingencia o problema, siempre se puede volver a la situación anterior o realizar de nuevo los cálculos. − Se pueden realizar tantos presupuestos como se requiera, pero siempre teniendo en cuenta las características del modelo y del propio Excel como hoja de cálculo. 62

Instalación y sistemas de utilización del modelo

− Se pueden relacionar datos calculados en otro archivo de Excel con los datos de Excel del presupuesto, pero siempre respetando los formatos iniciales. − Se deben respetar las formas de trabajo que hay en cada una de las hojas del modelo. − En cuanto al sistema de impresión de los datos, se debe de utilizar el que lleva incorporado el Excel. − El modelo está diseñado para poder actualizar los cálculos mediante la herramienta de tabla dinámica, que debe estar instalada de origen en su versión Excel 2003 o posterior; es una herramienta de serie y gratuita en Excel 2003 y versiones superiores. – No modificar NUNCA los nombre de las etiquetas o pestañas de cada archivo Excel. − A continuación se detallan las condiciones de uso: El usuario de este libro realizado con Excel® puede usarlo y modificarlo* como desee para su uso personal o profesional, pero no puede distribuir copias, comerciar con él, usarlo para crear productos destinados a la venta u otorgarse su autoría total o parcial sin el permiso previo y por escrito del autor o de la editorial. *Todas las hojas VISIBLES son modificables según los campos establecidos para ello, y las hojas ocultas no son modificables. Este producto es una plantilla de Excel para realizar los presupuestos correspondientes. Una plantilla es una estructura creada para facilitar los cálculos al usuario; sus resultados o formas de cálculo no deben considerarse correctos en todos los países y circunstancias. Es el propio usuario quien debe incluir adecuadamente los datos y contenidos adecuados, revisar que los cálculos y operaciones sean correctos y disponer de los conocimientos técnicos y las informaciones para hacer el presupuesto. Al adquirir una plantilla en Excel, únicamente se está abonando el tiempo y el conocimiento que el autor ha invertido en la creación de la plantilla tal y como le es entregada, no se adquiere ningún derecho a asesoramiento o asistencia en la elaboración del presupuesto ni para la adaptación de la plantilla a las necesidades específicas del usuario o de la legislación del * Ni el autor ni la editorial se hacen responsables de la manipulación del modelo aquí entregado y descrito en el libro.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

63

país. Las condiciones y funcionamiento están aceptadas por el usuario antes de la compra, y una vez adquiera el software y el libro tendrá derecho al uso. Este fichero Excel® ha sido guardado con la versión 2003 para Windows, pero puede ser utilizado en el resto de versiones. Excel® es una marca registrada propiedad de Microsoft Corporation. Este producto se entrega de forma que se puede utilizar sólo lo que tiene accesible el usuario*, por esta razón el editor y el autor no se hacen responsables de su idoneidad y perfecto funcionamiento en cualquier circunstancia.

2.2 Forma de utilizar el modelo A continuación, de forma esquemática, vamos a explicar el funcionamiento del modelo y las diferentes relaciones. En primer lugar se describe el contenido: El directorio principal contiene las plantillas de trabajo y los casos prácticos:

Relaciones entre modelos que funcionan sólo entre cuentas:

* Ni el autor ni la editorial se hacen responsables de la manipulación del modelo aquí entregado y descrito en el libro.

64

Instalación y sistemas de utilización del modelo

Relaciones entre archivos de Excel y pestañas: ARCHIVO PESTAÑA

PRESUPUESTO_MES

PR

IMPORTE_REAL

RE

ARCHIVO PESTAÑA ARCHIVO PESTAÑA

TD1

ARCHIVO PESTAÑA

TD1

En el caso de que se trabaje con centros de coste y cuentas, es el mismo proceso anterior:

Para los objetivos es mucho más sencillo, ya que sólo existe un Excel que incorpora los datos previstos y los reales:

Y para los casos prácticos tenemos el mismo esquema:

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

65

66

Instalación y sistemas de utilización del modelo

Parte 2 Confección del presupuesto de ingresos, gastos y de la previsión de tesorería

3 Enunciado del caso práctico de una empresa de fabricación mediante cuentas y centros de coste: cálculo de ingresos y gastos previstos Objetivos del capítulo

• Conocer cómo obtener los datos necesarios para realizar los diferentes tipos de presupuestos operativos para cada área de responsabilidad o responsable. • Obtener una visión sobre cómo los datos deben ser calculados previamente para obtener el presupuesto de ingresos y gastos, para ser presentados en un formato estándar por meses.

69

3.1 Actividad y descripción de la empresa La empresa PAMO, S.A. (en adelante PAMO) es una de las mayores organizaciones del sector alimentario en la Península Ibérica. Para este caso práctico se va utilizar una empresa que fabrica tres tipos de pan: pan de molde normal, pan tostado (normal e integral) y pan de molde integral. Con toda su estructura permite hacer llegar cada día a sus clientes sus productos frescos, tiernos y deliciosos. Gracias a esta gran aceptación por parte del público, los productos se han consolidado como líderes de mercado en las diferentes categorías. La empresa utiliza la última tecnología para fabricar sus productos, de forma que la calidad satisfaga las expectativas de sus clientes. PAMO ha realizado siempre una apuesta firme por la innovación y la calidad de sus productos. Por ello, un importante equipo de profesionales altamente cualificados trabaja permanentemente en los Centros de Investigación y Desarrollo (I+D). Allí mejoran constantemente la calidad de los productos y desarrollan otros nuevos que se adapten a las necesidades cambiantes del consumidor. Podemos decir, pues, que el éxito de esta compañía reside en su equipo humano: en todas esas personas que con su esfuerzo e interés consiguen que PAMO sea un líder del sector en ventas y, lo más importante, líder en confianza. La gente de PAMO es imprescindible para que todos nuestros clientes y consumidores reciban la mejor calidad y el máximo servicio. Por ello contratamos a los mejores profesionales y prestamos la máxima atención a su formación, seguridad, condiciones de trabajo y desarrollo profesional. En PAMO estamos orgullosos de tener a las mejores personas del sector entre nosotros. A continuación se desglosan los ingresos y gastos para el año 2011, que nos deben servir para calcular el presupuesto para el año 2011.

70

Enunciado del caso práctico de una empresa de fabricación...

• Organigrama Cuadro 3.1: organigrama funcional

Dirección general

Dirección comercial

Dirección de fábrica

Dirección de almacén y logística

Dirección de administración y finanzas

• Descripción de funciones:

− Dirección general: coordina y gestiona todas las áreas de la empresa en cuanto a la toma de decisiones y consecución de objetivos, así como la planificación estratégica. − Dirección comercial: coordina y gestiona todas acciones comerciales de la fuerza de ventas, la relación con los clientes y los objetivos comerciales. − Dirección de fábrica: coordina y gestiona todos los procesos de fabricación, incluyendo la gestión de la calidad. − Dirección de almacén y logística: coordina y gestiona todos los procesos de recepción, entrega y almacenaje de las materias primas y de los productos terminados. − Dirección de administración y finanzas: coordina y gestiona la parte administrativa y financiera, así como el control de gestión.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

71

• Centros de coste e ingresos

Los centros de coste e ingresos utilizados en la empresa son los siguientes: Cuadro 3.2: centros de coste e ingresos

TIPO DE CENTROS

COD_CENTRO

CENTROS

1_Ingresos

1000

Ventas

2_Costes directos

2000

Compras

2_Costes directos

3000

Directos de fabricación

3_Indirectos de fábrica

4100

Departamento de producción

3_Indirectos de fábrica

4200

Logística

3_Indirectos de fábrica

4900

Servicios generales fábrica

4_Comerciales

5000

Comercial

5_Estructura

6100

Administración

5_Estructura

6200

Dirección

5_Estructura

6900

Servicios generales de estructura

6_Financiero

7000

Financieros

En el siguiente cuadro se explica la relación y dependencia existente entre los centros de coste e ingreso y las diferentes áreas o departamentos de la empresa. Cuadro 3.3: relación entre centros de coste e ingreso y áreas o departamentos

Dirección Ventas

Comercial

Fábrica

Almacén

X

Compras

X

Gastos auxiliares

X

Gastos de fabricación

X

Comerciales

X

Estructura Personal

X X

X

X

X

Financieros Amortizaciones

72

Administración

X X

X

X

X

X

X

Enunciado del caso práctico de una empresa de fabricación...

A continuación también exponemos el plan de cuentas donde se utilizarán los conceptos de ingresos y gastos utilizados en el caso práctico: Cuadro 3.4: código y descripción de cuentas contables Código de la cuenta

Nombre de la cuenta

6000000

Materias primas A

6000001

Materias primas B

6000002

Materias primas C

6000003

Materias primas D

6000004

Materias primas E

6020000

Gas oil hornos

6020001

Lubricantes y aceites

6020002

Cajas de cartón

6020003

Celofán

6100000

Variación de existencias

6210000

Alquileres

6220000

Reparaciones y conservación

6230000

Servicios de profesionales

6240000

Transporte de ventas

6250000

Seguros

6270001

Catálogos

6270002

Envío de muestras

6270003

Ferias y exposiciones

6270004

Mailings y anuncios

6280000

Electricidad

6280001

Agua

6280002

Gas

6280003

Combustibles

6280004

Suministros

6290000

Comunicaciones

6290001

Material de oficina

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

73

Código de la cuenta

Nombre de la cuenta

6290002

Varios

6290003

Mensajeros

6310000

Tributos

6400000

Sueldos y salarios

6420000

Seguros sociales

6690000

Financieros

6820000

Dotación amortizaciones

6940000

Provisión insolvencias

7000000

Pan de molde normal

7000001

Pan tostado

7000003

Pan de molde integral

3.2 Cálculo de los ingresos • Previsión de las ventas

La previsión de ventas por familia de productos anual es la siguiente: Cuadro 3.5: previsión de ventas anual Familia de productos

Año actual

Aumento de ventas previsto

1.755.576

11%

1.950.000

Pan tostado

239.101

21%

290.000

11%

Pan de molde integral

291.331

27%

370.000

14%

2.610.000

100%

Pan de molde normal

Total

2.286.008

 

Ventas previstas

% sobre el total 75%

En este caso la previsión de ventas se calcula previamente y se llega a la conclusión de aplicar unos porcentajes respecto a los valores del año anterior.

74

Enunciado del caso práctico de una empresa de fabricación...

• Estacionalidad de las ventas

Un paso decisivo para poder calcular las ventas mensuales es fijar la estacionalidad por meses de las ventas. En nuestro caso esta estacionalidad es proporcional a todos los productos por igual, pero se puede dar el caso de que la estacionalidad sea en función de los tipos de productos que se vendan, y entonces se deberá calcular una para cada tipo de producto. Cuadro 3.6: estacionalidad de las ventas

Mes

%

Enero

6%

Febrero

9%

Marzo

10%

Abril

9%

Mayo

7%

Junio

9%

Julio

8%

Agosto

7%

Septiembre

8%

Octubre

10%

Noviembre

9%

Diciembre

8% Total

100%

• Previsión de ventas por mes

En este apartado calculamos, para cada tipo de producto, las previsiones de ventas mensuales que nos permiten según la estacionalidad y el porcentaje de ventas sobre el total para cada familia.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

75

76

Enunciado del caso práctico de una empresa de fabricación...

8%

8

7%

9

8%

10%

10

9%

11

8%

12

33.300

26.100 37.000

29.000 33.300

26.100 25.900

20.300 33.300

26.100 29.600

23.200 25.900

20.300 29.600

23.200

37.000

29.000

33.300

26.100

29.600

23.200

156.600 234.900 261.000 234.900 182.700 234.900 208.800 182.700 208.800 261.000 234.900 208.800

9%

7

Total

7%

6

22.200

9%

5

Pan de molde integral

10%

4

17.400

9%

3

Pan tostado

6%

2

117.000 175.500 195.000 175.500 136.500 175.500 156.000 136.500 156.000 195.000 175.500 156.000

1

Pan de molde normal

Estacionalidad mensual

Mes

Cuadro 3.7: previsión de ventas por mes

2.610.000

370.000

290.000

1.950.000

100%

Total

Ejemplo de cálculo para el mes de enero: Cuadro 3.8: ejemplo de previsión de ventas para el mes de enero

Ventas previstas totales Pan de molde normal

% de estacionalidad mes de enero

Ventas previstas mes de enero

% sobre el total

1.950.000

6%

117.000

75%

Pan tostado

290.000

6%

17.400

11%

Pan de molde integral

370.000

6%

22.200

14%

 

156.600

100%

Total

2.610.000

3.3 Cálculo de los gastos A continuación se van a calcular los importes por mes de los diferentes tipos de gastos para incluirlos en el presupuesto. • Previsión de compras y variación de stocks

La previsión de compras y la variación de stocks se calcula según un % sobre las ventas de cada período. Cuadro 3.9: resumen de la previsión de compras y las variaciones de stocks.

PREVISIÓN Ventas previstas

2.610.000

Ventas previstas

% Compras s/ventas

23%

Porcentaje total de las compras sobre las ventas

% Variación stocks s/ ventas

-6%

Porcentaje total de la variación de los stocks sobre las ventas

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

77

• Previsión de compras anual según un % sobre las ventas:

Cuadro 3.10: resumen del porcentaje de las compras sobre las ventas para su posterior cálculo. % compras sobre ventas

Importe

 

Ventas

2.610.000

Materias primas A

265.847

10,2%

Materias primas B

46.184

1,8%

Materias primas C

83.068

3,2%

Materias primas D

127.225

4,9%

Materias primas E

77.977

3,0%

600.300

23,0%

Total compras

• Previsión de compras y variación de stocks

En este apartado multiplicamos el porcentaje de cada tipo de compra por el importe de las ventas mensuales y la variación de stocks de la misma forma. (cuadro 3.12) Cuadro 3.11: porcentaje de compras sobre las ventas por mes

Mes

Materias primas A

1

2

3

4

5

6

7

8

9

10

11

12

10,2% 15,3% 17,0% 15,3% 11,9% 15,3% 13,6% 11,9% 13,6% 17,0% 15,3% 13,6%

Materias primas B

1,8%

2,7%

2,9%

2,7%

2,1%

2,7%

2,4%

2,1%

2,4%

2,9%

2,7%

2,4%

Materias primas C

3,2%

4,8%

5,3%

4,8%

3,7%

4,8%

4,2%

3,7%

4,2%

5,3%

4,8%

4,2%

Materias primas D

4,9%

7,3%

8,1%

7,3%

5,7%

7,3%

6,5%

5,7%

6,5%

8,1%

7,3%

6,5%

Materias primas E

3,0%

4,5%

5,0%

4,5%

3,5%

4,5%

4,0%

3,5%

4,0%

5,0%

4,5%

4,0%

23%

35%

38%

35%

27%

35%

31%

27%

31%

38%

35%

31%

total % compra/ ventas

78

Enunciado del caso práctico de una empresa de fabricación...

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

79

Previsión de compras en función del % de las ventas por mes

54.027

42.021

54.027

7.018

48.024

6.238

10.178

6.645

3.695

21.268

48.024

42.021

5.458

8.906

5.815

3.233

18.609

42.021

48.024

6.238

10.178

6.645

3.695

21.268

48.024

60.030

7.798

12.722

8.307

4.618

26.585

60.030

54.027

7.018

11.450

7.476

4.157

23.926

54.027

48.024

6.238

10.178

6.645

3.695

21.268

48.024

-9.396 -14.094 -15.660 -14.094 -10.962 -14.094 -12.528 -10.962 -12.528 -15.660 -14.094 -12.528

60.030

5.458

11.450

7.476

4.157

23.926

54.027

Variación de stocks

54.027

7.018

8.906

5.815

3.233

18.609

42.021

36.018

7.798

11.450

7.476

4.157

23.926

54.027

Total compras

7.018

12.722

8.307

4.618

26.585

60.030

4.679

11.450

7.476

4.157

23.926

54.027

Materias primas E

12

7.633

11

Materias primas D

10

4.984

9

Materias primas C

8

2.771

7

Materias primas B

6

15.951

5

Materias primas A

4

36.018

3

Compras

2

156.600 234.900 261.000 234.900 182.700 234.900 208.800 182.700 208.800 261.000 234.900 208.800

1

Ventas

Mes

Cuadro 3.11: compras y variación de stocks por meses

-156.600

600.300

77.977

127.225

83.068

46.184

265.847

600.300

2.610.000

Total

• Previsión de gastos auxiliares de fabricación En este apartado multiplicamos el porcentaje de cada tipo de gasto por el importe de las ventas mensuales. Consideramos unos porcentajes constantes cada mes. (Cuadro 3.13) En este apartado, para calcular los costes de fabricación por mes, multiplicamos el porcentaje de cada tipo de gasto por el importe de las ventas mensuales. Consideramos unos porcentajes constantes cada mes. Porcentaje de gastos auxiliares de fabricación sobre el total de ventas mensual. (Cuadro 3.14) • Costes de fabricación

En este apartado, para calcular los costes de fabricación por mes, multiplicamos el porcentaje de cada tipo de gasto por el importe de las ventas mensuales. Consideramos unos porcentajes constantes cada mes. (Cuadro 3.15) Porcentaje de costes de fabricación sobre el total de ventas mensual. (Cuadro 3.16)

80

Enunciado del caso práctico de una empresa de fabricación...

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

81

125

633

Precintos y plásticos

Total

2

950

188

150

500

113

2

3

1.056

208

167

556

125

3

4

950

188

150

500

113

4 88

5

739

146

117

389

5

6

950

188

150

500

113

6

7

844

167

133

444

100

7 88

8

739

146

117

389

8

9

844

167

133

444

100

9

10

1.056

208

167

556

125

10

11

950

188

150

500

113

11

12

844

167

133

444

100

12

Total

10.556

2.083

1.667

5.556

1.250

Total

0,05% 0,21% 0,06% 0,08%

0,40%

Aceites y grasas

Embalajes de cartón

Precintos y plásticos

Total

1

0,40%

0,08%

0,06%

0,21%

0,05%

2

0,40%

0,08%

0,06%

0,21%

0,05%

3

0,40%

0,08%

0,06%

0,21%

0,05%

4

0,40%

0,08%

0,06%

0,21%

0,05%

5

0,40%

0,08%

0,06%

0,21%

0,05%

6

0,40%

0,08%

0,06%

0,21%

0,05%

7

0,40%

0,08%

0,06%

0,21%

0,05%

8

Cuadro 3.14: porcentaje de gastos auxiliares de fabricación

0,40%

0,08%

0,06%

0,21%

0,05%

9

0,40%

0,08%

0,06%

0,21%

0,05%

10

0,40%

0,08%

0,06%

0,21%

0,05%

11

0,40%

0,08%

0,06%

0,21%

0,05%

12

156.600 234.900 261.000 234.900 182.700 234.900 208.800 182.700 208.800 261.000 234.900 208.800 2.610.000

Gas oil hornos

Ventas

100

Embalajes de cartón

1

333

Aceites y grasas

Mes

75

Gas oil hornos

1

Cuadro 3.13: gastos auxiliares de fabricación por mes

82

Enunciado del caso práctico de una empresa de fabricación...

2

16.688 3

18.542

181

3.056

11.111

444

3.750

3

4

16.688

163

2.750

10.000

400

3.375

4

5

12.979

126

2.139

7.778

311

2.625

5

6

16.688

163

2.750

10.000

400

3.375

6

7

14.833

144

2.444

8.889

356

3.000

7

8

12.979

126

2.139

7.778

311

2.625

8

9

14.833

144

2.444

8.889

356

3.000

9

10

18.542

181

3.056

11.111

444

3.750

10

11

16.688

163

2.750

10.000

400

3.375

11

12

14.833

144

2.444

8.889

356

3.000

12

1,44% 0,17% 4,26% 1,17% 0,07%

7,10%

Agua

Gas

Combustibles

Otros suministros

Total

1

7,10%

0,07%

1,17%

4,26%

0,17%

1,44%

2

7,10%

0,07%

1,17%

4,26%

0,17%

1,44%

3

7,10%

0,07%

1,17%

4,26%

0,17%

1,44%

4

7,10%

0,07%

1,17%

4,26%

0,17%

1,44%

5

7,10%

0,07%

1,17%

4,26%

0,17%

1,44%

6

7,10%

0,07%

1,17%

4,26%

0,17%

1,44%

7

7,10%

0,07%

1,17%

4,26%

0,17%

1,44%

8

7,10%

0,07%

1,17%

4,26%

0,17%

1,44%

9

Cuadro 3.16: Porcentaje de costes de fabricación sobre el total de ventas mensual

7,10%

0,07%

1,17%

4,26%

0,17%

1,44%

10

7,10%

0,07%

1,17%

4,26%

0,17%

1,44%

11

156.600 234.900 261.000 234.900 182.700 234.900 208.800 182.700 208.800 261.000 234.900 208.800

1

11.125

163

Electricidad

Ventas

Mes

Total

108

Otros suministros

2.750

10.000

6.667

1.833

Gas

400

3.375

2

267

2.250

Combustibles

Agua

Electricidad

1

Cuadro 3.15: Costes de fabricación por mes

7,10%

0,07%

1,17%

4,26%

0,17%

1,44%

12

2.610.000

Total

185.417

1.806

30.556

111.111

4.444

37.500

Total

• Gastos comerciales

Los gastos comerciales se calculan en función de las ventas aplicando un porcentaje en cada período a las ventas del mes. Cuadro 3.17: Porcentaje de comisiones sobre el total de ventas mensual

Importes

% sobre ventas

Comisiones

234.900

9%

Catálogos

52.200

2%

Envío de muestras

52.200

2%

Ferias y exposiciones

78.300

3%

Mailings y anuncios

26.100

1%

Provisión insolvencias

26.100

1%

Transporte de ventas

78.300

3%

Total

548.100

Cálculo de los gastos comerciales (Cuadro 3.18) • Gastos de personal

En el siguiente cuadro se describen los costes de personal anuales. Para simplificar vamos a suponer que los costes mensuales son proporcionales cada mes. (Cuadro 3.19, cuadro 3.20 y cuadro 3.21).

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

83

84

Enunciado del caso práctico de una empresa de fabricación...

49.329

54.810

49.329

7.047 38.367

5.481 49.329

7.047

2.349

43.848

6.264

2.088

2.088

6.264

4.176

4.176

18.792

38.367

5.481

1.827

1.827

5.481

3.654

3.654

16.443

43.848

6.264

2.088

2.088

6.264

4.176

4.176

18.792

54.810

7.830

2.610

2.610

7.830

5.220

5.220

23.490

49.329

7.047

2.349

2.349

7.047

4.698

4.698

21.141

43.848

6.264

2.088

2.088

6.264

4.176

4.176

18.792

32.886

7.830

1.827

2.349

7.047

4.698

4.698

21.141

Total

7.047

2.349

1.827

5.481

3.654

3.654

16.443

4.698

2.610

2.349

7.047

4.698

4.698

21.141

Transporte de ventas

2.349

2.610

7.830

5.220

5.220

23.490

1.566

2.349

7.047

4.698

4.698

21.141

Provisión insolvencias

12

1.566

11

Mailings y anuncios

10

4.698

9

Ferias y exposiciones

8

3.132

7

Envío de muestras

6

3.132

5

Catálogos

4

14.094

3

Comisiones

2

156.600 234.900 261.000 234.900 182.700 234.900 208.800 182.700 208.800 261.000 234.900 208.800

1

Ventas

Mes

Cuadro 3.18: Gastos comerciales por mes

548.100

78.300

26.100

26.100

78.300

52.200

52.200

234.900

2.610.000

Total

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

85

3.000

Director comercial

Jefe de ventas

Vendedores

Dirección de fábrica

Encargados de fábrica

Operarios fábrica

Horas extras fábrica

Jefe de almacén

Operarios

Dirección administrativa

Contable

Auxiliar contable

Limpieza

Vigilantes

Conductores

Comercial

Comercial

Comercial

Dirección de fábrica

Directos de fábrica

Directos de fábrica

Directos de fábrica

Logística

Logística

Administración

Administración

Administración

Costes generales fábrica

Costes generales fábrica

Logística

Total

1.000

Dirección general

Dirección

1.000

1.000

1.000

1.000

1.500

2.000

544

1.500

2.000

3.500

1.500

2.500

3.500

4.500

Componentes

Centro

Importe mensual

1

1

1

1

1

1

1

1

1

32

1

1

1

1

1

1

Número de personas

77.544

1.000

1.000

1.000

1.000

1.500

3.000

1.000

2.000

544

48.000

2.000

3.500

1.500

2.500

3.500

4.500

Importe bruto mensual

24.814

320

320

320

320

480

960

320

640

174

15.360

640

1.120

480

800

1.120

1.440

Seguridad Social mensual

Cuadro 3.19: Detalle de los gastos de personal

102.358

1.320

1.320

1.320

1.320

1.980

3.960

1.320

2.640

718

63.360

2.640

4.620

1.980

3.300

4.620

5.940

Total mensual

Administración

Administración

Administración

Almacén

Almacén

Fábrica

Fábrica

Fábrica

Fábrica

Comercial

Comercial

Comercial

Dirección

Almacén

Fábrica

Fábrica

 

86

Enunciado del caso práctico de una empresa de fabricación...

1.000 1.000 1.000 1.000 77.544

Administración

Costes generales fábrica

Costes generales fábrica

Logística

Total

544

Directos de fábrica

1.500

48.000

Directos de fábrica

Administración

2.000

Directos de fábrica

3.000

3.500

Dirección de fábrica

Administración

1.500

Comercial

1.000

2.500

Comercial

Logística

3.500

Comercial

2.000

4.500

Dirección

Logística

1

Centro

77.544

1.000

1.000

1.000

1.000

1.500

3.000

1.000

2.000

544

48.000

2.000

3.500

1.500

2.500

3.500

4.500

2

77.544

1.000

1.000

1.000

1.000

1.500

3.000

1.000

2.000

544

48.000

2.000

3.500

1.500

2.500

3.500

4.500

3

77.544

1.000

1.000

1.000

1.000

1.500

3.000

1.000

2.000

544

48.000

2.000

3.500

1.500

2.500

3.500

4.500

4

77.544

1.000

1.000

1.000

1.000

1.500

3.000

1.000

2.000

544

48.000

2.000

3.500

1.500

2.500

3.500

4.500

5

77.544

1.000

1.000

1.000

1.000

1.500

3.000

1.000

2.000

544

48.000

2.000

3.500

1.500

2.500

3.500

4.500

6

77.544

1.000

1.000

1.000

1.000

1.500

3.000

1.000

2.000

544

48.000

2.000

3.500

1.500

2.500

3.500

4.500

7

77.544

1.000

1.000

1.000

1.000

1.500

3.000

1.000

2.000

544

48.000

2.000

3.500

1.500

2.500

3.500

4.500

8

2.000

3.500

1.500

2.500

3.500

4.500

9

77.544

1.000

1.000

1.000

1.000

1.500

3.000

1.000

2.000

544

48.000

Cuadro 3.20: Detalle de los gastos de personal por mes

77.544

1.000

1.000

1.000

1.000

1.500

3.000

1.000

2.000

544

48.000

2.000

3.500

1.500

2.500

3.500

4.500

10

77.544

1.000

1.000

1.000

1.000

1.500

3.000

1.000

2.000

544

48.000

2.000

3.500

1.500

2.500

3.500

4.500

11

77.544

1.000

1.000

1.000

1.000

1.500

3.000

1.000

2.000

544

48.000

2.000

3.500

1.500

2.500

3.500

4.500

12

930.528

12.000

12.000

12.000

12.000

18.000

36.000

12.000

24.000

6.528

576.000

24.000

42.000

18.000

30.000

42.000

54.000

Total

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

87

640 320 960 480 320 320 320 320

Logística

Logística

Administración

Administración

Administración

Costes generales fábrica

Costes generales fábrica

Logística 24.814

174

Directos de fábrica

Total

15.360

Directos de fábrica

480

Comercial

640

800

Comercial

Directos de fábrica

1.120

Comercial

1.120

1.440

Dirección

Dirección de fábrica

1

Centro

24.814

320

320

320

320

480

960

320

640

174

15.360

640

1.120

480

800

1.120

1.440

2

24.814

320

320

320

320

480

960

320

640

174

15.360

640

1.120

480

800

1.120

1.440

3

24.814

320

320

320

320

480

960

320

640

174

15.360

640

1.120

480

800

1.120

1.440

4

24.814

320

320

320

320

480

960

320

640

174

15.360

640

1.120

480

800

1.120

1.440

5

24.814

320

320

320

320

480

960

320

640

174

15.360

640

1.120

480

800

1.120

1.440

6

24.814

320

320

320

320

480

960

320

640

174

15.360

640

1.120

480

800

1.120

1.440

7

24.814

320

320

320

320

480

960

320

640

174

15.360

640

1.120

480

800

1.120

1.440

8

24.814

320

320

320

320

480

960

320

640

174

15.360

640

1.120

480

800

1.120

1.440

9

24.814

320

320

320

320

480

960

320

640

174

15.360

640

1.120

480

800

1.120

1.440

10

Cuadro 3.21: Detalle de los gastos de la seguridad social a cargo de la empresa por mes

24.814

320

320

320

320

480

960

320

640

174

15.360

640

1.120

480

800

1.120

1.440

11

24.814

320

320

320

320

480

960

320

640

174

15.360

640

1.120

480

800

1.120

1.440

12

297.769

3.840

3.840

3.840

3.840

5.760

11.520

3.840

7.680

2.089

184.320

7.680

13.440

5.760

9.600

13.440

17.280

Total

• Gastos de estructura

En el siguiente cuadro se detallan los gastos fijos de estructura de cada período. (Cuadro 3.22) • Gastos financieros

Los gastos financieros mensuales se calculan según un porcentaje sobre las ventas mensuales. (Cuadro 3.23) • Gastos de amortización

Para calcular este tipo de gastos la amortización media aplicada es del 20%. Calculamos de forma mensual los gastos de amortización. (Cuadro 3.24 y cuadro 3.25) Resumen de cómo estos datos se resumen en un Excel y van a parar al presupuesto del caso práctico 1 de la EMPRESA INDUSTRIAL: Ingresos y gastos presupuestados resumidos aquí

88

Hoja de presupuesto que utilizaremos para hacer el mismo

Enunciado del caso práctico de una empresa de fabricación...

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

89

300

150

Mensajeros

Servicios de profesionales

500

100

400

250

4.100

7.900

Otros suministros

Comunicaciones

Material de oficina

Varios

Tributos

Total

2.000

100

Reparaciones y conservación

Seguros

50

Alquileres

1

50

7.900

4.100

250

400

100

500

2.000

150

300

100

2

50

7.900

4.100

250

400

100

500

2.000

150

300

100

3

7.900

4.100

250

400

100

500

2.000

150

300

100

50

4

50

7.900

4.100

250

400

100

500

2.000

150

300

100

5

50

7.900

4.100

250

400

100

500

2.000

150

300

100

6

50

7.900

4.100

250

400

100

500

2.000

150

300

100

7

50

7.900

4.100

250

400

100

500

2.000

150

300

100

8

50

7.900

4.100

250

400

100

500

2.000

150

300

100

9

Cuadro 3.22: Detalle de los gastos de estructura mensuales

7.900

4.100

250

400

100

500

2.000

150

300

100

50

10

7.900

4.100

250

400

100

500

2.000

150

300

100

50

11

7.900

4.100

250

400

100

500

2.000

150

300

100

50

12

94.800

49.200

3.000

4.800

1.200

6.000

24.000

1.800

3.600

1.200

600

Total

90

Enunciado del caso práctico de una empresa de fabricación...

 

Cod_Centro

Financieros

3

4

5

6

7

8

9

10

11

12

4.698

2% 5.220

2% 4.698

2% 3.654

2% 4.698

2% 4.176

2% 3.654

2% 4.176

2% 5.220

2%

Departamento de producción Logística Servicios generales fábrica Comercial Administración Dirección Servicios generales estructura

4100

4200

4900

5000

6100

6200

6900 Total

Directos de fabricación

Centro

50.000

10.750

750

2.500

2.000

7.500

6.500

3.000

17.000

Inmovilizado

8.370

1.800

120

450

300

1.200

1.000

500

3.000

Amortizaciones Año anterior

1.630

350

30

50

100

300

300

100

400

Amortizaciones Altas

2% 4.698

10.000

2.150

150

500

400

1.500

1.300

600

3.400

Total anual

Cuadro 3.24: Detalle de los activos actuales y las nuevas inversiones, así como amortizaciones

3.132

2%

Total mes

4.176

2%

156.600 234.900 261.000 234.900 182.700 234.900 208.800 182.700 208.800 261.000 234.900 208.800

2

3000

% Gastos financieros

Total ventas

1

Cuadro 3.23: Detalle de los gastos financieros mensuales

833

179

13

42

33

125

108

50

283

52.200

2%

2.610.000

Total

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

91

283 50 108 125 33 42 13 179

833

Directos de fabricación

Departamento de producción

Logística

Servicios generales fábrica

Comercial

Administración

Dirección

Servicios generales estructura

Total

1

833

179

13

42

33

125

108

50

283

2

833

179

13

42

33

125

108

50

283

3

833

179

13

42

33

125

108

50

283

4

833

179

13

42

33

125

108

50

283

5

833

179

13

42

33

125

108

50

283

6

833

179

13

42

33

125

108

50

283

7

833

179

13

42

33

125

108

50

283

8

833

179

13

42

33

125

108

50

283

9

Cuadro 3.25: Detalle de los gastos de amortización prorrateados por mes

833

179

13

42

33

125

108

50

283

10

833

179

13

42

33

125

108

50

283

11

833

179

13

42

33

125

108

50

283

12

10.000

2.150

150

500

400

1.500

1.300

600

3.400

Total

92

Enunciado del caso práctico de una empresa de fabricación...

4 Confección del presupuesto: ingresos, gastos presupuestados y reales

Objetivos del capítulo

• Aprender las técnicas necesarias para realizar el presupuesto de ingresos y gastos por meses para poder analizar la rentabilidad y el resultado de la empresa. • Obtener los datos de los diferentes programas de gestión que permiten generar los ingresos y gastos reales por mes, que servirán de base al análisis de desviaciones acumulado o por mes.

93

4.1 Confección de informes para los ingresos y gastos presupuestados Situación de los datos del ejercicio:

El fichero de Excel que contiene los datos del capítulo anterior es: 1_ EMPRESA_INDUSTRIAL. En este Excel se plasma toda la información del presupuesto que vamos a utilizar en este caso práctico. • Utilización del archivo de excel: 1_1_CALCULO_PR_CENTROS_CP1

Una vez calculados los datos del presupuesto que tenemos en 1_EMPRESA_INDUSTRIAL, copiamos los datos al fichero Excel siguiente: 1_1_CALCULO_PR_CENTROS_CP1, que es el que utilizaremos para nuestro modelo. Los datos a utilizar son los siguientes:

PRESUPUESTO_MES: contiene los datos de número de cuenta, nombre de la cuenta, código del centro de coste, importes para cada mes.

94

No_Cta

Campo numérico

Nombre_Cta

Campo de texto

C_Centro

Campo numérico o alfanumérico 1

Importes

2

Importes

3

Importes

4

Importes

5

Importes

Confección del presupuesto: ingresos, gastos presupuestados y reales

Total

6

Importes

7

Importes

8

Importes

9

Importes

10

Importes

11

Importes

12

Importes Suma total de importes por mes

Ejemplo de esta hoja:

• Estas hojas están protegidas, excepto las celdas donde debemos poner los importes correspondientes. • Se recomienda seguir las características de los campos. • Esta hoja se debe alimentar de otros cálculos realizados previamente. • En nuestro ejemplo utilizamos los importes de ingresos con signo positivo y los de gastos con signo negativo.

CONTROL: contiene los datos de la hoja anterior PRESUPUESTO_ MES, que mediante una tabla dinámica deben actualizar y verificar el resultado final. Es una tabla dinámica de consulta y verificación.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

95

Esquema resumen de lo realizado hasta el momento y cómo se relaciona con el paso del apartado siguiente: ARCHIVOS ORIGEN Cálculos del presupuesto ARCHIVOS DESTINO

96

ARCHIVOS DESTINO

Confección del presupuesto: ingresos, gastos presupuestados y reales

4.2 Confección de informes para los ingresos y gastos reales Este proceso nos debe permitir obtener los diferentes importes por cuentas y meses.

Aquí se irán incorporando los datos iniciales de nuestro programa contable mediante el balance de sumas y saldos, por cuentas, centros y meses. Estas plantillas sólo se deben utilizar en caso de que se quieran trasladar los datos del balance de sumas y saldos del programa contable cada mes al modelo de control presupuestario directamente, y como se puede compobar, se cambia el signo. Otra opción es obtener estos datos directamente, pero siempre hay que recordar que los ingresos son positivos y los gastos negativos.

Este fichero contiene los siguientes apartados:

• Descripción de las pestañas 1 a 12

Descripción y contenido: No_Cta

Número de cuenta

Nombre_Cta

Descripción de cuenta

C_Centro

Código del centro

Importe

Importes, en este caso siempre los gastos son positivos (saldo inicial deudor) y los ingresos son negativos (saldo acreedor)

NOTA: ES IMPORTANTE MANTENER EL TEMA DEL SIGNO. LOS DATOS IMPORTADOS AL DEBE = GASTO POSITIVO Y AL HABER = INGRESO NEGATIVO, ESTO LO TRANSFORMAMOS AL REVÉS EN ESTA PLANTILLA.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

97

Ejemplo de esta hoja: pestaña 1.

• Esta plantilla sirve para cambiar los signos de los balances de sumas y saldos de su programa contable para que queden con saldo negativo los gastos. • Debe verificar que todos los datos mensuales, una vez sumados, le proporcionan el resultado final, ya que el modelo funciona por meses y si se modifica un mes anterior cambian los datos.

Aquí obtedremos el resumen de los importes reales calculados en cada período. Estos importes sirven para copiar y pegar en otro archivo y poder así hacer el análisis comparativo. A continuación se muestra un ejemplo:

98

Confección del presupuesto: ingresos, gastos presupuestados y reales

NOTA: RECORDAR QUE ESTA PESTAÑA DE IMPORTES_REALES ES UNA TABLA DINÁMICA, Y QUE SU ACTUALIZACIÓN SE REALIZA MEDIANTE EL CURSOR SOBRE LA TABLA Y CON EL BOTÓN DERECHO DEL RATÓN PULSAR SOBRE ACTUALIZAR.

CONTROL: contiene los datos de la hoja anterior, IMPORTES_REALES, que mediante una tabla dinámica se deben actualizar y verificar el resultado final. Es una tabla dinámica de consulta y verificación. Se debe verificar que el resultado final es el correcto según el mes que nos encontremos. A continuación vemos un ejemplo:

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

99

Esquema resumen de lo realizado hasta el momento y cómo se relaciona con el paso del capítulo siguiente. Se adjunta el fichero excel de origen y la pestaña correspondiente: ARCHIVOS ORIGEN

IMPORTES_REALES

ARCHIVOS DESTINO

RE

NOTA: RECORDAR QUE SIEMPRE HAY QUE VERIFICAR LOS SALDOS TOTALES REALES POR SI HAY CAMBIOS EN LOS PERIODOS ANTERIORES Y VOLVERLOS A ACTUALIZAR SI ES NECESARIO.

100

Confección del presupuesto: ingresos, gastos presupuestados y reales

5 Análisis de desviaciones en ingresos, en gastos y en cuentas de explotación

Objetivos del capítulo

• Obtener los diferentes informes de desviaciones por mes acumuladas entre los ingresos y gastos reales y los presupuestados. • Confeccionar las diferentes cuentas de explotación en el formato que precise de forma automática y que expresen el análisis de las desviaciones mensuales y acumuladas. • Poder confeccionar, mediante los informes de tablas dinámicas, innumerables informes con desviaciones, cuadros evolutivos, gráficos e indicadores.

101

5.1 Confección del análisis de desviaciones en ingresos y gastos En este apartado vamos a utilizar este excel: 1_3_DATOS_PR_RE_ CENTROS_CP1. Su contenido es el siguiente:

De forma resumida tenemos los siguientes apartados: • DATOS: pestaña que nos indica los datos iniciales a poner en el modelo:

• Las siguientes pestañas incorporan los planes de cuentas y la tabla de centros de coste a confeccionar para poder efectuar los diferentes análisis: Pestaña 1D:

Pestaña 2D:

102

Análisis de desviaciones en ingresos, en gastos y en cuentas de explotación

Pestaña 3D:

Pestaña 4D:

NOTA: ESTAS PESTAÑAS SON OPTATIVAS PERO SE RECOMIENDA SIEMPRE RELLENAR LAS DE 1D A 3D.

• PR: aquí se introducen los datos presupuestados que provienen del presupuesto confeccionado en la hoja anterior: 1_1_CALCULO_ PR_CENTROS_CP1.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

103

• RE: aquí se introducen los datos reales que provienen del presupuesto confeccionado en la hoja anterior: 1_2_PLANTILLA_CUENTAS_ CENTROS_CP1.

Las pestañas siguientes contienen: • TD1: importes presupuestados y reales a nivel de venta de cuenta de tres dígitos. • TD2: importes presupuestados y reales centros de coste ingreso. • TD3: evolución de datos presupuestados por meses. • TD4: evolución de datos reales por meses. • En la pestaña TD5 de 1_3_DATOS_PR_RE_CENTROS_CP1: evolución de las desviaciones entre los datos reales y el presupuesto por cuentas contables a nivel 3.

104

Análisis de desviaciones en ingresos, en gastos y en cuentas de explotación

• En la pestaña TD6 de 1_3_DATOS_PR_RE_CENTROS_CP1: evolución de las desviaciones entre los datos reales y el presupuesto por cuentas contables a máximo nivel. Evolución de desviaciones por cuentas

• En la pestaña TD7 de 1_3_DATOS_PR_RE_CENTROS_CP1: evolución de las desviaciones entre los datos reales y el presupuesto por centros. Evolución de desviaciones por centros

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

105

5.2 Confección de las cuentas de explotación y análisis de desviaciones Una vez confeccionado el apartado anterior y obtenidos los datos de análisis, podemos ir al paso siguiente, que es configurar los diferentes estados financieros. A título de ejemplo veremos los siguientes: • Pestaña TD1, situada en 1_5_RESULTADOS_PR_RE_CENTROS_CP1, aquí copiaremos la tabla dinámica del Excel anterior y vincularemos mediante fórmulas los datos. Proceso a realizar: Copiar la tabla dinámica de la hoja Excel 1_3_DATOS_PR_RE_CENTROS_CP1, pestaña TD1, en el Excel 1_5_RESULTADOS_PR_ RE_CENTROS_CP1, pestaña TD1. Excel

1_3_DATOS_PR_ RE_CENTROS_CP1

COPIAR EN

1_5_RESULTADOS_PR_RE_CENTROS_CP1

Pestaña

TD1

COPIAR EN

TD1

En el Excel 1_5_RESULTADOS_PR_RE_CENTROS_CP1, pestaña TD1, tenemos la tabla dinámica a la izquierda, que alimenta las cuentas de explotación que tenemos a la derecha, este análisis es por cuentas contables (cuadro 5.2.1). En el Excel 1_5_RESULTADOS_PR_RE_CENTROS_CP1, pestaña TD2, tenemos la tabla dinámica a la izquierda, que alimenta las cuentas de explotación que tenemos a la derecha por centros de costes e ingresos (cuadro 5.2.2).

106

Análisis de desviaciones en ingresos, en gastos y en cuentas de explotación

Cuadro 5.2.1 Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

107

108

Análisis de desviaciones en ingresos, en gastos y en cuentas de explotación

NOTA: RECORDAR QUE ESTAMOS UTILIZANDO LAS TABLAS DINÁMICAS QUE NOS PERMITEN ACTUALIZAR Y FILTRAR LOS DATOS, NOS PONEMOS ENCIMA DE LA TABLA DINÁMICA, BOTÓN DERECHO DEL RATÓN Y ACTUALIZAR PARA OBTENER LOS ÚLTIMOS RESULTADOS.

Cuadro 5.2.2

RESUMEN: Confección del presupuesto 1_1_CALCULO_PR_ CENTROS_CP1



1_3_DATOS_PR_RE_ CENTROS_CP1 Importamos o vinculamos los datos presupuestados en la pestaña PR

Estos importes los enviamos a la hoja de proceso del presupuesto y los datos reales para obtener las desviaciones Cálculo de los saldos reales 1_2_PLANTILLA_ CUENTAS_CENTROS_CP1



 1_5_RESULTADOS_PR_ RE_CENTROS_CP1 Obtención de las diferenes cuentas de explotación hechas a medida por cada usuario

1_3_DATOS_PR_RE_ CENTROS_CP1 Importamos o vinculamos los datos reales en la pestaña RE

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel



109

110

Análisis de desviaciones en ingresos, en gastos y en cuentas de explotación

6 Realización del presupuesto de tesorería

Objetivos del capítulo

• Confección a partir de los ingresos los cobros correspondientes según plazos de cobro. • Confección a partir de los gastos los pagos correspondientes según el presupuesto de pagos. • Obtener los cobros y pagos relacionados con inversiones, impuestos, préstamos y otros conceptos. • Obtener un presupuesto de tesorería provisional para analizar la viabilidad financiera de una organización.

111

6.1 Enunciado del caso práctico: cobros y pagos previstos Para poder efectuar esta parte del caso práctico vamos a utilizar los datos del presupuesto de capítulos anteriores como punto de partida, es decir, los ingresos y gastos generan cobros y pagos, pero hay otros conceptos a tener en cuenta, como son los importes a cobrar y pagar pendientes, la financiación a obtener o bien las inversiones a realizar. Esto poco a poco lo iremos amortizando en este ejercicio. Pasos a realizar previamente: Confección del presupuesto 1_1_CALCULO_PR_ CENTROS_CP1



1_3_DATOS_PR_RE_ CENTROS_CP1 Importamos o vinculamos los datos presupuestado en la pestaña PR

 1_4_PR_TESORERIA_ CENTROS_CP1 Vinculamos o copiamos los datos del presupuesto con el Excel de gestión de la tesorería

Estos importes los enviamos a la hoja de proceso del presupuesto y los datos reales para obtener las desviaciones

Nos situamos en el Excel 1_4_PR_TESORERIA_CENTROS_CP1

Primero copiaremos los datos en las siguientes pestañas: • PR_INGRES

Copiamos los ingresos presupuestados del Excel anterior: 1_3_DATOS_ PR_RE_CENTROS_CP1, pestaña PR

112

Realización del presupuesto de tesorería

• PR_GASTO

Copiamos los gastos presupuestados del Excel anterior: 1_3_DATOS_ PR_RE_CENTROS_CP1, pestaña PR

NOTA: RECORDAR QUE LOS INGRESOS/COBROS VAN EN POSITIVO Y LOS GASTOS/ PAGOS EN NEGATIVO, Y QUE PARA LOS GASTOS E INGRESOS QUE NO SON COBROS NI PAGOS HAY QUE PONER UN PLAZO DE PAGO O COBRO DEL 0%

6.2 Confección del presupuesto de tesorería Una vez realizado el paso anterior se debe de trabajar con las anteriores pestañas para ir completando los datos. • DATOS1

Verificamos que todos estos datos están introducidos y adaptados a nuestra empresa.

• Se introducen los datos de la empresa. • Se ponen los tipos de cuenta según el concepto de impuestos. • Se introducen los conceptos de origen de cobros y pagos que permiten después analizar los cobros y pagos de una forma más ágil.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

113

• DATOS2

Se deben ir introduciendo las cuentas con número y descripción, según se necesiten en las diferentes partes del presupuesto de tesorería:

• IN_cobro

Aquí, para cada cuenta de cobro, se debe poner la forma de cobro. En nuestro ejemplo se cobra al mes siguiente, luego el 50% al mes siguiente y el resto del 50% al siguiente, y por último se cobra a los dos meses, o sea, a 60 días a partir del primer mes. Se debe poner también:

El IVA que se introduce es por cuenta, y se debe elegir el porcentaje medio adecuado. También se puede poner si la declaración es mensual o trimestral para que calcule el pago correspondiente en el período siguiente siempre, si dejamos en blanco este campo será mensual. • GT_pago

Aquí, para cada cuenta de pago, pondremos los plazos correspondientes según el origen del pago:

114

Realización del presupuesto de tesorería

El IVA que se introduce es por cuenta, y se debe elegir el porcentaje medio adecuado.También se puede poner si la declaración es mensual o trimestral para que calcule el pago correspondiente en el período siguiente siempre, si dejamos en blanco este campo será mensual, en según qué cuentas se debe poner el porcentaje de IRPF que toque por su origen. NOTA: RECORDAR QUE EN EL PRESUPUESTO TENEMOS EL SALARIO BRUTO DE LOS TRABAJADORES, POR TANTO, EN CASO DE QUE EXISTA IRPF, SIEMPRE DEBEMOS QUITAR DEL PAGO INICIAL DE PERSONAL LA PARTE DE IRPF Y PONER LUEGO EN IRPF ESA PARTE A PAGAR, POR TANTO, NUNCA SE PAGA EL 100% DEL GASTO QUE TIENE EL PRESUPUESTO EN EL CASO DE CONCEPTOS QUE LLEVEN INCORPORADO EL IRPF.

• OTROS_COBROS

En este caso iremos poniendo todos aquellos cobros que sean manuales, es decir, que su origen no proviene del presupuesto sino que es de otros conceptos como deudores, financiaciones, etcétera.

• OTROS_PAGOS

En este caso iremos poniendo todos aquellos pagos que sean manuales, es decir, que su origen no proviene del presupuesto sino que es de otros conceptos como acreedores, alquileres, etcétera.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

115

• INVERSIONES

En este apartado podemos poner todas las inversiones necesarias como pagos, su IVA correspondiente y el plazo del IVA. En este caso se trata de pagos, por tanto, es importante tener presente el volumen de las inversiones y su plazo de pago.

Importe de las nuevas inversiones del año según el caso práctico detalladas en el capítulo 3. Cod_ Centro

Centro 3000

Directos de fabricación

400

4100

Departamento de producción

100

4200

Logística

300

4900

Servicios generales fábrica

300

5000

Comercial

100

6100

Administración

50

6200

Dirección

30

6900

Servicios generales estructura Total

116

Altas

350 1.630

Realización del presupuesto de tesorería

6.3 Análisis del presupuesto de tesorería Antes de pasar a la siguiente fase de análisis del presupuesto de tesorería se deberá verificar que todas estas pestañas de 1_4_PR_TESORERIA_ CENTROS_CP1 están cumplimentadas correctamente, de lo contrario el resultado no será el adecuado. PESTAÑAS DE EXCEL

DESCRIPCIÓN

DATOS1

Aquí se introducen los datos necesarios para gestionar los pagos y cobros de la tesorería.

DATOS2

Aquí se introducen los datos necesarios para gestionar las cuentas de pagos y cobros nuevas.

PR_INGRES

Se importan o vinculan sólo los ingresos del presupuesto.

PR_GASTO

Se importan o vinculan sólo los gastos del presupuesto.

IN_cobro

Introducción de tipos de IVA y plazos de cobro según el tipo de cuenta de ingresos.

GT_pago

Introducción de tipos de IVA, IRPF y plazos de cobro según el tipo de cuenta de gastos.

OTROS_COBROS

Posibilidad de introducir otros cobros de forma manual.

OTROS_PAGOS

Posibilidad de introducir otros pagos de forma manual.

INVERSIONES

Pagos de inversiones por adquisición de inmovilizado u otro tipo.

REVISADO SÍ/NO

Las siguientes pestañas son las que vamos a utilizar: PESTAÑAS DE EXCEL

DESCRIPCIÓN

PT_1

Obtención de una primera lista de cobros y pagos desglosada en tablas dinámicas.

PT_2

Obtención de una segunda lista de cobros y pagos desglosada en tablas dinámicas.

CONTROL_ IVA

Permite analizar los saldos de IVA por si hay que efectuar algún ajuste al compensar el IVA.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

117

• PT_1

Aquí desglosamos todos los datos necesarios para calcular el presupuesto de tesorería resumido. Esta pestaña nos proporciona todos los importes totalmente desglosados, por lo que se puede verificar cualquier movimiento. Es importante tener en cuenta los siguientes aspectos: − Al inicio de la hoja encontramos el resumen por períodos de los saldos de tesorería:

− Es una tabla dinámica donde no se aplican filtros, por tanto salen todos los importes, − Su actualización se realiza estando encima de ella, con botón derecho del ratón pulsar sobre actualizar. Presupuesto de tesorería ampliado y desglosado:

NOTA: RECORDAR QUE ESTAMOS UTILIZANDO LAS TABLAS DINÁMICAS, QUE NOS PERMITEN ACTUALIZAR Y FILTRAR LOS DATOS, NOS PONEMOS ENCIMA DE LA TABLA DINÁMICA, BOTÓN DERECHO DEL RATÓN Y PULSAR SOBRE ACTUALIZAR.

118

Realización del presupuesto de tesorería

• PT_2

Esta hoja nos va a permitir analizar los resultados de los cobros y pagos de forma resumida, tal como vemos a continuación. Aquí observamos como la tesorería a partir de julio pasa a ser negativa, por tanto hay que actuar de alguna forma para evitar los problemas.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

119

Presupuesto de tesorería mensual resumido, por cobros y pagos, totales y saldo final.

120

Realización del presupuesto de tesorería

Presupuesto de tesorería mensual resumido, por meses y saldo final.

• CONTROL_IVA

Aquí controlaremos, una vez realizado el presupuesto de tesorería, que no existan cantidades positivas a cobrar de IVA, que tendremos lógicamente que ajustar a mano, siendo un pago de un período y un cobro del siguiente para compensar. Este proceso es necesario, ya que el sistema no lo puede hacer de forma automática y se realiza al final de todo el proceso.

SI ESTE SALDO ES POSITIVO NO SE DEBE CONSIDERAR UN COBRO SINO QUE HAY QUE AJUSTARLO EN ESTE PERÍODO Y EN EL SIGUIENTE

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

121

122

Realización del presupuesto de tesorería

7 Caso práctico para una empresa de transporte mediante cuentas contables

Objetivos del capítulo

• Saber qué información obtener y en qué formato para confeccionar los ingresos y gastos presupuestados y reales como punto de partida del presupuesto y el análisis de desviaciones. • Diseñar el sistema automático de confección de informes de desviaciones y cuentas de explotación con datos acumulados y mensuales. • Realizar el presupuesto de tesorería previsional a partir de los ingresos y gastos presupuestados y añadir los cobros y pagos necesarios para obtener la situación de tesorería mensual.

123

7.1 Datos iniciales: ingresos y gastos presupuestados y reales En este caso práctico nos proporcionan, en primer lugar, los importes del presupuesto y los importes reales del año 2011 de 12 meses para efectuar el análisis correspondiente siguiendo todo el proceso desde su inicio hasta su final. A continuación vemos los pasos a realizar: Los datos a utilizar son los que presentamos a continuación:

Primero: Obtener el presupuesto y copiarlo/vincularlo:

Datos de origen que nos proporcionan del presupuesto

Copiar en la pestaña: PRESUPUESTO_MES

Copiar los datos anteriores en la pestaña: PR

• En el archivo de Excel 2_1_CALCULO_PR_CUENTA_CP2, en la pestaña PRESUPUESTO_MES, tenemos los datos presupuestados que se deben poder poner en el siguiente Excel: 2_3_DATOS_PR_ RE_CUENTA_CP2, en la pestaña PR. Pestaña: PRESUPUESTO MES

• En el archivo de Excel 2_3_DATOS_PR_RE_CUENTA_CP2, en la pestaña PR, tendremos los datos anteriores: 124

Caso práctico para una empresa de transporte mediante cuentas contables

NOTA: en este momento ya podemos analizar datos del presupuesto en este archivo, incluso hacer la cuenta de explotación presupuestada, tal como se indica en el apartado siguiente.

Segundo: Obtener los datos reales y copiarlos/vincularlos:

Copiar en la pestaña: IMPORTES_REALES

Copiar los datos anteriores en la pestaña: RE

• En el archivo de Excel 2_2_PLANTILLA_CUENTAS_CP2, en la pestaña DATOS_REALES, tenemos los datos reales que se deben poder introducir en el siguiente Excel: 2_3_DATOS_PR_RE_CUENTA_ CP2, en la pestaña RE. Pestaña: DATOS_REALES

• En el archivo de Excel 2_3_DATOS_PR_RE_CUENTA_CP2, en la pestaña RE, tendremos los datos anteriores:

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

125

7.2 Estados financieros y análisis de desviaciones Una vez obtenidos los datos anteriores podemos, desde este Excel: 2_3_ DATOS_PR_RE_CUENTA_CP2, realizar los siguientes informes: • Pestaña TD1

Recuerde que se puede filtrar por mes la información, uno o varios meses.

• Pestaña TD2

Evolución de importes presupuestados por meses con cuentas de tres dígitos: 126

Caso práctico para una empresa de transporte mediante cuentas contables

• Pestaña TD3

Evolución de importes reales por meses con cuentas de tres dígitos:

NOTA: RECORDAR QUE ESTAMOS UTILIZANDO LAS TABLAS DINÁMICAS, QUE NOS PERMITEN ACTUALIZAR Y FILTRAR LOS DATOS. NOS PONEMOS ENCIMA DE LA TABLA DINÁMICA CON EL CURSOR Y APRETAMOS BOTÓN DERECHO DEL RATÓN Y PULSAMOS SOBRE ACTUALIZAR.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

127

• Pestaña TD4

Evolución de desviaciones por cuentas a tres dígitos:

• Pestaña TD5

Evolución de desviaciones por cuentas al máximo nivel: 128

Caso práctico para una empresa de transporte mediante cuentas contables

Para la obtención de estados financieros iremos al archivo siguiente: 2_5_ RESULTADOS_PR_RE_CUENTAS_CP2. Desde el anterior Excel, 2_3_DATOS_PR_RE_CUENTA_CP2, copiaremos la tabla dinámica que tenemos en TD1:

NOTA: RECORDAR QUE ESTAMOS UTILIZANDO LAS TABLAS DINÁMICAS, QUE NOS PERMITEN ACTUALIZAR Y FILTRAR LOS DATOS. NOS PONEMOS ENCIMA DE LA TABLA DINÁMICA CON EL CURSOR Y APRETAMOS BOTÓN DERECHO DEL RATÓN Y PULSAMOS SOBRE ACTUALIZAR.

En este Excel, 2_5_RESULTADOS_PR_RE_CUENTAS_CP2, y en la pestaña TD1: Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

129

130

Caso práctico para una empresa de transporte mediante cuentas contables

Se quedan vinculados los datos entre esta tabla dinámica y la cuenta de explotación:

Mediante esta cuenta de explotación y las tablas dinámicas ya se puede hacer el análisis de desviaciones. Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

131

7.3 Presupuesto de tesorería Para realizar el proceso de análisis del presupuesto de tesorería previsional tenemos que partir de los datos presupuestados, tal como se indica a continuación: Datos presupuestados obtener los ingresos y gastos a poner en el presupuesto

Se introducen aquí los datos anteriores en las pestañas de: PR_INGRES PR_GASTO

Datos de 2_3_DATOS_PR_RE_CUENTA_CP2 pestaña PR: se copian primero los gastos y luego los ingresos, o viceversa.

• PR_INGRES

Aquí ponemos los ingresos como cobros desde el presupuesto general:

132

Caso práctico para una empresa de transporte mediante cuentas contables

• PR_GASTO

Aquí ponemos los gastos como pagos desde el presupuesto general:

• IN_cobros

Introducimos los plazos de cobro:

• IN_pagos

Introducimos los plazos de pago de los conceptos que llevan plazo de los que no se dejan en blanco:

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

133

• OTROS_COBROS

Se ponen en esta pestaña los cobros pendientes:

• OTROS_PAGOS

Se introducen en esta pestaña los pagos pendientes:

• INVERSIONES

Se introducen los pagos por inversiones pendientes:

Una vez revisados todos los aspectos podemos actualizar la tabla dinámica de la pestaña PT_1:

NOTA: RECORDAR QUE ESTAMOS UTILIZANDO LAS TABLAS DINÁMICAS, QUE NOS PERMITEN ACTUALIZAR Y FILTRAR LOS DATOS. NOS PONEMOS ENCIMA DE LA TABLA DINÁMICA CON EL CURSOR Y APRETAMOS BOTÓN DERECHO DEL RATÓN Y PULSAMOS SOBRE ACTUALIZAR.

134

Caso práctico para una empresa de transporte mediante cuentas contables

• Pestaña: PT_1

• Pestaña: PT_2

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

135

Presupuesto de tesorería mensual resumido por cobros y pagos.

136

Caso práctico para una empresa de transporte mediante cuentas contables

Presupuesto de tesorería mensual resumido por saldo final.

• CONTROL_IVA

Tabla para verificar la situación del IVA si existe la posibilidad de compensación.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

137

138

Caso práctico para una empresa de transporte mediante cuentas contables

Parte 3 Confección de presupuestos por objetivos con diferentes tipos de variables

140

Caso práctico para una empresa de transporte mediante cuentas contables

8

Caso práctico: confeccionar la previsión de ingresos por ventas y por meses según objetivos por familias de productos

Objetivos del capítulo

• Conocer cómo definir unos objetivos previos para las ventas por familias. • Realizar el seguimiento de los objetivos previstos y reales mediante el análisis de desviaciones. • Diseñar informes que analicen las desviaciones por mes y acumulado, gráficos e indicadores.

141

En este apartado vamos a utilizar los siguientes datos:

Se trata de fijar previamente unos objetivos, por ejemplo, de ventas por familia de productos, tal como explicamos a continuación. Se trata de una empresa que posee tres tipos de familias de productos y/o servicios: ID_Concepto

Des_Concepto 1

Moldes

2

Matrices

3

Mantenimientos

4

Reparaciones

Para cada una de ellas obtendremos las ventas por meses previstas. A continuación explicamos el contenido de este archivo Excel: 3_1_ FIJAR_OBJETIVOS_VENTAS_FAMILIAS_CP_3. PESTAÑAS

DESCRIPCIÓN

DATOS

Datos iniciales para poder identificar la empresa, tipo de análisis, versión y año

PR

Información sobre las ventas previstas por familia y por meses

RE

Información sobre las ventas reales por familia y por meses

TD1

Análisis de desviaciones entre objetivos reales y previstos

TD2

Análisis de la evolución de los objetivos previstos por mes

TD2

Análisis de la evolución de los objetivos reales por mes

A continuación ponemos los diferente tipos de datos de las pestañas y los pasos a realizar:

142

Caso práctico: confeccionar la previsión de ingresos por ventas y por meses según objetivos...

• Introducir los datos iniciales en DATOS:

• Introducir los datos previstos en PR:

• Introducir los datos previstos en RE.

A medida que se van produciendo los datos podemos ir comparando los resultados para cada mes:

NOTA: ES IMPORTANTE QUE TENGAMOS EN CUENTA QUE ESTOS DATOS SALEN DE NUESTRO SISTEMA DE GESTIÓN Y QUE IREMOS ANALIZANDO LOS DATOS MES A MES A MEDIDA QUE SE VAN PRODUCIENDO.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

143

• TD1

Análisis de ventas por familia. Nos muestra la diferencia entre los importes reales y los previstos mediante el análisis de desviaciones. Podemos seleccionar el mes o los meses que estemos analizando en los filtros.

NOTA: ES IMPORTANTE QUE TENGAMOS EN CUENTA QUE ESTOS DATOS SALEN DE NUESTRO SISTEMA DE GESTIÓN Y QUE IREMOS ANALIZANDO LOS DATOS MES A MES A MEDIDA QUE SE VAN PRODUCIENDO.

NOTA: RECORDAR SIEMPRE QUE SE DEBEN ACTUALIZAR LAS TABLAS DINÁMICAS PARA OBTENER LOS ÚLTIMOS RESULTADOS.

144

Caso práctico: confeccionar la previsión de ingresos por ventas y por meses según objetivos...

• TD2

Evolución de objetivos de ventas por familias, por meses:

• TD3

Evolución de datos reales de ventas por familias, por meses:

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

145

146

Caso práctico: confeccionar la previsión de ingresos por ventas y por meses según objetivos...

9

Caso práctico: confeccionar la previsión de ingresos por ventas y por meses según objetivos por vendedores

Objetivos del capítulo

• Conocer cómo definir unos objetivos previos para las ventas por vendedores. • Realizar el seguimiento de los objetivos previstos y reales mediante el análisis de desviaciones. • Diseñar informes que analicen las desviaciones por mes y acumulado, gráficos e indicadores.

147

En este apartado vamos a utilizar los siguientes datos:

Se trata de fijar previamente unos objetivos, por ejemplo, de ventas por vendedores, tal como explicamos a continuación. Se trata de una empresa que posee los siguientes vendedores: ID_Concepto

Des_Concepto

1002

Juan Pérez

1003

Mario Camus

1004

Javier Ponce

1005

Daniel Vara

1006

José Gómez

1007

Ernesto Segura

1008

Miguel López

1009

Andrés Martínez

1010

Iker Arnillas

Para cada uno de ellos obtendremos las ventas por meses previstas. A continuación explicamos el contenido de este archivo Excel: 3_2_FIJAR_OBJETIVOS_VENTAS_VENDEDORES_CP_3. PESTAÑAS

DESCRIPCIÓN

DATOS

Datos iniciales para poder identificar la empresa, tipo de análisis, versión y año

PR

Información sobre las ventas previstas por vendedor y por meses

RE

Información sobre las ventas reales por vendedor y por meses

TD1

Análisis de desviaciones entre objetivos reales y previstos

TD2

Análisis de la evolución de los objetivos previstos por mes

TD2

Análisis de la evolución de los objetivos reales por mes

A continuación ponemos los diferentes tipos de datos de las pestañas y los pasos a realizar.

148

Caso práctico: confeccionar la previsión de ingresos por ventas y por meses según objetivos...

• Introducir los datos iniciales en DATOS:

• Introducir los datos previstos en PR:

• Introducir los datos previstos en RE.

A medida que se van produciendo los datos podemos ir comparando los resultados para cada mes:

NOTA: ES IMPORTANTE QUE TENGAMOS EN CUENTA QUE ESTOS DATOS SALEN DE NUESTRO SISTEMA DE GESTIÓN Y QUE IREMOS ANALIZANDO LOS DATOS MES A MES, A MEDIDA QUE SE VAN PRODUCIENDO.

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

149

• TD1

Análisis de ventas por vendedor. Nos muestra la diferencia entre los importes reales y los previstos mediante el análisis de desviaciones. Podemos seleccionar el mes o los meses que estemos analizando en los filtros.

NOTA: COMO EN EL CASO ANTERIOR SE DEBE ACTUALIZAR LA TABLA DINÁMICA CADA VEZ QUE SE INTRODUZCAN LOS NUEVOS DATOS CADA MES.

150

Caso práctico: confeccionar la previsión de ingresos por ventas y por meses según objetivos...

• TD2

Evolución de objetivos de ventas por vendedor, por meses:

• TD3

Evolución de datos reales de ventas por vendedor, por meses:

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

151

152

Caso práctico: confeccionar la previsión de ingresos por ventas y por meses según objetivos...

Anexo 1 Herramientas de Excel para el modelo

• Desbloqueo de macros en Excel 2007 o 2010

Ir, desde opciones de Excel, a la opción de Centro de confianza:

153

Y seleccionar

Aquí, en la configuración de macros, activar la opción marcada:

154

Anexo 1. Herramientas de Excel para el modelo

Anexo 2 Esquema general del modelo

Modelo en Excel 2003 o Excel 2007 o 2010 Aquí están incluidos los archivos par excel 2003 y versiones posteriones Contenido del modelo: plantillas y ejemplos

155

• Plantilla para el modelo de cuentas contables:

• Plantilla para el modelo de cuentas contables y de centros de coste:

• Plantilla para fijar objetivos:

• Caso práctico de la empresa industrial:

156

Anexo 2: Esquema general del modelo

• Caso práctico de la empresa de servicios de trasporte:

• Casos prácticos para el control de objetivos:

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

157

158

Anexo 2: Esquema general del modelo

Confeccionar y controlar presupuestos y previsiones de tesorería con Excel

159

160

Anexo 2: Esquema general del modelo