Cash Flow Forecasting and Cash Analysis The Key to Successful Investing Presented By: Keith Sawdon,

Director of Finance

TEN STEPS TO PRUDENT INVESTING





Identify your objectives, constraints, preferences and capabilities. Develop a written investment policy.



Develop a system of internal controls and administrative policies.



Forecast your cash flow.



Analyze the yield curve.



Establish an investment strategy.



Determine your investment outlook.



Select investment instruments.



Monitor and report the market and investment results.



Adjust and rebalance the portfolio.

WHAT MAKES UP AN INTEREST RATE?







Federal Funds Rate,

1.50%

Length to Maturity, and

1.00%

Credit Risk.

0.00%

0.50%

1 2 3 4 5 6 7 8 9 10 11

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

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

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

COMPONENTS OF CASH FORECASTS



Cash/Investment Balances



Projections of Receipts



Projections of Disbursements

Cash Flow Forecasting

BENEFITS OF CASH FORECASTING

• • •

•

Improve investment earnings Ensure liquidity Helps minimize borrowing needs by identifying any cash shortfalls Helps from being too “short” and losing opportunities with “longer” rates

THE FORECASTING METHOD USED DEPENDS ON: 

The time and resources available for forecasting.



The amount of funds you’re working with.



The level of commitment to forecasting.



Accurate information.

BASIC TOOLS NEEDED TO CONSTRUCT A CASH FORECAST



Beginning cash/investment balance.



Historical analysis of cash receipts and disbursements.



Personal experience and/or logic related to cash Receipts & Disbursements



Apply our experience and logic against current fiscal year’s budget.



Capital budget spending projections.

Cash Flow Forecast

RECURRING AND NONRECURRING RECEIPTS AND DISBURSEMENTS Receipts and disbursements that flow in and out can be characterized as either recurring or as nonrecurring 



Recurring flows are regular and predictable and the timing and amount is usually known in advance. An example of recurring receipts include property taxes. Examples of recurring disbursements include salaries and wages.

Nonrecurring flows are irregular and unpredictable. The timing and amounts are usually not known in advance. An example of nonrecurring flows include receipts from the sale of assets. Nonrecurring disbursements include items like capital project expenditures.

MAJOR REVENUE SOURCES • • •

•

• • •

Taxes Special Assessments Federal, State and Intergovernmental Revenue Charge for Services Investment Earnings Other Revenues Transfers In

NONRECURRING REVENUES



Bond Proceeds



Sale of assets • Land •

Buildings

•

Surplus Equipment

Estimated Average Applied to Budget OAKLAND COUNTY - GENERAL FUND PROJECTED CASH FLOW - FROM 3 YEAR HISTORY FY 2000

Revenue Taxes Special Assessments Federal Grants State Grants Intergovernmental Revenue Charges for Services Use of Money Other Revenues Transfers In TOTAL Salaries Fringe Benefits Contractual Services Commodities Internal Services Non-Departmental Capital Outlay Other Expenses Interest on Debt Transfers Out TOTAL NET AUDITED 9/30/99 BEGINNG BAL ENDING ESTIMATED CASH

179,384,000.00 65,200.00 286,181.00 194,950.00 34,499,021.00 64,357,953.00 11,577,400.00 13,576,093.54

MO 1 Ave % 0.010% 0.000% 0.465% 10.074% 2.779% 6.068% 1.386% 1.075%

Projection 17,546.02 1,331.09 19,638.71 958,566.77 3,905,467.79 160,485.46 145,913.41

MO 2 Ave % 0.078% 5.817% 1.420% 10.442% 12.834% 7.805% 1.591% 6.450%

Projection 139,925.60 3,792.41 4,064.57 20,355.78 4,427,567.33 5,023,188.45 184,188.85 875,650.01

9,831,687.00 313,772,485.54

0.002%

237.31 5,209,186.56

0.000%

10,678,732.99

103,441,354.00 40,435,928.00 30,582,342.32 5,577,506.93 39,705,331.77 17,568,402.73 1,197,447.56 250,800.00 75,013,372.23

6.213% 6.370% 4.527% 3.606% 1.903% 1.923% 0.100% 93.451% 0.000%

6,427,062.10 2,575,827.64 1,384,433.02 201,134.58 755,747.85 337,775.53 1,198.14 234,374.22 -

7.352% 7.271% 3.923% 6.170% 7.037% 4.368% 1.598% 6.549% 0.000% 0.000%

11,917,553.08 (6,708,366.53) 85,749,396.00

