Facultad de Economía y Empresa
Universidad de Zaragoza |
Presupuesto de Tesorería con hoja de cálculo
|
1) Objetivo
En este ejercicio vamos a utilizar la hoja de cálculo para desarrollar un Presupuesto de Tesorería.
 A partir de este modelo simplificado cabe realizar presupuestos detallados de inversiones, de ventas, de producción, etc.
Sabemos que existen distintos modelos de presupuestos y hay que adaptarlos a la necesidad concreta de la empresa.
En este ejemplo se utiliza un formato que, además del saldo final de la tesorería, nos organice los datos según conceptos (cobros y pagos de las ventas y las compras, los flujos provenientes de las operaciones de capital y los cobros y pagos provenientes de la financiación a corto plazo), lo que es muy apropiado para que la empresa pueda anticiparse e identificar las tensiones en la tesorería y sus causas.
A la hora de preparar el presupuesto veremos que algunas partidas son conocidas, como los pagos de intereses de un préstamo que hemos firmado (salvo que se renocie) o la nómina de los empleados (salvo despidos o nuevas contrataciones imprevistas).
Pero en otros conceptos habrá que hacer una estimación... por ejemplo, tocará predecir las ventas. Podemos usar funciones para estimar las ventas futuras. Habrá que comprobar su tendencia y si dependen del mes del año o de las estaciones del año u otros factores. En caso afirmativo habrá que desestacionalizar la serie o utilizar las denominadas "medias móviles" que permiten tener en cuenta estos factores.
El concepto más importante del presupuesto suelen ser las ventas. Además de estimarlas merece la pena poder hacer simulaciones para ver como una caida o aumento de las ventas puede afectar a la tesorería.
La hoja de cálculo permite administrar escenarios e incluso hay una opción en el menú que lo realiza de forma automática.
El caso analizado se trata de una empresa que comercializa jamones que quiere realizar un Presupuesto de Tesorería.
- Dispone de la cifra de ventas mensuales de jamones de los dos últimos años.
- La política de cobros es casi todo al contado pero quiere conocer el efecto de algunos cambios sobre la tesorería.
- Casi todos los pagos se realizan a 60 días.
- La liquidación del IVA es trimestral.
 Descarga la hoja de cálculo con el ejercicio: [14-Prestesoreria.xls].
