Transcript Auxiliary Financial Statements

Auxiliary Financial
Statement
Training Session
For Year End 2012
Presented by:
Finance and Accounting
General Accounting
Agenda
Agenda

Discuss the detailed steps required for preparing
and submitting financial statements for auxiliaries,
including:
accrual information – Adjusting Entries
 nVision Reports
 Adjusted Trial Balance
 Notes to SNA and SRECNA
 The

Discuss the information requested for quarters 1
through 3
3
Reporting Requirement

Auxiliaries with annual revenues greater than
one million dollars are required to submit annual
(year-end) financial statements only:
www.fa.ufl.edu/directives-and-procedures/auxiliaryeducationalbusiness-accounting/#financial

Each quarter the same auxiliaries must provide
the detail of the outstanding accounts receivable
and accounts payable.
4
Auxiliary Financial Statements and
UF Financial Statements


Auxiliaries having their own fund will not
prepare Fund Level statements for FY12.
The accrual information provided within
auxiliary financial statements will still be
included by General Accounting/Financial
Reporting in the preparation of the
University financial statements.
5
Preparing Financial
Statements:
Process Overview
Process Overview







Collect accrual information
Prepare adjusting entries
Submit adjusting journals to General Accounting
Run nVision and copy report in the workbook
Run Trial Balance query and format
Provide details for the Accounts Receivable and
Accounts Payable
Explain any significant variations on the SNA and
SRECNA in comparison to prior period.
7
PROCESS
→ Adjusting Entries Accruals
Adjusting Entries – Journal template
 All
adjusting entries must be prepared on a journal
template using PeopleSoft chartfield values to be posted
on the adjustment ledger.
9
Adjusting Entries – Debits and Credits
Account
Type
To Increase
To Decrease
Asset
Debit (+)
Credit (-)
Liability
Credit (-)
Debit (+)
Revenue
Credit (-)
Debit (+)
Expense
Debit (+)
Credit (-)
10
Possible Adjusting Entries
Debit (+)
Credit (-)
Accrued Accounts
Receivable
132200
440400/440500
Accrued Accounts
Payable
719300/729200/731100
742200/799900
211000
Deferred Revenue
440500
263000
Prepaid Expense
170000
719300/729200/731100
742200/799900
Accrued Interest
134100
521000
Inventory
151000 /152000
731100
Compensated Absences
Liability
699000
291000
Allowance for Doubtful
Accounts
870000
139100
Note: Adjusting entries should not include or generate any cash lines.
11
Adjusting Entries
All adjusting entries can be posted to one single
department ID in your auxiliary range.

All adjusting entries will be automatically reversed on
the first day of the following period, except the
Compensated Absences Liability and the year-end
Capital Assets reconciliation.

12
Adjusting Entries
Compensated Absences Liability
$115,000.00
The entire amount of the liability must been adjusted since the
prior year amount has be reversed by General Accounting. Enter on
a separate journal.
 The entry will be reversed only in June 2013.

Note1: Please refer to separate instructions on how to retrieve the report.
Note 2: Numbers reported in this presentation are for training purpose only and
do not necessarily represent accurately the financial position of University
Press of Florida.
13
Adjusting Entries - Capital Asset Reconciliation


Complete the
Capital Assets tab
in the workbook
(yellow
highlighted cells):
Refer to your Q1
Financial
Statements for
the beginning
Capital Assets
and Accumulated
Depreciation after
adjustment for
new capitalization
threshold.
14
Adjusting Entries - Capital Asset Reconciliation

The tab includes a journal template that will
be automatically populated:

Keep the Capital Assets reconciliation entries
on a separate journal.
15
Adjusting Entries - Capital Asset Reconciliation

The entry will not be reversed as of
07/01/2012.

After adjustment for capital assets all
78xxxx accounts on the Adjusted Balance
column should be zero except for
Depreciation Expense:
ACCOUNT
781100
788000
TOTAL
FINANCIAL STATEMENT CODE
Other Operating Expenses
Depreciation
ACTUALS BALANCE
19,503.00
19,503.00
UF_AUX_ADJ BALANCE
(19,503.00)
7,500.00
(12,003.00)
ADJUSTED BALANCE
0.00
7,500.00
7,500.00
16
Adjusting Entries – Journal Mask

Use the appropriate
journal mask (3
letters) followed by
AJ, year, month and
1 thru 9:
UPFAJ12061
UPFAJ12062
17
Adjusting Entries – Posting




Submit your journal templates to [email protected]
Please submit your adjusting entries at least two
days prior to the August 3rd deadline.
General Accounting will post them and notify
you.
Once all your adjusting entries are posted, you
can run the nVision report and the final adjusted
trial balance.
18
PROCESS
→ nVision Report
nVision Report

