How to Complete the CSFA TRAN Cash Flow Template

Welcome to “How to Complete the Cash Flow Template and Questionnaire,” presented by The California School Financing

Authority. Today’s webinar orients you to the CSFA template, notes key features, identifies the fields to complete and how

to complete them.

California School Financing Authority Presents:

How to Complete the CSFA

TRAN Cash Flow Template

To optimally benefit from this training, please have available a completed historical cash flow for 2019-20 and historical

actuals for the first four months of this fiscal year. Also, you will need to refer to previously prepared cash flow projections

for the remaining fiscal years, 2020-21 and 2021-22. To assist you further, detailed instructions accompany the CSFA

template to keep you on track.

This webinar teaches how to complete the template using an already prepared District cash flow. For prospective issuers

who need help understanding how to prepare a cash flow, CSFA offers webinars for beginners and advanced cash flow

preparers at the CSFA website.

To Optimally Benefit From Today’s Webinar

• Have on hand:

• Completed historical cash flow for 2019-20

actuals and actuals to date for 2020-21

through October 31, 2020 and projections

through the remaining fiscal year 2020-21 and

for 2021-22

• Montague DeRose – CSFA TRAN template

• More helpful CSFA webinars are located at:

https://www.treasurer.ca.gov/csfa/k-14-

trans/index.asp

• For help regarding understanding a TRAN, please

contact Annette Yee and the Montague DeRose

team at: yee@montaguederose.com.

• For general accounting / template questions,

email Caroline Larson and the Eide Bailly team at:

calarson@eidebailly.com

Let’s begin! Let’s open the Excel workbook. The tabs that require District data entry are: The CF Detail tab, the Alternative

liquidity tab and the TRAN Sizing tab. All other tabs are for Montague DeRose-CSFA TRAN calculations. For ease of use,

all District input cells are colored blue. All locked cells are colored white.

Let’s Begin!

• District data input tabs in the order of

input:

• CF Detail

• Alt. Liq.

• TRAN Sizing

• All other tabs are for Montague

DeRose/CSFA TRAN calculations

• All District input cells are blue. All

locked cells are white

In the blue cells, input historical monthly actuals from July 1, 2019 through October 31, 2020. This includes the entire fiscal

year of 2019-20 and the first four months of 2020-21.

If your financial system does not easily separate 2019-20 accruals from June 2020 activity, it is OK to include all accruals

in the June 2020 column. Input the accruals in the accrual column for all other years so that the formulas properly populate.

Input Historical in

the CF Detail Tab

If the financial system does not

clearly separate accruals from the

June 2020 activity for 2019-20, it

is OK to input the accruals into the

June 30 column for the 2019-20

year only. Accrual column entry is

required for 2020-21 and 2021-22

for the formulas to populate

properly.

If you struggle to input this information, please let us know. If your general ledger is exportable to Excel CSV, we can

translate that information into the template for you. We request a report of actuals in a debit/credit format for all four-

digit object codes from 1000-9999 to perform this function from July 1, 2019 through October 31, 2020. Email a request

to Caroline Larson at: calarson@eidebailly.com.

Need Assistance

Populating the

Historical Actuals?

• If your general ledger is

exportable to Excel CSV, we can

translate that information into

the template for you.

• We request a report of actuals

in a debit/credit format for all

four-digit object codes from

1000-9999 to perform this

function from July 1, 2019

through October 31, 2020.

•

• Email a request to: Caroline

Larson at:

calarson@eidebailly.com

A cash flow needs to reconcile to source documents. Examples of important source documents are the District’s general

ledger, financial system trial balances, the unaudited actuals, the audited actuals and the most recent District budget.

Although this reconciliation should be done prior to input, by following these instructions, you will be able to quickly identify

discrepancies and correct them.

Begin by inputting the 2019-20 beginning balances in column D, lines 43 though line 61. The entries need to match the

