Acctg DB: First Things First ...the FS

... continued

Here is the generated FS from the pivot table we have corrected as to the grouped accounts and the preferred order conducive to the FS presentation. I also created a modified database and used it to generate a pivot.

By going through the said file, you may have an idea of how the pivot table have helped us in our generation of the FS (Financial Statements).

If you would like to try other pivot views, just create other sheet tab and copy the pivot there. Because of the flexibility of pivot tables, putting other data near them is not really advisable. The pivot table can grow or shrink in area occupied, as you fiddle with them.

Also try to click an account title and it will create a drill-down detail in another sheet tab. This is the ledger posting that will serve as T-account of the book keeper. The created detail is a snapshot of the underlying data. If you post additional data to the pivot table source, the previously generated drill-down data is not able to be refreshed (the pivot table can be refreshed anytime as long as it can still locate the source data). So, you may have to create another drill-down operation to create an updated ledger of the said account.

Notice that the Balance Sheet for each month is composed of cumulative figures as of the end of that month.

The Monthly Income Statement (IS) is (usually) not cumulative so that owners can gauge the effectiveness of that month's operation (w/o dilution from previous month figures). There is, however, a carry over (cumulative) income or loss figure at the bottom of the monthly IS. The previous cumulative income or loss when added to more current month will result to the current Year to Date (YTD) income or loss. This, in turn, is carried over as adjustment (not formal adjustment) to Owners' Equity in the Balance Sheet.

It is only at the end of the Year that the formal transfer of cumulative income or loss is recorded formally as adjustment to the Owners' Equity (zeroing out the Income Statement accounts with that formal transfer in the books).


This exercise have given us a glimpse of the all around capability of Excel to handle data and serve the needs for record keeping, calculation, database of small business. The database accounting approach must be shared to accounting students and practitioners, because the tools within Excel alone will be underused if we stick to the conventional repetitive recording/transcribing from journal to ledger to special books to FS.

The manual Accounting know how, usually taught in school, requires a lot of manpower even with the use of computers (but it is important for understanding the concept). With Excel, Access and Word working together we have a formidable team to create a computerization system as the transactions developed.

Not to mention that we can include the other MS Office members and even bring in a web-enabled Database server to the mix. It is a shame, like a Mayweather and Paquiao non-event, to not be able to take advantage of the tools we have.

Buying a full-blown preconceived, pre-programmed ledger software is not an option in many instances, because in most cases, people cannot pre-determine the quirks and deviations that arises from the business. A new business inventory software, for example, can be foreseen but actual evaluation, testing up to purchase and deployment takes time. All the time, with the tools at hand, we can create a Distributed Data Collaboration in an As-It-Happens development phase.

So far we also noticed some limitations of Excel for book keeping: like the Ledger generated from the drill-down which is not live. Michael Jordan cannot win by himself all the time. In this instance, we have MS Access to help in handling ad hoc queries and reports where it would be cumbersome for Excel.

We have not yet even handled how to create the user interface (UI), or how we will post either to an MS Excel data container (like we did) or MS Access tables. What about, how the special books (like Cash Receipts Book or Sales Book) can be coaxed out of our general purpose data or ledger container tables? Or even about how we can print checks or voucher details on the pre-printed forms?.

But I hope you got a glimpse both in this "DB Acctg: First Things First" part and the "DB Acctg: Fast Forward" portions of the series.

Do check the other earlier parts of this series, if you have not yet done so.

In the meantime, here are some files related to "DB Acctg: Fast Forward" portion:
1.) List.xls
2.) Data Container (proxy for MS SQL Server)
3.) MDB file companion to 1 and 2 above
4.) MJ.xls Alley Oop Slam test file
5.) Pippen.mdb Alley Oop Slam test file

Put these files in "C:\DBAcctg\" folder (create one).

Subscribe and enjoy!