 In this presentation, we're going to take a look at the formatting of the unassigned categories and take a look at some alternatives for the formatting of the unassigned categories. Get ready because here we go with zero. Not for profit organization dashboard. We're going to be opening up our income statement worksheet going to the accounting drop down going to the income statement worksheet on the right hand side where we have these unrestricted items or the unassigned items I should say not unrestricted. So we've categorized all these are worksheet works well but we still have these items in unassigned and that looks a little bit funny. So in other words if we look at this first item we've got the unrestricted has nothing restricted has that 204 in it and that makes sense for the total these three columns are correct. However we still see that one of the 204 160 over here which is a little bit confusing why is it over there. Well it's because it's part of a restricted category and because we had two separate categories of restrictions we didn't categorize it for the unrestricted category. So there's a couple different solutions you can you can do to solve this problem. It's not really a problem but it's a little it's a little annoying to see this here we'd like to see basically these two columns zeroed out and that would indicate that we had assigned everything. So one one way you could fix this is instead of having two separate class categories here we could have put everything whether it be unrestricted or restricted in one class category and therefore have one column and then just simply choose the proper categorization every time we do a data input form and so we wouldn't have two unassigned columns here we couldn't because there's only one column of a side I'll show you that or detail in a second or we can basically every time we enter a transaction it doesn't relate to one of these categories we make a new category that just says you know doesn't apply here in a or something like that not not applicable. So that's what we'll do so let's take a look at how this would look and again just note that this worksheet is fine you're fine with this worksheet we wouldn't really have to do this but this is just to make it a little bit cleaner in terms of the worksheet setting so you could do it going forward also know that you could do this as you go we're going to go in and do it kind of retroactively but you could do the data input as you go in this type of format as well so I'm going to right click on the tab up top I'm going to duplicate this tab going to go back to the tab to the left and then I'm going to go up top and we're going to go into our settings so I'm going to go into the not-for-profit dropdown we're going to go into the settings so we'll take a look at these settings and then we want to go into the advanced settings so I'm going to go down to the bottom to looking for advanced settings and I'm going to click on it because we are indeed looking for the advanced settings and that's where they are so then we're going to go down here and say we want to go to the category tracking or tracking categories tracking categories is what we want and here's what I mean by this we have two separate sections here so we have two kind of categories and then subcategories or category options within them. So we could just make one category which is basically everything which is called you know this is our category thing and then put all the options down below in it and that means we'll only have one column. We won't have two separate columns in our data input forms and we just make gotta make sure that we select the right ones for the unrestricted categories and the that might make it a little bit more confusing when we allocate the items in our data input form rather than having two separate columns but that could lessen you know the room on the data input form and that's one option we could use to make it possibly easier to make our reports. The other thing we could do is we could have two items as we do here and then add another I'm gonna add another category and just call it NA or non-applicable or something like that I'll say NA non-applicable and I'll say non-applicable and make sure I say this is for the unrestricted so that it's distinct from the non-applicable I'm gonna put over here on the restricted item so I'm gonna then say save that. Now every time we input something and there's not a category for unrestricted we'll go to NA non-applicable so then I'm gonna go back to the restricted side and I'm gonna do the same thing here I'm gonna say alright every time we don't have something in here I'm not gonna just leave it blank I'm gonna say I'm gonna add another category and I'm gonna say NA for non-applicable, non-applicable for the restricted items and then say okay. So now when we do the data input we're gonna do it retroactively but you could do this as you go I'm gonna go back to the income worksheet I'm gonna go into each of these unrestricted items and I'm just gonna assign them to an NA category it's not applicable so to do that I'm gonna go up top I'm gonna right click I'm gonna duplicate the tab then I'm gonna pull the tab from the left to the right the reason I keep doing that is because that makes it so I have a separate tab and then I can go back to this report as I make changes on this one by drilling into it and this one's already been populated I don't have to wait for it to populate then I'm gonna go into the 204.160 so we're gonna go into the 204.160 here and we have two things in it so I'm gonna do the same thing I'm gonna right click on the tab up top gonna duplicate the tab I'm gonna hold I'm gonna pull the tab from the left to the right so again I can make adjustments on this tab and then this tab's the same one I can go back and do it again on that tab so I'm gonna go into the 108 here so we're gonna go into the 108 and drill down on this item then I'm gonna edit this item so I'm gonna edit it and then on the unrestricted instead of leaving it blank because it doesn't apply there is no unrestricted item here so instead of leaving it blank which means it's gonna populate in that unrestricted category I'm gonna say I want to just put it into in a not applicable so not applicable and then I'm gonna update that and then hopefully it'll turn green so hopefully we'll have a green item up top or it says I think it's good think it's okay so I'm gonna check it out now I'm gonna close this out and then I'm gonna update this report and one of these should go away so I'm gonna update this report and one of these should disappear so there we have it now I'm gonna click the other one I'm gonna do the same thing I'm gonna click on this one this is gonna be the other item that was in that unrestricted category I'm gonna edit this I'm gonna go to the option up top I'm gonna edit it and note again I'm doing this retroactively this is not something you have to wait till the end to do anytime it's not it's not applicable to one of these categories you could go in here and say in a basically as you go as you do the data input and so then I'll say update so there we have that and so then we update that report and then I'm gonna close this back out that'll take me back to my income statement worksheet which I will now update and now we have removed it I'd like to see these dashes over here not these things to be empty and then I'm gonna go into the next one I'm gonna do the same thing so we're gonna say the 237 237 went to each of these I'm gonna right click on it again I'm gonna right click up top so we can drill down on these I'm gonna say we want to duplicate the tab I'm gonna pull the tab from the left to the right these two are the same but this one's gonna be the one I'm gonna it's already populated I can drill down on it so now we're gonna drill down on it and make the changes in this tab so we're gonna enter into edit it so I'm gonna say options we're gonna say that we want to edit the edit the transaction and then within the transaction I'm gonna just say that we're gonna put this item to in a for the restricted side this time because it's an unrestricted so for the restricted side not applicable gonna update this then again update this looks good then I'm gonna close this back out and go back to the prior tab so now I'm gonna update this tab one should disappear now updating this tab one disappears I'm gonna do the next one actually I shouldn't I should go back I'm gonna go back and I'm gonna duplicate the tab again so duplicate tab and duplicate the tab I'm gonna pull the tab from the left to the right and then I'm gonna go into that 1897 1897 top we're gonna say that we want to edit the transaction editing the transaction I'm gonna say this restricted column should now be not a sign not in a and so then I'm gonna say update that one so there we have that and that looks good so I'm gonna close this back out and then in this report we should have a signed one out now so I'm gonna update it and it should then repopulate for us so I'm gonna update that and then I'll go into the next one there's only one left so I'm just gonna go right into this one and then we're gonna go to the options up top we're gonna edit this transaction editing the transaction and then I'm gonna say on the restricted side I'm gonna say in a I'm gonna go this is in a and then update that one so we'll update that and that'll take us back to our our report so that looks good so I'm gonna close this back out and then and then go back to our worksheet which have gone away somehow accounting I'm on the income statement worksheet it's gone I'm just gonna keep on going here's the this one is unassigned here so note that these two columns should tie out to this column that's how you know that things are working out and what we might want to have here's another column as well that calculates this by adding these two columns up and that way we can just easily look at those two columns but in any case I'll go into this one now and we're gonna say that we'll change this one so we'll go in here just one item so I'm gonna go into that one item and restricted here we're gonna sit on I have to edit it options we'll edit the transactions and then I can go into that item we're gonna go into the unrestricted item and put in a in a so there we have it and then let's update that so we'll update this one and so once again I I haven't been duplicating my tabs again so I'm gonna have to go to the accounting and go to my income statement worksheet again so let's open up the income statement worksheet keep going this time I'm going to right click on this tab up top I'm gonna duplicate this tab so we're gonna have another tab this is the way I should have been doing it the whole time here find these out so these these are all done below and once we're done we'll also add another total column so be ready to do that I think that's a useful thing on the worksheet as well so then we'll go to the restricted so this item we're gonna do the same process here so we'll go into that one item in it so let's just go into that item restricted one below this one is on both sides where we have a portion of it that's not assigned to go up top and edit it and so this one's unassigned on this side and so I'm gonna say in a here in a there and then this one's unassigned on this side so I'm gonna say in a here and then post that one so notice we're just filling every time there's a blank on the on the one of the restricted categories we're filling it in closing this one back out we're gonna update to this report so there we have that and then let's do this also that took care of both of those so now these are all all applied out now we still have all these items in the unrestricted column for for all the expense items I won't go through all of them but just note that on every expense item let's not do depreciation because it's a journal entry let's do one that's a not a journal entry so I'll go back on over and say let's say like the rent we went into the rent when you do the data inputs you don't have to wait till the end like we did before what you want is it for to be to be possibly leaving it blank for the unrestricted item and then on the restricted ones you can then so I'll go into any one of these will go into the same transaction could be applied it out to four places here so on all the restricted when you're doing it when you're doing the data input you could leave the unrestricted basically blank and then apply it out as we did at the end and that's what should properly show up in unrestricted so that you can then go back in and apply it out the restricted items then you could put all them as you do the data input just to in a and so I have to hit the options to do that so we'll go to options and we're gonna go to edit the transactions so this you can do as you do the the data input so that it doesn't show up in the restricted and as we then adjust it will in the restricted area we'll say in a not applicable and then in a not applicable and then in a not applicable and in a not applicable and so then we'll save an update that and once again I didn't copy the report so I'll go back into it again I'll go to the accounting drop down we'll go to the income statement worksheet assigned that one out so at the end of the day these two unassigned should be kind of of a check figure to tell you that that they should all be zero and everything should be assigned and then these two columns should tie out so so like I say if the if if you see these things that that you leave it the way we had it before it's still fine because you can tell that this one's unassigned for for for the restricted because it was an unrestricted that's why it's unassigned for the restricted side so that's fine it's just cleaner if you can make them all zero because these should be a check figure and then we might want to have another total column that sums up these totals two separate ways for our worksheet not for external reports but our worksheet and that way it's kind of a double check for us so let's check that out I can I can edit this worksheet here and say let's let's say we add another column I'm gonna hold down control scroll down just a bit to let's go to the 100% this total column notice it's total by category so let's rename it total by category and then let's add another one let's add another line and this make this one a formula so I'm gonna make this one a formula and I'm gonna say I want to make this the unrestricted plus the restricted unrestricted plus the restricted and this one's over to the right we can't see it if I double click on it this is going to be total by formula total by formula and then I'm gonna drag these two totals to be next to each other I'm gonna grab that blue item drag it to the left and so now they're side by side and this will give us for a worksheet a double check on our calculation this one's calculating one way not adding these two up but just assigning the categories which should be the same and then these two are adding the two up and those two should be you know the same and you know you even could have another column which would take the difference between these two items which should be zero right and that would mean that these items over here these three columns would be our check figure which should be zero at the end of the day so for example you could take another column and this is really neat that you could do this with zero and you can say okay I want to have another formula and I'm just gonna call this our check number and the check number should be the the total by category minus the total minus the total by formula and this right and then this if I double click on the name this is gonna be check numbers and that should be zero now and I'm gonna then pull this over next to the total columns and so now now these three columns should all be zero at the end of the day in essence those are our check figures at the end of the day so we're gonna say done and then pull up our worksheet on the zero over here and this column is checking these two numbers so these two columns are the same they're calculating different ways this one's with a formula this one's with a category it's really nice to have the category here because it also allows you to drill down on it so this one allows you to drill down on it this one gives you a double check this one might be redundant you might not need that because you could see these two tie out but if you have a column of zeros it's just so easy to see those zeros and this report's not too big that that's not too bad to be able to see so these last three columns then if we input everything you know properly in this in the new system we set up with these an unassigned means that these should all be zeros and again these we know why these are not but you know if we did it that way we can then see all these are zeros not a problem these are here you don't have to do that but like I say it could be a cleaner report and you can kind of double check on this and then you could take this report and make our our final reports which will basically be this report with just these first three columns and I could just make another report this is my internal worksheet that helps me to have my check figures in it then we can have an external report which is the one that of course we would be providing to whoever needs it the board or whatever with minor adjustments we might make within Excel however a lot of the stuff we can do is actually in the system with this formatting of the reports