ending audited balances for the year ending June 30, 2019. All audit adjustments must be incorporated into Column D.

Each asset and liability account should match to the final audited general ledger for the year.

Column D 2019-20 Beginning Balances Must

Match 2018-19 Audited Financial Statements

Next, enter the projections from November 1, 2020 Through June 30, 2022, including accruals. Please remember to

separate the month of June activity from closing entries. Closing entries are posted in the accrual column.

Note the split months of April 2021 and April 2022. The purpose of the split month is to demonstrate that most LEA’s reach

a cash flow “low point” in April. This is due to property taxes being deposited in late April and expenses and liabilities

being paid earlier in the month. In order to determine a cash low point, best to review prior year daily cash balances in

the general ledger.

Split the revenue, expense, asset and liability transactions between those transacted early in the month and those later in

the month to determine the cash low point.

LEA’s may adjust the dates from those pre-populated in the template. As an example, a LEA may hit a low cash point on

April 29. In that case, column N would include the transactions completed from April 1-29. Column O would include

transactions only for the 1 day of April 30. April 30 would be the date when cash is restored when the various revenue

apportionments and taxes become available.

Enter the Projections from November 1, 2020

Through June 30, 2022, Including Accruals

The purpose of splitting the revenue & expense in the month of April is to

demonstrate that expenses are usually paid early in the month and receipts are

usually received later in the month, creating a cash low point mid-month.

Districts can change the dates in the blue cells to match their individual cash low

point date.

Be sure to input the summarized 2019-20 Unaudited Actuals, 2020-21 Current Budget and 2021-22 Projected Budget

data into Column T. Please use the Board approved, or anticipated Board approved source data. For most LEA’s, this will

be the First Interim Report or the Q1.

Input the

2019-20

Unaudited

Actuals,

2020-21

Current

Budget &

2021-22

Projected

Budget

Into

Column T

In case of variance, fix the cash flow input or unaudited actuals input error

Next, please review Column U for variances; this column is designed to detect input errors.

No variances should appear in column U between the total annual input in column S and the 2019-20 Unaudited Actuals,

the 2020-21 Current Budget and the 2021-22 Projected Budget reported in Column T.

Should a variance be revealed, please check for input errors. Input errors can occur in either the monthly actuals or Column

T. In this case, if the actual input is correct, then the budget needs to be updated. If the budget is correct, then the actuals

need to be double checked and updated. The variance column should be zero in each cell once complete.

Column U

Should Not

Have a

Variance

Between

Column S

and T for

Any of the

Three Years

Solution to fix variance: update budget to cash flow or adjust cash projection to

budget

Please make certain the 2019-20 Unaudited Actuals are populating the locked cells properly in Column D of the 2020-21

cash flow. Should this not be the case, revisit the 2019-20 cash flow input and reconcile to the Unaudited Actuals. Checking

and re-checking the template input to source documents will result in a properly completed cash flow.

Ensure the 2020-21 Beginning Balances Agree to

2019-20 Unaudited Or Audited Actuals

The white cells are protected formulas. Check the 2018-19 beginning balances

