Note: Some view only pages are locked to prevent you from accidentally erasing formulas...if yours in grey and won't let you change anything, simply click in the REVIEW tab and click Unprotect Sheet. No Password Required!
Now let’s add those additional fund accounts we set up in the general ledger....
Following our previous example... we are going to insert 3 new fund accounts so we need to select 3 columns such as F, G, and H.
You can either right click and click on Insert or…. On the Home tab, in the Cells group, click the arrow next to Insert, and then click Insert Sheet Columns.
Then highlight (select) the 3 completed columns such as I,J, and K, click copy in your tool bar, select first new column such as F and hit enter.
Rename and color font to match corresponding General Ledger fund accounts.
Pick up corresponding beginning balances in row 9 from Budget to Actual report or General Ledger. Example: Click CELL F9 (2nd Fund), hit equal sign, go to Budget to Actual and click CELL H8 then hit ENTER. Do the same for the rest of the new funds.
Now you are going to have to pick up all the corresponding income and expense balances of the new fund columns you built in the general ledger. Example: click in the new fund you created in column F in the Income 1 (CELL F12), hit the equal sign, then go to the corresponding credit total in that income account (CELL G17)...you can then hit enter just to pick up that credit total or you could hit the minus sign and pick up the debit total in that first income account (CELL F17)if you think you would be doing any debits or adjustments in that account.
Do the same for every new income and expense accounts you created in the new fund accounts.
Repeat all steps for the rest of the 11 monthly reports.
Adding Additional Fund Accounts to the Quarterly and Annual Report:
Repeat steps above except when it comes to picking up the balances you will be getting them from the monthly reports for the Quarterly reports and picking up the Quarterly totals for the Annual report.
Once you get the first account set up in the new fund, you can use your pull down fill handle to autofill the rest in that column.
One Last Step: Go to Budget to Actual Report. Time to fix those #REF! in your new fund columns;-)
Click in CELL K10 (3rd Fund: First Income), hit the equal sign.
Go to the Annual Report and click CELL G12 and hit ENTER.
Click back in CELL K10 and grab fill handle.
Pull down to row 13 to finish picking up income "Actual" totals
Click in CELL K17 (3rd Fund: First Expense), hit the equal sign.
Go to the Annual Report and click CELL G19 and hit ENTER.
Click back in CELL K17 and grab fill handle.
Pull down to row 36 to finish picking up expense "Actual" totals.
Do the same for the other 2 new funds.
Now your finished! Congratulations!
Click on image above for more details.
The Spreadsheet Package includes a workbook for tracking contributions, an automatic accounting workbook for tracking up to 5 funds,10 income accounts and 40 expense accounts and several more accounting workbooks. It also includes a Word document with 3 customizable contribution statements.Click here for more information or purchase now for $19.95
Need training on how to use QBO or Aplos effectively for a church or better yet ...need an expert in either of those accounting software to do your monthly bookkeeping and/or payroll? See more on our accounting services!