Working with Interim Financial Statements

Step #1 Beginning Balances

Beginning balances are taken from Fundware balances as of June 30 of the most recent completed fiscal year. The year-end data consists of all accounting entries made during the year-end financial statement preparation. In order to use the beginning balances for interim statements, the following transactions are excluded from the year-end data:

  1. July accruals – transactions uploaded at the end of July with June occurrence dates. These transactions are brought into the interims through the Cash Basis query in step #2a.
  2. Accounts Receivable and AR Allowance – in Step #5 & 6, we bring in AR and Allowance balances as of the quarter’s end, so these are excluded from beginning balances.
  3. Grants Receivable and Deferred Revenue – see the above comment on AR and Allowance, grants and unearned revenue quarter-end balances are brought in through step #5 & 6.
  4. MAPS/MnSCU, vouchers payable, bank reconciliation, chargeback and state appropriation adjustments – these transactions directly affect cash and entries are made based on cash amounts certified by the Office of the Legislative Auditor. These are generally cash transactions processed in ISRS in July but with a June occurrence date. Since they are cash items, these adjustments come into the interim statements via the cash basis in Step #2a.Examples of the accrual adjustments (with short descriptions) and affected Reporting Categories (RepCat) are:
  • MAPS/MnSCU cash adjustments (to RepCat 110 A/R, 200 Accounts Payable,or 205 Salaries Payable) - Other reconciling amounts may be identified on the MAPS to MnSCU reconciliation prepared by Campus Assistance. These adjustments include MAPS rejected items, MnSCU travel reimbursements (when the pay date lands on June 28, 29, or 30), timing issues of items posted to MAPS and ISRS, and how the Fundware accounting system assigns cash transactions to AR, AP or SP based on the transaction type.
  • Bank reconciliation adjustments (to 110 A/R) – Any reconciling items in the Treasury accounts, such as fees or outstanding items.
  • Vouchers payable entries (to 200 A/P) - This is a reconciling item on the State of Minnesota’s certified A614 cash report, and needs to be shown as an increase to MnSCU cash and an increase to MnSCU AP. For transactions posted on June 30 with the corresponding warrant issued July 1st, the state treasury account shows the cash on MnSCU’s books at June 30th.
  • State appropriation changes (to 110 A/R, 200 A/P,or 600 State Appropriation revenue).
  • Chargeback adjustments (to 110 A/R or 200 A/P) - Chargeback amounts are also adjusted to cash. These entries are a reclassification between institutions with no impact on the totalsystem wide cash amount.

After excluding the above transactions, the remaining transaction amounts are sorted into categories as follows:

  1. All Other – This includes all funds and appropriations except agency funds and CAP appropriation; All Other includes ENT fund.
  2. Agency Funds – Beginning balances for CLR, LSO, and other agency appropriations. HEB fund codes include 940, 950, 990, 997, 998 and 999.
  3. Capital Projects appropriation – Part of GEN fund, but is made up of HEB fund codes 820 and 825.
  4. Revenue Fund –For state universities only, it is made up of HEB fund codes 210, 212, 214, 215, 810, 811, 812, 813, 814 and 815.

Note about Reporting Categories: Most reporting categories are made up of multiple Object Codes. Institutions are accustomed to recording data by object code; however, reporting categories are used to summarize data by functional categories according to Generally Accepted Accounting Principals (GAAP) for governmental agencies. An Excel spreadsheet version ofthe financial reporting chart of accounts, “Fundware ISRS Chart of Accounts Crosswalk”, is sorted by both Reporting Category (to show which object codes are included in the reporting category) as well as by Object Code (to show which reporting category the object code is assigned to), and is available in the MnSCU Financial Reporting web site, at:

Step #2a Cash Basis

Cash basis activity represents all transactions in ISRS that involve cash. The selection criteria in the Brio query is based on Proc Timestamp less than or equal to the last day of the quarter. This ensures that transactions are summarized in the same manner as the AC0581CP report, which reconciles to MAPS. The cash basis activity also excludes all Agency, Capital and Revenue Bond funds, which are processed in separate steps of the interim template.

Transactions in this step are changes in reporting categories, and are summarized with the beginning balances in Step #2b to reconcile cash and investment balances. The Brio query transactions are pulled from ISRS in the same manner used in the Fundware cash basis uploads for the annual financial statement preparation.

Step #2b Cash Inv Rec

This step is used to reconcile cash and investments. Cash and investments from the Beginning Balance and Cash Basis steps are summarized, and compared to the GL EOM (General Ledger End-of-Month) report.

GL EOM report is a summary of general ledger accounts (assets and liabilities) summarized from ISRS. The GL EOM is the Brio version of the AC0581CP trial balance numbers from MnSCU’s accounting system. The cash difference shown on this step should be minimal, except in the case of schools that are part of NESU and schools having NETS, PALS, SHOTS, or COMET funds. In those instances, the GL EOM balance should be adjusted for the NESU, NETS, and/or PALS cash to reconcile to total cash.

