Portfolio > 3b > 2. Capital Tracking
Search this site

3. An effective organizer with skills in:
(b) Allocating resources

3b2 Tracking and monitoring capital projects

Development Plan Portfolio Documentation
Develop a system for monitoring and reporting computer capital budgeting and expenditure. Document the tracking and monitoring system that was developed, and describe its effectiveness and impact on resource allocation.

Each year, various capital projects would be approved at Andrews, and the charges would all go against the capital account.  However, for the majority of the projects the expenditures for each project would be spread over multiple purchases, and it was becoming a nightmare to figure out if there were any funds remaining from the amount approved for a particular project.

So I developed a tracking system in Microsoft Access which we called CapTrak.

We had a screen for entering the budgets for each capital project, including carryover amounts - unspent amounts from the previous year - as well as requested and approved budget amounts.

We also had a screen for capturing all the purchase order details.  Actual ledger transactions were downloaded from Banner and matched up to the Purchase Orders in our system.  This was a feature sadly missing in the Banner system the way it had been implemented at Andrews.

We also had simple screens for maintaining the master file listings of ITS department or organization codes, project or activity codes, and a vendor listing.

This system allowed us to produce some very handy reports.  The Capital Budget report tracked total project spending for each project, showing carryover, the proposed and approved budget amounts and total available, the amounts committed and spent and the amount remaining.  It was an excellent report, and was useful during budget development as well as during the year as a management report.

The details of our capital expenditure were reported in the Tracking Equipment Purchases report.  This report showed us in a concise format the relevant details about each equipment purchase, and gave a summary across the bottom for each ITS department.

The actual transactions that were posted in Banner were quite confusing, as can be seen in the Capital Transactions report.  Most of the confusion was caused by transfers and correcting entries that cluttered the Banner reports and made reconciliation with our Tracking System virtually impossible.  But in spite of that, we managed to get our system to come out close enough to overlook the discrepancy, and so we received tremendous value from being to track capital projects and monitor expenditures.

To determine what projects should be budgeted and tracked, I used a spreadsheet.  In the 2002 budget spreadsheet, you can see that the projects initially proposed totaled $568,000, but even with carryover from the previous year, there was only $295,000 available.  In the next column, labeled AdmComp, the Administrative Computing Committee prioritized the projects so that the expenditure would be within the amount available.  In the next two columns, both headed 2002 Rebudget, there was a downward revision in the amount of new capital allocated for the year, as well as in the amount of carryover available.  In the budget that was finally approved, only $156,370 was available for capital projects.

For the 2003 capital budget, I made the spreadsheet interactive.  I added a column for importance or priority (headed "P").  The total initially requested was $552,000, which exceeded the available amount by 33$7,679.  When I took this to the Administrative Computing Committee, the first thing we did was assign an importance code to each project, where 1 was most important and 3 was least important.  Then we filled in the working column, which allowed us the option of approving part of a project, such as some of the switches requested on line 34.  Values appear in the AdmComp column only if the project has an importance code less than or equal to the Priority shown at the top of the column.  That value is a "1", but if you change it to a "2", then the next level of projects will be included.

This combination of prioritizing and approving projects along with the detailed tracking and reporting significantly reduced the tension that everyone felt (ITS and the accounting office) when we had to ask for large amounts of money to be released for approved capital projects.  The fear of overspending was allayed, and the risk of not carrying out approved projects because of uncertainty about funding was also dispelled.  It was very successful in improving the value of time spent researching and implementing projects as well as maximizing the value of funds approved for projects.


Return to 3(b) Allocating resources

Created: Sunday, February 20, 2000 05:33 PM 
Last Modified: Thursday, August 14, 2003 7:30 AM