Get documents about "Sample Excel Income Statement
Description
Sample Excel Income Statement document sample
Document Sample
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 total system wide cash amount.
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 1
Working with Interim Financial Statements
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 of the 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:
http://www.financialreporting.mnscu.edu/FinancialReportingInfo/index.htm
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
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 2
Working with Interim Financial Statements
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.
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 3
Working with Interim Financial Statements
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.
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 4
Working with Interim Financial Statements
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 of approximately 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 3 of 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
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 5
Working with Interim Financial Statements
(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;
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 6
Working with Interim Financial Statements
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.
The step contains guidance with each category on where reclassified amounts should go.
For example, any equipment or library costs in category 530 for Capital Expenditures
would be reclassified to Supplies; however, if the amounts in the Brio query show other
capital asset object codes, you will need to make a decision on how much to reclassify as
supplies and how much should be for Repairs and Maintenance. This can be done by
reviewing which step(s) incorporate the amounts to be reclassified, and running
additional queries to get additional transaction information.
Once the reclassified amounts are completed, the total at the bottom of this step should be
zero, showing that all amounts in these categories have been properly reclassified.
Step #12, Comp Abs
Compensated absences are liabilities recognized to account for accrued vacation, sick
time, and other leave bank costs that are earned but not used. The compensated absences
computations are very complicated due to working with pre- and post-merger contracts,
as well as multiple labor agreements. Therefore, for expediency in the interim statement
preparation, we use the audited amounts from the previous fiscal year, and escalate this
by 1% for each quarter during the year (estimated 4% per year for total compensation
adjustment to accrued leaves).
Step #13, Sec Lending
The Governmental Accounting Standards Board (GASB) defines securities lending
transactions as, ―Transactions in which governmental entities transfer their securities to
broker/dealers and other entities for collateral – which may be cash, securities, or letters
of credit – and simultaneously agree to return the collateral for the same securities in the
future‖. The purpose of the transaction is to enhance the return on the governmental
entity’s portfolio. Cash collateral is received by the entity and invested. If investment
income exceeds the amount paid (interest) to the broker, the governmental entity will
earn a net profit on the transaction. Conversely, if the return is less than the amount paid,
a net loss will occur.
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 7
Working with Interim Financial Statements
At the end of the securities lending transaction, the governmental entity will return the
collateral to the broker and the broker will return the underlying securities to the
governmental entity. Although the governmental entity can deal directly with a
broker/dealer, securities lending transactions are usually executed through a securities
lending agent (Wells Fargo Bank, State Street Bank). The Minnesota State Board of
Investment has been overseeing a program of securities lending for investments of the
Invested Treasurer’s Cash Pool (ITC) since May 2000. The purpose of this program is to
increase the earnings on the pooled cash balances in MAPS.
Securities lending information is certified by the Office of the Legislative Auditor. The
total amount of securities lending collateral is allocated to each school by fund based on
their cash balances as certified by the Legislative Auditor. Securities lending collateral is
reported on the statement of net assets. The collateral is recorded as an asset account
(report category 150, object code 8512). Since it has to be repaid, this same amount is
reported as a liability to the Securities Lending liability account (report category 255,
object code 8704). The asset and liability accounts will equal and therefore will have no
effect on the statement of net assets.
For the interim statements, we use audited securities lending amounts from the audited
financial statements of the prior year as part of the beginning balances. This allows
recognition of an asset and liability to meet the GASB requirements. Since we are using
prior audited amounts, there should be no change to the totals from the beginning
balances, so this step should have no change to the asset and liability totals.
Step #14, Chargebacks
The Office of the Chancellor pays for numerous activities centrally on behalf of the
colleges and universities, including Academic computing, administration and personnel
development, as well as executive management, staff development and general
administration. Chargebacks are allocated to the schools on a variety of bases, including
actual expenditures, allocation based on FYE or FTE, and allocation based on square
footage. These expenditures are recorded in the accrual statements as an adjustment to
state appropriation revenues.
For the interim statements, chargebacks are done using year-end amounts from the prior
fiscal year, and are prorated based on 25% expended for each quarter. The portion
applied for the current quarter is recorded to state appropriation revenue, and to other
expense. The unapplied portion is recorded in the accrual statements as an increase in
Accounts Receivable-State Approp and a decrease to Unearned Revenue-State Approp.
Step #15, Perkins Loans
This step is used to recognize changes in the Perkins loans receivable balance, net of
uncollectible allowance. The Perkins loan ending balances for the quarter are obtained
from Loan Management Services, and used as the ending balance for the quarter. This is
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 8
Working with Interim Financial Statements
compared to the loan receivable balance for the last fiscal year, and the difference posted
as an increase or decrease to loans receivable, with a corresponding change to cash
balances. The Perkins loan allowance for doubtful accounts is updated using the
percentages from the prior fiscal year allowance, and is recorded to Other Expense.
Step #16, Prorated Rev & Exp
Prorated revenue and expense is used to estimate amounts of earned revenue and/or
expense as of the end of the quarter, and recognize an increase or decrease to unearned
revenue. This step is divided into 4 components: Federal and State grant revenue, tuition
and fee revenue, state appropriation revenue, and summer session revenue.
Component 1 for state and federal grants is taken from the FR Activity – Rev Exp
FY20XX query in Brio. The query limits are for FFA and SFA appropriations, and for
reporting categories 605 Federal Grants, and 610 State Grants, and exclude A/R
transactions. These grants are generally reimbursable grants for financial aid, and are
considered earned in the term in which they are recorded. Therefore, in general, the
grants are shown as 100% earned.
Component 2 is to record amounts of earned tuition, fees, room and board revenue. This
calculation is based on the number of days incurred in the term, divided by the total
number of days in the term. Based on this percentage, a portion of tuition is shown as
earned revenue, and the remainder is reclassified as unearned revenue. The same
percentage is applied to financial aid, with a corresponding adjustment to prepaid assets.
Component 3 is to defer a portion of state appropriation revenue, and is reclassified to
Unearned Revenue – State Appropriation. There is also a calculation to prorate the same
percentage of Financial Aid expense, which ―unearned‖ expense is reclassified to Prepaid
Assets.
Component 4 is to compute a portion of summer session revenue. Accounts receivable
for summer session year term (YRTR) 200X1 consists of pro-rated earned revenues being
billed as of June 30 (based on the number of days prior to July 1—start date of the next
fiscal year) in relationship to what is uncollected at June 30. In this part, summer session
revenues and receivables are used from audited financial statements. These amounts in
the table have already been prorated to reflect portions earned before June 30, and are
summarized by reporting category. These then flow into the WIP tab for the appropriate
categories.
Step #17, Scholarship Allowance
The scholarship allowance is the difference between accounts receivable charges for
goods and services and the amount paid by the student or a 3rd party. Loans applied are
treated as student payments. The objective of computing scholarship allowance is to
avoid double counting revenues by reporting tuition, fees, book charges, room and board
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 9
Working with Interim Financial Statements
and financial aid net of scholarships. Scholarships include grants such as Federal
Financial Aid, which are recorded as revenue when received and again as tuition and fee
revenue, etc when applied to a student’s account. The authoritative standards for the
scholarship allowance are GASB 34 and NACUBO.
GASB 34, paragraph 100 states that revenues must be reported net of discounts and
allowances. These standards apply to all public, not for profit colleges and universities
reporting under GASB 34 standards. MnSCU has developed two business rules to
provide guidance to the allowance calculation.
Business rule #1 ― Grants, scholarships and waivers that are applied to a students’
accounts receivable for payment of tuition, fees, room, board and books will be
reclassified as a contra revenue to the applicable revenue object code. Loans applied,
third party and out of pocket student payments will be treated as revenues‖
Business rule #2 ―3rd party payments shall be considered to be applied to tuition and fees
before grants and scholarships‖
For the interim financial statements, we are using amounts computed for the latest
completed fiscal year. The allowance by revenue category is calculated and carried
forward into the WIP tab.
Step #18, Capital Projects
This step is to incorporate accounting for activity within the GFS funds 505 and 115. It
includes accounting activity related to the design, construction, and repair and
maintenance of Minnesota State Colleges and Universities (MNSCU) buildings from the
sale of General Obligation Bonds. Capital Projects generally affect multiple fiscal years
and are recorded in fiscal year 9999 in the accounting system. The data on capital
projects is compiled manually using a Balance Sheet and Operating Statement format by
institution. The final numbers are entered into Fundware with journal entries.
The interim statements use quarterly financial statements prepared for capital projects
(the June amounts are from audited statements). These statements are summarized into
reporting categories that can be used in the interim statement template. These then carry
forward into the WIP tab.
Step #19, Revenue Fund
The Revenue fund provides for the construction, maintenance and ongoing operations of
revenue-producing enterprises at State Universities by charging fees for the use of its
enterprise activities. The enterprises of the revenue fund are dormitories, food service,
student unions and parking. The revenue fund can sell bonds to build and repair revenue
producing facilities but does not receive any state appropriations. During the 2005
Legislative session, Revenue Fund bond authority was increased to $150,000,000. Bond
debt must be repaid from the fees generated by the facilities. The revenue fund can only
be used by State Universities. The issuance of revenue bonds and all related activities,
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 10
Working with Interim Financial Statements
including debt service, are covered by the Master Resolution adopted by the Board of
Trustees.
The Office of the Chancellor (OTC) serves as policy maker and administrator of Revenue
fund activities that benefit all institutions. The OTC has no fixed assets and gets its
revenues from assessments made to the participating institutions. Primary activities of the
OTC are the sale of bonds, management of bond proceeds, allocation of interest, debt
service management, and financial reporting. The OTC makes the semiannual interest
and annual principal payments from the debt service fund. The master resolution requires
that the debt service account be funded by March 1st of each year for the next year’s
principal and interest. This funding is done through a billing from the OTC to
participating institutions for their share of the bond debt.
For purposes of the interim statements, Revenue Fund statements are prepared quarterly
by the Revenue Fund Accountant at the OTC, and provided to the state universities for
inclusion in their institution statements. The OTC also prepares a consolidated Revenue
Fund statement for interim purposes.
Step #20, Misc Adjustments
In this step, institutions have the opportunity to make adjustments to their financial
statements. Some entries are made automatically, such as reclassification of net transfers
in the Reclassification and Capital Projects steps to Interest Expense.
Some examples of institution adjustments are correcting cash balances, recording bank
reconciliation adjustments, material accounts receivable or payable invoices received but
not yet paid, or other adjustments as necessary. Other adjustments include:
Cost Allocations Between ENT & GEN
Inventory Adjustments: COGS to Inventory
Advances: i.e. Loans to / from Office of Chancellor
A/R: Off-A/R Module Billings
Local Bank & Investment Reconciliation Adjustments
Retainage: i.e. Capital Projects Paid with General Fund $s
A/P: Additional Material Purchases Not in ISRS
Other Lines of Business: NETS & PALS
The adjustments made in this step are summarized by reporting category and carried
forward into the WIP tab.
Step #21, Due to & from
This is a self-calculating step. Costs included in the Due To and From are summarized
and then reversed to cancel each other out (if in balance). If the Due To/From categories
do not balance, the difference is reclassified to Other Expense.
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 11
Working with Interim Financial Statements
Step #22, Transfers In & Out
This is a self-calculating step. Non-mandatory transfers in and out are summarized and
then reversed to cancel each other out (if in balance). If the transfers’ categories do not
balance, the difference is reclassified to Other Expense.
Step #23, Net Assets
This is a self-calculating step. Part of the net assets of the institutions and the
consolidated system are restricted as Invested in Capital Assets, Net of Related Debt
(ICANRD). This classification of net assets is made up of capital assets, net of
accumulated depreciation and outstanding principal balances of debt and other
borrowings, attributable to the acquisition, construction or improvement of those assets.
The computation is to add up all capital assets (land, buildings, equipment, library assets,
and construction in progress, and subtract out accumulated depreciation and outstanding
debt principal. This net total shows how much of net assets must be reflected as
ICANRD.
The interim statement template compares beginning capital assets with computed
amounts, and calculates any increase or decrease to ICANRD based on changes in capital
assets, accumulated depreciation, and related debt.
Financial Statement Presentation
Once completed with all steps described above, the WIP tab has been populated with the
information necessary to prepare financial statements. As it is set up, the WIP tab
populates the financial statements in the blue tabs of the template, and creates a
Statement of Net Assets, Statement of Revenues, Expenses, and Changes in Net Assets,
and Statement of Cash Flow.
Below are charts to help see the flow of the data from the WIP tab into the appropriate
financial statement. Each reporting category is used only once for the Statement of Net
Assets and Statement of Revenues, Expenses, and Changes in Net Assets; the Statement
of Cash Flows uses data from both the Statement of Net Assets and the Statement of
Revenues, Expenses, and Changes in Net Assets.
1. The Financial Statement Presentation of Reporting Categories is a numerical
listing of reporting categories, and shows where they flow into the financial
statements.
2. The Statement of Net Assets table shows which reporting categories make up
each line in the Statement of Net Assets. It includes the breakouts between current
and noncurrent assets and liabilities.
3. The Statement of Revenues, Expenses, and Changes in Net Assets table shows
which reporting categories make up each line in that statement. This also helps to
show which items are considered operating revenues and expenses, and which are
nonoperating.
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 12
Working with Interim Financial Statements
Financial Statement Presentation of Reporting Categories
Rep
Cat Category Description Financial Statement Presentation
100 Cash and Cash Equivalents Cash and cash equivalents
105 Investments Investments
110 Accounts Receivable Accounts receivable, net
xxx Accounts Receivable - State Approp #N/A - Not used in audited statements
112 Allowance for Doubtful Accounts Accounts receivable, net
115 Accrued Investment/Interest Income Student loans and other assets, net
120 Grants Receivable Grants receivable
125 Inventory Inventory
128 Loans Receivable - Current Student loans and other assets, net
130 Loans Receivable Student loans and other assets, net
135 Notes Receivable - Current Student loans and other assets, net
138 Notes Receivable - Non Current Student loans and other assets, net
140 Prepaid Assets Prepaid expense
145 Other Assets Student loans and other assets, net
147 Advances From Other Schools (Current) Advances from other schools
150 Securities Lending Collateral Securities lending collateral
156 Due from Other Funds Due from other funds
157 Advances from Other Schools (Noncurrent) Advances from other schools
161 Cash and Cash Equivalents - Current Restricted Cash and cash equivalents
162 Investments Investments
163 Restricted Assets - Other Assets Other assets
164 Restricted Assets - CIP Construction in progress
165 Land Capital assets, net
169 Cash and Cash Equivalents - Noncurrent Restricted Cash and cash equivalents
170 Buildings Capital assets, net
175 Equipment Capital assets, net
176 Internally-developed Software Capital assets, net
180 Construction in Progress Capital assets, net
185 Library Collections Capital assets, net
199 Accumulated Depreciation Capital assets, net
200 Accounts Payable Accounts payable
205 Salaries Payable Salaries payable
210 Unearned Revenue Deferred revenue
xxx Deferred Revenue - State Approp #N/A - Not used in audited statements
215 Accounts Payable - Restricted Payable from restricted assets
218 Interest Payable Interest Payable
220 Revenue Bonds Payable Current portion of long-term debt
225 GO Bonds Payable Current portion of long-term debt
230 Notes Payable Current portion of long-term debt
Compensated absences payable/Workers'
235 Compensated Absences compensation, Early Termination
Compensated absences payable/Workers'
236 Early Retirement Benefits - Current compensation, Early Termination
240 Capital Leases Payable Current portion of long-term debt
Compensated absences payable/Workers'
245 Workers Compensation compensation, Early Termination
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 13
Working with Interim Financial Statements
250 Other Liabilities Other liabilities
251 Funds Held in Trust Funds held for others
255 Securities Lending Collateral Securities lending collateral
256 Advances to Other Schools - Current Advances to other schools
265 Payable to Other Funds Payable to other funds
266 Advances to Other Schools - Noncurrent Advances to other schools
Compensated absences payable/Workers'
270 Compensated Absences Payable compensation, Early Termination
Compensated absences payable/Workers'
271 Early Retirement Benefits - Non Current compensation, Early Termination
275 Notes Payable Noncurrent portion of long-term debt
280 Revenue Bonds Payable Noncurrent portion of long-term debt
281 Revenue Bond Premium Payable Noncurrent portion of long-term debt
283 Bond Premium Payable Noncurrent portion of long-term debt
285 GO Bonds Payable Noncurrent portion of long-term debt
290 Capital Leases Payable Noncurrent portion of long-term debt
Compensated absences payable/Workers'
295 Workers Compensation compensation, Early Termination
298 Capital Contributions Payable Capital contributions payable
300 Invested in Capital Assets Net of Related Debt Invested in capital assets, net of related debt
305 Donations Restricted expendable, other
310 Perkins Loans Restricted expendable, other
315 Bond Covenants Restricted expendable
320 Capital Projects Restricted expendable, other
322 Restricted for Debt Service Restricted expendable, other
325 Faculty Contracts Restricted expendable, other
330 Legislatively Mandated Purposes Restricted expendable, other
335 Unrestricted Unrestricted Net Assets
400 Tuition Tuition, auxiliary and sales, net
405 Fees Tuition, auxiliary and sales, net
410 Sales and Services Tuition, auxiliary and sales, net
415 Room and Board Tuition, auxiliary and sales, net
420 Other Income Other income
423 Securities Lending Income Securities lending income
425 Student Loan Income Other income
430 Accrued Revenue Other income
435 Third Party Obligations Tuition, auxiliary and sales, net
445 Scholarship Allowance - Tuition Tuition, auxiliary and sales, net
450 Scholarship Allowance - Fees Tuition, auxiliary and sales, net
455 Scholarship Allowance - Sales & Svcs Tuition, auxiliary and sales, net
460 Scholarship Allowance - Room and Board Tuition, auxiliary and sales, net
495 Cost of Goods Sold Tuition, auxiliary and sales, net
499 Bond / Loan Proceeds (reclassify) Reclass
500 Salaries Salaries
502 Benefits Salaries
505 Purchased Services Purchased services
510 Supplies Supplies
515 Repairs & Maintenance Repairs and maintenance
520 Depreciation and Amortization Depreciation
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 14
Working with Interim Financial Statements
523 Securities Lending Rebates & Fees Securities lending rebates/fees
525 Other Expense Other expense
530 Capital Expenditures (reclassify) Reclass
535 Debt Service (reclassify) Reclass
600 State Appropriation Appropriations
605 Federal Grants Federal grants
610 State Grants State grants
615 Private Grants Private grants
620 Investment/Interest Income Interest income
625 Gifts and Donations Capital appropriations
650 Financial Aid Financial aid, net
651 Scholarship Allowance - Financial Aid Financial aid, net
655 Grants to Other Organizations Grants to other organizations
665 Interest Expense Interest expense
670 Intra-MnSCU Transfer In Transfers in *
671 Intra-MnSCU Transfer Out Transfers out **
672 Transfer-In Transfers in
673 Transfer-Out Transfers out
700 Capital Contributions Capital appropriations
701 Donated capital assets Donated assets and supplies
710 Gain (Loss) on Disposal of Capital Assets Gain (loss) on disposal of capital assets
905 Prior Period Adjustment Other expense
910 Change in Reporting Entity Change in Reporting Entity
990 Fund Balance Unrestricted Net Assets
* May be reclassified to Private Grants in published financial statements
** May be reclassified to Other Expense in published financial statements
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 15
Working with Interim Financial Statements
Statement of Net Assets
Assets
Rep Cat Current Assets
100 Cash and cash equivalents
105 Investments
120 Grants receivable
110,112 Accounts receivable, net
140 Prepaid expense
125 Inventory
145,115,128,135 Student loans and other assets, net
156 Due from other funds
147 Advances from other schools
150 Securities lending collateral
Current Restricted Assets
161 Cash and cash equivalents
162 Investments
Noncurrent Restricted Assets
169 Cash and cash equivalents
163 Other assets
164 Construction in progress
Noncurrent Assets
157 Advances from other schools
130,138 Student loans and other assets, net
165,170,175,176,180,185,199 Capital assets, net
Liabilities
Current Liabilities
205 Salaries payable
200 Accounts payable
210 Deferred revenue
215 Payable from restricted assets
218 Interest Payable
251 Funds held for others
220,225,230,240 Current portion of long-term debt
235,236,245 Compensated absences payable/Workers' compensation, Early Termination
250 Other liabilities
265 Payable to other funds
256 Advances to other schools
255 Securities lending collateral
Noncurrent Liabilities
266 Advances to other schools
275,280,281,283,285,290 Noncurrent portion of long-term debt
270,271,295 Compensated absences payable/Workers' compensation, Early Termination
298 Capital contributions payable
Net Assets
300 Invested in capital assets, net of related debt
315 Restricted expendable
Enter manual >>>>>>>> Restricted expendable, bond covenants
305,310,320,322,325,330 Restricted expendable, other
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 16
Working with Interim Financial Statements
Statement of Revenues, Expenses, and Changes in Net Assets
Rep Cat Operating Revenues
400,435,445,405,450,410,495,455,415,
460 Tuition, auxiliary and sales, net
405,450,410,495,455,415,460 Restricted student payments, net
605 Federal grants
610 State grants
420,430,425 Other income
Total operating revenues
Operating Expenses
500,502 Salaries
505 Purchased services
510 Supplies
515 Repairs and maintenance
530,535,499 Reclass
520 Depreciation
650,651 Financial aid, net
525,905 Other expense
Total operating expenses
Operating income (loss)
Nonoperating Revenues (Expenses)
600 Appropriations
615 Private grants
423 Securities lending income
620 Interest income
665 Interest expense
655 Grants to other organizations
523 Securities lending rebates/fees
Total nonoperating revenue (expenses)
Income (Loss) Before Other Revenues, Expenses, Gains, or
Losses
700,625 Capital appropriations
701 Donated assets and supplies
670,672 Transfers in
671,673 Transfers out
710 Gain (loss) on disposal of capital assets
Change in net assets
Enter manual: Beginning Net Assets Total Net Assets - Beginning of Year
910 Change in Reporting Entity
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 17
Working with Interim Financial Statements
STATEMENT OF CASH FLOWS
FOR THE YEAR ENDED JUNE 30, 20xx
Cash Flows from Operating Activities
Cash received from customers $ - Total Operating Revenue (IS)- State and Federal Grants +/- Change in AR (BS) +/- Change in Deferred Revenue
Federal grants - Federal Grants (IS)+/- Change in Grants Receivable (BS)
State grants - State Grants (IS)
Cash receipt (repayment) of program loans - Principal repaid on Perkins Loans less Loans Advances on Perkins Loans (Perkins leadsheet)
Cash paid to suppliers for goods or services - Misc see list below
Cash payments to employees - Salaries and Benefits (IS) +/- Change in S/P (BS) +/- Change in Comp Abs (BS) +/- Change in Work Comp (BS)
Financial aid disbursements - Financial Aid (IS) +/- Change in Capital Contributions Payable (BS)
Net cash provided (used) by operating activities -
Cash Flows from Noncapital and Related Financing Activities
Appropriations - State Appropriation (IS)
Agency activity - Change in Funds Held in Trust (BS)
Private grants - Private Grants (IS) (if using fundware IS add Intra mnscu transfers in as well)
Grants to other organizations - Grants to Other Organizations (IS)
Net cash flows from noncapital financing activities -
Cash Flows from Capital and Related Financing Activities
Capital appropriation - Capital appropriation (IS)
Proceeds from sale of capital assets - G/L on Sale (IS) - Cost basis of assets sold ( fixed asset leadsheet)
New Rev and GO Bonds, less Decrease in Revenue Bond Payable plus Current Portion of
Proceeds from borrowing (loan repayment) - GO Bond Debt Payable and Change in Capital Lease Payable (BS), includes Change in Bond Prem Payable (BS)
Investment in capital assets - Cell M29 on "Complex Tab"- more info in fixed asset leadsheet
Interest paid - Int Exp (IS) +/- Ch in Int Payable (BS) +/- Change in Prepaid Asset that is interest related (GO Bond activity leadsheet)
Net cash flows from capital and related financing activities -
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 18
Working with Interim Financial Statements
Cash Flows from Investing Activities
Proceeds (Purchases) of investments - Value of investment sold or matured; If no new investment purchases, should match change in Investment (BS)
Investment earnings - Investment Income (IS) less Bond Prem Amort amount (GO Bond leadsheet) +/- Change in Accr Inv Income (BS)
Proceeds from securities lending transactions - Securities lending income and expense (IS)
Net cash flows from investing activities -
Net Increase (Decrease) in Cash and Cash Equivalents - Change in Cash (BS)
Cash and Cash Equivalents, July 1, 20xx - Cash at the beginning of the year (BS)
Prior Period Adjustment -
Cash and Cash Equivalents, July 1, 20xx, as adjusted -
Cash and Cash Equivalents, June 30, 20x(x+1) $ - Cash at the end of the year (BS)
D:\Docstoc\Working\pdf\d2a3869e-1760-4726-a054-ab8fb5d6f667.doc 19
