Clackamas County Technology Services (TS) manages a large array of technologies and services for many diverse departments and agencies. Technology Services also has a variety of funding sources and models ranging from “General Funding”, direct bill for services, indirect billing, grants and a large allocated component. This allocation is spread across the utilization of services based on several factors. For the most part, this allocation covers the overall enterprise infrastructure and support services such as network, servers, operations, call center, technicians, licensing, storage and equipment (PC, printers, etc.) utilized by the County. As with any technical support organization with an allocated cost recovery methodology, this process can be confusing, hard to manage, difficult to track, expensive to maintain, contentious in tough budget times, hard to align with technology utilization and goals, and difficult overall for departments to effectively budget for or even control their own utilization. Regardless of the system implemented, it was a management challenge to maintain a balance between simple but inequitable and fair but complex — especially when other factors such as grant restrictions, dedicated funding and politics are factored in. Clackamas County recently initiated a goal to both streamline the overall allocation system and to ensure the process was transparent to both internal departments and external scrutiny.
Previous allocation methodologies used by TS where antiquated, inflexible, unable to equitably correlate technical service utilization to cost recovery metrics, difficult to explain and worst of all did not give the user departments any ability to accurately track utilization for budget analysis or forecasting. To solve these problems TS decided to redesign the whole allocation methodology and process. Some of the concerns and tenants considered in the design of the new allocation methodology included:
- Must be integrated with the appropriate sources of real time, accurate allocation data.
- Needed to be simple to maintain, update and utilize.
- Needed to balance metrics between the overly complex (such as network packets or email counts) and the overly generalized (just PCs or flat rates) so the system is fair but efficient.
- Departments needed a simple method to monitor utilization and estimate costs.
- Departments needed to be able to utilize the system to make business decisions on the use of technology relative the costs.
- Must be able to meet all auditor and governance regulations such as grant constraints, etc.
- Must be flexible for changes in metrics, technologies and politics.
- Metrics need to be sampled more often than once a year to account for seasonal adjustments.
We formed an allocation committee with TS, Finance and departmental staff to develop the new model based on the above requirements. From this, TS designed and developed a new methodology for the tracking, calculation and reporting of allocations. This required 3 components: a new system of flexible metrics, a new calculation process and new reporting capability. During the process of design and development, we kept the County Auditor involved to ensure the process was fiscally correct.
To meet the tenant of balanced metrics that are fair, flexible and measurable — while not being too complex, expensive or confusing — TS reviewed numerous models and examples before settling on what Clackamas County believes is a practical list of metrics. During analysis, it became evident that even a fair mix of allocation metrics didn’t realistically reflect utilization. For example, a printer could be networked or directly connected to a PC – both are printers but require a much different level of support. Therefore, along with a new array of metrics, TS also designed a tier approach for each metric to better match the utilization. These tiers allowed the metrics to be efficiently tracked, and based on specific utilization; tiers would be a percentage of the costs for the base tier. Therefore if a department had ten printers — five are networked Tier 1 (100% rate) and five are direct connect Tier 2 (50% rate) — the allocation value would be 7.5 printers of the full rate for printers. The current metrics and tiers utilized are:
- Computing Device (PC, Laptop, tablet)
- Mobile / Remote
- CCTV Cameras
- Billable Staff
- Special Fees
Tier 1 Primary desktop PC or laptop 100% Rate Tier 2 2nd device for a person, usually mobile device 50% Rate Tier 3 Lab or public kiosks 25% Rate Tier 4 Prorated for special non-call center support 70% Rate
Tier 1 Networked printer / print server 100% Rate Tier 2 “Slaved” connected printer / labeler 50% Rate Tier 3 All-in-one copier or plotter 150% Rate
Tier 1 All scanners 100% Rate
Tier 1 Fully supported, normal Active Directory (AD) 100% Rate Tier 2 Adjusted for local support, full AD 80% Rate Tier 3 Service accounts for special applications, etc. 50% Rate
Tier 1 Web based Email (OWA) only 25% Rate Tier 2 Mobile Exchange – persistent connection 50% Rate Tier 3 Full mobile/remote access, applications 100% Rate Tier 4 VPN or specialized remote support 150% Rate
Tier 1 Fully supported cameras and storage 100% Rate Tier 2 Maintenance only, support on T&M 25% Rate
Tier 1 All billable project staff as required Hourly Rate
Tier 1 Special direct billed back fees, full rate 100% Rate of actual fee Tier 2 Subsidized fees for special projects Variable rate
To load, store and process the metrics a new database was developed. The metrics are loaded from the various source systems into the database on a quarterly, instead of annual, basis. This allows for seasonal fluctuations to be accounted for as well as prevent departmentsfrom using the annual sampling as a way to “hide” equipment based on procurement dates. The allocation loads are done first as a draft for departments to run reports to confirm accuracy, then two weeks later as the final. This process allows departments and TS to maintain an accurate count metric that is easily accessible by departments and allows TS to plan technology support around utilization that can be accurately projected based on historical trending. After a year of quarterly samples, an annual average can be calculated that is then used to generate the actual annual allocation. To generate this annual allocation, a sophisticated spreadsheet is utilized that has all TS expenses, allocated metric annual counts from the database and all other required information such as capital expenses, staffing, grant information, revenue calculations and allocation rate calculations (that utilize the metric counts to determine rates) to build an overall technology budget. This spreadsheet is dynamically created so all data is linked together for accurate up to date results. Once the data is loaded, the spreadsheet generates a final allocation that is then sent to the budget department for processing into the annual County budget. Departments are able to view their final allocation and utilize this information in budget preparation. This final allocation should also match departmental estimates based on the quarterly reports available from the allocation database thus confirming their technology utilization calculations. This provides a year long, open and manageable allocation system for budget and technology utilization monitoring.
To allow departments the ability to efficiently track and manage their technology utilization, as well as project their allocations, they needed an easy reporting system. In conjunction with the allocation database, a web accessible reporting system was developed that allows departments to monitor their utilization, check for accuracy and extract the information as required. The report menu allows for detailed reports for each metric, historical information related to the rates of the metrics (for trending analysis), quarterly summary reports (for budget tracking) and overall annual allocation estimates. This reporting system has been a major success for the departments as there are no longer any allocation “surprises” and departments now have the ability to make business decisions around the use of the technology.
Even with the success of the new Technology Allocation System, there is always room to improve. TS formed an Allocation Committee to keep a high level of communications related to technology expenses, coordinate training and provide a forum for improvement. Next steps include a review of additional metrics such as AV equipment and email accounts and new tier levels for on-demand mobile utilization, etc. The flexibility of the allocation system allows for additional metrics without major re-development. TS is also working with the Finance Department on the addition of more reporting to assist departments in budgeting for things such as A-87 grant reporting. TS is also looking into adapting the system for other internal service departments to apply to their allocation methodologies.
Services & Benefits
The new TS Allocation has been a great success, both for TS and the County departments. Some of the benefits so far are:
- Open and easy to utilize reporting system that keeps departments informed of their technology utilization and inventories, both for budget and planning purposes.
- Reviewed by financial auditors and external allocation consultant and found to be a very efficient and effective model that meets the auditing requirements of the County.
- TS has more accurate trending information for designing future technical services and budgets based on technology utilization.
- Benchmark for other Clackamas County departments to use as an allocation methodology and process to improve other services and charges. Other external agencies have also expressed interest in using the system as a basis for their own.
- Accurate and timely system based on actual metrics loaded from the source systems.
- Open and transparent allocation system based on actual technology costs and distributed via fair, equitable and measurable system for both departmental and public analysis.
- Multiple, yet simple to manage, metrics tied to actual utilization
- Variable tiers to tune equitable allocation rates based on variety of utilization
- Annual review of metrics, tiers and overall system to continually improve system.
- Efficient and relatively easy system to maintain without need for dedicated business staff to maintain allocation and reporting.
- Overall complaints of confusing allocations or lack of information have greatly diminished, allows more pertinent discussions around future of allocations.
The technology utilized for the new allocation system is very simple and easy to maintain:
- The allocation process is controlled by an large integrated Microsoft Excel Spreadsheet
- The reporting system uses a Microsoft SQL database storing all the detail metrics and data
- Reports are generated utilizing the Microsoft SQL Reporting System
- Source data sets include (and more in development)
- Active Directory
- PeopleSoft ERP
- In house equipment inventory system
- Genetec CCTV management system
- LanDesk Management System
- MobileIron Mobile Device Management System
- Allocation sample spreadsheet
- Allocation reports
- Allocation website (screenshot)
- Allocation presentation
Share this page