If there are still differences in cash and/or investments in total of more than $500, then further review is needed to determine the cause of the difference. It may be from state appropriation entries that cross fiscal years, or late entries of cash items with a prior year occurrence date. You should also check cash accrual entries that would affect beginning cash balances, such as vouchers payable, MAPS/MnSCU adjustments, chargebacks, and bank reconciling items. For differences in investments, look for reporting category 115, Accrued Investment/Interest Income, that may be included in the beginning balance or cash basis activity but not the GL EOM report (or vice versa).

Step #3 Agency

Agency activity is brought in through the GL EOM report described above. This brings in month-end balances for agency funds using HEB Fund Codes 940, 950, 985, 990, 997, 998 and 999. The GL EOM report for Agency funds excludes the following reporting categories, as they are populated automatically through later steps in the statement preparation:

110 Accounts Receivable

112 Allowance for Doubtful Accounts

200 Accounts Payable

205 Salaries Payable

The amounts in Step #3 for cash, investments and receivables are reconcilable to the AC0581CP report for the appropriations matching the Agency fund codes. The GL EOM amounts are sorted in the Brio query by Appropriation, and the results table copied into the template. This step also brings in the LSO amounts for Accounts Receivable and Allowance for Doubtful Accounts from Step #5&6 Current AR & Allow, as well as the LSO Accounts Payable balances for Step #7 Accounts Payable. Once populated and totaled, the appropriations are carried into the WIP tab under reporting category 251, Funds Held in Trust.

Step #4 Grants

Grant activity is similar to Agency, except that Brio restrictions are for the following appropriations: FFA, FGR, FPK, GRI, GRT, SFA and SGR. The GL EOM amounts are sorted in the Brio query by Appropriation, and the results table copied into the template. This step also brings in the grants amounts for Step #5&6 Current AR & Allow, Accounts Payable balances for Step #7, and Salaries Payable balances from Step #8. The totals from this step are carried into the WIP tab for Grants Revenue, Unearned Revenue, with corresponding adjustments to Federal, State and Private Grant revenues.

Step #5 #6, Current A/R and allowance

