Create a Cash Bill Format in Excel for Nonprofit Organizations

Cash bill format in excel for Nonprofit

hey this is david mccree cpa the non-profit tax guy should you use excel or another electronic worksheet to summarize your financial transactions for your nonprofit so in order to prepare a form 990 ez or form 990 you have to have a method to summarize your financial transactions into proper income and expense categories an electronic worksheet like excel or google sheets is one method that a small organization may choose to use worksheets have advantages and limitations the advantages are that they're inexpensive and sometimes even free and they're easy to use with very little training the primary disadvantage is that they are two-dimensional so if you want to summarize information based on criteria other than what's in the column headings here it can become awkward very quickly we need to somewhat summarize our transactions into a form that will make data entry onto the form 990ez very simple sometimes a worksheet is great for doing that and sometimes it isn't so let's let's see why so now we're going to look at the expense section of form 990 ez lines 10 through 15 have predefined expense categories the irs wants you to use these categories are what we call natural classifications in other words it's natural to label an expense based on its purpose compensation printing rent grants paid out and so on now you're probably aware that non-profits are often judged based on how much of their money they spend on programs rather than on overhead and fundraising while there's no legal requirement that a specific amount be spent on programs spending too much on overhead or on fundraising can raise questions in some cases for that reason it's helpful for a non-profit organization to not only be able to group its transactions into natural classifications but also into functional classifications so this means you need to have some method for grouping transactions based on whether they're spent on programs on overhead or on fundraising overhead is a common term for what's formerly known as general and administrative expenses or on the form 990 it's referred to as management and general expenses on the 990ez it's not necessary to separately show expenses in the general and administrative category or in the fundraising category they're all lumped together on on the page one of the 990 ez on lines 10 through 16. however for a 501c3 public charity it is necessary to be able to to determine and present the total amount of money spent on program expenses you can see here on page 2 part 3 of the 990 ez and the section called statement of program service accomplishments it asks for the total expenses that correspond to each program listed over here in this column here is where you list a description of each program the top three programs and the amount of expenses spent on each one so in contrast the full form 990 requires more detail so let's have a look at the form 990. now this is called the statement of functional expenses part 9 on this on this year's form and you can see they develop an entire they devote an entire page to expenses now not only are the expenses presented ing to their natural classification which you can see here in the rows they're also shown divided into functional classifications or the columns this makes it easy to see for instance how much of each type of expense was spent on programs how much on management in general and how much was spent on fundraising now let's look at a basic transaction summary in excel each row is a separate transaction generally organized by the date on which it occurred each column allows us to show the amount of transaction in its natural classification we have a section for income and we have a section for expenses by labeling each of these columns in a way that roughly corresponds to the lines on the form 990 ez by totaling the columns we're able to transfer the totals directly to the proper lines on form 990ez unfortunately it's not quite so easy to answer a question like how much money was spent on program services or how much money was spent on overhead management in general since we're preparing the form 990 ez we really only need to determine how much was spent on programs and for the simplest organizations this is not difficult now consider this transaction summary for a hypothetical little league baseball organization they have one program a baseball team after we total the columns to enter the income and expenses on page 1 of the 990ez it's easy to just eyeball the worksheet to determine which expenses were for programs and which expenses were for management in general or fundraising simply by adding up the program expense totals we can come up with a program expense total for page 2 on the statement of program service accomplishments it's easy so for instance if we look at the expense columns here advertising that's probably a program service expense printing may be it could be programs printed for the game or it could be management in general could be you printed out some financial statements and had them reproduced it could be for fundraising maybe you printed fundraising letters coaching that would be program expense field rental program expense field maintenance that would be program expense professionals fees no that's probably going to be general and management in general insurance that's probably management in general uniforms and gear t that's probably going to be that's definitely going to be program service t-shirt purchases and concession supplies those are not going to be either those will will have their own lines on for sales of inventory on the page one of the 990 ez now here's where the complication comes in what if the organization had two teams a recreational team and a competitive team to track income and expenses for each team you might need twice as many columns this would be way too complicated and it would result in many data entry errors if the organization decides not to track expenses separately for each team then a worksheet would be just fine otherwise you know a more robust bookkeeping solution is probably going to be needed in another video i'll show you how to push the limits of a worksheet like this to get the information you need even if you have multiple programs it's not a perfect solution but it works well if you just have a few hundred transactions during the whole year the takeaway here is that a worksheet is fine for summarizing transactions if you only have a few hundred maybe up to 500 or so transactions each year and if you only have one program or several very closely related programs that can be grouped into one program for purposes of the 990ez if you have more than 500 transactions or so each year or if your organization has two or more separate programs you really need to consider a more feature-rich bookkeeping solution now consider this i'm going to show you a statement of activities or also in the form of statement of functional expenses let's say you have this organization that helps homeless people one program you conduct is a soup kitchen it receives donated food and purchases some food and it rents part of a building to serve food to the homeless another program the organization conducts is a shelter the organization rents a building which it has equipped with beds and showers to help get the homeless off the streets at night these two programs are sufficiently different that at least from a management perspective tracking each program's income and expenses separately is highly desirable for the 990 ez it would be best practice to show the program expenses separately as well this is where a bookkeeping solution like quicken or quickbooks is probably needed when you have more than one program your bookkeeping solution needs to be able not only to produce summary reports that show expenses based on natural classifications it also needs to be able to produce a summary based on functional classifications like what you're seeing on the screen so this type of report you're looking at is called a statement of activities in an in non-profit accounting that's what it's called that's the same as a profit and loss report used by for-profit organizations the statement of activities corresponds to the statement of functional expenses as is required on the full form 990. now notice the natural classifications on the left here and the functional classifications across the top in columns to do this properly you need a feature called class tracking when you record each transaction instead of just assigning it to a natural category and being done with it natural category being like printing or rent expense you also assign it to a class such as in this case soup kitchen or shelter or the annual gala or a special event like silent auction or management general or fundraising and this will allow you to produce the reports that you'll need to properly prepare the form the full form 990. it's also very helpful in preparing the 990ez if you have more than one program now in the realm of affordable bookkeeping solutions quicken home and business and quickbooks online plus are the only two that i'm familiar with that offer class tracking quicken of course is the least expensive by far how do you know whether to use quicken or quickbooks my opinion is that if cash is your only asset and you have no liabilities like credit cards or loans to track then quicken is just fine it's an electronic check register and it allows class tracking which is very unusual okay to find that in a small accounting package like this tremendous benefit to quicken having class tracking now if the organization has a credit card balances or if it owns a building or equipment it should really should consider quickbooks online plus that is right here this version quickbooks online plus why this version well that's because the version that has class tracking the two uh earlier versions here essentials and simple start they do not have class tracking unlike many accountants i am a big fan of using worksheets for small simple organizations keep it cheap keep it simple once you screw up quickbooks or quicken you have a real mess on your hands do everything you can to avoid that now there is a trick to using quicken to produce the reports that you need just jumping into quicken and and having added entering transactions or downloading them as a prescription for disaster and i will cover that little trick in another video but it's very important to get the reports you need you have to set it up right okay at this point you should have an idea about whether excel is for you or not if it is and you'd like to know more about how to build a worksheet in excel to record your transactions and you'd like to do it with the least amount of hair pulling and frustration i have a course for you and that will teach you how to do just that and if you will go to uh nonprofit taxhelp.podia.com that's where my course lives and this is the page you will arrive at you you'll find all of my educational instructional materials here if you just scroll down here to bookkeeping with excel that's my basic bookkeeping with excel for non-profits level one and you can view the contents of the course here by clicking on view course i have it very reasonably priced i start out with showing you how to set up a basic excel transaction summary worksheet i show you how to set the whole thing up and you don't have to use excel right you can use google sheets you can use zoho office you can use openoffice libreoffice whatever worksheet you want whatever the mac has you can use as well i show you how to use freeze panes to make data entry much easier sometimes these worksheets can get really large and they can be difficult to navigate and so i show you a really simple trick for navigation then i show you how to create an income and expense statement from your excel worksheet so once you've finished entering all your transactions you've got a bunch of columns with data in them and totals you have to turn that into a profit and loss statement or income and expense statement of activities whatever you want to call it so you can transfer the amounts the the numbers to the tax return okay so i show you how to do that it requires only some very simple formulas okay anybody can do it it's not a complicated thing then i have a discussion about should you use debits and credits and i show you um how to use debits and credits in your worksheet and also show you how to do it without knowing anything about debits and credits okay so you don't have to know double entry accounting to to do this then really importantly i show you how to handle multiple bank accounts credit cards and paypal okay if you just got one bank account and that's it it's pretty easy but knowing how to organize things when you have two or three bank accounts you've got a couple credit cards you get paypal you've got stripe you've got square how do you do that right so many people they they end up with different tabs with different worksheets and it just creates a horrible nightmare i show you how to do it on one sheet keep everything organized and together it makes it really simple i talk about accounting for a change bank or a change fund you know an organization that has an event where it accepts cash it needs to make change and so it needs to go to the bank and take out a bunch of a bunch of small bills and coins i i talk about how to handle that and and get it back into the bank without double counting your revenue all right then accounting for reimbursements um it's very common for volunteers officers directors employees to purchase things for the non-profit using their own funds and then you know maybe they need to get reimbursed so when that's the case i show you how to record that properly okay this is these are the basics this is what you need to know to get to get started with your own spreadsheet so i hope you'll check it out i know you'll find value in it it's very reasonably priced and i think it's it'll be just what you need all right if you've gotten this far in this video thank you very much and have a great day