Sometimes we would like to know a little more about the use of Excel , especially when we have to do some task that at first glance may seem simple, but it ends up becoming tedious due to lack of knowledge. If you are a self-employed person or a businessman and you still see yourself making your monthly invoices in Word and with the help of the calculator , we are going to give you a cable.
You just have to follow the steps in this tutorial to be able to create your own template with which, just by adding the billing information, the calculations will be made automatically and thus avoid having to do them every month.
1- The first thing you have to do is open a new document in Excel . Once we have the document open we will have to add the basic billing information : our name and surname or fiscal name of the company, ID or CIF and full fiscal address. In another section we will publish the same data but of our client, and finally, the invoice number and the invoice date .
2- Next, we will add the concepts that we are going to invoice, the quantities and the unit amounts. It is important that we do it like this so that we can add the formulas later. We will also add a column of totals and a box "total" that will add the total of the amounts and then apply the different taxes.
3- Now we are going to add the corresponding formulas to get our gross total sum (without taxes applied). What we have to do is multiply the amount of the concept by its price so that it gives us the amount. To do this we will write in the cell corresponding to the total of each concept the following formula = (number of the quantity cell * number of the price cell) , we press enter and the product formula will be recorded, thus every time we write an amount and a price will automatically be multiplied in the corresponding total box.
We have to repeat this operation for each concept row that we have. Let's see the example:
The asterisk is the multiplication symbol in Excel.
Once we have applied the product formula in all the cells that we need, we will add one in the "total" cell that adds up all the total quantities. To do this, we will position ourselves in the cell in which we want the result to be applied and we will write in it the following formula = SUM (total cell1 + total cell2 + total cell3) . We will add as many cells as necessary and remember that it is not necessary to look for the cell number, just click on it so that it is automatically written in our formula. Once we have closed parentheses, press enter and the cells will always be added automatically.
4- Now that we have calculated the totals of our concepts, we are going to add the formulas corresponding to the taxes applied . In the case that you are going to see below, we are going to show you how to apply the two fundamental taxes on invoices: VAT and personal income tax. It is important to remember that the percentages are not the same for all cases, so you have to make sure which ones are applicable in yours .
In our examples we start with a VAT of 21% and an income tax of 15% . We remember that VAT is added to the total amount while personal income tax is subtracted , but the formula to apply it is the same.
To calculate VAT and personal income tax, we will go to the cell that we have awarded for that amount and write the following formula = (cell number of total amount * 0.21) . That is, we mark the cell of the total amount, the result of all the totals previously added and we multiply it by 0.21. It will be multiplied by 0.21 if the VAT is 21% , if it were, for example, 10%, we would multiply it by 0.1. In the case of the IRFP we have calculated it as if we were to apply 15%, so we have multiplied it by 0.15.
5- Now we have the last step, which is to add the formula that adds the amount of VAT and subtracts the personal income tax . To do this, we will position ourselves on the cell in which we want the net result of our invoice to appear and we will write the following (total cell + VAT cell - IRPF cell) . We close parentheses, press enter and that's it, the calculation of the total liquid is now carried out automatically.
In the invoice you can also add other data such as allowances or advances , remember that all you have to do is add the formula by adding or subtracting, depending on the concept. You can also include the details of the bank account where you want it to be credited but what you should always add is your signature.
We hope this tutorial has been useful and that it makes monthly billing less arduous and complicated.