Tutorial: Presupuesto en Excel

Cómo crear una hoja de Ingresos y Gastos con Excel

Para aquellos que siguieron los post anteriores o nuestra lista de reproducción en Youtube con tutoriales de Excel para transportistas dónde explicábamos cómo crear una Agenda de Contactos Simple, una Agenda de Contactos Pro, un Resumen de la Agenda o insertar un Calendario. Ahora, os traemos un nuevo post. En él, os vamos a explicar cómo realizar una hoja en Excel para llevar al día los Ingresos y Gastos, con el título de Presupuesto en Excel.

El formato de nuestra plantilla presupuesto en excel, va dirigida concretamente a transportistas que necesitan apuntar sus gastos diarios durante sus rutas, así como sus ingresos. Pero, se pueden realizar pequeñas modificaciones en la creación de la plantilla y aplicarla a cualquier situación; desde la economía del hogar a un pequeño negocio.

Al igual que en los artículos anteriores de Excel, en este también os vamos a explicar paso a paso cómo realizar una plantilla eficaz para tus ingresos y gastos.

Encabezados de la plantilla de presupuesto de Excel

El primer paso, es determinar los encabezados de nuestra plantilla. Nosotros, la creamos pensando directamente en los transportistas. Por ello, los encabezados que hemos elegido son los siguientes:

  • Fecha: en ella pondremos la fecha en la que emitimos la factura.
  • Nº factura: como el propio encabezado dice, indicaremos el número de factura.
  • Cliente: indicaremos el nombre a quién vamos a cobrarle nuestro transporte.
  • Fecha recogida: fecha exacta del momento en el que se recoge la mercancía.
  • Fecha entra: fecha en la que se entrega o llega a su destino la carga.
  • Ruta: aquí pondremos el lugar de recogida y entrega.
  • Km: los kilómetros que vamos a recorrer, podemos hacer una previsión de los gastos que nos va a suponer un determinado kilometraje.
  • Carga: el tipo de mercancía que vamos a tener que transportar.
  • Gasolina: el gasto en fuel que vamos a tener para realizar la ruta.
  • Peajes: el pago total en peajes, desde el origen hasta el destino.
  • Comida: gastos en alimentación durante el transporte.
  • Alojamiento: si la ruta es muy larga, es posible que tengamos que alojarnos en algún lugar o, no contemos con una cabina de descanso en el camión, para poder descansar.
  • Otros gastos: otros posibles gastos que puedan surgir.
  • Forma de pago: cómo vamos a realizar el cobro al cliente.
  • Pendiente: cantidad que el cliente nos debe o tiene pendiente a pagar.
  • Cobrado: cantidad que ya hemos recibido.
  • Beneficio: diferencia entre gastos e ingresos.
  • Aviso: nos servirá para indicar de forma muy visual si tenemos una cantidad pendiente o no.

Y el resultado sería el siguiente:

Encabezados para el Presupuesto de Excel

Crear una tabla de Excel

Al igual que hacíamos con la Agenda de Contactos, ahora vamos a introducir todos los encabezados mencionados anteriormente en nuestra hoja de Ingresos y Gastos de Presupuesto de Excel, con el fin de crear posteriormente una tabla de Excel. Los pasos son exactamente los mismos que para la creación de la Agenda:

Tras escribir los encabezados, el siguiente paso es elegir los rangos de nuestra tabla de Excel. En este caso será de A1 a R2. Para ello, nos colocamos en la casilla o rango A1 con nuestro ratón. Presionamos con el botón izquierdo del ratón y sin soltar arrastramos hasta R2.

Crear una tabla de Excel para Presupuesto

Luego, dentro de la pestaña de “Inicio” nos dirigimos a la cinta de opciones. Concretamente, a las opciones que están relacionadas con “Estilos” y elegimos “Dar formato como tabla”, presionado sobre “Dar formato como tabla” con el botón izquierdo del ratón.

Como crear una tabla de Presupuesto en Excel

Ya dentro de “Dar formato como tabla”, seleccionamos el formato que más nos guste con botón izquierdo del ratón.

Formatos tabla de Excel

Tras elegir un formato, nos aparecerá el siguiente cuadro de diálogo. En él, se indica por un lado el lugar dónde se va a crear nuestra tabla. Y por otro lado, si tenemos ya encabezados para nuestra tabla de Excel o no. En este caso, habrá que marcar la casilla de “La tabla tiene encabezados” con nuestro ratón.

Cuadro de diálogo de Tabla de Excel

Ya elegido el formato, este es nuestro resultado:

Tabla de Presupuesto en Excel

Crear listas desplegables con tablas de Excel

