establishing a cash flow model - · pdf fileestablishing a cash flow model emily a. upshaw,...

ESTABLISHING A CASH FLOW MODEL

EMILY A. UPSHAW, CPA

VALLEY VIEW CONSULTING, LLC

Government Finance Officers Association of Texas

Fall Conference 2017

Objectives:

• Understand the importance of developing a cash flow model

• Discuss laddering of investments • Learn approach to building a cash flow model for

operating funds

• Discuss example cash flow model and various methods of analysis

2

Questions:

• Would you try to build a house without a plan?

• Would you try to drive to an address across the country without a map?

• Then why would you try to build an effective portfolio without a cash flow plan??

3

Cash Flow Information is Important

• In most cases where portfolios imploded or caused financial duress, it was due to a failure to consider the cash flow needs of the organization.

• Cash flow models can set the portfolio on a firm foundation and should be the cornerstone for building the portfolio structure.

4


• How do Cash Flow Models Assist the Portfolio Manager?

– Historical Perspective to Cash Needs

– Identify Trends

– Tool for Enhancing Investment Income

5

Note: Important Distinction

• Operating funds

– Forecasting - the use of historic data to determine the direction of future trends (Investopedia)

• Project-based funds

– Draw schedule required for anticipated spending

6


Use Cash Flow to Ladder the Portfolio

• Importance of Laddering

– Minimizes Impact of Changing Interest Rates

– Diversifies Risk

– Results in a More Dependable Return

7

Yield Curve Opportunities

Rate

Time

Normal Yield Curve Offers Higher Rates for Longer Term Maturities

Cash Flow Model Identifies Potential Maturities Along the Yield Curve

8

Where Do I Start?

• Data is All Around You – Bank Statements

– Portfolio Reports

– Accounting Summary Reports

• How Much Time Will It Take? – Set Up and First Entries might take a day

– Subsequent Maintenance should only take a few minutes/hours depending on the number of portfolios/accounts

9

Analysis One – What is the History?

• Cash Flow from Cash/Investment Perspective – Bank Statements

– Monthly Portfolio Reports

• Collect Statements/Reports for Last Three (or more) Years

• On a spreadsheet, record the month-end balances for each bank account and the portfolio ending book values

10

A AW AX AY AZ BA BB BC BD

1 Sample Client

2 Operating Fund3

4 Feb-16 Mar-16 Apr-16 May-16 Jun-16 Jul-16 Aug-16 Sep-16

5

6 Depository Bank - Ending Balance 84,502,103.00 80,804,472.00 64,080,263.00 62,030,044.00 35,395,677.00 28,511,374.00 21,835,096.00 10,561,234.00

7 Checking Fund 001 (ending) 476.88 477.05 477.11 477.17 477.23 369,177.29 487.93 492.53

8 Checking Compensating Balance (ending) 25,000,000.00 25,000,000.00 25,000,000.00 25,000,000.00 25,000,000.00 25,000,000.00 25,000,000.00 25,000,000.00

9 TexPool (ending) 10,003,113.94 10,007,284.31 10,011,452.92 10,015,785.97 10,020,269.29 10,024,831.81 10,028,013.75 10,031,145.02

10 TexStar (ending) 5,014,531.44 5,039,220.90 5,059,621.20 5,079,658.57 5,094,610.94 5,106,141.73 5,214,904.10 5,222,688.76

11 Bank-MMA (ending) 20,004,520.54 20,013,865.12 20,023,214.06 20,033,219.24

12 Securities Portfolio (ending) 35,849,620.88 37,900,416.20 43,800,570.92 44,526,549.70 49,978,824.16 55,610,515.68 61,457,920.68 70,016,376.98

13

14

15 Ending Totals: 160,369,846 158,751,870 147,952,385 146,652,515 145,494,379 144,635,906 143,559,637 140,865,157

Ending Totals = sum of each column

Graph Row 15

11

$0

$20,000,000

$40,000,000

$60,000,000

