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