Cash Flow Forecasting & AnalysisCGFOA 9.3.2020

2

Who is Ehlers? Fully Integrated Services

3

About Ehlers’ Investments• SEC Registered Investment Adviser (est. 2007)

• More than $1.5 billion AUM

• 145+ clients

• Focus = separately managed fixed income solutions

Bond proceeds investment

General funds investment

Cash flow forecasting

Banking RFP services

Objectives

9/3/2020 4

• What is a Cash Forecast?

• Why is it important and beneficial?

• How to best utilize a Cash Forecast.• Analysis & Strategy

• Ways to construct a Cash Forecast.• Examples

5

Best Practice:

GFOA recommends that governments perform ongoing cash forecasting to ensure that they have sufficient cash liquidity to meet disbursement requirements and limit idle cash.

The cash forecast period should be at least a 12-month rolling period, as opposed to a fiscal year basis. The forecast within this rolling period should be divided up in at least monthly sections for most governments, or weekly or daily for larger and more complex governments.

Cash Forecasting and Strategy

What is a Cash Forecast?

9/3/2020 6

What is a Cash Forecast?

9/3/2020 7

Cash flow forecasting is the process of predicting cash flow, at least on a monthly basis, for the purpose of managing liquidity needs and for investment control.

Assumptions

• Cash flows for the current year will follow a pattern similar to that of the previous years

• 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)

Components of Cash Forecasts

• Cash Balances

• Investment Balances

• Projections of Receipts

• Projections of Disbursements

• Typical and Atypical

The Cash Flow Forecast Process• Review historical cash flows (typically 2-5 years), your

knowledge to determine cash flow percentages by month

• Determine if there are any “statistically significant” seasonal changes in monthly flows

• Identify any growth trends

• Adjust your percentage cash flow data and apply it to your current year budget to project your cash flows

• Review projected flows with actual flows

The Forecasting Method Used will Depend On:

• The time and resources you have available forforecasting

• The amount of funds you’re working with

• Your level of commitment to forecasting

• Ability to get accurate information

Cash Forecasting and Strategy

12

Benefits of Cash Forecasting• Ensure appropriate liquidity

• Improve investment earnings

• Helps from being too “short” and losing opportunities with “longer” investment options

• Helps minimize borrowing needs by identifying any cash shortfalls

• Provides Peace of Mind

Tools Needed to Build Your Cash Flow Forecast

• Beginning cash/investment balances

• Historical or professional experience of cash receipts and disbursements

• Apply historical data and experience against current fiscal year’s budget

• Capital budget spending projections

Recurring and Nonrecurring Receipts andDisbursements

Receipts and disbursements that flow in and out can be characterized as either recurring or 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 andwages.

• Nonrecurring flows are irregular and unpredictable. The timing andamounts are usually not known in advance.

An example of nonrecurring flows include receipts from the sale of assets. Nonrecurring disbursements include items like capital projectexpenditures.

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

Major Expenditure Sources

• Salaries & Fringe Benefits

• Contractual Service

• 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 engineersto develop estimates for projects.

• Explain the importance of cash flow to the success of the capital expense.

A Forecast Identifies Short & Long Term Needs

• Identifies an optimal allocation of Funds• Funds needed short-term to meet your cash flow

needs for operations• Funds that could be invested longer-term

• The management of short-term funds are based on cash flow requirements

• The management of longer-term funds are based on market conditions and the current yield curve

General Rule in Cash Forecasting• Best to forecast only those categories that account for

5% or more of the budget

• Combine items that fall outside of the 5% range into an “Other” category

Review Your Cash Flow Forecast

• Compare actual cash flow to the projected amount.

• Identify any large variances.

• Adjust assumptions as needed.

Watch Out for Changes!

-1,500,000

-500,000

-1,000,000

2,500,000

2,000,000

1,500,000

1,000,000

500,000

0

3,000,000

3,500,000

1 2 3 4 5 6 7 8 9 10 11 12

Projected Actual

Projected vs. Actual

Connecting Cash Flow and Investments

• Comparing the current investment holdings with the cash flow forecast to determine cash over/under funding to maximize cash and investment returns to create a reliable and predictable source of revenue.

Cash Flow Analysis and Investments

Overlay of forecasted cash flows with investment positions and related maturity dates

Will help you see:

• Periods of time where you may be over or under invested

• Where you may need to borrow funds to meet cash spendingrequirements

What will does an Overlay Tell Us?

• Identifies months where the investment coming due was not large enough to meet cash flow needs

• Identifies months where investments are coming due but aren’t needed because of positive cash flow