Ahora vamos a crear listas desplegables. Esta opción, también la hemos empleado para la creación de Agenda de Contactos. En este caso, nosotros vamos a colocar listas desplegable en dos columnas de nuestro Presupuesto de Excel. En la columna de Cargas (columna H) y en la columna Formas de Pago (columna N).

Los pasos son los que se detallan a continuación:

Primero, vamos crear una columna auxiliar para cargas. Esta columna auxiliar, tendrá que contener todas las cargas que podemos transportar (motos, palés, mudanzas…). Y otra columna auxiliar, para las formas de pago (cheque, al contado, transferencia…).

Columnas auxiliares de Cargas y Formas de Pago

Ahora, introducimos cada una las columnas en diferentes tablas de Excel. Como hemos explicado anteriormente, para introducir nuestra hoja de ingresos y gastos (A1, R2) en una tabla.

Un pequeño resumen del proceso es el siguiente: seleccionamos los datos de la columna V1 hasta V12, nos dirigimos a la pestaña de “Inicio” y, dentro de estilos, “Dar formato como tabla”. Y haremos igual, con la columna W1 hasta W8. Y este será el resultado:

Columnas auxiliares en tabla de Excel

Habremos conseguido dos tablas independientes, que serán nuestras columnas auxiliares, para para la creación de dos listas desplegables en nuestro Presupuesto de Excel. Recordaros que la principal ventaja que tenemos al introducir los datos en una tabla con respecto a un simple rango, es que nuestras listas estarán siempre actualizadas si tenemos que introducir nuevos datos. Ya que, si tenemos que añadir un nuevo tipo de carga o forma de pago, éste se reflejara automáticamente en la lista desplegable.

¿Cómo crear listas desplegables para la columna cargas y formas de pago?

Una vez creadas las columnas auxiliares, el siguiente paso es dirigirnos a nuestra tabla de Ingresos o Gastos y crear las listas desplegables.

El primer paso, seleccionar el rango dónde se va a aplicar la opción de lista desplegable. Para hacer este paso, colocamos el ratón en la parte superior de la columna H (Cargas). Sin seleccionar la columna entera ni el encabezado Cargas, sólo el rango de la columna H con el botón izquierdo del ratón.

Columna carga de nuestro presupuesto de Excel

Ya seleccionado, nos dirigimos a la pestaña de “Datos”. Allí, vamos a “Validación de datos” y presionamos sobre esta opción con el botón izquierdo del ratón.

Crear una lista desplegable en Excel

Tras presionar “Validación de datos”, nos aparecerá un nuevo cuadro de diálogo. En él, sólo consideraremos las opciones de la pestaña “Configuración”. En esta pestaña, elegiremos formato de lista y dejaremos seleccionadas las casillas de “Omitir blancos” y “Celda con lista desplegable”. Respecto a “Origen”, seleccionaremos la columna auxiliar Cargas en formato tabla (sin incluir el encabezado).

Cuadro de diálogo validación de datos

Queda darle a al botón de “Aceptar” con un click izquierdo del ratón. Y ya tendremos nuestra lista desplegable en cargas.

Resultado lista desplegable en Cargas

Estos mismos pasos habrá que seguir para crear una lista desplegable en la columna de Formas de pago. Con el siguiente resultado:

Resultado lista desplegable formas de pago

Dar formato moneda

En esta tabla que estamos realizando, alguno de los datos que vamos a introducir son valores monetarios. Así que vamos a asignar el formato adecuado a estos rangos.

¿Cómo dar formato moneda en Excel?

Seleccionamos las celdas monetarias de nuestra hoja de Ingresos y Gastos. En nuestra tabla son los rangos de Gasolina, Peajes, Comida, Alojamiento, Otros gastos, Pendiente, Cobrado y Beneficio.

Para seleccionarlos, nos colocamos en la casilla de Gasolina I2. Presionamos con el botón izquierdo del ratón y sin soltar arrastramos hasta Otros Gastos M2. Presionamos ahora la tecla “Control” del teclado, sin soltar dicha tecla nos colocamos en Pendiente O2, presionamos con el botón izquierdo del ratón y arrastramos hasta Beneficio.

Selección de columnas para aplicar formato

Tras elegir las casillas con el formato de moneda que queremos asignar, nos dirigimos a la pestaña de “Inicio”. Allí, en la cinta de opciones “Número”, elegimos de la lista desplegable “Moneda”.

Aplicar formato moneda

Si queremos comprobar que este formato ha cambiado a moneda, nos podemos situar en una casilla que hayamos asignado dicho formato y ver que pone Moneda.

Comprobación formato moneda

