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.