USA Tracking Different Flavors of Money

[Pepin_the_Short]

Okay, I am going to try to describe my problem as best I can.

So I have money coming in from different organizations, and each check that they cut carries a 'code' of sorts that restricts what I am allowed to spend the money on, although some money is so coded that I can use it for everything. They provide money based on a budget for broad-scope tasks that need to be completed by my organization, and, once each task is 'fully-funded' per the budget, the money for that task dries up.

However, the money comes in irregularly, and oftentimes I have to pull some money from an account that is assigned for one project and use it on another, because the more urgent project is not funded enough to meet its obligations.

To use a metaphor, I have a bunch of pots on one side that may or may not mix with each other, and a bunch more on the other than I have to fill. In order to keep myself from, for example, winding up with nothing left but money that is not coded for the remaining projects (which would create a frustrating admin drill for everyone involved), I want to get ahead of this and develop some kind of tool that can help me track what's in each of the pots on both sides. Unfortunately, I am not enough of a spreadsheet wizard to create anything more useful than an itemized list of what money I've received and what I've sent out, which can grow quite out of hand.

Does anyone have any experience with this sort of problem? Or know of a tool that they could share?

 

[John Baker]

I honestly don't think that a spreadsheet or anything close to it is going to help you. Perhaps someone with
extensive spreadsheet experience can assist in this regard. If a spreadsheet is on your desktop, or on a display screen,
are you going to be any further ahead, or is this just going to tell you something that you already know?
However, what I see, is a problem that doesn't belong in your court.
Projects are sold and committed to based on extensive credit reviews, industry indicators and other "getting business"
techniques. A remittance cycle is the norm when any project is committed to and a contract is agreed upon by all parties.
Some how, a lot is missing between the lines, long before this business hits the top of your desk.
In the ideal situation, project information would be submitted daily to the project executive, usually in sales, and he or
she would have a skull session to track progress or knuckle down to problems, based on that feedback and analysis.
I can tell you from my experience, any business that doesn't pay its way on a timely manner would be it halted and
a project manager would find himself/herself in front of somebody ASAP.
In my opinion, you seem to wearing too many hats that should be sitting on the top of others. Now I could be reading
your situation wrong.

 

[Fidget]

Projects are a menace in my experience. They're signed up to with all sorts of complex clauses in them making it very difficult to just refuse payment or halt the project completely without resorting to legal action. Something that doesn't help is when the supposedly project manager moves on, or is assigned to a new one, and somebody else takes their place and sign off (or lack thereof) of stages quickly gets blurry. I'm talking about I.T projects here.

We use spreadsheets - trackers, we call them, to at least try and keep up with what's going on. But it's not easy.

 

[Pepin_the_Short]

Your responses are very much appreciated, you guys. I am inclined to agree with you that I am wearing other peoples' hats, but that's just how things have shaken out. There are other people tracking this in their own way, but their priorities are very, very different from mine (that is, they don't care if I run out of money) and I want to make sure that I am doing my due diligence.

To explain the mechanics in a bit more depth:
-Each item of funding comes with a 'document number.'
-The funding in the document is broken down into 'coded' sections, telling me what I can and cannot spend it on, and when the money expires.
-Combine the code and the document number to a 'line of accounting.' These represent the banks full of money that I can actually spend.
-Each task has a total estimated cost, which I am calling its 'budget.'
-The different outside organizations have reached an agreement as to how much of each budget they will fund, and that drives the funding that they send.

As I said before, the guys in charge of actually sending funds seem to do so by throwing darts at a dartboard, not in response to what is actually needed. So I need to figure out how to connect lines of accounting to budgets that are unrelated, and track this mess in such a way that I'm still left with a balanced equation at the end of the FY.

If this problem is familiar in any way, I will accept any spreadsheets that you guys are using as trackers. Even if it doesn't apply to my situation, there may be some tools buried in there that I haven't been able to think of. Right now, I'm working on a database that uses these lines of accounting as its backbone, connecting them to budgets and document numbers with plus and minus dollar streams on both sides, but it's getting clumsy.

 

[kirby]

Your project sounds perfect for the use of an Excel pivot table. Round up an Excel literate friend who can help you do this. Not difficult.

 

[KristiC]

this sounds like governmental and non profit accounting, which I am taking now.. it sounds like you need the advice of someone who has worked with governmental or non-profits, and there may be specific software you need designed for this.

 

[Samir]

I think KristiC has hit the nail on the head as that's an industry that regularly deals with this issue. I applaud you looking for an existing solution because I think it does exist in any organization that depends on donations. Even checking at a local 'thrift store' and asking them may help as they have to figure out an inventory model based on donations, which would be similar to your funding issue. Perhaps they could share their methodology.

Good luck and please let us know what solution you find. The solution you find can be useful in many other scenarios, especially if you can start to forecast and stay ahead of the curve.