Club & Small Charity Treasurer: Dues, Cash Book, Bank Reconciliation & Annual Accounts

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Look up each member's annual fee from the fee table, pro-rate it for members who joined during the year, total the arrears and count the members the treasurer has to remind.
• Keep the club's cash book: a running balance after every line, receipts and payments by category with SUMIFS, the net result of the events and the largest payment of the period.
• Close a month of petty cash with the imprest system: total the vouchers, work out what should be in the tin, find the cash difference and calculate the top-up that restores the float.
• Reconcile the cash book with the bank statement: adjust the bank side for uncleared items, the cash book side for charges, interest and a direct debit, prove the difference is zero and mark what still has to be entered.
• Compare four fundraising events: total income, net result after direct costs, the net result per volunteer hour for each event and overall, and the event that uses volunteers' time best.
• Compare eight budget lines with the actuals after eight months: pro-rata budget to date, favourable and adverse variances for income and expenses, a run-rate forecast and a red flag on every adverse line.
• Separate free reserves from restricted and designated money, express them as months of spending, build a month-by-month cash forecast and find its lowest point.
• Finish the receipts and payments account: this year's surplus, the funds carried forward, the change against last year and a chart of where the money came from.