Ahora, queremos que al introducir en nuestra tabla un valor negativo, como son los gastos en gasolina, dicho valor aparezca en color rojo y no en color negro con el signo negativo.

Introducción de datos numéricos

¿Cómo hacer que los valores negativos en Excel aparezcan en rojo?

Para que los datos negativos tomen el color rojo, solo hay que cambiar el formato dentro del formato moneda. Estos son los siguientes pasos que puedes seguir:

Te sitúas sobre el rango I2, en el cuál aparece el valor -15,00€. Situado sobre esa casilla nuestro ratón, tenemos que presionar el botón derecho del ratón y se desplegará un menú de opciones.

Modificar el formato de celdas

Del menú de opciones que aparece, seleccionamos “Formato de celdas…”

Seleccionar formato celdas

Tras haber presionado con el botón izquierdo del ratón, veremos un nuevo cuadro de dialogo con el título de “Formato de celdas”.

En este caso, solo nos fijaremos en la pestaña de “Número”. Dentro de categoría, dejamos seleccionada la opción de Moneda, pero tenemos que modificar la selección de Números negativos. Allí, seleccionamos la segunda opción, que es en números rojos sin signo negativo.

Selección del valor numérico en rojo

Ahora presionamos “Aceptar” con el botón izquierdo del ratón. Y ya podremos ver la modificación que hemos hecho en nuestra hoja de ingresos y gastos.

Resultado del valor numérico rojo

Lo único que queda, es asignar ese mismo formato a todos los rangos de nuestra tabla de Excel. Se puede hacer siguiendo los pasos anteriores en cada uno de los rangos que queramos aplicarlo o arrastrando. Para hacerlo mediante el método de arrastre, estos son los pasos:

Primero eliminas el valor que hemos puesto de ejemplo (-15.00€). Ya que si arrastras con ese valor, este se copiará en las celdas contiguas y nosotros sólo queremos el formato.

Aplicar el mismo formato a varias celdas

Así que borramos los valores. Nos colocamos en la esquina de la celda I2 hasta que aparezca un símbolo de un +. Pinchamos en la esquina con el botón izquierdo del ratón y sin soltar, arrastramos. Ahora, ya hemos aplicado ese mismo valor a las celdas contiguas.

Forma correcta para aplicar el mismo formato

También aplicaremos este mismo formato en la columna de Pendiente y Beneficio.

Calculo del Beneficio

Antes de empezar con la función de calculo del beneficio en nuestro Presupuesto de Excel. Lo primero que vamos a hacer es rellenar nuestra tabla a modo de ejemplo. Ya que necesitamos datos para poder calcular un posible beneficio.

Completar tabla presupuesto

Tras rellenar nuestra tabla con datos ficticios, os vamos a enseñar como introducir una función para calcular el beneficio. Lo primero que hay que saber es que nuestro beneficio se va a calcular de la siguiente forma:

Beneficio= Cobrado – (Gasolina+Peajes+Comida+Alojamiento+Otros gastos)

Ahora que tenemos claro la fórmula, vamos a introducirla en nuestra tabla de Ingresos y Gastos.

Nos colocamos en Q2. Y escribimos el símbolo “=”, para poder empezar a insertar nuestra fórmula. Ya que una vez que empezamos escribiendo el símbolo “=” en una celda de Excel, este entiende que vamos a introducir una fórmula.

Introducir fórmula en Excel

Ahora, solo tenemos que ir seleccionando las celdas (Cobro, Gasolina…); ya que no es necesario introducir datos numéricos. Para seleccionar los datos, simplemente hay que presionar una única vez con el botón izquierdo del ratón. Y la formula será la siguiente:

Fórmula correcta del Beneficio en Excel

Resultado del Beneficio correcto

Como habéis podido observar, entre la cantidad Cobrada y los Gastos hemos puesto un +. Recordad que previamente, habíamos puesto nuestros datos de gastos en negativo, por esa razón ponemos el +. Para que nuestros datos no se sumen sino que se resten; ya que menos con menos se convierte en más matemáticamente. Para que podáis comprenderlo vamos a modificar el símbolo y ver qué sucedería.

Fórmula incorrecta de beneficio

Sólo queda decir, que para que aparezcan nuestros resultados de beneficios, basta con presionar la tecla “Intro” de nuestro teclado y, de forma automática, se habrán calculado los datos de la columna Beneficios.

Crear avisos

Y ya únicamente nos queda la columna de Avisos de nuestro Presupuesto en Excel. La utilidad de esta columna para nosotros, es que nos permite distinguir, de forma muy visual, quién es el contacto que tiene una cantidad pendiente con nosotros.

