Accounting Excel Template

Accounting Excel Template with Sales Tax

This free Accounting Excel Template is perfect for tracking your business income, expenses and bank balance and for viewing financial reports which update automatically when you enter your transactions into each month. 

This template has a similar layout as our 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

You can look at screenshots further down this page before deciding if you want to download it.

Accounting Excel Template DownloadSelect / click on your month below

December

January

February

March

June

September

One
To download the template, select (click-on) the financial year-end month that applies to your business. If your financial year-end is different to the months listed here, please contact me.

If your financial year ends in December, download the December template. If your financial year ends in April, download the April template.   And so on. 

If you don’t know what your financial year end is then use the December template.  In the end it does not really matter which one you use.  

Two
Once you have selected your month, it will automatically download into the Downloads folder on your computer. Go and check that folder to access the downloaded template.

Three
Open the template and save it by going into ‘File’, ‘Save Copy’ in Excel and give it a name of your choice and save it to your digital filing system.

More detail on what this accounting excel template offers

If you want a template without sales tax you can try our Excel Cash Book which is also free.

  • It includes sales tax columns for both income and expenses – simply select the sales tax percent for each transaction that applies to you depending on what country you live in. 

  • You can run a sales tax report for the months of your choice. 

  • In each month you can enter transactions for two bank accounts and one credit card, for example:-
  1. a checking account
  2. a savings account
  3. a business credit card

This saves you having to have three separate workbooks for each bank account | credit card.

This Accounting Excel Template is just like a cashbook and the money received and money spent transactions should be a reflection of what happens on your bank account. 

step by step using this template

  1. Download and save (the download links are up above).
  2. Explore the 20 different tabs / worksheets
  3. Look at the sample page
  4. Learn how Sales Tax in this template works
  5. Name your accounts on the Control tab
  6. Enter a pretend transaction in the first month 
  7. Check out how it affects the reports
  8. About inserting more rows and columns
  9. How the Opening and Closing Balances work
  10. 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 we have entered there. Take care not to accidentally delete them.

Explore the tabs along the bottom

Accounting Excel Template WorksheetsAccounting Excel Template Tabs/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 second tab is the Sample page which gives you an idea of how to enter transactions into the cells of the template.

  • The Control tab is where you update the names of all the accounts 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 Jan to Dec (the order of the months will depend upon what Template you downloaded for your financial year-end). These monthly worksheets are where the action happens! Here you will enter all your daily transactions of money received and money spent.

  • Then you have the Inventory sheet where you can enter your opening and closing balances. We 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.

sample page

At the top of this page are the headings which apply to both bank accounts and the credit card in each month.

In the screenshot below you can see:-

  • Three Details columns – Date, Description and Reference - these are the same for both Money Received and Money Spent

Money In Columns

  • Four Sales Tax columns for the income
  • Three income columns (there are more on your working pages - unused columns have been hidden on this example page to keep it smaller.
  • Four Other Funds received columns
  • Total Money Received In

...then, within the template, scroll to the right-hand side to see the columns that relate to the Money Out section - see screenshot below.

Accounting Excel Template Sample PageAccounting Excel Template Sample Page 'Money In' Section

Money Out Columns

  • Four Sales Tax columns for the expenses 
  • Four Cost of Goods Sold columns 
  • Four Expenses columns (there are many more on your working pages - unused columns have been hidden on this example to keep it smaller) 
  • Five Other Funds Paid Out columns and 
  • a Total Money Paid Out column
Accounting Excel Template Money OutAccounting Excel Template Sample Page 'Money Out' 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.

how the sales tax works

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 45 different sales tax rates. 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.

Every effort has been made to include sales tax rates from every country within this Accounting Excel Template but if yours is not on the selection list,
please Contact us so we can update the spreadsheet.

Sales Tax Report

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.

This 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 ReportAccounting Excel Template Sales Tax Report

NOTE: we 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. We do however, teach you how to enter the rates and values into this template and to view the report.


control worksheet

Accounting Excel Template Control AccountsAccounting Excel Template Control

Here, you enter the name of your business and the names of the headings (aka 'accounts)

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

You can also re-name the pink or yellow shaded headings that already have names if you know what you are doing and want a different name for them. 

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.

Note: You cannot use this control 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 Jan to Dec. 

The Monthly tabs

The monthly worksheets are where the day to day action happens!

For this explanation, we will use the January month. Below is a screenshot - we 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 Blank Version of JanuaryAccounting Excel Template January 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 Control page – see Cell F11.

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 light orangy-pink (there must be a proper name for that color, but I don’t know!!)


Practice a pretend transaction

Enter a pretend transaction into the first bank account so you can see what happens on this page. 

  • Enter a date a Description an amount in the Income transaction column 
  • select a pretend sales tax rate
  • 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 our videos to see how to insert rows and columns in Excel; this is based on our 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 balance in December is not linked to the opening balance in January – it would not make sense to link December 2017 to January 2017 as you can’t go backward in time, unless you have a time machine!

In the template ending in the month of December, the closing bank balances in Rows 61, 116 and 171 in January are linked to the opening bank balances in Rows 9, 64, 119 in February. 

The February closing bank balances are linked to the March opening balances. 

The March closing bank balances are linked to the April 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 December, it is time to start a new template for the new Financial Year (discussed below).

Note: This works much the same for the templates/year-ends March, June and September too.

starting a new financial year when your first one is finished

When December comes around (or whatever your year-end is), you will start a new accounting excel template for the new financial year (by downloading a current version of this Accounting Excel Template under Downloads near the top of this page.

You will manually enter December’s closing balance from the old accounting excel template into the opening balance of January in the new template.

If you like using the Balance Sheet, you must also manually enter in the December balances onto the new Balance Sheet in the Opening Balances column.


You are here:

Accounting Excel Template


GET FREE UPDATES

Bb_resized_even_smaller

Subscribe to get our latest content by email.

We won't send you spam. Powered by ConvertKit



Facebook Comments

Have your say about what you just read!
Leave me a comment in the box below.

Recent Articles

  1. Free Accounting Excel Template

    Sep 03, 17 08:51 PM

    Accounting excel template for tracking income and expenses with sales tax and reports

    Read More

  2. Free Excel Inventory Template

    Jun 28, 17 10:25 PM

    Download free Microsoft Excel inventory template. Learn how to update bookkeeping software with inventory balances.

    Read More

  3. Best Business Bookkeeping Software Choices

    May 09, 17 04:23 AM

    What is the best business bookkeeping software for your business? We compare Xero vs QuickBooks vs Sage vs free software and help you narrow down which one to use.

    Read More

Back to Top : Accounting Excel Template