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 and the third tab is your Budget to Actual spreadsheet.(In the accounting workbook for multiple businesses–these 2 spreadsheets are combined into one)
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 from this one spreadsheet.)
Second step: Enter 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.
Note: All of the “report” worksheets are “Protected” to prevent accidentally deleting a formula as they are all view-only worksheets. To modify these spreadsheets (hiding rows) simply click on the Review tab (in Excel), in the Changes section, click on “Unprotect Sheet”. I would protect the worksheets again when you are finished setting up your workbook. Do the same as above, but click “Protect Sheet”. A pop up window will appear. You can put a password in or just leave blank then click OK.
If you do not need all of the income and expense accounts, it is very easy 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.
If you need more income/expenses accounts, see this page for instructions on adding income/expenses accounts.
However, unless you are very familiar with Excel or your spreadsheet program, I would strongly suggest using a double entry accounting system such as QuickBooks as the spreadsheets are full of formulas and can be time consuming to add more accounts to.
Begin to use!
Important! Put your beginning balances in first!
Click on the Budget to Actual tab.
Put your beginning fund balances in row 8.
Note: No matter which month you start using this spreadsheet….your starting balance in each biz will go in these cells on this (Budget to Actual) worksheet. All of the rest of the workbook will build from these amounts. 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. 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 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
Quick Lesson in Basic Accounting:
You will notice the business are set up in a “T” account with three parts: the title of the business, debit and credit. You could relabel the DEBITS to EXPENSES and the CREDITS to INCOME to make it easier on you if you choose.
So:
- Sales, interest income, etc. will be entered under its corresponding account in the credit (Income)column (make sure you put income only in the top income accounts)
- Utilities, office supplies, payroll, etc, will be entered under its corresponding account in the debit (Expense)column.
(make sure you put expenses only in the bottom expense accounts) - 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 Profit and Loss reports; the Budget to 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).
Tip
Get into the habit of saving your workbook often as Excel does not automatically save as Word does. You can click the little blue floppy icon up in the top left corner of your Excel window or click on the green File tab in the top left corner and then click save.