7,605,154.92 2,940,051.73 1,199,840.61 344,126.89 2,794,221.97 767,428.31 19,139.03 16,424.89 15,686,388.36 (5,007,655.37) 79,041,029.47

79,041,029.47

74,033,374.11

313,772,485.54 -

MAJOR EXPENDITURE SOURCES • • • • •

• • •

Salaries & Fringe Benefits Contractual Service Commodities Internal Services Non-Departmental Capital Outlay Interest on Debt Transfers Out

NONRECURRING EXPENDITURES Capital projects are the largest nonrecurring expenditure item •

•

Work with department heads and engineers to develop estimates for projects. Explain the importance of cash flow to the success of the capital expense.

Estimated Average Applied Against the Budget OAKLAND COUNTY - GENERAL FUND PROJECTED CASH FLOW - FROM 3 YEAR HISTORY FY 2000

Revenue

MO 1 Ave % 0.010% 0.000% 0.465% 10.074% 2.779% 6.068% 1.386% 1.075%

Projection 17,546.02 1,331.09 19,638.71 958,566.77 3,905,467.79 160,485.46 145,913.41

MO 2 Ave % 0.078% 5.817% 1.420% 10.442% 12.834% 7.805% 1.591% 6.450%

Projection 139,925.60 3,792.41 4,064.57 20,355.78 4,427,567.33 5,023,188.45 184,188.85 875,650.01

Taxes Special Assessments Federal Grants State Grants Intergovernmental Revenue Charges for Services Use of Money Other Revenues

179,384,000.00 65,200.00 286,181.00 194,950.00 34,499,021.00 64,357,953.00 11,577,400.00 13,576,093.54

Transfers In

9,831,687.00 313,772,485.54

0.002%

237.31 5,209,186.56

0.000%

10,678,732.99

103,441,354.00 40,435,928.00 30,582,342.32 5,577,506.93 39,705,331.77 17,568,402.73 1,197,447.56 250,800.00 75,013,372.23

6.213% 6.370% 4.527% 3.606% 1.903% 1.923% 0.100% 93.451% 0.000%

6,427,062.10 2,575,827.64 1,384,433.02 201,134.58 755,747.85 337,775.53 1,198.14 234,374.22 -

7.352% 7.271% 3.923% 6.170% 7.037% 4.368% 1.598% 6.549% 0.000% 0.000%

11,917,553.08 (6,708,366.53) 85,749,396.00

7,605,154.92 2,940,051.73 1,199,840.61 344,126.89 2,794,221.97 767,428.31 19,139.03 16,424.89 15,686,388.36 (5,007,655.37) 79,041,029.47

79,041,029.47

74,033,374.11

TOTAL

Salaries Fringe Benefits Contractual Services Commodities Internal Services Non-Departmental Capital Outlay Other Expenses Interest on Debt Transfers Out TOTAL NET AUDITED 9/30/99 BEGINNG BAL ENDING ESTIMATED CASH

313,772,485.54 -

Core Cash

The Theory • Identify an optimal allocation of Funds • Funds that need to remain short-term for upcoming cash flow needs (disbursements) • Balances that could be invested longer-term • Management of short-term funds based on cash flow analysis • Management of longer-term funds (Core Cash) based on market conditions and relationship to the current yield curve

GENERAL RULE IN CASH FORECASTING A general rule used in forecasting is to only forecast those categories that account for 5% or more of the budget.

WATCH OUT FOR CHANGES ! Whenever you are forecasting and using historical data, remember that changes in the economy, state law, user fees, etc., will force you to re-think and adjust your past experience and/or logic so you don’t get a false reading on future collections and/or disbursements. 

As an example, the shift to a July County tax bill affected/changed our collection patterns. i.e. the history was now useless.



Changes in the State’s Revenue Sharing Payment methods change the flow of those as compared to the past

THE FOLLOWING TIPS SHOULD HELP YOU IMPROVE YOUR FORECAST: Try to be accurate and err on the conservative side. Concentrate on forecasting major items. Keep your forecast simple. Retain all your forecasting documentation.

REVIEWING YOUR CASH FLOW FORECAST Compare actual to the projected amount. Identify any large variances. Adjust assumptions as needed.

Actual vs. Projected OAKLAND COUNTY - GENERAL FUND PROJECTED CASH FLOW VERSUS ACTUAL FY 2000

Revenue Taxes Special Assessments Federal Grants State Grants Intergovernmental Revenue Charges for Services Use of Money Other Revenues Transfers In TOTAL Salaries Fringe Benefits Contractual Services Commodities Internal Services Non-Departmental Capital Outlay Other Expenses Interest on Debt Transfers Out TOTAL Beginning Cash/Invest Bal.

