>Oops! I didn't want to come here - please send me back.
Topics Covered:
SSEC Business Services runs on home grown software which will be briefly described here. Historically, the Center has been unable to use the UW's standard accounting system because it is too limited to handle the variety of requirements encountered in the kind of research we do. The typical scenario for other UW departments funded by grant money is that a given project will be funded by a single grant, and all costs associated with the work, whether labor or purchases, will be funded directly on the particular account established to support the grant. SSEC operates often on contract money rather than grant money, and one of the chief differences is increased accountability for activities. On a contract, with NASA for example, we may be required to give progress reports on many sub tasks within the overall program. With the UW's accounting system of one account per project, this would be impossible. SSEC's accounting system uses multiple project numbers within an overall contract structure to enable the tracking of sub tasks. That's a quick thumbnail sketch of why we've got our own accounting system separate from the University's. Some of the details appear in subsequent sections.
The accounting suite of programs were originally written back in the mainframe days, before desk top computers existed. As with most mainframe software, it was labor intensive to operate, and difficult to get changed as business needs changed. It's hard to get programmers to work on dry accounting stuff when they're used to writing things as interesting as McIDAS. Anyway, around 1986-87 the state of PCs was such that we (John Roberts and I basically) decided it was time to explore moving our accounting to desktop PCs. At the time, the best desktop database was dBaseIII+ and there was programming expertise in that platform across the street (DoIT now, MACC back then). We started with two projects, the labor run and the capital inventory. MACC's performance left a lot to be desired. Even though they were laying off programmers, we couldn't get them to commit to steady progress on our projects, so after we got the beginnings of these first two pieces from them, we decided we had to have our own programmer. That's when we hired Kelly Roth (Kelly Tucker back then). She patched up the labor run and has been busy ever since growing and evolving our whole accounting system. We never have gotten back to the capital inventory except for minor patches and fixes.(Slight aside: During this same time frame - actually dating back to about 1984 - I was heavily involved in the beginnings of SSEC's administrative network, and Kelly played a major role in growing the network as well until it was turned over to the TC group when it was established a couple of years ago.)
As time went on and desktop products improved, we evolved from dBase III+ to FoxBase, FoxPro and most recently to Visual FoxPro. Kelly's an excellent programmer, and with me helping to define what the requirements are, we've basically built everything that keeps business services functioning in the Center. A broad outline of what these various systems do appears below. Some of the more recent software additions in business services have been written by me in Visual Basic, which Kelly is also starting to pick up. There's more about that on another page.
Labor Run:
Because SSEC can be involved in several hundred on-going projects at any one time, employees are required to keep track of the hours they work on each project number they participate in. The hours and project numbers are recorded on time sheets that SSEC's Business Office collects at the end of the month. The labor subsection of the accounting system has a rate table holding the hourly rates, adjusted for benefits and other things that beancounters need to worry about. When the hours and project numbers for each person are put into the system, a monthly file is generated that holds the total charges to each project by person. This data is then used to produce the various labor reports that are distributed with the monthly accounting reports to program managers. The data also flows into the rest of the accounting system for the overall monthly accounting reports. That in a broad stroke, is the labor run. (By the way, it really helps if you turn your time sheet promptly on the first day of the month.)
Ledger Run:
The ledger run is the part of the system that handles the non-labor expenditures the Center makes each month. These costs come mostly from purchases that are made, but there are some direct charges that come from within the University without benefit of procurement action, and some charges like those from SSEC's stockroom that are included in this run before it is completed. Most of the data for the ledger run are obtained electronically from the University's accounting system. A text file is retrieved via FTP currently, although soon we will be getting this data via a query sent to the UW's data warehouse. This text file is imported into a database file, and becomes the monthly ledger file. Data is also loaded in from our in-house purchasing system, which provides requisition (req for short) numbers and project numbers. The system matches up req numbers from in-house against those that come with the expenditure data, and does a match up, automatically coding all req charges to the appropriate project number. There are also charges that come from credit card purchases. A lot of these are for airline tickets, and these charges can mostly be coded with a project number based on data supplied by the in-house travel system. After all the possible automatic matches, someone has to manually code all the remaining costs to a project number. Data is also added to this file from the stockroom system, the PC lease system, and manually from any of several other cost centers (mail room, UPS, copying, etc.,). After all these steps are completed, the monthly data file will contain all the line item expenditures that will be fed into the overall accounting run.
PC Lease Program:
Supplying desktop computers to employees in an environment such as ours is problematical. Most everyone works on multiple projects, most of which are funded in one way or another by the federal government. The feds are understandably unhappy if we buy a PC to put on an individual's desk when that person only works a fraction of his/her time on the particular project that paid for the computer. At the same time, the Center would go broke pretty quickly if we supplied computers for everyone's desktop "free" out of overhead funds. The solution we arrived at quite a few years ago involves buying the PCs with overhead funds, but recovering the costs on a prorated basis based on the individual's time sheet. Our theory is that if someone works 50% each on two different projects for a month (or pick any percent, any number of projects, as long as you end up with 100% of the time worked) then it's reasonable to charge 50% of a realistic monthly recovery amount to those projects for the "rental" of the computer. Auditors have accepted the concept, so that's the way we handle desktop computers in most cases. When a PC is purchased under this system (we call it affectionately the $/day plan), their name and the cost of the machine is put into a database. The PC lease system then, after the labor run has been finalized, finds the hours and projects for each person in the PC lease program, and automatically generates charges for the month based on the hours and projects charged in the Labor Run. For most desktop systems, the cost is recovered over two years, meaning that 1/24th of the cost will be charged by the PC lease system until the total cost has been recovered. The PC lease system automatically loads these charges into the monthly ledger file, to be used by the overall accounting system for reports and distribution of charges to projects. It also produces printed reports that show the total charges for an individual by project for the month, and another that shows the total charges to a project by person for the month. That's the $/day plan in a nutshell.
Accounting Run:
The accounting run basically just rolls all the separate inputs together to finalize all the data and do any necessary calculating that hasn't already been done by any of the individual sections. Overhead, for example, is calculated in this section of the program. There are several ongoing or cumulative files that are modified by the accounting run as well. The encumbrance system, which keeps track of open orders, and the amount of money that is committed by these outstanding transactions is modified by the accounting run to reflect the payments made for the month. There are several large databases that contain all of the accumulated data for ongoing projects by month. These allow program managers to look at their program through time rather than just a month at a time which is all that is shown in the standard management reports. Availability of this data is not automatic -- you need to ask, so if you're interested, just see me or Kelly. Finally, all the printed reports that are distributed that show contract and project spending summaries by cost categories are produced here.
Billing Run:
One of the side effects of running our own accounting system involving multiple UW fund/account combinations is that we have to shuffle a lot of money around. In addition to having a separate account set up in the University's accounting system for each grant or contract that we've been awarded, we have a sort of revolving general account which is used for most of our day to day expenditures. This is referred to as our "128" account, as opposed to most accounts for specific grants or contracts, which will have fund (actually fund-account) numbers like 144-DH69. The 128 account is a revolving account because we make most purchases and pay nearly all of our payroll out of this account, and then at the end of the month, we bill the appropriate 144 accounts for their share, based on the actual spending as determined by the overall accounting system. That's where the billing run comes in. When the accounting cycle has been completed in house, and our accounting folks are satisfied that everything has been properly accounted for that month, the billing run generates a text file which is submitted to the University's accounting system. The text file consists of dollar amounts debited against the long list of 144 accounts, with a single line crediting the total amount of the 144 charges back to our 128 account. That's why it's a revolving account -- we spend it down through the month via purchasing and payroll, and then refill it at the end of the month by the billing run output of our accounting system.
Small and Minority Business Reporting:
When we receive money from the federal government, which is where most of our money comes from in the Center, we are required to set goals in terms of spending a certain portion of those funds with minority and other small business ventures. A little social engineering by the purse strings. Anyway, to monitor our progress (or lack thereof) regarding these goals, we have to submit quarterly reports detailing where we spent the government's money. Since the accounting system stockpiles all this information by project number, and since most of the expenditures are made on our 128 fund and thus are invisible to the UW as to the source of the funds, we have to generate these reports here for submission to UW's Research Administration, which in turn reports the data to the federal government. This is only partially automated by the accounting system. One quarter's worth of data is gathered into a single file, and then the small business reporting system, which is in the Ledger Utilities section of the Ledger system is run. By putting in a contract number, all the charges for that contract are sorted by vendor and put into a separate text file. This file has to be edited extensively for submission, but at least all the data is there.
Stockroom Program:
The stockroom program is the latest addition to the Fox family of accounting programs. Although still under development, it is in full time use in the stockroom. This is the first large scale program in which Kelly has written a Windows based program (written in Visual FoxPro). Another new feature is the incorporation of bar-coding into the overall stockroom scheme. Once we get all the bugs exterminated, and all the planned features implemented, it should be very user friendly to stockroom personnel as well as customers. Tightly integrated with the rest of the accounting software, the charge-out data flows into the ledger file electronically, and appears in management reports with a great deal more detail than it did under the old mainframe system. Not having to re-key in the data and distribute the separate management reports are both great steps in the right direction. When completed, we expect it to be top notch.
Capital Inventory:
The capital inventory system is the oldest of the programs that we still use. The system was designed around the requirements of a long since retired inventory guru, and was coded by an equally long since relocated MACC (DoIT) programmer. The design wasn't great to begin with, and the coding was even less so. Kelly has made some major fixes to the program but it hasn't yet come to the top of our list of priorities for a complete rework. It's sorely needed, and I'm sure we'll get to it some day. Within the last couple of years we added bar coding capability to the system. If you look around the department you're sure to see the little 1" x 2" stickers with a bar code and description of the item. Addition of these bar codes has improved our ability to keep track of our capital inventory, and made it easier to search the database. If you ever have questions that a capital inventory database search can answer, see Mary Hensen. If she's not around, or is busy, Kelly or I can probably find something for you by a brute force command line search.
Summary:
It’s been interesting to help build these systems that pretty much keep the Business Office at SSEC afloat. I dare say there are those on SSEC’s staff who will be surprised at the volume of software development that comes out of this office. That’s part of what these pages are for - to let you know what we do down here, and to invite feedback. If you’re a user of our software, please feel free to comment, complain or whatever. That’s the only way we know when something’s broken or needs to be improved. Also, if you are a user of the output of any of these systems (and most everyone in the Center is in one way or another), now that you know where the technology comes from, if you have suggestions for improvements or additions that would be helpful, please let us know. I hope you’ve enjoyed a little history and a brief tour of software development on the third floor.
Return to the top of this page.
Return to Dave Allen's home
Last modified 3 June 1997
dave.allen@ssec.wisc.edu