Important: When you first open the file it may say it is a “Read Only” file; however, when you save the file on your hard drive you will be able to make changes and work in the spreadsheet.
After you have renamed the file and saved it to your hard drive (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.)
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.
Important! Put your beginning balances in first!
Click on the Budget vs Actual tab.
Put your beginning fund balance in row 8.
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
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 ).