Monday, March 17, 2014

Bookkeeping kit-MS Excel

BOOKKEEPING KIT-MS EXCEL Reneson M. Tiongson

IN THIS BLOG YOU CAN TOUR WITH ME HOW TO MAKE PROJECT USING BASIC FUNCTION IN MICROSOFT EXCEL.

You will learn here from start to end. 
1. Start with Company information 
2. Chart of Account Set up 
    2.1 General Accounts 
    2.2 Subsidiary Accounts 
    2.3 Beginning balance form entry 
4. Purchase Journal form-entry form 
5. Cash Disbursement Journal form-entry form 
6. Cash Receipt Journal form-entry form 
7. General journal form-entry form 
8. Trial balance-Working paper- 7.1 statement of financial position(bs) 7.2 statement of financial activity(Is) (Total)
8. Trial balance- monthly –per account title.


 1. COMPANY INFORMATION 1. It is important to set-up the company information because that is one goal of what our goal about our excel kit. The business information tells us what kind of account title we want to use later so, be sure you have it because it can be link to any working paper that is not hard for us to set-up dates. In this presentation this is bookkeeping kit for tax compliance annual but, you can make it also as your company database in your own convenience. 

Illustration 1. Company Information

2. CHART OF ACCOUNT SET-UP Chart of Account set-up are so very Important because this are language use for your account title and all entry account title, that support our trial balance report or Financial Statements. Once you have this, then, you can set-the Subsidiary accounts from your general accounts title. Take note that Be sure you understand the correct normal balances of each accounts title about simple debit and credit. If you don’t understand then, research about accounting or any tutorial online to help you.


Illustration 2

 CODE FORMULA:  CODE IT CAN BE TYPE MANUALY OR IF SEQUENCE YOU CAN USE =IF(LEN(B8)>0,SUM(A7+1)," ")


Illustrationc2.1

 Continue illustration 2/2.1 support

Code- found in left versus right. Is there any difference on that?  Well, that is telling us that binary way in handling accounts.  Left code dictates that if we type code manually and account title will follow while the right code if we want the account title as main source, then, automate the code.  But, in here I choose the right code because much easier and it will discuss later.  Be sure both must be filled up.
Account Title in here, no formula yet, be sure your account title are the language to produce financial statements.  Just type it manually.
Column D or normal balance.  In here, this is to communicate the MS excel what we want to do with different accounts.  And simply telling it, that what outcome if certain accounts having an entry debit and credit.  Because in real sense, computer has no senses, but, it can understand us by doing this.  Is  that fine to you?
Column E or FS Category. In here you classify the accounts where it belongs.  In this example, I showed to you that Balance sheet accounts or real accounts i put it as “B” means all account entry real accounts go to balance sheet while “I” all accounts amount will go to Income statement.
  Column 6 or IS Category.  In here illustration 2.1 telling us that it classify the “N” as income classification while the “E” expenses classification.  You can choose what kinds of code you like in your own project.
 Illustration 3





2.2 Subsidiary Accounts


In the code you can type it manually if not in sequence or make it formula the second row.  In the same manner same rule as general accounts title you can add subsidiary accounts title in accordance with what subsidiary accounts support to your general accounts title.  Means all those in the subsidiary amounts will go automatically to your general accounts title.
 Illustration 4


Where to set up your beginning balance amounts to start your transaction.
Now, we are ready for entry form.

3.  Purchase Journal form-entry form

Before you continue to another forms be sure to set-up correctly the same with other forms format so that you will change only the Title Description.  So, be sure this form is perfect already.  And formulas needed to link with other reports such working trial balance and monthly working paper.  In here, I will show you some ways to do it. 
Illustration 5


 In here much easier to make formula in the code of “account title” and “sub account”.
Support Illustration 5

=IF(LEN(H9)>0,VLOOKUP(H9,'ACCOUNT TITLE'!$B$7:$C$74,2,FALSE)," ") formula to use to pick up code.

I using MS excel 2010, but in lower version you should copy by using + sign in your purchase form sheet and hide the cells. 

