Report on Cash Flow and Fund Flow - Excel - Excel

Document Sample
Report on Cash Flow and Fund Flow - Excel - Excel Powered By Docstoc
					                                                         Instructions

                 Cash Receipts

State Aid Operating Receipts (Column B)
  •Latest Ideas Report – State Aids Payment Schedule Page
    (usually the last or second to last page)

Federal Aid Receipts (Column C)
  •Non Ideas Entitlement Payment (last page of the Ideas Report)
  •Current Year Federal Food Service Receipts
  •SERVS Payments
  •MFR (MN Funding Reports) Payment Summary by Finance Code OR
  •Add draw requests from SERVS

Levy Operating Receipts (Column D)
  •Prepare estimate of levy receipts using excel spreadsheet; see tab "Calc Levy Revenue"
      •Levy Certification
      • Ideas Report - Current Account Entitlements for Property Tax Relief Aids (Tax Credits)
      •County Tax Settlement Statements (Compare actual to estimate)
      •Use history of spring and fall receipts from tax statements and adjust the percentage on the
        spreadsheet in column E; rows 20 - 26

Misc Receipts (Column E)
 •No entry – this is a formula

Total Receipts (Column F)
  •Export Detailed Trial Balance Report to Excel
  •See tab "How to Run Trial Bal"
  •Enter the net amount of AR transactions (debit as a addition - credit as a subtraction)
   plus debit journal entries and/or less credit journal entries


             Cash Disbursements

Payroll Disbursements (column H)
 •Enter the Total net amount of IMP transactions from the exported detail trial balance
  (credits less debits - this is net payroll and does not include deductions)
 •Add or subtract accruals as needed usually in June, July and August

Other Disbursements (column I)
 •Enter the total net amount of AP transactions (credits less debits) plus credit
   journal entries and/or less debit journal entries

Repay Aid Certificates (column J)
 •Enter amount paid from aid certificate document

Total Disbursements (column K)
  •No entry – this is a formula

End of the Month Cash Balance (column M)
 •Formula (previous balance plus receipts less disbursements)
 •Reconcile with the Detailed Trial Balance for each period
     •Ending Balance of Cash


                        FY 2012 Reminders
Two reports are now available on the Minnesota Department of Education’s website to assist
districts and charter schools in their state aid cash flow estimates for FY 2012. They can be
found at http://education.state.mn.us. Select Accountability Programs > Program Finance >
Minnesota Funding Reports System. Or, you may use the direct link.
http://education.state.mn.us/MFRSystem/index.do



                                                           Page 1 of 6
                                                         Instructions



When the first IDEAS payment report comes out with the July 15, 2011 IDEAS payment,
and for each period after that date, districts and charter schools should use the “IDEAS State
Aids Combine Payment” report to review cash payment projections.

State Aid Operating Receipts (Column B)
  •Update State Aid estimates as the payment schedule from the ideas payments are
   received or as new information is received
  •Changes from 2011 to 2012 when estimating State Aid
      •State Aid payments are meterd at 70% for both FY 2011 and FY 2012
      •Final Payments are at 30% for both FY 2011 and FY 2012
      •Tax Shift in May and June of 2011
      •FY 2012 Tax Shift is calculated based on February 2011 forecast data.
      •The MDE State Aid Payment Projection cash pattern does not include
        a cash flow delay under Minnesota Statutes, section 127A.46.
  •Keep in mind changes(within your district) that effect state aid
      •Student counts (more, less, the same)

Federal Aid Receipts (Column C)
  •Changes from 2011 to 2012 when estimating Federal Aid
      •Review balance forward dollars for one time money; Stabilzation, ARRA and Education
       Jobs for Title and Special Education federal programs
      •Consider SERVS reimbursement request patterns when estimating monthly receipts
  •Estimate the following;
      •Title and Special Education Programs
      •Food Service
  •Update Federal Receipts
      •as changes are made to Federal allocation or payments are requested differently than originally planned

Levy Operating Receipts (Column D)
  •Prepare estimate of levy receipts using excel spreadsheet; see tab "Calc Levy Revenue"
      •Use history of spring and fall receipts from tax statements and adjust the percentage on the
       spreadsheet in column E; rows 20-26
      •County Tax Settlement Statements (Compare actual to estimate)
  •Estimate 2011-2012 Levy
      •Use history of previous levies
      •OPEB levy
      •Added a Referendum

