(Automatic) Accounting Workbook Instructions

Important: When you first open the file it may say it is a “Read Only” file; however, when you save the file ... you will be able to make changes and work in the spreadsheet.

Church Accounting Package

A set of 4 ebooks that covers the following topics...

  • Fund Accounting Examples and Explanations
  • Setting up a fund accounting system
  • Donation management
  • Minister compensation and taxes
  • Internal controls and staff reimbursements
  • Much more - Click here for details

Step by Step Instructions:

Watch this 5 Fund Accounting Spreadsheet Tutorial first and then read below for more detailed instructions.

After you have renamed the file and saved it (make sure you save in a location you can find it easily later such as your desktop).…close it and then reopen it.

Look at the bottom tabs.

The first tab is named Intro and contains information about the workbook and some tips for working in it.

The second tab is your all-important Chart of Accounts spreadsheet.

Here is where you will set up most of your information.

First step: Click in the heading and type in your organization’s name. (Notice that it auto filled into every worksheet (spreadsheet) from this one spreadsheet.)

Second step: Enter your accounts ( see tips on creating an effective chart of accounts)

This workbook (a single file containing a set of spreadsheets) is set up for 1 general fund, 9 income accounts, and 28 expenses accounts.

Type in your accounts. Notice that it auto filled into every worksheet (spreadsheet) from this one spreadsheet. This will make it much easier to make changes when needed. (The numbers and accounts listed are for example only.)

Don't Delete...Hide!

If you do not need all of the income and expense accounts, don't delete them and chance messing up preset formulas ... it is easier to hide what you do not need:

Simply highlight all the rows you do not need; right click; go down to hide; and click. For example, say you only need 2 income accounts for right now.

Highlight rows 8-14--right click---move down to HIDE and left click.

You’ll notice your rows now read …6, 7, 15, 16…. The extra income accounts is there hidden and can be “unhide” when you need them.

To "unhide" simply highlight rows 7 and 15--right click--left click on "unhide".

You will need to “hide” those extra income/expense accounts on every worksheet in the workbook.

IMPORTANT-Hide the same rows through the entire workbook.

For example: if you are hiding 3-9 income accounts then make sure you hide 3-9 income accounts throughout the entire workbook.

After this is completed, save your workbook as original template and close it.

(Make sure you click YES if a pop up appears asking if you want to save your work:-)

Now open it again and save another copy for this year.

Spreadsheet package

This accounting workbook is set up to track one fund...if you need more funds, consider the 5 Fund Accounting Workbook in the Spreadsheet Package.

Entering Data:

Important! Put your beginning balances in first!

Click on the Budget vs Actual tab.

Put your beginning fund balance in row 8.

Note: No matter which month you start using this spreadsheet….your starting balance will go in this cell on the (Budget to Actual) worksheet. All of the rest of the workbook will build from this amount. You will mess up formulas if you put your beginning balances in the month you start using this workbook.

Fill in your annual budget  in column E and your actual income and expenditures for the previous year in column D...if desired.

Do not fill out or type in column F as there are formulas in this column to will take your annual budget and divide it by 12 and multiply it by the number you put in cell F4 to give you a year to date budget amount.

Do not fill out or type in column G as there are formulas in this column to bring your year to date income and expenses over automatically from the monthly general ledger reports.

You do not need to do anything to the next worksheet (Summary by Month). It will automatically fill in as you enter your expenses and income in the general ledgers.

Scroll down to the bottom of this worksheet and notice row 54. This is the only place in this Summary by Month worksheet that you will type anything into.

You can use it to help reconcile your bank statements. Note: If your bank does not end on the last day of each month, you may not be able to use this simple reconciliation.

Click on the 5th tab named GL-Jan. (There is a general ledger worksheet set up for each month) Each of these worksheets is where you will enter the data that the rest of the worksheets use to generate reports

  • Contributions, fundraiser funds, etc. will be entered under its corresponding account in the credit (Income)column
  • Utilities, office supplies, payroll, etc, will be entered under its corresponding account in the debit (Expense)column.
  • Don’t worry if you accidentally delete a formula. Just click the “Go Back Button”.

Notice every time you enter an amount in a general ledger it is automatically carried to the monthly, quarterly, and annual reports; the Budget vs Actual report; and the Summary by Month report.

Note: If you add extra lines in your income/expense accounts, you will need to reset the balance column (see instructions for resetting it ).