Build a cash tracking worksheet

- [Instructor] One of the simplest but most useful Excel worksheets is the cash tracking worksheet where you keep a record of income and expenses as well as your current cash balance. In this movie, I'll show you how to set up this type of worksheet. My sample file is the cash tracking workbook, and you can find it in the chapter three folder of your exercise files collection. I've created an Excel table and it has six columns and those are date, income, expense, category, description, and balance. The date allows us to track when a transaction occurred, income or expense is money going in or money coming out, category is a classification that allows us to group our transactions for sorting and filtering later, then also description, which gives us more information if we want, and balance gives us a running total of cash on hand. To start I will enter a starting balance, but rather than using today's date, I'm going to use a date in the past. That way, I'll make sure that it doesn't conflict with any of the transaction dates that we add later. So in cell A2, I will add a date in the past, let's say that's January first of 2010, then I will tab all the way over to, actually I'll go to the description, and type "starting balance" then press tab, and let's say that we start with a balance of $45,000. Now I can press tab, and I get another row in my table. Next I'm going to create a row with a formula that updates based on income and expense from the previous balance, so I'll do the next day, 1/2/2010, then I will tab over to column F, which is the balance column, and I will add the balance from the previous row to any income and subtract any expense that we have. So I'll type an equal sign, and then the balance is in F2, I'll add any income from the entry that we're creating, so that would be in cell B3, and note that because the cell is in the same table row as the cell where we're creating the formula, we get the reference at income and that's because we're in the income column. And then I will subtract any expense. And that is in C3, and you'll see we get the at expense indicator. So I'll press tab to create a new row, and I see that my formula was created correctly. Please do note, though, that the formula wasn't copied down. So whenever you add a new entry, we'll need to copy the formula in the balance column down to cover all the new entries. So let's start, just do a couple of new entries. Let's say that on August 2nd, 2017 we received income through the business of $15,000, category of sales, and for description, we'll say, "speaking" and then I won't worry about the balance yet because I'm going to add a couple more rows, so I'll press tab, and let's say that on 8/5/2017 I had an expense of $3,000, training, and let's say that it was a workshop. I'll do one more row, so I won't worry about the balance yet, and say that on 8/8/2017 I had an expense of $600, category travel, and plane ticket, and press tab. So now I don't have my balance in place yet but what I can do is to click cell F3 which contains my formula, and when I double click the fill handle, and I can tell my mouse pointer is in the right position when it changes to a black cross hair, I'll double click and we copy the formula down. And you can see that my worksheet has updated based on the data in each of the rows. I was able to copy the formula down with no problem. But now let's say that I want to delete a row. If I want to delete a row, I can go to the header of the row, but note this is not the worksheet header, this would be the table header for each row, so I'll move to the right, and you can see that the mouse pointer has changed to a right pointing arrow but rather than being here, on the worksheet row header, I'm within the table. And you can see there's a transition where you get a four pointing arrow. So I'm here, I will right click, and from the shortcut menu I will delete, table rows, it's gone, and you can see that the formula is now giving a reference error. That's because I deleted one of the cells to which it was referring. To fix that, I will just click cell F4, double click on the fill handle, and it's copied down. As with all other Excel tables, I can sort, filter, and change the data as I want. You'll find this type of cash tracking worksheet to be very useful. It's a simple tool, but the model that I've created here is one you can use in many different situations.