This template enables users to record their personal income & expenses and to generate a comprehensive personal finance report for any user defined 12 month period. Transactions can be recorded from an unlimited number of transaction sources like bank accounts and credit cards and the functionality that has been included in the template enables users to easily reconcile the reports to bank account or credit card statements. This template is the ideal solution for measuring household income and expenditure.
The template consists of the following sheets:
Sources - create a unique source code for each transaction source that you want to include in the template. This sheet also includes transaction totals that can be calculated by simply entering the appropriate Start Date and End Date. The reporting periods that are included in the 12 month personal finance report is also defined on this sheet by simply entering the appropriate Start Date in cell C2.
Accounts - this sheet includes the default accounts list and can be used to edit account descriptions and to add additional accounts to the default accounts list.
Transactions - all personal income and expense transactions should be recorded on this sheet and allocated to the appropriate account by selecting an account code from the list box in column G. Transactions can be recorded by entering the data or copying the data from a bank or credit card statement before pasting the data into the appropriate columns. Income should be recorded as positive values and expenses as negative values.
Report - this sheet includes a 12 month personal income & expenses report. All the calculations on this sheet are automated and the only user input that is required is to ensure that all the accounts that have been added to the Accounts sheet are included on the report. Reporting periods can be amended by simply changing the Start Date in cell C2 on the Sources sheet.
Transaction Sources
As we mentioned before, an unlimited number of bank accounts and credit cards can be included in the template by simply creating a unique transaction source code for each account. The source codes that are created on this sheet are then selected when recording transactions on the Transactions sheet.
We recommend using an alphanumeric code when creating the required source codes and to use descriptive codes that make it easy to distinguish between different transaction sources. The template includes four default source codes but additional source codes can be created by simply entering a new source code in the first empty cell below the Excel table - the table will be extended automatically to include the new source code.
After entering a new transaction source code, users should also enter a unique description of the source code in column B. All the columns with a light blue column heading contain formulas that are automatically copied when a new row is added to the table. The formulas in these columns calculate the opening balance, total income & receipts, total expenses & payments, total contra transactions and the closing balance for each transaction source based on the date range that is defined by entering the appropriate Start Date and End Date.
The opening balance for each transaction source is calculated based on the transactions that are recorded on the Transactions sheet. When you start using the template, you therefore also have to record the initial opening balance for each transaction source on the Transactions sheet by entering the balances at the end of the day prior to the initial template Start Date (specified in cell C2 on the Sources sheet). All transactions after this date should then be recorded on the Transactions sheet.
We've mentioned that the Start Date that is specified on the Sources sheet is used to define the 12 month reporting period that is included on the Report sheet - after specifying the initial Start Date and recording the appropriate opening balances, the Start Date can be amended to include a new 12 month period and the calculation in column C will update the opening balance calculation based on the transactions that have been recorded on the Transactions sheet. Refer to the Opening Balances section under the Recording Transactions heading of the Instructions for more guidance on recording opening balances.
The income & receipts total is calculated as the sum of all the positive values that have been entered on the Transactions sheet for the particular source code (excluding Contra transactions). The expenses & payments total is calculated as the sum of all the negative values that have been entered on the Transactions sheet for the particular source code (also excluding Contra transactions). Both of these totals (as well as the Contra Transactions total) are calculated based on the date range that is defined by entering a Start and an End date in the appropriate cells at the top of the Sources sheet (note that the End Date is optional).
Contra Transactions include all transfers between transaction sources that are included in the template and should be allocated to account 999 when transactions are recorded on the Transactions sheet. The total of the values in this column should therefore always be nil - if this is not the case, the column heading will automatically be highlighted in orange in order to indicate that an imbalance exists. Refer to the Contra Account section under the Recording Transactions heading for more guidance on Contra Transactions.
All the calculations in the calculated columns are based on the date range between the Start Date (cell C2) and the End Date (cell H2). As we've mentioned, the Start Date is also used to define the reporting periods on the Reports sheet and this cell should therefore always contain a date - you'll notice that an error message is displayed if the cell does not contain a valid date.
The End Date can however be left blank - the calculations in the calculated columns will then include all the transactions after the Start Date that have been entered on the Transactions sheet. When you enter a date in the End Date cell, only transactions with a Payment Date between the specified Start Date and End Date will be included in the calculations in the calculated columns. This functionality therefore enables users to easily calculate the transaction source balances based on any user defined date range.
Account Reconciliations
We recommend that you reconcile the closing balances of each transaction source code to the appropriate account statement. This will ensure that all transactions have been recorded accurately on the Transactions sheet.
When you leave the End Date cell blank, all transactions that have been entered on the Transactions sheet are included in the closing balance that is calculated in column H. If you want to reconcile the closing balance of one of the transaction sources to a previous statement, this can be accomplished by simply entering the appropriate statement date in the End Date cell in order to display the calculated closing balance as at the specified statement date.
Note: All the calculations on the Sources sheet are based on the dates that are entered in the Payment Date column on the Transactions sheet, while the calculations on the Report sheet are based on the dates that are entered in the Document Date column. The net account movement on the Sources sheet may therefore differ from the transaction totals on the Report sheet if the document dates and payment dates of transactions are different. We'll discuss the difference between these two transaction date columns in more detail later on in these Instructions.
Accounts
The reporting line items on the monthly personal finance report are based on a three digit account number. The default accounts list is included on the Accounts sheet and consists of 65 accounts that are grouped into nine reporting categories. The reporting categories are listed in column D to F at the top of the Accounts sheet.
The template can be customized to your specific requirements by editing the account descriptions of the default account numbers and you can also add additional accounts to the list by inserting a new row in the appropriate position (according to the account number sequence), entering the appropriate account number and entering a description for the new account. All new accounts should therefore be created by inserting a new row above the "999" account number.
After inserting an additional row and adding an account to the list, you also have to insert a new row on the Report sheet in order to include the new account in the report. If this step is omitted, the account number will be highlighted in orange on the Accounts sheet. Note that if transactions are allocated to an account that is not included on the Report sheet, the report will not be accurate.
Also note that the Contra Account (account 999) is required for the allocation of inter-account transfers and should therefore not be deleted.
When you create a new account, the account number should be determined based on the reporting category that you want the account to be included in. For example, if a new Remuneration type account is created, a three digit account number that starts with a "1" should be used because all the accounts from account number 101 to account number 199 fall into the Remuneration account group.
Recording Transactions
All income and expense transactions should be recorded on the Transactions sheet. You can enter the transaction details or copy the data from the account statements and then simply enter the information that is not included on the statements. The following information needs to be recorded for each transaction:
Document Date
As we've mentioned before, the calculations on the Sources sheet are based on the Payment Date column (the transaction date on the account statement should be recorded in this column).
The monthly personal finance report on the Report sheet is however based on the document date column. The account statement date can also be recorded in this column but in some cases this date may not be appropriate for reporting purposes. For example, you may be paying the rent that is due for a particular month on the last day of the previous month - if you use the account statement date when recording the transaction, the expense will be included in the incorrect monthly period. In order to display the transaction in the correct monthly period, you can simply enter a different date in the Document Date column and still be able to keep track of when the payment was made by referring to the data in the Payment Date column.
Source
The Source column includes a list box that contains all the transaction sources that have been created on the Sources sheet. You can therefore simply select the appropriate transaction source from the list or copy the source code from one of the previous transactions.
Note that you have to create a source code on the Sources sheet before you will be able to allocate transactions to the particular transaction source.
Customer / Supplier
The name of the entity to which the transaction relates should be entered in this column. If the Auto Complete feature is active, Excel should suggest a customer or supplier name after only entering a few characters. Users need to make sure that the same customer / supplier name is used for all similar transactions because this would enable users to filter transactions by this column in order to compile an income or expense report by customer or supplier (use the Auto Filter feature for this purpose).
Transaction Reference
Enter an appropriate transaction reference in this column - this reference should enable you to trace a transaction to its supporting documentation.
Description
Enter a description of the transaction in this column - this description should enable you to determine the nature of the income or expense transaction.
Amount
Enter the transaction amount - income should be recorded as positive values and expenses should be recorded as negative values. The amount should be exactly the same as the amount that is displayed on the account statement - if this is not the case, the template balances will not reconcile back to the account statements.
Note that a total has been included above the column heading. This total is calculated based on all the transactions that are displayed on the Transactions sheet. If you therefore use the AutoFilter feature to filter the data on this sheet, only the filtered data will be included in this total.
Account
This column includes a list box that contains all the account numbers that have been created on the Accounts sheet. It also contains the account description of each account. You therefore have to create the appropriate account before you will be able to allocate transactions to the account.
Excel actually does not allow multiple columns to be included in a list box but we have implemented a workaround in order to provide the account descriptions together with the account numbers, thereby making it easier to allocate transactions to the correct account. In order to accomplish this, we have had to sacrifice some flexibility when entering transactions - you will not be able to enter the account number into column G because an error will be encountered - you therefore have to select the appropriate account number from the list or copy the appropriate account number from one of the previous transactions. A validation error (red triangle in the top left corner of the affected cell) may also be displayed but you can safely ignore this error.
If you find this limitation to be an inconvenience, you can change the Named Range that has been defined as the source of the list box in this column so that only the account number column is included in the list box. The account descriptions will then not be included in the list box and you'll be able to enter the account numbers when recording transactions.
If you select the account description instead of the account number from the list box, you'll notice that the cell is highlighted in orange. This is because the account description is not recognized as a valid account number and will result in the personal finance report being inaccurate. The cell will also be highlighted if an invalid account number is copied into the Account column. If an account number is highlighted, simply select the correct account number from the list box in order to remove the highlighting.
Payment Date
The transaction date on the account statement should be entered or copied into the Payment Date column. As we mentioned before, the calculations on the Sources sheet are based on the dates that are entered in this column. By including the statement date in this column, you will be able to easily determine when income was received or when a payment was made.
Account Description
This column contains a formula that is used to look up the account description on the Accounts sheet based on the account number that has been entered in column G. The formula will be copied automatically when you add new rows on the Transactions sheet because the entries are included in an Excel table. The aim with this calculated column is to make it easy to determine to which account a transaction has been allocated.
Opening Balances
The initial opening balances of each transaction source also need to be recorded on the Transactions sheet. As we've explained before, the initial Start Date needs to be recorded on the Sources sheet and this date determines the reporting periods that are included on the Report sheet.
The account statement balance at the end of the day before the Start Date that is specified should be used as the opening balance and the opening balance transaction should be dated on the day before the Start Date (in both the Document Date and Payment Date columns).
The appropriate source code that is created on the Sources sheet should be entered in the Source column, you can leave the Supplier / Customer column blank, enter a reference like "Statement" in the Reference column and enter a text string that refers to the opening balances in the Description column. The Account that the opening balance is allocated to is not used for reporting purposes because the transaction is dated before the report Start Date but we recommend that you use the Contra account when recording the initial opening balances.
After entering the opening balance transactions, the balances in column C on the Sources sheet should agree to the appropriate statement balances. All transactions on or after the Start Date that is specified should then be recorded on the Transactions sheet and the closing balances on the Sources sheet should reconcile to the account statement at all times. Note that the closing balances are calculated based on the End Date that is specified and will include all transactions if the End Date cell is left blank.
Also note that the Start Date can be amended and the opening balances that are subsequently calculated on the Sources sheet will automatically include all transactions that are dated before the amended Start Date. When you amend the Start Date, the monthly reporting periods are also automatically adjusted in order to include a twelve month period that commences on the Start Date.
Example: If we specify a Start Date of "2010/03/01" on the Sources sheet, the opening balances that we need to record in the Document Date and Payment Date columns on the Transactions sheet should be based on the closing statement balance on the 28th of February 2010. All transactions from the 1st of March 2010 should then be recorded on the Transactions sheet.
Contra Account
All inter-account transfers between transaction sources that are included in the template should be allocated to the Contra Account (account number 999). The net effect of payments from one account and the matching receipts in the other account should therefore be nil. This means that the Contra account balance should always be nil and if this is not the case, the heading on the Sources sheet (cell F5) and the account number and description on the Reports sheet will be highlighted in orange.
Example: A bank account and a credit card account are both included in the template. When we make a payment from the bank account to the credit card account, the payment transaction is recorded on the Transactions sheet, the bank account source code is selected in the Source column, the amount is entered as a negative value and the transaction is allocated to the Contra account (account 999).
The receipt transaction is also recorded on the Transactions sheet (from the information on the credit card statement). The credit card account source code is selected in the Source column, the amount is entered as a positive value and the transaction is also allocated to the Contra account (account 999). Both the payment and receipt transactions should be dated on the same date in both the Document Date and Payment Date columns.
The net effect of the credit card payment is therefore nil and these types of transactions therefore have no influence on the amounts that are included in the personal finance report. Instead, the transactions that were incurred with the credit card are recorded as expenses on the Transactions sheet and included in the personal finance report.
Example 2: If a bank account is included in the template but the credit card account is not included in the template (not recommended), the credit card payments cannot be allocated to the Contra account because there will be no receipt transactions that can offset the payment transactions.
Under these circumstances, you would have to allocate the credit card payments to the appropriate account under the Financing Cost account group.
Personal Finance Report
The personal finance report is included on the Report sheet. All amounts are automatically calculated and the only user input that is required is to specify the reporting periods and to add additional accounts to the report after the accounts have been created on the Accounts sheet.
As we've explained, the Start Date that is specified on the Sources sheet determines the periods that are included in the report. The first month on the report will always be the same as the month that the Start Date falls into but please note that all transactions during this particular month will be included in the report even if the Start Date is not the first day of the particular month.
If you therefore specify a Start Date of 2010/03/15, the first monthly period on the report will be March 2010 and the report will include all transactions from the 1st to the 31st of March.
After a new account has been created on the Accounts sheet, you have to include the account on the report by inserting a new row in the appropriate section of the report, copying the formulas in all the columns with a light blue column heading from one of the existing rows and entering the new account number in column A. All the transactions that are allocated to the new account will then be included in the report.
As we've mentioned before, all accounts that have been created but are not included in the personal finance report will be highlighted in orange on the Accounts sheet - this makes it easy to identify accounts that have been omitted from the personal finance report.
Report Filtering
We've also added some innovative filtering functionality to the personal finance reports. The source code selection cell (cell C2) can be used to select one of the transaction sources that have been created on the Sources sheet. When a transaction source is selected in this cell, only the transactions for the particular transaction source will be included in the report. When the cell is blank, all transactions from all transaction sources are included in the report.
You can therefore revert back to displaying all transactions by simply positioning the cursor in cell C2 and pressing the delete key to remove the transaction source code that was previously selected.
Report Totals
The personal finance report includes totals for each line item in column P on the Reports sheet. By default, these totals are calculated for the entire 12 month period that is included in the report but you can elect to only display transaction totals for a user defined date range.
This can be accomplished by simply entering the appropriate "From" and "To" dates in cells P1 and P2 respectively. The total calculations will then be based on the specified date range. Note that you don’t have to enter both dates - if you want to include all transactions after a specified date, enter only a "From" date. Similarly, if you want to include all transactions before a specified date, simply enter only a "To" date.
Roll Forward
Rolling the template forward after the initial twelve month period could not be easier - simply enter a new Start Date on the Sources sheet and all the template calculations will automatically be updated.
