Keeping track of your expenses in life and business (Excel Template)

excel tracking expenses

Subscribe to our newsletter here, so you don’t miss out on the latest advice and articles on business.

Every day we spend money on food, accessories, clothing and other recreational activities. Entrepreneurs spend money on staff salaries, purchasing stock, website development, etc. At the same time, there is (hopefully) income and sales that you need to account for as well. As we all know, keeping track of your cashflow means having a healthy business. More than that, whether in personal life or in business, keeping track of money spent and earned means no unpleasant surprises in the future. So how do you do that?

For starters, if you are a small/medium sized business, then I would definitely recommend Quickbooks (and no, they are not paying me a single $ for this). This is the most widely used tools by small businesses, and it is pretty great.

However, if you don’t want to spend money on a third-party tool, or you prefer to have something at hand that you can adjust/edit yourself, then excel is the answer. I learned this hack back in 2018 from one of my friends who works at one of the top accounting firms in the world (no name dropping), and I hope you find it useful.

Note on the template

The initial template is available here. Download the template before proceeding, so you can make changes/view its details as you read this guide. (If you want to receive a bunch of other super cool and useful stuff on a regular basis, you can always sign up here).

The question is: what do you do with this file? Good news – it looks more complicated than it is. Going forward even if you don’t understand how to create it, you can always use the template as a starting point. Simply replace the values and codes according to what you wish to track and you will be good.

Understanding the template

A screenshot of a social media post

Description automatically generated

Beg.balance row: your bank balance that you start with. Just input the initial value that will adjust itself going forward according to money taken in/out.

Date: When was the payment processed. For expenses, I personally like to record it as the date when the payment was made, but you can also record it only when the sum leaves your bank account.

BT: This is the type of payment used. Nowadays it will most likely be just your card, but you can also input “CASH”, or “CARD 2”, in case you want to sort through payments later according to where they originated.

Paid out: any payments made by you.

Paid in: any payments made to you.

Balance: Track your balance as transactions take place. The last value in this column is your current balance (cash + bank account).

Codes: This is where you categorise different sources of income/expenses. I recommend colour coding these, as it will be a lot easier to find the right column later. As you can see there is a column AND a row of the same codes. I will explain this later.

At the bottom of each code column you will find the total = spent + received for that particular category.

As an example, your FREELANCE code can include payments that have anything to do with your freelance work. In the “Paid out” column, $9 was spent on a coffee that you bought for a potential client on the 2nd of February. You also got paid $399 for your work on Fiverr on the 13th of February. As the result your total for February for FREELANCE is $390.

Tabs at the bottom

A screenshot of a cell phone

Description automatically generated

Use the tabs at the bottom to split up your timeline and payments into months (or weeks/years). In this example the starting month is February 2020, so as we move to the right, next table will be March 2020, April 2020, and so on…

Note for your convenience: If you want to copy-paste the template from one tab to another do the following:

1. Select all cells in a spreadsheet/tab where the template is already present (in this instance February 2020).

2. Go to the next tab (March 2020). Right-click A1 cell. Select option to “Paste Special”  “Keep Source Column Widths”. That way your new tab spreadsheet will look identical to the first one, and from there you can edit any information going forward, without having to recreate the template from scratch.

Understanding how the template is made

The Balance column

While this column starts with a set value (for example, $138.39) we want this value to upgrade as payments come in and out. If your balance column is column F, and your starting balance is located in cell F2,this is the formula =F2-D3+E3.

F2 represents the updated value.

D3 represents any payments taken out under Paid Out column.

E3 represents any payments that were brought in under Paid In column.

For any Excel beginners – if you wish for this formula to automatically update in the next cells in this column, enter formula in the first cell (here it’s cell F3). Select the cell, then grab the bottom right corner of the cell and drag it down through all the cells in the column where you wish for the formula to repeat itself.

The Codes

Make sure that all of the codes that you list under the Codes column ALSO appear in the first ROW of your spreadsheet.

Every INDIVIDUAL COLUMN with a code in it will have a standard formula. This formula is key to tracking your expenses/income for each category of payments. Every time you enter this code under Codes, you will see the value reflected in the relevant column and added to the total at the bottom.

Ex Formula) For when your Travel codes are located under G3 and H1. Formula: =IF($G3=H$1,$E3-$D3,””).

The IF formula allows you to return a value based on whether certain conditions are satisfied. Here the way to read it: If Cell G3 is the same as H1 (so if the codes match), calculate the difference between Paid In and Paid Out amounts. That way any time you enter the code in column G, other columns scan it to see if there is a match, and then the column with the same code will record this payment.

The “totals” row

In our example, row 20 is the Total calculated for each column (each code). All you need to do is select the cell at the bottom of the column –> Formulas –> Auto-sum –> drag the top right corner of the selected box to the very top.

To copy the sum formula to all adjacent cells in that row:

select the cell with the formula –> copy –> select all cells in that row, where you want to copy the formula to –> paste.

Sorting by code

Last but not least. Select Data –> Filter, and now you will be able to sort all payments by code, type of transaction, amounts, etc. Simply select the column that you are interested in, click on the arrow on the right-hand side of the cell and select the values.

Voila, you are all good to go!

Any questions you have about the above, please feel free to get in touch with me at boris@rykovmedia.com.

Subscribe to our newsletter here, so you don’t miss out on the latest advice and articles on business.

Learn how to diversify your revenue streams and monetise your personal brand from none other than Barack Obama here.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.