Fast Accounting, Simple Accounting, Inexpensive Accounting, For Non Bookkeepers- Summarize a Year in One Sitting

Most small business just use Quickbooks to sort their data and put it in the form of financial statements. Often the person using Quickbooks doesn't even understand what the program is doing. This article will show you how to do that using a spreadsheet program. Most people who read this article already know the few spreadsheet operations needed to replace Quickbooks.

Every year Intuit seems to come out with a new version of Quickbooks and Quicken. While this may seem like progress for many users it is just too complicated and does all kind of things that they just don't know. The system recommended in this article will work for:

• Most cash businesses
• Most sole proprietor-ships (Schedule Cs)
• Rental Properties
• Restaurants
• Professional PracticesWith adjustments,
• Small business on the accrual method

I am sure that there are thousands of small businesses which have not yet started summarizing their books for 2011. This is by far the fastest way to summarize and categorize the year's data.

The columns of the templates have the following headings:

• Date
• Check Number
• Description- place any comment here; it is a good idea to enter the name of the payee for checks. For large sales, the customer may be helpful.
• Account (category)- this is the account or category that best describes the source of the deposit or the purpose of the disbursement.
• Deposit Detail- This splits the deposit into it accounts.Deposit- The total of the deposit should be entered here; this is the amount that will appear on the bank statement.
• Disbursement - this is the total amount of the check, or disbursement; this is the amount that will appear on the bank statement.
• Disbursement Detail- this splits disbursements into more than one account. For example, a check written for office furniture and office supplies would be broken down into office furniture, and office supplies.
• Book balance - this is the cash balance on the above line, plus total deposits on this minus cash disbursements on this line. This is the balance in the checkbook

On Exhibit 1 we show how this spreadsheet looks with data.

Note Column A would have the date and Column B the check number; these columns are left off so that the spreadsheet can fit on this blog.

Please note the following:

• Column totals are at the top of the page.
• The detail of the deposit and disbursements equals the total (columns E= F and G=H) as data is entered it is necessary to make sure this is always true.
• Column I line 10 is the beginning balance; column I line 11 is the above balance plus deposits less disbursements (I10+F11-G11). The formula is replicated all the way down.
  By using the windows or titles function the totals and headings can be seen while working lower on the spreadsheet.

* * * * * * * * *

You could stop at this point and if your accountant understands spreadsheets he could finish the job.

* * * * * * * * *

The Commands You Must Know

Less than 10 spreadsheet operations, functions, or commands have to be known in order to keep books using this method. On Appendix A, the procedures are explained for Excel, Lotus 123, and Microsoft Works Spreadsheets.

Summarizing the Information

Before summarizing the data it is necessary to save a copy of the raw data. Print it out too. I call it "data" and note the date.

Please examine Exhibit 2; this compiles totals by account. Note the $215 in column K; this is the total advertising. The $137 is total auto. The $5,000 in column J is the total bank loan while the $680 is total non taxable sales.

This is Exhibit 1 after the following steps:

• The data sort function (some spreadsheet programs may just call it the sort function) was used to sort the data. The data starts with the line numbered one which is actually 11 lines down. It was sorted by account (category); the column with the sort key is column D.
  Columns J (total deposits by account) and K (total disbursements by account) were added.
• The sum or @ sum function was used to total all the items with the same account. Column J contains totals of the items in each account in E; column K contains totals of the in each account in F. Obviously, the totals of column J equals column E and column F equals column K.
• To make the output smaller and easier to work with columns G, H, and I were deleted.

Save this file; I always call it "sorted data" and note the date.

Exhibit 2 functions like the general ledger in accounting software. A general ledger really is just a sorted summary of transactions with totals by accountant. This is a good summary to give to your accountant, but, it is easy to improve on this.

Trial Balance

Exhibit 3 is what accountants call a trial balance.

It is a very easy to compile:

• First, convert the formulas in columns J and K to numbers. Use the Copy, and Paste- Special commands (or Range and Value).
• Next use the Data and Sort function to first sort the subtotals in the deposits subtotal column and then sort the disbursements subtotal column. The result is that most lines do not have anything in either subtotal column. Delete those lines; then delete all columns except: (1.) Account, (2.) Total Deposits by Acc. (account), and (3.) Total Disb. (disbursements) by Acc (account).

To prove that the computations are correct check the following:

Beginning Balance + Deposit - Disbursements = Ending Balance

This is the trial balance your accountant will need. Print it out and save it. I call name it "trail balance" and give it a date.

Reconcile the Book Balance With the Bank Every Month

The balance on the spread sheet should be reconciled with the bank every month. There usually is a form on the back of the bank statement that should help. It is helpful if you print out the spreadsheet and check off the items that cleared the bank. Those that have not cleared will be reconciling item and will be listed on the reconciliation.

How to Compile a Year at One Sitting

What if data for the entire year has not been summarized? An easy way to summarize books is to work off of the bank statements. Enter the items off the bank statements in the same order that they appeared on the statement. Then fill in the account (category) and do the steps listed above. The amount in the book balance column should agree with the amount in the banks statements because the bank statements are being used as the source document.
The items that have not cleared the bank should then be summarizes. By examining the statements for the next month or two (i.e. January and February for December year ends) those items can be easily found. Your accountant will have to add these to your trail balance, also, it is necessary to subtract out the items that cleared in the current year that were taken account in the prior years tax return.

