Specifying Date Ranges

When creating financial reports, it is important to understand how the date range affects an accounts final balance. When printing most reports, you usually specify a date range that has a beginning date and ending date. But this rule doesn’t always apply when printing account balances on a balance sheet.

The date on a balance sheet is often worded “As Of” a certain date. For example, if it was for the end of the year, it would say, “As Of 12/31/2007”. It won’t say, “For the last twelve months of 2007”. This is because the current value of assets and liabilities are calculated from the first day they were created. If an account has been in use for three years, then its current balance is the summation of every one of its transactions for the past three years. I call these “cumulative accounts” because their final balance is the accumulation of every transaction. This is important because you might be tempted to use a date range to sum the transactions for just the current year. If you do that, then the final balance will be too small.

Revenue and expense accounts are accounted for using a date range. An income statement header is often worded, “For the Year Ended 200x”. This tells the reader that the income was for the full twelve months of the year. When creating an income statement, you need to set a date range filter that specifies both the beginning date and ending date. Unlike the balance sheet, the income statement doesn’t include transactions that occurred prior to its start date. If you include every transaction in the database, it would overstate the true amounts.

For an easy way to see how each type of account uses date ranges differently, let’s look at an example of getting a new job. We’ll compare how to report the cash balance in your checking account versus how to report how much money you made as an employee. Your cash balance is representative of the balance sheet and your income is representative of the income statement.

Let’s assume you’ve been working on the job for three months and you started in December of 2006. Table A-3 shows the transactions within your checking account.

Table A-3. Checking account transactions for new job.

You can see that this job pays you $10,000 each month and you’ve deposited three paychecks so far. Let’s look at how we would calculate the balances that would appear in the balance sheet and income statement reports for the years 2006 and 2007.

In 2006, the balance sheet is going to report how much cash you have in your bank account as of the end of the year. Since you were only paid once in 2006, the balance is $10,000. The income statement is going to report how much money you made in 2006. Again, you only worked one month so your income is reported as $10,000.

In 2007, things are a little trickier. For the balance sheet, you need to state what the checking account balance is as of 2/28/2007. If you just look at the transactions in the year 2007, you would report that your cash balance is $20,000. But that isn’t correct because you know that if you called up the bank, they would tell you that there is $30,000 in your account. To calculate the cash balance, you need to add up every transaction since the checking account was opened. Thus, you would also include the 2006 transaction when you got your first paycheck. Now, your cash balance would be accurately reported as $30,000.

Reporting how much money you made in 2007 requires that you only look at the transactions during 2007. You were paid twice for a total of $20,000. You don’t want to include all the transactions since you started working at the job (like you did with the checking account) because then you would overstate your 2007 income in 2007 by $10,000.

It’s important to understand the differences between the ways these account balances are reported because you might have to use two different date ranges within the same report. If your report uses the same date range to report the cash balance and the income in 2007, one of the two balances would be wrong. If you don’t specify the beginning date range, the cash balance will be accurate but the income balance will be too large. If you only specify the transactions in 2007, the income balance will be correct, but the cash balance will be too small.

If you find this a bit confusing, you will be glad to know that some databases take care of this problem for you. If you remember what we discussed earlier about archiving data, you learned that many databases will only keep a single year of data within the current table to save space. All account balances that are cumulative (like the checking account in the previous example) will have an entry on the first day of the year that states the account’s previous balance. This entry summarizes all transactions prior to that date into a single entry. This makes it easy on you because if you use the same date range for all accounts, these cumulative accounts will still be accurate.

The date range for the revenues and expenses is always restricted to the period being reported. Revenue and expense accounts don’t have transactions that carry balances forward from previous years. You must always state a beginning and ending date.

When creating a report, you need to determine whether you can use a single date range for your report or whether you need one date range for assets and liabilities and another date range for revenues and expenses. If the database carries forward the balances for assets and liabilities from the previous years, you can use one date range for the entire report. If the database spans multiple years and there aren’t any transactions that carry the balance forward, the date range for assets and liabilities needs to include every transaction in the database. You need to review the database for these “balance forward” transactions to see if they exist or you can consult the documentation that came with the software.