Current accounts receivable and allowances are developed from the Brio FR_AR_Balances query. This query pulls data from the AR Customer Balance tables in ISRS, which are accessible in either WH (warehouse or “live”), or QR (quarterly report) tables. The interim statement process uses the QR upload, which is over-written after the start of a new calendar quarter. The query is further limited to exclude any “8000” numbered object codes, which are balance sheet items in ISRS. The query also is set to pull accounts receivable transactions prior to the next year-term (YRTR), to recognize A/R related to current and prior terms (future terms are addressed in step #9, Future YRTR Revenue). The AR balances from the query should be reconciled to the Accrued Revenue balances on the current AC0581CP report.

Step #7, Accounts Payable

This step consists of two separate Brio queries. The first query, AP Trend Analysis FYXX, pulls accounts payable (AP) balances by month from the prior fiscal year, to compare the prior year actual payables for that month with current month. The purpose of this comparison is to calculate the higher of the two amounts (in general, only about 60-70% of AP activity for the current month is entered into ISRS by the end of the calendar quarter), and use the higher amount to be conservative. The second query is the FR Activity – Rev Exp FY20XX, which contains pivot tables for AP for Current Activity, and for Agency and Grants AP activity.

The query table for Current Activity is limited to results with transaction types BL, EG, EV, PG, PV, XG, XL and XV; excludes HEB funds for Agency, Capital and Revenue fund activities; and pulls transactions for only revenue and expense reporting categories (Rep Category Code > 399).

The query table results for Agency and Grants is limited to the same transaction types as Current Activity, but includes HEB funds for federal and state grants, as well as Agency funds (Capital approp and Revenue Fund types are included in the appropriate steps of the interim statement template).

After populating the tables with data from the Brio queries, the template calculates the percentage of current AP activity by reporting category, and then compares the calculated percentages to a pro-rated share of prior year AP. The larger of the two amounts is then carried forward as AP activity for that particular reporting category. This allows the interim statements to be conservative in estimating total accounts payable activity for the quarter just ended.

Note: From this point forward, all steps use the FR Activity - Rev Exp FY20XX query in Brio to prepare data and populate the tables for the applicable steps.

Step #8, Salaries Payable

The Salaries Payable (SP) data is based on two reporting categories: 500 Salary Expense, and 502 Benefits. This query excludes Revenue and Agency fund types, as well as CAP appropriation. The Occurrence Date for this step is set for a window ofapproximately 4-5 calendar days of the pay date following the last pay period of the quarter. The purpose of this is to allow processing of the pay into the payroll system (SEMA4) and recognize wages that are earned but not yet paid as of the end of the quarter. Note that this computes total payroll for the pay period!

Once the above data is populated in the interim statement template, there is a calculation called Component 1, Regular Salaries Payable. This takes the total salary expense calculated above, divides it by 10 (the number of working days in a pay period), and multiplies by the number of days of salaries to be accrued as a liability. The following is a detailed example of how the accrued liability is determined:

The June13 - June 26 pay period is paid on July 6 (which is outside the current quarter), and the June 27 – July 10 pay period is paid to employees on July 20. Salaries payable liabilities consist of all 10 days of the June 13 – 26 pay period, plus the 3 days for June 27 - 29 that will be paid on July 20. Therefore, total salaries payable for the quarter ended June 30 will be 13 days. Note, however, that this calculation does not include any estimate or assumption for any salary adjustment as of July 1, which may be something to consider as an adjusting entry.

Component 2 of Salaries Payable is for employees using the Work-9-Pay-12 system. Salaries are broken out between Academic Support, Instruction, and Student Services. Based on a breakout from your payroll (or HR) department, the salary expense is multiplied by the percentage using 9-pay-12, to determine the difference in salary expense under the 9-pay-12 system instead of a 26-pay period system. The table also determines where the quarter ended in relation to total pay periods for the year, to determine whether we should be accruing a liability or expensing accrued salaries. The differences in salary expense calculations are then used to either increase or decrease salaries payable, and perform the opposite action on salary expense.

Component 3of salaries payable is for computing summer session accrued liability; therefore, this component applies only to the fourth quarter statements for June 30. The Brio query captures transactions for the following Program Categories: Academic Support, Instruction, and Student Services for the payroll transaction types. In ISRS, all summer session activity is booked in the next fiscal year, regardless of when earned. In order to match expenses to revenues, salaries earned through June 30 must be included in the current fiscal year’s financial statements. The pay periods that cross fiscal year end (June 30) are the only periods that need to be accrued. When these “split” payrolls are processed, all salaries are coded to the year in which they are earned. For example, in the pay period ending July 10, 2007, the first 3 days of regular salaries are coded to 2006 (June 27 – 29) and 7 days to 2007 (July 2 – 10). All ten days of summer session salaries are coded to 2007, but only 3 days, June 27-29, should be accrued.

Once salaries payable are computed above, the template multiplies the salaries payable by 75% to reduce salary expense, and by 25% to reduce benefit expenses.

Step #9, Future YRTR Revenue

This step is to recognize revenues for future terms that have been collected but are not yet earned. A good example of this is for the December statements, when the next quarter begins shortly after January 1 so a large amount of tuition, fees, and room & board revenues have been collected but are not yet earned. In the cash basis query, those amounts were included in revenue amounts in Brio, and must therefore be recognized as Unearned Revenue through Step #9. Note, however, that we do not adjust any cash that was collected, in order to maintain the reconciliation of total cash on hand.

The Brio query is set up to summarize revenues that have been entered into ISRS for a term that has not yet started (a later step, #16, is used to pro-rate revenues collected but not yet earned for a term currently in session), with a proc timestamp through the first day of the next quarter in order to get transactions processed overnight on the last night of the quarter. The data from the Brio query is reversed in step #9 to back out of revenue categories, and is reclassified at the bottom to Unearned Revenue (formerly Deferred Revenue). In the case where little revenue has been collected for future terms (such as September or March), it is common (and normal) for the Brio query to return zero results; however, when the end of the quarter is close to the beginning of the next term (i.e. December and June), there will be material amounts being reclassified as Unearned Revenue in this step.

Step #10, All Other Capital Assets

In this step, the Brio query is designed to accumulate data for capital assets other than CAP appropriation, Revenue Fund, and Agency funds. A number of object codes are reclassified in Brio to Reporting Category 530, Capital Expenditures (Reclassify), to force action to classify those assets properly. The object codes included are those for library materials, equipment, land, and other capital assets. Since these are forced into the Capital Expenditures (Reclassify) category, they must be reclassified on the interim statements into the correct categories for each type of asset. This step is also where estimates are used to show depreciation expense for the period to date, and to show disposal of library assets according to MnSCU policy. Note that institutions must make an estimate for equipment disposals, either based on prior knowledge or through querying the equipment module.

Step #11, Reclass

This step is used to reclassify amounts from steps 1 through 10 that are sitting in a “reclass” reporting category. The reporting categories include 430, Accrued Revenue; 435,Third Party Obligations; 499,Loan and/or Bond Proceeds; 530, Capital Expenditures (amounts remaining after what has been reclassed in Step #10 above); 535, Debt Service; and 905, Prior Period Adjustment. These amounts will have come into the WIP tab anywhere from Steps 2 through 10 (for all practical purposes, there should be no material reclassifiable amounts in the beginning balances). The Brio query for these amounts to be reclassified exclude Revenue Fund, Agency funds, and CAP appropriations. The query also excludes RE transactions, to prevent from double counting accounts receivable transactions. The amounts reflected in the Brio table for this step will not always match the amounts to be reclassified, because of adjustments made during steps 2 through 10; however, the amounts should be used for guidance in determining where the amounts should be reclassified to. In order to help you determine which categories are appropriate for reclassification, the query results include both the reporting category as well as the object code and object code description.