Title: Auxiliary Financial Statement Training Session
1Auxiliary Financial StatementTraining
Session
For Fiscal Year 2007-2008 Presented by General
Accounting Finance and Accounting
2Agenda
3Agenda
- Discuss the general flow of information related
to preparing year-end financial statements for
auxiliaries. - Discuss the detail steps required for preparing
and submitting financial statements for
auxiliaries, including - Identifying and reconciling the data
- Preparing the financial statements in the proper
format - Review available queries
- Discuss due date - August 08, 2008
4New this Year
- Only auxiliaries with annual revenues greater
than one million dollars will be required to
submit quarterly and annual (year end) financial
statements to General Accounting. - New Financial Statement template
- Some minor modifications on the SRECNA and the
SNA - One single sheet with all the reconciliations and
adjustments
5Process Overview
6Process Overview
- Run a trial balance query, download to Excel, and
organize the data with a pivot table. - Transfer the numbers from the trial balance pivot
table to the Trial Balance Reconciliation sheet. - Enter the numbers from the prior year Statement
of Net Assets (SNA) to the Trial Balance
Reconciliation sheet. - Reverse prior year accruals Reversing Entries.
7Process Overview (cont)
- Prepare current year accruals on the Trial
Balance Reconciliation sheet. - Input information from Depreciation Report.
- The last column of the Trial Balance
Reconciliation sheet will automatically populate
the Financial Statements - SRECNA SNA. - Complete SRECNA for prior year Comparative
information only. - Provide details for the accrued Accounts
Receivable and accrued Accounts Payable.
8PROCESS? Trial Balance
9Trial Balance Query Navigation
- Security Roles Needed
- UF_FI_FUNC_USER
- UF_GL_RESEARCH_REPORTING
- PeopleSoftgtFinancials People ToolsgtReporting
Toolsgt Querygt - Query Viewer
10Trial Balance Query Procedures
- Search for query
- UF_GL_TB_BY_FUND_DEPT_AUX_QRY
- Aux year end TB by Fund Dept Â
- Run to HTML
11Trial Balance Query Procedures (cont)
- Input criteria for query
- Fiscal Year 2008
- Accounting Period 12
- SetID UFLOR
- From Fund Dept Fund
- To Fund Dept Fund
- From Dept Beginning Dept ID
- To Dept Ending Dept ID
- (Dept IDs and Funds that you are preparing
financial statements for) - View Results
All data used in this presentation are for
training purposes only and do not necessarily
represent accurately the departments activities.
12Trial Balance Query Procedures (cont)
- Download into Excel
- Save file as
- Dept ID_fund_TB_2008
- (ex.13500xxx_149_TB_2008)
- Cannot create Pivot Table w/o first saving file.
- Organize Data with Pivot Table Function
13Pivot Table Steps
- In Excel, open the saved Trial Balance file just
created. - Step 1 - Go into
- Data ? PivotTable and PivotChart Report
1
14Pivot Table Steps (cont)
- Step 2 Accept the default settings and click
Next.
15Pivot Table Steps (cont)
- Step 3 - Verify that the cells selected include
all cells with data. - Step 4 - Click Next
- Step 5 Click Layout
16Pivot Table Steps (cont)
- Step 6 Construct the Table
- A Drag Fund DeptID to COLUMN
- B Drag Fin St Code to ROW
- C Drag Sum of Net Balance to DATA
- Step 7 Click OK
6
17Pivot Table Steps (cont)
18Pivot Table Steps (cont)
19PROCESS? Trial Balance Reconciliation
20Trial Balance Reconciliation Purpose
- Reconcile and combine data from different
sources - PeopleSoft Trial Balance
- Prior Year Financial Statement
- Depreciation Report from Asset management
- Internal Records
- Adjust PeopleSoft Trial Balance for any relevant
accruals - Accrued Accounts Receivable
- Accrued Accounts Payable
- Deferred Revenues
- Prepaid Expenses
- Allowance for Doubtful Accounts
- Inventory
- Compensated Absences
- Others
21Trial Balance Reconciliation Structure
Rows
- Assets
- Cash
- Accounts Receivable
- Inventory
- Loan Receivable
- Prepaid Expenses
- Capital assets
- Liabilities
- Accounts Payable
- Compensated Absences
- Deferred Revenues
- Loan Payable
- Revenues
- Operating Revenues
- Nonoperating Revenues
- Expenses
- Personnel Services
- Services and Supplies
- Utilities and Communications
- Overhead
- Nonoperating Expenses
- Gain/Loss on Capital Assets
- Transfers In/Out
- Net Assets
22Trial Balance Reconciliation Structure
Columns
- PeopleSoft Trial Balance
- Statement of Net Assets (SNA) as of 06/30/07
- Reversing Entries
- Adjusting Entries
- Asset Management Data
- Adjusted Trial Balance
- Financial Statement Template
23Trial Balance Reconciliation Overview
Financial Statement Template
24Trial Balance Reconciliation Start
- Save blank Aux FS Forms as FS_Dept ID_fund_YE08
- (ex. FS_13505xxxx_149_YE08)
- Copy the PeopleSoft Trial Balance data to the FS
template TB-Data tab. - Copy the PeopleSoft Trial Balance Pivot Table to
the FS template TB-Pivot Table tab. Use Paste
Special Values and number formats.
FS Tabs
25TB Reconciliation Header Information
- At the top of TB Reconciliation (TBREC) enter
your Auxiliarys - Name (cell E3)
- Dept ID (starting from cell G5)
- Fund (starting from cell G6)
- Other sheets will be automatically populated with
this information.
26TB Reconciliation Balances from Pivot Table
- Enter departmental balances from the Pivot Table
in the appropriate cells on the TB Reconciliation
sheet for each financial statement code (Cash, Op
Rev, Overhead). - PeopleSoft figures should be entered in the
yellow colored cells only. - Insert new columns if necessary.
TB Reconciliation
27TB Reconciliation Balances from Pivot Table
TB Pivot Table
TB Reconciliation
All balances in debit (credit) are reported as
positive (negative) figures. Just report the
numbers as they appear on PS TB.
28TB Reconciliation Balances from Pivot Table
- Once completed, the sum of the debits and credits
of each individual column should be zero.
TB Reconciliation
29PROCESS? Previous Year SNA Balances
30TB Reconciliation Previous Year SNA
- Enter all the balances from the 2007 Statement of
Net Assets (SNA) in the appropriate cells on the
TB Reconciliation. - SNA figures should be entered in the orange
colored cells only. - These balances will automatically populate the
2007 column on the SNA.
TB Reconciliation
31TB Reconciliation Previous Year SNA
- Once completed, the sum of the debits and credits
in the Previous Year SNA column should be zero.
TB Reconciliation
32TB Reconciliation SNA
- At this point on the SNA tab, 2007 and 2008
- Total Assets - Total Liabilities and Net Assets
0 - All subsequent entries should not modify this
status. - The financial statements are not yet completed.
SNA
33PROCESS? Reversing Entries
34TB Reconciliation Reversing Entries
- The reversing entries are required to reverse the
prior year accrual entries. - The gray colored cells are for the reversing
entries. - All reversing entries are automatically computed.
- Review them and verify they are logical and
accurate.
35TB Reconciliation Reversing Entries
- All reversing entries are double side entries
Debit and Credit
36TB Reconciliation Reversing Entries
- The sum of the debits and credits of Reversing
Entries column should be zero.
37PROCESS? Adjusting Entries - Accruals
38TB Reconciliation Adjusting Entries
- The Adjusting Entries are required to reflect any
information not yet entered in PeopleSoft or to
comply accounting principles Revenue
Recognition, Matching Principle - Adjusting entries should be entered in the blue
colored cells only. - The information is from different sources
including your internal records.
39TB Reconciliation Adjusting Entries
- All reversing entries must be double-sided
entries Debit and Credit
40TB Reconciliation Adjusting Entries Accrued
Accounts Receivable
41TB Reconciliation Adjusting Entries Deferred
Revenues
42TB Reconciliation Adjusting EntriesCompensated
Absences Liability
Note The Compensated Absences Liability report
is only available at year end. Please use the
same liability number for quarterly FS
43TB Reconciliation Possible Adjusting Entries
44TB Reconciliation Possible Adjusting Entries
45TB Reconciliation Adjusting Entries
- The sum of the debits and credits of Adjusting
Entries column should be zero.
46PROCESS? Capital Assets
47TB Reconciliation - Capital Asset Reconciliation
- A reconciliation to the Depreciation Report is
required. - The accurate information is from this
Depreciation Report. - All reconciliations are prepared on the TB
Reconciliation No more specific Capital Asset
Reconciliation schedule - Depreciation Report data should be entered in the
light green colored cells only.
48TB Reconciliation - Capital Asset Reconciliation
All data used in this presentation are for
training purposes only and do not necessarily
represent accurately the departments activities.
Please provide a reconciliation sheet if the
figures you use are different from the
Depreciation Report.
49TB Reconciliation - Capital Asset Reconciliation
- The reconciliation is automatically computed on
the adjusted TB Reconciliation. - The results are a gain or loss on the capital
assets and on the accumulated depreciation. Both
are combined on the SRECNA.
50TB Reconciliation - Capital Asset Reconciliation
- Capital Assets
- Ending Capital Assets on FS (2007)
- () Capital Assets Expenses (2008)
- (-) Accrued AP (2007)
- () Accrued AP (2008)
- Total Capital Assets per FS
- Compare to
- Capital Assets per Depreciation Report
- Any difference will result in a gain or loss on
Capital Assets.
Accumulated Depreciation Ending Accumulated
Depreciation on FS (2007) () Depreciation
Expense (2008) Total Accumulated Depreciation per
FS Compare to Accumulated Depreciation per
Depreciation Report Any difference will result
in a gain or loss on Accumulated Depreciation.
51PROCESS? Adjusted Trial Balance Column
52Adjusted Trial Balance Column
- All totals are automatically computed in the
Adjusted TB column. - If new rows are added for entry types not already
designed on the template, the adjusted TB column
might required some modifications. For support
contact General Accounting.
53Adjusted Trial Balance Column
- The sum of the debits and credits of Adjusted
Trial Balance column should be zero.
54Adjusted Trial Balance Column
- The numbers from the Adjusted Trial Balance
column will automatically populate the SRECNA and
SNA. - No data should be entered directly on the SRECNA
(except prior year balances) or SNA.
55TB Reconciliation Adjustment to Beginning Net
Asset
- No adjustment needed for Amount to be Financed in
the Future (Compensated Accumulated Liability). - No adjustment needed for Replacement Cash Asset
reclassification. - The Adjustment to Net Beginning Asset is not the
magic solution to bring the FS in balance. - Any adjustment to Beginning Net Asset must be
justified by appropriate documentation.
56Ending Total Net Assets SRECNA SNA
- The Ending Total Net Assets on the SRECNA and on
the SNA should be the same.
57PROCESS? Recap of Accrued Accounts Receivable
58Reconciliation Recap of Accrued AR
- Input current year Accrued AR and break it down
by interdepartmental sales (ID Sales) Non UF
Sales. - Break down the AR by age.
- This total will be the same as the TBREC.
59PROCESS? Recap of Accrued Accounts Payables
60Reconciliation Recap of Accrued AP
- Accrued AP should be divided into PS Accounts.
- Break down the AR by age.
- The total on the Recap of Accrued AP is the total
on the Accrued Accounts Payable on the TBREC.
61Available Queries
62Available Queries
- UF_GL_JOURNAL_DETAIL_QUERY2
- Provides the entries making up the Trial Balance
- UF_GL_JOURNAL_DETAIL_QUERY2_AR
- Provides ONLY AR journals from direct deposit
- UF_VOUCHER_LOOKUP2
- Provides vouchers for a certain account or vendor
payment - UF_EX_RPTS_FOR_JRNLID
- Provides Expense reports with Travel Journal ID
63Review of the Process
- Run a PS trial balance query.
- Transfer trial balance pivot table balances to
the Trial Balance Reconciliation sheet. - Enter the numbers from the prior year SNA to the
Trial Balance Reconciliation sheet. - Reverse prior year accruals (automatic).
- Prepare current year accruals.
- Input information from Depreciation Report.
- Complete SRECNA for prior year.
- Provide details for the accrued AR and accrued AP.
64Financial Statement Due Date
- Remember that financial statements are due to
Auxiliary Accounting by - August 8, 2008
- Financial Statements can be submitted in
electronic format. You can either request your
director to submit them by email or include a
scan copy of the signed cover sheet.
65Any Questions?
66Contacts
- We are happy to assist you!
- Patrice Lecomte
- Email plecomte_at_ufl.edu
- Phone 392-1326 ext. 626
- Sheela Moudgil
- Email moudgils_at_ufl.edu
- Phone 392-1326 ext. 410