 In this presentation we will continue to allocate expenses to categories. Get ready because here we go with zero. Here we are in our not-for-profit organization dashboard. We're going to jump on over to Excel to see what our objective will be. We're going to be continuing on to allocate our expenses out. So you'll recall down here that we have our statement of activities, which is in essence our income statement, breaking that income statement out to items with donor restrictions and those without donor restrictions. The items with donor restrictions then are going to be broken out. We need them to be seen both in terms of their function as they're showing here, what they're used for, program and fundraising and so on. And we also need to see them by their nature, which is what we would typically think of as expenses, the rent expense, the salaries and so on are categorized in that way. So we have them currently categorized here. We're going to use the categorization feature to be breaking them out in this format. So let's go on back into zero. We've started this process. We're going to be continuing with it. We're going to be opening up our accounting dropdown. We're going to go on down to the income statement. Let's take a look at the income statement worksheet. So let's open up the income statement worksheet. Once open, then we're going to go back up top, right click on the tab up top, duplicate that tab. Then we're going to go back to the tab to the left, and we're going to be opening up our other worksheet, hitting the dropdown. We want to go to the unrestricted worksheet. So let's go into the unrestricted worksheet. So what we're going to do then is go back to the income statement worksheet, and we're going to continue just simply allocating out our activities. We had the depreciation last time. We're going to continue in this column allocating it out to the unrestricted categories, and then further breaking it out to the four categories of unrestriction. Now, I think the easiest way to do this is actually to make a new tab each time so that we can drill down on it and then go back to these two reports. So what I'm going to do is I'm going to go back up top, right click on this tab again, duplicate it, and then use our duplicated tab here to be drilling down on the information. And then once we go back and do the next one, we'll go back to the tab to the left. So we're going to scroll down. We got that 35,800 in the rent. So we're going to go into the rent here. Then we will go into the rent again. So we're going to go into this item. A little different format. This is a spend money form, but we can still just make the adjustment. Last time we had a journal entry, so I'm going to edit the transaction. Let's go ahead and edit the transaction, and then we're going to have the same process here. I'm going to add four new lines, and I'm going to replace this rent with the four categories. So I'm going to say we need to add a new line, one, two, three, four, and then I'm just going to make a whole new item. So it's going to be six, one, one, five, and then that was supposed to be tab, tab, and then six, one, one, five, tab, and then six, one, one, five, tab, and then six, one, one, five, tab, and then we're going to go into the unrestricted category over here. I'm going to choose our categorization. We're going to say admin 20%, select the dropdown again. We're going to say community service 20%, select the dropdown again. We're going to pick the 40% for the education. We'll do it again. And this time we're going to be picking the fundraising. Next, we're going to pull out the trustee calculator. Let's pull out the trustee calculator and do some calculations. So we're going to say this is going to be 35800 times .2, 20%. That's going to be here. So I'm going to say all the 20% ones, then all the numbers on this side. I've got to say one and then 7160. Let's do it again. 20% here, one, 7160. And then down here, I've got to skip one. And then one, 7160. Back to the trustee calculator. Now we're going to take the 35800 times .4. That's not .4. So there we have it. So now we're going to say one and then... Oh, there's not a one there. What happened to the one? One and then one, four, three, two, zero. So there we have that. And now it should... I'm going to delete this first item and it should still add up to that 35800. That's our check. So I'm going to delete this and we're still adding up to the 35800. So that looks good. So now let's go ahead and update this. I'm going to say update and once updated, then I should get a green icon up top or some green stuff, meaning it's everything that looks good. I hope that's what it means. And then we're going to go back to the prior tab and let's update this tab. And it should now appear here in the unrestricted column. So now we have the unrestricted column. It's no longer over here. It's an unrestricted. If I want to see more detail about the unrestricted column, the 20, 20, 40 percentages, then we're going to go to the first tab and update that one. So we're going to update that item and then scroll down and there we have it. So the rents at the 760 and so on and the 14320. And so if I go back on over to the rent here in our worksheet, I think that's right. So everything looks good. I'll review it a little bit more closely later, but everything looks good so far. If I make a mistake, we'll pick that up later. I'm going to go back over. Now I'm going to go back and I'm going to delete this one. I'm going to do it again. So I'm going to delete this tab and then I'm going to right click on this tab, duplicate it so we can do it again and not have to close these reports so we can use that zoom in or drill down feature without having to close this report. And then I'm going to scroll down and pick up the 2225 for the wages and salaries. I think there's going to be two items in this one. So if I go into this one, then it's consisting of two items, right? One was the adjusting entry. Now you might want to duplicate the tab again so that again I can drill down onto here and then go back to this one instead of having to generate the report again and drilling down. So what I'm going to do is I'm going to duplicate the tab again, right click on it, duplicate it. I'm going to drag this one to the right this time. So these two tabs are the same, but this one, that way I don't have to wait for it to basically populate itself. I could just go, okay, now I'm going to go right into the 209, 460, and we're going to apply the same process going to the options dropdown. We're going to edit the transaction. Same process. This is a spend money form. So I'm just going to add four lines. One, two, three, four. And then I'm going to put the account numbers first. That's going to be 6680. Account number 6680. Look how much easier it is with the account numbers. The account numbers actually help this process a lot because if you're typing into words and you were in a software that didn't have account numbers, then you can easily pick up the wrong account. And so I've done that before quite a few times. It's kind of annoying, a little embarrassing. And then we're going to select the dropdown. We're going to say this is going to be the 20%. This is going to be the 20%, second 20%. This is going to be the 40% for the education. This is going to be the fundraising. Then we're going to pull out, of course, the trustee calculator. Why? Because we're doing some calculations and that's going to be the 209, 209, 460 times the 0.2. So all the 0.2 ones are going to be down here. I'm going to put 1 and 41892. And then we're going to go down to this 20% 1, 1 and 41892. And then we're going to go down to this 20% 1 and say 1 and 41892. And then we'll go to the 40% calculation, which is going to be 209, 460 times 0.4. And that's going to be the 8. So we're going to say 1 and the 83784. The 1 didn't show up. Let's do that again. 1 and then that and then there we have it. Now we're going to delete the top line, the 209, 460. And this should then add up to the 209, 460. That's our double check. So I'm going to delete this line, 209, 460. So then I'm going to say update that one. And that should be good. So we should have allocated that one out. Now I'm just going to simply delete this tab. And then that'll take me back to this tab, which had the two items on it. I'm going to refresh it now. I'm going to update this report. And that means one of them should go away now. So one of them went away from here because there's no longer in the unrestricted category. So then I can do that again. I can duplicate this tab. I'm going to right click and duplicate it so that I can go back to that report and see that it should disappear. I'm going to pull this one to the right. So these two tabs are the same, but this one's already refreshed. And then I'm going to go into the 16,800. So let's go into the 16,800. I'm going to go into the journal. We're going to say we want to edit this. And then this one's a journal entry, but same process. So we saw a journal entry before. No problem. We're just going to take the first number and repeat it. So the first line, the debit, 6680, tab, tab, tab, tab, tab, tab. 6680, tab, tab, tab, tab, tab, tab. 6680, tab, tab, tab, tab, tab, tab. 6680, tab, tab. And then we're going to go to the unrestricted. We're going to say this one's going to be for the 20%. This one's going to be for the 20%. The other 20%. This one 40%. And then we're going to have the fundraising at the fundraising 20%. Then we're going to pull out the trustee calculator and do some calculating. We're going to say 16800 times 0.2. 16800 times 0.2 is going to be that 3360. So we're going to say 3360, tab. And then this one, we'll say 3360. And then this one down here, we're also going to say 3360. And then we're going to go back up top again. And we're going to say this is going to be 16800 times 0.4. And then that means that this one's going to be for the 6720. I forgot the six. I didn't forget it. I totally hit it. But the keyboard didn't work right. So there we have it. So then I'm going to say we'll delete the first tab and this one should still add up to that 16800. So I'm going to delete the first tab. And there we have the 16800. So it still looks good. So let's go ahead and post that. So we'll post this out. And then if we scroll back up top, we're going to say there's green. So we're good. We're going to close this back out. I'm going to then refresh this report. This thing should disappear. So I'm going to update this report, refresh it. And then that one disappeared like we've expected. And then I'm going to close this back out. And now we're going to update our worksheet report. Let's update the worksheet report. And now we have that item is now allocated to wages here. So now it's in the correct column. If we want to break the detail out for that column, let's go to the first tab and then update this report. So we will update this report and then scroll down. And there we have the wages broken out the 45 to whatever. And then the 90. Let's double check that to our worksheet. So if we go to our worksheet here, we're going to say this is going to be for the salary. So there it looks proper. Looks good. I think everything's good there. Let's do it again. We're going to go back on over. We're going to go back to our worksheet tab. I'm going to right click on it. I'm going to duplicate it. Then I'm going to take the tab on the left, pull it to the right so that we have the one that's already refreshed and ready to go. Then I'm going to go into this 21 six. This is for the provision for the uncollectable pledges. And we will allocate that one out in a similar fashion. So then we'll scroll down. We're going to be picking up that 21,600. Then we're at this is a journal entry. So we're going to edit this one. So I'm going to go, all right, let's go to the journal and let's go ahead and edit this one. Once again, the debit is the one we want to be editing. So I'm going to add four new accounts for that seven zero. So seven zero, six zero, tab, tab, tab, tab, tab, tab, seven zero, six zero, tab, tab, tab, tab, tab, seven zero, six zero, tab, tab, tab, tab, tab, seven zero, six zero, tab. And then we're going to put our categories. So we're going to select the categories. We're going to say 20%. Selecting the category at 20%. and then selecting the category at 40% and then selecting the category at 20%. Then we're going to pull out the trustee calculator and we're going to take the 21600 times 0.2 and that's going to be then here at the 4320 and here at the 4320 and here at the 4320 and then we'll take the trustee calculator again 21600 times 0.48640 and that's going to be the 8640. There we have that closing up the trustee calculator. This side then once we delete the first item should be 26216 so I'm going to delete this there we're at the 216216 looks good so we're going to say post that please and once posted we should get some green indication that it's done so we'll say that looks good then this one up this is going to be our worksheet now I'm going to update the worksheet and we should have this pull over then to this column so that looks that looks good now we're going to break out that number to the unrestricted so we'll go to the unrestricted worksheet and then update this one and that item then is broken out for the provision so that's going to be the 43 the 43 and the 86 so let's go back on over and say okay so the provision down here 43486 and so on so I think that looks good all right now let's go back up top we're going to go back to the other worksheet I'm going to copy it right click on it and duplicate the tab right click and duplicate I'm going to pull the tab to the left to the right these two tabs are the same but this one has already been refreshed now I'm going to go into that 12900 which is going to be the printing and the postage same process we're going to scroll down we're going to go into that line item we're going to go to the options we're going to go to the edit the transaction we're going to add new lines one two three four of them and then we're going to is that for I missed one somehow I hit it the thing didn't work right six two three zero tap tap tap tap tap tap six two three zero tap tap tap tap six two three zero tap tap tap tap tap six two three zero and then we're going to pick the categories which are going to be unrestricted at the 20 category we're going to be picking the 20 percent category we're going to be picking the 40% and then the fundraising we're going to be picking the 20% then we're to pull out the trustee calculator. We'll take the twelve nine zero zero times point two two five eight zero. So we're going to be picking that up here let's pick it up here one and then two five eight zero and then here where we're going to say one two five eight zero and then down here where we will say one and two five eight zero then we'll calculate this again we're going to be saying one two nine zero zero times point four and that means here we're going to say this is one and five one six zero the one didn't the one didn't go again so there it is and now we're going to delete the first one and once we do that twenty five eight should then be that twelve thousand nine hundred let's delete that so there it is twelve thousand nine hundred closing this back out we're going to then update our report here so we're going to update this it should show a green item up top so we can then close this out and then we're going to update our report here so we will update our income statement worksheet and we see now that twelve thousand nine hundred now is in the correct column and then if we want to break that out we're going to go to our worksheet our first tab update that tab and then if we scroll down we see the breakout for this one being the printing and postage so the five one six zero and the two five eight zero that should tie out to our worksheet here printing and postage so I think that's right looks good i'm a very quick glance and then we're going to go back to the first tab or the the second tab right click on it duplicate it duplicate it i'm going to pull the tab from the left to the right so that working out we're working on the refreshed tab we're going to pick the next one which is this two six zero zero that's in the unclassified column we are going to then pick up that two six zero zero for we'll go up to the options up top we're going to edit it so we're going to edit the transaction we'll add four new rows one two three four new rows and then we're going to be entering the number which is going to be six two two six two two zero tab tab tab tab tab tab tab six two two zero tab tab tab tab tab tab tab six two two zero tab tab tab tab tab tab tab tab six two two zero tab then we're going to be taking the categories which are going to be 20 percent next category is going to be the 20 percent next category is going to be the 40 percent next category is going to be the 20 percent we'll pull out then the trustee calculator in the 6200 times point two which is going to be the 1240 so we're going to be here we're going to say one and one two four zero and then here we'll say one and one two four zero and then down here we're going to say one and one two four zero back to the trustee calculator 6200 times point four you might be like why don't you do that in your head and say no I'm just calculator I'm going to go one and two four eight zero and so we're going to go here one all right so there's the 6200 once we delete that first line that's what the bottom line should be so we'll close this first line 6200 let's close the trustee calculator and then go down to the update we're going to update this one then we should have a green icon showing up up top that looks good let's close this one back out then and then we're going to go updating this report so we will update this report scroll down and now we've got two more to do this too but this one went to the 6200 so let's go to the first tab then scrolling back up top and update and then we'll scroll back down and now we have the telephone broken out one two two four zero the two four eight zero so back over to excel we've got the telephone there it is that looks good minimizing this back out back to the second tab we're going to right click on it duplicate it then we're going to pull the tab from the left to the right then we're going to go into the supplies in the uncategorized column for the five thousand next we're going to go down and go into there's two of them now so I might want to duplicate this tab again so I'll go back up top and duplicate the tab again pull the tab from the left to the right and that way we could see them disappearing in this column so I'm going to go into the 500 here so we'll go into the 500 options drop down we're going to edit the transaction we will then add new lines so four new lines two three four new lines and then we're going to say that we want the category 6250 tap tap tap tap tap tab 6250 tap tap tap tap tap tap tap 6250 tap tap tap tap tap tap tap 6250 then we're going to go and apply the amounts the 20 or the percentages the categories the 20% the 40% and then the 20% then we're going to pull out the the trustee calculator we're gonna then take the 500 times point two that's gonna be 100 so we'll say 100 here here one and 100 and then right down below it we're gonna say one and 100 and then we're gonna go down here and do the same thing and say one and 100 and then of course if we take the trustee calculator 500 times point two actually we're looking for point four which would be you know 200 times point four 200 and let's put that one here we're gonna say one and 200 so one one and 200 now I'm gonna delete the first one and these should add up to 500 then after I delete this first one so I'm gonna delete that 500 looks good let's update that let's close up the trustee calculator let's wait for the green thing there's the green thing so I'm gonna then close this tab and then once I refresh this tab it should populate down here so I'm gonna update this tab and then it will populate for us down here and it means it disappeared we're gonna do the same thing for the second one now so here's the 4500 we're gonna go ahead and edit this so we're gonna go this is gonna be a bill same thing and we haven't seen this form before but looks it looks a whole lot like money to spend money type form right and then I'm gonna go down here we're gonna add another line we're gonna add another line we're gonna add another line add another line we're gonna add the for the account same account six two five zero tab tab tab tab tab tab six two five zero tab tab tab tab tab tab six two five zero tab tab tab tab tab tab tab six two five zero tab categories being unrestricted column 20 percent admin 20 percent community service 40 percent education fundraising 20 percent pulling up the trustee calculator we're then going to take the four five zero zero times the point two and that's going to be the nine hundred the nine hundred we're going to put here well not there that's not where it goes goes here one and nine hundred and then here where we will say one and nine hundred and then here we will say one and nine hundred then i'm going to go back up top again four five zero zero times point four where we will say here one and one eight zero zero so then we'll close up the trustee calculator i'm going to close this or or delete this first one which should result it won't result until i put a one here and this one down below being four thousand five hundred so i'm going to delete this four thousand five hundred looks good update wait for the green thing up top so the green thing should happen up top at any point now any point okay i think it recorded i'm going to go ahead and close this i didn't get a green thing which is i'm kind of disappointed i like the green thing but i'm going to update this report and see if it see if it works so we're going to go on down and we're going to see here that the office supplies are applied out i think that's the one we were on so everything looks good so i'm going to go back to the unrestricted tab update this report let's update this one and we should see the breakout of the office supplies so then if we go down the two thousand one thousand one thousand and so on check that out to our excel worksheet over here if we go to the excel worksheet for the office supplies looks like pretty good looks good there it's gone let's go on back i'm going to go back to our income statement worksheet let's do it again we're going to right click on it we're going to duplicate it we got one more to go i'm going to pull the tab from the left to the right these two tabs are the same now we're going to go down to this two thousand or that nine thousand one hundred end utilities utilities this is the last one we get to do so we want to savor the good times of this one because it's the last one then we're going to go down we're going to be picking up that nine thousand one hundred it's going to be a spend money type form which we have seen already we're going to select the drop down we're going to say i want to edit this form so we're going to say edit the transaction and then we're going to be putting in the accounts again so we're going to say i need four new lines one two three four and then we're going to put the account six two four zero tab tab tab tab tab tab tab six two four zero tab tab tab tab tab tab tab six two four zero tab tab tab tab tab tab tab six two four zero tab then we're going to put the accounts here i'm going to select the drop down 20 percent going to admin another 20 community service 40 percent going to the education and finally we have the other 20 remaining going going to the fundraising. Pulling out the trusty calculator, and we're gonna take that 9100 times 0.2, and that's gonna go here. So we're gonna put that here. We're gonna say one and 1-8-2-0 tab. And then here, where we say one and 1-8-2-0 tab. And then here, where we say one and 1-8-2-0 tab. And then, calculating it again, 9100 times 0.4, and then we're gonna go here and say that that's gonna be one and 3-6-4-0 tab. All right, now I'm gonna delete the first line, and once we do, we should be left with that 9100. So let's delete that one. There it is, looks good. Update this one. Wait for the green thing. I hope I get a green thing this time, because this is the normal form. Yeah, there's a green thing. So I'm gonna close it back out, and then I'm gonna update this report. And we should have been the proper allocation, so all this whole column is gone now. That was the objective. And now it moved over to this column, which is good. So these are the unrestricted items. So now, of course, these two columns are resembling more the statement of activities column down here, which is the statement of activities. So we have these two columns. Now note that in this one, we broke it out in the statement of activities by function, because that might be easy for someone to see on the board of directors. You could, we're gonna need either way. So the goal is to get something that's gonna be easy for someone to kind of read. But notice the software zero is gonna record it first by nature, because that's just typically the case. So we have a similar worksheet, but we have it by nature basically here with one column breaking out by nature. And then we're still gonna need the other report, which is gonna break it out both by nature and by function. So here it is by nature, rent, utilities, and so on, and then by function, program, and then admin and general. So that means we have it here just by unrestricted. So they're here by unrestricted, then we're gonna take these categories and break them out into their unrestricted four categories, that being the education community service, those being the two programs, which we will total up then admin and fundraising, then our totals over here. So remember, these two columns on the right are just calculating the total in two different ways. This one's just picking up the four categories. This one's summing up with a formula. So that's why they're the same number, but they'll give you more detail. Also note that it's nice because this column then is one that we can drill down on to the data, whereas this one is not because it's simply a formula calculation. This one is actually being driven from the data bottom up. So if I wanna drill down on, say, all of these items, I can go to this column and drill down on it within our worksheet. So then we have this broken out, and then if we see the totals then, the totals for all the columns on this worksheet, it's gonna be, the expenses are three, one, two, one, 60. And if I go back over here, three, one, two, three, three, two, one, two, 60. I think a three, one, two, one, 60. Okay, and the problem is that I didn't refresh the report, so I got a little excited to jump into it and didn't refresh, so I update the report now, and then the utilities one, which is the last one, then appears down here. So now if we look at our totals, we've got the three, two, one, two, 60, which is gonna be matching up with the three, two, one, two, 60 over here. So it's gonna give us the more detail on those items. So you can see how these two worksheets will basically work out. Now again, later on, we're gonna take this from a worksheet program and make something, this will be our internal kind of worksheets that we wanna make so we got our check numbers over here and everything. It will make a similar type of worksheet for the external reports within our system. We'll take a look at that in a future presentation. That's gonna be it for now. Let's get out of here.