In here you should go to “Data” then “validation” then setting, after that, scroll to “list” in MS Excel 2010 you can go directly to list of chart of account title general accounts. Then go to column “B” scroll down, be sure up to 200 cells to make it sure all accounts title will be read.  If not, then you will edit this many times.  So, you don’t need to type it manually just scroll the cell in your account title then choose account title you like.
Support illustration 5

 

=IF(AND($C5>'myworking paper'!$M$20,'FOR WORKING PAPER'!$B4<'myworking paper'!$N$20,$G5='myworking paper'!$F$6),12,IF(AND($C5>'myworking paper'!$M$19,'FOR WORKING PAPER'!$B4<'myworking paper'!$N$19,$G5='myworking paper'!$F$6),11,IF(AND($C5>'myworking paper'!$M$18,'FOR WORKING PAPER'!$B4<'myworking paper'!$N$18,$G5='myworking paper'!$F$6),10,IF(AND($C5>'myworking paper'!$M$17,'FOR WORKING PAPER'!$B4<'myworking paper'!$N$17,$G5='myworking paper'!$F$6),9,IF(AND($C5>'myworking paper'!$M$16,'FOR WORKING PAPER'!$B4<'myworking paper'!$N$16,$G5='myworking paper'!$F$6),8,IF(AND($C5>'myworking paper'!$M$15,'FOR WORKING PAPER'!$B4<'myworking paper'!$N$15,$G5='myworking paper'!$F$6),7,IF(AND($C5>'myworking paper'!$M$14,'FOR WORKING PAPER'!$B4<'myworking paper'!$N$14,$G5='myworking paper'!$F$6),6,IF(AND($C5>'myworking paper'!$M$13,'FOR WORKING PAPER'!$B4<'myworking paper'!$N$13,$G5='myworking paper'!$F$6),5,IF(AND($C5>'myworking paper'!$M$12,'FOR WORKING PAPER'!$B4<'myworking paper'!$N$12,$G5='myworking paper'!$F$6),4,IF(AND($C5>'myworking paper'!$M$11,'FOR WORKING PAPER'!$B4<'myworking paper'!$N$11,$G5='myworking paper'!$F$6),3,IF(AND($C5>'myworking paper'!$M$10,'FOR WORKING PAPER'!$B4<'myworking paper'!$N$10,$G5='myworking paper'!$F$6),2,IF(AND($C5>'myworking paper'!$M$9,'FOR WORKING PAPER'!$B4<'myworking paper'!$N$9,$G5='myworking paper'!$F$6),1," "))))))))))))

 Illustration 5 support

d f h j l d f h j l d f h j l d f  as you see this, that is the cells where your working paper sheet start. That signifies the accounts title from our chart of accounts.

 Illustration 5 support

=SUMIF('PURCHASE JOURNAL'!$R$5:$R$2000,'myworking paper'!$A$10:$A$21,'PURCHASE JOURNAL'!$K$5:$K$2000) debit cell formula in working paper in monthly report.

  =SUMIF('PURCHASE JOURNAL'!$R$5:$R$2000,'myworking paper'!$A$10:$A$21,'PURCHASE JOURNAL'!$L$5:$L$2000) credit cell formula in credit in working paper for monthly report.

=SUMIF('TRIAL BALANCE'!$A$7:$A$42,'myworking paper'!D6,'TRIAL BALANCE'!$C$7:$C$42) formula to look up the beginning balance.
Illustration 5 support.

If you’ve done that correctly then your working paper will easy for you. But working paper monthly be sure to the formula below.

1

=VLOOKUP(D6,'ACCOUNT TITLE'!$A$7:$B$74,2,FALSE) for account title.

=SUMIF('TRIAL BALANCE'!$A$7:$A$42,'myworking paper'!D6,'TRIAL BALANCE'!$C$7:$C$42) formula to get data from your beginning balance. 
Illustration 6-Cash Disbursement. form

 
5.   Cash Receipt Journal form-entry form
Illustration 7-Cash Receipts form
  Illustration 8 General Journal form




Illustration 9-Trial balance
 