• Helps you identify excess cash so you can invest the excess to periods where you need additional funding.

• Builds a Strategy and a Story around your Cash and Investment decisions

1 2 3 4 5 6 7 8 9 10 11 12

Dol

lars

(1,000,000)Month

(2,000,000)

4,000,000

Net Cash Position From Operations3,000,000

2,000,000

1,000,000

-

Cash Flow Analysis and Investments

300,000

400,000

0

50,000

100,000100,000

150,000

250,000

200,000200,000

300,000

350,000300,000

400,000

450,000

Oct-15 Nov-15 Dec-15 Mar-16 Jun-16

Par V

alue

Months

Investment Portfolio

Cash Flow Analysis and Investments

Example - Combined Cash Flow

Jul-15 Aug-15 Sep-15 Oct-15 Nov-15 Dec-15 Jan-16 Feb-16 Mar-16 Apr-16 May-16 Jun-16 Total

Cash from Operations 559,350 93,850 3,046,375 -536,125 -298,650 -318,650 -863,650 -363,625 -413,625 -428,800 6,525 -482,975 0

Cash FromInvestments 300,000 400,000 100,000 300,000 200,000 1,300,000

Net Cash Position 559,350 93,850 3,046,375 -236,125 101,350 -218,650 -863,650 -363,625 -113,625 -428,800 6,525 -282,975 1,300,000

Cash Flow Forecast example

5,000,000

5,500,000

J A S N A M J

Cash Flow Projection

O

Core Cash

D J F M

Ending Cash & Investments

4,500,000

4,000,000

3,500,000

3,000,000

2,500,000

2,000,000Core Cash

1,500,000

1,000,000

Examples - Large Cash Balances

$-

$2,000,000.00

$4,000,000.00

$6,000,000.00

$8,000,000.00

$10,000,000.00

$12,000,000.00

$14,000,000.00

$16,000,000.00

Jan-

18

Feb-

18

Mar

-18

Apr-

18

May

-18

Jun-

18

Jul-1

8

Aug-

18

Sep-

18

Oct

-18

Nov

-18

Dec-

18

Jan-

19

Feb-

19

Mar

-19

Apr-

19

May

-19

Jun-

19

Jul-1

9

Aug-

19

Sep-

19

Oct

-19

Nov

-19

Dec-

19

Jan-

20

Outflow Compared to Avg. Monthly Balance

Avg Combined Balances Largest Debit

Examples - Simplified Ins and Outs

Examples - Ins and Outs with Net Flow

-4,000,000.00

-3,000,000.00

-2,000,000.00

-1,000,000.00

0.00

1,000,000.00

2,000,000.00

3,000,000.00

4,000,000.00

5,000,000.00

6,000,000.00

7,000,000.00

Jan-

18

Feb-

18

Mar

-18

Apr-

18

May

-18

Jun-

18

Jul-1

8

Aug-

18

Sep-

18

Oct

-18

Nov

-18

Dec-

18

Jan-

19

Feb-

19

Mar

-19

Apr-

19

May

-19

Jun-

19

Jul-1

9

Aug-

19

Sep-

19

Oct

-19

Nov

-19

Dec-

19

Jan-

20

Net Cash Flow - Max

Purchases Redemptions Net Cash Flow

Examples – Cash flow with Standard Deviation

-

20,000,000.00

40,000,000.00

60,000,000.00

80,000,000.00

100,000,000.00

120,000,000.00

Jan Feb March April May Jun Jul Aug Sep Oct Nov Dec

City of ABC: 95% Confidence Interval of Cash Reserves

+2 Standard Deviation -2 Standard Deviation 2020 Forcast at Average Growth

Examples – Historical Cash Flow

$-

$20,000,000.00

$40,000,000.00

$60,000,000.00

$80,000,000.00

$100,000,000.00

$120,000,000.00

$140,000,000.00

$160,000,000.00

Jan Feb March April May Jun Jul Aug Sep Oct Nov Dec

City of ABC - Historical Total Assets

Restricted Cash 2016 2017 2018 2019

Examples - Complex

Examples – Live Examples

Tips to Help 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.

Conclusion• Like exercising, getting started is the hardest part of cash

flow forecasting

• Once you begin to gather your historical data and build the model, the process tends to flow smoothly

• Once built, the forecast model is easier to keep updated and only requires a relatively small amount of time to keep it up to date

• Find a system that works for you!

• The benefits of a cash flow forecast outweigh the effort!

Thank you

9/3/2020 41

Ryan Miles, CPFIM

Senior Investment Advisor

rmiles@ehlers-inc.com

303.802.2311