 In this presentation we will generate analyze print and export to Excel a report that will show the expenses broken out both by their nature and by their function. Get ready because here we go with zero. Here we are in our not-for-profit organization dashboard. We're going to head on over to Excel to see what our objective will be. So we're going to be within Excel. Last time we created our statement of activities, we're in here on tab 10 by the way and we are on cell T49. The statement of activities, basically the income statement breaking out that information between the amounts that are restricted and unrestricted. Now then we have to give the other information with regards especially to the expenses and that's going to be broken out both by nature and by function. Now note here on this statement of activities we have it broken out by their function, what they're used for, which is going to be like programs and the general and admin. In our system we have it a little bit different because we have it by nature here because that's going to be the default for the zero system. But the bottom line is you've got to report both of them and you want to give it to somebody like the board in some way that's going to be digestible. So that means so whichever way we have it here we typically don't want to put both nature and function in the statement of activities although we could because it would be a bit overwhelming. Now remember when we break this out why do they need both of this information? Because a company only has by nature, what's this by function thing? Why do I need to know the expenses broken out by program? Just remember that for a for-profit the point of the for-profit is to generate revenue. So that's the point whereas we're not for-profit it's to be spending the money in accordance with the programs. So when we're thinking about decision making or how well the organization is doing a major part of that is how efficient are they with regards to the expenses that are being spent on the program as opposed to the general and admin expenses which those things are important but to have a lean program that's really doing their purpose well you would expect that they would spend a larger portion of their money on the program rather than the general and admin and the fundraising activities. So that's why we need the breakout. So the format of the schedule then it's going to look like this. Now this schedule has the expenses by their nature what we would normally have them broken out by and then also by function the two programs and then the admin and so on and then the totals. Again note this reports a little bit overwhelming so if you put this report in with the normal statement of activities it could be a little bit overwhelming so we're going to try to break this out in chunks to make it digestible for decision making purposes especially with those who might not be professionals at looking at financials and even if you are again these are a little bit different than normal financials and it can be a little more confusing in some ways. Here we are back at zero now so let's open up our reports. I'm going to hit the accounting dropdown. I'm going to open up our statement our income statement worksheet. Now this is the one we typically will be working with the income statement worksheet not our finalized report but when we're generating this information I think the worksheet is a good one to be working with that basically mirrors our statement of activities where we have the restricted and unrestricted activities and then the total. Now we're drilling down on this unrestricted column and we want to break out mainly the expenses by their function rather than just by their nature down here. So I'm going to right click on this tab up top I'm going to duplicate that tab then I'm going to go back to the tab to the left and we're going to open up just a standard income statement. Now well before I do that let's hit the accounting dropdown. We also have the unrestricted worksheet so we've also we've basically done this already so I'll hit the unrestricted worksheet which is going to be breaking this information out. We'll actually do it straight from the income statement though to basically rebuild this so we can practice it. But note here now you have the four categories including a subcategory the two programs the program total the admin and then the fundraising and then you have the total by formula and then you have the total by category. So the only thing we really need to remove here are these we only need one total column so that's and everything else is pretty much straightforward on this looks like a solid report and we could change the net income we'll do that when we export. So let's recreate it real quick and just not have the total columns over here so we get the practice of just picking up and using the zero functionality with the report. So I'm going to right click I'm going to duplicate it again I'm going to go back to the tab to the left and we'll make it a bit more quickly this time since we've seen it before but I want to just show you this construction of it so we'll go to the income statement so we'll go to the income statement and this again we're going to be making our internal kind of report for it now so the date range looks good I'm going to go down to editing it now so we're going to be editing and we're breaking out basically the unrestricted categorizations so I'm going to go up top we're going to say I would like to see another column and we want to see the unrestricted so we want the unrestricted going to select the drop down now first I'd like to see the two programs and the two programs are education and the community service so let's see the education first and then I'm going to click off of it and double click on our item over here and I'll keep the 40% I'll keep it education 40% and that's it then I'm going to go back to the total I'm actually going to just delete the total I don't need I don't want the total column here because that's the total of the whole income statement I'm only looking at the amounts and the unrestricted so I'm going to trash can up top going up to the trash can then I'm going to go back and we're going to make another column so we'll make another column unrestricted I'm looking for then the other program and then I'm going to make community service the other program clicking off of that double clicking on the name then and removing that the 2020 so we will remove the 2020 and there we have that now we could make a total here for the total programs or we could not and just keep going with the expenses I'll make a total because I think a lot of people you know when they do the comparison they might want to see the total because that's what they're looking for the total in the programs compared to the admin and the other and the other kind of thing to see how lean there are how efficient they are one judgment you might try to make with a not-for-profit so I'm going to say this time I want to see a formula I want to make a formula and just add those two up so formula I'm going to insert then the education plus and then I'm going to insert the community service education plus community service I'm going to name this total total programs so total for the programs and then I'm going to make another column for the unprogrammed stuff so I'm going to make another column and this one we're going to say unrestricted as well and now I would like the drop-down and we could pick up the admin so we need the admin I'm going to double-click on the name remove the date so there we have that and then we'll pick up one more there's one more category so well there's one more category and then we'll have the total column one more unrestricted and then I'm going to select the drop-down and then we want the fundraising so fundraising clicking off of it double-clicking the title and then removing the 2020 and then I'm going to make a total column which will basically be these three columns because this total column sums up these two so the total could be this plus this plus this plus this or simply the total program plus admin and fundraising so let's do that we're going to make the another column and we're going to make it a formula type column another formula type column and then I'm going to select a drop-down I'm going to be picking up the total programs plus drop-down then the admin plus drop-down then the fundraising and there we have it if I scroll to the right that's going to be a formula I'm going to call it total all right so that looks pretty good so I did that fairly quickly but this is the second time we've seen it I just want to rebuild it here so we're going to say done and that should pull over this information now the main thing I'm looking for is to break out the expenses so I might actually name the report to to to break out the expenses by both nature and function even though we have the income statement up here so on the income side of things so in essence when I when I export this to excel we may just delete basically the income income statement side of things because our focus of course is on the expense side of things and you know we can actually maybe even delete the income side of things for for this particular report so let's actually check that out so let's go to the edit layout again and let's just be like I don't even want the income side of the income statement here and I'll and to do that I'll just select the whole income because these are all subcategories of income so I'll select the whole income thing and that's going to be a grouping within the formatted enough of the report and I'll just delete it it's gonna say go up to the trash can and we don't want that deleting this group will impact a formula so that's going to impact a formula that's okay I'm going to proceed anyways and then here's the cost of goods sold and then I'm gonna I I could delete that group as well because there's nothing in cost to get sold so I'm going to delete the group so there we have that and then the cost of goods sold I don't need that so I'm going to go into the cost of goods sold because we have everything down here in the in the expenses there's nothing in these accounts these accounts you see a lot of information here but there's nothing in them in our in our worksheet these are just in the chart of accounts there's no actually activity to them and notice if you mess this thing up not a big deal because it's not that it's not the overarching one is this these are the ones that we can always redo it so you don't have to worry about it so I'm going to say close proceed delete and then the gross profit we don't need the gross profits because that's just a formula and so I'm going to go up top and say delete that as well so delete and now we just have our operating expenses now then if I go down to the bottom of this thing all I really want is the total operating expenses so I don't need the operating income so I'm gonna delete that proceed and then other expenses we don't have any I'm gonna delete that delete proceed delete and then the net income we don't need that I'm gonna say delete that and that leaves us with just the expenses the operating expenses so let's see what that looks like going back to our report here and wow that's just what we need right there so that's basically out of a you know a lot of flexibility with the reporting there so we don't really have to do much of anything you know in excel to it to adjust this at all to make it to make it what we would like it to be but so now we're gonna export this for well let's save it first we're gonna go up top let's change the name for it so I'm gonna change the name I'm gonna call it expenses by nature and function something like that so that looks good let's go ahead and customize this one so I'm gonna copy this and save it as a custom report so I'm gonna save it as a custom report like so and then we're gonna save that so it's in our custom reporting let's check it out just to see if it's there I'm gonna go to the last tab now go to the accounting drop-down I'm gonna go down to the reports so within the reports we want to take a look at the custom reports and now you'll see that we have these are our worksheets and then we have our custom reports these are gonna be the two that we're gonna use for basically our final type of reports so I'm gonna go back on over and say all right that looks good let's export it to Excel cell and a PDF so I'm gonna and then we want to put it on that same Excel file we had before so I'm gonna export to a PDF file first shows up on the bottom left I'm gonna make this small I'm gonna drag it into my other where I want to put it so I'm just gonna take that and drag the good old drag and drop there it is let's open up the Excel sheet and open up Excel I'm not gonna drag and drop this one I'm gonna open it because I really want it in my other sheet so I'm gonna copy the entire sheet and bring it over to my other worksheet that's where we want to actually put it so let's open this thing up I'm gonna enable editing and then what I'm gonna do is I'm gonna copy that entire sheet I'm gonna put my cursor on the on the triangle to copy the entire sheet and then right click and copy it and then I'm gonna I'm gonna minimize this and I'm gonna open up our other Excel worksheet this is where I want to put it I want to put it on a tab in this worksheet though with the same one we open last line with the statement of activities so I'm gonna go down here and then add another sheet so we're gonna say add a sheet down here double click on it I'm gonna say this is expenses then I'm gonna go in a one you gotta be an a one or select the entire sheet right click in a one and paste it so now we have pasted expenses by nature and function now the total column is a little messed up here again so I'm gonna widen this out a bit so I could see it I'm just I'm going between G and F and just making it a bit wider you can also double click on it if you want and then I'm gonna go down to the I like to go to this page layout view because that'll show me where the page break is so notice it doesn't fit on one page so I'm gonna go back to the other tab you know there's a lot of different things you could do to fit it on one page obviously this cell is is really large so you could use a wrap text type of thing so you could say I'm gonna highlight these maybe and then and then go up top and say alignments let's wrap the text so if I wrap the text so alignment wrap text then I can make these cells I'm gonna hold I'm gonna hold B to G and I'm gonna make these cells a little bit smaller and it and the thing that was holding it back are these texts and they'll wrap now so that won't have a problem so and I might even be able to double click on them well hold on a second double click all right that was not doing what I want let's make it smaller and there we have that now the wrap text didn't expand this cell so let's make this cell a little bit larger so there we have the effects of the wrap text here I also want to highlight this and center it so home alignment center center so there we have it okay so we could do some more format into that but but that is pretty nice also note that that if you're deleting rows or something like this this merged column can kind of mess you up so just remember you don't have to merge the column up here I'm not even sure why they did it because it's a left aligned but in any case home tab alignment you can get rid of this merge thing and you'll you should still have the cells and then if you want to if you want to like center it across I could go like this to the whole page or just to the extent of the report possibly right click on it and then go to the format cells and then I'm gonna say alignment and I want to have a horizontal alignment across the selection so horizontal across the selection okay and so that's how you can kind of center it if you want to center it without without using that that merging thing that can mess up you know your formatting so there's our reports now we can give this to somebody to the we have two reports now we can give them to them individually or note note if these were all the reports we had and we're gonna keep on making more but you can also go to the file tab and if I needed to print these for example I could print them and I can print the entire worksheet now that's the key print the entire worksheet and that means I have my two pages and that makes it a lot easier to to to collate so I don't need to do anything funny I don't need to print two pages of ten copies and then you know collate them together and also if I have a cute PDF printer and you can you can Google search a cute PDF printer so instead of sending it to a printer I'm sending it to this thing this cute PDF printer which will make a PDF file of it which will now make one PDF file with the two files on it that I can then attach to something which is nice as well so let's do that I'm gonna go ahead and print this with the cute PDF printer it will actually send it to a PDF asking us where do we want to put it so here's it asking us where we want to put it I'm gonna say alright I'm gonna put it on this very messy desktop somewhere so we're gonna put it in here somewhere zero not for profit and let's put it here financial statements so let's do that so then we'll save it there and then if I close this back out minimize this and go into our information here now we have these two reports and the excel sheet so I could give this to somebody by basically attaching these two reports or I could zip these two reports by saying I'm gonna make a new folder and say financial statements financial statements then I can drag our two reports into it which is gonna be this one goes into the financial statements and this one goes into the financial statements and then to attach that one folder I could right click on that folder send to and have a compressed file and then I can send someone this compressed file so that's one way you can give it to someone that's a little bit nicer and then also now we have our PDF file that we can give one PDF file which has the two reports in it which is kind of impressive because because most people don't know how to do that so they might think that you did it with like a PDF writer or something like that to combine the reports so now we've got you know kind of the financials on the one PDF file which is pretty nice so we're gonna continue with the reports next time we'll continue adding to this but that's gonna be it for now let's get out of here