179,384,000.00 65,200.00 286,181.00 194,950.00 34,499,021.00 64,357,953.00 11,577,400.00 13,576,093.54 9,831,687.00 313,772,485.54 103,441,354.00 40,435,928.00 30,582,342.32 5,577,506.93 39,705,331.77 17,568,402.73 1,197,447.56 250,800.00 75,013,372.23 313,772,485.54 85,749,396.00

Projection 17,546.02 1,331.09 19,638.71 958,566.77 3,905,467.79 160,485.46 145,913.41 237.31 5,209,186.56

MO 1 Actual 43,591.61 41,040.00 102,227.88 3,864,176.91 (420,416.96) 426,503.01 4,057,122.45 6,062,859.35 2,272,103.26 2,229,412.15 221,404.26 470,934.18 175,608.00 -

Diff 26,045.59 (1,331.09) 21,401.29 (856,338.89) (41,290.88) (580,902.42) 280,589.60 (237.31) (1,152,064.11)

6,427,062.10 2,575,827.64 1,384,433.02 201,134.58 755,747.85 337,775.53 1,198.14 234,374.22 11,917,553.08

11,432,321.20

(364,202.75) (303,724.38) 844,979.13 20,269.68 (284,813.67) (162,167.53) (1,198.14) (234,374.22) (485,231.88)

(6,708,366.53)

(7,375,198.75)

(666,832.22)

79,041,029.47 78,374,197.25

GET GOING ON FORECASTING Like exercising, getting started is the hardest part Once the forecast is laid out it’s easy to keep updated The benefits are worth the work

CASH FLOW ANALYSIS

WHAT ARE WE GOING TO COVER? •

What is Cash Flow Analysis?

•

The Current Investment Portfolio and its Inter-Action with the Cash Flow Forecast

•

Cash Flow Analysis

TEN STEPS TO PRUDENT INVESTING 









Identify your objectives, constraints, preferences and capabilities.



Establish an investment strategy.



Determine your investment outlook.



Select investment instruments.



Monitor and report the market and investment results.



Adjust and rebalance the portfolio.

Develop a written investment policy. Develop a system of internal controls and administrative policies.

Forecast your cash flow. Analyze the yield curve.

CASH FLOW ANALYSIS Cash flow analysis • Combine cash flow forecast with an investment maturity schedule • Help you see: • periods of time where you may be over or under invested • where you may need to borrow funds to meet cash spending requirements.

SAMPLE CASH FLOW FORECAST

SAMPLE CASH FLOW FORECAST

THE INVESTMENT PORTFOLIO

TEN STEPS TO PRUDENT INVESTING 

 

Identify your objectives, constraints, preferences and capabilities.



Establish an investment strategy.



Determine your investment outlook.



Select investment instruments.



Monitor and report the market and investment results.



Adjust and rebalance the portfolio.

Develop a written investment policy. Develop a system of internal controls and administrative policies.



Forecast your cash flow.



Analyze the yield curve.

Sample Investment Portfolio

Investment Portfolio 450,000

400,000

400,000

350,000

300,000

Par Value

300,000

250,000

300,000

200,000

200,000

150,000

100,000

100,000

50,000

0

Oct-11

Nov-11

Dec-11

Months

Mar-12

Jun-12

Investment by Type

Investment Pool 23%

CD 77%

Investments by Issuer

Bank A 31%

Bank C 38%

Bank B 31%

CASH FLOW ANALYSIS OVERLAY OF FORECASTED CASH FLOWS WITH INVESTMENT POSITIONS AND RELATED MATURITY DATES

Net Cash Position From Operations 4,000,000

Dollars

3,000,000 2,000,000 1,000,000 (1,000,000)

1

2

3

4

5

6

7

(2,000,000) Month #

8

9

10

11

12

Investment Position Before Cash Flow Forecast 25-Oct-11

Investment Type

Issuer

Purchas e Maturity

Par

%

Value

Portfolio

Date

Date

Yield to Days

Maturity

Investment Pool Bank A

300,000

23%

Oct-11

Oct-11

1

0.50%

CD

Bank B

400,000

31% Sep-11

Nov-11

69

1.25%

CD

Bank A

100,000

8% Sep-11

Dec-11

107

1.30%

CD

Bank C

300,000

23% Sep-11

Mar-12

183

1.50%

CD

Bank C

200,000

15% Sep-11

Jun-12

274

1.60%

Total

1,300,000

450,000

400,000

400,000

