One of my goals last year was to hone my Excel skills. I had used Excel for project management and task tracking, but wanted to get more comfortable with the formulas, functions, and advanced settings within the application.
I worked through Maven Analytics’ Excel Specialist Path, beginning with Charts and Graphs course and finishing with Forms and Functions.
As an application of my learning, I developed an Excel Dashboard to track the middle school PTO finances as part of my role as Treasurer. I created a generic version for others to modify and utilize for their own parent teacher organizations.
Disclaimer: I am not an accountant and using this workbook should not be considered legal or financial advice. Work with your organization’s CPA to make informed financial and tax decisions.
Parent Teacher Organization Accounting in Excel
The Newton parent teacher organization had been paying $350 per year for QuickBooks, but was using Excel workbooks to finalize taxes with the CPA. After conversations with our accountant, I created a robust workbook that included reconciliation and automation of completing a 990 form (required to maintain our non-profit status).
The workbook utilizes an X-Lookup to fill in the Accounting Workbook. The totals are aggregated automatically from the accompanying ledger.
In the past, completing the 990 form would take a minimum of ten hours to complete. Automating the completion of this form made this volunteer position more sustainable for the future.
Automating the Treasurer Position
In addition to simplifying accounting, I created a Jotform to manage reimbursements. This automation allowed me to download records from a Google Sheets document and easily paste them into our ledger. The combined efforts of my automation improvements decreased the time needed to fulfill my treasurer duties by eighty percent.
Note: The ledger below utilizes fictitious numbers. The column labeled “uncleared” was used to document the check amounts before they cleared with the bank.
I included a visual within the dashboard to monitor income and expense categories throughout the year. Parents appreciated being able to visualize how the Newton PTO utilized donations throughout the year.
Not included in the workbook, I created an income trend visual that helped our PTO track our progress. We raised 25.8% more funds as a result of our PTO team’s efforts.