How to Create a Treasurer's Report in Microsoft Excel
Spreadsheet software, like Microsoft Excel, is a great tool for creating financial reports. This short tutorial will show you how to create a treasurer's report using a template that already has all the necessary formulas and sample categories. You can customize the template to fit your organization's personal needs.
Getting Started
Before you begin this tutorial you'll need to have your ledger handy and up-to-date. Also, you'll either need to create your own template or download the one that is being used in the tutorial.
The treasurer's report template can be downloaded here.
Directions
Here is an overview of the steps needed to complete the this tutorial. Follow the links to see each step in more detail.
| Step One: | Rename and save the template. |
| Step Two: | Enter the beginning and ending balance. |
| Step Three: | Enter budget data for each category. |
| Step Four: | Enter monthly income and expenses. |
| Step Five: | Enter year-to-date data for each category. |
| Step Six: | Save, print, & sign completed report. |
| Evaluation: | Calculate account balances, compare data to ledger, interpret report. |
Step One:
Open the treasurer's report template and rename the file for the current year and month. Save in a convenient location where you will easily be able to find the file.
Step Two:
Enter the current month and year and beginning balance at the top of the page in the designated cells. Enter the current month and year and the ending balance at the bottom of the page in the designated cells.
Step Three:
Enter the budget for each category in the corresponding cell in the Budget column.
Step Four:
Enter the current monthly income and expense totals for each category in the corresponding cell in the Monthly column.
Step Five:
Enter the year-to-date (YTD) totals for each category in the corresponding cell in the YTD column. This total is the sum of the previous months YTD amount and the current month's amount.
Step Six:
Save and print the completed treasurer's report. The treasurer must sign report. Treasurer's report is ready for evaluation.
Evaluation:
Calculate account balances by subtracting YTD expenses from YTD income for each category. Compare these balances with the budget, ledger, and executive board member meeting minutes. Treasurer's report balances and ledger balances should be the same. The variance column will show any remaining funds that have been budgeted.
Links:
Here are some additional resources that may be useful:
This is a snippet from John Paul Dalsimer's book, A Guide for the Volunteer Treasurer, that gives information specific to a treasurer's report:
Here are some instructions from Microsoft on how to do some basic tasks in Excel 2010: