Collaborate on Billing Excel Sheet for Nonprofit with Ease Using airSlate SignNow

Billing excel sheet for Nonprofit

hi this is John summers at FMA and the uh purpose of this video is to walk you through the cash flow projection template that is available for download on this website um good cash management of course is essential for a nonprofit organization just as it's essential for any business um so this tool is intended to help nonprofit leaders uh Financial Executives and CEOs to uh project and monitor their cash needs over the course of the year so that you can plan in advance for any issues that might need to be managed um so let's start with a general overview of this tool um so what it will do is basically provide a snapshot of your cash receipts for the year um that's in the first section then the next section here is for cash dispersements um and then there's a third section that deals with cash flows from capital and financing so there'll be some receipts and some dispersements there and what you will get uh from this on a monthly basis is that you will be able to see your net cash excess or shortfall for that particular month and then at the bottom you will see your uh cash balance for that month and the goal of course is keeping that Rolling Cash balance at a comfortable uh level throughout the course of the year so now let's talk about how to actually use this tool for um projecting and monitoring your cash needs and your organization for the year um so first of all you'll see the key here in the upper right uh corner of the spreadsheet that shows us that the yellow cells are for input for the data entry and the gray is for output output uh cells so that's the first important piece of information is only uh only use the yellow cells for input uh and the first yellow cell is the uh uh the name of the organization so you'll put your organization's name there for a demonstration just make one up so we'll say the ABC organization um and then the next uh input is this uh fiscal year begins so you uh it's set by default to January but you can actually by clicking on the dropdown you can select any month of the year so let's say for this again you want to you want to pick the uh month in which your fiscal year begins um a lot of nonprofits have fiscal years that begin on uh July the 1st so for this demonstration let's just put July 1 and what you will see is hap has happened is that that will um automatically create your flow uh uh create the the months across the top of the projection spreadsheet based on uh the first month of your fiscal year um and then the third piece of kind of background information that you will input into this spreadsheet is your beginning cash balance so in other words the um the the closing cash balance as of the end of your prior fiscal year um and for a lot of people you might be working on this before your previous fiscal year has ended that's probably a good idea to to plan ahead like that so you want to put in you know a projection of what you think your closing balance will be and then when the year is actually closed you can go back and um update that based on the actual so again here let's put in uh just for demonstration purposes we'll put in 15,000 and then if we scroll to the bottom you'll see that that has automatically created um a rolling cash balance for us across the year of 15,000 obviously um that's just based on your beginning balance because we haven't entered any input any uh uh receipts or dispersements yet so that's the next uh the next thing that we will do and one thing that you will want to have with you when you do that uh when you begin to put information into this spreadsheet is your organization's budget for the fiscal year because the information about all of these receipts and dispersements is ultimately going to be based on your organization's budget um so let's come back up to the cash receipt section here and this is where we will begin to enter data that will show us the cash position for each month of the fiscal year and you'll see there are several categories here for um typical um line items in in a budget so you'll put in your budget for the relevant line item so over in column e for the current year budgeted so for foundations for example you would enter your foundation grants into um into this line and if you have more than one Foundation Grant you'll notice these um plus box buttons over here on the left so you can click on that and you can see you can have some individual um line items um once you click that to expand that category and if you have more than one um Foundation Grand it's a good idea to enter those separately so you can sort of keep it straight when you're looking back in monitoring this um so let's say and you can just over type directly into these cells um so let's say we get a grant from The Wallace foundation so you can type Wallace Foundation into that cell um and let's say that Grant is budgeted at 100,000 and then you can enter 100,000 there and then let's say we have a grant from a local foundation and that's budgeted at $25,000 um and so you do that for each of your categories of um receipts so corporations for example uh here again you can look at the um plus button below that line if you want to expand that out any further um individual contributions again there's the option um if you want to add additional line items that is not accounted for in these categories you can click that to add some uh additional line items there um similarly government contracts um federal state and local those are all expandable and then we have some other Revenue categories and again if you just need to expand out to add a few additional uh line items or categories you can click to expand uh expand there so again for a demonstration here let's say that we get um 15,000 in in individual contributions um let's say we have one Federal contract for 50,000 and let's say we have a special event that brings in 10,000 so we enter each of those um it items into the budget and then for these uh categories lines 55 and 56 if you happen to have any receivables that you're carrying forward into the current fiscal year um you would enter those items here and again now this would be items that are not technically in your budget because we're budgeting on an acral basis and so this would be for a service that you performed and so realized the revenue for in the prior year but the cash itself hasn't come in yet so let's say that the organization did some Service uh in May of last year and build for it in June but the cash itself hasn't actually come in yet so um let's say we've got something like that we had a a contract to do something for $5,000 we build for that but the cash has not come in yet um so it's not part of your current budget and you'll see it doesn't total into your current your budget for this year um but it will be part of your cash flow for this year so we want to account for it on the spread sheet so the next thing we will want to do once we have all of our receipts budgeted for the year is to go back into these monthly columns and um spread the cash receipts across the year based on the month that we expect the cash to actually come in um so let's go back up to the Wallace Foundation Grant uh and let's say that that Grant comes in October of every year so we'll put the $100,000 in October um and for the let's say the local Foundation gives us two installments of $12,500 we get one of those in September and one of those in March and so you'll see these totaling over here uh in the total column on the right as you add them into uh each month and one check um if if you look in the variance column while you're filling out the spreadsheet um you'll notice if there is no variance so in other words if you spread the entire budgeted amount across the um across the months then this variance column will show at zero that's kind of a way to check that you have in fact uh allocated all of your costs all of your uh revenues into the appropriate month um so one other uh you also have a thereafter column here here so if you happen to have a again budgeting on the acral basis if you happen to have a budgeted item that you don't expect to collect in the current year so for example uh a service maybe that you'll provide uh late in the year but won't actually realize the cash won't actually um receive the cash until the next uh fiscal year then you could put uh any amounts there into the their after column so then we will spread the rest of our uh revenues out in the same way so we've got individual contributions here um most of our individual contributions will probably come around the end of the calendar year as people start to um get in the holiday spirit or start thinking about their tax deductions um so let's say we've get we get $5,000 in uh November and December and then let's say we have a um annual appeals letter that goes out and that goes out and we start to collect some cash from that in May um and let's say we have our contract payment in October and then special events we'll have a golf tournament so we'll do that in August and then this um receivable we're going to collect let's say in July and then we can scan over into our variance column again and it looks like we have allocated all of our um receipts into appropriate months so that should finish us with the cash receipt section so then we'll come down and do something very similar in the cash dispersements so we will put our budgeted amounts uh here in column e again we've got typical line items for personnel for otps lines again if you want to expand this out to have some more options uh you click the plus click the plus and otps um so salaries and wages payroll taxes rent utilities again similar to the prior year receivables above we have a line for prior year acral so if there were items budgeted uh expense items budgeted last year that you didn't actually send the cash in until this year you would put those items on this line and again additional lines there if necessary um so let's go ahead and fill in some uh budget numbers here salaries let's say we have 120,000 in total salaries um that'll give us about 8,000 in payroll taxes um let's say 15,000 for medical we'll do a 5,000 pension contribution um again you can add additional categories if you want come down to rent let's do 24,000 for rent let's do 2500 for utilities and so on um until youve basically accounted for all of your planned dispersements for the year we have that golf tournament so let's put uh 2500 as a cost for that for the special event and so again on the dispersement side you'll want to spread all of those uh budgeted dispersements into the appropriate months and so one thing I want to bring your attention to is this column column F spread evenly so what this does uh and you'll see the instruction there when you click on this column uh this is in case you have a uh have a dispersement that's basically a recurring item over each month of the year so rent is typically a good example uh in many cases salary is basically a recurring item that doesn't really change that much change that much from month to month so in those cases you would just select yes from the drop-down menu here and then you'll see that it it will spread that $120,000 equally over the 12 months of the fiscal year so in this case $10,000 uh per month um so let's say salary payroll taxes and medical benefits we're going to do that um we'll have our pension contribution be a one-time thing in December um rent again will probably spread evenly so we can do that utilities also we can say spread evenly um spread evenly is also an option uh up here in your receipts categories it's just not quite as common to have sort of a regular payment uh coming in every month but if you do uh then that option is available here as well um and let's see the special event is our golf tournament so we'll put that in August uh one important thing to note is that we don't have non-cash budget items or other items that don't affect cash flow as part of this template so depreciation for example um depreciation should be an expense item in your budget but it doesn't directly affect cash so we're not building it into this cash projections model um so for those sorts of reasons this cash projections template and the budg buget um the budget totals that you see in column e um might not reconcile exactly to your board approved budget uh but the budget should still be the framework for projecting out these receipts and dispersements but you might just notice some small differences on things like uh depreciation expenses or um transfers from uh temporarily restricted assets or that sort of thing that won't directly affect uh affect your cash flow um so these so that gives us our operational receipts and dispersements for the year um you'll see at the bottom there's also um um a section for Capital and finance financing related cash flows so you can add in any receipts from loans or credit lines uh or transfers into your cash account from your savings investment accounts or also dispersements in the form of capital purchases or repayments or transfers out to your SA saing and Investments I'm not going to enter anything here for our demonstration but again it works the same way as the items above you'll enter a budgeted amount and then you'll spread the cash flows into the appropriate months um so once you get all of your receipts and your expenses plugged in for the year you'll see line uh you know your two output lines here at the bottom line 112 is going to show your net cash excess or shortfall for the individual month uh and then the final line line4 here on the bottom will show you your Rolling Cash balance for each month and so you'll see in the example that we've put together here that although we're projecting a cash Surplus for the year as a whole that's here in um in this cell the total we see $28,000 Surplus uh and we've got a large cash influx here in October that looks like enough to carry us through the rest of the um the rest of the fiscal year um we have August and September these two months are showing um are showing that we're not going to have enough cash to cover our dispersements in those months so this is obviously a red flag that we'll need to address before we get to that point so maybe we'll take a draw on a line of credit in July and again that's something that you would also want to enter into your um into your template so maybe we take $5,000 in July um and then pay that back in October I'm sorry um and then you know note of course that that will probably give you an interest expense as well that you would want to account for um or maybe you know we have other options maybe one of your board members is uh a member of the golf club that we're having our event at and they he can sort of help us push that expense back by a couple months so if we change that into um say October then we're at least in the black this $125 in September is probably a little bit too close for comfort but uh again the point being that this spreadsheet gives us you know is going to give us a little bit of an early warning if we need to address any cash issues so we aren't faced with that crisis to manage at the last minute so that basically completes your uh initial buildout of your cash projection spreadsheet for the year um but in order for this to be uh a really useful tool for ongoing monitoring of your of your cash position you actually also need to keep it updated uh over the course of the of the fiscal year uh and you do that by inputting the actual results on a monthly basis uh into this same spreadsheet so once you finish a month what you should do is come back to this tool and make any adjustments to the monthly cash spreads based on what actually happened in that month and if something changes then at that point you need to make the decision as to whether it's simply the timing has changed or the fact of that receipt or dispersement itself has changed so let's take uh let's take one example let's say that uh that prior year receivable that we had budgeted here in line 56 um we expected it we had expected originally to elected in July um July happens it turns out that it didn't actually come through in July but we got a call from the group that we provide services to telling us promising it will come in August so at the end of July we will uh remove it from that column and we will move it over into the August column and you'll see if you go down to the bottom that makes our cash position for July a little bit tighter um but you know again we we keep it in the budget we just simply shift it Forward by a month um let's take uh let's say that something else happened in July at the same time and we get a contribution um let's say an individual contribution that we hadn't been expecting um say somebody learned about the organization in the newspaper and thought we were doing great work and send us a check for $1,000 it's always nice um so we put ,000 into the column into the July column for individual contributions um but since we're still expecting the yearend and the year end uh contributions to come in as they usually do and the appeals letter to go out in May and it'll get its contributions um we don't we're not going to change those so essentially what that's going to do is show a positive variance over here for the year of $1,000 so we had only expected 15,000 when we did our original budget but we got this extra ,000 we put it in July and it shows up as a positive variance over here at the uh uh at the column on the right um so now let's take one other a different type of example let's say our local Foundation that we were expecting to get $25,000 sends us out check in September as we expected uh but that due to some sort of circumstances they're not going to be able to send us the other half of the grant that we had expected for March um so when we get this news you know again as we're updating this uh spreadsheet on an ongoing basis when we get this news um we will go ahead and delete this expected receipt from the local foundation in March um so that again changes our overall projection and gives us a variance of in this case a negative variance of 12,500 on that item um and this also starts to give us you know a sense of where are overall so with just those changes that we talked about that will create an overall negative variance of $1,500 um and again you'll want to do the same thing every month on the dispersement side um and those are particularly important as that can vary pretty widely based on timing factors uh so again you want to make sure that after the close of each month you go back into this spreadsheet to update actuals uh each month for receipts dispersements Al the capital and financing section uh and once you make those updates based on the actual results the bottom line will update automatically and show you what your overall cash position uh for each month looks like and that again is how you can get a sense of any upcoming uh challenges or issues that you might need to address before they become a a cash management crisis um so again that's how that's uh in general how you can use this tool for planning and and monitoring your organization's cash position over the course of the year um I hope this is helpful to you in in running your organization and remember that you can find many other useful tools for strengthening your organization's financial management on the resources for financial management section of the wallet Foundation website uh this is John summers at FMA and thanks for your time