|
¿Qué vamos a aprender?
- Los aspectos básicos de un Presupuesto de Tesorería.
- Realizar una estimación de las ventas futuras, a partir de los datos históricos.
- Simular el efecto de cambiar las políticas de pago a proveedores y cobro a clientes.
Ingredientes:
- La funcion TENDENCIA() que realiza una estimación lineal y servirá para predecir las ventas.
- Diversos tipos de gráficos.
- La funcion PAGOINT(), PAGOPRIN() y PAGO() sobre intereses de un préstamo.
- Validación de datos.
2) Previsión de ventas
La cuenta de resultados de una empresa se analiza de arriba abajo: la partida más importante son las ventas. Por ello, un apartado importante del Presupuesto de Tesorería es la previsión de ventas.
En este ejemplo, los datos de partida son las ventas mensuales de jamones durante los dos años anteriores.
Vamos a visualizarlas gráficamente y despues ajustaremos una línea.
En el ejemplo, parece adecuado empezar con una estimación lineal, pero en otras ocasiones será mejor otro tipo de ajuste.
A la vista del gráfico, para predecir las ventas parece razonable usar la función TENDENCIA() que realiza una estimación lineal.
En los argumentos se le indica:
- Primero las ventas reales históricas,
- Luego los meses y
- Finalmente el periodo que tiene que predecir.
Veamos un ejemplo sencillo. Las ventas de una empresa en los últimos 4 meses han sido 5, 10, 15 y 20. A ojo ¿cuanto serán las ventas previstas para el mes siguiente, es decir el quinto?
Parace razonable estimar que serán 25. Si aún tenemos dudas, lo vemos más claro con un gráfico:
La excel a través de la función TENDENCIA() realiza una estimación lineal, que en este caso se considera adecuada. Por tanto añadimos la función:
=TENDENCIA(B2:E2;B1:E1;F1) |
--- > Tendencia de las ventas. Primero las ventas, luego el periodo y finalmente el mes a predecir
y como vemos, obtiene el resultado esperado de 25.
En el caso de las ventas de jamones, añadiremos la función:
=TENDENCIA($D$40:$AA$40;$D$39:$AA$39;D68) |
--- > Tendencia de las ventas.
Veamos el resultado:
No está mal. Pero tiene un problema: a ojo en el gráfico se ve que hay estacionalidad, es decir en diciembre es cuando más jamones se vende y en verano se venden menos. Y eso no lo detecta la función TENDENCIA().
Entonces en nuestro Presupuesto de Tesorería tendríamos mal estimadas las ventas, especialmente esos meses. Vamos a tener en cuenta el efecto estacional de la serie temporal.
Hay varias formas de hacerlo. Una sencilla es reagrupar los datos por años y calcular el "índice estacional" (mensual en este caso). Para ello reordenamos las ventas según el mes.
Calculamos el promedio de las ventas mensuales, que son 27.629€. Para ello utilizamos la funcion =PROMEDIO() o sumar y dividir por dos.
Las ventas promedio de enero son 20.534€. También podmeos usar la función =PROMEDIO().
Dividiendo 20.534 entre 27.629 da 0.74, que es el índice estacional correspondiente a enero. Es decir, que enero es un mes que se vende menos jamón que la media (74,3%). Mientras que diciembre, con un índice estacional de 149,8%, es el més que más jamones vende nuestra empresa.
Lo podemos visualizar en un gráfico:
Para obtener el gráfico de ventas según mes y año:
- Seleccionamos el rango
- Insertar gráfico -> Línea con marcadores
En ese gráfico se aprecia muy bien el patrón mensual.
Vamos a "desestacionalizamos las ventas", para ello se divide cada mes por su índice. Ojo al arrastrar las fórmulas en enero del segundo año.
Estimamos la TENDENCIA() del nuevo año y aplicamos a la serie anterior el índice de estacionalidad, para tener en cuenta el mes del año.
En el siguiente gráfico vemos como ahora sí que tenemos una predicción que parece más realista. Para obtenerlo hemos tenido que reordenar los datos:
3) Cobros por ventas
Una cosa es vender jamones y otra es cobrarlos, que puede ser perfectamente dos meses más tarde. Vamos a suponer que la empresa tiene la siguiente política de cobro de las ventas:
- Contado 80.0%
- a 30 días 10.0%
- a 60 días 5.0%
- a 90 días 5.0%
Para el Presupuesto de Tesorería también es importante el IVA. Este es un impuesto que la empresa va recaudando y luego se lo entrega a Hacienda. Normalmente se liquida trimestralmente (algunas empresas mensual).
En cuanto a los cobros, eso significa que en enero cobraremos el 80% de las ventas del mes de enero, el 10% de lo que vendimos en diciembre del año pasado, el 5% de lo que vendimos en noviembre y el 5% de lo que vendimos en octubre. Y así con el resto de meses.
4) Pagos por compras
Igual que en las ventas también en cada sector suele haber una política de pagos de las compras que se realizan a los proveedores. Con los que vamos a suponer que hemos negociado las siguientes condiciones:
- Contado 10.0%
- a 30 días 20.0%
- a 60 días 60.0%
- a 90 días 10.0%
Además hemos de tener en cuenta que en esta empresa las compras suponen el 60% de las ventas, por el margen que aplicamos a los jamones.
Igualmente consideraremos el IVA. En enero pagaremos el 10% de las compras del mes de enero, el 20% de lo que compramos en diciembre del año pasado, el 60% de lo que compramos en noviembre y el 10% de lo que compramos en octubre. Y así con el resto de meses.
5) Otros pagos
Suponemos que los gastos de personal y fabricación dependen de las ventas, aunque sería más realista poner un fijo y un % de ventas.
En los gastos de personal se incluye la seguridad social y el IRPF.
Aunque el IRPF es trimestral en empresas pequeñas, lo que podría tener un importante efecto en tesorería.
(http://conocetusimpuestos.blogspot.com.es/2011/04/modelo-111-irpf-retenciones-y-pagos.html).
Supondremos que:
- Los gastos de personal son el 22.0% de las ventas
- Los gastos de fabricación son el 5.0% de las ventas
- Los gastos de administración son fijos, concretamente 2,340€ mensuales
Se podría añadir la compra de equipamiento prevista en algún mes puntual.
6) Pagos por gastos financieros
En cuato a los gastos financieros tenemos un préstamo que se amortiza por el método francés. Ello implica que cada mes se paga la misma cuota, que es la suma de los intereses y lo que pagamos por la devolución del mismo.
- Préstamo 30,000€
- Duración 5 años
- Tipo 12%
Ponemos una función condicional de forma que si el préstamo es cero, ponga cero y si no aplique la función correspondiente, que es PAGOINT() para los intereses, PAGOPRIN() para el principal y PAGO() para la cuota, que es la suma de intereses y principal.
Entonces la suma de pagos queda como sigue:
7) Liquidación del IVA
El IVA se liquida mensual o trimestralmente (http://www.agenciatributaria.es/AEAT.internet/Inicio_es_ES/La_Agencia_Tributaria/Campanas/Devolucion_mensual_del_Impuesto_sobre_el_Valor_Anadido/Devolucion_mensual_del_Impuesto_sobre_el_Valor_Anadido.shtml ) y
(http://finanzas-personales.practicopedia.lainformacion.com/impuestos/como-saber-si-es-mejor-declarar-el-iva-mensual-o-trimestralmente-12867). Si la empresa es nueva y compra mucho y vende poco, le puede interesar mensual.
Por tanto calcularemos el IVA repercutido y el soportado y la diferencia supondrá un desembolso que haremos cada trimestre.
8) Modelo de Presupuesto de Tesorería
Todo modelo de Presupuesto de Tesorería incluye los Cobros Totales de cada periodo menos los Pagos Totales. De forma que se genera un superávit o un déficit. Y obtendremos el Saldo de tesorería como suma entre el saldo del periodo anterior más ese superávit (o déficit).
Pero para llegar a esos cobros y pagos totales podemos usar varias clasificaciones. Una interesante es realizar tres agrupaciones, según se trate de.
- Tesorería de Explotación. Es decir, lo relacionado con el negocio en sí: los cobros por ventas de jamones, los pagos por las compras a los proveedores, salarios y pagos relacionados con la explotación.
- Tesorería de Operaciones de Capital. Se la empresa va a pedir un préstamo o a ampliar capital, comprar nuevas máquinas...
- Tesorería de Operaciones de Circulante. Los intereses de los créditos, dividendos, el pago de impuestos, las liquidaciones de IVA...
Así podemos ver como evoluciona la explotación del negocio por separado de las grandes operaciones de capital y de toda la parte del circulante. Lo vemos en el siguiente pantallazo:
Veamos ahora de forma gráfica la evolución de los cobros y pagos mensuales.
Para obtener el gráfico de ventas según mes y año:
- Seleccionamos el rango
- Insertar gráfico -> Barra apilada
- Opciones del eje: Categorías en orden inverso
- Etiquetas del eje: bajo
- Opciones del eje: hemos fijado el maximo por simetría
El gráfico inferior es más interesante pues se obtiene por la diferencia entre cobros y pagos y muestra también el saldo acumulado de tesorería. Nótese como es un gráfico mixto de columna y línea.
Gráfico de ventas según mes y año
- Seleccionamos el rango
- Insertar gráfico -> Columna agrupada
- Se selecciona la serie correspondiente al saldo -> se cambia el tipo de gráfico a línea.
- Rellenar -> invertir si es negativo. -> sale en rojo si el saldo es negativo
Como vemos se observa tesorería negativa. Para solucionarlo, el Director Financiero tendrá que contemplar varias opciones, como son, por ejemplo:
 Citar como: Serrano Cinca
C. (2014): "Lecciones de Finanzas",
[en línea] 5campus.org
<http://www.5campus.org/ifinanzas> [y añadir
fecha consulta] |
[Índice] |