Investment Portfolio

350,000

300,000

Par Value

300,000 250,000

300,000

200,000

200,000

150,000 100,000

100,000

50,000 0

Oct-11

Nov-11

Months

Dec-11

Mar-12

Jun-12

COMBINED CASH FLOW

Net Cash Flows & Investment Maturities

3,500,000 3,000,000 2,500,000

Cash from Operations

Dollars

2,000,000

Investment Maturities Net Cash Position

1,500,000 1,000,000 500,000 0 -500,000 -1,000,000 -1,500,000

Month

WHAT DOES THIS OVERLAY TELL US? Identifies months where investments are coming due where you actually have a positive net cash flow Identifies months where the investment coming due was not large enough to meet cash needs. Helps you identify excess cash so you can invest the excess to periods where you need additional funding.

REVISED OR RE-BALANCED INVESTMENT PORTFOLIO

Cash Flow After Rebalance of Investment portfolio 3,500,000 3,000,000 2,500,000 2,000,000

Dollars

1,500,000 1,000,000 500,000 0 -500,000 -1,000,000 -1,500,000

Cash Flow From Operations Adjusted Investment portfolio Orginal Investment Portfolio

Before Cash Flow Investment by Type Investment Pool 23%

CD 77%

Adjusted Investment Portfolio Investment by Type Investment Pool 6% TREAS CD 39%

10%

GSE 37%

CP 8%

Before Cash Flow Investments by Issuer

Bank A 31%

Bank C 38%

Bank B 31%

After Cash Flow by Issuer US Treas GE 10% 8% Freddie 8% FHLB 11% Fannie 19%

Bank A Bank B 8% 8% Bank C 8% Bank D 8% Bank E Bank F 8% 4%

CASH FLOW FORECASTING AND ANALYSIS

Keith Sawdon Voice: 248 841-2535 e-mail: [email protected]

Any Town in Michigan Cash Flow Forecast Fiscal Year 20XX Current Budget

Month Amended Revenue Budget

Property Taxes State Grants Charges for Services

Jul-13

1 %

Month

Aug-13

Month

2 %

6,000,000

60%

15%

900,000

800,000

8%

15%

120,000

10%

Sep-13

Month

3 % 600,000 -

Oct-13

Month

4

Nov-13

5

%

%

60%

3,600,000

-

5%

40,000

-

15%

120,000

2,000,000

20%

15%

300,000

15%

300,000

15%

300,000

10%

200,000

10%

200,000

Investment Income

500,000

5%

8%

41,500

8%

41,500

8%

41,500

8%

41,500

8%

41,500

Other Revenues

250,000

3%

8%

20,750

8%

20,750

8%

20,750

8%

20,750

8%

20,750

50,000

1%

400,000

4%

50%

200,000

Gain on Exchange of Assets Transfers In

-

10,000,000 100%

1,582,250

-

-

-

-

-

-

962,250

4,002,250

-

262,250

382,250

Expenditures Salaries & Wages

5,000,000

50%

8%

415,000

8%

415,000

8%

415,000

9%

457,500

8%

415,000

Fringe Benefits

2,250,000

23%

9%

191,250

8%

186,750

8%

186,750

8%

186,750

8%

186,750

Professional/Contractual Services

750,000

8%

10%

75,000

10%

75,000

8%

62,475

8%

62,475

5%

37,500

Commodities

500,000

5%

8%

41,650

8%

41,650

8%

41,650

8%

41,650

8%

41,650

Interest on Debt

500,000

5%

50%

250,000

1,000,000

10%

10%

50,000

Captial Outlay Trandfer Out

10,000,000

Cash from Operations

0.00

0% 100%

-

15%

150,000 -

25%

250,000 -

1,022,900

868,400

955,875

559,350

93,850

3,046,375

5%

-

-

50,000

-

-

-

798,375

680,900

(536,125)

(298,650)

Beginning Cash & Investments

1,000,000

1,000,000

1,559,350

1,653,200

4,699,575

4,163,450

Ending Cash & Investments

1,000,000

1,559,350

1,653,200

4,699,575

4,163,450

3,864,800

Any Town in Michigan Cash Flow Forecast Fiscal Year 20XX Month

Dec-13

Month

6 % 5%

Jan-14

Month

7 % 10%

-

Month

Mar-14

Month

9

%

300,000 -

Feb-14

8

Apr-14

Month

10

%

May-14

%

% 5%

300,000

Total

-

5%

300,000

0%

80,000

5%

40,000

5%

40,000

5%

40,000

25%

200,000

15%

120,000

