·         The Illinois Annual Treasurer’s Report is a report that each treasurer needs to file with the clerk. 

·         The clerk then publishes the report in a newspaper that is published within the municipality.

·         This needs to be done within six (6) months from the end of the fiscal year.

·         There is no required format for the report.  It is left to the discretion of each municipality.  However, it needs to have certain information

What information is needed on the report?

• A listing of all revenues received by source and amount.
• A summary statement of operations for all funds.
• A listing of all individuals and vendors receiving more than $2,500 by name and amount.
• A listing of compensation paid to all elected officials and employees in one the following categories:
• Under $25,000.00
• $25,000.00 to $49,999,99
• $50,000.00 to 74,999.99
• $75,000 to $99,999.99
• $100,000 to $124,999.99
• $125,000 and over

How do we build this with miExcel?

 Listing of revenues received by source and amount

This is rather simple if the client’s chart of accounts is consistent throughout each fund.  We will use the “=getaccountbalancemask()” function to retrieve this information. 

You can embed the fund number in a row (row 7) and the source in a column (column B) within your spreadsheet and then use the formula pictured above.  Note: Make sure you put a negative sign at the beginning of the formula or you will end up with negative numbers.  Also, cell L1 contains the fiscal year end in a cell off to the side.

Once complete, you should hide row 7 and column B.

Summary statement of operations for all funds

This section should summarize the financial position of each fund.  This can be summarized in one of two ways.  One, showing beginning cash, receipts, disbursement, and ending cash for each fund.  Two, showing beginning fund balance, revenues, expenses, and ending fund balance for each fund.  We would prefer that the client use option two as there are easy functions we can use to extract this information into a spreadsheet.  Namely using the “=gettotalfundequity()”, “=gettotalfundreveneues()”, and “=gettotalfundexpenditures()”. 

I generally put this on the sheet right below the list of revenues received by source. 

Once complete, you should hide column B.

You can embed the fund number once again in a column as we did above in column B. 

To get the beginning fund balance, use “=gettotalfundequity()”.  Note: Make sure you put a negative sign at the beginning of the formala or you will end up with negative numbers.     

Again, cell L1 contains the fiscal year off to the side.

 To get revenues, use “=gettotalfundrevenues()”.   Note: Make sure you put a negative sign at the beginning of the formala or you will end up with negative numbers.

To get expenses, use “=gettotalfundexpenditures()”.

To finish, use a simple formula for the ending balance.

Listing of all individuals and vendors paid over $2,500

This will automatically generate when we create the report.  See next section

This will automatically generate when we create the report.  See next section.

How do you generate the report?

1. Make sure you have hidden all columns and rows in your spreadsheet.
2. Highlight the Revenues by Source and the Summary of Operations sections in your spreadsheet.  This is why it is nice to have it on the same sheet one right above the other.  
   
3. Click on miExcel | Reports | Illinois MTR.  
4. In the settings, verify the range of cells highlighted matches under the Financial Range section.  If not, click Get.                               
   
5. Specify the Threshold for Vendor Payments.  This should be 2500 as it currently stands.   
   
6. Under Employee Payments, specify the groupings.  You can modify the header in this section.  Also, you don’t need to use groupings.  You can list what each employee made to the penny by unchecking Use Groupings.  This is not recommended. 
   
7. You can output this report in a Table or in Delimited format.  The Table format will take up more room.  As a result, the Delimited format is more desirable for printing in the paper to save on cost.  If you choose Delimited, you can specify the delimiter character.   
   
8. Enter the Start Date and End Date for the report.  This should coincide with the fiscal you are reporting on.                                               
   
9. Finally, you can report vendor payments and employee compensation on the cash basis or accrual.  To report on the cash basis (recommended), check the Cash Basis? check box.
   
10. Click Generate.  The report will appear in Word.

Example

Some cleanup may be necessary to eliminate unnecessary space.  Below is an example:

DISBURSEMENTS (GREATER THAN $2,500.00) SUMMARY

ACE LUMBER & HARDWARE, INC  7,649.82; ACME EQUIPMENT RENTAL  3,425.18; ANYCITY AUTO  84,595.69; ANYCITY SERVICE CONTRACTORS  92,091.44; ANYCITY TIRE & LUBE  2,552.47; ASHCROFT & COMPANY, LLC  8,420.00; AT&T  3,035.02; BRYCE JENSEN, ATTORNEY  5,120.50; CHEVRON USA INC.  5,539.26; GLOBAL ENGINEERING CO  179,311.48; INTERSTATE ROCK PRODUCTS  81,834.50; IRS  22,756.62; MORTGAGE COMPANY, THE  4,145.88; PLUMBERS SUPPLY CO INC  3,621.20; PUBLIC SAFETY SUPPLY CO  14,511.55; SCHOLZEN PRODUCTS CO INC  36,356.59; SHAFFER ELECTRIC  24,132.28; SOFTWARE INC  2,601.00; STATE BANK CARD  66,104.82; STATE POWER COMPANY  12,121.73; STATE RETIREMENT SYSTEMS  11,325.90; UNITED CREDIT UNION  3,003.95; WASTE MANAGEMENT INC  59,367.13

TOTAL DISBURSEMENTS (GREATER THAN $2,500.00) - $733,624.01

COMPENSATION SUMMARY

Under $25,000.00: BLACK, LANE; BROWN, SHAWN A; CARTER, DARRIN; CURTIS, AMBER; DICKENS, AMY; ELLISON, RAY; GARDNER, EDWARD D; GILSON, MARVIN; GORDON, NATALIE C; GRANGER, BENJAMIN R; HARRISON, JASON; HOBSON, MAURICE; JOHNSON, KELLY H; MCNEIL, ANNIE; MORRIS, SHARON; PRICE, GORDON; RAYBURN, SUSIE; SPENCER, ANNETTE; WATSON, LAMONT; WILSON, SAMUEL

$25,000.00 to $49,999.99: AUSTIN, GEORGE; JOHNSON, DOUGLAS; ROBERTS, DALE B; ROCKFORD, JULIE M; SLADE, RANDY L; SMITH, JACOB S