Auxiliary Financial Statement Training Session

1
Auxiliary Financial StatementTraining
Session
For Fiscal Year 2007-2008 Presented by General
Accounting Finance and Accounting
2
Agenda
3
Agenda

• 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

4
New 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

5
Process Overview
6
Process 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.

7
Process 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.

8
PROCESS? Trial Balance
9
Trial Balance Query Navigation

• Security Roles Needed
• UF_FI_FUNC_USER
• UF_GL_RESEARCH_REPORTING
• PeopleSoftgtFinancials People ToolsgtReporting
  Toolsgt Querygt
• Query Viewer

10
Trial Balance Query Procedures

• Search for query
• UF_GL_TB_BY_FUND_DEPT_AUX_QRY
• Aux year end TB by Fund Dept  
• Run to HTML

11
Trial 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.
12
Trial 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

13
Pivot Table Steps

• In Excel, open the saved Trial Balance file just
  created.
• Step 1 - Go into
• Data ? PivotTable and PivotChart Report

1
14
Pivot Table Steps (cont)

• Step 2 Accept the default settings and click
  Next.

15
Pivot Table Steps (cont)

• Step 3 - Verify that the cells selected include
  all cells with data.
• Step 4 - Click Next
• Step 5 Click Layout

16
Pivot 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
17
Pivot Table Steps (cont)

• Step 8 - Click Finish

18
Pivot Table Steps (cont)
19
PROCESS? Trial Balance Reconciliation
20
Trial 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

21
Trial 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

22
Trial 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

23
Trial Balance Reconciliation Overview
Financial Statement Template
24
Trial 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
25
TB 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.

26
TB 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
27
TB 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.
28
TB Reconciliation Balances from Pivot Table

• Once completed, the sum of the debits and credits
  of each individual column should be zero.

TB Reconciliation
29
PROCESS? Previous Year SNA Balances
30
TB 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
31
TB Reconciliation Previous Year SNA

• Once completed, the sum of the debits and credits
  in the Previous Year SNA column should be zero.

TB Reconciliation
32
TB 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
33
PROCESS? Reversing Entries
34
TB 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.

35
TB Reconciliation Reversing Entries

• All reversing entries are double side entries
  Debit and Credit

36
TB Reconciliation Reversing Entries

• The sum of the debits and credits of Reversing
  Entries column should be zero.

37
PROCESS? Adjusting Entries - Accruals
38
TB 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.

39
TB Reconciliation Adjusting Entries

• All reversing entries must be double-sided
  entries Debit and Credit

40
TB Reconciliation Adjusting Entries Accrued
Accounts Receivable
41
TB Reconciliation Adjusting Entries Deferred
Revenues
42
TB 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
43
TB Reconciliation Possible Adjusting Entries
44
TB Reconciliation Possible Adjusting Entries
45
TB Reconciliation Adjusting Entries

• The sum of the debits and credits of Adjusting
  Entries column should be zero.

46
PROCESS? Capital Assets
47
TB 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.

48
TB 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.
49
TB 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.

50
TB 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.
51
PROCESS? Adjusted Trial Balance Column
52
Adjusted 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.

53
Adjusted Trial Balance Column

• The sum of the debits and credits of Adjusted
  Trial Balance column should be zero.

54
Adjusted 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.

55
TB 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.

56
Ending Total Net Assets SRECNA SNA

• The Ending Total Net Assets on the SRECNA and on
  the SNA should be the same.

57
PROCESS? Recap of Accrued Accounts Receivable
58
Reconciliation 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.

59
PROCESS? Recap of Accrued Accounts Payables
60
Reconciliation 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.

61
Available Queries
62
Available 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

63
Review 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.

64
Financial 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.

65
Any Questions?
66
Contacts

• We are happy to assist you!
• Patrice Lecomte
• Email plecomte_at_ufl.edu
• Phone [phone] ext. 626
• Sheela Moudgil
• Email moudgils_at_ufl.edu
• Phone [phone] ext. 410