confeccionar controlar presupuestos en excel
DESCRIPTION
excelTRANSCRIPT
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel
Luis Muñiz
Confeccionar y controlar presupuestos y previsiones
de tesorería con ExcelAplicación en tablas dinámicas
© Luis Muñiz, 2011
© Profit Editorial, 2011 (www.profiteditorial.com) Profit Editorial I., S.L., Barcelona, 2011
Diseño cubierta: XicArtMaquetación: www.eximpre.com
ISBN: 978-84-15330-55-4Depósito legal: B–Impreso por: Liberdúplex
Impreso en España - Printed in Spain
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.
Dedicatoria:
Una vez más me enfrento a un nuevo reto, dar a conocer una nueva for-ma 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 reali-zar 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 pacien-cia 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íay 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
9
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
Índice
9
10 Índice
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
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 11
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
12 Índice
13
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 constata-ción de que teníamos a todos los actores del sector (fabricantes y distri-buidores) 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én-ticos 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-
Prólogo
14 Prólogo
tiendo muchas horas para conocer con detalle la composición de los campos de las bases de datos, había confeccionado un sistema de infor-mació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 utili-zando 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 mu-chas 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 lanza-miento del proyecto, afianzó nuestro mutuo conocimiento como per-sonas y me permitió ver en Luis otras muchas cualidades profesionales. A destacar de estas últimas, que es un luchador incansable, tiene la men-te 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 conti-nuamente nuevas relaciones profesionales. Tampoco puedo dejar de alu-dir a los muchos correos electrónicos que recibo mencionando las acti-vidades 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 conoci-miento sobre herramientas informáticas, en especial los ERP, CRM y
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 15
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 insospecha-dos, incluso corrigiendo errores de programación. Por lo que he obser-vado, 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 intro-ducció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 necesi-dades 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 infor-mes de tesorería ampliados o reducidos y centros de costes diferenciados.
Es de una gran versatilidad al poder generar copias con múltiples com-posiciones 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 po-sibilidades y potencia de la herramienta, como su uso en benchmarking, y ayudará a directivos y resto de usuarios no habituados a trabajar con apli-caciones sofisticadas, así como profesionales de las finanzas, donde el mo-delo puede ser fácilmente adaptado a complejas necesidades.
16 Prólogo
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 cierta-mente 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ónDirector General Bansabadell RentingDirector Financiación Medio y Largo Plazo, Banc SabadellPresidente del Consejo de Renting de la Asociación Española de Leasing y Renting
17
Es de todos conocida la necesidad de poder prever el futuro de una em-presa 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 ob-jetivos 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 antela-ción. Por último, es necesario también que podamos controlar la evolu-ció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:
• Confeccionarunpresupuestodeingresosygastospormesespararealizar el posterior análisis de desviaciones de una forma rápida y sencilla con Excel y tablas dinámicas.
Introducción*
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 progra-ma y no el detalle del contenido de las ilustraciones.
18 Introducción
• Confeccionarelpresupuestodetesoreríaprevisionalquenosper-mita analizar la viabilidad financiera de la empresa en los próxi-mos 12 meses, y tomar las decisiones oportunas.
• Realizardiferentesanálisisdelasdesviacionesentrelosimportesreales 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 lacomparació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:
• Mejoraentiempoyformalamaneraderealizarsupresupuestoyasí asegura la viabilidad futura de su organización.
• Permitecalcularelpresupuestodetesoreríaprevisional,yestoesde 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.
• Ayudaaconfeccionarlosdiferentesanálisisdedesviacionesconnumerosos informes y las cuentas de explotación adaptándolas a las necesidades de cada organización.
• Conseguirpoderponerlosobjetivosprevistosalosdiferentesresponsables y hacer el seguimiento de una forma fácil y prác-tica.
• Nospermiteutilizaren todos losapartadoselpotencialde lastablas dinámicas en Excel como herramienta de reporting y siste-ma de información.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 19
Esquema del modelo
CONFECCIÓN Y ANÁLISIS DEL PRESUPESTO
CONFECCIONAR EL PRESUPUESTO DE INGRESOS Y GASTOS
•CONFECCIÓNDEINFORMES DE ANÁLISIS DE DESVIACIONES
•CONFECCIÓNDECUENTASDEEXPLOTACIÓN
•CONFECCIÓNDEGRÁFICOSEINDICADORES
OBTENER LOS INGRESOS Y GASTOS REALES
PRESUPUESTO DE TESORERÍA
PREVISIONAL
INGRESOS Y GASTOS PREVISTOS
COBROS Y PAGOS PENDIENTES
•PRESUPUESTOSDETESORERÍA POR MESES
•EVOLUCIÓNDECOBROSYPAGOS POR MESES
•ANÁLISISDELAVIABILIDADFINANCIERA
FIJAR OBJETIVOS
OBTENER LOS OBJETIVOS PREVISTOS
Y
OBTENER LOS RESULTADOS REALES
•CONFECCIÓNDEINFORMESDE DESVIACIONES
•CONFECCIÓNDEGRÁFICOSEINDICADORES
20 Introducción
Contenido del libro
PARTES CAPÍTULOS CONTENIDO
Parte 1: Esquema y contenido del modelo
1. Descripción y contenido del modelo
2. Instalación y utilización del modelo
Esta parte contiene de una forma muy extensa el desarrollo del contenido del modelo y su funcionamiento, así como el proceso de instalación.
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.
4. Confección del presupuesto: ingresos, gastos presupuestados y reales.
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.
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.
Parte 3: Confección de presupuestos por objetivos con diferentes tipos de variables
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.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 21
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ápi-damente 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 cuen-ta 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.
22 Introducción
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.
23
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í en-tenderemos cómo funcionan cada una de ellas y para qué sirven.
Conocer en que partes del libro están explicadas las principales fun-cionalidades
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 funcio-nalidad: 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.
Principales consejos para utilizar este libro
24 Principales consejos para utilizar este libro
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
• Confeccionarelpresupuestopormesesparalascuentascontables y por centros de coste/ingresos.
Plantilla a utilizar: 2_1_CALCULO_PR_CENTROS
• Obtenerdesussistemascontableslosdatosrealespormeses 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
• Introducirlosdiferentesnivelesdecuentascontablesdeuno a cuatro dígitos.
• CopiarenunaplantillaExcelelpresupuestocalculadopara realizar su seguimiento.
• Introducirlaplantilladedatosrealespormesescalculadaen el punto anterior en una hoja Excel para realizar el análisis de desviaciones.
• Obtenermediantetablasdinámicaslosdiferentesanálisisde 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
• Obtenerlascuentasdeexplotaciónpresupuestadasyelanálisis de desviaciones a través de las tablas dinámicas como orígenes de datos.
Plantilla a utilizar: 2_5_RESULTADOS_PR_RE_CENTROS
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 25
Segunda funcionalidad: Confección de las previsiones de tesorería
SECCIÓN DEL LIBRO SECCIÓN DEL LIBRO
1.2.3
Contenido del modelo de cobros y pagos para realizar el presupuesto de tesorería
• Obtenerlosimportespresupuestadosporcuentascontables o centros de coste/ingresos.
Plantilla a utilizar: 2_3_DATOS_PR_RE_CENTROS
• Introducirlosingresosygastospresupuestadosdelapartado anterior para obtener los cobros y pagos.
• Calcularlosdiferentescobros,pagosporotrosconceptosy los pagos por inversiones.
• Obtenerelpresupuestodetesoreríaampliadoyresumidoasí 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 SECCIÓN DEL LIBRO
1.2.4
Contenido del modelo del presupuesto de objetivos para realizar su seguimiento
• Introducirlosobjetivosprevistosdelasvariablesquesenecesitan por ejemplo: previsión de ventas por clientes, vendedores, familias de artículos, etcétera.
• Introducirlosdatosrealesdelosobjetivosanteriorespararealizar e análisis de las desviaciones.
• Obtenerlasdesviacionesentelosobjetivosprevistosyreales por periodos y acumulados.
Plantilla a utilizar: 3_1_FIJAR_OBJETIVOS
26 Principales consejos para utilizar este libro
Esquema y contenido del modelo
Parte 1
29
Objetivos del capítulo
• Conocerelcontenidodelmodelosobreloquenosproporciona: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.
• Permiteconocercómoestructurarmodelodeingresosygastospara 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.
• Ayudaacalcularloscobrosypagospararealizarelpresupuestode tesorería previsional permitiendo que se pueda introducir otro tipo de pagos y cobros como son inversiones, financiación, deudores y acreedores.
• Contieneunaherramientaquenosayudaafijarobjetivosdeventas, 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.
Descripción y contenido del modelo
1
30 Descripción y contenido del modelo
1.1 Descripción del modelo
Este modelo de análisis está compuesto por las siguientes partes:
• Unaprimerapartequenospermiteconfeccionarelpresupuestodeuna 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 rea-lizar tantos tipos de informes como se requiera, sólo depende del tiem-po 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 in-gresos.
• Lasegundapartenospermite,apartirdelosdatosanterioresdelpre-supuesto de ingresos y gastos, calcular el presupuesto de tesorería te-niendo 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.
• Laterceraparteesmuyimportante,yaquesonmuchaslasempresasque necesitan controlar los objetivos de ventas por clientes, vendedo-res, 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.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 31
Esquema y contenido del modelo:
1. PRESUPUESTO DE INGRESOS Y GASTOS
2. PRESUPUESTOS DE TESORERÍA PREVISIONALES
3. PRESUPUESTOS: FIJAR OBJETIVOS
• Cálculodelosingresosygastos previstos por mes y año.
• Confeccióndelesquemadeingresos y gastos reales por mes y año.
• Confeccióndecuentasdeexplotación.
• Seguimientodedesviacionesentre importes reales y previstos.
• Importarlosdatosdeingresos y gastos del presupuesto para calcular los cobros y pagos previstos.
• Introducirloscobrosypagos previstos diferentes a ingresos y gastos.
• Obtenerelpresupuestode tesorería mensual previsional.
• Fijarobjetivosdediferentes tipos por mes y año.
• Realizarelseguimientode objetivos, analizando las desviaciones entre los datos reales y previstos.
Objetivos del modelo
• Nos ayuda a confeccionar deuna forma estándar y disciplinada losdatos del presupuesto.
• Nosaportaherramientasparacalcularlosdatosrealespormesdeunaforma estándar sin importarnos el software de gestión que tengamos.
• Permite,deformamensual,obtenerelcontroldelosdatospresupues-tados y reales mediante el análisis de desviaciones de forma mensual y/o acumulada.
• Ayudaaconfeccionar losdiferentes tiposdecuentasdeexplotaciónpara realizar el análisis de desviaciones mensuales.
• Nospermitecombinardatoscontablescondatosde lacontabilidadanalítica según las necesidades de cada organización.
• AlserenExcel,elmodelopuedeseradaptadoacualquiertipodefor-mato de informe utilizado.
• LosdatosseanalizanyexplotanentablasdinámicasdeExcel,locualpermite una mejor prestación y utilización de los mismos.
• Proporcionaunaprevisióndetesoreríamensualqueelusuariopuedeadaptar a sus necesidades y verificar si el presupuesto de ingresos y gastos es coherente y razonable.
• Permite,unavezcalculadoslosobjetivosdeventauotrotipo,realizarsu seguimiento posterior mediante el análisis de desviaciones.
32 Descripción y contenido del modelo
• Ayudaaconfeccionardiferentestiposdeinformes,gráficoseindica-dores con Excel mediante la utilización de las tablas dinámicas.
• Loscasosprácticosy la teoríanosayudanaentenderelprocesodecá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.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 33
• 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.
34 Descripción y contenido del modelo
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
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 35
• 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 dife-rencia 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 me-ses en un formato determinado. Partiremos del modelo que combina cuentas contables con centros de coste:
36 Descripción y contenido del modelo
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
Total Suma total de los importes para los 12 meses Numérico
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 37
Ejemplo:
Notas a tener en cuenta:
• Estahoja/etiquetasólodebeserrellenadacondatosdesdeA2..O1001,es decir, soporta mil cuentas e importes para cada fila, y por doce meses.
• Lascuentasdegastospuedensernegativasopositivas,ylasdeingresotambié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.
38 Descripción y contenido del modelo
• descripción del archivo excel: 2_2_plantilla_cuentas_centros
Este archivo tiene diferentes etiquetas, y nos permiten introducir los da-tos 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 in-troducir 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 dife-rentes programas de contabilidad, entonces el modelo, de forma automá-tica, hará los cambios correspondientes.
La siguiente pestaña/etiqueta IMPORTES_REALES es una tabla di-ná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:
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 39
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,lapestaña/etiquetadeCONTROLnospermite,comosiem-pre, analizar los resultados obtenidos de los registros contables que vamos introduciendo en esta plantilla de excel:
40 Descripción y contenido del modelo
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 cam-bios 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.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 41
- DescripcióndeetiquetaDATOS:
Aquí podemos introducir los datos iniciales para identificar en qué ver-sió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:
42 Descripción y contenido del modelo
Situación incorrecta:
Situación incorrecta:
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.
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.
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.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 43
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 recordato-rio recomendando que evite el cortar y pegar, ya que puede generar un error en los cálculos cuando tenemos fórmulas vinculadas.
44 Descripción y contenido del modelo
- Descripción de etiquetas de centros de coste o ingreso:
CENTROS
C_Centro Centro_Nombre Control
Número del dígito correspondiente. Este campo es numérico o alfanumérico, pero es recomendable que tenga un determinado orden.
Descripción del centro correspondiente, campo de tipo texto.
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:
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 45
- 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ónEl número de versión introducido anteriormente
Puede ser numérico y alfanumérico
Empresa Nombre de la empresa abreviado Campo texto
AñoAño en el que estamos haciendo el análisis
Importes numéricos
No_CtaNúmero de la cuenta que se esté utilizando
Mejor que sea formato numérico, sin puntos siempre
Nombre_CtaNombre de la cuenta que se esté utilizando
Texto
C_CentroCó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ónEstos campos vendrán automatizados de la etiqueta PR
Puede ser numérico y alfanumérico
EmpresaEstos campos vendrán automatizados de la etiqueta PR
Campo texto
AñoEstos campos vendrán automatizados de la etiqueta PR
Importes numéricos
No_CtaNúmero de la cuenta que se esté utilizando
Mejor que sea formato numérico, sin puntos siempre
Nombre_CtaNombre de la cuenta que se esté utilizando
Texto
C_CentroCó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
46 Descripción y contenido del modelo
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_2_PLANTILLA_CUENTAS_CENTROS
Desde la pestaña o etiqueta:IMPORTES_REALES
2_3_DATOS_PR_RE_CENTROS
Se copia o vincula a la pestaña o etiqueta:
RE
- 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 poten-cial de esta herramienta, pero el usuario puede confeccionar tantos ejem-plos de cuentas como necesite con las funciones de las tablas dinámicas.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 47
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 explota-ción por centros de coste que permita hacer el análisis de desviaciones:
MUY IMPORTANTE: CADA USUARIO PUEDE HACER LOS DIFERENTES INFORMES VINCULANDO LOS DATOSDELASTABLASDINÁMICASCONLASFÓRMULASDEEXCEL,AQUÍSÓLOPONEMOSUNOSEJEMPLOS MUY CONCRETOS.
48 Descripción y contenido del modelo
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:
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 49
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 obte-ner los pagos por impuestos o, en el caso de impuestos a cobrar o com-pensar, 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.
50 Descripción y contenido del modelo
• 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 quetenemosenelarchivode2_3_DATOS_PR_RE_CENTROS,pes-taña de PR:
• pr_Gastos
Aquí se copian o vinculan las cuentas e importes por meses de gastos que te-nemosenelarchivode2_3_DATOS_PR_RE_CENTROS,pestañadePR:
NOTA:SISECOPIANOVINCULANLOSDATOSTENGAPRESENTEQUENOTODOSLOSCONCEPTOSDE INGRESOS Y GASTOS SON AL FINAL COBRABLES O PAGABLES. EN LOS APARTADOS SIGUIENTES EXPLICAREMOS ESTO DE UNA FORMA MÁS AMPLIADA.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 51
• 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, men-sual o trimestral.
Origen_Cobros Nos indica el origen el cobro de forma automática
IVA Indicar si la cuenta lleva un IVA incorporado.
PlazoIndicar si queremos realizar los pagos de impuestos de forma trimestral o mensual según el tipo de cuenta.
C_Centro Nos indica el centro de forma automática.
No_Cta Nos indica el número de cuenta de forma automática.
Nombre_Cta 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 pode-mos 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.
52 Descripción y contenido del modelo
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 ingre-so 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.
Observemosestosdoscasos:enelprimero,sinollegamosal100%nosloindica 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, men-sual o trimestral.
Su funcionamiento es exactamente igual que la anterior opción de ingre-so/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.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 53
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 men-suales 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 ajus-tes 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.
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.
Total Fórmula que totaliza la suma de importes por meses para comparar.
54 Descripción y contenido del modelo
Ejemplo de cobros:
NOTA:RECORDARQUELOSCOBROSSEINTRODUCENENVALORESPOSITIVOS.
• otros_paGos
En este apartado podemos introducir los diferentes tipos de pagos o ajus-tes a realizar de forma manual. Se compone de los mismos campos que el apartado anterior. Veamos un ejemplo a continuación:
NOTA:RECORDARQUELOSPAGOSSEINTRODUCENENVALORESNEGATIVOS.
• inversiones
En este apartado podemos introducir los diferentes tipos de pagos por in-versiones 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.
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.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 55
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.
Total 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 encon-tramos con los saldos iniciales de cada período que acumula el saldo an-terior con el saldo del período actual.
56 Descripción y contenido del modelo
• 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 funciona-miento 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:
RECUERDEQUEELIVASOPORTADOSINOSECONSIDERAUNCOBROADEVOLVERSEDEBEAJUSTARMANUALMENTE.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 57
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 DESCRIPCIÓN
DATOS 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.
58 Descripción y contenido del modelo
• 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 me-ses, pueden ser, por ejemplo, ventas por vendedor, por familia o por clien-tes.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 59
• 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.
60 Descripción y contenido del modelo
61
Objetivos del capítulo
• Conocerelcontenidodelsoftwareylascondicionesdeuso.
• Conocercómoseinstalaelsoftwareylosdiferentestiposdedatos.
• Obtenerunavisióngeneraldelcontenidoyrelacionesentrelosdiferentes tipos de datos del modelo.
Instalación y sistema de utilización del modelo
2
62 Instalación y sistemas de utilización del modelo
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 flexibi-lidad 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 cuan-do 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 soft-ware 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, siem-pre 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.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 63
− 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 gra-tuita 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 ven-ta u otorgarse su autoría total o parcial sin el permiso previo y por escri-to del autor o de la editorial. *Todas las hojas VISIBLES son modifica-bles 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 co-rrespondientes. Una plantilla es una estructura creada para facilitar los cálculos al usuario; sus resultados o formas de cálculo no deben conside-rarse correctos en todos los países y circunstancias. Es el propio usuario quien debe incluir adecuadamente los datos y contenidos adecuados, re-visar que los cálculos y operaciones sean correctos y disponer de los co-nocimientos 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.
64 Instalación y sistemas de utilización del modelo
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á dere-cho 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 funciona-miento del modelo y las diferentes relaciones. En primer lugar se descri-be el contenido:
El directorio principal contiene las plantillas de trabajo y los casos prác-ticos:
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.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 65
Relaciones entre archivos de Excel y pestañas:
ARCHIVO
PESTAÑA PRESUPUESTO_MES PR
ARCHIVO
PESTAÑA IMPORTE_REAL RE
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:
66 Instalación y sistemas de utilización del modelo
Confección del presupuesto de ingresos, gastos y de la previsión
de tesorería
Parte 2
69
Objetivos del capítulo
• Conocer cómo obtener los datos necesarios para realizar losdiferentes tipos de presupuestos operativos para cada área de responsabilidad o responsable.
• Obtenerunavisión sobrecómo losdatosdeben ser calculadospreviamente para obtener el presupuesto de ingresos y gastos, para ser presentados en un formato estándar por meses.
Enunciado del caso práctico de una empresa de fabricación mediante cuentas y centros de coste: cálculo de ingresos y
gastos previstos
3
70 Enunciado del caso práctico de una empresa de fabricación...
3.1 Actividad y descripción de la empresa
LaempresaPAMO,S.A.(enadelantePAMO)esunadelasmayoresor-ganizaciones 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 mer-cado en las diferentes categorías. La empresa utiliza la última tecnología para fabricar sus productos, de forma que la calidad satisfaga las expecta-tivas de sus clientes.
PAMOharealizadosiempreunaapuestafirmepor la innovacióny lacalidad de sus productos. Por ello, un importante equipo de profesionales altamente cualificados trabaja permanentemente en los Centros de Inves-tigació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 quePAMOseaunlíderdelsectorenventasy,lomásimportante,líderenconfianza.LagentedePAMOesimprescindibleparaquetodosnues-tros clientes y consumidores reciban la mejor calidad y el máximo servi-cio. Por ello contratamos a los mejores profesionales y prestamos la máxi-ma atención a su formación, seguridad, condiciones de trabajo y desarrolloprofesional.EnPAMOestamosorgullososdeteneralasme-jores 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.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 71
• 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 co-merciales.
− Dirección de fábrica: coordina y gestiona todos los procesos de fabri-cación, incluyendo la gestión de la calidad.
− Dirección de almacén y logística: coordina y gestiona todos los proce-sos 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.
72 Enunciado del caso práctico de una empresa de fabricación...
• 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 en-tre 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 Comercial Fábrica Almacén Administración
Ventas X
Compras X
Gastos auxiliares X
Gastos de fabricación X
Comerciales X
Estructura X
Personal X X X X X
Financieros X
Amortizaciones X X X X X
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 73
A continuación también exponemos el plan de cuentas donde se utiliza-rá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
74 Enunciado del caso práctico de una empresa de fabricación...
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
Ventas previstas % sobre el total
Pan de molde normal 1.755.576 11% 1.950.000 75%
Pan tostado 239.101 21% 290.000 11%
Pan de molde integral 291.331 27% 370.000 14%
Total 2.286.008 2.610.000 100%
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 an-terior.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 75
• 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 estacionali-dad 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 pro-ducto.
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 por-centaje de ventas sobre el total para cada familia.
76 Enunciado del caso práctico de una empresa de fabricación...
Cua
dro
3.7
: pre
visi
ón d
e ve
ntas
por
mes
Mes
12
34
56
78
910
1112
Tota
l
Esta
cion
alid
ad
men
sual
6%9%
10%
9%7%
9%8%
7%8%
10%
9%8%
100%
Pan
de m
olde
no
rmal
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.95
0.00
0
Pan
tost
ado
17.4
00
26.1
00
29.0
00
26.1
00
20.3
00
26.1
00
23.2
00
20.3
00
23.2
00
29.0
00
26.1
00
23.2
00
290.
000
Pan
de m
olde
in
tegr
al22
.200
33
.300
37
.000
33
.300
25
.900
33
.300
29
.600
25
.900
29
.600
37
.000
33
.300
29
.600
37
0.00
0
Tota
l15
6.60
0 23
4.90
0 26
1.00
0 23
4.90
0 18
2.70
0 23
4.90
0 20
8.80
0 18
2.70
0 20
8.80
0 26
1.00
0 23
4.90
0 20
8.80
0 2.
610.
000
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 77
Ejemplo de cálculo para el mes de enero:
Cuadro 3.8: ejemplo de previsión de ventas para el mes de enero
Ventas previstastotales
% de estacionalidad mes de enero
Ventas previstas mes de enero
% sobre el total
Pan de molde normal 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%
Total 2.610.000 156.600 100%
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
78 Enunciado del caso práctico de una empresa de fabricación...
• 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.
Importe% compras sobre
ventas
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%
Total compras 600.300 23,0%
• 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 1 2 3 4 5 6 7 8 9 10 11 12
Materias primas A 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%
total % compra/ventas 23% 35% 38% 35% 27% 35% 31% 27% 31% 38% 35% 31%
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 79
Cua
dro
3.1
1: c
ompr
as y
var
iaci
ón d
e st
ocks
por
mes
es
Mes
12
34
56
78
910
1112
Tota
l
Vent
as15
6.60
0 23
4.90
0 26
1.00
0 23
4.90
0 18
2.70
0 23
4.90
0 20
8.80
0 18
2.70
0 20
8.80
0 26
1.00
0 23
4.90
0 20
8.80
0 2.
610.
000
Com
pras
36.0
18
54.0
27
60.0
30
54.0
27
42.0
21
54.0
27
48.0
24
42.0
21
48.0
24
60.0
30
54.0
27
48.0
24
600.
300
Mat
eria
s pr
imas
A15
.951
23
.926
26
.585
23
.926
18
.609
23
.926
21
.268
18
.609
21
.268
26
.585
23
.926
21
.268
26
5.84
7
Mat
eria
s pr
imas
B2.
771
4.15
7 4.
618
4.15
7 3.
233
4.15
7 3.
695
3.23
3 3.
695
4.61
8 4.
157
3.69
5 46
.184
Mat
eria
s pr
imas
C4.
984
7.47
6 8.
307
7.47
6 5.
815
7.47
6 6.
645
5.81
5 6.
645
8.30
7 7.
476
6.64
5 83
.068
Mat
eria
s pr
imas
D7.
633
11.4
50
12.7
22
11.4
50
8.90
6 11
.450
10
.178
8.
906
10.1
78
12.7
22
11.4
50
10.1
78
127.
225
Mat
eria
s pr
imas
E4.
679
7.01
8 7.
798
7.01
8 5.
458
7.01
8 6.
238
5.45
8 6.
238
7.79
8 7.
018
6.23
8 77
.977
Tota
l com
pras
36.0
18
54.0
27
60.0
30
54.0
27
42.0
21
54.0
27
48.0
24
42.0
21
48.0
24
60.0
30
54.0
27
48.0
24
600.
300
Varia
ción
de
stoc
ks-9
.396
-1
4.09
4 -1
5.66
0 -1
4.09
4 -1
0.96
2 -1
4.09
4 -1
2.52
8 -1
0.96
2 -1
2.52
8 -1
5.66
0 -1
4.09
4 -1
2.52
8 -1
56.6
00
Prev
isió
n de
com
pras
en
func
ión
del %
de
las
vent
as p
or m
es
80 Enunciado del caso práctico de una empresa de fabricación...
• 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 cons-tantes cada mes. (Cuadro 3.13)
En este apartado, para calcular los costes de fabricación por mes, multi-plicamos 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, multi-plicamos 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)
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 81
Cua
dro
3.1
3: g
asto
s au
xilia
res
de fa
bric
ació
n po
r m
es
12
34
56
78
910
1112
Tota
l
Gas
oil h
orno
s75
11
3 12
5 11
3 88
11
3 10
0 88
10
0 12
5 11
3 10
0 1.
250
Acei
tes
y gr
asas
333
500
556
500
389
500
444
389
444
556
500
444
5.55
6
Emba
laje
s de
car
tón
100
150
167
150
117
150
133
117
133
167
150
133
1.66
7
Prec
into
s y
plás
ticos
125
188
208
188
146
188
167
146
167
208
188
167
2.08
3
Tota
l63
3 95
0 1.
056
950
739
950
844
739
844
1.05
6 95
0 84
4 10
.556
Mes
12
34
56
78
910
1112
Tota
l
Vent
as15
6.60
0 23
4.90
0 26
1.00
0 23
4.90
0 18
2.70
0 23
4.90
0 20
8.80
0 18
2.70
0 20
8.80
0 26
1.00
0 23
4.90
0 20
8.80
0 2.
610.
000
Cua
dro
3.1
4: p
orce
ntaj
e de
gas
tos
auxi
liare
s de
fabr
icac
ión
12
34
56
78
910
1112
Gas
oil h
orno
s0,
05%
0,05
%0,
05%
0,05
%0,
05%
0,05
%0,
05%
0,05
%0,
05%
0,05
%0,
05%
0,05
%
Acei
tes
y gr
asas
0,21
%0,
21%
0,21
%0,
21%
0,21
%0,
21%
0,21
%0,
21%
0,21
%0,
21%
0,21
%0,
21%
Emba
laje
s de
car
tón
0,06
%0,
06%
0,06
%0,
06%
0,06
%0,
06%
0,06
%0,
06%
0,06
%0,
06%
0,06
%0,
06%
Prec
into
s y
plás
ticos
0,08
%0,
08%
0,08
%0,
08%
0,08
%0,
08%
0,08
%0,
08%
0,08
%0,
08%
0,08
%0,
08%
Tota
l0,
40%
0,40
%0,
40%
0,40
%0,
40%
0,40
%0,
40%
0,40
%0,
40%
0,40
%0,
40%
0,40
%
82 Enunciado del caso práctico de una empresa de fabricación...
Cua
dro
3.1
5: C
oste
s de
fabr
icac
ión
por
mes
12
34
56
78
910
1112
Tota
l
Elec
trici
dad
2.25
0 3.
375
3.75
0 3.
375
2.62
5 3.
375
3.00
0 2.
625
3.00
0 3.
750
3.37
5 3.
000
37.5
00
Agua
267
400
444
400
311
400
356
311
356
444
400
356
4.44
4
Gas
6.66
7 10
.000
11
.111
10
.000
7.
778
10.0
00
8.88
9 7.
778
8.88
9 11
.111
10
.000
8.
889
111.
111
Com
bust
ible
s1.
833
2.75
0 3.
056
2.75
0 2.
139
2.75
0 2.
444
2.13
9 2.
444
3.05
6 2.
750
2.44
4 30
.556
Otro
s su
min
istro
s10
8 16
3 18
1 16
3 12
6 16
3 14
4 12
6 14
4 18
1 16
3 14
4 1.
806
Tota
l11
.125
16
.688
18
.542
16
.688
12
.979
16
.688
14
.833
12
.979
14
.833
18
.542
16
.688
14
.833
18
5.41
7
Mes
12
34
56
78
910
1112
Tota
l
Vent
as15
6.60
0 23
4.90
0 26
1.00
0 23
4.90
0 18
2.70
0 23
4.90
0 20
8.80
0 18
2.70
0 20
8.80
0 26
1.00
0 23
4.90
0 20
8.80
0 2.
610.
000
Cua
dro
3.1
6: P
orce
ntaj
e de
cos
tes
de fa
bric
ació
n so
bre
el to
tal d
e ve
ntas
men
sual
12
34
56
78
910
1112
Elec
trici
dad
1,44
%1,
44%
1,44
%1,
44%
1,44
%1,
44%
1,44
%1,
44%
1,44
%1,
44%
1,44
%1,
44%
Agua
0,17
%0,
17%
0,17
%0,
17%
0,17
%0,
17%
0,17
%0,
17%
0,17
%0,
17%
0,17
%0,
17%
Gas
4,26
%4,
26%
4,26
%4,
26%
4,26
%4,
26%
4,26
%4,
26%
4,26
%4,
26%
4,26
%4,
26%
Com
bust
ible
s1,
17%
1,17
%1,
17%
1,17
%1,
17%
1,17
%1,
17%
1,17
%1,
17%
1,17
%1,
17%
1,17
%
Otro
s su
min
istro
s0,
07%
0,07
%0,
07%
0,07
%0,
07%
0,07
%0,
07%
0,07
%0,
07%
0,07
%0,
07%
0,07
%
Tota
l7,
10%
7,10
%7,
10%
7,10
%7,
10%
7,10
%7,
10%
7,10
%7,
10%
7,10
%7,
10%
7,10
%
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 83
• 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).
84 Enunciado del caso práctico de una empresa de fabricación...
Cua
dro
3.1
8: G
asto
s co
mer
cial
es p
or m
es
Mes
12
34
56
78
910
1112
Tota
l
Vent
as15
6.60
0 23
4.90
0 26
1.00
0 23
4.90
0 18
2.70
0 23
4.90
0 20
8.80
0 18
2.70
0 20
8.80
0 26
1.00
0 23
4.90
0 20
8.80
0 2.
610.
000
Com
isio
nes
14.0
94
21.1
41
23.4
90
21.1
41
16.4
43
21.1
41
18.7
92
16.4
43
18.7
92
23.4
90
21.1
41
18.7
92
234.
900
Catá
logo
s3.
132
4.69
8 5.
220
4.69
8 3.
654
4.69
8 4.
176
3.65
4 4.
176
5.22
0 4.
698
4.17
6 52
.200
Envío
de
mue
stra
s3.
132
4.69
8 5.
220
4.69
8 3.
654
4.69
8 4.
176
3.65
4 4.
176
5.22
0 4.
698
4.17
6 52
.200
Feria
s y
expo
sici
ones
4.69
8 7.
047
7.83
0 7.
047
5.48
1 7.
047
6.26
4 5.
481
6.26
4 7.
830
7.04
7 6.
264
78.3
00
Mai
lings
y a
nunc
ios
1.56
6 2.
349
2.61
0 2.
349
1.82
7 2.
349
2.08
8 1.
827
2.08
8 2.
610
2.34
9 2.
088
26.1
00
Prov
isió
n in
solve
ncia
s1.
566
2.34
9 2.
610
2.34
9 1.
827
2.34
9 2.
088
1.82
7 2.
088
2.61
0 2.
349
2.08
8 26
.100
Tran
spor
te d
e ve
ntas
4.69
8 7.
047
7.83
0 7.
047
5.48
1 7.
047
6.26
4 5.
481
6.26
4 7.
830
7.04
7 6.
264
78.3
00
Tota
l32
.886
49
.329
54
.810
49
.329
38
.367
49
.329
43
.848
38
.367
43
.848
54
.810
49
.329
43
.848
54
8.10
0
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 85
Cua
dro
3.1
9: D
etal
le d
e lo
s ga
stos
de
pers
onal
Cent
roCo
mpo
nent
esIm
port
e m
ensu
alNú
mer
o de
pe
rson
asIm
port
e br
uto
men
sual
Segu
ridad
So
cial
men
sual
Tota
l men
sual
Dire
cció
nDi
recc
ión
gene
ral
4.50
0 1
4.50
0 1.
440
5.94
0 Di
recc
ión
Com
erci
alDi
rect
or c
omer
cial
3.50
0 1
3.50
0 1.
120
4.62
0 Co
mer
cial
Com
erci
alJe
fe d
e ve
ntas
2.50
0 1
2.50
0 80
0 3.
300
Com
erci
al
Com
erci
alVe
nded
ores
1.50
0 1
1.50
0 48
0 1.
980
Com
erci
al
Dire
cció
n de
fábr
ica
Dire
cció
n de
fábr
ica
3.50
0 1
3.50
0 1.
120
4.62
0 Fá
bric
a
Dire
ctos
de
fábr
ica
Enca
rgad
os d
e fá
bric
a2.
000
1 2.
000
640
2.64
0 Fá
bric
a
Dire
ctos
de
fábr
ica
Oper
ario
s fá
bric
a1.
500
32
48.0
00
15.3
60
63.3
60
Fábr
ica
Dire
ctos
de
fábr
ica
Hora
s ex
tras
fábr
ica
544
1 54
4 17
4 71
8 Fá
bric
a
Logí
stic
aJe
fe d
e al
mac
én2.
000
1 2.
000
640
2.64
0 Al
mac
én
Logí
stic
aOp
erar
ios
1.00
0 1
1.00
0 32
0 1.
320
Alm
acén
Adm
inis
trac
ión
Dire
cció
n ad
min
istr
ativ
a3.
000
1 3.
000
960
3.96
0 Ad
min
istr
ació
n
Adm
inis
trac
ión
Cont
able
1.50
0 1
1.50
0 48
0 1.
980
Adm
inis
trac
ión
Adm
inis
trac
ión
Auxi
liar c
onta
ble
1.00
0 1
1.00
0 32
0 1.
320
Adm
inis
trac
ión
Cost
es g
ener
ales
fá
bric
aLi
mpi
eza
1.00
0 1
1.00
0 32
0 1.
320
Fábr
ica
Cost
es g
ener
ales
fá
bric
aVi
gila
ntes
1.00
0 1
1.00
0 32
0 1.
320
Fábr
ica
Logí
stic
aCo
nduc
tore
s1.
000
1 1.
000
320
1.32
0 Al
mac
én
Tota
l77
.544
24
.814
10
2.35
8
86 Enunciado del caso práctico de una empresa de fabricación...
Cua
dro
3.2
0: D
etal
le d
e lo
s ga
stos
de
pers
onal
por
mes
Cent
ro1
23
45
67
89
1011
12To
tal
Dire
cció
n4.
500
4.50
0 4.
500
4.50
0 4.
500
4.50
0 4.
500
4.50
0 4.
500
4.50
0 4.
500
4.50
0 54
.000
Com
erci
al3.
500
3.50
0 3.
500
3.50
0 3.
500
3.50
0 3.
500
3.50
0 3.
500
3.50
0 3.
500
3.50
0 42
.000
Com
erci
al2.
500
2.50
0 2.
500
2.50
0 2.
500
2.50
0 2.
500
2.50
0 2.
500
2.50
0 2.
500
2.50
0 30
.000
Com
erci
al1.
500
1.50
0 1.
500
1.50
0 1.
500
1.50
0 1.
500
1.50
0 1.
500
1.50
0 1.
500
1.50
0 18
.000
Dire
cció
n de
fábr
ica
3.50
0 3.
500
3.50
0 3.
500
3.50
0 3.
500
3.50
0 3.
500
3.50
0 3.
500
3.50
0 3.
500
42.0
00
Dire
ctos
de
fábr
ica
2.00
0 2.
000
2.00
0 2.
000
2.00
0 2.
000
2.00
0 2.
000
2.00
0 2.
000
2.00
0 2.
000
24.0
00
Dire
ctos
de
fábr
ica
48.0
00
48.0
00
48.0
00
48.0
00
48.0
00
48.0
00
48.0
00
48.0
00
48.0
00
48.0
00
48.0
00
48.0
00
576.
000
Dire
ctos
de
fábr
ica
544
544
544
544
544
544
544
544
544
544
544
544
6.52
8
Logí
stic
a2.
000
2.00
0 2.
000
2.00
0 2.
000
2.00
0 2.
000
2.00
0 2.
000
2.00
0 2.
000
2.00
0 24
.000
Logí
stic
a1.
000
1.00
0 1.
000
1.00
0 1.
000
1.00
0 1.
000
1.00
0 1.
000
1.00
0 1.
000
1.00
0 12
.000
Adm
inis
trac
ión
3.00
0 3.
000
3.00
0 3.
000
3.00
0 3.
000
3.00
0 3.
000
3.00
0 3.
000
3.00
0 3.
000
36.0
00
Adm
inis
trac
ión
1.50
0 1.
500
1.50
0 1.
500
1.50
0 1.
500
1.50
0 1.
500
1.50
0 1.
500
1.50
0 1.
500
18.0
00
Adm
inis
trac
ión
1.00
0 1.
000
1.00
0 1.
000
1.00
0 1.
000
1.00
0 1.
000
1.00
0 1.
000
1.00
0 1.
000
12.0
00
Cost
es g
ener
ales
fábr
ica
1.00
0 1.
000
1.00
0 1.
000
1.00
0 1.
000
1.00
0 1.
000
1.00
0 1.
000
1.00
0 1.
000
12.0
00
Cost
es g
ener
ales
fábr
ica
1.00
0 1.
000
1.00
0 1.
000
1.00
0 1.
000
1.00
0 1.
000
1.00
0 1.
000
1.00
0 1.
000
12.0
00
Logí
stic
a1.
000
1.00
0 1.
000
1.00
0 1.
000
1.00
0 1.
000
1.00
0 1.
000
1.00
0 1.
000
1.00
0 12
.000
Tota
l77
.544
77
.544
77
.544
77
.544
77
.544
77
.544
77
.544
77
.544
77
.544
77
.544
77
.544
77
.544
93
0.52
8
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 87
Cua
dro
3.2
1: D
etal
le d
e lo
s ga
stos
de
la s
egur
idad
soc
ial a
car
go d
e la
em
pres
a po
r m
es
Cent
ro1
23
45
67
89
1011
12To
tal
Dire
cció
n1.
440
1.44
0 1.
440
1.44
0 1.
440
1.44
0 1.
440
1.44
0 1.
440
1.44
0 1.
440
1.44
0 17
.280
Com
erci
al1.
120
1.12
0 1.
120
1.12
0 1.
120
1.12
0 1.
120
1.12
0 1.
120
1.12
0 1.
120
1.12
0 13
.440
Com
erci
al80
0 80
0 80
0 80
0 80
0 80
0 80
0 80
0 80
0 80
0 80
0 80
0 9.
600
Com
erci
al48
0 48
0 48
0 48
0 48
0 48
0 48
0 48
0 48
0 48
0 48
0 48
0 5.
760
Dire
cció
n de
fábr
ica
1.12
0 1.
120
1.12
0 1.
120
1.12
0 1.
120
1.12
0 1.
120
1.12
0 1.
120
1.12
0 1.
120
13.4
40
Dire
ctos
de
fábr
ica
640
640
640
640
640
640
640
640
640
640
640
640
7.68
0
Dire
ctos
de
fábr
ica
15.3
60
15.3
60
15.3
60
15.3
60
15.3
60
15.3
60
15.3
60
15.3
60
15.3
60
15.3
60
15.3
60
15.3
60
184.
320
Dire
ctos
de
fábr
ica
174
174
174
174
174
174
174
174
174
174
174
174
2.08
9
Logí
stic
a64
0 64
0 64
0 64
0 64
0 64
0 64
0 64
0 64
0 64
0 64
0 64
0 7.
680
Logí
stic
a32
0 32
0 32
0 32
0 32
0 32
0 32
0 32
0 32
0 32
0 32
0 32
0 3.
840
Adm
inis
trac
ión
960
960
960
960
960
960
960
960
960
960
960
960
11.5
20
Adm
inis
trac
ión
480
480
480
480
480
480
480
480
480
480
480
480
5.76
0
Adm
inis
trac
ión
320
320
320
320
320
320
320
320
320
320
320
320
3.84
0
Cost
es g
ener
ales
fábr
ica
320
320
320
320
320
320
320
320
320
320
320
320
3.84
0
Cost
es g
ener
ales
fábr
ica
320
320
320
320
320
320
320
320
320
320
320
320
3.84
0
Logí
stic
a32
0 32
0 32
0 32
0 32
0 32
0 32
0 32
0 32
0 32
0 32
0 32
0 3.
840
Tota
l24
.814
24
.814
24
.814
24
.814
24
.814
24
.814
24
.814
24
.814
24
.814
24
.814
24
.814
24
.814
29
7.76
9
88 Enunciado del caso práctico de una empresa de fabricación...
• 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í
Hoja de presupuesto que utilizaremos para hacer el mismo
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 89
Cua
dro
3.2
2: D
etal
le d
e lo
s ga
stos
de
estr
uctu
ra m
ensu
ales
12
34
56
78
910
1112
Tota
l
Alqu
ilere
s50
50
50
50
50
50
50
50
50
50
50
50
60
0
Repa
raci
ones
y
cons
erva
ción
100
100
100
100
100
100
100
100
100
100
100
100
1.20
0
Men
saje
ros
300
300
300
300
300
300
300
300
300
300
300
300
3.60
0
Serv
icio
s de
pr
ofes
iona
les
150
150
150
150
150
150
150
150
150
150
150
150
1.80
0
Segu
ros
2.00
0 2.
000
2.00
0 2.
000
2.00
0 2.
000
2.00
0 2.
000
2.00
0 2.
000
2.00
0 2.
000
24.0
00
Otro
s su
min
istro
s50
0 50
0 50
0 50
0 50
0 50
0 50
0 50
0 50
0 50
0 50
0 50
0 6.
000
Com
unic
acio
nes
100
100
100
100
100
100
100
100
100
100
100
100
1.20
0
Mat
eria
l de
ofici
na40
0 40
0 40
0 40
0 40
0 40
0 40
0 40
0 40
0 40
0 40
0 40
0 4.
800
Vario
s25
0 25
0 25
0 25
0 25
0 25
0 25
0 25
0 25
0 25
0 25
0 25
0 3.
000
Trib
utos
4.10
0 4.
100
4.10
0 4.
100
4.10
0 4.
100
4.10
0 4.
100
4.10
0 4.
100
4.10
0 4.
100
49.2
00
Tota
l7.
900
7.90
0 7.
900
7.90
0 7.
900
7.90
0 7.
900
7.90
0 7.
900
7.90
0 7.
900
7.90
0 94
.800
90 Enunciado del caso práctico de una empresa de fabricación...
Cua
dro
3.2
3: D
etal
le d
e lo
s ga
stos
fina
ncie
ros
men
sual
es
12
34
56
78
910
1112
Tota
l
Tota
l ven
tas
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.61
0.00
0
% G
asto
s fin
anci
eros
2%2%
2%2%
2%2%
2%2%
2%2%
2%2%
2%
Fina
ncie
ros
3.13
2 4.
698
5.22
0 4.
698
3.65
4 4.
698
4.17
6 3.
654
4.17
6 5.
220
4.69
8 4.
176
52.2
00
Cua
dro
3.2
4: D
etal
le d
e lo
s ac
tivos
act
uale
s y
las
nuev
as in
vers
ione
s, a
sí c
omo
amor
tizac
ione
s
Cod_
Cent
roCe
ntro
Inm
ovili
zado
Amor
tizac
ione
sAñ
o an
terio
rAm
ortiz
acio
nes
Alta
sTo
tal
anua
lTo
tal m
es
3000
Dire
ctos
de
fabr
icac
ión
17.0
00
3.00
0 40
0 3.
400
283
4100
Depa
rtam
ento
de
prod
ucci
ón3.
000
500
100
600
50
4200
Logí
stic
a6.
500
1.00
0 30
0 1.
300
108
4900
Serv
icio
s ge
nera
les
fábr
ica
7.50
0 1.
200
300
1.50
0 12
5
5000
Com
erci
al2.
000
300
100
400
33
6100
Adm
inis
traci
ón2.
500
450
50
500
42
6200
Dire
cció
n75
0 12
0 30
15
0 13
6900
Serv
icio
s ge
nera
les
estru
ctur
a10
.750
1.
800
350
2.15
0 17
9
To
tal
50.0
00
8.37
0 1.
630
10.0
00
833
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 91
Cua
dro
3.2
5: D
etal
le d
e lo
s ga
stos
de
amor
tizac
ión
pror
rate
ados
por
mes
12
34
56
78
910
1112
Tota
l
Dire
ctos
de
fabr
icac
ión
283
283
283
283
283
283
283
283
283
283
283
283
3.40
0
Depa
rtam
ento
de
prod
ucci
ón50
50
50
50
50
50
50
50
50
50
50
50
60
0
Logí
stic
a10
8 10
8 10
8 10
8 10
8 10
8 10
8 10
8 10
8 10
8 10
8 10
8 1.
300
Serv
icio
s ge
nera
les
fábr
ica
125
125
125
125
125
125
125
125
125
125
125
125
1.50
0
Com
erci
al33
33
33
33
33
33
33
33
33
33
33
33
40
0
Adm
inis
traci
ón42
42
42
42
42
42
42
42
42
42
42
42
50
0
Dire
cció
n13
13
13
13
13
13
13
13
13
13
13
13
15
0
Serv
icio
s ge
nera
les
estru
ctur
a17
9 17
9 17
9 17
9 17
9 17
9 17
9 17
9 17
9 17
9 17
9 17
9 2.
150
Tota
l83
3 83
3 83
3 83
3 83
3 83
3 83
3 83
3 83
3 83
3 83
3 83
3 10
.000
92 Enunciado del caso práctico de una empresa de fabricación...
93
Objetivos del capítulo
• Aprender las técnicas necesarias para realizar el presupuesto deingresos 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 quepermiten generar los ingresos y gastos reales por mes, que servirán de base al análisis de desviaciones acumulado o por mes.
Confección del presupuesto: ingresos, gastos presupuestados y reales
4
94 Confección del presupuesto: ingresos, gastos presupuestados y reales
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 informa-ció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_EM-PRESA_INDUSTRIAL, copiamos los datos al fichero Excel siguien-te: 1_1_CALCULO_PR_CENTROS_CP1, que es el que utilizare-mos para nuestro modelo. Los datos a utilizar son los siguientes:
PRESUPUESTO_MES:contienelosdatosdenúmerodecuenta,nom-bre de la cuenta, código del centro de coste, importes para cada mes.
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
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 95
6 Importes
7 Importes
8 Importes
9 Importes
10 Importes
11 Importes
12 Importes
Total Suma total de importes por mes
Ejemplo de esta hoja:
• Estas hojas están protegidas, excepto las celdas donde debemosponer los importes correspondientes.
• Serecomiendaseguirlascaracterísticasdeloscampos.
• Estahojasedebealimentardeotroscálculosrealizadospreviamente.
• Ennuestroejemploutilizamoslosimportesdeingresosconsignopositivo y los de gastos con signo negativo.
CONTROL:contienelosdatosdelahojaanteriorPRESUPUESTO_MES, que mediante una tabla dinámica deben actualizar y verificar el resultado final. Es una tabla dinámica de consulta y verificación.
96 Confección del presupuesto: ingresos, gastos presupuestados y reales
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 ARCHIVOS DESTINO
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 97
4.2 Confección de informes para los ingresos y gastos reales
Este proceso nos debe permitir obtener los diferentes importes por cuen-tas 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.
98 Confección del presupuesto: ingresos, gastos presupuestados y reales
Ejemplo de esta hoja: pestaña 1.
• Esta plantilla sirve para cambiar los signos de los balances desumas y saldos de su programa contable para que queden con saldo negativo los gastos.
• Debeverificarquetodoslosdatosmensuales,unavezsumados,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:
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 99
NOTA:RECORDARQUEESTAPESTAÑADEIMPORTES_REALESESUNATABLADINÁMICA,YQUESUACTUALIZACIÓNSEREALIZAMEDIANTEELCURSORSOBRELATABLAYCONELBOTÓNDERECHODELRATÓNPULSARSOBREACTUALIZAR.
CONTROL:contienelosdatosdelahojaanterior,IMPORTES_REA-LES, 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 encon-tremos. A continuación vemos un ejemplo:
100 Confección del presupuesto: ingresos, gastos presupuestados y reales
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:RECORDARQUESIEMPREHAYQUEVERIFICARLOSSALDOSTOTALESREALESPOR SI HAY CAMBIOS EN LOS PERIODOS ANTERIORES Y VOLVERLOS A ACTUALIZAR SI ES NECESARIO.
101
Objetivos del capítulo
• Obtenerlosdiferentesinformesdedesviacionespormesacumu-ladas entre los ingresos y gastos reales y los presupuestados.
• Confeccionarlasdiferentescuentasdeexplotaciónenelformatoque precise de forma automática y que expresen el análisis de las desviaciones mensuales y acumuladas.
• Poder confeccionar,mediante los informesde tablasdinámicas,innumerables informes con desviaciones, cuadros evolutivos, gráficos e indicadores.
Análisis de desviaciones en ingresos, en gastos y en cuentas de explotación
5
102 Análisis de desviaciones en ingresos, en gastos y en cuentas de explotación
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.Sucontenidoeselsiguiente:
De forma resumida tenemos los siguientes apartados:
• DATOS:pestañaquenosindicalosdatosinicialesaponerenelmodelo:
• Las siguientespestañas incorporan losplanesdecuentasy la tabladecentros de coste a confeccionar para poder efectuar los diferentes análisis:
Pestaña 1D:
Pestaña 2D:
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 103
Pestaña 3D:
Pestaña 4D:
NOTA:ESTASPESTAÑASSONOPTATIVASPEROSERECOMIENDASIEMPRERELLENARLASDE 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.
104 Análisis de desviaciones en ingresos, en gastos y en cuentas de explotación
• 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ñaTD5 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.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 105
• En la pestañaTD6 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.
• EnlapestañaTD7 de 1_3_DATOS_PR_RE_CENTROS_CP1: evo-lución de las desviaciones entre los datos reales y el presupuesto por centros.
Evolución de desviaciones por cuentas
Evolución de desviaciones por centros
106 Análisis de desviaciones en ingresos, en gastos y en cuentas de explotación
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 es-tados financieros. A título de ejemplo veremos los siguientes:
• Pestaña TD1, situada en 1_5_RESULTADOS_PR_RE_CEN-TROS_CP1,aquícopiaremoslatabladinámicadelExcelanterioryvincularemos mediante fórmulas los datos.
Proceso a realizar:
Copiar la tabla dinámica de la hoja Excel 1_3_DATOS_PR_RE_CEN-TROS_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
EnelExcel1_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).
EnelExcel1_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).
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 107
Cua
dro
5.2
.1
108 Análisis de desviaciones en ingresos, en gastos y en cuentas de explotación
Cua
dro
5.2
.2
NOTA:RECOR
DARQU
EESTAMOS
UTILIZAND
OLASTABLASDINÁM
ICASQUENOS
PERMITEN
ACTUALIZAR
YFILTRARLOSDATOS,NOS
PON
EMOS
ENC
IMADELATABLADINÁ
MICA,BOTÓN
DERECH
ODELRATÓNYAC
TUALIZARPARAOB
TENERLOSÚLTIMOS
RESULTADO
S.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 109
RESUMEN:
Confección del presupuesto
1_1_CALCULO_PR_CENTROS_CP1
1_3_DATOS_PR_RE_
CENTROS_CP1Importamos 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
1_5_RESULTADOS_PR_RE_CENTROS_CP1
Obtención de las diferenes cuentas de explotación
hechas a medida por cada usuario
Cálculo de los saldos reales1_2_PLANTILLA_
CUENTAS_CENTROS_CP1 1_3_DATOS_PR_RE_
CENTROS_CP1Importamos o vinculamos
los datos reales en la pestaña RE
110 Análisis de desviaciones en ingresos, en gastos y en cuentas de explotación
111
Objetivos del capítulo
• Confecciónapartirdelosingresosloscobroscorrespondientessegún plazos de cobro.
• Confecciónapartirdelosgastoslospagoscorrespondientessegúnel presupuesto de pagos.
• Obtener los cobros y pagos relacionados con inversiones,impuestos, préstamos y otros conceptos.
• Obtenerunpresupuestodetesoreríaprovisionalparaanalizarlaviabilidad financiera de una organización.
Realización del presupuesto de tesorería
6
112 Realización del presupuesto de tesorería
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 presupuesto1_1_CALCULO_PR_
CENTROS_CP1 1_3_DATOS_PR_RE_
CENTROS_CP1Importamos o
vinculamos los datos presupuestado en la
pestaña PR
Estos importes los enviamos a la hoja
de proceso del presupuesto y los datos reales para obtener las
desviaciones
1_4_PR_TESORERIA_CENTROS_CP1
Vinculamos o copiamos los datos del presupuesto con el Excel de gestión de
la tesorería
NossituamosenelExcel1_4_PR_TESORERIA_CENTROS_CP1
Primero copiaremos los datos en las siguientes pestañas:
• pr_inGres
CopiamoslosingresospresupuestadosdelExcelanterior:1_3_DATOS_PR_RE_CENTROS_CP1,pestañaPR
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 113
• pr_Gasto
Copiamos losgastospresupuestadosdelExcel anterior:1_3_DATOS_PR_RE_CENTROS_CP1,pestañaPR
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 CO-BROS 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 nues-tra empresa.
• Seintroducenlosdatosdelaempresa.
• Seponenlostiposdecuentasegúnelconcepto de impuestos.
• Seintroducenlosconceptosdeorigende cobros y pagos que permiten des-pués analizar los cobros y pagos de una forma más ágil.
114 Realización del presupuesto de tesorería
• 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 si-guiente 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:
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 115
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 DEBE-MOS 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 INCOR-PORADO 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.
116 Realización del presupuesto de tesorería
• 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 inversio-nes 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 Altas
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 350
Total 1.630
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 117
6.3 Análisis del presupuesto de tesorería
Antes de pasar a la siguiente fase de análisis del presupuesto de tesorería sedeberáverificarquetodasestaspestañasde1_4_PR_TESORERIA_CENTROS_CP1estáncumplimentadascorrectamente,delocontrarioel resultado no será el adecuado.
PESTAÑAS DE EXCEL
DESCRIPCIÓN REVISADO SÍ/NO
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.
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.
118 Realización del presupuesto de tesorería
• 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 movi-miento. 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 TA-BLA DINÁMICA, BOTÓN DERECHO DEL RATÓN Y PULSAR SOBRE ACTUALIZAR.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 119
• 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.
120 Realización del presupuesto de tesorería
Presupuesto de tesorería mensual resumido, por cobros y pagos, totales y saldo final.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 121
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 siguien-te 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
122 Realización del presupuesto de tesorería
123
Objetivos del capítulo
• Saber qué información obtener y en qué formato paraconfeccionar 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 dedesviaciones y cuentas de explotación con datos acumulados y mensuales.
• Realizar el presupuesto de tesorería previsional a partir de losingresos y gastos presupuestados y añadir los cobros y pagos necesarios para obtener la situación de tesorería mensual.
Caso práctico para una empresa de transporte
mediante cuentas contables
7
124 Caso práctico para una empresa de transporte mediante cuentas contables
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 has-ta su final. A continuación vemos los pasos a realizar:
Los datos a utilizar son los que presentamos a continuación:
Primero:Obtenerelpresupuestoycopiarlo/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
• EnelarchivodeExcel2_1_CALCULO_PR_CUENTA_CP2,enlapestaña PRESUPUESTO_MES, tenemos los datos presupuestadosquesedebenpoderponerenelsiguienteExcel:2_3_DATOS_PR_RE_CUENTA_CP2, en la pestaña PR.
Pestaña:PRESUPUESTOMES
• EnelarchivodeExcel2_3_DATOS_PR_RE_CUENTA_CP2,enlapestaña PR, tendremos los datos anteriores:
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 125
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:Obtenerlosdatosrealesycopiarlos/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 lapestañaDATOS_REALES,tenemoslosdatosrealesquesedebenpo-derintroducirenelsiguienteExcel:2_3_DATOS_PR_RE_CUENTA_CP2, en la pestaña RE.
Pestaña:DATOS_REALES
• EnelarchivodeExcel2_3_DATOS_PR_RE_CUENTA_CP2,enlapestaña RE, tendremos los datos anteriores:
126 Caso práctico para una empresa de transporte mediante cuentas contables
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,realizarlossiguientesinformes:
• 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:
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 127
• 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.
128 Caso práctico para una empresa de transporte mediante cuentas contables
• 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:
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 129
Para la obtención de estados financieros iremos al archivo siguiente: 2_5_RESULTADOS_PR_RE_CUENTAS_CP2.
DesdeelanteriorExcel,2_3_DATOS_PR_RE_CUENTA_CP2,copia-remos 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.
EnesteExcel,2_5_RESULTADOS_PR_RE_CUENTAS_CP2,yenlapestaña TD1:
130 Caso práctico para una empresa de transporte mediante cuentas contables
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 131
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.
132 Caso práctico para una empresa de transporte mediante cuentas contables
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 con-tinuació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
Datosde2_3_DATOS_PR_RE_CUENTA_CP2pestañaPR:secopianprimero los gastos y luego los ingresos, o viceversa.
• pr_inGres
Aquí ponemos los ingresos como cobros desde el presupuesto general:
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 133
• 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:
134 Caso práctico para una empresa de transporte mediante cuentas contables
• 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ámi-ca 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.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 135
• pestaña: pt_1
• pestaña: pt_2
136 Caso práctico para una empresa de transporte mediante cuentas contables
Presupuesto de tesorería mensual resumido por cobros y pagos.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 137
Presupuesto de tesorería mensual resumido por saldo final.
• control_iva
Tabla para verificar la situación del IVA si existe la posibilidad de com-pensación.
138 Caso práctico para una empresa de transporte mediante cuentas contables
Confección de presupuestos por objetivos con diferentes tipos
de variables
Parte 3
140 Caso práctico para una empresa de transporte mediante cuentas contables
141
Objetivos del capítulo
• Conocercómodefinirunosobjetivospreviosparalasventasporfamilias.
• Realizarelseguimientodelosobjetivosprevistosyrealesmedianteel análisis de desviaciones.
• Diseñar informes que analicen las desviaciones por mes yacumulado, gráficos e indicadores.
Caso práctico: confeccionar la previsión de ingresos
por ventas y por meses según objetivos por familias de productos
8
142 Caso práctico: confeccionar la previsión de ingresos por ventas y por meses según objetivos...
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:
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 143
• 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.
144 Caso práctico: confeccionar la previsión de ingresos por ventas y por meses según objetivos...
• td1
Análisis de ventas por familia. Nos muestra la diferencia entre los impor-tes 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.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 145
• td2
Evolución de objetivos de ventas por familias, por meses:
• td3
Evolución de datos reales de ventas por familias, por meses:
146 Caso práctico: confeccionar la previsión de ingresos por ventas y por meses según objetivos...
147
Objetivos del capítulo
• Conocercómodefinirunosobjetivospreviosparalasventasporvendedores.
• Realizarelseguimientodelosobjetivosprevistosyrealesmedianteel análisis de desviaciones.
• Diseñar informes que analicen las desviaciones por mes yacumulado, gráficos e indicadores.
Caso práctico: confeccionar la previsión de ingresos
por ventas y por meses según objetivos por vendedores
9
148 Caso práctico: confeccionar la previsión de ingresos por ventas y por meses según objetivos...
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 empre-sa 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_FI-JAR_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.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 149
• 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.
150 Caso práctico: confeccionar la previsión de ingresos por ventas y por meses según objetivos...
• td1
Análisis de ventas por vendedor. Nos muestra la diferencia entre los im-portes 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.
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 151
• td2
Evolución de objetivos de ventas por vendedor, por meses:
• td3
Evolución de datos reales de ventas por vendedor, por meses:
152 Caso práctico: confeccionar la previsión de ingresos por ventas y por meses según objetivos...
153
• desbloqueo de Macros en excel 2007 o 2010
Ir, desde opciones de Excel, a la opción de Centro de confianza:
Herramientas de Excel para el modelo
Anexo 1
154 Anexo 1. Herramientas de Excel para el modelo
Y seleccionar
Aquí, en la configuración de macros, activar la opción marcada:
155
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
Esquema general del modelo
Anexo 2
156 Anexo 2: Esquema general del modelo
• 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:
Confeccionar y controlar presupuestos y previsiones de tesorería con Excel 157
• Caso práctico de la empresa de servicios de trasporte:
• Casos prácticos para el control de objetivos:
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