Form 28 Monthly Cash Report
|
Form 28 Monthly Cash Report |
|
|---|
Click on the link "Download Form 28 Excel File" and you will get a File Download dialog box that asks you "Do you want to open or save this file?" Select "Save" and save the file to a folder in documents named "Form 28" which you should create before you download the file.
When you first open the Microsoft Excel spread sheet you should see the portion of the spreadsheet that is printed out each month. This is displayed in Figure 1. You will be tempted to input data as you normally do on a form. However things must be done differently using this spreadsheet. The actual form is protected so that you are not allowed to enter data here.
Figure 1
Image of form 28 in the Spreadsheet.
Below this section of the spread sheet is the “INPUT SETUP DATA” shown in Figure 2. This is where you put in the data for the beginning of each year.
Figure 2
Spreadsheet view of where the Branch Information is Inputted.
Input the setup data above this line (Excel C62-C64, J62-J64). The data will transfer to the appropriate cells on the Report Form 28. Please note that the "…YTD Cash Balance (Excel cell J63) does not change from month to month.
2. For the January report only the worksheet formulas copy the "Previous Year's December Cash Balance:" (Excel cell J63) to "Beginning Cash Balance Columns (Line 1). Excel positions O5 and L5). Note that the "Beginning Cash Balance YTD Column (line 1) stays the same for all months because it is always the value of the previous December "Ending Cash Balance."
The portion of the Spreadsheet to the right of the Form 28 report area is where the Monthly Input Data is entered and is shown in Figure 3.
Figure 3
Excel Spreadsheet portion that shows where monthly data is inputted.
3. Using the input section (January uses column O and February uses column R and so on).
a) For each month, enter your ledger data by categories (101:103, 201:207, 301:308, 401:407. Cash Elements, Membership, and meeting data) in the appropriate Monthly column.
b) If the report area formulas have not been altered the YTD totals will all be computed in the report YTD column.
4. The program calculates and transfers all Current Month and YTD data to the Report area.
5. Use "Auto" or Change cell J61 to the reporting month. Please spell the month out fully.
a) If you have entered data in the columns to the right of the reporting month, AUTO moves to the last month that has entered data. You must manually enter the desired month in Cell J61 to force the report date to a specific Month. You can return cell J61 back to Auto for future reports.
b) If you feel you must change data in a prior month that was sent to the distribution list, Please list those changes in your email that you send to the distribution list. It is very difficult for us to find a change in prior months when you do not tell us. If you send this entire worksheet to the distribution list that is also fine, but still tell us of the changes. We need information like Month and Line item number.
6. Make sure the Line 8 Month and YTD are equal, if they are different a formula has probably been changed or needs repair. Make sure that Line 8 Ending Balance and Total of Cash Elements are equal. If they are different there is probably a Cash balance or pre-paid fund ( e.g. Postage) which needs to be shown as a receipt as well as an expense.
7. Cell D49 should match your checkbook balance. If they do not match, you need to be able to explain the differences to your Branch Executive Committee as they should match.
8. If you are mailing this report, make sure the print area is selected for the report area of Form 28 only. The creator of this worksheet has preset the print area selection to A1:L57 from the Print Setup menu.
9. Sign or initial the report and distribute.
10. Print copies per the distribution list on the bottom of the Form 28 or more copies of the form depending on your own branch distribution list. Emailing to the State members of the distribution list is very acceptable and desirable.
11. Emailing this entire report to the Area Governor and Assistant State Treasurer is also acceptable. You may also extract and email just the report information in PDF format to the mailing list.
12. It is recommended that you save the worksheet after each month to a unique name. This will give you a method of backup if worksheet or formulas are damaged. E.g. Form28-Br200-201002 for February.
Figure 4
Excel Spreadsheet showing the three parts of the Spreadsheet.
