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.
Below are step-by-step instructions for setting up and using the free manual accounting spreadsheets.
The Manual Accounting Workbook includes:
- 12 Monthly General Ledgers
- 12 Monthly Reports
- 1 Annual Report
Unlike my automatic accounting spreadsheets…only the annual report is a “view only” sheet. (A view-only sheet is one that is full of formulas and will automatically fill out from data on other sheets.)
The annual report is set up to carryover your monthly totals which means at anytime, you can look at how much you have spent in any account for the year.
I built these spreadsheets for small and start up businesses.
Set up Instructions:First: Personalize
- SAVE an original copy.
- Look at the bottom tabs.
- Click on the tab named “GL-Jan”
- Click on “Name” in – Row #1.
- Type in your business’s name-(All you “set up” on this first sheet will auto fill in the rest of the months’ general ledger worksheets).
- Type in your current year. Will need to repeat for GLs in remaining months .
- Type in your business names (Row #7 ) if you are using the 3Biz manual spreadsheets. Tip: If you do not need the third Biz column simply highlight the columns you want to hide and right click your mouse; go down to hide and click. You would then need to do that to all the reports…but when you need the extra business column it will be there to unhide and use
- Put in beginning balances-Row #5 in the Jan GL. – (*Even though you will probably be starting in another month…the balances will carry forward)
- Look at the bottom tabs…click on the tab named “Jan”.
- Type in your business’s name – Row #2 – (All you “set up” on this first report sheet will auto fill in the rest of the reports).
- Type in the name of the report- Row #3 –(This is an Income Statement. You can name it whatever you want though.).
- Type in your current year. Will need to repeat for remaining reports.
- Type in business names-Row #8 and Row #9. (3 biz sheet only)
- Type in Revenue and Expense accounts. (You only have to set up Jan. the rest of the months will auto fill).
- There are no formulas in this first monthly report so you can delete rows, add rows, change around the format…anyway you want to set it up. Only be aware that the line items on the following months have formulas in them to auto fill rest of the reports and annual report so if you change much you will have to hand set each month accordingly…which really isn’t that big of deal…so remodel to your heart’s content.
- SAVE one more copy! This one will be your original to use every year. Can delete original one not personalized now.
Entering Data:Quick Lesson in Basic Accounting:
- This is a basic general ledger. You just enter revenue and expenses as you would in a paper ledger.
- At the end of the month…click on second tab and fill in monthly report. I used to print out the GL and highlight in different colors the different line items. Whatever works for you.
- Remember the Annual Report is a view-only sheet and will automatically fill in as you complete each month’s report…unless you did some major remodeling…then you will have to manually fill in the numbers.
- Example: Say I needed 2 more rows; I would click on any row. Go up to the toolbar. Click on “insert” twice for 2 more rows. Notice there are empty cells now in the “balance” column. Click on the last row with numbers in it or you can also select (highlight anywhere in the number row) the number of rows you need, right click, insert. (example:click on the number 14, hold your mouse button down and “select” rows down to number 21, right click, insert.)
- Notice the little black box on the bottom right corner of that cell. That is your “Fill Handle”. Put your mouse pointer over it. Notice how the pointer goes from a fat white plus sign to a skinny black plus sign. That means your fill handle is ready. Click and pull down to the end of the empty rows. Your formulas are now set.
- To delete rows, click and highlight the rows you want to delete. Hit delete on keyboard or on editing ribbon. Notice that from that point down in the “balance” column says “REF!” Click on the last cell with a number in it. Pull fill handle down to the black line. All of the rest of the formulas are now set.