17. Ancient Data Warehousing

Although it only now has become a common term as the core of business intelligence, the practice of data warehousing has been with us for over five-hundred years. Since the early Italian renaissance, merchants have kept databases of their business transactions wherein each transaction was related to the critical dimensions that characterized its type and the effect that it had on the business.

More specifically, each transaction, representing a transfer of financial resources from one place to another, was related to the source and destination of the transfer. Each transactions was related to the place from which it was withdrawn and the place to which it was deposited in what has come to be known as double-entry bookkeeping. The record of the withdrawal was referred to as the “credit” entry and the record of the deposit was referred to as the “debit” entry.

Like all data warehouses, each of transactions was also related to the date of its occurrence, allowing the merchant to sort and sum the transactions to measure the activity of the business during given periods of time. Furthermore, the financial state of a business could be determined by summing the deposits to a given account, then summing the withdrawals from the same, and finding the account’s “balance,” or state, by subtracting the withdrawals from the deposits.

This process of relating transactions to its critical factors (“dimensions”) and summing the transactions according to these critical factors is exactly how a modern data warehouse is used. The data warehouse, the central focus of the field of business intelligence, is universally implemented as a multidimensional database. The multidimensional database, like the bookkeeper’s journal, is made up of chronologically ordered records that represent business transactions with each transaction related to the customers, products, accounts, dates, and other “dimensions” of its existence. The data warehouse is, in effect, a journal of business transactions in the same way that the accountant’s book of original entry (his “journal”) is.

So, what is the difference between a traditional bookkeeping journal and a modern data warehouse? Only the existence of the SQL language (or some equivalent database query language). The modern query language, allows the user to sum and sort transactions by any combination of its many dimensions, including the transactions date and its debit and credit accounts.

Because the renaissance bookkeeper did not have modern database automation, he needed to first sort the transactions into individual databases (called “ledger accounts”) and then sort them again into rigid time frames (called “reporting periods”). Because of these once necessary and arduous sorting tasks, the business intelligence of times past was slow, expensive, error-prone, and untimely. And because we have not integrated financial reporting and analysis into common business intelligence practices, financial information has continued to be untimely to this day.

16. Flow, Measurement, and Analysis

There are three simple ideas that will revolutionize financial information in the twenty-first century. All financial information is derived from the fundamental data that is recorded by double-entry bookkeepers and reported by accountants in various GAAP reports. By going to the original meaning of fundamental double-entry data, more financial information will be produced and financial resources can be more wisely allocated.

1. Financial data has for over five-hundred years been stored in what is now called a data warehouse. The records of financial transactions in a traditional bookkeeping journal are fundamentally the same as a data warehouse, differing only because of the primitive forms of recording that existed before automation. We need to update the journal according to modern data warehouse techniques.
2. Financial data is far more intuitive and easy to understand in its original intent and has become complicated and obscure because that original intent has been lost by modern accountants. The most significant cause of this obscurity is the so-called accounting equation, which was developed with a primitive numbering system, is mathematical nonsense, and serves to make the simple concepts of finance nearly incomprehensible.
3. The primary thing that double-entry bookkeeping is doing is keeping track of the flow or movement of financial resources from one financial space to another. The GAAP reports report balances or changes in balances in those financial spaces rather than the flow. This is primarily due to the lack of automation during the development of traditional accounting. In the 1980’s, the financial world moved toward the tracking of flow with the addition of the Cash Flow Statement. This is an improvement in the right direction, but it is just a start to the real financial analysis that comes from studying the flow of all of the resources.

I have dealt with these ideas in my two books, Banking the Past and The Tao of Financial Information. Banking the Past addressed the first idea while The Tao of Financial Information addressed the last two. In following blog posts, I will summarize the powerful solutions offered in those two works. By returning to the foundation of financial information, we can profoundly increase the intelligence that we use in allocating our resources.

15. Financial History

Double-entry bookkeeping has been the traditional way of keeping track of a company’s revenue, expenses, and other financial categories for over five-hundred years. Proven by the test of time, double-entry bookkeeping is a powerful technique for recording the dynamic activity of trading wealth.

The importance of double-entry bookkeeping cannot be overstated. Because it allows the activity of trading to be accurately recorded, it actually makes trading more efficient and productive. As trading is made easier, the productivity of an economy is increased because its members are more able to exploit divisions of labor and the other comparative advantages that trading makes possible.