This is by far the fastest way to summarize information that I have ever found; it is relatively simple.

Accrual Method Taxpayers

Since this only summarizes cash disbursements and cash receipts, it is not on the accrual method. If you are on the accrual method it is necessary for your accountant to make entries to record, accounts receivable, inventory and accounts payable. For small business it is easy to list these as of the last day of the year and your accountant can make the necessary entries.

Try It! You Will Like It!

Excel, Lotus, Microsoft Works Spreadsheets, and spreadsheets that can be downloaded free can be used.

Hub users can send a e-mail to the author and he will send you the template.

The biggest advantage using this method is that the person doing the work understands what they are doing. They never wonder why the computer did something. It is always faster to learn something that is logical easily understood.

Just give it a try. Enter the data on Exhibit 1 on a spreadsheet and see it you can come up with Exhibit 2 and 3. I bet you can do it in very little time!

Sophisticated Analysis

On Exhibit 4 the data compiled on Exhibit 3 is summarized on the form of a cash flow statement. Exhibits 1, 2 and 3 should be all your accountant needs, but, Exhibit 4 can be very meaningful to the entrepreneur in managing their business.

Note how the balances on Exhibit 3 are classified into operating, investing, and financing activities. The beginning cash plus the total equals the ending cash balance. This can be used to manage a small business.

Spreadsheet Commands

In order to do this you need to master 8 easy spreadsheet operations. The following is a summary by spreadsheet program:

Excel:

Copy- in order to copy click and highlight the cells that are to be copied and right click on "copy," or use the "copy" listed in the "edit" menu; the data is now stored in the computer.

Paste- just highlight were the copied data should go, right click on "paste," or use "paste" which is listed in the "edit" menu.

Paste Value- in order to change a formula into a value it is necessary to first copy the formulas, then click on the edit menu; a list will drop down; click on "paste special;" a box will appear; check the "value" box and click on "OK".

Window- place you cursor in column A down to the last row that you want to be in the heading. In Exhibit 1 that would be 9 rows down. Click on "window" which is near the top of your screen; then click on "freeze panes."

Formula- first enter "=" then type the formula. In Exhibit 1 the formula at I11 is: "=(I10+F11-G11)." Use copy past to enter or replicate that formula all the down column I.

Sort- click and highlight the cells you want sorted; click on the data menu; click on "sort" and then in the "by" boxes click on the columns you want sorted by ( i.e. on Exhibit --- , column would sort accounts); click "OK."

Delete- to delete a column or row, click and highlight a cell in the columns or rows that are to be deleted; click on the edit menu; click on the "row" or "column" box and then "OK."

Add- the formula to add cells A2 to A10 is: =SUM(A2:A10) .

Lotus 123:

Copy- in order to copy click and highlight the cells that are to be copied; a right click on "copy," or use "copy" listed in the "edit" menu; the data is now stored in the computer.

Paste- just highlight were the copied data should go, right click on "paste," or use "paste" which in the "edit" menu.

Titles-place the cursor at on the line below the line that is to be in the tiles; on the view menu click on titles and then click on "rows above selected cell:" click "OK."

Formula- enter the formula in parenthesis. In Exhibit 1 the formula at J11 is: "(J10+G11-H11)." Use copy and paste to enter or replicate that formula all the down column J.

Sort- Click and highlight the cells you want sorted; click on the range menu; click on "sort;" in the "columns available box" click on the column that the data should be sorted by ( i.e. to sort Exhibit 1 by account, it would be necessary to click on ----------); click on "OK."

Delete- to delete a column or row, click and highlight a cell in the columns or rows that are to be deleted; click on the range menu; click on the row or column box and then "OK."

Add cells- the formula to add cells A2 to A10 is: @SUM(A2..A10) .

Microsoft Works Spreadsheet:

Copy- in order to copy click and highlight the cells that are to be copied and right click on "copy," or use the "copy" listed in the "edit" menu; the data is now stored in the computer.

Paste- just highlight were the copied data should go, right click on "paste," or use "paste" which is listed in the "edit" menu.

Paste Value- in order to change a formula into a value it is necessary to first copy the formulas, then click on the edit menu; a list will drop down; click on "paste special;" a box will appear; check the "insert value in the cell" box and click on "OK".

Window- place you cursor in column A down to the last row that you want to be in the heading. In Exhibit 1 that would be 9 rows down. Click on "window" which is near the top of your screen; then click on the view menu; then click on "split." Some lines appear, they can be moved around. Click "OK" when the location is correct (usually this will be at the left of cell A-9)

Formula- first enter "=" then type the formula. In Exhibit 1 the formula at I11 is: "=(I10+F11-G11)." Use copy past to enter or replicate that formula all the down column I.
Sort- click and highlight the cells you want sorted; click on the data menu; click on "sort" and then in the "by" boxes click on the columns you want sorted by ( i.e. on Exhibit --- , column would sort accounts); click "OK"

Delete- to delete a column or row, click and highlight a cell in the columns or rows that are to be deleted; click on the insert menu; click on the " delete row" or "delete column" box and then "OK."

Add- the formula to add cells A2 to A10 is: =SUM(A2:A10) .