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
Illustration 8 General Journal form
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
Try the challenge few only knows that they are more capable than others. God bless
>>>>>>>>THE END>>>>>>>>>>
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.
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 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)
CREDITTransactions=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.
