Empower Your NPO with Billing Software in Excel for Seamless Management

Billing software in excel for NPOs

I'm John Riley and I am the owner of capital accounting services in Middle SE Vermont my website is capital accounting. org for the past 30 years or so I've been working with nonprofit organizations and assisting them to manage their financial information and their statistical information for the financial information I use QuickBook software but for statistical information and managing a lot of the financial projections I use Excel QuickBooks does great in doing the day-to-day transactions but does not do very good in trying to put them together in meaningful reports it's an idea of some of the things that can be done with Excel and some of the things I do I work a lot with budgets and one of the things I insist on in working with budgets is to show detail so we know where the figures came from it's a very simple budget showing some detail in the middle showing the the summary on the the left hand side and showing some part charts that might be of more interest to the board or somebody who was was outside the the organization as you can see one of my first problems is that I have a deficit almost a $10,000 deficit so I can play what if and I can see how I might go about balancing that budget one of the things I could do is cut the social worker to 30 hours a week and notice when I I hit enter the total budget for the social worker total budget for salaries as well as The Fringe of benefits and that bottom line will change so there's a way I can turn that deficit into a surplus but the question is can I do it with reduction of hours for the the social worker this is looking at something a little different this is a a bigger budget or more detailed budget uh budget in fact that has several sheets and because it has a number of sheets I've done a table of contents and I can click on what I want to see on the table of contents again this budget has a loss you can see that a large part of their income is coming from ticket sales and I have a separate page on ticket sales uh this gives me a list of the various types of presentations the amount of the sales the concession Revenue sponsor ad Revenue grand total of Revenue expenses and net and what I can do is right now I have $81,000 in presentation Revenue I can say suppose I had 10 rather than five I move up to 115 and my net income for events goes to 41,000 go back to my profit and loss and now indeed I do have a surplus I can go back again on presentations and say well maybe seven is a little more realistic these things change go back on my profit and loss and I still have a profit so those are the types of things those are the wh ifs you can do and play wh if with with any of these numbers and all of these numbers have details on additional sheets going to back and look at something different in Excel and that is the database of excel in this case what I'm doing is I'm tracking the number of hours of service I give for in in office services in Home Services I'm looking at the fees and both and I'm looking at that at my various offices looking at it by month I'm looking at it by year and by fiscal year oh for the past 10 years or so so there's quite a bit of data there's 5,000 lines of data in here Excel can handle up to a million lines of data so there's virtually no limitation on what Excel can handle do a very quick analysis that I can do on a table and say what I want I don't want all my offices I just want Chicago there's just Chicago or I really have too many fiscal years I want to look only at fiscal 2009 there's fiscal 2009 kind of oneclick instant analysis is very easy to get may not tell you however as much detail as you like and consequently the thing that is most powerful to me in Excel is the use of so-called pivot tables and pivot charts this is a pivot chart and what I've asked for it to give me is the sum of hours that are given by year some of inhome hours in off in home hours that are given by year I can say I really don't want that what I want is in office hours there's my in office hours by years I can say that's not really as detailed as I like so I'm going to bring down County there's my in office hours by County four years I can also say I'm going to get County off I can also say I want to look at it by site there's my information by site by year but that may be a little too much because there's just too much data on there so I can say that instead of all Sites all I want is the Boston site so there's my information on Boston they didn't do very well in 2007 uh by year so you can kind of get instantaneous analysis of anything that's on here in any way way you particularly like to see it you can also using that same pivot table um pull data from the web or from any Source uh I often pull data from access access is very tricky to to write reports and do charts and graphs and so I export it into Excel you can do the same thing from the the web and you can see it's laid out pretty much the same way so you can get the same type of information and that's also you can you can pull pivot charts you can pull pivot tables again the sort of same way to to use data from the web and move on to a third topic something a little different this is an allocation sheet a lot of organizations have uh various funds that they allocate money from funds have a budget and we want to keep track of how we're doing with that budget and this this case I've set up a Fuel Fund food clothing Transportation housing and what I can do is I can add entries to this with a date with the social worker who has done the uh assignment I can then say um what program it is assigned to and I can use a pull down I set up a pull down based on my budget and I'm going to choose in here temporary house and I'm going to give this to Sam Jones who got some other funds notice it puts the names together here Sam lives in Granville so I'm going to add Granville and here I'm going to give $1,000 for for temporary housing now notice what will happen as soon as I hit return is you'll see that show up in the allocation and it has reduced the balance same way we looked at data on the other data sheet I can now pull that information by town and I can say this is how much I gave to the people who lived in the town of Burlington uh or I can pull it by program and I can get the D details of what I allocated and there's the details of what I allocated there's 355 there there's 355 there so it's a great way to be able to manage funds and have some management information as to where those funds are going okay those are the things I wanted to show you here uh if you have questions you can give me a call I'm at 82229 5988 you can also check out the website Capital accounting. org thank you for watching