"EDITED ONLINER DIALOGS"
TOPIC: Fund Acctg Set Up via QB ORIGINATED July 2005
TOPICAL OVERVIEW: Setup for a unique Non Profit Organization. This dialog speaks to some of these special Fund Accounting and Excel doc matters as handled within QuickBooks (TM) (QB) .
FILE: 2038-077 UPTD: March 09, 2012
TO Jim B:
I just joined this http://groups.yahoo.com/group/qbunch group to see if I could find some answers. I used your recommendations to set up my special funds, but am struggling with the export to Excel. It seems cumbersome to work with the data after it is in Excel. I'm not quite sure I even set up the Special Funds correctly to produce the reports I need--beg. bal, changes in fund, end. bal. I guess it boils down to making sure I set up the chart of accounts correctly.
TO Onliner::TO Jim B:
Thank you for connecting up.....would you feel comfortable in viewing my desktop ?? See www.bcidot.org/hlp/0130-06.html for more specifics of how this is done as it will enable you to see samples on my desktop and get a better idea as we interact and view how QBooks is your Fund Acctg emphasis and not Excel. I trust you have digested the contents of: www.bcidot.org/qbb/0601-06.html www.bcidot.org/all/0041-01.html Have you looked into the resources found within? www.bcidot.org/hlp/0079-20.html
Let me know how I can help
Thank you for your quick response, Jim. It does not seem necessary to view your desktop.
First, I am a bookkeeper with Fund accounting experience, but I have never set up the Chart of Accounts for a ministry before, just worked with what I inherited. I think my biggest problem is getting the information to produce the reports that I need, and I know that requires that I set up the COA correctly. I actually think I should be able to get QB to provide what I need by printing a P & L by classes. Maybe I�m wrong in my logic, but can�t my various SPFs be thought of as one of my �store locations� in the models that QB references?
I have read just about everything pertinent to setting up for Fund Acctg at least twice. It has been helpful. I set up my COA with GPF using QB income/expense lines and then my first SPF this way:
Class is "Endowment Fund"
Class Sub Accts
10 Bal. Fwd.
41 Investment Inc
42 Gains/Losses on Investments
44 Other Income
52 Management Fee
53 Program Director Compensation
54 Misc. Expense
90 Auth In/Out
I followed your recommended Method 1.
TO Onliner::TO Jim B:
We have a fundamental problem as to the basics of 0601-06 Method 1. In Method 1 a Special Purpose Fund is a QB Equity Account � not simply a QB Class like Intuit suggests within Method 4. Please see the QB samples within www.bcidot.org/qbsam/0719-07.html . Please particularly notice how the $129,369 of QB Equity accts appearing within the B/Sheet is reconciled via QB classes via Proof 3.
This method gave me my Endowment Fund Net Assets on the B/S, but the Board wants to be able to see a Statement of Financial Position. I read your editorial about QB Overkill (I think that was the name of the article) and agree wholeheartedly as this is the attitude I ran into when the Treasurer (a CPA) told me I didn�t need to segregate funds that were transferred into the GPF from the SPF because they were still �on the books� within the SPF (held in the larger pool of temp rest assets.) Unfortunately that type of thinking got our Director into some trouble because he has lost sight of the balance of the funds that were transferred and they were committed funds. I�m not sure if that is clear, but the point is, the Director likes that he can now see the Net Assets� real-time balance so he can manage the funds with assurance that he isn�t �borrowing� committed funds. Therein lies my confusion, I suppose. Method 1 has enabled me to give the Director the day-to-day information he needs, but I can�t see how I will be able to produce the Statement of Financial Position (including Fund changes) that the Board emphasizes.
Generally my preference is to pool all of a Ministry�s "Operational assets" and utilize the features of www.bcidot.org/all/0041-01.html to do your General Purpose Fund (singular) and Special Purpose Funds (plural) normal operational Ministry activity acctg. The emphasis here is �operational� Then I recommend that you have a separate set of QBooks for such activity regarding what I call �Trust-Fiduciary Funds-TFFunds� (which includes Endowments) where you can clearly separate committed funds/assets, etc. and not view them for operational purposes. You might find the info within www.bcidot.org/qbb/0680-02.html of interest. Notice my statements within www.bcidot.org/all/0051-02.html#4 plus some more on separate QB set of books found at www.bcidot.org/all/0127-02.html
TO Jim B:
Thank you again for your assistance. I need to correct myself first: I did set up my SPFs as QB Equity accounts, that is how I was able to see the current balance after I entered my transactions against the account applying the various classes.
Great!!!! so very glad to hear that!!!.
TO Jim B:
I would like to create the reconciliation you reference, Proof 3 (� Please particularly notice how the $129,369 of QB Equity accts appearing within the B/Sheet is reconciled via QB classes via Proof 3.�), so I can create the reconciliation, and I have tried unsuccessfully.
Please go to www.bcidot.org/hlp/0079-20.html#7 for said instructions. If you still have problems we can view QB samples of same via my desktop, etc., as mentioned earlier.
TO Jim B
I like the idea of using a separate set of books for the Endowment Fund and then consolidating them, in our case quarterly, for Board review. Even though a separate set of books sounds like it complicates things, this method makes it is easier to satisfy the needs of the Director (operational) and the Board (fiduciary review). Now, I don�t see where QB Pro allows me to combine company data for a consolidated report, so I would export to Excel to achieve this, correct?
That is correct ----- this is best done via Excel --- but not an export process. Did you notice the one page �Big Pix� (designed for overview purposes for Boards and top Mgt) via an Excel doc at www.bcidot.org/qbb/0601-06.html#97 and at www.bcidot.org/qbsam/0786-07.html
TO Jim B
Right now I�m feeling as if I understand your method conceptually, I just need to set up QB correctly to produce what I need. I am almost there!TO Onliner:
Very encouraging, ........ keep looking up !!!!!
TO Jim B
Yeah! I was able to create the Proof I needed with your instructions.
YEAH --- Indeed
TO Jim B
Now, the Excel driven consolidated report�I�m comfortable in Excel, and I read the explanation of the Big Pix report. What I�m not clear on is whether I am supposed to (1) just set this report up from scratch and then plug in the numbers quarterly and annually, or (2) if this is a report that can be designed to pull from QB. I imagine (1) is the way to go because I can�t figure out how I could set it up to dump the information into the correct cells from QB.
Right on re (1) --- establish Excel report structure from scratch � said numbers for �Big Pix� are usually manually produced appropriate condensed monthly numbers and so inserted/reported that often.
As most of you know, at www.bcidot.org dialog by email with Onliners regarding Ministry Finance (MinFin) matters. Above are what we call "Edited Onliner Dialogs", or edited versions of actual dialogs with some of you; but we have honored your privacy and not used your name or that of your Ministry. Our purpose is to make topical MinFin information available to any interested Ministry Finance Team so we all can learn from these perspectives. Thanx - Jim Bramer, Retired Auditor-CPA - Proverbs 9:10
ABOUT US | OUR PURPOSE | SITEMAP | COMPUTER FUND ACCTG | HELPS | HOME - Contact: Jim@bcidot.org