In MyUFL ,navigate to People
Soft and Financials PeopleTools.

In the new window, navigate to
Reporting Tools / PS/nVision /
Define Report Request.
20
nVision Report

On the Report Request
page, search for report ID
“AUX_REP”

On the nVision
Report Request
page, select the
appropriate date:
06/30/2012 for
year-end.

Click on Scope and
Delivery Templates.
21
nVision Report

Search for Report Scope
starting with AUX.


Each auxiliary has its
own scope.
Select the appropriate
scope and click OK to
return to prior page.
22
nVision Report

Click Run Report on
the nVision Report
Request page.

Click OK on the
Process Scheduler
page.
23
nVision Report



Click Process
Monitor on
the nVision
Report
Request
page.
On Process
Request
page, click
refresh to
update the
status
Wait for
Success and
Posted
24
nVision Report


Navigate to
Report Manager
either through
the nVision
Report Request
page or the top
menu.
On the Report
Manager page
click on
Administration
tab and
retrieve the
report.
25
nVision Report – Copy to workbook

Copy the SNA and the
SRECNA for both years
To do so:


Copy separately each
of the 4 blocks: SNA
current and prior
period, SRECNA
current and prior
period.
Paste each in the
appropriate area of the
workbook
(SNA&SRECNA tab)
using paste special
value
26
PROCESS
→ Trial Balance –
ACTUALS + UF_AUX_ADJ
Trial Balance – ACTUAL and UF_AUX_ADJ

Run trial balance query: UF_AUX_TB_FS with the
appropriate prompts:

Download in Excel:
Note: The same query is used to pull the balances from both Actuals and
UF_AUX_ADJ ledgers.
28
Trial Balance – ACTUAL and UF_AUX_ADJ
1
 Save
the file.
 Format
using
Excel
pivot table
tool.
 Remove
any
subtotals
from the
Pivot
Table.
3
2
29
Trial Balance – ACTUAL and UF_AUX_ADJ

Copy the entire pivot table (excluding the headers) in the
workbook under tab - Adjusted Trial Balance using paste
special value:
Pivot
Table
Workbook
30
PROCESS
→ Accounts
Receivable Detail
Accounts Receivable


Accrued accounts receivable should be categorized
between interdepartmental sales (ID Sales) & Non UF
Sales, then by age.
Provide the status of any receivables over 120 days.
32
Accounts Receivable
33
PROCESS
→ Accounts Payables
Detail
Reconciliation – Recap of Accrued AP


Accrued accounts payable should be listed by vendors
and categorized by age.
Provide the status of any payables over 60 days.
35
Accrued Accounts Payable
36
Accrued Accounts Payable

If no record is available the following query can be used:
FR_AP_QTR_ACCRUAL
37
PROCESS
→ Notes to SNA and
SRECNA
Notes to SNA and SRECNA

Provide a brief explanation for any significant variations
on the SNA and SRECNA in comparison to prior period.

Pay special attention to the following lines: Cash &
Investments, Accounts Receivable, Operating Revenues,
Employee Compensation and Benefits and Services &
Supplies.
39
Available Queries
Available Queries





UF_AUX_TB_FS
 Trial Balance for ACTUALS and UF_AUX_ADJ ledger
UF_GL_TB_BY_FUND_DEPT_AUX_QRY
 Trial Balance for ACTUALS ledger
UF_GL_JOURNAL_DETAIL_QUERY2
 Provides the entries making up the ACTUALS Trial
Balance
UF_AUX_ADJ_JRNL_DETAIL
 Provides the entries making up the UF_AUX_ADJ Trial
Balance
FIT Cube
41
Financial Statement Due Date
Submit your completed
workbook to [email protected] no
later than:
August 3, 2012
Please submit your adjusting
entries at least two days prior to
the deadline.
42
Quarterly Information - Accounts Receivable
and Accounts Payable
 Auxiliaries with annual revenues
greater than one million dollars are
required to report the accounts
receivable and accounts payable at
the end of each quarter.

This information is included in the
quarterly report to the Board of
Trustees and it is also reported to the
Auxiliary Enterprise Committee.
43
Quarterly Information – Variance Analysis
Report

General Accounting will send a
variance analysis report at the end of
the second and the third quarter

This report is on a cash basis and
groups GL accounts in just a few
broad categories.
44
Quarterly Information – Variance Analysis
Report

Provide a brief explanation for any
significant variations
45
Any Questions?
Contacts
We are happy to assist you!

Patrice Lecomte



Jiachun Zhan



[email protected]
294-7256
[email protected]
294-7258
Sheela Moudgil


[email protected]
294-7255
47