Join Curt Frye for an in-depth discussion in this video Creating an Excel table to track cash on hand, part of Creating Business Budgets in Excel 2007.
One of the most basic budgetary tasks that you can undertake when you run a business is to track your cash on hand. For better or worse, that's most of the budgetary analysis some small business do. However, Excel gives you the tools you can use to analyze your Cash on Hand much more effectively than in previous versions. In Excel 2003, Microsoft introduced the list, which enabled you to manage tables of data more efficiently. In Excel 2007, these list objects became Excel tables, which are much more useful when performing budgetary analysis in Excel.
First, I'll show you how to create an Excel table based on an existing data list. To create a table, first you layout your data in list form and it helps if you have headings on each column that are formatted differently from the rest of the data. So for example, here I have a Date, Income, Expense, and Balance and they're all centered and displayed in bold. That way Excel can distinguish those headers from the remainder of the list. Also there's no extraneous data around the list.
In other words, this column, this row, this column, and the row above the table or the soon-to-be table are all blank. That way no extra data will be included. To create the table, I click any cell in the list and then on the Home tab click Format As Table and select my format of choice. I'll use this one. In the, Format As Table dialog box. I verify that Excel has identified the data correctly.
My table does have headers, that is these values here, and click OK, and Excel creates my table. This table contains income and expense data, taken at increments throughout the year. I have my starting balance, which I typed in just as a regular number and then the next formula takes the value above it, E4 in this case is the starting balance, and then it adds the value in the Income column, subtracts the value here in D5 from that and then puts the total in cell E5 and the formula continues down the table, E5, E6 and so on.
If I want to add a new row to a table, I can click the final cell in the final row, press Tab, and add to it. So now let's say that I had an expense of $10,000 and let me see the next date would be 10/1/2010 and my expense is 10,000. When I add that in, the Excel table updates the data so that this formula is now $97,742. Now let's say that I wanted to analyze my average daily balance.
That's often useful to see how your cash flow is progressing. If I wanted to find the average daily balance, I would need to find the average of this column here, the Balance column. To do that I can add a total row. To add the total row, I click any cell on the table and then on the Design Contextual Tab select Total Row. When I do, Excel adds the total row, although in this case the value is a sum. It is all of these values added together.
That's not meaningful information, so I'm going to change that value to an Average. Click the cell and select Average from the list of functions that appears. When I do, I find that my average daily balance is just over $91,000. Excel tables help you summarize your data effectively and provide a base for more advanced analysis using PivotTables, which we will get into later in this course.
- Tracking income and expenses by category and contract
- Using balance sheets
- Designing worksheets to assist decision making
- Creating income statements
- Calculating loans payments and interest
- Creating cell references to other worksheets
- Summarizing data in a chart
- Building alternative budget scenarios