This free Accounting Excel Template is perfect for tracking your business income, expenses and bank balance, with or without sales tax, and for viewing financial reports which update automatically when you enter your transactions into each month.
This template has a similar layout as my very popular Excel Cashbook, but in this template you can record sales tax and track the balances of two different bank accounts and one credit card within each month.
This template can currently track one type of sales tax, example GST or HST or VAT. It cannot be used for two types of sales tax, example GST and PST.
Further, if for example you use GST which also involves Zero Rated or Exempt tax codes etc, this is fine - you can incorporate these, and you can use different GST rates if required - example 5% and 7%.
Note: if you insert new rows into the monthly tabs, the sales tax report will not be accurate because the source data has not been set up to include new rows. You must manually unhide the Sales Tax Data sheet (there is no password to unhide it), insert the number of new rows required in the correct month, and enter the formulas (copy from ones already in there) into those rows to pick up data from the monthly tabs.
Here are printable PDF instructions for updating the sales tax report :
When you look through the workbook, there are many cells that have a 0.00 value showing. These cells have pre-set formulas that I have entered there. Take care not to accidentally delete them.
This shows you how to enter transactions on a day to day basis and includes payments out, payments in and transfers to and from the other bank account and credit card.
Working with the sales tax columns is explained in more detail further down on this article.
You will see that the account headers have been given a unique name.
The Total Money in or Out columns calculate the total automatically, and the monthly totals along the bottom for each bank account are also calculated automatically.
This column will show you how much money is in your bank accounts or credit card on any given day after you have entered your daily transactions.
It calculates automatically so you don’t have to.
The Funds Available formula is the bank balance from the row (day) before, plus total money received in, minus total money paid out.
Income Transaction Amount
In this column you will enter the full amount of each transaction including sales tax.
Sales Tax Rate
In this column you can click on the selector arrow (this becomes visible when you click on the cell), and a list will pop up with the sales tax rates that you must have already entered into the AccountsHeaders sheet. Choose the rate that applies to your transaction.
Sales Tax Amount
The cells in this column have a formula that will automatically calculate the sales tax amount based on the information you allocated in the first two columns discussed above.
Allocate to Income or Other Funds
The cells in this column also have a formula that will automatically calculate the amount excluding sales tax. You must manually enter the resulting amount into one of the Income or Other Funds Received columns.
It works the same with the Sales Tax for the expenses columns to the right of the spreadsheet.
The Accounting Excel Template sales tax report is in the tab next to the Balance Sheet.
Once you have entered your transactions into the current month you are working on and all your sales tax rates, go to the Sales Tax Report.
You can select the month or months totals that you want to view by clicking on the small filter icon in Cell D4 and Cell G4 – select the months of your choice and click OK.
NOTE: I do not teach how to process sales tax in your country because the sales tax process is different in every country. If you do not know what your sales tax rates are, you need to search the internet or ask your friends and business associates. I do however, teach you how to enter the rates and values into this template and to view the report.
The template default shows January to December - so Month1 is January, Month2 is February etc.
If your financial year starts in June, for example, you would change it so that Month1 shows as June, Month2 shows July etc.
These are linked to every monthly sheet and to the Profit and Loss and Balance Sheet reports.
The names of the monthly tabs cannot be automatically updated in Excel without complicated coding, but once you have set your months above, you can manually rename them by right clicking on the tab, typing the month and enter. Make sure the tab month name matches the month within the worksheet (which takes the name of the month from the MonthsHeaders sheet).
Here, you enter the name of your business and the names of the headings (aka accounts).
These are linked to every month and to the reports which will update automatically with your changes.
You can rename the headings titled ‘Head 1, Head 2’ and so on through to the expenses side (not seen in this screenshot) – these are your ‘accounts’. If you earn money from consulting, then you could rename Head 1 ‘Consulting Fees’. If you also earn money from selling products, then you could rename Head 2 ‘Product Sales’.
To rename any of the accounts, click in the header cell and type away, then hit enter on your keyboard. This will write-over the current wording and replace it with yours.
Enter in your Sales Tax Rates.
Note: You cannot use this page to update the font, or column sizes or background shading for all the monthly tabs. You have to go into each monthly tab to do that.
The monthly worksheets are where the day to day action happens!
For this explanation, I will use the Month1 month. Below is a screenshot - I can't fit the whole worksheet into the image so you can just simply download the Accounting Excel Template to look at it properly.
You will see in red writing it says ‘Bank Account #1’ – you can rename this, if you haven’t done so already, to a name of your choice by going on the Accounts Headers page.
Explore this page by scrolling down and scrolling right so you can see all there is to see.
Scroll all the way down to Row 172 – this is the final row and is the total of all three bank/credit accounts combined. Do not delete this row because it is linked to the P&L and Balance Sheet.
Bank Account #1 is color coded blue
Bank Account #2 is color coded green
Credit Card Account is color coded pale orange
Save a copy of the accounting excel template to practice entering pretend transactions so you get the feel of it and see what happens in the totals columns that have formulas.
If you need more rows or columns you can insert some.
Inserting Rows – Watch my videos to see how to insert rows and columns in Excel; this is based on my Excel Cashbook but is similar for this Accounting Excel Template. Make sure you copy and paste formulas from one of the original rows into your new rows that you have inserted. Make sure the totals along the bottom of the spreadsheet include the new rows – you can do this by updating the formula.
Inserting Columns – When adding more columns, you are in effect adding new accounts to your spreadsheet. The totals of these columns are linked to the Profit and Loss Report and Balance Sheet. When inserting new account columns, you need to link them to the P&L and the Balance Sheet manually – this requires inserting new rows into each report and linking the cells in each month on these reports to the totals on each monthly tab....
The closing bank balances in Rows 61, 116 and 171 in Month1 are linked to the opening bank balances in Rows 9, 64, 119 in Month2.
The Month2 closing bank balances are linked to the Month3 opening balances.
The Month3 closing bank balances are linked to the Month4 opening balances… and so it goes with the remaining months.
This carries the correct bank balance through the template… and they are linked to the Balance Sheet.
Once you get to the end of Month12, it is time to start a new template for the new Financial Year (discussed below).
When your last month comes around, you will start a new accounting excel template for the new financial year (by downloading a current version of this Accounting Excel Template.
You will manually enter Month12's closing balances from the old accounting excel template into the opening balances of Month1 the new template.
If you like using the Balance Sheet, you must also manually enter in the Month12 balances onto the new Balance Sheet in the Opening Balances column.