In this presentation, we will generate, analyze, print and export to Excel a report that will show the expenses both by their nature and by their function. Get ready, because here we go with aplos. Here we are in our not for profit organization dashboard. Let’s first take a look at our Excel sheet to see what our objective will be. We’re over here on tab 10. You’ll recall last time that we made the statement of activities. So the statement of activities in essence, the income statement being broken out by two columns, and a total column, those with restrictions and without restrictions. And now we’re concentrating on those expenses, which we want to break out both by nature and function, which we could do on the statement of activities.
00:44
However, it would be overwhelming. Therefore, we’re going to say one or the other basically, on the statement of activities, and then create another report here, we’re showing them by their nature, what they’re used for, in the report in aplos, will actually show them by function, their normal kind of categories, and then we’ll provide another report. So then the other report that we’re working on now, looking something like this, where we have the expenses by nature, telephone printing, so on, and function program, and then other stuff, management and fundraising.
01:15
Very important report, because remember that with a not for profit organization, we kind of like want to compare the program, what they’re spending money on in terms of what they’re designed to do, versus the admin and fundraising, which kind of shows the efficiency if they’re doing the program information with right we want we want, we would like the money to be going to the programs, and and then the admin and the fundraising to be as efficient as possible for that to happen. Okay, so then we’re going to go back to the dashboard, let’s open up our reports, we’re going to first open up the statement of activities that we worked on in a prior presentation. So that in essence is the income statement by fund, but we made custom reports down below.
01:58
Now we have a statement of activities, basically, for external use. And we have the statement of activities here, our worksheet, let’s open up the worksheet here, this is the one we’re typically going to work on and think about when you know designing things, and it has more the subtotals. We’re down here in our expenses area. And that’s what we’re going to be concentrating on now. Now I’m going to change the date up top, and let’s just make the date, we’ll make the custom date. And we’ll make this going from January, bringing this on back to January 1. And then we’re going to go out to the 31st of January, for the entire month of January. So there we have that. And now we’ve got the restricted and the unrestricted. So we look like the statement of activities on our Excel worksheet, only difference being here.
02:49
Now we’re breaking the expenses out first, by their nature, what they’re used for, then by function, you’ll note up top that you’ll note that all the expenses are in the unrestricted category. And that’s because the way we’re working this is that whenever something becomes unrestricted, then and if it was previous restricted, and then it became unrestricted, we’re going to remove it with this transfer, right, we got this transfer, taking it out of the restricted to the unrestricted, and therefore the expenditures that we actually make are going to be coming out of unrestricted. Now we need to take a look at that unrestricted column, break that out into this categories, we use a format of tags to do that. So we need to run a report by tag breaking this out now by both function and nature.
03:38
So I and you can think about this, if you’re if you’re talking to someone presenting this report, the first report we would present for the statement of activities, the activities that have happened would be this report, then they’re going to say, all right, that looks good. We have restricted unrestricted, what exactly did you spend the money on, you know how you breaking that out between what you’re doing what you’re designed to do in terms of program versus other stuff, like admin and fundraising, say, okay, right click on this tab, duplicating this tab, going back from to the tab to the left, let’s go on over to the reports. So we’re going to open up another report, we want to take a look at the income statement this time by tag.
04:15
So we’re going to scroll on down, we’ve got the unrestricted reports, we want the income statement. So we’re going to open that income statement. Let’s do our date thing up top, let’s make this a custom date range. We want to make this going from bringing it back on over to January and then to bringing that back to January as well January the 31st, then we’ll run that report. Now it usually only shows these three columns just for by default, I think it’s just a room thing. You know how much room is on the report. So you’re gonna go up top, as well. And we need to make sure that we’re going to unrestricted and we want to make sure all categories are checked off education, community service, admin and fundraising. Then we’ll apply that out. Now once we save the report, it should save that formatting.
05:03
So we don’t have to do that all the time. Once we we save and have our custom report. Now this reports pretty much it this is going to be our our, you know, report that looks pretty good for external use. Now note that we’re not focused here on the income side of things, we still have an income line up top. And that’s kind of kind of annoying, but it’s not too burdensome, doesn’t really bother us all that much. And then we have the expenses what we want and the net income again, we don’t really need the net income, because we’re kind of breaking out just the expenses for this report at this time. So what I’d like to do is save this report.
05:39
So let’s save this report. And let’s call it then expenses by nature and function expenses by nature and fun function. And then I’m going to say save, that will also change the title up top. So now we’ve titled The report, basically what we want the title to be. So now let’s see, I’m going to go back into our reports and see if I can find it in the saved reports again, so that’s kind of risky here, because I’m going to go over here and basically delete the report, but it should be in our saved reports in that exact format. So then we’re going to go back down, we’re Living Dangerously here doing this. And then we’re going to save reports. Here we have it expenses by nature and function, that’s the one we want, I’m going to go ahead and open that back up. And we should then have it all formatted.
06:23
So we don’t have to go back in here and open up the fundraising, which is there already. And we have renamed the report, everything looks good. So again, this is pretty good to show to someone as is, however, we might want to export it and do a little bit of formatting because if we export it to excel, we can delete like the income line and the expense line down here. So let’s work on that. So we’re going to now put all of our reports together, we export the profit and loss or statement of activities last time, now we’re going to export this one that will allow us to either print it if we want to, or we can put it into Excel and then print them together. So they’ll be collated together, or we can print them as a PDF file. So they’ll all be on the same tab. And if we need to do any kind of more little little touch ups in terms of Excel formatting, we can do that there as well. So let’s take a look at that.
07:17
So we can go over here. And I’m going to say let’s, let’s do the download. So I’m going to download it as a as a notice it doesn’t have the PDF here. And I think it’s because it’s too long, unavailable, it has too many columns, it says, so it has too many columns. So it’s not letting me do the PDF. So we’re gonna have to do some type of workaround on that. If I go to the print item here, this report cannot be printed because it has too many columns. Alright, so that’s a problem, but we can export it. And then we can do the formatting within the exportation. And then we can print it either as a PDF for one report or another. So what I’m going to do then and say, all right, well, then let’s download it for I can do is download it as an Excel file. Let’s do that. And then we can do any formatting we need.
08:00
Now notice, it’s kind of a favor that they do that to me, and they don’t let us print it as a PDF, rather than squish it up and change the, the the size of the font. Because if you if the if it does that, then sometimes your reports get all messed up, or it prints on two pages wide, which is not a useful report. I mean, giving that to somebody is just kind of annoying if it you know if your report is two pages, why if it’s two pages long, not really a problem. But if you got to tape the thing together to see the columns, you know, side by side, then you know, that’s not really useful typically. So it’s kind of a nice thing that they say you know what it doesn’t fit if you just print it like this. And so we’ll take it out to excel and do a little bit of formatting.
08:43
So I’m going to do enable the editing. Now note that you could do the formatting here, right here and then print it out. When you do it, you can, you know, reduce the size of some columns and whatnot. What I’m going to do is take this whole this whole thing, and I want to put it in the report that we had made last time. So you’ll recall last time if I open up our data files that we’ve been working on, we had our Excel reports where I want to put all the financial statement reports right here. So let’s open that one up. And I’m going to I’m going to take the new report and add it to this workbook. So I’m going to open up this workbook. So there it is. And there’s our statement of activities. Now I’m going to add a new tab with a little plus button at the bottom. And I’m going to put the new worksheet right here on it.
09:32
So I’m going to go to the to the next worksheet and I’m going to select this whole thing by by selecting this little triangle, you got to be in between the one and the A or you can be anywhere else and say Ctrl A, and then right click and I’m going to copy it. And then I’m going to minimize this tab I’m going to bring that on over here. Now you have to be in a one if you’re anywhere else. Like if you’re somewhere else and you try to paste it and I say like paste that it’s gonna say hey, there’s too many cells because you copy the entire worksheet last time, so you can either be in a one, or you have to hit the little triangle again, and then right click and paste the entire thing. So that should paste the entire thing. Now that you’re over here, where you want it to be in the end result, I’d work on the formatting. So then I can go back to the prior tab, or I go to the this viewpoint over here, the layout view. And that shows me if it fits on on one page.
10:26
Now look, it’s here, it’s on one page wide right here, it’s so it looks like it’s fitting, that will work. So if it doesn’t fit your first, your first kind of thing is usually go to the page layout. And say, I’d like to change the orientation and the page setup. And then you could change it to landscape. And that’ll typically be the first thing you kind of want to look into and see if you can adjust it in that format. Now the other thing in the formatting that aplos uses here, when it exports, it doesn’t put it doesn’t pull over the title and I and I’m going to adjust the title the way I did it last time, it’s pretty easy this way to take the title, if I just copy the entire title here. And say, let’s bring that over. And instead of putting it up top here, let’s go to the page layout.
11:14
And that I’m going to put that in the heading. So I’m going to put that right in the heading up top and just paste it in the heading. And so that way, it should show up there. And if you were then to go back over, that’s going to be a little bit easier than what we had that last time, then I’m going to close this one back out. And then I’m going to close and then I’m going to delete this. So that’s kind of a standard format. And then we’ve got to make these row I’m going to put my column on See, I’m just going to make these rows wide enough. Just wide enough, so I can see the all the numbers here. So there it is. Now that’s kind of like the minimum formatting that you that you need to do here. Now I’m going to do a little bit more formatting to kind of match what we did before. So there’s probably an overkill.
12:00
Again, I’m going to rename the tab, I’m going to say this is an expenses, x fences. And then Okay, so let’s do a little bit more now. Now, these these account numbers, we might not need those, right, we might not need the account numbers here. So I could remove the account numbers, and I could remove basically this entire column then might be something that’s not necessary for external use. Now to do that, I want to get rid of any kind of merged cells. So you notice they put these merged cells in there. So what I’m going to do anytime I see a merge cell, I’m going to go to the Home tab, alignment and remove the merge.
12:40
And then Home Tab alignment, remove the merge Home Tab alignment, remove the merge, and then right here on the income, this whole income line I don’t even need I don’t want it. So this is that because there’s nothing here, right. So I can, I can select these two lines and just delete the entire thing. I could clean this thing up a bit. So I’ll just delete that. And then the net income down here, again, don’t need it, all I want are the expenses. So I can just take the whole line and delete that. So let’s just remove that altogether. Notice this cell is merged. So I’m going to go home tab alignment, remove the merge, this cell is merged Home Tab alignment, remove the merge. And then I want to keep this heading and this heading.
13:29
So I’m just going to take that, I’m going to cut it Ctrl x and then paste it. And then Same here, I’m just gonna say Ctrl x and paste, cut and paste. And now I can just delete this entire column, I don’t really need this column, I’m just gonna delete the whole thing. Alright, so there we have that Now note that these headings right here are kind of squished up. So the common way to fix that is to go to the Home tab. So you don’t, you can widen the columns out. And that’s probably why the columns were too wide to print before. But the easier way or the better way. Or another way to do it is to go to the Home tab alignment, and then go to this wrap. So I’m going to wrap the names and then I’m going to center the name. All right now it still doesn’t look perfect, because this one, it’s it’s admin and at all and you know, you’d like that to be you know, the 20% on one line or something like that.
14:23
So you could shorten the name to admin if you so choose. Some people actually don’t like this Wrap Text, just so you know, like the other way you can do this is you can you can unwrap the text. You can say all right, I don’t like to read Did they say I don’t like to wrap text because I don’t get to decide where the cutoff will be. So one way you could decide is to unwrap the text and then I’m going to hit alt, holding down old the key and then enter. And that’ll tell that’ll be kind of like wrapping it but now you’re telling it where you want it to wrap at seven delete the space old Enter. And then Okay, so Then double clicking here, I’m going to delete the space, Alt, Enter. And then I’m going to double click here, and I’m going to remove this and alt enter. Alright, so there we have that, and this one still seems to be wrapping, remove the wrap. Okay, and then I’m going to expand this a bit.
15:26
And if you want them all to be the same line size, then you’d have to expand all of them to the size of the of the largest name. Alright, so there’s kind of, like I said, a little bit of an overkill on the formatting, probably. But there we have that. Now, we also removed the, the decimals before. So we could do that I could say, I’m just gonna take this entire thing. And let’s right click on it, and format this, I’m going to say let’s format these cells. And I’m going to remove the decimal. So I’m going to go to, I like to go to currency, and then negative numbers read and then remove the decimals. And then Okay, so there we have, that might be a little bit cleaner to see. And so there we have that point being now we have the total expenses at the 321 260. So now we’ve got our statement of activities here 321 260 on the expenses, and then our expenses over here broken out by the education and the admin and fundraising.
16:36
Now note, you might want to also just just to give your readers that kind of ratio, give give them the total program, or you could say total programs, which is the sum of these two. And then you could say, total, add, admin. And fund raising is going to be the some of Oh, not that, that one, these two, and then total expenses. And then you could sum these up, because that’s kind of the relationship that people are looking for typically. And so you can say, hey, there’s that, do the double underline over here, I’m totally doing an overkill here. And you can even give them the percentages if you wanted, you can say, well, that means that this one is going to be this divided by the total. And then 60%, I’m going to go home tab number make that a percent. So 60% programs equals this divided by the total.
17:53
And that’s going to be the 40%. And then this should be the hundred percent here and then we can underline this. Let’s double underline this, since it’s already on the double underline. So double underline, and then we’ll underline this one as well. So you can give them that little little added piece of information. Now let’s go ahead and print this now. Now we can print this one page now of course as a PDF file, or we can print the entire thing now as a PDF file so we can collate it, if we need to give this to someone else. We can or multiple people, we can print it all the two pages at one time with one printer without doing fancy stuff with the printer. Or we can print it to a PDF file and send the one PDF instead of multiple PDFs. So let’s go back and see how that can work, we can go to the File tab, and then I’m going to go to see I’m going to go to print.
18:49
And I’m going to print it now if we were giving it to someone, we can use it a printer and just print it to the printer and print multiple copies. The key here though being you want to print not just the active sheet, but the entire workbook, it will default to the active sheet, you want to print the entire workbook. So now you got the two pages here that you’re going to be printing to. And notice the second page. If you go to the second page, now you can see the header. That’s how the header is going to show up when we put it into the header field, as opposed to the header when we put it into the actual body of the report as we did with this one.
19:24
So you can compare and contrast the two there. Also know this, notice that the second page is basically landscape, meaning that you know we put we put it the opposite direction, which isn’t a problem really, if it’s a PDF file, because the PDF kind of takes care of it, the way the formatting works seems to me. So then we’re going to simply select the drop down, I want to pick the cutepdf printer now, because I’m going to print this as a PDF file. This is a free program. So just looking it up and having some kind of PDF printer because this is a really useful tool. Then we’re going to say print and it’ll print, not to a printer, but to that cute PDF printer making a PDF for us. So something’s gonna pop up here saying, you know, where do you want me to put this thing and we’re going to say, All right, let’s see, it’s kind of messy here.
20:12
Let me see if I can find where I want to put it, we’re going to say this is going to go to the desktop. And I had one with his aplos on the desktop, which is probably over here, data files, and then I’m going to put it right there. That’s where I want it. So then I’m going to say, save, looks good. Now let’s check it out. I’m going to minimize this. And so now we could print like I said, each report as a PDF, we can print it collated from the Excel or we can send this one financial statement, which will keep on adding more reports to this is probably the most most impressive way to give it to somebody and easiest for people to handle. Again, you can work on the formatting, customize the formatting a little bit more if you want to whatever your your customization is, but it’s nice to have just basically that one file that you can you can give to someone that’s going to be it for now. Let’s get out of here.