Lo primero  que vamos a hacer es relacionar la columna de Pendiente con Avisos.   Para ello, vamos a utilizar la función “SI” o, también llamada en Excel, función condicional.

¿Cómo es la función SI de Excel?

El primer paso, es encontrar la función Si o condicional de Excel. Así que, sí presionamos sobre el Símbolo de función en Excelsiguiente símbolo, podremos ver todas las funciones que nos ofrece Excel.

En el nuevo cuadro de diálogo que aparecerá cuando presionemos, tendremos que buscar la función SI. La forma más rápida es introducir el nombre de la función que estamos buscando en el cuadro de “Buscar una función”.

Cuadro de diálogo funciones de Excel

En nuestro caso, la primera función que nos aparece es la que estábamos buscando. Así que sólo tendremos que seleccionarla y presionar el botón “Aceptar”.

Ahora, otro nuevo cuadro de dialogo aparecerá. Este cuadro de dialogo, nos explica los campos que hay que introducir en la función y sus significados.

Campos de la función SI

En nuestro caso, la función SI tendrá la siguiente forma:

Fórmula de nuestra función SI

Para que podáis entender su significado, está función quiere decir lo siguiente:

  • Sí en la columna de Pendiente la cantidad es igual 0, en la casilla de Avisos quiero que me ponga 0.
  • Por el contrario, si esta condición no se da. Entonces, tiene que poner el valor de 1.

Ya creada esta condición, presionaremos Intro. Y aplicaremos esta misma condición para toda la columna Avisos.

Resultado de la función SI

Ahora, vamos a dar color a la columna Avisos. Para ello, vamos a utilizar la opción de Escalas de color que nos ofrece Excel.

¿Cómo aplicar una escala de color en Excel?

Para poder aplicar una escala de color en Excel, lo primero que tenemos que saber es dónde se encuentran las herramientas necesarias. Así que nos tendremos que dirigir al botón de “Formato condicional” que se encuentra en la pestaña de “Inicio” y “Estilos”. Una vez, presionado el botón de “Formato condicional” seleccionamos escalas de color y elegimos formato.

Escalas de color para Excel

Tras haber seleccionado el formato, habrá que ir al administrador de las reglas. Lo podéis ver en la imagen anterior. Se encuentra como última opción del botón de “Formato condicional”.

Administrar escalas de color

Cuando presionemos sobre “Administrar reglas”, nos aparecerá un nuevo cuadro de diálogo y seleccionaremos la regla que queremos administrar. En nuestra hoja, únicamente aparecerá una una opción a seleccionar para administrar sus reglas. Así que elegimos Escala de color escalonada y presionamos el botón de “Editar regla”.

Seleccionar nuestra escala de color

Una vez, dentro del cuadro de diálogo de Editar reglas. Lo primero que haremos será modificar el Estilo de formato. Ya que sólo queremos que nuestra escala tenga dos colores, uno diferente para cada valor (0 y 1). Allí, elegiremos escala de 2 colores.

Cambiar a escala de dos colores

Completar cuadro de diálogo de escala de colores

Finalmente, presionamos el botón de Aceptar. Y volveremos a este cuadro de diálogo, en él presionaremos Aplicar y Acepta:

Aplicar y aceptar edición escala de color

Únicamente quedará aplicar este mismo formato a todas las celdas de la columna de Aviso arrastrando y ya tendremos nuestra tabla de Excel completa.

Resultado de nuestra escala de color

Este ha sido nuestro tutorial de Excel, con plantillas y vídeos. En todos ellos, os hemos explicado paso a paso como hacer una Agenda o como crear, en este caso, un Presupuesto en Excel de Ingresos y Gastos. Además, también habrás podido aprender a utilizar herramientas de Excel que hasta el momento desconocías o no sabias como utilizar. Por ello, todos estos tutoriales de Excel, son de gran utilidad en la vida cotidiana, empresarial o estudiantil y te pueden sacar de más de un apuro.

Si te ha gustado este post, te animamos a que te descargues GRATIS la plantilla de presupuestos en excel y que compartas el post con tus amigos en redes sociales.

A partir de ahora, podrás disfrutar de nuevos tutoriales relacionados con otras aplicaciones en nuestra sección de Tutoriales en el Blog de Movesimo.

Autor del Post: Arantxa Soria

Tutorial: Presupuesto en Excel


3 Voto/s, Promedio 5.00 de 98

¿Te ha gustado el post? Déjanos tu valoración:

2 pensamientos en “Tutorial: Presupuesto en Excel

  1. Pingback: Tutorial Google Drive, tus archivos en la nube | Blog Movesimo

Deja un comentario

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

Puedes usar las siguientes etiquetas y atributos HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>