https://youtu.be/F4FtVtXckPo?list=PL60SIT917rv6ERsGZxM9V_IZLVybpgfNU
This presentation and we’re going to start breaking out our expenses by nature. In other words of what the expenses are used for with the use of the tags, the categories being the education, the community service, the administrative and the fundraising for that 4020 2020. Being the allocation percentages, we will be using, get ready, because here we go with abalos. Here we are in our not for profit organization dashboard, we’re going to be heading on over to Excel to see what our objective will be.
00:31
We’re currently in the 10th tab. So tab number 10. What we want to do now is considered them up top at the top in row one, we have our information here broken out on the expense side of things is where we’re concentrating, and we have it broken out by their nature, in other words, what they would typically be broken out by, so that being rent salaries, and so on and so forth. When we do the reporting, we want to break them out both by nature and by function. And we would like to do it in such a way that we’re not overwhelming our reader. In other words, the statement of activities here, maybe we want one or the other, either by nature or by function, and then have another report that will expand on it. And this report, we have it down here you can see by by function, what they’re used for.
01:16
So in other words, we have the two programs, we have the management and the fundraising, that’s what the the expenses are going to be broken out what they’re used for. And then if we want to see the more detail of the nature, it’s going to be up top. Now the way we have it set up the way the system is going to work is it’s this will naturally be usually by nature, because that’s what how a database system, usually it’s going to be easier to to work, and then we’ll break out by what they’re used for by function in a separate schedule. But in any case, we want to present it not both things typically on the statement of activities or on the income statement or it is overwhelming.
01:53
So in other words, we’re going to need this report. And then another report typically that we’ll be breaking out the expenses, both by their nature. Now this is going to be their actual expense categories. And by their function, what they’re used for the two programs and the the management and in the fundraising. To do this, you’re going to need some kind of percentage breakout. So typically, the organization will come up with some percentage that will help them to determine how much of the expenses are being allocated to these categories. For the readers of the financial statements, this is really important, because when you’re looking at the expense line, you’re saying, Hey, this is the expenses, this is what they’re using.
02:31
We want to know how much of it is going to what the program is used for, which would be the programs versus the efficiency of the program to spend that money on what we want our money to go to, which means the admin kind of stuff, the management and the fundraising. So you’re oftentimes the reader of financial statements for not for profit organizations, is looking at the ratio of you know, expenses that are being going to the actual use the programs versus the the management and the fundraising. And that’s going to be a measure of the efficiency that your money is going towards what you actually want it to go to if you’re thinking about being a donor to the organization. All right, so how are we going to do this, we’re going to go back on over to our Excel file, it’s opened up our financial statements.
03:16
So I’m going to be concentrating in on the income statement, let’s go to the reports on the right hand side. And then within the reports, we want the income statement, we want the income statement by fund. So then I’m going to go up top, we’re going to say this is for the current years. So we’re going to say this is this year to date, we’re working in January, so as long as you’re including January, we should be okay, I like to include the total columns, I’m going to hit the break down here, the report layout and add the total column. And then I’m going to apply that change.
03:51
Alright, so there we have that now we’re concentrating here on the expenses, you’ll note that unrestricted has all the expenses, that’s typically going to be the case, because again, we’ll see it we’ll see later that when something’s going to be expensed, it’s triggering the release of a restriction will show the release of the restriction and that’ll happen up here, we’ll put it up as an income statement line, taking something out of what was previously restricted, and then moving it into unrestricted, then recording the expenses that are unrestricted now, because we will now be down below. So we’ll show how that’s gonna play out. But so in other words, we then need to break this out this unrestricted by its categories, which we’re going to use tags to do.
04:32
So this entire amount that’s here on the expense side of things. This 321 260 needs to be broken out between those four categories, the two programs admin, and the fundraising in another report. So what we’re going to do then is I’m going to right click on this tab up top, duplicate this tab again, and take a look at what that other report will be. We’ll go back to the tab to the left.
04:55
And now let’s open up the other report. So we’re going to go to the reports drop down And we want to open up the reports that’s going to support this unrestricted items. And that’s going to be our tagged reports. So we want an income statement with the tags now. So we’ll pick that one, we’re in the unrestricted tag reports income statement. So and then we’re going to select the drop down, and we want to list this year to date this year to date. So then if we scroll down, nope, nothing’s in it right. Now, of course, because we haven’t brought any tags into those items, we’re concentrating here on the expense items. So what I’m gonna do is I’m going to, so if we go back to our income statement, then what we’re going to do is we’re going to drill down on each of these expense items, and we’re going to add, we’re going to apply a tag to it.
05:41
Now it’s kind of a tedious process here. But we’re specialized in the duties, we didn’t do it as we entered it, you can do that. But it’s it’s less specialized work, or it takes less skill to enter them just kind of like normal into here, and then go back in and reallocate this information. So that’s what we’re going to do, we’re going to go back in here, and now do the specialized duty of reallocating. So before you do this, you probably want to print this report, make sure that you know what the totals are, so that if you mess something up, you can always go back in and just simply put the total back in, and then do go back into your reallocation process. Now, we’re also going to try to do this as efficiently as possible. So we’ll think about, okay, how can we do this, as quickly as possible make this a specialized thing and put this data input as fast as we can.
06:24
So the basic process will be this, I’m going to duplicate the tab each time. And then I’m going to drill down on this information. And then that in that way, I’m not going to delete this report. So I’m going to duplicate this report, then drill down on the tab, and then make the change. And then I’ll go back to this report duplicated again, after the next one, drill down on it, make the change, and so on and so forth. So I’m going to go back up top, we’re going to duplicate this right click and duplicate. Now, sometimes it takes a little bit of time for it to populate here as well. So to make it a little bit faster, while it’s doing that, while it’s populating, like you can pull this one to the right, because these two tabs now have the same report on it. And that way, you don’t have to wait for it to populate. And then you can go back up top, and I’m just going to drill down on the wages, I’m going to drill down on the wages. Now if there’s more than one transaction in here, then I might do the same process.
07:15
Again, I might duplicate this tab, and then drill and then drill down on one of these, change it and then do the other. So I’m gonna do the same thing, I’m gonna go back up top, right click, right click on the tab, duplicate the tab, pull the tab from the left to the right, because that one’s still thinking and I don’t want to wait for it to think. And then I’m going to drill down on the data here. to drill down on the data, you often have to go over here to the like contact rather than the debits and credits for some reason, or you can go into the amount works.
07:44
But some of you can’t do the whole line basically. So there we have it. If we if we drill down on the data, and scroll down, this is going to be our information that we use to populate this amount. Now basically what we want here, we can see the amount is the 209 460. What we want to do is break it out in accordance to our percentages. Now using our unrestricted categories. So we know there’s going to be four columns. So that’s what I’m going to do. First, I’m going to add a column and say, let’s add a column here. And I’m going to add two more columns. And then they’re all going to be going to the account. So I’m going to say the account is going to be five 5000. So I just put in the account number each time, I’m just kind of trying to specialize my task here to do one thing at a time 5000 5000 5000.
08:35
They’re all going to be in the unrestricted fund, then we just need to change the category. So the categories if we if we take a look at the drop down, we have then the categories of the education. So I’ll say the 40% education. And then we had 200, which was the community service, the 20% 300, which was the administrative and then the 400, which was the fundraising. So education, community service, admin fundraising. And notice we put the little percentages in that are make it nice and easy for us to do this to this breakout. And they have the percentages over here, which makes it really easy for us to do it. So I’m just going to say now I’m just going to change the percentages to match this item. So I’m just gonna say 40% here, and then 20% here 20%. Here, it might be easier to tab all the way through to get back to the 20 bin than using the mouse. And there we have it.
09:40
So now it should be broken out in the proper amounts and those amounts should then add up to this 209 460 If it doesn’t, they probably shouldn’t let us basically record it. So now we’ve got that breakout. So those percentages are great. We don’t need to pull the calculator out or anything. Just let’s just put those percentages in. That’s something you don’t typically have enough. Financial Accounting or not for profit accounting types of software? Let’s see what it’ll do. What’s it, what’s it going to do? It’s gonna, it’s not going to change the unrestricted, it has the same amount in the two column worksheet or report. But then it’s going to break it out to these tags categories for our second worksheet. So I’m going to say submit, let’s check it out. And see if we did anything wrong. No, no big red things here, your results are filtered.
10:27
So I think that’s okay. So I’m going to say, I’m going to close this tab. Now I’m going to close it, and that’ll bring us back to the prior tab, then I’m going to refresh this, this tab. Now, once you refresh it, you should have this this change here that we see on this one, right. And that’s going to be that this first one is now broken out between these categories, unrestricted, unrestricted, unrestricted, and so on, and so forth. And that and that it will also be reflected if we go to the first tab now, which is our income statement by with the tags, which is going to be supporting the tag information. And we update that report, update the report. Note, when we first generate the report, it’s filtering out one of the tabs. So you want to make sure that you go up top and select your filters up top, and we want to go to the report filters and look at those unrestricted, we’re in the unrestricted category.
11:21
And then I want to pick up this one, for some reason, by default was not checked when I first go in there. So they might just show three by default, just for the size of the report, probably. And so you want to pick the last one up, and then apply that. And there we have it. So now we’ve got our categories, I’m also going to change the date up top, which would be helpful this year to date. So we’ll pick up this year to date. And there we have it. So we’ve got the 83, the 4141 to 41 to 209 460. Now let’s do the other one. So I’m going to go back to the tab to the right. And after I’m done with these, all of these should be broken out with these four categories, because that’s, you know, that’s how it should be looking.
12:01
So anyone that’s not, then we’re going to break it out. So this one, I’m going to go back into the 16 eight, I’m going to do the same thing here. Now this is a different type of form. It’s a journal entry, which you might think it’s a little bit more confusing, because this whole screen is a little bit more intimidating looking. But really this debit amount, all we want to do is is record the the debits break them out into the four categories. So you what I think is easiest is just to leave that debit there for now and then break them out down here and then delete that first row. That’s what I was, will try to do so. So in the next row, I’m going to put 5000. And in the category, it’s going to be unrestricted, and then I’m just going to add another one I’m going to be putting down here, I’m going to put 5000. Again, I’m going to create four of them down below because I’m going to delete that first category. And then I’m going to go down here again and put we need a 5000. So that’s 1234 looks good, I’m going to hold down Ctrl and scroll down just a little bit, so it fits on a screen for us.
13:02
So there we have it, then we don’t have that nice little feature with a percentage breakout this time, because we’re using a journal entry. So let me pull up my giant calculator and make a little bit smaller, the calculator gets excited that when I pull it out, so has to take the whole screen up like that. And then we’re going to say that we not be the categories, then let’s pick the categories first. So then we’re going to say that this first one needs to go to the uncategorized tags, I’m going to say 100. Note, there’s 100 200 thing is really nice, because that’s easy to remember just 100 200 and then there’s a there’s the tag, and then this one’s going to be 300. And there’s the tag, didn’t pick up the tag 300. So there’s the tag there, and then 400 400 and there is the tag there. So now we have the tags in place, then we can go back up and allocate this out. So now I’m just going to go to the debit. And now I have my percentages. So I’m going to take that 16 eight times point 416 eight times 2.4. And I’m going to say this debit is going to be replaced with this 6720.
14:17
And then I’m going to say that same 16 eight times point two, if I could, I should probably do that in my head. But you know, I’ve been working on this computer there with Excel for quite some time. And so we’re going to say 33603360, we’re going to say 3360. And so there we have it. Now I’m going to delete this first one, see if I can remove this first column, I don’t even want this first column. So I’m going to delete that all together. And hopefully that won’t bother the system that there’s that blank line up there. And then we’ve got admin, adjusting entry all the way down. So now we should have the same amount debits of the the 16, eight and the 16, eight. But the restricted and unrestricted is off. So they should all be unrestricted. This one should be unrestricted. This one should be unrestricted. Alright, so they’re all unrestricted. And they’re a well, that one’s not there, and they’re all going to a tagged category, the category has been 4020 2020.
15:28
Again, this is probably the most confusing kind of screen to deal with this. That’s why like I say, I would, I would keep the first row there, then populate the debits all down below in accordance with the breakout and then you should tie out to the credit column there and have the same, you know, amount for it. So then let’s post this. Now then, if we go back to our report, now we’re back on the income statement income statement by fund. Now we see we’re gonna we don’t have a change here, it’s still an unrestricted. But if we go into it, now, I’m just going to go into it real quickly, it’s going to have those two accounts, those two amounts that are broken out between the categorization. So here they are, we don’t have like a single line item, that’s, that means it doesn’t have a tag to it, then I’m going to go back on over. And then we could see the total here is at the two to six to 16. Now if we go to the other report the report on the left, we’re going to refresh this screen up top.
16:22
So you’re going to go up top, and you’re going to refresh the screen. And then if you take a look at the right, notice that doesn’t tie out here, I got the 2229. And it the four items are broke out 4020 2020. So something seems funny with this line items. So 2229, let’s go back over to the other tab, where it should be the 226260. So this number is right. And again, if that journal entry is a little bit quirky, and I think what’s happening here, there might be something a little bit quirky with the system that doesn’t like that, that first debit not being there, I think that might throw things off. So let’s check it out. Let’s say okay, this has to work, we’re off by if you subtract those two numbers, we’re off by a 3360. So these three are allocated out, you can see four columns, this allocation is correct.
17:10
But let’s take it let’s go back into this journal entry, I’m going to drill down into that journal entry and check it out a little bit further here. And you’ll see what’s happening here, notice they kind of readjusted that first number and put the debits on top and the credits on the bottom, which is kind of like probably just a quirky thing within the accounting system that doesn’t like you know, the database doesn’t like the credit on top. So, so it readjusted things, which could be a little bit of make things a little bit more difficult for us. So note, we have the debit, debit, debit, and then and then they put the credit down here, it’s been readjusted, and our categories got shuffled, when that readjustment basically happened. So that’s what it looks like happened here to me. So what I’m going to do is say,
17:53
All right, now I don’t really need the category down here for the, for this one, this is the credit, I’m just going to recategorize my debits and say, All right, now I gotta, you know, make that a little a little bit easier on the system next time. So the 100, this 101 down here, this one that’s large, should be for the 40%. So we’re going to say or let’s make, so this 6007 20. This one got shuffled, right, so this one right here, this tag should be the 40% one. So that’s the 40% for education, for the six to six, seven to zero, and this one up top. So now we’re missing, we’ve got 100, we’re missing the 200. So 200. So there we have it. So that’s a little a little bit quirky there on the system, it’s gonna, if you don’t have your debits on top, and it you know, you could shuffle things around. So when we do the journal entries, we’ll have to be a little bit careful on that as well. So let’s go back down and say, that looks good. Let’s save this, and then go back into our report. So let’s go into the fund accounting.
19:06
And I’m sorry, let’s go into the reports. Let’s go to the reports. Let’s open that income statement back up the income statement by fund income statement by fund now having let’s make it this year to date drop down this year to date this year to date. And then we could have our totals I like to have my total column there and it’s show me the total plays and apply that out. And then didn’t want to apply it out. Show me the total boys. And apply gotta make sure you hit that apply button. All right, and then we’ve got our total which is a two to six to 16. Now let’s match that out to our to our report over here. We’re going to run this again by refreshing the screen. So we will refresh the screen. And so now we’ve got the two to six to 60.
19:57
So that that works out so this system the What they have here something that works really nice with everything, but the journal entries are a little bit quirky most of the time, we won’t have journal entries, we only have those a couple of adjusting journal entries. And so it’ll be pretty straightforward because the forms have that nice percentage breakout that makes this really easy. But that’s going to be the system will work on just doing that a little bit faster in the future presentations here. But we’ve got the 9504. And here’s our breakout that’s going to match out if you wanted to match that to our report, you can see this is the report, we’re basically building that has it by both their nature and then by function, the 90 the 45, the 45, the 45 226 260.
20:39
So we will and and we will continue with this next time, our goal is to have this total column, the total amount tying out to the unrestricted column. And therefore each everything in this unrestricted column for expenses will have been assigned a proper class and will be properly reflected and therefore the total amount will be reflected by the other report. So we can tell people hey, here’s the restricted unrestricted Do you want the restricted by program and by support, then we’re going to go to our other tag report and it’ll tie out to this end number giving that more detail for the users. That’s it for now. Let’s get out of here.