Explore the tabs along the bottom
Accounting Excel Template : Tabs (also called Worksheets)
- The first two tabs (also called worksheets) About and Links pages contain helpful links to the Beginner Bookkeeping website. When you first download and open the template, you might get an Excel message about automatic updating of external links - it is these links Excel refers to - it is safe to Enable the Content.
- The third tab is the Tips sheet which gives you quick tips for using the template.
- The Examples page gives you an idea of how to enter transactions into the cells of each month of the template.
- The MonthsHeaders tab is where you enter the order that you require your months to be depending upon your financial year.
- In the AccountsHeaders you can rename the headers and account names to what you want to have in the template – so you can name five different streams of income, and 20 different expense types. These accounts are also known as Ledger Accounts and are found on the Chart of Accounts.
- The next 12 worksheets are months Month1 to Month12. You can rename them to suite the order of the months in your financial year. These monthly worksheets are where the action happens! Here you will enter all your daily transactions of money received and money spent.
- There is a Reconciliation page where you can perform a reconciliation for the month you are working in if the closing balance for the month does not match your bank account balance. This will be due to things like bank fees or interest, or checks not yet presented and so on.
- Then you have the Inventory sheet where you can enter your opening and closing balances based on the Periodic method of Inventory. I have a free Excel Inventory Template which will help you keep track of your daily stock levels and calculate what those opening and closing balances should be.
- Next is the Profit and Loss report (aka Income Statement) – a very important report to tell you if your business is making a profit or a loss.
- After that is the Balance Sheet which gives you a snapshot of the financial position of your business.
- Finally, you get a Sales Tax Report so you can see the total Sales Tax figures for each tax rate on your Income and Expenses.
Accounting Excel Template : Example page
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.
Accounting Excel Template Example Page 'Money In' Section
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.
Example Page 'Money Out' Section
how the sales tax works in this template
Sales Tax Columns
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.
Sales Tax Report
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.
You must first refresh this page so that the report includes any new data you just entered. Instructions on how to refresh it are in the worksheet.
Accounting Excel Template
Sales Tax Report
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).
Months Headers Page
Accounts Headers Worksheet
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 re-name 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.
When you enter information into the Business Name cells and the Heading Cells it will update all the same information in each month Month 1 to Month 12.
The Monthly tabs
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.
Accounting Excel Template Month1 Worksheet
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
Practice a pretend transaction
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.
- Enter a date, a name of a client or vendor, a description, a reference (invoice number or check number) and an amount in the Income transaction column
- select a pretend sales tax rate (you will have had to enter it on the Accounts Headers page first)
- enter the amount that has to be allocated into one of the income columns
- scroll to the right and see that the Total Money Received In column (Column ‘U’) has updated as well as the ‘Funds Available column (Column ‘BF)
- check out the P&L and Balance Sheets to see what has occurred
- once you’ve played around a bit and become familiar with the layout, you can delete any pretend transactions you have entered (just be careful not to ‘delete’ the formulas in the columns that have formulas.)
Inserting Rows or columns
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....
If inserting columns sounds too hard you could ask yourself “do I really need to insert new columns?” Are you instead able to combine certain income or expense types into one account column that already exists in the spreadsheet?
For example, stationery or printing costs could be combined into one column called “Printing & Stationery”. Vehicle repairs and fuel purchases could be combined into “Vehicle Costs”.
the opening and closing balances
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).
The closing balance in Month 12 is not linked to the opening balance in Month 1 – it would not make sense to link December 2019 to January 2018 as you can’t go backward in time, unless you have a time machine!
starting a new financial year
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.