$80,000,000

$100,000,000

$120,000,000

$140,000,000

$160,000,000

$180,000,000

Operating Fund Historical Portfolio Balances

12

Analysis Two – How Much?

• Portfolio Allocations – Three Buckets

1. Long-term (Core)

‒ Maturity beyond 1-year

2. Intermediate

‒ Maturity between 3-months and 1-year

3. Liquidity

‒ Maturity less than 3-months

13

$0

$20,000,000

$40,000,000

$60,000,000

$80,000,000

$100,000,000

$120,000,000

$140,000,000

$160,000,000

$180,000,000


14


1 Sample Client

2 Operating Fund3


5




9 TexPool (ending) 10,003,113.94 10,007,284.31 10,011,452.92 10,015,785.97 10,020,269.29 10,024,831.81 10,028,013.75 10,031,145.02

10 TexStar (ending) 5,014,531.44 5,039,220.90 5,059,621.20 5,079,658.57 5,094,610.94 5,106,141.73 5,214,904.10 5,222,688.76

11 Bank-MMA (ending) 20,004,520.54 20,013,865.12 20,023,214.06 20,033,219.24


13

14

15 Ending Totals: 160,369,846 158,751,870 147,952,385 146,652,515 145,494,379 144,635,906 143,559,637 140,865,157

16 Portfolio Monthly Change 7,340,893 (1,617,976) (10,799,485) (1,299,870) (1,158,136) (858,474) (1,076,269) (2,694,480)

17 Portfolio Declines 0 1,617,976 10,799,485 1,299,870 1,158,136 858,474 1,076,269 2,694,480

18 Portfolio Increases 7,340,893 0 0 0 0 0 0 0

19 Graph table: Feb-16 Mar-16 Apr-16 May-16 Jun-16 Jul-16 Aug-16 Sep-16

20 Core base 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000

21 Portfolio Balance 86,314 84,939 75,760 74,655 73,670 72,941 72,026 69,735

22 Liquidity Base @ 15% 24,055 23,813 22,193 21,998 21,824 21,695 21,534 21,130

23

24 Liquidity Base 15%

Build the Graph Table to begin to allocate the portfolio into maturity buckets

