Problem

A Municipal Agency managed $350 million worth of cash for 75 different fund accounts by holding an average of 90 short term investments in US Treasury and high quality corporate securities. Accomplishing this with a variety manual systems, Excel spreadsheets, and a proprietary database application no longer supported by its vendor had become impractical and highly vulnerable to error and fraud. Each new bond issue and federal regulation brought an additional set of record and reporting requirements, usually resulting in an additional Excel spreadsheet. None of the commercially available treasury software applications had all the features the agency needed, so it decided to build its own.

Solution

Rusty LaFerney developed a Microsoft Office solution that integrates Access and Excel into a multi-user network application. Although it appears to be highly customized, the integrated application is built totally with form tools and VBA included in Access and Excel, plus the Analysis Toolpak. The application generates 11 reports for any date or time period, historical or future.

Results

The Agency now knows to the penny the cash flow that each of its investments has and will generate daily for each of its fund accounts. Individual reports that used to prove difficult to prepare now run accurately in seconds. Additional earnings from keeping all of its cash invested rather than sitting idle is estimated to be $250,000 per year. Errors and omissions due to carelessness have been eliminated. Ad hoc requests for historical data that used to be unavailable are now filled quickly and accurately, which greatly improves tracing and auditing of these complex investments.