i’ve got a nagging headache.

i’ve been trying to redesign the revenue portion of our financial database at work. i worked on it straight for a week before cambodia, until i had this nagging problem with negative expenses. well i solved that by just treating a negative expense like a new source of funding.

what this has all come to is that for everything to work correctly, i’ve got a little algorithm that must churn away for 13 seconds (comparing every expense to every funding source) before the user interface can open. this is starting to make me think all is not right, so i’ve forced myself to step back and see if there are some more fundamental changes i need to make to the fiscal system as a whole.

the gist of the problem is and has always been that the purpose of the database is to connect expenses that hit our university accounts to activities that our project runs. on the revenue side, we’ve got to track the funds we get from USAID by type (which is pretty straightforward) and then we budget portions of what we receive to each activity.

so what we have here is:

• an expense can be split among one or more activities
• an activity can have one or more expenses
• a fund can be split among one or more activities
• an activity can have one or more funds

the point of all this is to be able to answer the inevitable question that USAID will ask: “how much have you spent of X fund?” or “how much Y funding do you have left?”

so take a given activity. it has a bunch of expenses and a bunch of funding. you can sum the expenses and find out how much the activity has spent, you can sum the funding and find out the total amount of funding the activity has received, and you can subtract the total amount funded from the total amount received to find out how much money the activity has left to spend.

but you cannot ask specific questions about how much of a given fund has been spent, especially project-wide.

so my algorithm is a loop that compares (for a given activity) the amount spent by year to the amount funded by year, and it stores the results in a new table so we can answer the questions above.

what is unique about our accounting system is that we can go back in time and reallocate expenses to different activities as long as the bottom line remains the same. which means from month to month the amount an activity spends can vary widely, going up or down, depending on how expenses are allocated.

this flexibility is the source of my problem, namely that the static table generated by my algorithm depends on the state of the expenses in the database at any given point in time. since any number of expenses can be reallocated at any time and since expenses are imported into the database every month and since there is no record of what has changed and what hasn’t, my 13 second long algorithm must run (periodically) so that Phill (my boss) and USAID get an accurate picture of ‘how much has been spent by fund’.