Need a spreadsheet for finances

[FleurDeLis]

Well, I'm our new troop treasurer, against my better judgement. The outgoing treasurer was an accountant, but a bit negligent tbh. I am not going to use the mess of QuickBooks that was left for me, I am planning on simplifying and hopefully using Excel or Google Sheets for all of the finance tracking - both to keep things more transparent (something that's been missing) and to make it easier for someone to take it on when I am finished.

We do have ISAs, but that's easy enough to track on Sheets. What I'm looking for is a spreadsheet that can track the big things - checking account, events, all of the outgoing and incoming money. 

Scouting boards have lots of mentions of "we use a spreadsheet"...any chance anyone is willing to share their template with me? I don't want to take too much time re-inventing the wheel.

I haven't yet met with our committee, and I plan on asking them, but I'd also love to know what your committee's treasurer provides to you at meetings, and in what kind of format. 

(Also, I am trying not to be frustrated with BSA or my local council, but I honestly can't believe that there's no standard software or widely-accepted processes for handling money within troops. Literally every Scouter I've talked to says their troop tracks finances differently. How is this not a class I can take at PTC or something?)

[Pselb]

Any time I have done books for an organization, I ask what they want and then develop an Excel document that easily gets to that.  I use Excel for everything I want to track.  All my grading (I'm a teacher) is done with Excel and any organization I worked for I've done the same thing.

Get a copy of the current report, create a tab of just that report.  Everything else done on other tabs totals into that singular report.  The Report Tab is what gets printed off for the meeting.  Anyone needs to see detail, that is a print out of the tab in question.

In my case each student's grades are accumulated on an individual tab and then referenced by the class roster tab and that tab is in the format the school wants and I just print it off at the end of the quarter and turn it in.  If a parent wants to see the progress of their child, I go to that child's tab and it's all there.  I print them out for Parent/Teacher Conferences. 

 

[ItsBrian]

https://www.scouting.org/programs/boy-scouts/program-planning-tools/

 

There are spreadsheets that can be edited for packs/troops for budgets. 

 

Hope it helps!

[ItsBrian]

I also found one that many seemed to like, you can probably easily change it to be used as a Troop spreadsheet.

https://www.dropbox.com/s/xauvw78j932bkax/!Finance_Spreadsheet 201x.xlsx?dl=0

[FleurDeLis]

17 hours ago, ItsBrian said:

https://www.scouting.org/programs/boy-scouts/program-planning-tools/

 

There are spreadsheets that can be edited for packs/troops for budgets. 

 

Hope it helps!

This document is what I was talking about when it comes to BSA not having a system in place. This is fine for a yearly budget - but there are no guidelines or established system for how to track each scout, how they've paid and for which event, what we are paying vendors, who needs to be reimbursed and for what, storing scans of receipts and emails, how to process donations, how to ask for money that hasn't been paid, depreciation and replacement of equipment ... I could go on, but you get the idea. I'm looking for a streamlined way to organize the nuts and bolts.

There's a lot of money flowing through our troop, and I'm surprised there's no standard app for making sure all troops are good stewards of their funds. 

 

[FleurDeLis]

17 hours ago, ItsBrian said:

I also found one that many seemed to like, you can probably easily change it to be used as a Troop spreadsheet.

https://www.dropbox.com/s/xauvw78j932bkax/!Finance_Spreadsheet 201x.xlsx?dl=0

I'm definitely going to build on some of these. Thanks for sharing!

[ItsBrian]

1 hour ago, FleurDeLis said:

I'm definitely going to build on some of these. Thanks for sharing!

I found it on the scoutbook forums, maybe you should look there for other advice!

[Pselb]

These spreadsheets seem to be an overwhelming, complicated, overkill. 

How much did we take in?  Total of fundraisers and other sources of revenue.

How much did we spend? Total of vendor expenses, reimbursements, and activity costs.

A detailed accounting of those issues should suffice.

Income Tab -

Left row title Fundraisers, columns of each project to enter in the amount

Left row title Other Income, columns of income sources

Total at the bottom: total income

Expense Tab-

 Left row activities, column of amounts such as travel expense, food expense, registrations, etc.

Total at the bottom: total expenses

Report Tab-

Beginning balance from previous report

Plus Income from Income Tab

Minus Expense from Expense Tab

Ending balance for the report

The detail collected from this system should be able to quite accurately be the foundation for the next budget.

 

 

 

[blw2]

https://www.troopwebhost.org/

I would not have done the treasurer job without it, period.

Even if the troop did not want to use it for the other tools, I would have insisted they keep it or find another treasurer.  

It's set up to log everything in for you.  Payments can be made through the site (seamlessly with paypal), it's a tool like quicken in a sense to balance your checkbooks and accounts.

It has lots of budgeting tools, but I recommend against a lot of that.  It's a layer of complexity that simply is not needed and will amount to busy work.  keep it simple.

It has great tools for sending out balance statements to scouts or parents, that sort of thing...tools for printing various reports for the monthly committee meeting, you can drill in to see reports from events (camping trips, fundraisers, etc.... to see the money...

parents and scouts can log in to see their individual situation

and I liked that it gives complete transparency.  Any committee member can be given permissions to either view or to edit the money stuff, so they can log in any time they want to see what I'm doing.  An online spreadsheet (such as google sheets) would do this too, but it more complicated and apt to 

 

and then the other things....troop emails, newsletters, calendar, etc....

we found the newsletter doesn't get read for the most part, folks don't really log in to sign up for events, stuff like that..... and personally I think a lot about it looks a bit dated and maybe "clunky", but it was still a good thing.

The guy that operates it was a troop treasurer and he has it set up well I think.

We used it with "scout accounts"...the idea is that every troop member has an account.  They put their own money in, and can choose to maintain a positive balance that can be used to pay things like camping charges or for dues...  It's their money sitting in the troop's bank account.  the site just breaks it out really nicely to show what their balance is.  It wasn't scout accounts in the sense that fundraising money from the troop goes to the scout personally like payment for doing the work.  thats' a whole sticky wicket that a lot of troops used to do, probably many still do, but should not be doing.  So if a scout owed money for an upcoming camping trip, I would charge their account.  It would show as a negative balance if they didn't have enough in to cover it.  worked pretty well overall.....

[KelseyS]

@ItsBrian, any chance you might have that spreadsheet still and be able and willing to share it?

Edited February 28 by KelseyS