Miscellaneous Receipts (Column E) – Local Receipts
 •Increasing Fees
     •Activities
     •Food Service
 •Discontinuing or adding a program
 •Increase or Decrease in receipts from other Districts

Roll forward previous year disbursements

Payroll Disbursements (column H)
 •Increased Salaries
 •Increased or Decreased FTE’s

Other Disbursements (column I)
 •Budget Cuts or Increases




                                                           Page 2 of 6
                                    Instructions
        How to Run the Detailed Trial Balance for Cash Flow Projection Model

1.     Run the Detailed Trial Balance report to obtain the Starting Balance. Export to Excel with Headers.
            a. L = B
            b. Org = 10%
            c. Start Period = start of fiscal year; e.g. 201101 for July 1, 2010
            d. End Period = current period or end of fiscal year; e.g. 201112 for June 30, 2011
2.    Determine which funds are Operating Funds
            a. Smart Finance > General Ledger > Chart of Accounts Setup > Account Segment Setup
            b. Operating Funds are all funds with a UFARS code or are cross walked to UFARS Code 01, 02, and 04
3.    Delete rows with non-operating funds such as Funds 06, 07, 08, 25, etc.
4.    Delete unnecessary columns (gltd_seg6 through gltd_seg9, gltd_6-9, gltd_segty, glab_amt)
5.    Sort data by “glt_glpd_no”, then by “glt_source”
6.    Delete zero balance rows
7.    Subtotal at each change in “glt_glpd_no” (period)
            a. Use function = sum
            b. Add subtotal to gltd_amt_db and gltd_amt_cr (deselect any defaults)
8.    Subtotal again by change in “glt_source”
            a. Deselect “Replace current subtotals”
            b. Use function = sum
            c. Add subtotal to gltd_amt_db and gltd_amt_cr
9.    Delete rows for JE source codes when the JE debit and credit cancel each other out; these are not deposits
     or disbursements but generally transfers or reclassifications of cash between operating funds.
         A            B               C            D               E             F         G          H                I            J                K       L        M           N                O                    P
1    Updated: 5/20/11
2       School District Name and Number:                                                       Quick Estimate of Federal Borrowing Limit
3                                Fiscal Year:                                                  Five percent (5%) of Prior Fiscal Year Expenditures               $        -
4                                                                                              Largest Negative Monthly Cash Balance                             $        -
5                                                                                              Estimated Borrowing Limit                                         $        -
6
7    Actual Cash Expenditures from Prior Fiscal Year                                           Quick Estimate of State Borrowing Limit
8    Fund 01 - General Fund                                   $           -                    Taxes Receivable                                                  $        -
9    Fund 02 - Food Service                                   $           -                    Seventy-five percent (75%) of Taxes Receivable                    $        -
10 Fund 04 - Community Services                               $           -
11 TOTAL PRIOR FISCAL YEAR Expenditures                       $           -                    Aids Receivable                                                   $        -
12                                                                                             Seventy-five percent (75%) of Aids Receivable                     $        -
13
14               KEY:
15 Input field
16   Receipts                                                     Cash Flow Projection Model
17   Disbursements                                         (General, Food Service and Community Ed Funds)
18   Calculated Field
19                                              Receipts                                                              Disbursements                                 End of
20    Fiscal Year   State Aid-     Federal       Levy-                                                                        Repay Aid                             Month
21       2011       Operating        Aid        Operating         Misc.        Total                Payroll          Other    Certificates      Total            Cash Balance
22   June                                                                                                                                                                  -               SERVS Federal Revenue 2010
23   July                  -              -            -                  -            -                      -            -                             -                   0        Title I ARRA
24   August                -              -            -                  -            -                      -            -                             -                   0        Title I
25   September             -              -            -                  -            -                      -            -                             -                   0        Title II
26   October               -              -            -                  -            -                      -            -                             -                   0        Title IV
27   November              -              -            -                  -            -                      -            -                             -                   0        Fin 151 Stab.
28   December              -              -            -                  -            -                      -            -                             -                   0        Fin 419/619
29   January               -              -            -                  -            -                      -            -                             -                   0        Fin 420/620
30   February              -              -            -                  -            -                      -            -                             -                   0        Fin 480/680
31   March                 -              -            -                  -            -                      -            -                             -                   0                                       0.00
32   April                 -              -            -                  -            -                      -            -                             -                   0        rcvd YTD=
33   May                   -              -            -                  -            -                      -            -                             -                   0                                       0.00
34   June                  -              -            -                  -            -                      -            -                             -                   0        enter est number of remaining
                                                                                                                                                                                                 payments
