HOW MUCH DO SERVICES DELIVERED BY YOUR AGENCY COST? PART III
The price question is rarely offered to or asked by recipients of human services. For public service agencies and non-profits, service cost has to be estimated to complete proposals for funding, budget development, service planning, operational planning, and continuous improvement. These three articles introduce the concepts comprising a simple but effective cost information system. PART I argued why agency cost to deliver service or the productivity of the delivery process is the ratio of resources used measured in dollars to a measure of a unit of output. PART II will delve into the how of data collection with the objective of completing a “flat file” in Excel from which two service cost statements are generated. PART III summarizes the method of updating an Excel template with contents from the flat file.
At the end of a reporting period, the service cost reporting template comprised of seven Excel worksheets activates. The conceptual diagram above arrays how data and information are presented on each worksheet. The diagram below presents the hierarchy, as well as the information content of each of the seven worksheets. Service cost statements are copied from the top two of these worksheets and pasted on the management report to the administrator. Once the last worksheet has been updated with pay period end employee-service category-hours, the calculation by embedded formulas cascades towards the top worksheets. Identification of employees in the service unit populates the rows while service and administrative categories label the columns of the last five worksheets.
The workings involved in each worksheet summarizes as follows:
1. Employees-Service/Administrative Categories-Hours. A pivot table of pay period data from the payroll system is pasted on this worksheet to initiate the update the cost statements. This is also the worksheet that receives any new employee complement or changes in service categories.
2. Total Hours for Service/Administrative Categories. This worksheet is where the first levels of calculations take place. The sums of time spent on overhead activities or administrative categories are found here, too. Management reports may by exception, only present significant categories and groups of categories. Total time spent on these service categories are also presented here. Changes in hourly salary and benefits for the employee are included in this worksheet.
3. Distribution of Vacation, Medical and Holiday Hours to Service Categories. The portion of an employee’s total vacation, medical and holiday hours allocated to a service category is proportional to the ratio of that employee’s time on that service category and the sum of time the employee spent in all service categories. Unit activities such as meetings and training are included in the divisor.
4. Distribution of Total Training and Unit Administrative Hours to Service Categories. The distribution formula is similar to that embedded in the previous worksheet. Unit training and administrative time are allocated proportionally to service by the ratio of the time the employee spent on these administrative categories over the total of the time spent on the service, training and unit administrative categories.
5. Update of Service Categories’ Output Measures. An output measure is one of the valuable features in a service process control chart. These charts are regularly updated providing an intuitive source of management information.
6. Reporting Period Service Hours per Unit Ratios. The picture below demonstrates the calculated hourly and expense ratios per unit of output measures for services A1 to A6.
7. Reporting Period Total Service Hours and Total Expenses. The total hours and expenses for services A1 to A6 are presented in the picture below.
The procedure demonstrated in the three articles does not represent a service cost reporting practice in a specific public service agency but rather a fusion of concepts observed among public and private organizations. The reader is encouraged to email any questions to contact@mgmtlaboratory.com. WWW.mgmtlaboratory.com offers free online assistance to public service organizations in developing similar but specific service cost reporting template. Please contact staff via the email address above.
By Noel Jagolino, contributing management consultant
Mgmtlaboratory.com 2018