Controllership Function in School Districts
Texas Association of School Business Officials Annual Conference - Austin Texas March 2008
James Terry PhD, CPA, CTSBO Executive Director of Finance and Accounting Services North East ISD
Controller’s Job More than Financial Control Systems
Accounting controls
Safeguarding of assets Reliability of the financial records
Plan of organization Operational efficiency Managerial policies Achieving the business objectives
Administrative Controls
Operational Controls
The Controller’s Job Functions
Planning Organizing Directing Measuring/Reporting Financial analysis/Modeling Process analysis/Statistical analysis Forecasting
The Organization
Annual Planning (Budgeting)
Strategies
Revisions
Execution
Strategic Planning
Processes
Activities
Tasks
The Community
Evaluation Balanced Scorecard Six Sigma Dashboard
Source: Robert N. Anthony and Vijay Govindarajan, Management Control Systems
The Controller’s Job Functions
Planning Organizing Directing Measuring/Reporting Financial analysis/Modeling Process analysis/statistical analysis Forecasting
Department Of Finance
Executive Director of Finance & Accounting Services E9 Departmental Secretarial Pool Secretary to the Executive Director – C9 Administrative Support Specialist – C7 Finance Secretary– C3
Director of Accounting Services & Payroll E8
Director of Budgets & Financial Control E8
Director of Procurement & eCommerce E7
Director of Risk Management & Employee Benefits E8
Director of Grants Administration / Comptroller E6
Office of Accounting Services and Payroll
Director E8 Payroll Manager E4 Leave Coordinator E3 Payroll Technician C10 Senior Accounting Manager E5 District Cashier C6 Accounts Payable Supervisor E1
Secretarial Pool
Senior Accounting Manager E5 Accounting Clerk Part Time C3 Accounting Technician C10
Data Technician C4
Payroll Specialist C8
Accounting Specialist Payroll Deductions C8 Payroll Clerk C4 Assistant Payroll Manager E3
Accounts Payable Specialist C8 Accounts Payable Specialist C8 Accounts Payable Specialist C8 Accounts Payable Specialist C8 Accounts Payable Specialist C8 Accounts Payable Specialist C8 Accounts Payable Specialist C8
Accounting Specialist C8
Payroll Technician C10
Payroll Specialist C9 Payroll Specialist C9 Payroll Specialist C9
Office Of Grants Administration
Assoc. Supt. of Campus Support Assoc. Supt. of Instruction
Secretarial Pool
Director for Grants Administration/Comptroller E6
Executive Director of Athletics
All Executive Directors and Principals Grant Writer Consultant Executive Director of Finance & Accounting Services Corporate Development Consultant
Athletic Accountant Reports to Jerry Comalander E1
Senior Accountant – Grant Administration E2
Special Education Accountant E1
Instructional Program Specialist C9 Senior Accountant – Curriculum Compliance & School Improvement E2 Senior Accountant – Continuing Education / KIN E2
Executive Director of Curriculum Compliance
Director of Community Education
Office of Procurement & eCommerce
Director E7
Assistant Director E6
Financial Technology Coordinator T3 Secretarial Pool Technology Specialist C9 Finance Clerk C3
Manager E3
Specialist/ OWS Inventory C9
Facility Usage & Textbook C9
Textbook Coordinator Temporary X1
Specialist/TWS Inventory C9
Part-time Specialist/ Facility Usage & Textbook C9
Procurement Coordinator E2
Specialist/GWS Inventory C9
Procurement Specialist Frozen C9
Bid Coordinator C10
Contract Coordinator C10
Procurement Specialist C9 Specialist/ PO Processor C9 OWS Warehouse Supervisor E1 Material Handler O4 Material Handler O4 Material Handler O4 Specialist/ PO Processor C9 Specialist/ PO Processor C9
Material Handler O4
Material Handler O4
Material Handler O4 Material Handler/ Testing O4
Material Handler O4 GWS Warehouse Supervisor E1
Material Handler O4 Material Handler Frozen O4
Material Handler O4
Procurement Warehouses
Property Transfer Coordinator Pending
Property Management Technician O1 Property Management Technician O1
Office Of Budgets & Financial Control
Director of Budgets & Financial Control E8 Secretarial Pool
Budget Analyst E1
Vacant VOE Student
Div Comptroller for Operations E5
Assoc. Supt. of Auxiliary Services Assoc. Supt. of Business Services
Senior Accountant E3
Sr. Accountant for School Nutrition E2
Executive Director of School Nutrition
Comptroller for Campus Support E5
Assoc. Supt. of Campus Support Assoc. Supt. of Business Services
Campus Support Technician C10 Comptroller for Business Services & Treasurer E5
Assoc. Supt. of Business Services
Staff Accountant - Treasury E2
The Controller’s Job Functions
Planning Organizing Directing Measuring/Reporting Financial analysis/Modeling Process analysis Forecasting
The Controller’s Job Functions
Managing Coaching Managing by Objective (MBO) Priorities Mediation Praise Reprimand
The Controller’s Job Functions
Planning Organizing Directing Measuring/Reporting Financial analysis/Modeling Process analysis/Statistical analysis Forecasting
The administrative cost ratio administrative costs divided by instruction costs, expressed as a percentage.
Year
NEISD
97-98 98-99 99-00 00-01 01-02
5.80% 6.21% 6.04% 6.23% 6.30%
The state standard for the administrative cost ratio is 11.05%
INSTRUCTIONAL EXPENDITURES PER REFINED ADA
Instr. Expend. /RADA 1 North East $ 4,189 2 Houston $ 4,186 3 Cypress-Fairbanks $ 4,103 4 Austin $ 4,098 5 San Antonio $ 4,090 6 El Paso $ 4,048 7 Dallas $ 4,017 8 Forth Worth $ 3,957 9 Fort Bend $ 3,928 10 Northside $ 3,927 11 Arlington $ 3,926
CAMPUS ACADEMIC RATINGS
100% 90% 80% 70% 60% 50% 40% 30% 20% 10% 0%
I SD FA IR BA NK SI SD ISD I SD ISD SD SD AS I I SD IS D PA SO EA ST ID EI TIN N D H O I SD
Low Performing Acceptable Recognized Exemplary
WO RT
TO
EN
AR LIN G
FO RT
NO RT HS
FO
RT H
77% of NEISD’s districts are rated Exemplary or Recognized. Fort Bend is the only large district with similar results.
CY PR ES
SA N
S-
NO
AN TO NI
DA LL
AU S
RT B
EL
YTD Days per Paraprofessional
4.5 4 3.5 3 2.5 2 1.5 1 0.5 0
G AR N ER EG ER IT Z N IM KR U N JA C KS O H IT E O D LE Y D R IS C x( AL T_ M O LL BU SH S, SR C ) TE JE DA O
SUBSTITUTE DAYS PARAPROFESSIONALS HS & MS
1st Quarter 2003-04
W W BR AD
1st Quarter 2002-03
HS ADA by Year by Campus
98
Percent of Average Daily Attendance
96 94 92 90 88 86
CHURCHILL LEE MAC MADISON REAGAN ROOSEVELT ISA
A/B Schedule Linked to PEIMS Discipline
89 90 90 91 91 92 92 93 93 94 94 95 95 96 96 97 97 98 98 99 -9 - 9 20 200 00 0 20 -0 0 1 20 1-0 02 2 -0 3
School Year
Automated Calling System
TTAD
% Productive Time
100% 60% 65% 70% 75% 80% 85% 90% 95%
Air Conditioning Carpentry Energy Control Environmental Heating Electrical
Filter Crew
Fire Alarms
Auxiliary Services
Maintenance Department Productivity September - November, 2003
Floors Facility Managers Glass
Grounds
Bond Expenditures & Commitments
As of April 30, 2004
Principal & Interest Received $ 88,221,817 72,119,836 230,801,146 175,737,073 300,799,927 $ 867,679,799 $ Actual Expenditures & Commitments $ 88,113,600 71,933,771 221,224,782 150,081,429 71,247,079 602,600,661 $ $ $ % Balance of Total P&I
* ** *** **** *****
Bond Series 1996 1997 1999 2000 2004 Total Notes: * **
*** *** **** **** *****
Amount Issued $ 80,000,000 67,485,000 211,000,000 155,870,000 300,000,000 $ 814,355,000
Balance 108,217 186,066 9,576,364 25,655,644 229,552,848 265,079,138 108,217 186,066
8,633,423 942,941 25,058,585 597,059 229,552,848 265,079,138
0.12% 0.26% 4.15% 14.60% 76.31% 30.55%
Earmarked for budgeted projects Earmarked for budgeted projects Earmarked for budgeted projects Earmarked for administrative costs Earmarked for budgeted projects Earmarked for administrative costs Earmarked for budgeted projects and administrative costs T otal Earmarked
$
100%
Interest earned and received for the period to date is the difference of the issued amount and the principal and interest received. Interest earned and received through the current period is:
$
53,324,799
15
Capital Expenditures
EXPENDITURES BY FISCAL YEAR
$120,000,000
$100,000,000
$95,792,166 $100,331,724
$91,839,567
$80,000,000 $71,995,842 $63,261,521 $60,000,000
$47,472,424
$40,000,000 $32,561,560
$20,000,000 $4,834,710 $-
$17,246,501
1995 - 96
1996-97
1997-98
1998-99
1999-00
2000-01
2001-02
2002-03
2003-04
16
The Controller’s Job Functions
Planning Organizing Directing Measuring/Reporting Financial analysis/Modeling Process analysis Forecasting
Financial Analysis
Statistical Section of CAFR Inventory Carrying Costs Bank Earnings Credit vs. Investments Cash inflows and outflows Benchmarking – ERG Desktop Analyst APi FPI
18
Financial Analysis
Variance Reports Staffing Analysis Teacher Utilization Classroom Utilization Magnet Program Costs Utility Usage
18
Financial Analysis
Course Costs Mindsphere Performance Measures APQC OSBC
18
The Controller’s Job Functions
Planning Organizing Directing Measuring/Reporting Financial analysis/Modeling
Process analysis/Statistical
Forecasting
Performance Measures
Measure what you want to change.
Source: NEISD Finance Department
E X E C U T I O N
Processes
Planning/Budgeting Teaching Reading Writing Arithmetic
A C T I V I T I E S
Determining Performance
3 by a deterministic function 2
is operated on…
y = f (x)
5
into an outcome! (the dependent variable) 1 The independent variable… 4 To transform it…
Using actual data to identify world-class performers in Enterprise Performance Management
Effectiveness (Examples)
Industry relative total excess return
Operating profit volatility Analysts seen as business partners Balance of both financial and non-financial performance measures Quality metrics Accuracy of forecasts and analysis Use of balanced scorecards, simulation models Percent managers describing budget process convenient and easy Percent of time analytic focus is on proactive decision making Percent of time reports and commentaries address future actions
Efficiency (Examples)
•Overall planning cost as a % of revenue •Process cost as a % of revenue •Percent of business reports distributed electronically •Operations managers use of on-line budgeting tools •Staffing levels of process •Cycle times •Budget iterations •Performance reporting productivity •Utilization of self-service for inquiry •Application complexity
Transportation Warehouse Order Processing - HLP
Vendor Vendor Truck Fulfills PO Vendor Truck
e
Verbal PO’s PO’s are faxed, emailed or mailed Operations Whse Processes Requisitions, Bid Quotes, completed PO’s, returns, exceptions
START
Deliveries
Direct Orders PO are called / faxed for same day shipment by Vendor Requests 1 Clerk at Trans Whse 4 On-Line Calalog Only Processes VENDOR and INTERNAL requisitions and PO returns Parts Mgr at Trans Whse Maintains and reconciles Inventory
Maintenance Deliveries
Daily Receiving Tickets (P/S) 2 Receiving Receives Merchandise, processes requisitions and returns
Pick Tickets
Manual Work Orders
Daily Receiving Tickets (P/S) Pick Tickets 1
1 Hot Shot, Direct Orders Deliveries
Picking Pulls stock from Inventory
Picking / Loading 2 Stock Pile Pulls stock from Inventory Loads Truck for Orders
1
Receiving Receives Merchandise, processes requisitions and returns for
Fulfills Pick Tickets
Manual Work Orders
END
Motor Pool Ready to Use Vehicle(s)
3
Tool Inventory
Mechanic Maintains Personal Tool Inventory Repairs Vehicles
1
START
Oper Whse Inventory
deliveries
1 Hot Shot Deliveries
NEISD Truck
PEIMS Reporting Process
Verify PEIMS file against CAFR. Audit LEA runs an edit check on PEIMS. Identify major errors and correct them. Submit file to Data Processing PEIMS Coordinator.
Resubmit until file is clean.
TEA runs an error listing. Errors are communicated and corrected.
Submit file to TEA.
File is error free, end process.
Expenditure Reclassification Process
Campus/Department calls regarding expenditure posting error in GL Discuss with campus/department action necessary to correct error and prevent future errors. Submit journal entries to Accounting Services for posting Yes Director Approval? No Notify campus/department.
Documentation to support expenditure reclassification is requested from campus/department
Prepare journal entries to reclassify expenditure.
Accounting Services scans and files expenditure reclassification documentation.
End of process.
End of process.
The Controller’s Job Functions
Planning Organizing Directing Measuring/Reporting Financial Analysis/Modeling Process Analysis/Statistical Analysis
Forecasting
Forecasting Into the Future
Big numbers vs. small numbers One number vs. many numbers Multiple periods over time vs. limited periods over short periods of time
36
Linear Correlation
Two distributions are correlated when a change in one causes a change in another Example: People who eat lots of peas may eat less broccoli (or may eat more…) Usually doesn’t have much effect unless significant correlation (||>0.75)
Correlation is a dependency between your Xs
Correlation does not mean causality! Always lies between -1 and 1 Measures strength of the linear relationship between two Xs
Correlation coeff = 0 Correlation coeff > 0
Correlation coefficient
Correlation coeff < 0
Crystal Ball Forecasting
Crystal Ball offers opportunities to quickly run Monte Carlo Simulations in Excel to improve services Crystal Ball can be customized to do several what-if analyses to achieve quality performance and reduce cost
Monte Carlo Simulation
Computer-based “experimental” method estimating statistical distribution of output (Y) Steps for Crystal Ball Simulation:
Assign each input (X) a value chosen “at random” from its assumed distribution Substitute values of inputs (Xs) into the system transfer function and compute a value of output (Y) Record value of output (Y) Repeat until enough samples of output (Y) characterize its statistical distribution
Controller’s Functions Example
Measuring/Reporting Financial Analysis/Modeling
Forecasting/Statistical Analysis
Planning
Identifying Inputs & Outputs
Cash Inflows
Sales
Concentration
Cash Outflows
Employees
Fees
Bond Holders
Subsidies
Vendors
Inv. Income
Stock Holders
4
Enter Historical Daily Rates into a Spreadsheet
Date
5/22/01 5/23/01 5/24/01 5/25/01 5/26/01 5/27/01 5/28/01 5/29/01 5/30/01 5/31/01 6/1/01 6/2/01 6/3/01
TexPool 4.3591% 4.3115% 4.3062% 4.2441% 4.2441% 4.2441% 4.2441% 4.3159% 4.3183% 4.3691% 4.3329% 4.3329% 4.3329%
Create a Line Graph Using the Information in Your Spreadsheet
4.50% 4.40% 4.30% 4.20% 4.10% 4.00% 3.90% 3.80% 3.70% 3.60%
Past 90 Days of Investment Pool Rates
6/ 5/ 01
7/ 3/ 01
8/ 7/ 01
/0 1
/0 1
/0 1
/0 1
/0 1
/0 1
/0 1
/0 1
/0 1
/0 1 8/ 14
5/ 22
5/ 29
6/ 12
6/ 19
6/ 26
7/ 10
7/ 17
7/ 24
7/ 31
TexPool
8/ 21
/0 1
90 Days of Rates from Three Different Sources - Example
4.50% 4.40% 4.30% 4.20% 4.10% 4.00% 3.90% 3.80% 3.70% 3.60%
Past 90 Days of Investment Pool Rates
6/ 5/ 01
7/ 3/ 01
8/ 7/ 01
/0 1
/0 1
/0 1
/0 1
/0 1
/0 1
/0 1
/0 1
/0 1
/0 1 8/ 14
5/ 22
5/ 29
6/ 12
6/ 19
6/ 26
7/ 10
7/ 17
7/ 24
7/ 31
TexPool
LOGIC
Lone Star
8/ 21
/0 1
Forecast New Chart with Information
Comparison of Security vs. Trend Line
4.50%
4.00%
3.50%
3.00%
2.50%
2.00%
5/ 22 /0 1 6/ 22 /0 1 7/ 22 /0 1 8/ 22 /0 1 9/ 22 /0 10 1 /2 2/ 0 11 1 /2 2/ 0 12 1 /2 2/ 01 1/ 22 /0 2 2/ 22 /0 2 3/ 22 /0 2 4/ 22 /0 2 5/ 22 /0 2 6/ 22 /0 2 7/ 22 /0 2 8/ 22 /0 2 9/ 22 /0 2
TexPool
Security
TexPool Trend
The Break Even Point
Comparison of Security vs. Trend Line
4.50%
4.00%
Break Even Point (~2/4/02)
3.50%
3.00%
2.50%
2.00%
5/ 22 /0 1 6/ 22 /0 1 7/ 22 /0 1 8/ 22 /0 1 9/ 22 /0 10 1 /2 2/ 0 11 1 /2 2/ 0 12 1 /2 2/ 01 1/ 22 /0 2 2/ 22 /0 2 3/ 22 /0 2 4/ 22 /0 2 5/ 22 /0 2 6/ 22 /0 2 7/ 22 /0 2 8/ 22 /0 2 9/ 22 /0 2
TexPool
Security
TexPool Trend
See the Additional Earnings after Break Even Point
Comparison of Security vs. Trend Line
4.50%
4.00%
Break Even Point (~2/4/02)
Potential Additional Earnings
3.50%
3.00%
2.50%
2.00%
5/ 22 /0 1 6/ 22 /0 1 7/ 22 /0 1 8/ 22 /0 1 9/ 22 /0 10 1 /2 2/ 0 11 1 /2 2/ 0 12 1 /2 2/ 01 1/ 22 /0 2 2/ 22 /0 2 3/ 22 /0 2 4/ 22 /0 2 5/ 22 /0 2 6/ 22 /0 2 7/ 22 /0 2 8/ 22 /0 2 9/ 22 /0 2
TexPool
Security
TexPool Trend
Actual Rates vs. Projected Rates
Performance of Security Purchased 08/24/2001
(maturity of 07/18/2002) 4.0%
3.5%
3.0%
2.5% Actual BEP 10/02/02 2.0%
Projected BEP 02/04/02
1.5%
9/ 23 /0 1 10 /2 3/ 01 11 /2 2/ 01 12 /2 2/ 01
8/ 24 /0 1
1/ 21 /0 2
2/ 20 /0 2
3/ 22 /0 2
4/ 21 /0 2
5/ 21 /0 2
Security
Projected TexPool Rate
Actual TexPool Rate
6/ 20 /0 2
Example of the Model Showing Potentially Higher Earnings
Comparison of Security vs. Trend Line
3.00%
2.50%
Break Even Point (~7/1/02)
(after maturity date!)
2.00%
1.50%
1.00%
0.50%
10 /1 7/ 01 10 /3 1/ 11 01 /1 4/ 11 01 /2 8/ 01 12 /1 2/ 12 01 /2 6/ 01 1/ 9/ 02 1/ 23 /0 2 2/ 6/ 02 2/ 20 /0 2 3/ 6/ 0 3/ 2 20 /0 2 4/ 3/ 0 4/ 2 17 /0 2 5/ 1/ 0 5/ 2 15 /0 5/ 2 29 /0 2 6/ 12 /0 6/ 2 26 /0 2 7/ 10 /0 7/ 2 24 /0 2
LOGIC Security LOGIC Trend
100.00%
120.00%
-40.00% 20.00% 40.00% 60.00% 80.00% 0.00%
2/ 7/ 20 0 2/ 14 8 /2 00 2/ 21 8 /2 00 2/ 28 8 /2 00 8 3/ 6/ 20 0 3/ 13 8 /2 00 3/ 20 8 /2 00 3/ 27 8 /2 00 8 4/ 3/ 20 0 4/ 10 8 /2 00 4/ 17 8 /2 00 4/ 24 8 /2 00 8 5/ 1/ 20 08 5/ 8/ 20 0 5/ 15 8 /2 00 5/ 22 8 /2 00 5/ 29 8 /2 00 8 6/ 5/ 20 0 6/ 12 8 /2 00 8
-20.00%
Fixed Rate Security versus BlackRock TempFund (Allied Irish C/P 3.11% Mat 6/6/08) Dated 2/15/08
Cash Flow Forecasting
Ensures liquidity Enhances cash management practices Forecasts problems
Revenues not being collected properly Expenditures exceed budget Amounts available for investment Period of time to hold the investment
5
Increase investment income
Controller’s Functions Example
Developing Cash Budgets
Measuring/Reporting Financial Analysis/Modeling
Forecasting
Statistical Analysis Planning
Assumptions
Cash flows for the current year will follow a pattern similar to that of the previous year All of the budgeted revenues will flow in as cash within the fiscal year All budgeted appropriations will flow out as cash during the fiscal year
8
Fiscal Year to Date General Operating Cash Reciepts: Cash Flows In
$120,000,000
$100,000,000
$80,000,000
$60,000,000
$40,000,000
$20,000,000
$0
September October November December January February March FY00-01 FY01-02 April FY02-03 May June July August
12
$120,000,000
Monthly (Current) Taxes Collected
$100,000,000
$80,000,000
$60,000,000
$40,000,000
$20,000,000
$0 Oct Nov Dec Jan Feb FY01-02 Mar Apr FY02-03 May FY03-04 Jun Jul Aug Sep
13
Assumptions
Increase (decrease) in cash balance at the end of the year will be approximately equal to the excess (deficit) of budgeted revenues over budgeted expenditures
9
The Theory
Identify an optimal allocation of Funds
Funds that need to remain short-term for upcoming disbursements Balance that could be invested longer-term
Management of short-term funds based on cash flow analysis Management of longer-term funds based on market conditions
10
The Process
Review historical cash balances (typically monthly data for 2-5 years) Determine if there are any “statistically significant” seasonal changes in monthly balances Identify any growth trends Project future balances
11
Current Year Cash Flows
Use the percentages calculated and the current budgets for revenue and expenditures to determine the cash flow for the year Determine next year’s cash flows by multiplying percentages times next years budgeted revenues and expenditures
28
Information Required
Beginning cash balance of liquid investments
Bank Accounts Pool Accounts Money Market Accounts, etc.
Par value of outstanding investments
34
Current Year Cash Flows
Using the Forecasted Excess (Deficiency) of Cash along with the beginning cash balance and expected fixed income maturities, determine if there are any cash shortages If there are no shortages, determine where fixed income securities could be purchased and when they should mature based on the forecast
35
DAILY CASH MGT.SPREADSHEET Purch (-) Sold Int. (+) (+) CFO (-) CFI (+)
9/01/93 …… …... 8/31/94
CASH MANAGEMENT PROGRAM
( - ) MEANS REDUCING THE BALANCE ( + )MEANS INCREASING THE BALANCE
Weekly Cash Flow Management Spreadsheet
ACTUAL CASH 93-94
RECEIPTS SEPT 93………AUG 94 Week 1 2 3 4
Cash Budgets
FORECASTED RECEIPTS
SEPT……………………. 1 $ $ 4 $ AUG 95
ACTUAL CASH %
RECEIPTS SEPT 93 ………AUG 94 1 % % % 2 3 4 % % %
PROJECTED MATURING INVESTMENTS
ACTUAL
EXPENDITURES SEPT 93………AUG 94 Week 1 2 3 4
+
SEPT……………………. 1 $ $ 4 $
AUG 95
GRAND TOTAL
100%
FORECAST EXPENDITURE
ACTUAL CASH %
EXPENDITURES SEPT 93 ………AUG 94 1 % % % 2 3 4 % % %
-
ACTUAL
EXCESS/DEFICIT SEPT 93………AUG 94 Week 1 2 3 4
SEPT……………………. 1 $ $ 4 $
AUG 95
FORECAST CASH Excess/Deficit
GRAND TOTAL
100%
=
SEPT……………………. 1 $ $ 4 $
AUG 95
FISCAL YEAR
FISCAL YEAR
21
Actual Receipts: Cash Flows in
SEPT 1st WK 2nd WK 3rd WK 4th WK 5th WK TOTAL $ $ $ $ 266,309 $ 378,548 $ 295,191 $ OCT NOV DEC JAN FEBMAR APR MAY ## ## ## ## ## ## ### ### ### ### ### ### ## ## ## ## ## ## ### ### ### ### ### ### ### ### ### ### JUNE ##### $ ##### $ ##### $ $ JULY 235,168 $ 893,818 $ (323,114) $ 381,110 $ AUG 329,988 $ (29,289) $ 65,342 $ 274,541 $ 385,280 $ TOTALS 80,756,909 81,999,786 16,112,265 72,053,085 39,728,412 734,085 $ 46,783,892 $ 11,367,424 2,546,418 $ 46,082,768 $ 7,722,074 5,990,609 $ (4,441,861) $ (3,888,914) 4,919,827 $ 5,892,337 9,197,929 $ 2,713,211 ##### $ 4,588,126 $
$ 29,221,470 $ 32,782,345 $ $ 12,213,217 $
### ### $
$ 30,161,517 $ 54,266,674 $ 102,542,555 $ 23,806,132
##### $ 5,775,109 $ 1,025,862 $ 290,650,457
Actual Disbursements: Cash Flows Out
SEPT 1st WK 2nd WK 3rd WK 4th WK 5th WK TOTAL OCT NOV DEC JAN FEB MAR APR MAY JUNE JULY AUG TOTALS $ (2,560,518) $ (3,258,080) $ (583,324) $ (3,445,460) ## ### #### #### ### $ (2,383,357) $ (5,053,501) $ (3,823,062) $ (30,335,840) ### #### #### ### $ (5,771,302) $ (2,781,122) $ (1,134,775) $ (38,163,589) ### #### #### ### $ (2,817,272) $ (2,477,795) $ (3,931,719) $ (71,148,373) ### #### #### ### $ (16,098,003) $ (16,077,359) $ (1,913,717) $ (116,971,577) ### #### #### ### $ $ (1,897,841) $ (19,742,476) $ (73,183,692) ### #### #### ### $ (27,069,934) $ (28,287,619) $ (30,545,749) $ (329,803,071) $ (2,733,047) $ (3,863,166) $ (3,597,608) $ (2,894,389) ## $ (7,656,420) $ (2,931,160) $ (3,233,889) $ (17,408,892) ## $ (9,366,265) $ (13,988,058) $ (18,059,712) $ (2,137,946) ## $ $ (4,714,921) $ (2,714,840) $ (1,232,514) ## $ (22,316,251) $ (28,755,384) $ (28,189,373) $ (27,119,200) ##
Excess (Deficiency) of Actual Cash Flows:
1st WK 2nd WK 3rd WK 4th WK 5th WK TOTAL $ $ $ $ $ $ (2,294,210) (2,354,499) (7,361,230) 19,855,205 7,845,266 $ $ $ $ $ $ (2,523,994) (1,316,748) 3,059,449 18,794,288 7,498,296 25,511,290 $ $ $ $ $ $ 46,200,568 42,485,161 (7,675,751) (13,139,885) 6,483,089 74,353,182 # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # $ $ $ $ $ $ (656,205) (3,875,771) (267,395) (12,773,252) (17,572,624) $ $ $ $ $ $ (465,375) (2,545,954) (1,583,977) (16,400,472) (1,516,731) (22,512,509)
24
% of Receipts: Cash Flows in by Week
SEPT 1s t WK 2nd WK 3rd WK 4th WK 5th WK TOTAL 0.1% 0.1% 0.1% 10.1% 0.0% 10.4% OCT 0.3% 0.9% 2.1% 11.3% 4.2% 18.7% NOV 16.1% 15.9% -1.5% 1.7% 3.2% 35.3% DEC 3.9% 2.7% -1.3% 2.0% 0.9% 8.2% JAN 2.4% 1.8% 1.7% 0.7% 4.1% 10.7% FEB 1.9% 5.3% 2.7% -1.7% 0.3% 8.5% MAR 0.1% 0.4% 0.3% -0.8% 0.3% 0.4% APR 0.6% 0.1% 0.1% 0.2% 0.1% 1.1% MAY 0.1% 0.4% 0.2% 0.1% 0.3% 1.1% JUNE 0.6% 0.7% 0.9% 1.1% 0.0% 3.3% JULY 1.6% 0.1% 0.3% -0.1% 0.1% 2.0% AUG TTLS 0.1% 0.0% 0.0% 0.1% 0.1% 0.4% 100%
% of Disbursements: Cash Flows out by Week
SEPT 1s t WK 2nd WK 3rd WK 4th WK 5th WK TOTAL 0.8% 0.8% 2.3% 2.8% 0.0% 6.8% OCT 1.0% 1.2% 0.9% 4.2% 1.4% 8.7% NOV 0.2% 1.1% 1.0% 5.5% 0.8% 8.5% DEC 1.0% 0.9% 5.3% 0.6% 0.4% 8.2% JAN 0.5% 0.8% 4.6% 1.5% 0.4% 7.9% FEB 0.4% 0.6% 1.5% 0.4% 5.0% 7.8% MAR 0.4% 1.2% 1.1% 0.1% 5.6% 8.4% APR 1.0% 1.3% 1.1% 5.2% 0.5% 9.1% MAY 0.5% 0.8% 1.0% 4.7% 1.6% 8.6% JUNE 0.7% 1.7% 0.9% 4.9% 0.0% 8.2% JULY 1.5% 0.8% 0.8% 4.9% 0.6% 8.6% AUG 1.2% 0.3% 1.2% 0.6% 6.0% 9.3% 100% TTLS
27
Forecasted Receipts: Cash Flows In
1st WK 2nd WK 3rd WK 4th WK 5th WK TOTAL $ $ $ $ $ $ SEPT 272,188 386,905 301,707 3,908,951 25,957,618 30,827,368 $ $ $ $ $ $ OCT 750,291 2,602,633 6,122,858 33,506,054 12,482,839 55,464,675 $ $ $ $ $ $ NOV JAN MAR MAY DEC FEB APR 47,816,702 # # # 47,100,100 # # # (4,539,921) # # # 5,028,438 # # # 9,400,984 # # # 104,806,303 # # # $ $ $ $ $ $ JUNE 1,765,280 1,937,378 2,606,168 1,861,448 3,244,702 11,414,976 $ $ $ $ $ $ JULY AUG 4,689,415 $ 337,273 240,360 $ (29,935) 913,551 $ 66,784 (330,247) $ 280,602 938,524 $ 19,021,353 6,451,603 $ 19,676,076 8/19/02 budget adoption= $ $ $ $ $ $ $ TOTALS 82,539,714 83,810,028 16,467,963 49,953,496 91,699,023 324,470,223 324,470,223
Forecasted Disbursements: Cash Flows Out
1st WK 2nd WK 3rd WK 4th WK 5th WK TOTAL $ $ $ $ $ $ SEPT (2,696,744) (2,878,452) (8,063,760) (8,996,200) (868,373) (23,503,529) $ $ $ $ $ $ OCT (3,431,417) (4,068,696) (3,087,105) (14,732,256) (4,965,766) (30,285,239) $ $ $ $ $ $ NOV (614,358) (3,789,009) (3,405,940) (19,020,532) (2,859,276) (29,689,115) DEC #### #### #### #### #### #### JAN #### #### #### #### #### #### FEB #### #### #### #### #### #### MAR #### #### #### #### #### #### APR #### #### #### #### #### #### MAY #### #### #### #### #### #### $ $ $ $ $ $ JUNE (2,510,157) (6,078,349) (2,967,158) (15,217,708) (1,736,747) (28,510,119) $ $ $ $ $ $ JULY AUG (5,322,360) $ (4,026,458) (2,929,085) $ (1,195,148) (2,609,620) $ (4,140,896) (16,932,713) $ (2,015,531) (1,998,811) $ (20,792,823) (29,792,588) $ (32,170,856) 8/19/02 budget adoption= $ $ $ $ $ $ $ TOTALS (31,949,779) (40,193,983) (74,933,637) (137,783,419) (62,488,565) (347,349,384) (347,349,384)
Forecasted Excess (Deficiency) of Cash:
1st WK 2nd WK 3rd WK 4th WK 5th WK TOTAL CUM. $ $ $ $ $ $ $ SEPT (2,424,556) (2,491,547) (7,762,053) (5,087,249) 25,089,244 7,323,839 7,323,839 $ $ $ $ $ $ $ OCT (2,681,126) (1,466,063) 3,035,754 18,773,798 7,517,073 25,179,436 32,503,275 $ $ $ $ $ $ $ NOV DECJAN MAR MAY FEB APR 47,202,344 # # # 43,311,091 # # # (7,945,861) # # # (13,992,094) # # # 6,541,708 # # # 75,117,188 # # # 107,620,463 # # # $ $ $ $ $ $ $ JUNE (744,877) (4,140,972) (360,989) (13,356,260) 1,507,955 (17,095,143) 12,956,604 $ $ $ $ $ $ $ JULY (632,945) (2,688,725) (1,696,069) (17,262,959) (1,060,286) (23,340,985) (10,384,381) $ $ $ $ $ $ $ AUG (3,689,185) (1,225,083) (4,074,112) (1,734,930) (1,771,470) (12,494,780) (22,879,161) $ $ $ $ $ $ TOTALS 50,589,935 43,616,045 (58,465,675) (87,829,923) 29,210,458 (22,879,161)
32
To help make Investment Decisions
Short Roll vs. Going Out
Make Payroll (Do we have enough money)
Matching Maturities to Cash needs
End of Year Forecasting
Determining Final Budget Amendment Or Fund Balance
7
Forecasting End of Year
Using the calculated cumulative cash flows to project end of year expenditures
68
Forecasting End of Year
ACTUAL CASH DISBURSEMENTS SEPT OCT 1st WK (2,560,518) (3,258,080) 2nd WK (2,733,047) (3,863,166) 3rd WK (7,656,420) (2,931,160) 4th WK (9,366,265) (13,988,058) 5th WK 0 (4,714,921) TOTAL (22,316,251) (28,755,384)
MOV AVG (22,316,251) (25,535,818)
NOV
(583,324) (3,597,608) (3,233,889) (18,059,712) (2,714,840) (28,189,373) (26,420,336)
DEC
(3,445,460) (2,894,389) (17,408,892) (2,137,946) (1,232,514) (27,119,200) (26,595,052)
JAN
(1,739,308) (2,701,507) (15,057,548) (5,063,624) (1,366,883) (25,928,870) (26,461,816)
FEB
(1,264,801) (1,866,411) (4,909,350) (1,274,648) (16,326,742) (25,641,951) (26,325,172)
MAR
(1,440,536) (3,863,517) (3,673,036) (346,242) (18,469,228) (27,792,558) (26,534,798)
APR
(3,139,115) (4,274,349) (3,781,167) (17,138,753) (1,533,479) (29,866,863) (26,951,306)
MAY
0 0 0 0 0 0 (23,956,717)
69
Forecasted Ending Cash Balances
(before purchase of any investments)
$200,000,000 $180,000,000 $160,000,000 $140,000,000 $120,000,000 $100,000,000 $80,000,000 $60,000,000 $40,000,000 $20,000,000 $SEPT OCT NOV DEC JAN FEB MAR APR MAY JUNE JULY AUG
36
The Controller’s Job
Jack Welch of General Electrics once said, “If the rate of change outside your organization is greater than the rate of change inside your organization, then the end is in sight.”