Optimal Social Service Resource Planning
In the administrator's mind, there lies a nagging question. Are the agency’s important resources, its workers, assigned to service areas in a way that produces the best results for the county? There is a management tool that can distill service area planning assumptions into a practical and optimal solution in seconds. This tool is linear programming or LP for managers experienced in its use. If planning objective is to maximize social service time study or SSTS, and related revenue as in this article, LP will suggest the best assignment of SSTS workers among service areas of the agency. This management tool named SOLVER is an add-in app in Microsoft Excel.
Midwest Social Services administrator, Ernie Gumban greeted staff present in an hour’s session to develop an initial resource plan for SSTS services. Service area directors present include Chad O’Malley representing adult services, Pilar Jardin representing children services and Darcy Harkin representing related income maintenance services. Ernest stood up, “First a thank you assistant administrator, Lamar, for arranging this work session around everybody’s calendar. By the end of this one-hour session we will have developed an initial resource plan for the coming year. I will facilitate this session and share my experience with you on the use of LP as a planning tool.
An opportunity exists for the agency to achieve the highest level of SSTS revenue by astute assignment of our 934 workers to appropriate service areas. In order to realize the objective of maximizing revenue, we will establish a relationship of staffing level to revenue that can be calculated." Sheila, Midwest's planning manager spoke, "accounting has quarterly data that enables us to trace this relationship. A full-time equivalent worker makes available to us 480 hours per quarter. A utilization table also exists that gives the percent of worker hours in a service area spent on SSTS categories under the Department of Human Services. Further, these contact hours develop into qualified contacts or ‘hits’. These hits translate into dollar values unique for each service area. Conceptually, the following chart displays the algorithm converting worker hours to revenue.” Sheila opened her laptop and after several
minutes, pulled up a spreadsheet on the screen. A graph shows quarterly revenue for each hour of service delivered by each worker in a service area.
”I think I’ve got it," Darcy exclaimed after seeing the revenue per hour rates. “For a given set of staffing among service areas, Midwest’s total SSTS revenue for the quarter is the sum of the respective revenue per hour rates times the number of workers in those service areas. If for a quarter there are 100 worker hours in each service area, total revenue for that quarter will be $7,633.” Ernie added, “That is correct, Darcy, you have described the objective function which expresses what is being mathematically maximized and what management actions make it so.
The next step will be to define the constraints or policies that we want the app to follow as it drives revenue towards the maximum to our objective. These policies are related to budgets, the voice of client and stakeholders vision for Midwest's residents. To be effective the constraints must have quantifiable relationships with elements of the objective function, i.e. staffing and the rates.
Chad, Pilar and Darcy, the capacity and need projections you have brought into this initial planning session enabled us to draw the following policies:
The budget this coming year requires that we keep our workers at the previous level of 934 social workers or 467,000 hours per quarter.
While we have service areas that are not SSTS revenue generators, they are either the foundation of Midwest’s service delivery or are important sources of other revenue. The lowest staffing level acceptable for these service areas is CMH (7,750 hours); CP (139,500 hours); CYF (10,000 hours); OHP (4,250 hours) and WS (7,500).
ABH, ADS, SINT and OSS service areas are better SSTS revenue generators but any additional clients will have a lower intensity of need making them fall outside of our scope of services. The most staffing level for these service areas is 81,400 hours; 129,250 hours; 41,800 hours and 25,850 hours; respectively.”
Ernie entered the constraints into the SOLVER template that he had set up previously in Microsoft Excel. The table below shows the best resource plan of worker-hours (in green) resulting in the best SSTS revenue as constrained by expressed policies. This solution gave a quarterly revenue of $4,904,923.
At the end of the session, Pilar made this comment. "Thank you, Ernie, for sharing your experience in linear programming. I can think of several uses of this planning tool or approach not only in human services and public health but also in public works, public safety, and continuous improvement projects."
By Mgmtlaboratory.com staff and Noel Jagolino, contributing management consultant. 2019