financial reports for management tracking human services program allocations
TRANSCRIPT
FINANCIAL REPORTS FOR MANAGEMENTTracking Human Services Program Allocations
2
One approach… maybe another is better?
HIGH-LEVEL OVERVIEWProgram Expenditures, Projections & Related Data
4
Major Allocations SummarySFY15 as of 2/28/2015
SFY15 as of 2/28/2015 67% SFY Expended
Block AllocationAllocation
AmountYear-to-date expenditures
% ExpendedYear-end
projectionVariance
(+/-)County
Administration $6,800,177 6,489,871$ 95.4% $9,638,764 ($2,838,587)Child Care 6,108,554 4,020,623 65.8% 6,128,475 ($19,920)
Child Welfare 20,619,037 13,073,045 63.4% 19,954,025 $665,012Core Services 2,752,978 1,932,566 70.2% 2,853,323 ($100,345)
Colorado Works 12,228,210 8,920,826 73.0% 13,972,774 ($1,744,564)Total $48,508,957 $34,436,929 71.0% $52,547,361 ($4,038,404)
5
Major Capped Allocations
County Administra-tion
Child Care Child Welfare Core Services Colorado Works$0
$5,000,000
$10,000,000
$15,000,000
$20,000,000
$4,556,118 $4,092,731
$13,814,755
$1,844,495
$8,192,901
Allocation Amount Year-to-date expenditures Year-end projection Allocation (YTD)
6
County AdministrationMonthly Total
Alloc. Alloc.566,681$ 6,800,177$
Benefit Eligibility Common FA HCPF HCPF Net % YTD YTDSalaries, Support Fraud Regular Enhanced Expend. of Expend. Alloc.
Benefits & Cost Alloc.Operating
July 398,654$ 163,774$ 13,189$ 90,799$ 214,208$ 880,623$ 13.0% 880,623$ $566,681August 459,550 185,786 17,151 103,510 253,104 1,019,101 15.0% 1,899,724 1,133,363September 326,995 172,838 11,400 92,193 181,544 784,971 11.5% 2,684,695 1,700,044October 323,954 148,978 9,507 103,667 193,831 779,937 11.5% 3,464,631 2,266,726November 337,144 (33,564) 9,442 165,074 194,271 672,367 9.9% 4,136,999 2,833,407December 354,873 94,909 9,371 98,819 151,691 709,664 10.4% 4,846,662 3,400,088January 454,414 92,446 16,782 118,859 243,226 925,728 13.6% 5,772,390 3,966,770February 323,453 99,575 12,649 110,038 171,765 717,480 10.6% 6,489,871 4,533,451March 337,083 87,195 13,235 96,610 185,686 719,812 10.6% 7,209,682 5,100,133April 348,638 97,001 14,020 102,100 197,451 759,203 11.2% 7,968,885 5,666,814May 340,410 93,079 13,235 96,610 219,020 762,354 11.2% 8,731,238 6,233,495June 393,625 185,035 13,235 96,610 219,020 907,525 13.3% 9,638,764 6,800,177TOTAL 4,398,794$ 1,387,051$ 153,217$ 1,274,889$ 2,424,817$ 9,638,764$ 141.7%
% Spent 6,800,177$ 2/28/2015 9,638,764
County Admin $3,633,348 98.2% (2,838,587)$ HCPF Regular $893,208 86.5% (329,089) HCPF Enhanced $2,273,621 63.0% (3,167,676)Total $6,800,177 1,045,333
0(2,122,343)$
SFY15 ProjectedAllocation
Allocation Detail SFY15 ExpendituresNet (Over)/Under
Adult Protection DeficitTotal
Federal Pass Through (33%)Surplus Distribution
Net (Over)/Under
7
Food Assistance
Feb-1
3
Mar-
13
Apr-13
May
-13
Jun-1
3Ju
l-13
Aug-13
Sep-1
3
Oct-13
Nov-13
Dec-1
3
Jan-1
4
Feb-1
4
Mar-
14
Apr-14
May
-14
Jun-1
4Ju
l-14
Aug-14
Sep-1
4
Oct-14
Nov-14
Dec-1
4
Jan-1
5
Feb-1
5$4.2M
$4.4M
$4.6M
$4.8M
$5.0M
$5.2M
$5.4M
17,000
18,000
19,000
Food AssistanceBenefits & Caseload
Benefits $ FA Caseload Linear (FA Caseload)
8
Medical AssistanceSe
p-13
Oct
-13
Nov
-13
Dec
-13
Jan-
14
Feb-
14
Mar
-14
Apr
-14
May
-14
Jun-
14
Jul-1
4
Aug
-14
Sep-
14
Oct
-14
Nov
-14
Dec
-14
Jan-
15
Feb-
15
0
5,000
10,000
15,000
20,000
25,000
30,000
35,000
40,000
45,000
-
200
400
600
800
1,000
1,200
1,400
1,600
1,800
2,000
Applicationsand Ongoing Cases
Applications Ongoing Cases
9
Adult Protective ServicesMonthly Total Monthly Total
Alloc. Alloc. Alloc. Alloc.55,279$ 663,345$ 4,238$ 50,853$
Salaries, Allocated Net % YTD YTD Client % YTD YTDBenefits & Costs Expend. of Expend. Alloc. Benefits of Expend. Alloc.Operating Alloc. Alloc.
July 48,582$ 20,458$ 69,040$ 10.4% 69,040$ $55,279 July 4,660$ 0.7% 4,660$ $4,238August 70,245 23,278 93,523$ 14.1% 162,563 110,558 August 3,047 0.5% 7,707 8,475September 54,076 21,578 75,654$ 11.4% 238,217 165,836 September 2,612 0.4% 10,319 12,713October 55,306 20,936 76,243$ 11.5% 314,460 221,115 October 2,126 0.3% 12,445 16,951November 76,636 37,363 113,999$ 17.2% 428,459 276,394 November 2,678 0.4% 15,123 21,189December 57,722 21,440 79,162$ 11.9% 507,621 331,673 December 1,951 0.3% 17,074 25,426January 68,090 18,249 86,339$ 13.0% 593,960 386,952 January 1,626 0.2% 18,700 29,664February 58,608 19,537 78,145$ 11.8% 672,105 442,230 February 1,308 0.2% 20,009 33,902March 56,224 20,922 77,145$ 11.6% 749,251 497,509 March 4,000 0.6% 24,009 38,140April 58,576 22,098 80,675$ 12.2% 829,925 552,788 April 4,000 0.6% 28,009 42,377May 58,757 20,922 79,678$ 12.0% 909,603 608,067 May 4,000 0.6% 32,009 46,615June 61,910 20,922 82,831$ 12.5% 992,435 663,345 June 4,000 0.6% 36,009 50,853
TOTAL 724,732$ 267,703$ 992,435$ 149.6% TOTAL 36,009$ 5.4%
663,345$ 50,853$ 992,435 36,009
(329,089)$ 14,844$ 0 0
(329,089)$ 14,844$ Net (Over)/Under Net (Over)/Under
Expenditures ExpendituresNet (Over)/Under Net (Over)/Under
Surplus Distribution Surplus Distribution
SFY15 Projected SFY15 ProjectedAllocation Allocation
10
Adult Protective Services
Feb-14Mar-1
4Apr-1
4
May-14Jun-14
Jul-14
Aug-14Sep-14
Oct-14
Nov-14Dec-14
Jan-15Feb-15
60
70
80
90
100
110
120
130
140
150
160 Cases
Mandatory reporting legislation took effect July 1, 2014 as indicated by the red marker.
11
Other Benefit Payments
Aid to Home Old FoodNeedy Care Age Assistance
Disabled Allowance PensionFeb-14 108,469$ 70,497$ 940,833$ 4,738,449$ Mar-14 125,221 69,936 950,753 4,874,010 Apr-14 142,926 68,905 945,625 4,853,362 May-14 135,060 69,135 955,091 4,915,590 Jun-14 129,501 70,325 953,022 4,880,624 Jul-14 121,336 68,795 946,728 4,875,476 Aug-14 142,657 67,116 939,868 4,788,266 Sep-14 130,516 66,365 939,772 4,809,402 Oct-14 129,432 64,558 935,139 5,063,590 Nov-14 149,959 66,384 943,706 5,016,893 Dec-14 149,959 66,384 943,706 5,016,893 Jan-15 147,516 65,693 936,529 4,906,514 Feb-15 124,177 62,569 945,323 4,745,419
14.48%-11.25%
1.45%0.15%FA % change
Other Program Benefits Expenditures
Percentage change from February of last yearAND % changeHCA % changeOAP % change
12
Other Benefit Payments
Feb-
14
Mar-1
4
Apr-1
4
May-1
4
Jun-
14
Jul-1
4
Aug-1
4
Sep-
14
Oct-1
4
Nov-1
4
Dec-1
4
Jan-
15
Feb-
15 $920,000 $925,000 $930,000 $935,000 $940,000 $945,000 $950,000 $955,000 $960,000
Old Age Pension (OAP)
Feb-
14
Mar-1
4
Apr-1
4
May-1
4
Jun-
14
Jul-1
4
Aug-1
4
Sep-
14
Oct-1
4
Nov-1
4
Dec-1
4
Jan-
15
Feb-
15 $50,000
$70,000
$90,000
$110,000
$130,000
$150,000
$170,000
Aid to Needy Disabled (AND)
Feb-
14
Mar-1
4
Apr-1
4
May-1
4
Jun-
14
Jul-1
4
Aug-1
4
Sep-
14
Oct-1
4
Nov-1
4
Dec-1
4
Jan-
15
Feb-
15 $50,000
$55,000
$60,000
$65,000
$70,000
$75,000
$80,000
Home Care Allowance (HCA)
13
Child CareMonthly Total
Alloc. Alloc.459,899$ 5,518,783$
Salaries, Allocated Child Care Net % YTD YTDBenefits & Costs Subsidies Expend. of Expend. Alloc.Operating Alloc.
July 36,018$ 8,584$ 588,322$ 632,924$ 11.5% 632,924$ $459,899August 45,767 8,089 427,245 481,102$ 8.7% 1,114,026 919,797September 29,167 8,467 384,834 422,467 7.7% 1,536,493 1,379,696October 32,272 1,500 511,274 545,046 9.9% 2,081,538 1,839,594November 34,036 7,390 404,254 445,679 8.1% 2,527,218 2,299,493December 33,599 1,545 398,898 434,042 7.9% 2,961,260 2,759,391January 52,272 320 515,997 568,589 10.3% 3,529,849 3,219,290February 37,082 1,823 451,868 490,774 8.9% 4,020,623 3,679,189March 38,352 3,000 441,200 482,552 8.7% 4,503,175 4,139,087April 38,020 3,000 550,400 591,420 10.7% 5,094,595 4,598,986May 37,300 3,000 469,600 509,900 9.2% 5,604,495 5,058,884June 37,900 3,000 483,080 523,980 9.5% 6,128,475 5,518,783TOTAL 451,786$ 49,717$ 5,626,972$ 6,128,475$ 111.0%
5,518,783$ Allocation -HB14-1317 589,772$
6,128,475(19,920)$
0(19,920)$ Net (Over)/Under
Surplus Distribution
SFY15 ProjectedAllocation
ExpendituresNet (Over)/Under
14
Child Care
Feb-1
3
Mar-
13
Apr-13
May
-13
Jun-1
3Ju
l-13
Aug-13
Sep-1
3
Oct-13
Nov-13
Dec-1
3
Jan-1
4
Feb-1
4
Mar-
14
Apr-14
May
-14
Jun-1
4Ju
l-14
Aug-14
Sep-1
4
Oct-14
Nov-14
Dec-1
4
Jan-1
5
Feb-1
5$0.0M
$0.2M
$0.4M
$0.6M
$0.8M
-
200
400
600
800
1,000
1,200
1,400
1,600
Provider Payments & Caseload
Child Care $ Linear (Child Care $) Active Children Total Children
15
Child Care
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec $-
$20,000.00
$40,000.00
$60,000.00
$80,000.00
$100,000.00
$120,000.00
$140,000.00
Child Care – Cost Per Week
2015 2014 2013 2012
16
Child WelfareMonthly Total
Alloc. Alloc.1,718,253$ 20,619,037$
80/20 Block CW 100% Subsidized Out Residential CHRP CW Net % YTD YTDSalaries, Salaries, Adoption of Mental Related Expend. of Expend. Alloc.
Benefits & Benefits & Home Health Child Alloc.Operating Operating (PRTF) Care
July 741,097$ 5,237$ 252,510$ 298,277 19,561$ 9,851$ 19,658$ 1,346,192$ 6.5% 1,346,192$ $1,718,253August 1,038,742 6,643 260,444 302,799 17,797 8,869 18,936 1,654,231 8.0% 3,000,422 3,436,506September 885,975 4,720 260,299 308,116 16,491 9,895 16,238 1,501,734 7.3% 4,502,157 5,154,759October 789,766 6,824 253,477 336,448 13,895 7,529 40,620 1,448,559 7.0% 5,950,716 6,873,012November 990,166 8,090 258,438 337,392 14,369 8,991 45,623 1,663,071 8.1% 7,613,787 8,591,266December 1,650,132 6,790 253,010 327,595 7,483 12,855 38,933 2,296,797 11.1% 9,910,584 10,309,519January 974,987 9,413 263,367 343,470 25,423 12,298 60,951 1,689,909 8.2% 11,600,493 12,027,772February 879,323 7,628 261,930 242,456 21,070 7,893 52,251 1,472,551 7.1% 13,073,045 13,746,025March 875,979 7,129 258,460 299,620 19,848 11,407 37,779 1,510,222 7.3% 14,583,267 15,464,278April 913,181 7,319 258,460 278,707 19,848 11,407 37,795 1,526,717 7.4% 16,109,984 17,182,531May 891,855 7,129 258,460 325,475 19,848 11,407 37,795 1,551,969 7.5% 17,661,953 18,900,784June 47,874 1,637,221 258,460 279,468 19,848 11,407 37,795 2,292,072 11.1% 19,954,025 20,619,037TOTAL 10,679,079$ 1,714,143$ 3,097,317$ 3,679,823$ 215,480$ 123,809$ 444,374$ 19,954,025$ 96.8%
20,619,037$ 266,005$ 19,954,025
665,012$ 0
665,012$ Surplus Distribution
Net (Over)/Under
SFY15 Estimated CMP Savings SFY15 ProjectedAllocation
ExpendituresNet (Over)/Under
17
Child Welfare
A provider rate increase and an increase in OOH placements led to an higher OOH placement costs in late 2014. The drop OOH placement costs in Feb 2015 is related to a new funding source.
Feb-
13
Mar-1
3
Apr-1
3
May-1
3
Jun-
13
Jul-1
3
Aug-1
3
Sep-
13
Oct-1
3
Nov-1
3
Dec-1
3
Jan-
14
Feb-
14
Mar-1
4
Apr-1
4
May-1
4
Jun-
14
Jul-1
4
Aug-1
4
Sep-
14
Oct-1
4
Nov-1
4
Dec-1
4
Jan-
15
Feb-
15$0.0M
$0.1M
$0.2M
$0.3M
$0.4M
$0.5M
$0.6M
-
20
40
60
80
100
120 OOH Placements
OOH Costs $ Total OOH Placements
18
Subsidized Adoptions
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 -
10
20
30
40
50
60
Count of Children by Age
2015 2016 2017 $2,000,000
$2,200,000
$2,400,000
$2,600,000
$2,800,000
$3,000,000
$3,200,000
$3,400,000
Est. Adoption Subsidies 2015-2017
As of 2/28/15
19
Core ServicesMonthly Total
Alloc. Alloc.229,415$ 2,752,978$
100% 80/20 Core Core Core Core Net % YTD YTDSalaries, Salaries, Services Services Services Services Expend. of Expend. Alloc.
Benefits & Benefits & 80/20 ADAD MH FICF 100% Alloc.Operating Operating FICF
July 100,130$ 3,372$ 92,736$ 19,745$ 31,664$ 6,964$ 254,611$ 9.2% 254,611$ $229,415August 139,384 3,819 88,208 4,638 34,001 8,111 278,161 10.1% 532,772 458,830September 94,921 3,362 87,023 19,494 41,365 2,782 248,947 9.0% 781,720 688,245October 92,839 2,941 91,975 11,398 43,104 3,618 245,874 8.9% 1,027,594 917,659November 95,440 2,875 102,241 27,598 45,405 3,507 277,066 10.1% 1,304,659 1,147,074December (30,114) 2,288 106,074 18,490 34,137 3,235 134,110 4.9% 1,438,770 1,376,489January 91,766 4,138 120,628 4,047 35,785 2,793 259,157 9.4% 1,697,927 1,605,904February 67,924 2,449 128,203 5,012 30,060 990 234,639 8.5% 1,932,566 1,835,319March 68,788 2,635 101,367 14,615 37,692 4,231 229,329 8.3% 2,161,894 2,064,734April 71,423 3,058 101,367 14,615 37,692 4,231 232,387 8.4% 2,394,281 2,294,148May 68,788 2,827 101,367 14,615 37,692 4,231 229,521 8.3% 2,623,802 2,523,563June (107,317) 178,932 101,367 14,615 37,692 4,231 229,521 8.3% 2,853,323 2,752,978TOTAL 753,972$ 212,695$ 1,222,557$ 168,885$ 446,290$ 48,924$ 2,853,323$ 103.6%
2,752,978$ 2,853,323(100,345)$
0(100,345)$ Net (Over)/Under
Surplus Distribution
SFY15 ProjectedAllocation
ExpendituresNet (Over)/Under
20
Core Services
Feb-
13
Mar-1
3
Apr-1
3
May-1
3
Jun-
13
Jul-1
3
Aug-1
3
Sep-
13
Oct-1
3
Nov-1
3
Dec-1
3
Jan-
14
Feb-
14
Mar-1
4
Apr-1
4
May-1
4
Jun-
14
Jul-1
4
Aug-1
4
Sep-
14
Oct-1
4
Nov-1
4
Dec-1
4
Jan-
15
Feb-
15 -
20
40
60
80
100
120
140
160
180
200
Youth Services Caseload
Youth Services Caseload (avg daily census) Linear (Youth Services Caseload (avg daily census))
21
Core Services 80/20
Home
Based
Life
Skill
s
Func
. Fam
ily T
hera
py
Day T
reat
men
t
Domes
tic V
iole
nce
Multi-
Syst
emic
Direct
Lin
k $-
$100,000
$200,000
$300,000
$400,000
$500,000
$600,000
$700,000
Expenditures by Service Type
Est. SFY15 SFY14
22
Colorado WorksMonthly Total
Alloc. Alloc.1,019,018$ 12,228,210$
Salaries, Cost Contracts Basic Diversion Net % YTD YTDBenefits, Alloc. Cash & Other Expend. of Expend. Alloc.
Operating & Plan Assist. Sup. Service Alloc.Allocated
July 133,225$ -$ 1,600 636,505$ 51,430$ 822,760$ 6.7% 822,760$ $1,019,018August 155,515 - 443,222 640,421 46,056 1,285,214 10.5% 2,107,974 2,038,035September 121,976 61,839 253,004 644,378 41,650 1,122,847 9.2% 3,230,821 3,057,053October 91,109 - 263,878 665,391 45,137 1,065,515 8.7% 4,296,336 4,076,070November 126,193 - 449,485 651,458 48,177 1,275,313 10.4% 5,571,649 5,095,088December 97,815 42,725 576,435 652,617 39,730 1,409,322 11.5% 6,980,972 6,114,105January 109,383 - 31,980 634,136 37,670 813,169 6.6% 7,794,140 7,133,123February 117,359 - 354,752 612,143 42,431 1,126,685 9.2% 8,920,826 8,152,140March 95,339 60,000 386,000 614,880 41,695 1,197,914 9.8% 10,118,739 9,171,158April 109,670 - 386,000 614,880 41,700 1,152,250 9.4% 11,270,989 10,190,175May 102,660 - 386,000 602,880 41,700 1,133,240 9.3% 12,404,229 11,209,193June 101,870 60,000 762,000 602,880 41,795 1,568,545 12.8% 13,972,774 12,228,210TOTAL 1,362,113$ 224,565$ 4,294,356$ 7,572,569$ 519,171$ 13,972,774$ 114.3%
12,228,210$ 13,972,774 (1,744,564)$ 4,230,202 2,485,638$
Reserves at Start of SFY15Reserves at Close of SFY15
Net (Over)/Under
SFY15 ProjectedAllocation
Expenditures
23
Colorado Works
11%1%
27%
58%
4%
Salaries, Benefits, Allocated CostsCost AllocationContractsBCASS/Diversion
Expenditures YTD (2/28/15) Total Expenditures SFY14
11%2%
29%
55%
3%
Salaries, Benefits, Allocated CostsCost AllocationContractsBCASS/Diversion
Efforts are underway to reduce expenditures for contracted services.
24
Colorado Works
Feb-1
3
Mar-
13
Apr-13
May
-13
Jun-1
3Ju
l-13
Aug-13
Sep-1
3
Oct-13
Nov-13
Dec-1
3
Jan-1
4
Feb-1
4
Mar-
14
Apr-14
May
-14
Jun-1
4Ju
l-14
Aug-14
Sep-1
4
Oct-14
Nov-14
Dec-1
4
Jan-1
5
Feb-1
5$0.3M
$0.4M
$0.5M
$0.6M
$0.7M
$0.8M
$0.9M
1,300
1,400
1,500
1,600
1,700
1,800
1,900
EBT Payments & Caseload
Total Benefits $ Linear (Total Benefits $) BCA $Caseload Linear (Caseload)
25
Child Support ServicesSalaries & Operating Net YTD Federal State IV-D TANF IV-DBenefits Expend. Expend. Expend. Incentives* Incentives Reimb Rev Retained
January 311,840$ 63,208$ 375,049$ 375,049$ -$ -$ 247,529$ 20,536$ February 157,420 114,839 272,259 647,307 62,312 37,813 179,691 35,399 March 223,529 96,853 320,382 967,690 - - 211,452 47,059 April 233,333 97,676 331,010 1,298,700 - - 218,466 37,647 May 223,529 97,299 320,828 1,619,528 - - 211,747 33,333 June 156,863 131,443 288,305 1,907,833 66,667 82,353 190,282 19,608 July 312,941 111,294 424,235 2,332,069 - - 279,995 18,431 August 223,529 101,000 324,529 2,656,598 - - 214,189 18,431 September 156,863 101,780 258,643 2,915,241 66,667 66,667 170,704 18,431 October 223,529 100,808 324,337 3,239,578 - - 214,063 18,431 November 223,529 99,912 323,441 3,563,019 - - 213,471 18,431 December 156,863 107,132 263,995 3,827,014 66,667 20,784 174,237 18,431 TOTAL 2,603,770$ 1,223,245$ 3,827,014$ 262,312$ 207,617$ 2,525,826$ 304,170$
207,617 210,561 2,525,826 2,478,498
304,170 300,232 3,037,613$ 2,989,291$ 3,827,014 3,757,416 (789,401)$ (768,125)$
* Federal incentives offset program costs in the period they are received.
Total expenditures (projected) Total expendituresEst County Share: County Share:
IV-D Reimbursement IV-D ReimbursementTANF IV-D Retained TANF IV-D Retained
Total Revenue: Total Revenue:
State Incentives State Incentives2015 Projected: 2014 Final:
26
Child Support Services
Feb-
13
Apr-1
3
Jun-
13
Aug-1
3
Oct-1
3
Dec-1
3
Feb-
14
Apr-1
4
Jun-
14
Aug-1
4
Oct-1
4
Dec-1
4
Feb-
1510,000
10,200
10,400
10,600
10,800
11,000
11,200
11,400
11,600
Enforcing Caseload
Feb-
13
Apr-1
3
Jun-
13
Aug-1
3
Oct-1
3
Dec-1
3
Feb-
14
Apr-1
4
Jun-
14
Aug-1
4
Oct-1
4
Dec-1
4
Feb-
1585%
86%
87%
88%
89%
90%
91%
92%
% Caseload w/Orders
27
Family & Adult ServicesIntake & Ongoing Cases
Family and Adult Services Intake Statistics Feb-14 Mar-14 Apr-14 May-14 Jun-14 Jul-14 Aug-14 Sep-14 Oct-14 Nov-14 Dec-14 Jan-15 Feb-15
% Change Feb-14 to
Feb-15% Change 12
mo AvgFood Assistance 1,780 1,355 1,638 1,797 1,402 1,622 1,720 1,337 918 951 721 895 893 -49.8% -35.4%Colorado Works 312 248 270 355 309 313 367 249 304 304 263 270 248 -20.4% -9.7%Adult Financial 234 269 325 345 255 301 303 273 240 258 255 232 205 -12.3% -16.5%Food Assistance (Expedited) 1,702 1,456 1,525 1,616 1,398 1,501 1,415 916 1,016 959 953 1,009 963 -43.4% -23.2%Medical Assistance 1,714 1,563 2,628 2,006 1,008 1,163 1,045 805 1,394 1,395 1,461 1,842 1,556 -9.2% 25.2%Child Care - - - - 49 54 53 96 65 58 39 42 67
Family and Adult Ongoing Caseloads Feb-14 Mar-14 Apr-14 May-14 Jun-14 Jul-14 Aug-14 Sep-14 Oct-14 Nov-14 Dec-14 Jan-15 Feb-15
% Change Feb-14 to
Feb-15% Change 12
mo AvgTotal 50,926 52,220 54,274 55,322 55,653 55,694 56,661 57,412 57,878 58,539 59,862 60,351 62,120 22.0% 10.5%Adult Financial Assistance 3,751 3,760 3,760 3,778 3,765 3,688 3,692 3,722 3,673 3,666 3,601 3,570 3,634 -3.1% -1.8%Adult Medical Assistance 0 0 0 0 0 0 0 0 0 0 0 0 0 0.0% 0.0%Colorado Works Ongoing Caseload 1,584 1,510 1,446 1,411 1,443 1,409 1,397 1,523 1,512 1,536 1,516 1,480 1,528 -3.6% 3.2%County Diversion 131 105 89 76 72 80 70 62 57 55 60 68 72 -44.9% -6.3%Expedited Food Assistance 69 71 74 84 74 82 106 183 98 123 58 37 100 45.5% 13.3%Food Assistance 17,958 18,357 18,630 18,782 18,514 17,709 17,909 17,660 17,681 17,815 18,029 18,074 18,728 4.3% 3.5%Medical Assistance 27,290 28,271 30,135 31,046 31,752 32,693 33,346 34,104 34,698 35,181 36,445 36,975 37,907 38.9% 16.1%Unduplicated 35,656 36,734 38,428 39,150 39,702 40,176 40,865 41,512 41,891 42,239 43,431 43,944 45,017 26.3% 11.7%
Adult Protective Services 110 115 108 112 107 88 108 127 127 130 119 113 127 15.1% 11.9%
Child Care (cases- unduplicated)Child Care Child Welfare 18 19 16 17 21 27 30 29 30 26 26 23 23 25.0% -2.9%Child Care TANF 219 214 207 213 233 237 258 248 255 248 286 252 276 26.3% 15.5%Child Care Low-Income 498 541 518 535 536 541 533 540 550 568 570 579 568 14.1% 4.8%
Child Support ServicesEnforcing caseload 10,989 10,938 10,948 10,918 10,873 10,898 10,888 10,894 10,917 10,917 10,866 10,868 10,855 -1.2% -0.5%Enforcing caseload with orders 9,837 9,828 9,822 9,809 9,773 9,749 9,744 9,752 9,730 9,730 9,683 9,694 9,680 -1.6% -0.8%
28
Child WelfareIntake & Ongoing Cases
Child Welfare Intake Statistics Feb-14 Mar-14 Apr-14 May-14 Jun-14 Jul-14 Aug-14 Sep-14 Oct-14 Nov-14 Dec-14 Jan-15 Feb-15
% Change Feb-14 to
Feb-15% Change 12
mo AvgAssessments Closed 95 41 93 98 102 87 79 118 86 104 88 113 93 -1.3% 1.6%Children in Founded Assessments 45 18 22 45 47 53 43 84 53 63 60 60 58 28.7% 17.2%Founded Assessments 23 18 22 23 24 27 18 40 25 28 25 27 30 32.7% 21.5%Inconclusive Assessments 30 16 31 35 33 30 23 38 29 34 27 39 32 7.0% 4.1%Unfounded Assessments 29 12 30 27 37 23 28 32 22 25 26 29 23 -20.3% -14.1%New Referrals 365 175 353 361 358 272 280 291 370 380 272 283 352 -3.6% 12.4%
Child Welfare Ongoing Caseloads Feb-14 Mar-14 Apr-14 May-14 Jun-14 Jul-14 Aug-14 Sep-14 Oct-14 Nov-14 Dec-14 Jan-15 Feb-15
% Change Feb-14 to
Feb-15% Change 12
mo AvgOngoing
Intact Families (Avg. Daily Census) 130 127 125 121 123 127 120 116 115 120 136 135 138 6.8% 11.1%New Child Involvements 24 7 18 14 20 31 22 26 24 29 28 38 33 40.4% 42.4%
Open Cases (Avg. Daily Census) 363 313 356 348 346 346 341 339 338 342 354 335 330 -9.1% -3.8% Open Clients (Avg. Daily Census) 571 555 557 539 527 528 526 524 525 532 560 574 571 0.0% 5.1%OOH Placement - - - - - - - - - 0 0 0 0
Foster Care Placements (Avg. Daily Census) 102 103 100 98 100 95 95 99 99 98 100 113 100 -1.6% -0.1%Group Home Placements (Avg. Daily Census) 22 23 22 24 25 25 25 25 23 23 20 22 23 7.7% 0.9%Lic. FC Homes in County (Avg. Daily Census) 62 60 59 55 52 55 56 51 49 48 47 47 45 -27.0% -15.7%Lic. Kin FC Homes in County (Avg. Daily Census) 18 18 16 16 15 15 13 12 13 14 11 9 9 -51.2% -37.8%New OOH Placements 30 11 33 30 21 3 34 51 20 25 20 25 28 -5.6% 11.2%Non-Adoption Placements (Avg. Daily Census) 188 189 188 184 185 175 173 174 171 172 170 168 176 -6.4% -1.2%Paid Kinship FC Placements (Avg. Daily Census) 23 23 23 21 22 20 18 16 14 14 11 9 9 -61.1% -50.8%RCCF Placements (Avg. Daily Census) 36 35 37 36 32 30 30 30 31 35 37 40 42 14.9% 21.6%
29
Outcomes we hope to avoid!
Heads hurting from too much detail
Or worse?
KNOW YOUR ‘STUFF’
Audience, Information, Answers
31
Know your ‘stuff’
Who is your audience? What do they need to know?
If possible, ask them what they would like to see Required versus helpful information
Information: keep it simple s______ (KISS) Consider alternatives - provide examples, use charts Use plain English – avoid acronyms and jargon Less is more: avoid long lists, big tables of numbers,
busy charts; use round numbers Anticipate the questions
Look at the data and ask your own questions Understand the ‘why’ behind variances
THE FOUNDATION OF THE REPORTData – State & County, Communication, Tools
33
The Foundation – Data: State Sources
Utilize the State systems for financial and caseload data CFMS: Expenditure/Revenue Detail PTD (see
EXAMPLE slides 36-38 ) Discoverer data (excellent data a variety of
purposes) County Allocation-Expenditure Reports Trails, CBMS, CHATS, CSS-MMR/QMR (see graph
EXAMPLES) C-Stat and other performance metrics Do you understand how each allocation closes
at SFY end?
34
Allocation Summary: A peek under “the hood”
Careful consideration is given to the amount of detail and how best to summarize the data.
Example: BCA (in yellow).
CO Works is one of the more difficult programs to track, a full spreadsheet is provided.
Monthly TotalAlloc. Alloc.
1,019,018$ 12,228,210$
Salaries, Cost Contracts Basic Diversion Net % YTD YTDBenefits, Alloc. Cash & Other Expend. of Expend. Alloc.
Operating & Plan Assist. Sup. Service Alloc.Allocated
July 133,225$ -$ 1,600 636,505$ 51,430$ 822,760$ 6.7% 822,760$ $1,019,018August 155,515 - 443,222 640,421 46,056 1,285,214 10.5% 2,107,974 2,038,035September 121,976 61,839 253,004 644,378 41,650 1,122,847 9.2% 3,230,821 3,057,053October 91,109 - 263,878 665,391 45,137 1,065,515 8.7% 4,296,336 4,076,070November 126,193 - 449,485 651,458 48,177 1,275,313 10.4% 5,571,649 5,095,088December 97,815 42,725 576,435 652,617 39,730 1,409,322 11.5% 6,980,972 6,114,105January 109,383 - 31,980 634,136 37,670 813,169 6.6% 7,794,140 7,133,123February 117,359 - 354,752 612,143 42,431 1,126,685 9.2% 8,920,826 8,152,140March 95,339 60,000 386,000 614,880 41,695 1,197,914 9.8% 10,118,739 9,171,158April 109,670 - 386,000 614,880 41,700 1,152,250 9.4% 11,270,989 10,190,175May 102,660 - 386,000 602,880 41,700 1,133,240 9.3% 12,404,229 11,209,193June 101,870 60,000 762,000 602,880 41,795 1,568,545 12.8% 13,972,774 12,228,210TOTAL 1,362,113$ 224,565$ 4,294,356$ 7,572,569$ 519,171$ 13,972,774$ 114.3%
12,228,210$ 13,972,774
Contract MOU (1,744,564)$ 80,000$ 4,300,000$ 4,230,202
July -$ 1,600$ 2,485,638$ August - 443,222 September - 253,004 October - 263,878 12,837,643$ November 1,978 447,506 15,121,973 December - 576,435 (2,284,330)$ January 31,980 - 6,514,532 February 5,215 349,537 4,230,202$ March 6,000 380,000 April 6,000 380,000 May 6,000 380,000 June 12,000 750,000 Total 69,173$ 4,225,183$ Balance 10,827$ 74,817$
Reserves at Close of SFY14
Reserves at Start of SFY15Reserves at Close of SFY15
SFY14 CloseoutAllocation
ExpendituresNet (Over)/Under
Reserves at Start of SFY14
Net (Over)/Under
DHS Colorado Works / TANF SFY2015 Expenditures
ContractsSFY15 Projected
AllocationExpenditures
35
Accumulate Monthly CFMS E/R Detail
•Create a worksheet for each major program. Group by major program segment of the CFMS E/R report, by function code, and by account or account code.
• May be imported to Access and exported to Excel to save time• Month-by-month comparisons - excellent resource for a variety
of reports and analyses• Historical monthly data to assist with monthly projection
•Include prior year totals as a helpful reference and comparison• Reasonableness check for projections
•Check your spreadsheet totals to the CFMS report!
COLORADO WORKS / TANF Jul-14 Feb-15 Mar-15 Apr-15 May-15 Jun-15 Total SFY15 SFY14 Total SFY13 Total4150 BCA ARRA SP (I201) -$ -$ -$ -$ -$ -$ -$ -$ 257,098$ 4150 BCA NT MOE (I205) 104 71 939 400 400 400 1,238 1,153 597 4150 BCA NT MOE SP (I205) 3,961 4,266 3,368 4,000 4,000 4,000 20,696 16,361 8,561 4150 BCA SP (I200) 630,367 607,806 609,422 606,000 606,000 610,000 3,005,799 3,146,203 2,864,072
36
CFMS E/R Detail ExampleCFMS SET OF BOOKS
Period: FEB-15 Currency: USDSubmitted: 20-MAR-15 13:53:31
CTY=0xx (Example)COUNTY EXPEN DITURE AND REV ENUE BY SHARE - PERIOD TO DATE
Total Expense Federal Share State Share County SharePTD-Actual PTD-Actual PTD-Actual PTD-Actual
FUNC PRGM ACCNT FEB-15 FEB-15 FEB-15 FEB-15------------------- ----------------- -------------------
BALANCE FORWARD 0.00 0.00 0.00 0.00
PROGRAM SETTLEMENT BY CFMS
COLORADO WORKS NON- ADMIN4150 IM CoWks TANF I200 TANF Client 47400 Fed Earned Re 0.00 561,125.68 0.00 0.004150 IM CoWks TANF I200 TANF Client 49522 County Share 0.00 0.00 0.00 46,679.434150 IM CoWks TANF I200 TANF Client 65793 SP-EBT/EFT Py 606,204.39 0.00 0.00 0.004150 IM CoWks TANF I200 TANF Client 65794 SP-EBT/EFT W/ 3,044.90 0.00 0.00 0.004150 IM CoWks TANF I200 TANF Client 65798 SP-EXPUNGEMEN (1,444.17) 0.00 0.00 0.004150 IM CoWks TANF I200 TANF Client TOTAL 607,805.12 561,125.68 0.00 0.004150 IM CoWks TANF I205 County MOE-N 49522 County Share 0.00 0.00 0.00 4,337.914150 IM CoWks TANF I205 County MOE-N 65793 SP-EBT/EFT Py 4,266.03 0.00 0.00 0.004150 IM CoWks TANF I205 County MOE-N 65794 SP-EBT/EFT W/ 71.88 0.00 0.00 0.004150 IM CoWks TANF I205 County MOE-N TOTAL 4,337.91 0.00 0.00 4,337.914195 IM TANF/Non-Mo I851 TANF-Child C 47400 Fed Earned Re 0.00 5,295.81 0.00 0.004195 IM TANF/Non-Mo I851 TANF-Child C 49522 County Share 0.00 0.00 0.00 440.554195 IM TANF/Non-Mo I851 TANF-Child C 61910 Contract Pymt 5,736.36 0.00 0.00 0.004195 IM TANF/Non-Mo I851 TANF-Child C TOTAL 5,736.36 5,295.81 0.00 440.55
37
Did I mention to check your totals?
38
Accumulate Caseload Data
Gather and organize caseload data from the various systems
Create charts using the data to help with projections Identify trends or changes in trends Identify unusual events Useful for per unit calculations: cost per case, cases
per worker, etc. Useful for tracking certain performance metrics
A few examples…
39
Charts – Assist with Projections
Chart caseload data in a variety of ways to helps spot trends
Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep 900
1,000
1,100
1,200
1,300
1,400
1,500
1,600
1,700
1,800
1,900
CO Works Cases by FFY
14-15 13-14 12-13 11-12 10-11 09-10 08-09
40
Food Assistance
Feb-1
3
Mar-
13
Apr-13
May
-13
Jun-1
3Ju
l-13
Aug-13
Sep-1
3
Oct-13
Nov-13
Dec-1
3
Jan-1
4
Feb-1
4
Mar-
14
Apr-14
May
-14
Jun-1
4Ju
l-14
Aug-14
Sep-1
4
Oct-14
Nov-14
Dec-1
4
Jan-1
5
Feb-1
50
200
400
600
800
1,000
1,200
1,400
1,600
1,800
2,000
70%
75%
80%
85%
90%
95%
100%
Application Volume and % Processed Timely
Applications Processed Linear (Applications Processed)Processed Timely % C-Stat Goal 95% <= 30 Days
41
Child Welfare
Feb-
13
Mar-1
3
Apr-1
3
May-1
3
Jun-
13
Jul-1
3
Aug-1
3
Sep-
13
Oct-1
3
Nov-1
3
Dec-1
3
Jan-
14
Feb-
14
Mar-1
4
Apr-1
4
May-1
4
Jun-
14
Jul-1
4
Aug-1
4
Sep-
14
Oct-1
4
Nov-1
4
Dec-1
4
Jan-
15
Feb-
1520,000
21,000
22,000
23,000
24,000
25,000
26,000
27,000
28,000
29,000
30,000
Out of Home Placement - Paid Bed Days
42
Charts (Cont’d)
Feb-
14
Mar-1
4
Apr-1
4
May-1
4
Jun-
14
Jul-1
4
Aug-1
4
Sep-
14
Oct-1
4
Nov-1
4
Dec-1
4
Jan-
15
Feb-
15 1,200
1,300
1,400
1,500
1,600
1,700
1,800
CO Works Cases - Past 12 Months
Aug-1
3
Oct-1
3
Dec-1
3
Feb-
14
Apr-1
4
Jun-
14
Aug-1
4
Oct-1
4
Dec-1
4
Feb-
15800
1,000
1,200
1,400
1,600
1,800
2,000
CO Works Cases - Past 18 Months
Feb-
13
Apr-1
3
Jun-
13
Aug-1
3
Oct-1
3
Dec-1
3
Feb-
14
Apr-1
4
Jun-
14
Aug-1
4
Oct-1
4
Dec-1
4
Feb-
151,300
1,400
1,500
1,600
1,700
1,800
CO Works Cases - 24 Months
Be mindful of timing issues; make sure to have at least six data points
43
Charts (Cont’d)
Chart refresher Line charts: Best for showing trends over time Bar charts: Best for showing comparisons and
short time series Stacked column charts: Best for showing
changes in composition over time Pie charts: Best for showing relative
comparisons, such as percentages of total of the whole or size comparisons
44
The Foundation – Data: County Sources
County Accounting system Actuals versus budget (EXAMPLE) What is in the budget?
Assumptions, timing of significant expenditures, practice changes
What budget modifications have been made since the budget was adopted?
Consider the timing of expenditures – can be tricky with two accounting bases
What internal tracking reports are available? Vacancy rates, depreciation schedules, contracts/MOUs,
encumbrance tracking
45
County Financial Data Example
February 28, 2015
Feb-15 2015 2015 2015 2014
(Curr Period) YTD Projection Budget Actual
Sources
Taxes 0.09 1.45 9.13 9.35 9.08
Intergovernmental 2.58 3.80 24.80 26.75 25.34
Other Revenues 0.04 0.07 0.45 0.54 0.46
Total Sources 2.71 5.32 34.37 36.64 34.89
Uses
Salaries 1.39 2.43 16.66 17.46 16.87
Employee Benefits 0.37 0.68 4.64 4.96 4.58
Supplies 0.03 0.05 0.37 0.43 0.38
Services 0.05 0.15 1.17 1.41 1.13
Community Programs 1.04 1.50 10.61 12.10 11.07
Internal & Central Services 0.10 0.10 0.62 0.19 0.20
Total Uses 2.98 4.91 34.07 36.55 34.24
Surplus/(Deficit) (0.27)$ 0.41$ 0.30$ 0.09$ 0.66$
Fund Balance
Beginning Balance 9.04$
Increase/(Decrease) to Fund Balance 0.30
Ending Fund Balance 9.34$
Fund Balance Detail
Beg. Bal. 1/1/2015
Proj. Change
12/31/15
Est. End Bal.
12/31/15Restricted 4.86$ 0.22 5.09$ Assigned 1.83$ 1.83$ Unassigned 2.35$ 0.07 2.42$
9.04$ 0.30$ 9.34$
46
County Financial Data - Detail
Variances to investigate are highlighted – impact allocation• Personnel
• Vacancies by program area to understand impact on allocations
• Community Programs = largely county share• Settlement data, caseloads, program changes
• Internal & Central Services• Building & grounds maintenance (painting, flooring,
parking lot )
February 28, 2015
Feb-15 2015 2015 2015 2014
(Curr Period) YTD Projection Budget Actual
Salaries 1.39 2.43 16.66 17.46 16.87
Employee Benefits 0.37 0.68 4.64 4.96 4.58
Supplies 0.03 0.05 0.37 0.43 0.38
Services 0.05 0.15 1.17 1.41 1.13
Community Programs 1.04 1.50 10.61 12.10 11.07
Internal & Central Services 0.10 0.10 0.62 0.19 0.20
Total Uses 2.98 4.91 34.07 36.55 34.24
Surplus/(Deficit) (0.27)$ 0.41$ 0.30$ 0.09$ 0.66$
47
County HS Financial Detail
Accumulate the county financial data in a similar fashion as shown with CFMS example Month by month, by cost/budget centers, by account,
Totals for current year and prior years
Include expected changes in costs, practice or organizational changes, one-time versus ongoing expenditures Consider tracking material items separately (i.e. large
contracts or intra-governmental expenditures)
Get acquainted with the cost pools! Understand the connection between expenditures in your
county flow through to HS programs
48
The Foundation – Data: State AND County
Both data sets work together To understand what happens to the allocations, you
must understand: The impact of administrative and program costs that flow
from the County accounting system Impact of special funding, performance incentives, etc.
To understand what happens to the county accounting system, you must understand: The MOE or county share or for program costs paid by state
systems How administrative/program costs are reimbursed and
impact revenues Incentives and close-out
49
It’s a magical moment when it all comes together…
50
The Foundation - Communication
Frequent communication with business partners County program area
Changes to approach, staffing, program policy, etc.
State/County
State/County contacts FOG, Sub-PAC, etc.
Monitor changes in rule, program approach, initiatives, etc.
51
The Foundation - Tools
Technology is your BFF Excel
SUMIFS VLOOKUP IF (nested statements) TREND Descriptive Statistics (data analysis add-in
required) Access for large data sets
Usually requires training/expertise - reduce repetitive tasks and potential errors
52
Of course, not all tools are available just yet…
