Acctg DB: First Things First ...continuation

... continued


As you can see, the database as generated by entries previously posted in the first part were done from the viewpoint of a non-accountant. It resulted from some redundant Account Descriptions which a database system being a machine will consider differently. For one, the "Checking Account" title must be separated from the check number used. Therefore, the database needs at least another field. Also the data entry could have employed some data validations which forces entry of Account based on a predefined list.

( See here is a sample of "Data Validation", "Range Name" operation and "VLookup" formula which would get to you the idea.)

( See here how to make the "Data Validation" source dynamic to accommodate changes from the source list. In addition you'll know about the "Offset" formula.)

Going thru our pdf workbook we find that for such reason accountants used what is called "Chart of Accounts". In this case, this one:



Combining "Data Validation" and the "Chart of Accounts" as the source for data entry we would have gotten a nicer Pivot table result for generating the Financial Statements (FS).

However, as it was entered we can play around with the "Pivot" table to create our FS.

We can select a contiguous row of field names, then right click to classify (or group) them as one:


We select group from the dialogs that appear and a new column will be inserted to reflect the original Description and the grouped items - named as Group1.


(The fields can be dragged into place, if needed, to make similar fields contiguous so that we can apply the above-mentioned grouping technique.)

By going to the cell where 'Group1' is used as label, we can then type the more accurate "Account" title using the formula bar.



We also notice that the date field was not sorted right by the Pivot because our entry was in the form "Dec/2010" and "Nov/2010". I suggest that we should have more precisely entered the date as in the form "mm/dd/yy". Excel will automatically format and recognized it as a date entry. We could then have another column (field of data) in the form "201011" and "201012" which will be useful for pivoting based on months (this will be sorted correctly).

For this session, we just edit the months to "201011" and "201012" and drag the latter to the right side of the column for the month of November.


... to be continued

------------
Data Validation: