TEMPLATE INFORMATION VERSION 6.2 06-1-2007 ACCOUNTS SHEET AND ACCOUNTS REPORT This template is a working model of, and prints directly on the Society's forms: Accounts Sheet S-26-E, Revision date 1/06 and Accounts Report S-30-E, REVISION DATE 1/05 It’s important to note the revision date in the lower left of your forms, as the Society changes the layout slightly from year to year. This template computes all totals and transfers the totals from the Accounts Sheet to the Accounts Report form. The Accounts file name has an XLS name extension. Excel will give you the option to save your file in a newer format. You may do so BUT….... IT SHOULD HAVE A "XLS" EXTENSION. NOT A "XLSX" or "XLSM" extension. Excel 2007 users should refer to the inclosed PDF instructions for allowing macros to run. When you open this template, Excel may prompt you with the message: AccountsXX.xls should be opened as "read-only" unless you need to save changes to it. Open as read only?" You should answer "NO" since you will be making changes and additions to it. This template uses macros so when you open this file Excel will prompt you with a warning that it contains macros you will want to click the "enable macros" button. All totals including individual "TC" calculations and dates carry over to appropriate sections of each form. I would suggest two prerequisites to using this template: 1. That you have maintained the congregation accounts by hand, and are familiar with the "mechanics" of it. And 2. That you have a basic knowledge of using spreadsheets. I also suggest the first time you use this program, you should input a set of monthly accounts that you previously did successfully by hand, then compare the two. TIPS 1. Click on the sheet tabs located on the lower left of the screen labeled "Accounts_Sheet", Accounts_Report and "Instructions" to move to each form. 2. Printing is done by use of pre-defined Range names and macros. To print a form: (1) Insert a society form in your printer. (2) Click on the print button located on the top of the respective form. The Accounts Sheet requires that you place the form in the printer a second time to print the back of the page, (page 2). It’s important to note the revision date in the lower left of your forms, as the Society changes the layout slightly from year to year. Also, use only original forms, NOT photocopies. Copies may shift the layout. Adjusting the print layout should not be necessary,however Every printer prints slightly different, so if you need to make some alignments, follow these instructions: MAKING ADJUSTMENTS: Assuming you are using the Society's forms S-26-E revision date 1/06 in English. First be sure your feeding the forms into your printer strait and correctly. Test to see if the numbers CONSISTENTLY print in the same places on the forms. If it's NOT consistent, weather right or wrong, then part of the problem is the way your printer is accepting your forms. Experiment with the way your feeding the forms to your printer. If that doesn't solve the problem, try this next. The Account06.xls is formatted for Society's Account Sheet form revision date 1/06 in English. If you don't have that version of the forms, or if your numbers need alignment adjustment, you may begin by "OPENING" the ACCOUNTS, file and "SAVE" it with a DIFFERENT NAME. Then "OPEN" the new file you just saved. "UNPROTECT" the sheet in question, and ADJUST the row widths where necessary. START ALL ADJUSTMENTS BEGINNING AT THE TOP OF THE FORM, AND WORK DOWNWARD. NOTE: INCREASING a row width at the top of a form will PUSH EVERYTHING DOWNWARD on the form. So make your adjustments one at a time and ABOVE the area in question. CAUTION.....ADJUST ONE ROW AT A TIME, then test print on the forms you have. SEE where it moved the numbers, and repeat this process until your happy. If you need to move numbers to the right or left; keep in mind that ALL columns to the RIGHT of the column you adjust are affected. For example: If you "widen" column "E" the "DATE" column on the "ACCOUNTS SHEET", ALL the other columns "F thru M" will move to the RIGHT. 3. The column titled "TC" on the Accounts Sheet is currently set up to recognize (in upper or lower case), the Receipts codes: (W for WWW); (K for KHF); (C for Congregation); and (A for Assembly Hall Fund). The "A" code may be changed if needed by changing the field located at hidden cell (D17). This will not be necessary in most cases, and extreme care should be taken if you decide it needed. This is how to change the TC codes: W = Society World wide work K = Society Kingdom Hall fund C = Congregation funds A = Assembly hall fund It is a little tricky to change these codes, so follow carefully and do this: MENU: "Tools"---> "Protection"---> "Unprotect sheet" MENU: "Edit" ---> "Go To" ---> in the "Reference field", type A1, then click OK. it will look like nothing has happened, but do not worry. Next, MENU: "Format" ---> "Column" ---> "Unhide". This will unhide column "A". Next, put the curser in cell "A1" if it is not already. Then hold down the "Shift" key with your left hand, and with your right hand press the keyboard "Arrow" key to the RIGHT one space so column "E" is also highlighted. Next, MENU: "Format" ---> "Column" ---> "Unhide". This will unhide columns "B", "C", and "D". Next, go to the cells at "A17" through "D17" and change the TC codes W,K,C,and A to any letter you want, in either upper or lower case. Next, hide columns A through D. Put the curser in cell "A1" . Then hold down the "Shift" key with your left hand, and with your right hand press the keyboard "Arrow" key to the RIGHT three spaces so columns "A" through "D" is highlighted. Next, MENU: "Format" ---> "Column" ---> "Hide". MENU: "Tools"---> "Protection"---> "protect sheet" 4. You will have to insert the balances forward from the prior month reports on each of your monthly worksheets. All worksheets and cells are protected, except for the cells which you will have to manually input a number from the prior month. The unprotected cells are shown in blue fonts. Note also items 5 and 6 in the caveats section of these instructions. 5. You may use the "COPY" (Ctrl-C) and "PASTE" (Ctrl-V) commands, or "copy icon" to copy "Transaction descriptions" and "TC" codes. This will prove to be a great time saver. Please take note of the caution below concerning the "MOVE" and "Ctrl-X" commands. You may use the "Delete" key to remove input errors. 6. Just below the bottom of page 1 of the Accounts Sheet, are two helpful summaries. The first is a breakdown of the total contributions to the Society for (WWW) and (KHF). Located below this is a cross check of the "Contributions" and "Receipts in". If the number on the bottom is NOT ZERO, you most likely forgot to enter a "TC" code for one or more receipt. IT IS A GOOD PRACTICE TO CHECK THIS PRIOR TO PRINTING YOUR REPORT. If the total funds sent to the Society include any additional donations or payments OTHER than WWW and KHF, such as KHAA. You must manually add this KHAA to your disbursements line on both the Accounts Sheet and Accounts Report forms. 7. Located just below these total summaries of the Accounts Sheet is a macro button. Click on this button to jump to page 2 of the Accounts Sheet. 8. For safety sake be sure to leave the worksheet in protected mode, the appropriate entry fields have been unprotected. AND, continue to save your files with the password for extra safety. 9. You may print this information, by clicking the button at the top left of this form. 10. Save each months account information under a different name, such as Jan-99, Feb-99, Mar-99., since you will use this file as your master file.