 In this presentation, we will generate, analyze, print, and export to Excel the statement of activities report, which is basically like the profit and loss or income statement report. Let's get into it within two weeks. QuickBooks online. Here we are in our not for profit company or organization dashboard. Let's go on over to Excel to see what our objective will be. So we're going to be in Excel. We're going to be considering our statement of activities, which is basically like our income statement type of report. So here's our statement of activities that we created in Excel. And this is kind of like the format that we are shooting for. So remember, when we're thinking about the statement of activities, there's a lot of information we want. So we want to have all the information that's necessary for reporting purposes. And then we also may want to make it somewhat digestible in some way shape or form so that people that may not be professionals in terms of just looking at reports and even if they are, these are kind of more complex reports in some ways than normal financial statement reports due to the fact of us having multiple columns for restrictions and unrestrictions and so on and so forth. So we want to make it digestible in that way as well. So what we have then is the statement of activities where we're going to have the things that are kind of like revenue, but not exactly like revenue. Those are going to be the contributions, the money that's coming in, the cash flow that's coming in. You could think of it or the revenue or the inflows. And then we have the expenses, things that were expended in order to help and not really generate revenue. That would be a for profit type of term, but in order to help do what we want to do in order to help facilitate the activities that we want to facilitate, which would be primarily the programs for the not for profit. And therefore, when we think about the expenses, it's useful for us to group this by function in some ways too. So we want to then look at these expenses and do something a little bit different than the for profit. And that is that we want to see them by what they're used for the programs and then the admin and the fundraising. Why is this important? Because what people really want to see in a not for profit is look how much of the money that you're spending is going into the actual function, which is the programs. That's what the point is of the not for profit versus the management and the admin and the fundraising. Because if it's disproportionate to management and fundraising, it would seem that the not for profit is not streamlined enough, right? It's not efficient enough. So that's kind of what you're looking for. In order to do that, you need to in some way break the expenses out basically by function. However, we also want to see them by basically natural category, which is what we're expected or normal kind of reporting, which would be the rent expense, salaries expense, telephone and so on and so forth. So we want to have both those integrated in some way that's not going to be too overwhelming for people to basically look at. So we've got that here. And then we also need to be breaking out the items that are going to be restricted items versus the non restricted items. We want to see in the current period, the basically restricted versus non restricted items for the income statement. All right. So now let's take a look at what we generated in and of course, the income statement then would be, you know, the revenue or the money coming in the similar to revenue type of items, the expenses then, and that would bring us down to the net income, which is going to be termed here net increase in net assets. So we're not going to call it net income net increase in net assets. Why is it net increase in net assets? What does that even mean? You'll recall that the net assets is going to be the equity section for basically the balance sheet, right? The balance sheet we had the net assets, which is assets minus liabilities. The income statement is the increase in the current period of that net asset amount. So the net assets net income rolls into the equity section, which here is called net assets. And therefore it's the increase in that amount. So it's not net income that because that implies profit. And so I'm assuming that's the reason. So we have to call it something else. So we're going to call it increase in net assets. So we have increase in net assets in accordance with these two categories. And here's the total increase in the net assets. And then we have the further schedule that we broke out that's going to be breaking out our expenses that we need to be broken out by nature as well as by category, including the programs, management and fundraising. Okay, so let's see if we could see how we can format this in QuickBooks to do all that. So we're going to go back over into QuickBooks and we're going to say let's open up our reports on the left hand side. We're going to be opening up the PNL, the profit and loss, the income statement. So let's open up an income statement. And here we have it. Let's close up the old hamburger. Let's hold down control and scroll up just a bit. Get up to that one, two, five, which is where we like to be. Then I'm going to go back up top changing the dates from 01, 0120 to 013120. And that'll bring us for the month of January. That's what we want to take a look at. So here's like our normal income statement of profit and loss type of report where we have the income, which is going to be the inflows. And again, you may not want to term it income because that implies kind of profit. These are, these are going to be the inflows. There's not really a transaction that happened. Most of this is going to be from basically donations that we've got up top. But the income name here is part of the functioning of QuickBooks. It's part of kind of like the programming. Again, we can't really remove it. This dropdown is because we had to set these up as income type of accounts. And therefore they'll be in the income sub category. Also note, of course, the name is going to be called profit and loss. And the normal name for a for-profit is income statement. So it's a little different than that. And then, but here we want to call it the statement of activities possibly. So I could change that. We could say, all right, let's change this and call it the statement of activities. I could change that actually in the QuickBooks. I could change it by clicking here to update it. Or I can go into the customized reports up top. And I want to go into the header and footer. And we want to change the name of the report to the statement of activities. So that's not a problem. And we can actually save that, of course, if we memorize this report, then we don't have to change that every time we can go back in there and find it each time. So then we have the income. Again, might not be the best term, but like we say, we have to have those two, that term that way, because it's in the programming of basically QuickBooks as a category. So we have the income category, and then the expenses, and then of course, the net income. And again, we don't typically call it net income and the not-for-profit organization. It's going to be a change in net assets. Same thing, however, and again, we can't really change the net income line. It shouldn't bother us too much. We all have a good idea of what this means. It's going to be the change in net assets and net income. However, we can't adjust that. It's not an actual account for us to adjust. So then if we go into the income accounts here, we have our inflows, we have the contributions restricted, the once unrestricted, the government grants, and then we broke out the net assets released, so that the transfer from the items that are going to be released from the restricted to the unrestricted. And then of course, in the expenses, we have the expenses broken out by nature here. So now that they're broken out by nature, what we don't see in this format, and we have to basically do it this way, because this is while QuickBooks normally does it, right? And QuickBooks normally breaks this information out in a P&L by nature, not by function. How do we break it out by function then? Because we need that information as well, what we used these expenses for. We need to do that. We did that with classes. So, you know, so then we can go up top and say, okay, let's turn on the classes and go see the drop down here and turn on the classes. And then we're going to say run that report. So now, because we use the classes, now note, we added subclasses for the restricted items, which we said we could do or we could do it in another report with the job costing type of report as well. So but we added them here in both ways. So now this report's kind of a long report, right? We got the restricted items and the total restricted here. And then we've got the unrestricted. And then that includes all the programs and and everything under unrestricted the two programs, the admin and the fundraising. And then we get the total unrestricted. What I would like to see is simply the total unrestricted and the total restricted. Now, you would think that you can customize the report and say maybe I could filter that if I go thing go down here. I'd like to go to the classes and just see the total columns, but they won't allow you to basically just see the total columns, right? So there's nothing here that'll allow you to basically restrict it down to the bare bones, just the total, we can't collapse the column. And the desktop version, you actually can do that. It's a little finicky sometimes, but you can kind of do that. So that's something that we could adjust basically in Excel. And this isn't a I know I did a lot of formatting in the balance sheet. That's not really difficult formatting. We just hide the columns. We're just going to collapse these columns. So in essence, what you want to do is I just want to collapse these columns and see the total restricted. And then collapse these columns and see the total unrestricted and the total column. So that we can do fairly, fairly quickly in Excel. So what I want to do up top is we've got this report and it's called the statement of activities. Let's go ahead and customize and save this report. So I'll customize and save it. And then I'm going to say save. Now if I wanted to go into this report again and not have to change the name, I could go into the reports down below. We can go into the customize the custom reports. So we'll go into the custom reports and we want the statement of activities. So then we go into the statement of activities and there we have it. Now I'm going to go ahead and do some formatting to this report. Now if we keep it like this, we can we can export it like this and save it as a PDF file just like we did before. So I could save it as a PDF file in this format. So I could say let's go ahead and print this. I'm going to print it as a PDF file and I'm going to say we're going to print it, print it. I'm going to print it using the Qt PDF printer. So I'm going to print it as a PDF file. So I'm printing it as a PDF file and I'm going to say print and it's going to ask me where do we want to put it. Pretty soon it's going to ask me any time now. There it is. There it is. So then I'm going to say I want to put it into the QBO and I'm going to pull this over here and so I could see the name and I'm going to call this the statement STMT of activity statement of activities and then we'll go ahead and save that. So I'll close that. Now I'm going to export it to Excel where we can do a little bit further formatting and that's typically what we would want to do with this type of report. We probably want to you know collapse those columns. So let's take a look at what that would look like. I'm going to then export it to Excel. I'm going to say Excel. Let's open this up. It opens down here if I'm using Chrome so it's going to open up in this little bar down here for Chrome. Now what I want to do is take this and put it onto the other worksheet I have so I can have another tab in the prior worksheet. So I'm going to open the prior one that we exported which was like the balance sheet. The statement of net position statement of financial position. I'm going to add the tab here and paste it into here. So I'm going to add a tab to this sheet. Then I'm going to go back to the other sheet that we just exported. I want to take the whole thing and I can do that by selecting this little triangle up top and that'll highlight the whole sheet. You don't want to highlight just part of it. You got to take the whole sheet here then or you can you can be off here someone and hit control A and it should highlight the whole sheet. I didn't do it. I won't do it that way. I'm going to hit the triangle and then I'm going to right click on it. I'm going to copy it. Now I'm copying the entire thing and therefore I have to be on cell A1 or I have to highlight the entire thing again. In other words if I'm anywhere else other than A1 and I try to paste this don't do this because it won't work. I'm trying to paste it normal paste the first one. It's going to say hey I can't do that because the entire I need every cell here because you copy the entire thing. So you got to be on cell A1 or highlight the entire worksheet right click and I want to paste normal so you can hit control V if you want. I'm going to say paste the first one. So there we have it. So I like to do that before you do any formatting and then I'm going to go to the view tab. So if I go to the page layout to view we can see it does not fit on one page. So we're going to have to deal with that. We're going to have to go back to the prior tab and say here we have it. Okay so first thing I'll do is I'll remove the the header. It's I don't like this merged cell because it might stop me from doing some of the things I want to do which is to hide some cells. So what I'm going to do is select that one go to the home tab alignment unmerge and then I'm going to go down here. I'm going to go home tab alignments get rid of the merge and one more time we'll do it here. We're going to go home tab alignment group get rid of the merge. Now all we want to do what I really want is the total columns total restricted and and the total unrestricted that's all I want at the at the first part here. So I'm just going to hide these columns all I'm going to do is put my cursor right on column B and drag over to column E B to E. Let go right click the selected area so we're going to right click on the selected area and hide it then we're going to hide from from here all the way over to the total unrestricted so that's from G to L G to L let go right click the selected area and hide that item hide those and that's basically it so now we've got actually the program total program I don't need that either I want the total unrestricted I'm going to highlight M right click on it and then hide that too and that's basically it so that it looks like kind of tedious but really that's that's the core of what you would need to do because now you have something that's basically you know presentable it looks similar to what we have over here so if we go back over here to our this excel worksheet we've got the with restriction without restriction and we've got our total columns here and that lines up to the 44 244 and the 234 656 so if I line this up down that's the 44 244 and the 234 656 and then if we were to add those up add those up they add up to the 278 900 so that's that's really the essence of of what you want to do then you can further clean things up or make any further adjustments at that point in time I'm also going to rename this tab down here I'm going to double click on this tab I'm going to say that this is going to be a let's just call statement of activities and there we have it and then we might want to like I like I say in here I might want to just say I don't need the total so I'm going to double click here and I'm going to say restricted and then I'll double click here and say unrestricted then the total makes sense over here and then and then we've got income we might want to call it revenue or something like that but that's fine contributions restricted contributions unrestricted note that you could put this into one line item and just call it contributions if we wanted to at you know at this point because then we'll break it out between restricted and unrestricted up here I like having those two lines because it's it's kind of helps me to to visualize it the other way you can deal with that if you didn't want to make the an adjustment obviously in excel is you could set those two items up as in QuickBooks you could have a parents account which was called which would be called contributions and then underneath it contributions restricted and unrestricted and then you could collapse them as you can similar to this this income collapsing line so that's one way that uh that you can you can deal with that but if you if you brought this into here as as well you could say oh maybe I want to bring the contributions up top here and then just call this two three seven zero zero zero and then I wouldn't need this line at all so we can just break the contributions out that way and then delete this line and hopefully I don't think that should should change anything I'm going to say that this then is going to be equal to the sum of these and this is going to be equal to the sum of these and this is going to be that so that looks good now the gross profit line is basically irrelevant because we don't have any cost to get sold so I could just delete this line altogether we don't really need the gross profit line and then the net income is going to be equal to this item minus this so it would be equal to the this minus that and there we have it so we have the contributions and then the contributions we wouldn't need to say restricted and unrestricted I would just say this is contributions contributions and then it would be broken out restricted and unrestricted in that format and then the total so then the government grants the the net assets and then you could do you could do the similar kind of thing here and break it out between the net assets have one account that would go and then break it out between the two if you if you wanted to and then I won't do it again here and then we have the expenses down below so here's going to be the expenses notice against their broken out by by their nature here not by what they're used for not by the function so that's just the way QuickBooks generally will put this so we're going to have to have this information there and then again we can provide the other detailed report which will provide this information by both function and nature so we'll do that in another presentation so we've got it broken out here so this isn't too overwhelming to see there's the total expenses and this is going to be the what are we going to call this if we go back to the other sheet it's like increase in net assets make sure i get it right here we're going to say this is going to be in the statement of activities the increase in net assets let's call it so i'll put that over here say that's going to be the increase in net assets so i'll put that there and that's going to be the format of that again we might want to change the format in here i'm going to highlight the whole thing right click on it and and uh let's say we we edit format these sales and i put the negative numbers in brackets i like to make it like currency negative numbers in brackets let's remove the decimals we can remove the decimals by the way and do some of this formatting uh in in QuickBooks if you so choose as well and then i'm going to go ahead and say okay so then uh kind of make that a little bit more customized so there we have that and that lines up to to some degree again to to this report the format of this report so it lines up pretty much similar to this however it's grouping not by by function but by nature because again that's how QuickBooks basically uh sets it up now this one is by is by function and then it breaks it out by function and nature with this other report which we can do too and so we'll we'll use the classes to do that so now we can use the classes to break out the the you know the expenses and what they're used for so we'll do that in a future presentation so let's go back on over here and so this is where we're at right now we've got the two uh reports i'm going to go ahead and save this now note that as you as you do this if you were to provide this to somebody else now uh you could do this a couple different ways i'm going to minimize minimize minimize if i was to open up our files that we have at this point we're gonna say all right let's open these up we've got our two reports now you could give them to someone in pdf format again but again this statement of activities isn't formatted as well as it could be in excel if we had to print them out and you had to print them out individually like this then you'd have to collate the reports so that's kind of that's not fun and then in excel we can do some added formatting as we've seen and we can also print it from excel so that they're already collated so that would be nicer and we can make it we can make a pdf file that we can give to someone with these two reports attached to it so let's take a look at that now if i go back over here and i want to send these two reports to somebody i could use this and the pdf printer to make it a little bit easier so i can go to the file tab i can go to let's print the report and then if you were to print it to a printer to give to like the board of directors or something the key is that you want to make sure that you print all the entire workbook so i got the entire workbook so it has the statement of financial position and uh the the not-for-profit the statement of activities and then if then i can print this and it'll it'll print all collated for us and then if i want to print it as a pdf file i'll use the cute pdf file which is a free program so you could get the cute pdf file printer and use that and that'll allow me to print this as a pdf file with both reports on one pdf file which i could give to somebody so i'm going to say print and then um and then we'll see where we want to put it so it's going to ask me what do we want to put it i'm going to put it here and i'm just going to call it financial statements again i'm going to say financial statements save that then i'll close this and now we've got this one financial statements pdf that if i wanted to to attach to an email or send out to the board of directors by email or something like that then it's all on on one pdf i don't have to attach multiple reports and if you have like you know five or ten reports this is this is nicer than even a zip file uh it's easier for someone to just pick up and and um and work with typically so in a case that's going to be it for now let's get out of here