{

1. Core 2. Intermediate 3. Liquidity

1 2

3

15


1 Sample Client

2 Operating Fund3


5




9 TexPool (ending) 10,003,113.94 10,007,284.31 10,011,452.92 10,015,785.97 10,020,269.29 10,024,831.81 10,028,013.75 10,031,145.02

10 TexStar (ending) 5,014,531.44 5,039,220.90 5,059,621.20 5,079,658.57 5,094,610.94 5,106,141.73 5,214,904.10 5,222,688.76

11 Bank-MMA (ending) 20,004,520.54 20,013,865.12 20,023,214.06 20,033,219.24


13

14

15 Ending Totals: 160,369,846 158,751,870 147,952,385 146,652,515 145,494,379 144,635,906 143,559,637 140,865,157





20 Core base 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000


22 Liquidity Base @ 15% 24,055 23,813 22,193 21,998 21,824 21,695 21,534 21,130

23


1. Core – approximately 50% of the portfolio’s lowest historic balance

16

$0

$20,000,000

$40,000,000

$60,000,000

$80,000,000

$100,000,000

$120,000,000

$140,000,000

$160,000,000

$180,000,000


November 2013 was portfolio’s lowest balance at $110MM 17


1 Sample Client

2 Operating Fund3


5




9 TexPool (ending) 10,003,113.94 10,007,284.31 10,011,452.92 10,015,785.97 10,020,269.29 10,024,831.81 10,028,013.75 10,031,145.02

10 TexStar (ending) 5,014,531.44 5,039,220.90 5,059,621.20 5,079,658.57 5,094,610.94 5,106,141.73 5,214,904.10 5,222,688.76

11 Bank-MMA (ending) 20,004,520.54 20,013,865.12 20,023,214.06 20,033,219.24


13

14

15 Ending Totals: 160,369,846 158,751,870 147,952,385 146,652,515 145,494,379 144,635,906 143,559,637 140,865,157





20 Core base 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000


22 Liquidity Base @ 15% 24,055 23,813 22,193 21,998 21,824 21,695 21,534 21,130

23


Core can be constant – copy value across the row

Formula: = $AW$20 18


1 Sample Client

2 Operating Fund3


5




9 TexPool (ending) 10,003,113.94 10,007,284.31 10,011,452.92 10,015,785.97 10,020,269.29 10,024,831.81 10,028,013.75 10,031,145.02

10 TexStar (ending) 5,014,531.44 5,039,220.90 5,059,621.20 5,079,658.57 5,094,610.94 5,106,141.73 5,214,904.10 5,222,688.76

11 Bank-MMA (ending) 20,004,520.54 20,013,865.12 20,023,214.06 20,033,219.24


13

14

15 Ending Totals: 160,369,846 158,751,870 147,952,385 146,652,515 145,494,379 144,635,906 143,559,637 140,865,157





20 Core base 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000


22 Liquidity Base @ 15% 24,055 23,813 22,193 21,998 21,824 21,695 21,534 21,130

23


3. Liquidity – target percentage of cash equivalents for the total portfolio

Input target percentage in Cell AW24

3

19


1 Sample Client

2 Operating Fund3


5




9 TexPool (ending) 10,003,113.94 10,007,284.31 10,011,452.92 10,015,785.97 10,020,269.29 10,024,831.81 10,028,013.75 10,031,145.02

10 TexStar (ending) 5,014,531.44 5,039,220.90 5,059,621.20 5,079,658.57 5,094,610.94 5,106,141.73 5,214,904.10 5,222,688.76

11 Bank-MMA (ending) 20,004,520.54 20,013,865.12 20,023,214.06 20,033,219.24


13

14

15 Ending Totals: 160,369,846 158,751,870 147,952,385 146,652,515 145,494,379 144,635,906 143,559,637 140,865,157





20 Core base 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000


22 Liquidity Base @ 15% 24,055 23,813 22,193 21,998 21,824 21,695 21,534 21,130

23


Formula Cell AW22: = ( AW15 * $AW$24 ) / 1000

Copy across the row

20


1 Sample Client

2 Operating Fund3


5




9 TexPool (ending) 10,003,113.94 10,007,284.31 10,011,452.92 10,015,785.97 10,020,269.29 10,024,831.81 10,028,013.75 10,031,145.02

10 TexStar (ending) 5,014,531.44 5,039,220.90 5,059,621.20 5,079,658.57 5,094,610.94 5,106,141.73 5,214,904.10 5,222,688.76

11 Bank-MMA (ending) 20,004,520.54 20,013,865.12 20,023,214.06 20,033,219.24


13

14

15 Ending Totals: 160,369,846 158,751,870 147,952,385 146,652,515 145,494,379 144,635,906 143,559,637 140,865,157





20 Core base 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000


22 Liquidity Base @ 15% 24,055 23,813 22,193 21,998 21,824 21,695 21,534 21,130

23


2. Intermediate – remainder of the portfolio total

Formula for AW21: = (AW15 / 1000) – (AW20 + AW22)

2

21


1 Sample Client

2 Operating Fund3


5




9 TexPool (ending) 10,003,113.94 10,007,284.31 10,011,452.92 10,015,785.97 10,020,269.29 10,024,831.81 10,028,013.75 10,031,145.02

10 TexStar (ending) 5,014,531.44 5,039,220.90 5,059,621.20 5,079,658.57 5,094,610.94 5,106,141.73 5,214,904.10 5,222,688.76

11 Bank-MMA (ending) 20,004,520.54 20,013,865.12 20,023,214.06 20,033,219.24


13

14

15 Ending Totals: 160,369,846 158,751,870 147,952,385 146,652,515 145,494,379 144,635,906 143,559,637 140,865,157





20 Core base 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000


22 Liquidity Base @ 15% 24,055 23,813 22,193 21,998 21,824 21,695 21,534 21,130

23


Graph the entire table

“Stacked Area Chart”

22

Using the Historical Balances Graph

• Completed Graph Shows Three Portfolio Divisions

– Liquidity Component – the amount to deposit in Pools/MMA/MMMF to pay day-to-day bills

– Intermediate Component – the amount used to ladder to address projected obligations during the year

– Core Component – The longer-term foundation of the portfolio

24

Analysis Three – When?

• Targeting Maturity Needs

– Use a historical perspective

• Which Months Need Maturities and Which Do Not?

25


1 Sample Client

2 Operating Fund3


5




9 TexPool (ending) 10,003,113.94 10,007,284.31 10,011,452.92 10,015,785.97 10,020,269.29 10,024,831.81 10,028,013.75 10,031,145.02

10 TexStar (ending) 5,014,531.44 5,039,220.90 5,059,621.20 5,079,658.57 5,094,610.94 5,106,141.73 5,214,904.10 5,222,688.76

11 Bank-MMA (ending) 20,004,520.54 20,013,865.12 20,023,214.06 20,033,219.24


13

14

15 Ending Totals: 160,369,846 158,751,870 147,952,385 146,652,515 145,494,379 144,635,906 143,559,637 140,865,157


Formula Cell AX16: = AX15 - AW15

Then copy formula across the row

26


1 Sample Client

2 Operating Fund3


5




9 TexPool (ending) 10,003,113.94 10,007,284.31 10,011,452.92 10,015,785.97 10,020,269.29 10,024,831.81 10,028,013.75 10,031,145.02

10 TexStar (ending) 5,014,531.44 5,039,220.90 5,059,621.20 5,079,658.57 5,094,610.94 5,106,141.73 5,214,904.10 5,222,688.76

11 Bank-MMA (ending) 20,004,520.54 20,013,865.12 20,023,214.06 20,033,219.24


13

14

15 Ending Totals: 160,369,846 158,751,870 147,952,385 146,652,515 145,494,379 144,635,906 143,559,637 140,865,157




Formula Cell AX16: = AX15 - AW15 Formula Cell AX17: = IF ( AX16>0 , 0 , -AX16 ) Formula Cell AX18: = IF ( AX16<0 , 0 , AX16 )

Then copy formulas across the row

27


1 Sample Client

2 Operating Fund3


5




9 TexPool (ending) 10,003,113.94 10,007,284.31 10,011,452.92 10,015,785.97 10,020,269.29 10,024,831.81 10,028,013.75 10,031,145.02

10 TexStar (ending) 5,014,531.44 5,039,220.90 5,059,621.20 5,079,658.57 5,094,610.94 5,106,141.73 5,214,904.10 5,222,688.76

11 Bank-MMA (ending) 20,004,520.54 20,013,865.12 20,023,214.06 20,033,219.24


13

14

15 Ending Totals: 160,369,846 158,751,870 147,952,385 146,652,515 145,494,379 144,635,906 143,559,637 140,865,157





20 Core base 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000


22 Liquidity Base @ 15% 24,055 23,813 22,193 21,998 21,824 21,695 21,534 21,130

23


Graph Row 16

28


1 Sample Client

2 Operating Fund3


5




9 TexPool (ending) 10,003,113.94 10,007,284.31 10,011,452.92 10,015,785.97 10,020,269.29 10,024,831.81 10,028,013.75 10,031,145.02

10 TexStar (ending) 5,014,531.44 5,039,220.90 5,059,621.20 5,079,658.57 5,094,610.94 5,106,141.73 5,214,904.10 5,222,688.76

11 Bank-MMA (ending) 20,004,520.54 20,013,865.12 20,023,214.06 20,033,219.24


13

14

15 Ending Totals: 160,369,846 158,751,870 147,952,385 146,652,515 145,494,379 144,635,906 143,559,637 140,865,157





20 Core base 50,000 50,000 50,000 50,000 50,000 50,000 50,000 50,000


22 Liquidity Base @ 15% 24,055 23,813 22,193 21,998 21,824 21,695 21,534 21,130

23


Graph Row 17

30

Look at the trends and evaluate each month relative to the same month in other years: 1. December, January and February are typically well funded 2. April and October are typically cash strapped (except when bond proceeds broke the trend, i.e. April 2015)

$0

$2,000,000

$4,000,000

$6,000,000

$8,000,000

$10,000,000

$12,000,000

$14,000,000

Operating Fund Historical Unfunded Obligations

32

A B C D E F G H

1 Sample Client

2 Operating Fund3

4 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12

5

6 Depository Bank - Ending Balance 58,018,679 42,582,870 40,871,891 39,375,873 37,367,758 36,038,641 33,600,000

7 Checking Fund 001 (ending) 20,423 20,425 20,428 343,640 440.43 440.43

8 Checking Compensating Balance (ending) 25,000,000 25,000,000 25,000,000 25,000,000 25,000,000 25,000,000 25,000,000

9 TexPool (ending) 39,545,962.12 43,066,796.33 43,089,403.37 43,111,292.41 43,133,911.11 43,158,419.18 43,178,176.57

10 TexStar (ending) 16,948,269.48 18,457,198.43 18,466,887.16 18,476,268.17 18,485,961.91 18,496,465.36 18,504,932.82

11 Bank-MMA (ending)

12 Securities Portfolio (ending)

13

14

15 Ending Totals: 139,512,911 129,127,288 127,448,607 125,983,861 124,331,271 122,693,966 120,283,550

16 Portfolio Monthly Change (10,385,623) (1,678,681) (1,464,746) (1,652,590) (1,637,305) (2,410,416)

17 Portfolio Declines 10,385,623 1,678,681 1,464,746 1,652,590 1,637,305 2,410,416

18 Portfolio Increases 0 0 0 0 0 0

19 Graph table: Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12

20 Core base 50,000 50,000 50,000 50,000 50,000 50,000 50,000

21 Portfolio Balance 68,586 59,758 58,331 57,086 55,682 54,290 52,241

22 Liquidity Base @ 15% 20,927 19,369 19,117 18,898 18,650 18,404 18,043

23


25

26 Graph For Projections:

27 Mar Apr May Jun Jul Aug Sep

28 Average Change By Month (1,347,665)$ (8,862,078)$ (1,380,831)$ (1,419,521)$ (1,237,322)$ (1,550,443)$ (2,438,048)$

Formula Cell C28: = AVERAGE ( C16, O16, AA16, AM16, AY16 ) NOTE: These are the columns that contained April date across the years

33

Use a multi-year average to determine what is the typical monthly change. This could be used to develop a future-year projection of maturity needs.

34

A B C D E F G H

1 Sample Client

2 Operating Fund3

4 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12

5

6 Depository Bank - Ending Balance 58,018,679 42,582,870 40,871,891 39,375,873 37,367,758 36,038,641 33,600,000

7 Checking Fund 001 (ending) 20,423 20,425 20,428 343,640 440.43 440.43

8 Checking Compensating Balance (ending) 25,000,000 25,000,000 25,000,000 25,000,000 25,000,000 25,000,000 25,000,000

9 TexPool (ending) 39,545,962.12 43,066,796.33 43,089,403.37 43,111,292.41 43,133,911.11 43,158,419.18 43,178,176.57

10 TexStar (ending) 16,948,269.48 18,457,198.43 18,466,887.16 18,476,268.17 18,485,961.91 18,496,465.36 18,504,932.82

11 Bank-MMA (ending)

12 Securities Portfolio (ending)

13

14

15 Ending Totals: 139,512,911 129,127,288 127,448,607 125,983,861 124,331,271 122,693,966 120,283,550

16 Portfolio Monthly Change (10,385,623) (1,678,681) (1,464,746) (1,652,590) (1,637,305) (2,410,416)

17 Portfolio Declines 10,385,623 1,678,681 1,464,746 1,652,590 1,637,305 2,410,416

18 Portfolio Increases 0 0 0 0 0 0

19 Graph table: Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12

20 Core base 50,000 50,000 50,000 50,000 50,000 50,000 50,000

21 Portfolio Balance 68,586 59,758 58,331 57,086 55,682 54,290 52,241

22 Liquidity Base @ 15% 20,927 19,369 19,117 18,898 18,650 18,404 18,043

23


25

26 Graph For Projections:

27 Mar Apr May Jun Jul Aug Sep

28 Average Change By Month (1,347,665)$ (8,862,078)$ (1,380,831)$ (1,419,521)$ (1,237,322)$ (1,550,443)$ (2,438,048)$

29 Months with Average Shortfall 1,347,665$ 8,862,078$ 1,380,831$ 1,419,521$ 1,237,322$ 1,550,443$ 2,438,048$

Formula Cell C29 = IF ( C28<0, -C28, 0 )

Graph Row 29

Copy across the row

35

Graph of the months with averages that are negative.

36

Various Methods of Analysis

• Based upon the Historical Perspective:

– Use the worst case scenario for each month

– Perform an averaging for each month over the historic period

– Use the last year as a benchmark

• Add a section to your spreadsheet to calculate, based on the preferred method

37

Sample Analysis and Benefits

• New Client – Analysis Performed At Beginning of Fiscal Year

($141 MM total balance at 9/30/16) • Core = $50 MM • Intermediate = $70 MM • Liquidity = $21 MM

– Used Historical Averages as the Monthly Targets

(based on 4-years of data)

– Overlay Current Portfolio Maturities – Develop Proposed Maturities

38

Graph of the months with averages that are negative.

40

Analyze All Graphs to Project Future Liabilities Unfunded Current Proposed

Obligations Cash Equivalent Investments Maturities

Oct-16 10,101,734.44$

Nov-16 1,323,662.85$

Dec-16 -$

Jan-17 -$

Feb-17 -$

Mar-17 1,347,665.11$

Apr-17 8,862,078.49$

May-17 1,380,831.02$

Jun-17 1,419,520.60$

Jul-17 1,237,321.61$

Aug-17 1,550,442.89$

Sep-17 2,438,048.29$

Oct-17 10,101,734.44$

Nov-17 1,323,662.85$

Dec-17 -$

Jan-18 -$

Feb-18 -$

Mar-18 1,347,665.11$

Apr-18 8,862,078.49$

May-18 1,380,831.02$

Jun-18 1,419,520.60$

Jul-18 1,237,321.61$

Aug-18 1,550,442.89$

Sep-18 2,438,048.29$

Oct-18 10,101,734.44$

Nov-18 1,323,662.85$

Dec-18 -$

Maximum=2-years41

Unfunded Current Proposed


Oct-16 10,101,734.44$ 70,848,779.55$

Nov-16 1,323,662.85$ 5,016,376.98$

Dec-16 -$

Jan-17 -$

Feb-17 -$

Mar-17 1,347,665.11$ 5,000,000.00$

Apr-17 8,862,078.49$ 10,000,000.00$

May-17 1,380,831.02$ 5,000,000.00$

Jun-17 1,419,520.60$ 5,000,000.00$

Jul-17 1,237,321.61$ 5,000,000.00$

Aug-17 1,550,442.89$ 5,000,000.00$

Sep-17 2,438,048.29$ 10,000,000.00$

Oct-17 10,101,734.44$ 15,000,000.00$

Nov-17 1,323,662.85$ 5,000,000.00$

Dec-17 -$

Jan-18 -$

Feb-18 -$

Mar-18 1,347,665.11$

Apr-18 8,862,078.49$

May-18 1,380,831.02$

Jun-18 1,419,520.60$

Jul-18 1,237,321.61$

Aug-18 1,550,442.89$

Sep-18 2,438,048.29$

Oct-18 10,101,734.44$

Nov-18 1,323,662.85$

Dec-18 -$

Maximum=2-years

43

Actual Cash Balance = $71 MM

44



Oct-16 10,101,734.44$ 25,848,779.55$

Nov-16 1,323,662.85$ 5,016,376.98$

Dec-16 -$

Jan-17 -$

Feb-17 -$

Mar-17 1,347,665.11$ 5,000,000.00$

Apr-17 8,862,078.49$ 10,000,000.00$

May-17 1,380,831.02$ 5,000,000.00$

Jun-17 1,419,520.60$ 5,000,000.00$

Jul-17 1,237,321.61$ 5,000,000.00$

Aug-17 1,550,442.89$ 5,000,000.00$

Sep-17 2,438,048.29$ 10,000,000.00$

Oct-17 10,101,734.44$ 15,000,000.00$

Nov-17 1,323,662.85$ 5,000,000.00$

Dec-17 -$

Jan-18 -$

Feb-18 -$

Mar-18 1,347,665.11$ 5,000,000.00$

Apr-18 8,862,078.49$ 10,000,000.00$

May-18 1,380,831.02$ 5,000,000.00$

Jun-18 1,419,520.60$ 5,000,000.00$

Jul-18 1,237,321.61$ 5,000,000.00$

Aug-18 1,550,442.89$ 5,000,000.00$

Sep-18 2,438,048.29$ 10,000,000.00$

Oct-18 10,101,734.44$

Nov-18 1,323,662.85$

Dec-18 -$

Maximum=2-years

45

Cash Balance After New Investment = $26 MM

46

Orange Shaded Area Represents the “Core” Position = $50 MM



Oct-16 10,101,734.44$ 25,848,779.55$

Nov-16 1,323,662.85$ 5,016,376.98$

Dec-16 -$

Jan-17 -$

Feb-17 -$

Mar-17 1,347,665.11$ 5,000,000.00$

Apr-17 8,862,078.49$ 10,000,000.00$

May-17 1,380,831.02$ 5,000,000.00$

Jun-17 1,419,520.60$ 5,000,000.00$

Jul-17 1,237,321.61$ 5,000,000.00$

Aug-17 1,550,442.89$ 5,000,000.00$

Sep-17 2,438,048.29$ 10,000,000.00$

Oct-17 10,101,734.44$ 15,000,000.00$

Nov-17 1,323,662.85$ 5,000,000.00$

Dec-17 -$

Jan-18 -$

Feb-18 -$

Mar-18 1,347,665.11$ 5,000,000.00$

Apr-18 8,862,078.49$ 10,000,000.00$

May-18 1,380,831.02$ 5,000,000.00$

Jun-18 1,419,520.60$ 5,000,000.00$

Jul-18 1,237,321.61$ 5,000,000.00$

Aug-18 1,550,442.89$ 5,000,000.00$

Sep-18 2,438,048.29$ 10,000,000.00$

Oct-18 10,101,734.44$

Nov-18 1,323,662.85$

Dec-18 -$

Maximum=2-years

47

Results of Sample Analysis

• Initially, Portfolio was excessively liquid and yield was only slightly higher than Pools

• New Portfolio addresses all maturity needs for at least a year and builds in a Core Component

• Portfolio remains significantly liquid to address any unanticipated needs

• As Portfolio matures, the Ladder will continue to roll

48

How Will Cash Flow Analysis Enhance Your Management?

• You will better understand your organization’s cash flows

• You will be able to improve yields by not being too liquid

• You will be more comfortable with the investment strategy knowing that cash flow needs are covered for a longer period of time

49

Questions?

Emily A. Upshaw, CPA

(512) 633-4093 Office

[email protected]

Susan K. Anderson

(830) 637-7755 Office

[email protected]

50

establishing a cash flow model - · pdf fileestablishing a cash flow model emily a. upshaw,...

Documents