800,000

-

5%

100,000

10%

200,000

10%

200,000

10%

200,000

2,000,000

-

-

Jun-14

12

%

-

Month

11

-

6,000,000

8%

41,500

8%

41,500

8%

41,500

8%

41,500

8%

41,500

8%

41,500

9%

43,500

500,000

8%

20,750

8%

20,750

8%

20,750

8%

20,750

8%

20,750

8%

20,750

9%

21,750

250,000

100%

50,000 50%

200,000

400,000

585,250

10,000,000

-

-

-

-

-

-

-

-

-

-

362,250

142,250

402,250

202,250

-

302,250

-

812,250

50,000

8%

415,000

8%

415,000

10%

475,000

7%

325,000

8%

415,000

8%

415,000

8%

422,500

5,000,000

8%

186,750

8%

186,750

8%

186,750

8%

186,750

8%

186,750

8%

186,750

9%

191,250

2,250,000

5%

37,500

15%

112,500

8%

62,475

8%

62,475

5%

37,650

8%

62,475

8%

62,475

750,000

8%

41,650

8%

41,650

8%

41,650

8%

41,650

8%

41,650

8%

41,500

8%

42,000

500,000

-

50%

250,000

-

500,000

680,900 (318,650)

1,005,900 (863,650)

-

-

-

-

-

-

5%

50,000 -

765,875

615,875

731,050

(363,625)

(413,625)

(428,800)

10%

100,000 805,725 6,525

35%

350,000 1,068,225 (482,975)

1,000,000 10,000,000 0.00

3,864,800

3,546,150

2,682,500

2,318,875

1,905,250

1,476,450

1,482,975

1,000,000

3,546,150

2,682,500

2,318,875

1,905,250

1,476,450

1,482,975

1,000,000

1,000,000

Jul-11

Cash from Operations

559,350

Aug-11

Sep-11

93,850 3,046,375

Cash From Investments Net Cash Position

559,350

93,850 3,046,375

Oct-11

Nov-11

Dec-11

Jan-12

Feb-12

Mar-12

-536,125

-298,650

-318,650

-863,650

-363,625

-413,625

300,000

400,000

100,000

-236,125

101,350

-218,650

300,000 -863,650

-363,625

-113,625

Apr-12

-428,800

May-12

6,525

Jun-12

-482,975

Total

0

200,000 1,300,000 -428,800

6,525

-282,975 1,300,000

Investment Position Before Cash Flow Forecast 30-Jun-07

Investment Type Investment Pool CD CD CD CD Total

Issuer Bank A Bank B Bank A Bank C Bank C

Par Value 300,000 400,000 100,000 300,000 200,000 1,300,000

% Purchase Portfolio Date 23% Jun-07 31% Jun-07 8% Sep-06 23% Sep-06 15% Sep-06

Maturity Date Jul-07 Aug-07 Dec-06 Mar-07 Jun-07

Days 1 62 92 182 273

Yield to Maturity 4.85% 4.95% 5.10% 5.25% 5.30%

Par Value

Investment Portfolio 450,000 400,000 350,000 300,000 250,000 200,000 150,000 100,000 50,000 0

400,000 300,000 300,000

Jul-07

200,000 100,000 Aug-07

Dec-06 Months

Mar-07

Jun-07

Investment Type CD CD CD CD Sub-Total Investment Pool Sub-Total Total

Issuer Bank B Bank A Bank C Bank C Bank A

Par Value 400,000 100,000 300,000 200,000 1,000,000 300,000 300,000 1,300,000

% Purchase Portfolio Date 31% Jun-07 8% Sep-06 23% Sep-06 15% Sep-06 23%

Investment by Type1000000 CD Investment Pool

300000

Investment Pool 23%

CD 77%

Jun-07

Maturity Date Aug-07 Dec-06 Mar-07 Jun-07 Jul-07

62 92 182 273

Yield to Maturity 4.95% 5.10% 5.25% 5.30%

1

4.85%

Days

Investment Type Investment Pool CD CD CD CD Total

Par Value

Issuer Bank A Bank A Bank B Bank C Bank C

400,000 400,000 500,000 1,300,000

% Purchase Portfolio Date 0% Jun-07 31% Sep-06 31% Jun-07 38% Sep-06 0% Sep-06

Investments by Issuer

Bank A 31%

Bank C 38%

Bank B 31%

Maturity Date Jul-07 Dec-06 Aug-07 Mar-07 Jun-07

Days 1 92 62 182 273

Yield to Maturity 4.85% 5.10% 4.95% 5.25% 5.30%