Auxiliary Financial StatementTraining Session

For Fiscal Year 2007-2008Presented by:General AccountingFinance and Accounting

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

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.

PROCESS→ Trial Balance

9

Trial Balance Query – Navigation

Security Roles Needed:

UF_FI_FUNC_USER

UF_GL_RESEARCH_REPORTING

PeopleSoft>Financials People Tools>Reporting Tools> Query>

Query Viewer

10

Search for query

UF_GL_TB_BY_FUND_DEPT_AUX_QRY

Aux year end TB by Fund & Dept   Run to HTML

Trial Balance Query – Procedures

11

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

Trial Balance Query – Procedures (cont)

All data used in this presentation are for training purposes only and do not necessarily represent accurately the departments’ activities.

12

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

Trial Balance Query – Procedures (cont)

13

Pivot Table – Steps

1

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

2

15

Step 3 - Verify that the cells selected include all cells with data.

Step 4 - Click “Next”

Step 5 – Click “Layout”

3

5

Pivot Table – Steps (cont)

4

16

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”

Pivot Table – Steps (cont)

B

A

7C

6

17

Pivot Table – Steps (cont)

Step 8 - Click “Finish”

8

18

Pivot Table – Steps (cont)

Format cells:

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

1. AssetsCash

Accounts Receivable

Inventory

Loan Receivable

Prepaid Expenses

Capital assets

2. LiabilitiesAccounts Payable

Compensated Absences

Deferred Revenues

Loan Payable

Rows3. Revenues

Operating Revenues

Nonoperating Revenues

4. Expenses Personnel Services

Services and Supplies

Utilities and Communications

Overhead

Nonoperating Expenses

Gain/Loss on Capital Assets

5. Transfers In/Out

6. Net Assets

22

Trial Balance Reconciliation: Structure

1. PeopleSoft Trial Balance

2. Statement of Net Assets (SNA) as of 06/30/07

3. Reversing Entries

4. Adjusting Entries

5. Asset Management Data

6. Adjusted Trial Balance

Financial Statement Template

Columns

23

Trial Balance Reconciliation: Overview

Financial Statement Template

24

Trial Balance Reconciliation: Start1. Save blank Aux FS Forms as FS_Dept ID_fund_YE08

(ex. FS_13505xxxx_149_YE08)

2. Copy the PeopleSoft Trial Balance data to the FS template: TB-Data tab.

3. Copy the PeopleSoft Trial Balance Pivot Table to the FS template: TB-Pivot Table tab. Use Paste Special – Values and number formats.

3 2

FS Tabs

25

At the top of TB Reconciliation (TBREC) enter your Auxiliary’s Name (cell E3) Dept ID (starting from cell G5)

Fund (starting from cell G6)

Other sheets will be automatically populated with this information.

TB Reconciliation – Header Information

Auxiliary Name

Fund

Dept ID

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. *All balances in debit (credit) are reported as positive (negative) figures. Just report the numbers as they appear on PS TB.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

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

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.

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

Account Type

To Increase To Decrease

Asset Debit (+) Credit (-)

Liability Credit (-) Debit (+)

Revenue Credit (-) Debit (+)

Expense Debit (+) Credit (-)

40

TB Reconciliation – Adjusting EntriesAccrued Accounts Receivable

41

TB Reconciliation – Adjusting EntriesDeferred 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

Debit Credit

Accrued Accounts Receivable

Accrued Accounts Receivable

Op Revenues

Accrued Accounts Payable

ExpensesAccrued Accounts

Payable

Deferred Revenue Op RevenuesDeferred Revenue

(Liability)

Prepaid Expense Prepaid Expense (Asset) Expenses

Accrued Interest Interest Receivable Interest Revenue

44

TB Reconciliation – Possible Adjusting Entries

Debit Credit

Compensated Absences Liability

Personnel ExpensesCompensated Absences

Liability

Write-off Allowance for Bad Debt AR

Allowance for Doubtful Accounts

Bad Debt ExpenseAllowance for Doubtful

Accounts (Contra-Asset)

Petty cash (Asset Reclassification)

Petty cash Cash

45

TB Reconciliation – Adjusting Entries

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

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

Depreciation Report

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

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.

TB Reconciliation - Capital Asset Reconciliation

50

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.

TB Reconciliation - Capital Asset Reconciliation

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

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.

TB Reconciliation – Adjustment to Beginning Net Asset

56

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

Ending Total Net Assets: SRECNA & SNA

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.

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.

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 DateRemember that financial

statements are due to Auxiliary Accounting by

August 8, 2008Financial 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.

Any Questions?

66

ContactsWe are happy to assist you!

Patrice Lecomte Email: plecomte@ufl.edu Phone: 392-1326 ext. 626

Sheela Moudgil Email: moudgils@ufl.edu Phone: 392-1326 ext. 410