Why has double-entry bookkeeping been such an important part of our economic reasoning and why will continue to be in the future? Because the “double” in double-entry bookkeeping supports the recording of dynamic processes as opposed to simple static states. By relating each single transaction to two of its critical dimension, the bookkeeper memorializes the activity that occurs during a trade. Rather than just measuring the amount of wealth at a given location with single entries, double-entry measures the actual activity that occurs as financial resources are moved from one place to another.

The two entries that make up the double-entry record both the source and destination of a flow of resources (a trade). The entries record from where the resources are withdrawn (the “credit” entry) and to where they are deposited (the “debit” entry). From these two entries, enough information is recorded to allow an analyst to unwind a company’s history to any point in its existence. While a single entry may be sufficient to keep track of the balance in our checking account, two entries are necessary to keep track of the activity that creates those balances (see The Tao of Financial Information).

In later posts to this blog, double-entry bookkeeping will be explored as the foundation of our knowledge of commercial trades. Rather than question the efficacy of double-entry bookkeeping, we will show how this powerful technique can be expanded in the age of the computer to further empower the “knowledge is the business” company.

14. Daily Accruals

Accrual accounting attempts to match revenues and expenses to the time periods in which they are accrue, as opposed to the time when they are paid. For example, a machine might be purchased in one year and have a productive life of ten years. Rather than “expense” the cost of the machine during the year of its purchase, the company will accrue the expense of the machine over its expected life. With accrual accounting, the expense of the machine is matched to the ten years of its use by expensing a fraction of the machine’s total cost each year as it slowly “depreciates.”

For the bookkeeper, accrual accounting means that a single large expense transaction, such as the purchase of a machine, is recorded not once when it occurs, but many times as fractions of the total expense are “matched” to a time periods of the machine’s life. The machine with a useful life of ten years, purchased at, let’s say, $100,000.00, could be expensed as a cost of $10,000.00 per year over its ten year life (this assumes straight-line depreciation and no salvage value). At the end of the ten years, the total expense of the machine, the $100,000.00, will have been fully expensed.

If the company only reports its expenses once a year, each year of the machine’s life will cause the bookkeeper to enter one transaction recording the $10,000.00 expense for that year’s depreciation. On the other hand, if the company wants its financial information to be more current, it might report its expenses on a quarterly basis. Since there are four quarters in a year, there would be forty quarters in the ten-year life of the machine. The bookkeeper would then have to produce forty accrual entries for the gradual depreciation of the machine, one expense entry of $2,500.00 for each quarter of the machine’s life. This quarterly reporting schedule has all of the advantages of the annual schedule in that, if someone wants to know the amount of depreciation for the whole year, they can simply ask a computer to sum all of the accrued expenses for the four quarters of the year. The arithmetic operation of addition is quite easy for a modern computer.

If we take this reporting schedule even further to provide daily reports to our information-starved executives, we need to produce accrual transactions each day of the ten-year period. This is possible with modern automation because the bookkeeping labor can be automated, allowing the entries to occur automatically each night of the machine’s life.

A daily accrual schedule offers the company the advantage of having an accurate daily expense report (and therefore, accurate daily earnings and balance sheets). In addition, the computation of the amount of accrual for a quarter or year can be accomplished by simply summing the appropriate daily accrual entries.

Most importantly, with daily accruals, a clever financial analyst could produce reports for particular weeks, months, or even sales periods without regard for any rigid fiscal reporting schedule. By having a busy executive simply enter the beginning and ending points of time, a computer program should be able to produce complete financial information about the intervening period.

13. Daily Account Balances

In the preceding post, the production of a trial balance on a daily basis was proposed. By simply using the power of the computer to add a few numbers together, a complete trial balance should be available as soon as the business transactions are recorded. It was also shown how to-date earnings and other information could be produced at little or no cost from this trial balance.

A trial balance, however, has the formality of being part of the official quarterly report preparation process and, since we are just trying to get critical information quickly to the company’s executives, we get dispense with formalities and just talk about producing real information on a real-time basis. Balance sheets, trial balances, and income statements aren’t as important as the information contained in them, and we can produce that information as easily as the dashboards in our car produce current information about speed and mileage. There is no reason why every detail of a company’s financial data could not be made available to corporate leaders on a daily basis.

The general ledger is made up of accounts; each account has entries in it that represent deposits (debits) or withdrawals (credits); and each account has a balance that represents the sum of the deposits and withdrawals. The account balances of the general ledger are really all that is needed to easily produce the information found in the formal GAAP reports.

Because this is the twenty-first century, we can assume that the general ledger is automated and that the entries in each account are recorded by a program. As easy as it is for the program to record the entry, it can also keep the running balance of the account, adding a ten dollar debit entry could automatically update the running balance ten dollars in the debit direction. An automated running balance means that the balance of every account in the General Ledger (representing all of the financial data in the company) is available to us as quickly as the data is posted.

