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.