Creating A Personal Budget Using Excel
Assuming the user has an intermediate knowledge of Excel, creation of a powerful, comprehensive, personal monthly budget is a very easy project that should not require more than a few minutes of the user’s time, exclusive of time spent actually entering budget data. This article provides detailed instructions for spreadsheet layout, entry of budget data, and examples of how valuable budget information can be easily extracted.
The budget data will consist of income events (e.g., wages, loans, Lotto winnings, etc.) and expense events (e.g., rent, utility bills, etc.) laid out in a traditional, tabular manner, with each row corresponding to a separate income or expense budget event. Of course, each income or expense will correspond to a dollar amount, with income entered as positive amounts and expenses entered as negative amounts.
The functionality of the budget will derive primarily from combining Excel’s AutoFilter with a subtotal of the income/expense dollar amounts. The subtotal formula acts much like a basic summation, except it respects a filter. It will be shown that specific filters can be easily applied to provide a wide range of budget information, from a simple list of bills currently due to a forecast of cash on hand at any given point in time. Examples will be provided after some budget data has been entered.
The budget list will be laid out as follows: in cells B5 through K5, type the following labels, bolded and centered: Yr-Mo-Wk, Year, Month, Week, Title, Type, Period, Status, Amount, and Description. Some of these fields will be restricted to very specific entries only. Such restrictions, as well as content for all the fields in general, will be discussed below.
In cell I3, type Total Of Amount:. Right-align this text and bold it. In cell J3, type the following formula: =SUBTOTAL(9, J6:J1048576). This formula subtotals the Amount column, respecting whatever filter might be in place. Note: 1048576 refers to the last row in the spreadsheet, pertaining to Excel 2007. If the reader is using a different version of Excel, his spreadsheet may have a different number of rows, and he should change this number accordingly.
We will now begin entering, row-by-row, the actual budget data. At this time, the user should not be concerned with the order in which the rows are entered, because the list will be properly sorted later.
For budget purposes, a weekly granularity will be more than sufficient. There is no need – although it could be done – to track income and expenses on a daily basis. Therefore, by convention, each month will be defined to have exactly four weeks. Of course, for all months except February, the fourth week will be two or three days longer than the other weeks. This will require some minor modification of certain Week 4 entries, but any such additional efforts will be more than counterbalanced by the overall labor savings due to the consistency and simplicity of this layout.
Entries in the first column, labeled Yr-Mo-Wk, will be year-month-week codes that will provide additional filter possibilities. The format of these codes should become clear following an example: at the time of this writing, the year is 2010, the month is August (the 8th month of the year), and the week is 4, so a corresponding entry in the Yr-Mo-Wk column would be ‘2010-08-4. Note the apostrophe that begins the entry, which the user should not fail to type. This apostrophe is a formatting instruction to Excel to treat the entry as text, rather than as a date. It does not appear in the spreadsheet.
In cell B6, enter the Yr-Mo-Wk of the budget event. If the event occurs in the current week, enter ‘2010-08-4. Of course, if the event occurred at another time, enter a corresponding code. Entries in this column should be centered.
In cell C6, enter the Year of the budget event. Although this information may appear redundant given that it is included in the previous column, having a specific column for Year will make filtering more convenient and will expand the functionality of the budget. Entries in this column should be centered.
In cell D6, enter the Month of the budget event. At the time of this writing, the current month is August (the 8th month of the year). A corresponding entry should be in the following format: ’08-August. Note again the apostrophe that precedes the entry. When the user sorts the budget data, this format guarantees that the months will sort in the proper order. Entries in this column should be centered.
In cell E6, enter the Week of the budget event. At the time of this writing, the current week is Week 4. A corresponding entry should be in the following format: Week 4. The purpose of writing Week 4, instead of simply 4, is in anticipation of perhaps later subtotaling the budget data by Month. In a Subtotal layout, “Week 4” will look better than a bare “4”. Entries in this column should be centered.
In order to determine which week corresponds to a given date, notice that the first three weeks of any month end in multiples of seven and the fourth week ends at month’s end (EOM). Specifically, Week 1 refers to days 1 through 7, Week 2 refers to days 8 through 14, Week 3 refers to days 15 through 21, and Week 4 refers to days 22 through EOM. Forcing weeks to begin and end on certain days, such as Sunday through Monday, would introduce unnecessary complications with no added benefit.
In cell F6, enter the Title of the budget event. This title should a simple, generic descriptor of the event that will likely reoccur in future rows. Specific details related to the event should be entered in the Description column. Entries in this column should be centered.
For example, for a row pertaining to an income event such as wages from a job, the Title might be “Wages”, and in the Description column the user might enter the name of the employer. If entering an expense event such as the electric bill, type “Electric” in the Title column and then enter the name of the electric company in the Description column.
As stated previously, although it is possible to track every expense on a daily basis, this is probably more trouble than it is worth. Instead, it is recommended that typical weekly expenses such as groceries, toiletries, gas, etc., be combined under one Title heading such as “Misc”. The user would initially enter the Misc amount each week as a projected estimate of his weekly expenses. For example, the user might initially allocate $150 dollars for Misc expenses in each of Weeks 1 through 3, and perhaps $200 dollars in Week 4 (to accommodate the extra days).
At the end of each week, the user can compare his actual cash on hand (as indicated by his bank balance) with the cash on hand predicted by his budget. Any discrepancy will be due to Misc expenditures that were more or (hopefully) less than what was projected for that week. The user can then go back and update his projected weekly Misc expenditure with an exact value.
Some income or expense events will be unexpected occurrences that cannot be related to any existing Title classification. For example, a car breakdown, or perhaps a fall resulting in unexpected medical bills, or Lotto money, are unexpected expense or income events that do not properly fit into any established Title category, even the Misc category. For such events, just type “Unexpected” in the Title column and enter associated details in the Description column.
In cell G6, enter the Type of the budget event. In case of income, enter “Credit”; in case of expense, enter “Debit”. This column should never contain any entries other than “Credit” or “Debit”. Entries in this column should be centered.
In cell H6, enter the Period of the budget event. Most budget events will reoccur periodically. The following are examples of entries that would be appropriate in the Period column: “One Time” (non-reoccurring), “Weekly”, “Bi-Weekly”, “Monthly”, “Quarterly”, “Semi-Annual”, or “Annual”. Note that Unexpected events are always listed as “One Time”. Misc expenses are always “Weekly”. Wage events might be “Weekly”, “Bi-Weekly”, “Monthly”, or “Annual”. Bonuses should probably be listed as “One Time”, even if the user has already received several bonuses and has good reason to expect such on a recurring basis. Entries in this column should be centered.
In cell I6, enter the Status of the budget event. This column will contain exactly one of four possible entries: “Scheduled”,” Due”,” Paid”, or “Received”, depending on whether the budget event is an income or an expense. For events more than one week in the future, enter “Scheduled”. For income that has been received, enter “Received”. For expenses that have been paid, enter “Paid”. For expenses scheduled for payment in the current week, enter “Due”. Evidently, all Scheduled expenses eventually become Due, whether or not they are ever Paid. Entries in this column should be centered.
In cell J6, type the Amount of the income or expense. Income events should be positive numbers, and expense events should be negative numbers. Use whole numbers only. In case of cents, round up to the nearest whole dollar. Use currency format. Entries in this column should be right-aligned.
In cell K6, enter a Description of the budget event, including any distinguishing or otherwise important information relating to the entry in the Title column. Entries in this column should be right-aligned. Since the Description column can also be filtered, it is desirable to be as consistent as possible with entries in this field, as with the other fields.
The user should repeat the above for each income/expense event relating to his personal finances. It is not necessary to enter all budget information in one sitting. Typically, the user will enter some information, and after reviewing this data will enter more information, and then perhaps try a filter, and so on.
It will probably be necessary to add an extra row for a beginning Credit with the title Starting Cash. This entry should be dated in the first Year-Month-Week of the time period covered by the budget.
After adding budget data, the user should now sort the rows by Year, Month, Week, Type, and Title. Since this sort encompasses multiple columns, a custom sort will be required. It is assumed that the user already has, or can acquire, the necessary expertise.
After sorting, the rows will be neatly organized. Since this sort can be conveniently repeated at need, new rows of budget data should always be appended at the end of the list, as opposed to attempting to insert them into the middle. The user might accidentally insert a new row into the wrong place, but Excel will always sort the rows perfectly.
Adding budget data for future weeks/months will be a simple matter of copying the rows from corresponding past time periods. For example, to add the budget data for an upcoming month, the user can simply filter for the preceding month, copy the rows, paste them at the end of the list, and make the appropriate Year-Month-Week replacements. He would delete any Unexpected income/expense events that might have been copied, and reset the previous month’s adjusted Misc entries to his projected weekly values.
Let us now discuss leveraging the power of the AutoFilter to provide almost any desired budget information. For example, consider a simple filter such as Status = “Due”. This filter returns a list of the user’s bills that are currently due, and the Total Of Amount field will contain the sum of these bills.
Consider another filter, such as Status = “Paid” or “Received”. Evidently, this filter returns a list of all expense events that have been paid and all income events that have been received. However, it provides considerably more information than that, because income received minus bills paid equals cash on hand, which is displayed in the Total Of Amount field. If the filter was modified to include Status = “Paid” or “Received” or “Scheduled”, along with a filter applied to one or more of the Year/Month/Week columns, the Total Of Amount field could represent the user’s cash on hand forecasted into the future.
Filters can be applied to any of the columns, in combination, to pull different kinds of budget information. For instance, if Year = “2010” and Month = “August” and Period = “Monthly” and Type = “Debit”, this filter returns a list of the recurring monthly bills in August 2010, and the Total Of Amount field will contain the sum of these monthly bills. Many other filters are possible, providing the user with a wide variety of views of his budget.
Note: once applied, the AutoFilter might as well always be left on. Having the AutoFilter on does not interfere with any other functionality, so removing it accomplishes nothing. Warning: filters on columns persist until explicitly removed. If the user sets a filter on one or more columns and forgets that he did so, future filters may not return the proper information.
Although beyond the scope of this article, Excel provides two other powerful, built-in tools that the user might investigate in relation to his budget data. Simplest to use is the Subtotal function (not the same as the =SUBTOTAL() formula). The Subtotal function allows the user to subtotal his budget on a month-by-month basis. It respects a pre-applied filter. For example, the user might first filter for Type = “Debit” and then Subtotal his budget list, obtaining thereby a breakdown of monthly expenses.
Although considerably more complicated, nevertheless Pivot Tables are another very powerful tool that the user can use to aggregate his budget data on a monthly basis. Moreover, after first creating a Pivot Table to aggregate the data, the user can then use this aggregated data as input for a Pivot Chart. He can then display his financial data graphically across a month-by-month breakdown. For this purpose, a line chart works nicely.
A big advantage to the recommended tabular layout is that the list of budget data can be used directly as input to the Subtotal and PivotTable/PivotChart functions.
It is expected that the above information will provide the reader with a clearer picture of his personal finances. It is hoped that, in so doing, the reader will gain better control over his financial circumstances.