plus the 2019-20 input to ensure it results in the 2019-20 closed trial balance (If

not audited, then the Unaudited Actuals totals or if the audit is complete, then

the Audited Actuals.

The template is designed to calculate the state apportionment based on the State’s schedule of deferrals. It also calculates

the receipt of the deferrals as an accounts receivable in the subsequent year.

The calculator requests input of the expected apportionment at 100% receipt in a “normal, pre-deferral” year.

Therefore, for line 97 and line 179, columns L through Q, February through June, please input the LCFF State Aid

apportionment anticipated to be certified as of the First Apportionment as if the apportionment is fully restored and

funded. The template is designed to calculate the anticipated deferrals on lines 98 and 180 based on the current

California Department of Education deferral schedule. The anticipated state aid accrual will auto-populate in column R

and the accounts receivable will auto – populate on line 214, columns E through I.

For California Community Colleges, the concept would be the same, substituting the State General Apportionment, object

8610.

Input the State Apportionment (Object: K12 8011/ CCD 8610) In

2020-21 and 2021-22 Years at 100% (Without Deferrals).

CSFA Template Will Calculate the Deferral & the Accrual for You!

Line 98 calculates the revenue

deferral

Line 133 calculates the State Apportionment accounts

receivable

Line 214 calculates the receipt of the 2020-21 State Apportionment accounts

receivable in the subsequent year

Once the cash flow tab is complete, best to move to the Alternative Liquidity tab.

Enter the District’s legally available cash balances outside the General Fund that would be available for inter-fund

borrowing. This is purely for illustrative purposes and in no way implies that the District will be borrowing from these funds.

These balances will be used by the rating agency and/or investors to calculate coverage ratios and entering the additional

fund information will allow the district to obtain the best possible rating and interest rate.

Alternative

Liquidity Tab

• List cash resources of the

District not included in the

General Fund cash flow

• Include all District cash that

can be loaned to the General

Fund on a short-term basis

(less than six months)

• Only include funds that are not

restricted or highly regulated.

• Do not include bond proceeds,

deferred compensation, funds

held by a trustee

When calculating the balances for each year of each fund outside the General Fund, calculate the available cash by

assuming the receivables are received and the payables are paid. Remember, the receivables and payables may or may

not be received or paid within the traditional 60 days from year-end. This calculation requires thought and analysis. The

answer is not found in any source document. One must use the beginning trial balance, the projected budget of the fund

and knowledge of the activity within the fund. For some funds, it is prudent to do a complete cash flow of the alternate

fund to determine when it will have funds to loan the General Fund and for how long.

Alternative

Liquidity Tab

• For 2019-20, it is the cash as of June 30,

2020 that is available after accounts

receivable has been received and accounts

payable has been paid, and that is not

obligated in 2020-21.

• For 2020-21, it is the cash as of June 30,

2021 that is available after accounts

receivable has been received and accounts

payable has been paid, and that is not

obligated in 2021-22.

• For 2021-22, it is the cash as of June 30,

2022 that is available after accounts

receivable has been received and accounts

payable has been paid, and that is not

obligated in 2022-23.

2021-22.

Almost complete! You are in the home stretch! The TRAN sizing tab, has 3 entries. In cell D59, toggle either YES or NO,

depending on your District situation.

Select YES, if your district expects to issue:

more than $15 million in tax-exempt obligations including the March 2021 TRAN, during the 2021 calendar year, or

more than $5 million in TRANs during the 2021 calendar year

Common non-taxable obligations are Certificates of Participation (COPs) and other outstanding TRAN.

Note that this information is for a new issue sold and closed during the CALENDAR Year, 2021, from January 1, 2021

through December 31, 2021

Cell J91 is the Board Authorized Amount for the 2021 deferral TRAN. The Board authorized amount defines the ceiling

that the LEA can borrow.

Cell J92 is the 2021 deferral TRAN preferred issuance amount based on the District’s analysis. It is a reasonable estimate

based on facts known as of time of completion of the template.

TRAN

Sizing Tab

Three Cell Input

• Cell 59: Toggle Yes or No

• Cell J91: Board Authorized TRAN

amount

• Cell J92: District preference for

deferral TRAN amount

Now that your hard work is complete, please email the template to Annette Yee at yee@montaguederose.com

Thanks so much for your attention to the details requested. We look forward to a successful issuance!

When Complete…

• Return the template to:

yee@montaguederose.com;

• K14TRAN@treasurer.ca.gov;

• kremer@montaguederose.com;

• calarson@eidebailly.com

Annette Yee, Managing Director

Montague DeRose and Associates

27205 Meadows Road, Carmel, CA 93923

(831) 626-4524 Office

(831) 915-6175 Cell

yee@montaguederose.com