Agency PRS Prototype
After CDBG changed requirements for counting statistics, the opportunity came for the Agency to redesign their process for claiming and and for providing statistics to the funder. The older method had been very thorough, well planned out, coordinated, and orchestrated, but required a lot of manual tallying and passing of spreadsheets back and forth between the centers and the central office. Under the old system each center sent in a monthly spreadsheet to the central business office with information for BOTH current enrollments and new enrollments. The business office counted, totaled and combined all this information into one spreadsheet for ALL the centers in order to track statistics for the monthly Claim – in particular making sure there were no duplicates for the funding period, according to funder specifications. I believe each month, new enrollments from the previous month had to be moved up into current enrollments and then these spreadsheets were sent back to the centers for the next month’s cycle.
With the new spreadsheet, the centers will still need to send in the information on new enrollments each month – which are fairly small except at the beginning of the cycle when you can count everyone all over again. Central Business office will still accumulate new enrollments into one spreadsheet. However there will be no more passing back and forth of current and new enrollees. Also, the new spread sheet automates and looks up more of the calculations and makes use of array arithmetic in excel to track demographics, etc., so that there is no need for manual tallying (God bless the web - I went out looking for varying ways to count and sum and found a great site explaining array arithmetic!). Or for maintaining separate sections for new enrollments and current enrollments. Also the new spreadsheet eliminates some of the data collection – in the old system the Agency was collecting more information than needed or used. Perhaps the older system applied to more than just the funder's claims, but this seemed to be no longer the case – because current staff did not know what some of the information was used for. Following is the model of the new spreadsheet, which the executive director piloted for an upcoming CDBG monitor.
The
record keeping requirements changed slightly. In the old spreadsheet I
believe the agency just counted the child. But CDBG is now wanting to
count EACH FAMILY MEMBER in the unduplicated number of clients served.
Excel’s array arithmetic will sort out the statistics that CDBG
requires and summarize/count them automatically. With
the new method, we still enter each child on a separate line, but we only
enter the household number, household demographics, and household salary
info once per family and that information now includes every
member of the household, not just the child. To be able to sort and keep
the all the children in a family together, we do have to keep entering
the Head of Household name for each child. Family income has been changed
to monthly instead of weekly – then the spreadsheet calculates the
annual income and looks up the Area Median Income Category according to
annual income and household size and gives a summary count of those categories
as well.
In the future I hope to play with Excel Pivot Tables to see what Excel can do for us!