Illustration 9 support.
code =IF(LEN(B8)>1,VLOOKUP(B8,cpaassociates,2,FALSE)," ")
ACCOUNT TITLE=IF('ACCOUNT TITLE'!B8>0,'ACCOUNT TITLE'!B8," ")
BEG BALANCE =SUMIF(' BEGINNING BALANCE ENTRY'!$C:$C,'TRIAL BALANCE'!$A$7:$A$56,' BEGINNING BALANCE ENTRY'!$G:$G) DEBIT
=SUMIF(' BEGINNING BALANCE ENTRY'!$C:$C,'TRIAL BALANCE'!$A$7:$A$56,' BEGINNING BALANCE ENTRY'!$H:$H) CREDIT

Transactions=SUMIF('CASH DISBURSEMENT'!$H$5:$H$1994,'TRIAL BALANCE'!$A$7:$A$56,'CASH DISBURSEMENT'!$L$5:$L$1994)+SUMIF('PURCHASE JOURNAL'!$G$5:$G$2001,'TRIAL BALANCE'!$A$7:$A$56,'PURCHASE JOURNAL'!$K$5:$K$2001)+SUMIF('CASH RECEIPT JOURNAL'!$H$5:$H$1002,'TRIAL BALANCE'!$A$7:$A$56,'CASH RECEIPT JOURNAL'!$L$5:$L$1002)+SUMIF('GENERAL JOURNAL'!$C$5:$C$2001,'TRIAL BALANCE'!$A$7:$A$56,'GENERAL JOURNAL'!$G$5:$G$2001) DEBIT


TRANSACTION=SUMIF('CASH DISBURSEMENT'!$H$5:$H$1994,'TRIAL BALANCE'!$A$7:$A$56,'CASH DISBURSEMENT'!$M$5:$M$1994)+SUMIF('PURCHASE JOURNAL'!$G$5:$G$2001,'TRIAL BALANCE'!$A$7:$A$56,'PURCHASE JOURNAL'!$L$5:$L$2001)+SUMIF('CASH RECEIPT JOURNAL'!$H$5:$H$1002,'TRIAL BALANCE'!$A$7:$A$56,'CASH RECEIPT JOURNAL'!$M$5:$M$1002)+SUMIF('GENERAL JOURNAL'!$C$5:$C$2001,'TRIAL BALANCE'!$A$7:$A$56,'GENERAL JOURNAL'!$H$5:$H$2001) CREDIT
 
ADJUSTED TRIAL BALANCE=IF(AND(A7='ACCOUNT TITLE'!A7,'ACCOUNT TITLE'!D7="D"),SUM('TRIAL BALANCE'!C7+'TRIAL BALANCE'!E7)-SUM('TRIAL BALANCE'!D7+'TRIAL BALANCE'!F7),0) DEBIT
=IF(AND(A7='ACCOUNT TITLE'!A7,'ACCOUNT TITLE'!D7="C"),SUM('TRIAL BALANCE'!D7+'TRIAL BALANCE'!F7)+SUM('TRIAL BALANCE'!E7),0) CREDIT
 
INCOME STATEMENT=IF(OR(G7>0,G7<0),IF(AND(A7='ACCOUNT TITLE'!A7,'ACCOUNT TITLE'!D7="D",'ACCOUNT TITLE'!E7="I"),'TRIAL BALANCE'!G7,0),0) DEBIT
 
=IF(OR(H7>0,H7<0),IF(AND(A7='ACCOUNT TITLE'!A7,'ACCOUNT TITLE'!D7="C",'ACCOUNT TITLE'!E7="I"),'TRIAL BALANCE'!H7,0),0) CREDIT
=BALANCE SHEET IF(OR(G7>0,G7<0),IF(AND(A7='ACCOUNT TITLE'!A7,'ACCOUNT TITLE'!D7="D",'ACCOUNT TITLE'!E7="B"),'TRIAL BALANCE'!G7,0),0) DEBIT
=IF(OR(H7>0,H7<0),IF(AND(A7='ACCOUNT TITLE'!A7,'ACCOUNT TITLE'!D7="C",'ACCOUNT TITLE'!E7="B"),'TRIAL BALANCE'!H7,0) CREDIT.

Try the challenge few only knows that they are more capable than others.  God bless


                
                   >>>>>>>>THE END>>>>>>>>>> 

Presented by Reneson M. Tiongson

Presentation based on his personal experiences using MS Excel for Business.

No comments:

Post a Comment