When you start you probably do not have a bookkeeping program or you are a fan of Excel or like to keep track of your bookkeeping yourself. Anyway with this simple template (Dutch version) you can do your accounting in Excel.
Building of your Administration in Excel
The administration template in Excel consists of a tab for each month of the year and a tab called VAT. On the VAT tab your turnover and VAT per month are calculated, more on this later. And on the tabs of the months you do your actual administration. Let’s start with that
Income and costs
We take the tab Jan as an example. In the first orange column, we enter the income and the costs in the 2nd orange column. Every income or expense gets a separate line in this tab.
So the first income comes on line 5. At client A5 you enter the name of the client. At B5 you set the description of the assignment. At C5 the category of type of work. At D5 comes the date that the invoice has been paid. Depending on when you fill this in, it may be that you only fill in the date later. I fill in this schedule as soon as I issue an invoice, but leave the date field empty until the invoice has been paid and then enter that date.At E5 comes the amount that the client owes you exclusive of VAT. At F5 comes the VAT they owe you. In G5, the system automatically counts the total amount they owe you (amount excluding VAT + VAT). This way you enter all your income for January.
On line 9, the system gives you total income excluding both VAT (E9) and VAT (G9). And in F9 the total VAT that you have to pay this month.
You enter the costs in the same way. In A13, the supplier from a product or service. In B13 the description of the service / product. C13 the category of the product or service. You can think of: travel costs, network costs, study costs, operating costs, etc.
In D13 comes the date of purchase product or service. In E13 the amount excluding VAT and in F13 the VAT you paid for the product. In G13, the amount excluding VAT and VAT is added together. This way you enter all your costs for January in this case.
At the end, the total costs for you in line 28 is given. In E28 the amount of costs excluding VAT, F28 the VAT you have to pay and G28 the total amount including VAT.
VAT and Turnover per quarter
On the VAT tab, everything is automatically calculated when you enter the tabs of the months. In Column B you see the VAT you paid, in column C the VAT received. In column D the difference between received and paid VAT. This is the amount that you have to pay or receive in VAT from the tax authorities.
In Column E you see your gross sales excluding VAT. In column F the quarterly expense Excluding VAT. And in Column G you gross profit before tax per quarter.
If you turn a small turnover with no more than 1345 in VAT to pay, you can use Column I, J and K to get VAT back.
If you have any questions about this administration system or want to expand it, you can contact me. Other Excel templates can be found on the Templates pagina. Read more about Excel in my other Excel blogs. Improve your Excel skilss with my Excel lessons.