Accounting Excel Template

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.

Download and save the Accounting excel Template

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%.

1. To download the template, select (click-on) the blue button. This can be customized to use with any financial year-end.
2. Once you click on the button the template will automatically download into the Downloads folder on your device. Go and check that folder to access the downloaded template.
3. Open the accounting excel template and save it by going into ‘File’, ‘Save Copy’ or 'Save As' in Excel and give it a name of your choice and save it to your digital filing system.

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.

accounting excel template Features

• It includes sales tax columns for both income and expenses – go to the Control page, enter your sales tax rates and then on the monthly tabs simply select the sales tax percent from the drop down box for each transaction that applies to you depending on what country you live in.  The drop down box is linked to the Rates box on the Control page.
  
• You can run a sales tax report (using Pivot Tables) for the months of your choice. 
  
• In each month you can enter transactions for two bank accounts and one credit card, for example:-

Extra Helpful Instructions

Here are printable PDF instructions for updating the sales tax report :

1. PIVOT TABLE DATA SOURCE LINK
   Often, when an Excel file containing a pivot table report is renamed, the pivot table information in the new file will still point to the original file data and not the new file data, so the link needs to be redirected to the new file. These instructions will tell you how to update it. 
   >>DOWNLOAD INSTRUCTIONS
   
   
2. EXTRA ROWS IN DATA SOURCE
   The Sales Tax Report gets its information from one data source within the template. When you insert extra rows into each month because you have more transactions than the available rows in the template, the data source does not automatically update with these new rows. You must manually update the data source worksheet to include these new rows. These instructions will tell you how to do that. 
   >>DOWNLOAD INSTRUCTIONS

step by step using this template

1. Download and save (the download link is above)
2. Explore the different tabs / worksheets
3. Look at the Tips and Examples pages
4. Learn how Sales Tax in this template works
5. Enter the months of your financial year in the months headers page
6. Name your accounts on the accounts headers tab
7. Enter a pretend transaction in the first month 
8. Check out how it affects the reports
9. About inserting more rows and columns
10. How the Opening and Closing Balances work
11. Starting a fresh template in your second financial year.
    

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.

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

Funds Available

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.

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.

MonthsHeaders Sheet

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

AccountsHeaders worksheet

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 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 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....

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).

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.

Site Sponsors

From The Blog

• 

1. How is Sales Tax Calculated

   Jul 25, 24 01:07 AM

   
   How is sales tax calculated? The main formula is: the price multiplied by sales tax rate, see more formulas, lots of examples, get free Excel calculator.

   Read More

2. What is Sales Tax

   Jun 05, 24 11:07 PM

   
   What is sales tax? It is a consumer tax charged to customers and paid on to the government by the business making the sale. Technology can help automate this process.

   Read More

3. Types of Bookkeeping Accounts Used To Organize Income and Expenses

   Nov 21, 23 08:10 PM

   
   The main types of bookkeeping accounts are assets, liabilities, equity, revenue and expenses which can be further classified into real and nominal accounts

   Read More