35
36    Total                -              -            -                  -            -                      -            -                             -                            proj Federal pmts               #DIV/0!
37      2012
38
39   July                  -              -            -                  -            -                      -            -                             -                    0            SERVS Federal Revenue 2011
40   August                -              -            -                  -            -                      -            -                             -                    0       Title I ARRA
41   September             -              -            -                  -            -                      -            -                             -                    0       Title I
42   October               -              -            -                  -            -                      -            -                             -                    0       Title II
43   November              -              -            -                  -            -                      -            -                             -                    0       Title IV
44   December              -              -            -                  -            -                      -            -                             -                    0       Fin 151 Stab.
45   January               -              -            -                  -            -                      -            -                             -                    0       Fin 419/619
46   February              -              -            -                  -            -                      -            -                             -                    0       Fin 420/620
47   March                 -              -            -                  -            -                      -            -                             -                    0       Fin 480/680
48   April                 -              -            -                  -            -                      -            -                             -                    0                                      0.00
49   May                   -              -            -                  -            -                      -            -                             -                    0       rcvd YTD=                      0.00
50   June                  -              -            -                  -            -                      -            -                             -                    0                                      0.00
                                                                                                                                                                                      enter est number of remaining
51
                                                                                                                                                                                                 payments
52    Total                -              -            -                  -            -                      -            -            -                -                                                                    10
53                                                                                                                                                                                    proj Federal pmts                     0.00


54                                                                                                                                                                                    proj Federal pmts                     0.00
          A            B            C            D             E              F       G         H               I               J   K   L   M   N   O   P
55   Comments:
56   * Districts are limited to borrowing the lesser of the State or Federal restrictions.
57   * Cash Flow projections represent a forecast and will not be exact.
58   * Cash Flow projections should reflect a reasonable expectation of the district's cash flow.
59   * Keep in mind that the IRS reserves the right to audit projections.
60   * Cash Flow projections use Cash and Investments balances, not Fund Balance.
61   * Cash Flow projections include all Operating Funds (Funds 01, 02 and 04). The General Fund may be projected separately.
62   * The beginning September balance should include investment earnings received to date.
63   * Do NOT include earnings and/or interest expense on investments on subsequent months.
64   * Do NOT include purchase or sales of investments in the projection.
65   * Include repayment of any current outstanding certificates.
Updated: 5/20/11                          2011-12
                                        Levy Revenue

                                    2010 Payable 2011

Description                 Fund 01                        Fund 04                  Totals

Gross Levy                                                                                    -

Less:
 Disparity                                                                                    -
 HMVC                                                                                         -
 ALMVC                                                                                        -

                                                                                                     % Del Tax Rcvd    Net Rcvd

NET LEVY                                   -                             -                    -               99.0% $             -


                   Estimated Tax Settlement                                       % Rcvd by Dist      % Del Tax Rcvd
                    1st half of Property Taxes Due   50% of 1/2 of Levy Revenue              45.0%            99.0%    May-11         $   -
                    1st half of Property Taxes Due   50% of 1/2 of Levy Revenue              45.0%            99.0%    Jun-11         $   -
                                                             * Clean-up Payment              10.0%            99.0%    Jul-11         $   -
                   2nd half of Property Taxes Due    25% of 1/2 of Levy Revenue              22.5%            99.0%    Oct-11         $   -
                   2nd half of Property Taxes Due    25% of 1/2 of Levy Revenue              22.5%            99.0%    Nov-11         $   -
                   2nd half of Property Taxes Due    50% of 1/2 of Levy Revenue              45.0%            99.0%    Dec-11         $   -
                                                             * Clean-up Payment              10.0%            99.0%    Jan-12         $   -
                                                                                                                                      $   -




                                                                                                                                              7/14/2011^

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:23
posted:7/14/2011
language:English
pages:6
Description: Report on Cash Flow and Fund Flow document sample