PART VI. FINANCIAL STATEMENTS

In manual book keeping financial statements prepared after trial balance have been available. Trial balance contain ending balance of each account that get from General Ledger. Similar to the manual book keeping, here we can prepared financial statements only if Summary of General Ledger have been set. Note that ending balance of each accounts are available on Summary of General Ledger. There are three financial statements that will be prepared as follows:

• Income Statement

• Capital Statement

• Balance Sheet

A. Income Statement

All amount of each revenue accounts and expense account are taken from last two columns of Summary of General Ledger (sheet “gls”). This last two columns are ending balance of each accounts.

The formula applied to get amount of each account is similar to this:

=$gls.I23

The formula is an example that aimed to get ending balance of account “Revenue” that located in cell I23 (represented by “I23” on the formula) in sheet gls (represented by “$gls).

B. Capital Statement

All amount of each items but “Net Income” item are taken from sheet gls. Net income amount is taken from sheet “is” by applying formula:

=$is.G26

The three other are taken from sheet “gls” by applying formula:

=$gls.E21, for beginning balance

=$gls.G21, for paid in capital

=$gls.H22, for withdrawal

C. Balance Sheet

Similar to those in Income Statement almost all of the amount of each items in Balance Sheet are taken from the last two columns in Summary of General Ledger (sheet gls). Only one items, Capital, that the amount is not taken from sheet gls but from sheet cs (Capital Statement) instead. We also apply the same formula to those applied in Income Statement and Capital Statement.

PART V. SUMMARY OF GENERAL LEDGER

Create new sheet named “gls” contain summary of general ledger. This sheet is very important one in the sense that all transactions data entered in general journal are processed here before further used by other sheets. All financial statement sheets get processed data available here.

As the name suggests this sheet basicaly contain information about beginning balance, total debits, total credits and ending balance of each account for the period. The last two columns of the sheet are similar to those in trial balance.

As depicted in figure above sheet gls consist of nine columns as follows:

• Acc Nr, contain account number

• Account Title, contain account title

• NB, contain normal balance

• LBD, contain beginning balance for accounts that have debit normal balance.

• LBC, contain beginning balance for accounts that have credit normal balance.

• TRD, contain total amount debited for the period for each account.

• TRC, contain total amount credited for the period for each account.

• LED, contain ending balance for accounts that have debit normal balance.

• LEC, contain ending balance for accounts that have credit normal balance.

Only two columns, LBD and LBC, out from those nine columns that need to be filled out by typing the cell. The Acc Nr column filled by copying Acc Nr column on sheet ca. The seven other columns are filled with formulas. Remember that we just need to type the formula once on the first row for each column and copy the cell on the following rows.

The formula for the colums Acc Title is:

=vlookup(A6;rca;2;false)

The formula for the colums NB is:

=vlookup(A6;rca;3;false)

The formula for the colums TRD is:

=sumif(gjn;A6;gjd)

The formula for the colums TRC is:

=sumif(gjn;A6;gjc)

The formula for the colums LED is:

=if(C6=”d”;D6+F6-G6;””)

The formula for the colums LED is:

=if(C6=”c”;E6+G6-F6;””)

Each column in this sheet is totaled using “sum” formula to enable us detect earlier and esier if something incorrect happens.

PART IV. GENERAL JOURNAL

Create Sheet General Journal, named “gj”, to record transactions. The sheet consist of eight column as follows:

1. Date, to record date of transactions

2. Doc Nr, to record number of source documents

3. Acc Nr, to record accounts number

4. SL , to record customer/suplier card number

5. Account Title, to record account title

6. Explanations, to record brief explanation of the transaction

7. DR, to record amount debited

8. CR, to record amount credited

Format different colour at the last row. Transactions should not recorded beyond this marked row. If we need more rows, inserting rows must be done somewhere above the marked row. To check whether total amount debited and total amount credited are balance, put sum formula at the end and at the top of column DR and CR to sum all amount debited/credited.

Some columns should be range named. This will make us esier to refer to when we need the data at other sheet. Cells included in each columns starting form column's header and ended in lowest cell (cell marked by different colour). The range name and their respected columns are as follows:

1. gjn for column Acc Nr

2. sl for column SL

3. gjd for column DR

4. gjc for column CR

Only limited automation can be done here. All transactions data should be entered manually but accounts title. The cells in Accounts Title column contain Vlookup formula to refer account title to rca range on sheet ca. The formula are:

=Vlookup(C6;rca;2;false)

This formula will get the account title of account number typed in column “C”. This will avoid error. We just need to type this formula once, that is in the first row of column “E”. Then we just simply copy this cell to the cells in the following rows. Of course, this will save much time.

PART III. CHART OF ACCOUNTS

The first step we have to do,after the file (workbook) created, is creating sheet containing chart of accounts named “ca”. The existance of this sheet will enable us to enter account in General Journal by just entering its number. Entering accounts this way will save time a bit and avoid errors. From the figure we can see that this sheet consist of four column:

1. Number

2. Account Title

3. NB

4. Explanation

Column Number contained account number. Column Account Title is for account title. Column NB for the normal balance position of each account. Other information about accounts can be filled in column Explanation.

All column starting from columns' header to the lowest row (marked by different colour) is named “rca”. This range name will make it esier for us to refer to when we use this data in the sheet in other sheet. When we need data in the range, in our formula for example, we just refer to the name of the range.

PART I. BASIC CONCEPT

May be it is hard to believe that we can process accounting data using Open Office Spreadsheet (Calc) with high degree of automation. As we know Open Office Spreadsheet is free multi purpose spreadsheet software that not designed specially for accounting data processing. Some of you may have been familiar with aplication software specially designed to process accounting data. Such a software automate most of clerical work needed, user only enter journal entries or transaction data and the software will do the rest. But using such a software will cost you some. The company with complex and numeoures transactions may be have no choice but using the accounting software.

Company with less complex transactions such as service companies and some trading companies can use open office spreadsheet to process accounting data. By adopting method explained here we can process accounting data with high degree of automation.

Figure above shows us the basic concept of processing accounting data using open office spreadsheet. Each box represents sheet that should be created. General journal and Summary of General Ledger have different colour to stress that this two have very important aspects in data processing. Most of works, after all sheet have been set, are done in General Journal. I can say that the only thing to do is entering journal entries in General Journal and we can automatically get General Ledger and Financial Statement. Summary of General Ledger considered as important one because as the figure suggest all data needed to prepared Financial Statement is flowed from here.

The Chart of Account, consist of account numbers, account titles and normal balance. These are supporting data mainly needed by General Journal and Summary of General Ledger. The General Journal is sheet where transactions are recorded. Almost no automation done here. We have to enter all transactions quite similar to manual process. But this is the only sheet that we have to enter the transactions because the other sheets will use data that available here. Transactions data in General Journal summarized for each account in Summary of General Ledger using “Sumif” formula so that ending balance of each account will be available here. This accounts' ending balance used by all financial statements by getting the figure form the cell in Summary of General Ledger sheet. Unlike in manual data processing, where figures used in preparing financial statement are taken from General Ledger, data processing using Open office Spreadsheet doesn't need General Ledger to do so. But General Ledger is standard record that must be kept. General ledger is created by filtering transactions data form General Journal using “If” formula, no more transaction entries needed. So the miticulous and timeconsuming manual posting from General Journal to General ledger can be omitted.

The detailed explanation of how to use Open Office spreadsheet (Calc) to process accounting data can be found in other part of this blog.