This means that for any company large enough to have its bookkeeping on a machine of laptop power or greater, the account balances should be always available. The account balances are the details of the trail balance that was discussed in the previous post, so we are essentially where we were in the previous post, having the ability to present all of the critical GAAP quarterly report information to executives on a daily basis (see previous post).

More essentially, it can be shown that if we have the account balances available to us, it is simply a matter of grammar school arithmetic to produce totals of assets, liabilities, revenues, expenses, and earnings (revenues less expenses).

Quarterly reports can be converted into real-time dashboard information by simply performing trivial arithmetic on the ledger account balances that should be available and current at any point in time. Greater sophistication can be made real-time by making accrual entries daily (this will be the subject of a later post).

12. Daily Balance Sheet

In the preceding post, I proposed, as a thought experiment, the daily closing of the books, allowing for a computation of the earnings reports on a daily basis. Let us extends that thought experiment a little further and propose that a balance sheet, summarizing the financial position of the company, be produced on a daily basis.

With modern automation, all of the arithmetic of producing a balance sheet should take only moments and be relatively costless. It can be set to go off automatically, immediately after all of the day’s transactions are posted. To make the job easier, let us propose that we do not even have to close the books to prepare the balance sheet. We can, for example, obtain our up-do-date financial position from a daily “trial balance.”

The trial balance will give us an intact summary of critical parts of our financial position. For example, directly from the trial balance, before any accounts are closed, we can obtain the amount of the assets broken down by category. An accurate summary of all liabilities would also be available directly from the trial balance. What would be missing from the trial balance would be earnings information. For the earnings data to be directly in our balance sheet the closing operations would normally be performed, turning our trial balance into a true balance sheet.

However, our daily trial balance would include the balance of every account in the ledger and a complete and accurate earnings summary could be produced in milliseconds by simply adding the balances of the revenue and expense accounts together. Therefore, in effect, our trial balance could produce all of the information in a true balance sheet as well as the earnings data normally found in the income statement, without the closing of the books.

In summary, all of the data found in quarterly reports could be generated on a daily basis by simply summing the balance of each account and putting it into a trial balance. This, of course, could be done without closing the books. The key to producing real-time financial data in the age of automation is to simply allow a trivial computer program to perform addition on ledger accounts each night as soon as the day’s transactions are recorded.

11. Daily Closings

Let’s consider the idea of closing the general ledger on a daily basis. I propose this idea as a thought experiment only, intended to open minds to new ideas and perhaps provoke further discussion. This is not suggested as the optimal solution on maximizing financial information available to corporate decision-makers

The closing of the books involves summing up the so-called “temporary accounts” (revenue, expense, and dividend accounts) and transferring their balances to the retained earnings account. This is typically done at the end of a business period, allowing the bookkeeper to determine the earnings for the period and leaving these temporary accounts with a zero balance to begin the next period accumulating new earnings data.

Since most corporations use the closing operations to prepare their income statement and other periodic reports, the closing operations determines the reporting period. Valid reports are only available after the period’s closing operations and the only reported metrics available are for the period itself. It is hard to imagine how important financial information is reported in such a limited and rigid manner in today’s age of automation, but the method was designed in the middle ages when addition was done with beads and the practice has been dogmatically accepted as necessary regardless of the mounting evidence of its archaic absurdity.

If we choose to continue the process of closing the books before preparing the reports, let us now consider amplifying the amount of information available and increasing its timeliness by closing the books every day and then producing earnings reports for each day.

Since we are working with computers, the closing operations can be easily automated to kick-in at midnight each day and run at little or no cost to the company to summarize the day’s activity. This means that every day the people who run the company could have daily earnings statements as well as more detailed reports on specific types of revenues and expenses. The effects of marketing plans, weekend sales, and work interruptions could be easily gauged just as they happen, allowing the alert helmsman to adapt his company by quickly making necessary corrections and adjustments.

But, if the temporary accounts begin each day with a zero balance, how would a company ever be able to produce a quarterly earnings report? Simple, the earnings for one week is the sum of the earnings for each day of that week; the earnings for a given month is the sum of the earnings for each of its days; and the earnings for a quarter can easily be produced by summing the appropriate daily reports.

Therefore, there is no reason why account closing must await the end of a fiscal period. For the company unwilling to forego the whole wasteful process of closing the books, the process of doing daily closes is a simple way of freeing financial information from the limitations of a rigid fiscal period.