year end close workshop - sfcontroller.org · city and county of san francisco controller’s...
TRANSCRIPT
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
1
Year End Close Workshop
Presented By:Controller’s Office &
Office of Contract AdministrationMay 15, 2017
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
2
Introduction by Controller
Ben RosenfieldController’s Office
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
3
Agenda9:00 Introduction by the Controller
9:05-9:40 Logging Into SF Employee PortalDress RehearsalBlack Out DatesFile Submissions & ConversionsData Validation & General LedgerRole MappingUsing the Incident Command Center
9:40-9:50 Questions
9:50-10:00 Break
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
4
Agenda10:00-11:30 City Preparedness for the New Accounting System
Processing Transactions in Two Accounting SystemsFY 17 Year End CloseDual ProcessingYE Reconciliations, Financial Statements & FY 18 BudgetsGrantsFixed Assets
11:30-11:50 Office of Contract Administration
11:50-12:00 Questions
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
5
Update by F$P
Jack Wood/Julie Ansell
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
6
It’s Official!
6
We’re Going Live Monday, July 3rd
7
Logging Into the SF Employee Portal – Get Started
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
8
Be Ready for Go-Live
Verify Your Access
Locate Your Training
Schedule Time for Training
Identify Your Super Users
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
9
Be Ready for Go-Live
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
10
Save Pain at the Finish Line—Verify IAM• New Messaging
Next Week at Navigation Sneak Peek
• Users should verify they can get to Employee Portal
• Super Users have been instructed how to help
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
11
Identity and Access Management (cont.)
• This page is accessed by the Home link on the IAM login page
• Allows you to complete:– Password Changes– Security Questions– Enhanced Protection
Registration
• Close this window when tasks completed
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
12
SF Employee Portal12
• After IAM login, arrive at…
• SF Employee Portal
• Self-servicebegins here
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
13
SF Employee Portal13
Use Work Links to access:
• Learning
• Financials
• Procurement
• Reports & Analytics
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
14
Learning – My Learning Page14
FSP online training will be available in the Learning system
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
15
Online Learning15
• Online Learning tool: UPK (User Productivity Kit)
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
16
Expense Reimbursements16
Expense Reimbursement made available in the SF Employee Portal
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
17
FSP TRAINING ROLL OUT
Key Statistics
4750 50 70 246Internal Users
May 1 - 15 May 15 - 31 June 1 - 15 June 15 - 30 July 1 - 15 July 15 - 31 August 1 - 15 Aug 15 - 31 Sept 1 - 15 Sept 15 - 30
Sneak Peek Sessions
Instructor Led ClassesPrior to Go-Live
E-Learning Courses
Days of Open Lab
E-Learning Courses
Instructor Led Classes
Open Labs
Sneak Peeks Knowledge Base
175Knowledge
Base Articles
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
18
Sneak Peeks
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
19
SF Employee Support Portal
Functional Team
Technical Team
AOSD
DT
Password Reset
Policy Questions
OCA
25,000 Vendors
4750 City Employees
Incidents can be reported via--email--telephone--support ticket
600 Super Users
Tier 2 Issues
Tier 3 Issues
HELP DESK
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
20
Starting June 5 for F$P
Bookmark this Page
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
21
Vendor Portal: sfcitypartner.sfgov.org
Vendor website hits: 70,779
POC Total Updates:8,478
ACH POC Updates:1,813
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
22
ACH/Paymode Vendors • In next phase, we are targeting
Paymode Vendors to update their POC
• Follow up email to ACH Vendors • ACH will take one to two weeks
to conduct the “pre-note process”
• We’ve issued a spreadsheet of ACH Vendors who have not responded to date.
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
23
Additional F$P Topics…
• Expense Reimbursement – Submitters & Approvers
• Critical July payments
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
24
Dress Rehearsal
Jerry Wong
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
25
What’s Dress Rehearsal?
• Dress Rehearsal – a practice of production deployment. • Follow schedule as planned deployment
• Hold checkpoint/status meetings
• Task execution are planned during working hours during the work week where possible
• Task Owners will validate task timings
Plan
Practice
Execute
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
26
What to Expect Deployment moves
quickly Your Departmental
Checklist will help ensure success
Flexibility may be required You will not always know
the answer Expect the unexpected! Have fun!!!
We’re Going Live!!!
CommunicationsClear • Honest • Open • Timely
CollaborationAgile • Team Oriented •
Relationship Focused • Helpful
FunHumor • Joy • Balance Minded• Can- Do Attitude • After Hours
PatienceCourtesy • Flexibility
• Perseverance • Compromise
RespectConsideration• Civility • Self-Reliance • Proactive • Polite
Stakeholder DelightExceed Expectations • Seek
Quality • Customer Oriented
Roger that!
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
27
Dress Rehearsal – Pre Cutover Schedule Activities Dates Primary Team Refresh Configuration (FSPRD from FSCFG)
5/3/2017 Tech Team
Functional Team Environment Shakeout
Starting 5/5 Application Team
Early Submission Conversion Files Received
5/11 Conversion and Application Team
Early Submission Conversion FileExecution
5/ 15-17 Conversion and Application Team
eMerge notification to proceed Midday 5/17 Project Leadership, eMerge Team
eMerge Full Sync & Workforce Sync 5/18 – 19,5/22-23
eMerge Team
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
28
Dress Rehearsal - Cutover Schedule Activities Dates Primary Team
Master Data Conversion Files Received 5/15 Conversion and Application Team
Master Data Conversion File Execution 5/19-22 Conversion and Application Team
Transaction Data Conversion Files Received 5/17 Conversion and Application Team
End User Security Load 5/20-23 Security Team
Transaction Data Conversion File Execution 5/23-26 Conversion and Application team
Flashback and Smoke Testing 5/26 Tech Team and Application team
BI Full Sync 5/26 BI Team
“Go-Live Date” 5/30
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
29
Meeting Schedule
• Cutover Command Center – 1155 Market Street (Jack’s Office)– 5/11 through 5/26
• Daily Dress Rehearsal Status Emails are sent to:– Project Leadership– Team Leads– Task Owners
• Deployment Status Meetings 10am – eMerge Check In – PeopleSoft Conversion Updates– Invitation out already– Attendees
• Conversion Departments • F$P Team• AOSD Team• OCA Team• Departments
• Dial In – Web Ex invitation out already
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
30
Dress Rehearsal – File Submission
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
31
Cutover
HaShawn Epps/Keith DeMartini
32
Blackout Dates
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
33
Blackout DatesTransaction Guidance
FY16-17 System Blackout Starting
at 5pm
Conversion File Submittal
Dates
Begin Processing FY 17/18 in F$P
Supplier - New Add new Supplier Entry in FAMIS by end of day 5/19/2017 6/23/2017 7/3/2017Supplier - Existing Make changes to Existing in FAMIS by end of day 5/19/2017 6/23/2017 7/3/2017Supplier Point of Contact Work with your Suppliers to ensure they register
prior to Go Live https://sfcitypartner.sfgov.org 6/16/2017 N/A N/A
12B/14B Certifications All activity will freeze in Elations 6/1/2017 6/23/2017 7/3/2017Requisitions Processed and transitioned to approved POs
before the PO cutoff 6/23/2017 N/A 7/3/2017
Purchase Orders (approved)
Ensure POs are in an approved status before 5pm 6/28/2017 6/28/2017 7/3/2017
Inventory Balances Finalized by end of day 6/28/2017 6/28/2017 7/3/2017Chartfield/Index codes/Projects
Finalized by end of day 5/8/2017 6/9/2017 7/3/2017
Voucher Approval - Final FAMIS cycle
Ensure the Pay date is not greater than 6/30 and all system approvals are completed before 5pm 6/28/2017 N/A 7/3/2017
Employee Reimbursements
Ensure the Pay date is not greater than 6/30 and all system approvals are completed before 5pm. This includes Travel Advance liquidation
6/28/2017 N/A 7/3/2017
Payment Processing (FAMIS)
Pay run will include all approved vouchers and Employee Reimbursements to be paid thru 6/30 6/28/2017 N/A 7/3/2017
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
34
Blackout DatesTransaction Guidance
FY16-17 System Blackout Starting at
5pm
Conversion File Submittal
Dates
Begin Processing FY 17/18 in F$P
Budget Journals FY 17 from FAMIS Finalized by end of day 6/28/2017 6/28/2017 7/3/2017
Budget Journals FY 18 from the Budget System Finalized by end of day 6/28/2017 6/28/2017 7/3/2017
GL Journals Finalized by end of day 6/28/2017 6/28/2017 7/3/2017Customers Final date for Grant Sponsors 5/31/2017 6/9/2017 7/3/2017
Customers Final date for non-grant customers (i.e. PORT, Airport, MTA, Library) 6/19/2017 6/23/2017 7/3/2017
Open Receivables All grant and non-grant and receivables will need to be finalized by end of day 6/28/2017 6/28/2017 7/3/2017
Deposits Finalized by end of day 6/28/2017 N/A 7/3/2017Work Orders Finalized by end of day 6/28/2017 N/A 7/3/2017
Fixed Assets System will be unavailable but the cutoff date for Assets isn't until August N/A N/A
Late July/early August (pending
conversion)
35
File Submissions & Conversions
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
36
Conversion Expectations
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
37
Conversion File Requirements• To comply with data validation audit requirements• Record and report data extraction time• Take backup/snapshot at the time of data extraction• Inform F$P if not possible• Regular system backups should suffice• Retention 30 days for Dress Rehearsal• Different file submission schedule for different
conversions
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
38
Final Deploy File Submission Due Dates
39
FAMIS to PeopleSoft Conversion:General Ledger & Commitment Control
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
40
Conversion Key PointsFor both General Ledger (GL) and Commitment Control (KK) conversions:o Converting the following data sets from EIS to compute the
Annual and Life to Date Amounts for Actuals and Budgets in PeopleSoft: FAMIS Periods Calculated
FM0 + FM1-13 (Annual) = Life to Date AmountFAMIS Data:
GL GLOperating Operating OperatingBudgets Budgets Budgets
o The budgets here are the “full” budgets, not the remaining balance and doesn’t include pre-encumbrances and encumbrances
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
41
Conversion Key Points• To obtain the Remaining Balance Budget Figures:
o Budget conversion – General Ledger conversion = Remaining Budget Balance
o Once the Purchase order conversion is done, it will do budget checking against the remaining balance.
• The index code crosswalks are used to produce PeopleSoft ChartFields of Fund, Dept, Authority, Project, Activity, Agency Use, TRIO, and Account.o These files are at the summary level for each ChartField
string (every combination of Fund, Dept, Authority, Project, Activity, Agency Use, TRIO, and Account) and not at the transaction detail level.
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
42
Data Validation
Peter Yee
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
43
Data Validation
• AOSD & CSA are working together on data validation project. Compare converted data to FAMIS/ADPICS Limited assurance that data is mapped and converted properly.
• Department to validate their external subsystem data converted to PeopleSoft. Accounts Receivable (Port, MTA) Inventory (DPH) Project Life to Date (MTA) Fixed Assets (non-FAACS)
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
44
Role Mapping
Michael Rottmayer
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
45
Role Mapping• Due by May 19
– Revised roles• Contact [email protected] for your department’s FAMIS
User security report.– Role conflict forms
• For pre go-live, send completed conflict forms to [email protected] and CC [email protected]
• AOSD will review roles and conflicts– Conflict exceptions will be based on need– Especially for small departments
• Sign-off by department once final
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
46
Using the Incident Command Center (ICS)
Todd Rydstrom
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
47
Using ICS at Go LiveGo Live is a Special Event!
Controller’s Office will use our Incident Command Structure (ICS), like with other Big Events.
o Ensures clear communications and timely updates.o Dress Rehearsal ICS already underway.
What’s Next…o Go-Live Cutover is June 28th to July 3rd
o Call Invites will be sent to CFOs, Change Agents and Key Leads.
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
48
Using ICS at Go LiveGo Live is a Special Event!
Report Outs will include:o Cutover Goals and Targets for the Dayo Tasks Updates and Deployment Impacts &
Impediments o Business Impact Updates from AOSD & OCA o Change Management & User Support Updates o Next Steps & the Day Ahead
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
49
Questions
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
50
Break
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
51
City Preparedness for the New Accounting System
Jocelyn Quintos
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
52
The City’s New Financial System• How did the City prepare for PeopleSoft?
– Go Live Date: July 3, 2017• Citywide Account Reconciliations & Data Cleanup• Updated Policies & Procedures• Accounting Task Force• Change Management
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
53
Citywide Reconciliations
Funds and Projects:
• Aligned balances from General Ledger File (GLF) and Operating File (OPF)
• Reviewed inactive and close out abnormal balances at GL account (subsidiary), lowest project and grant detail, sub-fund and document file level.
• Closed out Inactive Projects
• Worked with departments to address nonsufficient funding
To get FAMIS/FAACS files ready for PeopleSoft Interface.
Fixed Assets:
• Identified and confirmed fully depreciated fleet assets that are still “In Use”.
• Reviewed status of fleet assets and adjusted FAACS accordingly.
• Standardized asset non financial data for fleet, building and land.
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
54
Projects & Funds Reconciliations
BEFORECount
Inactive GL 60,733
Abnormal GL 50,279
AFTERCount
Inactive GL 4,842
Abnormal GL 24,314
CLEAN UP
BEFORE AFTERCLEAN UP
Count Out-of-sync 30,272
Document Fi le 1,926
Source: General Ledger and Operating FilesScope: Citywide
Count% of Items
Cleaned UpInactive GL 55,909 92.03%
Abnormal GL 25,965 51.64%
Count% of Items
Cleaned UpOut-of-sync 29,931 98.87%
Document Fi le 1,058 54.93%
Count Out-of-sync 341
Document Fi le 868
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
55
BEFORE AFTERCLEAN UP
Projects & Funds ReconciliationsSource: General Ledger and Operating FilesScope: Citywide
Count Project
Closeout 28,020
NSF 3,731
Subs idiary 5,039
Count% of Items
Cleaned UpProject
Closeout 23,923 85.38%
NSF 1,646 44.12%
Count Project
Closeout 4,097
NSF 2,085
Subs idiary 277 5.50%
Subs idiary 4,762
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
56
Data Cleanup“GARBAGE IN, GARBAGE OUT“
This principal applies to Data Conversion.
The quality of converted data is only as good as the source the data is imported from. Steps taken to clean up FAMIS data prior to conversion:
Standardized data Identified missing or duplicate records Purged unnecessary data Reformatted data if necessary Evaluated data for accuracy Compiled supporting documentation if needed for audit
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
57
Vendor Clean Up
Duplicate Vendor Cleanup
60%
Active Criteria Cleanup
30%
Inactive Cleanup
10%
Active Criteria Cleanup
Expiration Dates for Vendors with Blanket Agreements
Vendors with Balance greater than Zero
Vendors with Active Status and Current Business Tax
Vendors with Unapproved Suppliers
Others (12B, 14B, 1099, Item, HCM)
BEFOREVendor ID Count
1.4 Million
AFTERVendor ID Count
50,309
CLEAN UP
Source: Vendor Table as of June 30, 2016Scope: Citywide
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
58
Disposals Criteria Cleanup
Sold/Turned-in Duplicate Entries Transfers to other
departments Major/Minor
Class Adjustments
CLEAN UP
Fleet Clean Up
Sold/Turned-in92%
Duplicate Entries
4%
Transfers to other
departments2%
Major/Minor Class
Adjustments2%
BEFOREFleet Count: 3,650
Original Cost $184.5MAcc. Depr. ($154.4M)Net Book Value $30.1M
AFTERFleet Count: 2,455
Original Cost $147.1MAcc. Depr. ($117.6M)Net Book Value $29.5M
Source: FAACS Data as of June 30, 2016Scope: 37 Departments
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
59
Building & Land Clean Up
BEFORE
CLEAN UP
Source: FAACS Data as of June 30, 2016Scope: 37 Departments
Correct APN Allocated
Building 1276Land 561
Description Scrubbed
Building 1442Land 568
CountBuilding 1442Land 568
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
60
Accounting Policies, Procedures & Task Force Updated Accounting Policies
& Procedures Worked through Policies with
Departments through Task Force Groups
Met with Departments on Detailed Procedures through Super User Groups
Communicated with Grantors and Community Based Organizations (CBOs)
Participated in Dress Rehearsals
Working with Departments & F$P on the Transition
Subject Sent out Taskforce Final Draft Before Go-Live
Expenses Dec 2016 Completed June 2017
Vendor Mgmt Dec 2016 Completed June 2017
Payables Dec 2016 Completed June 2017
Grants Dec 2016 Completed June 2017
Fixed Assets Dec 2016 Completed June 2017
Debt Jan 2017 Completed June 2017
Work Order Jan 2017 Completed June 2017
Chap 6 Payables Apr 2017 Completed June 2017
Project Costing Jan 2017 Completed June 2017
Cash Mgmt Mar 2017 5/30/17 June 2017
Allocation/ Abatement
Mar 2017 TBD June 2017
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
61
Accounting Policies & Procedures (PnP)
• The Accounting Policies & Procedures have been updated with the input from the various taskforces held.
• Various drafts have been sent to taskforce participants and CFOs for review.
• Final version of the PnPs will be emailed by 2nd week of June 2017.
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
62
Processing Transactions in Two Accounting Systems
Carmen LeFranc
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
63
Assumptions
• FAMIS is the system of record for FY 16-17.• FY 16-17 CAFR is published out of FAMIS.• FY 17 Ending Balances per CAFR (FAMIS)
agree to the FY 18 Opening Balances in PS.
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
64
Converted Data• Budgets will be converted into the PS Commitment
Control Module and allocated:o AAO budgetso Supplementalo Transfers & Adjustments
• All Year View for Continuing Funds & Current Year View for Annual Funds
• Through the grant and project life to date balances, the maximum billing limit amount will be established in the Contract Module.
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
65
Purchase Orders
• After all FY17 payments are done by July 31:o PO in continuing funds will close in FY17
and rollover to FY18. o PO in annual funds will close and not roll
into FY18 unless manually carried forward.
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
66
After the Blackout Date
• FAMIS will remain open for posting until Sept. 8.• FY 17 Expenditure and Revenue Transactions associated
with PS Accounts Payable and Accounts Receivable after cutover will go through “dual processing”.
• All other FY 17 transactions originating in FAMIS after the cutover will be “pushed” to PS using the GL incremental conversion program.
67
FY 17 Year End Key Dates – FAMIS
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
68
FY 16-17 Key Dates• Fiscal Month 12 & 13:
o Fiscal Month 12 Close – July 31 (includes all abatements, adjustments, and allocations)
o Fiscal Month 13 Open - July 3Close - September 8
• Work Orders:o Carryforward request must be supported by the corresponding source
budget by May 31.o Performing departments complete billings by June 9.o Requesting departments complete approvals by June 15. o Controller’s Office process unresolved work orders by June 16.o Departments submit carryforward request of appropriation to
Controller’s Office by September 11.
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
69
FY 16-17 Key Dates• Manual Appropriations Carryforwards:
o Departments submit requests to Fund Accountants by September 11o Mayor’s Office and Controller’s Review, September 11 to 21o Carryforwards are approved by September 22o Approved Carryforward budgets will be processed in Peoplesoft.
• Grants:o May 15: Year End Work shop & Single Audit Entrance Conferenceo May 16: 1090 Grants closeout for inactive grantso July 31: Year End Reconciliation Report dueo July 31: Accrual of unpaid expenditures for goods & services providedo August 11: Accrual of revenue receivableso September 8: 3-Column Grant Reconciliation dueo January 26, 2018: Issuance of Single Audit
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
70
FY 16-17 Key Dates • Last Day to Post FY 17 Financial Statement Transactions
o Capitalized Interest for enterprise fixed assets – August 4o Depreciation – August 20o Debt Related – September 8o GAAP and other CAFR adjustments – September 8
• Separately audited financial statements due October 20
• CAFR submission date – November 20
71
FY 17 Year End Close Details
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
72
Union Related Employee Reimbursements
– DHR must receive all reimbursements requests by Friday, June 2, 2017. Any incomplete, pending or new requests after June 2 will need to be entered and submitted in Peoplesoftafter July 3.
• The new process will be in place
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
73
Employee Reimbursement Workflow
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
74
What is Fiscal Month 13 Used For?• Fiscal Year adjusting entries• GAAP and Balance Sheet Adjustments• Significant Adjusting Entries• Auditor Required Entries• Expenditure Accruals• Revenue Accruals• GASB 45, 65 and 68 Entries Estimates
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
75
Fiscal Month 13 – Accruals & Estimates in FAMIS• Expenditure Accruals - Invoices received but not
processed for Goods or Services received on or before June 30.
• Expenditure Estimates – Develop these when invoices are not available or processed but goods or services have been received as of June 30.
• Revenue Estimates – In general, departments should not be booking increase to revenues based on estimates.
• Please contact your Fund Accountant to discuss this topic before making entries
76
Encumbrances & Carryforwards
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
77
Manual Appropriation Carryforward
• Carryforwards of Annual & Non-Salary Funds only• Must be used for same purpose in the next fiscal year• Carryforward amount must not exceed unexpended
balance, attach FAMIS print screen with request to confirm available balance
• Must submit request to the Controller’s Office Fund Accountants before due date
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
78
PO Encumbrance Carryforward
•Do not close encumbrances with activity in new fiscal year
•Encumbrances with negative balances will automatically close out
•Encumbrances older than one year require strong justification to keep open
•Provide date and amount of accrual if justification is “waiting on invoice”
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
79
Encumbrance Closeout/Carryforward• Departments receive listing of outstanding
encumbrances on 6/2• Request to keep open with justification due to
CON on 6/16• Encumbrance balance for FY17 will be converted to
PS and available for FY17 expenditure on 7/3• Encumbrance carryforwards to FY18 will be posted
by 9/5
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
80
Dual Processing
Min Fang
81
Accounts Payable Voucher Posting
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
82
FAMIS - Voucher PostingREPORT OF OPEN VOUCHER WITH DUE DATES BEYOND 6/30
• Departments review report of open voucher with due dates beyond 6/30:1. Request AOSD to change due date to 6/30 or earlier for FY 17 vouchers,
or2. Departments will reprocess in PS.
• AOSD sends report to departments two weeks before black out date (6/16)
KEY DATES:6/27/2017 Last interface file for voucher payments with due dates <= 6/306/28/2017 Last day to process and post payment voucher in FAMIS6/28/2017 Last check run and ACH run for vouchers due by 6/306/28/2017 Clear errors by 5 pm
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
83
PS - Voucher PostingINVOICE VOUCHERS RECEIVED AFTER JUNE 30, 2017
Dual processing (FAMIS & PS) from 7/3 onward for FY 17 related transactions is required for the following:
o All invoices that are related to a grant funding sourceo All PO related paymentso Non-PO related payments greater than $25,000 for the following types:
o Departments will complete an Excel Template for each invoice voucher requiring dual processing.
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
84
John GeeDepartment AExcellent City
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
85
Year End AP Processing
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
86
Where to Process WhatPayments (vouchers from converted POs, direct payments,
etc.) StaffFAMIS PS
FY17 FY17 FY18
1 FY 17 vouchers post with due date <= 6/30 for last FAMIS Check/ACH run, i.e. 6/28
Dept. X
2 Accounting Date >=‘7/1/2017’ for: i) FY 18 vouchers & ii)FY 17 vouchers: non-grant, non PO & <$25K
Dept. X
3a FY 17 vouchers Accounting Date =’6/30/2017’. Threshold of invoice >= $25K, apply to non-grant and non PO payment only
Dept. X
3b Post reversal entry against Accounts Payable (200002) in GL Module (see step 3d)
F$P/ CON (X)
3c JE to record Accounts Payable GL 202 (AP Accrual Form & Hostbridge Process)
Dept./ CON X
3d FAMIS to PS Incremental GL conversion – Accounts Payable (200002)
F$PX
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
87
Date Received: July 16, 2017
88
Accounts Receivables Receipts Posting
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
89
Year End AR Processing
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
90
Where to Process What?Cash Receipts (Deposits, wires, ACH,
etc.)Staff
FAMIS PSFY17 FY17 FY18
1 Bank transactions up to 6/30 TTX/ Dept. X2 Bank transactions after 6/30 for FY 18 TTX/ Dept. X
3a Bank transactions after 6/30 for FY 17: Apply receipts to Accounts Receivable
TTX/ Dept. X
3b Bank transactions after 6/30 for FY 17: process JE to record Accounts Receivable (AR Accrual Form & Hostbridge process)
Dept./CON X
3c FAMIS to PS Incremental GL conversion –Accounts Receivable
F$P X
Net Accounting Impact of steps 3a – 3c (revenue related)
AR/Rev AR/Rev Cash/ AR
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
91
For further instruction, please attend the Dual Process Training in the afternoon session.
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
92
Year End Reconciliations, Financial Statements & FY 18 Budgets
Peter Yee
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
93
Year End Reconciliation • Follow all year-end closing guidelines, procedures and take
other steps to ensure:– Revenues and expenditures are properly reconciled: budget-
actual and GAAP basis– All entries (assets, liabilities, budget, actual, etc.) are posted – General ledger accounts (at subsidiary level) with abnormal
balance or have no change from last year are analyzed and adjusted as needed
– This year over last year variance is reasonable and accurately reflect current year’s operation results. Check if there are any unusual, probable fraudulent activities or errors?
– Completed project and grants are reconciled and have no outstanding balances in FAMIS
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
94
Fiscal Year 17 CAFR Tasks and Key Dates• May to August – Interim audit begins for selected
departments depending on external audit firm and department schedule
• September 22– Draft Financial Statements for separately audited departments due to the Controller’s Office and Auditors
• October 20 – Final printed Financial Statements for separately audited departments due to the Controller’s Office
• November 20 – Submit CAFR to the Board
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
95
Deferred Inflow for Govt. FundsEntries to record as deferred inflow of resources, if cash for governmental fund revenues not received by August 31.
– No “Auto-Reverse” AR entries in FAMIS
– Deferred inflow T/C 671R (GL224) entries to be posted by 9/8/17 to FM13 in FAMIS
– GL converter to post incremental balance in PS– Deferred inflow analysis file
• Send to departments 9/18/17• Receive confirmation from departments by 9/22/17
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
96
Fiscal Year 2017-18 Budget• Interim Budget will be loaded in PeopleSoft
during the blackout dates.• The interim budget is not available for capital
and equipment expenditures or new positions that were not on the Mayor’s Interim Exceptions list.
• Budget will be fully available after adoption of Budget Ordinance by Board of Supervisors and Mayor.
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
97
GASB UpdateReminder of significant GASB:
• Statement No. 68, Accounting and Financial Reporting forPensions – Is here to stay. Net pension liability, deferred inflowsand outflows, and pension expense will be reflected on financialstatements.
• Statement No. 72, Fair Value Measurement and Application -Provides guidance for applying fair value to certain investmentsand disclosures related to all fair value measurements.
97
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
98
GASB Update
• Statement No. 74, Financial Reporting for Postemployment Benefit – Revises andestablishes new accounting and financial reporting requirements forpostemployment benefit plans other than pensions (OPEB). This applies to theplan and not the participating governments but only impacts our retireehealthcare trust fund.
• Statement No. 77 (GASB) Tax Abatement Disclosures - This Statement requiresdisclosure of tax abatement information about (1) a reporting government’s owntax abatement agreements and (2) those that are entered into by othergovernments and that reduce the reporting government’s tax revenues. We willcontact you if it impacts your department.
98
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
99
Grants Year End Close
Lourdes Nicomedes
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
100
Grants Year End – Key Dates• Monday, May 15 – Year End Work shop & Single Audit Entrance
Conference
• Tuesday, May 16 - 1090 Grants closeout for inactive grants with no asset or liability account balances based on 3rd Quarter reconciliation
• Monday, July 31 – Year End Reconciliation Report due
• July 3 – Sept 8 – Accrual of unpaid expenditures for goods & services provided or revenue receivables
• Friday, September 8 - 3-Column Grant Reconciliation due
• January 26, 2018 – Issuance of Single Audit
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
101
Grant FY17 Year End CloseAfter the Cutover Date:FAMIS remains open for posting in FY 17 for payroll, abatements, adjustments, allocations and interfaces.
o Last day to post expenditure related transactions is Monday, July 31.
o For Q4 FY 17 grant billing, post a revenue accrual in FAMIS and send the corresponding bill to the grantor. Last day to post revenue related transactions in FAMIS is Friday, August 11.
o Grant related FY 17 transactions will be uploaded to or from FAMIS through Hostbridge or the GL Incremental Conversions.
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
102
FY16 Single Audit Findings• Procurement
– The City did not ensure open and full competition, or otherwise obtain proper approval for awarding the contract under limited competition.
• Special Tests and Provisions– The City did not follow the maximum per unit subsidy amount
requirement under a federal program.
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
103
Fixed Assets
Andrea Gremer
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
104
CCSF Fixed Assets Group• FAACS on-line departments:
– Governmental funds: DPW, DPH, Emergency Management, Police
– Enterprise funds: PUC, MTA, Laguna Honda Hospital
• FAACS off-line departments:– The rest of the governmental fund departments.
• Non-FAACS departments:– Airport, Port, SF General Hospital, Internal service
fund Depts., e.g. Central Shop, Reproduction.
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
105
Key Fixed Asset Dates• Project Summary will run weekly every Sunday:
– July 9 to August 6– Confirmation that charges have cleared the Hold
File should be done on the August 6 Run.
• Last Day to clear Hold File:– On-line Departments:
• Governmental funds: August 4, 2017• Enterprise funds: August 4, 2017
– Off-line Departments Return all FAACS documents by July 31, 2017
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
106
Key Fixed Asset Dates
• Last Day to Post in FAACS for On-Line Departments is August 4:o Disposals & Retirements o Capitalized Interesto CIP
• Final FY 17 Depreciation Run is August 20.
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
107
Rollforward Schedule• Beginning Balance: Agree to prior year audited ending balance• Additions: Clear the Hold File & consider additional transactions (Software, Land,
Donated assets)• Disposals: Assets not in use should be disposed
GOVERNMENTAL ACTIVITIES(In Thousands)
Reflected as the Ending Balance of Capital Assets in CAFR 2016
Beginning Balance 7/1/16 Additions Disposals
Ending Balance 6/30/17
Land 334, 261 334, 261
Construction in Progress 456, 093 456, 093
Facilities and Improvements 3,372,183 3,372,183
Equipment & machinery 201,333 201,333
Infrastructure 686,365 686,365
Intangible asset 75,117 75,117
5,125,352 5,125,352
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
108
Best Practices
• IT Equipment• Networks and stand-alone items
• Internally Developed Software• 3 Phases – only one phase is capital
• Land or Donated Assets • Skim Board of Supervisor resolutions
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
109
Best Practices
• Construction in Progress• Demolition or Hazmat costs expensed in the period
incurred• Review June 2017 Schedule of Values • Estimated date of substantial completion
• Date asset is ready for intended use• Vehicles still in Central Shops at 6/30/17 are not ready for
intended use
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
110
Certification of ComplianceGASB 34 – Basic Financial Statements – and Management’s Discussion and Analysis – for State and Local Governments• State & Local governments must begin to recognize capital assets
GASB 42 – Accounting and Financial Reporting for Impairment of Capital Assets and for Insurance Recoveries• Fixed assets that are still depreciating, but no longer being used as expected
GASB 49 – Pollution Remediation Obligations• Once there is an obligation to address existing pollution, it must be accounted for.
GASB 51 – Accounting and Financial Reporting for Intangible AssetsAre we creating or customizing software? (Also consider easements, water or air rights, patents, or trademarks.)
GASB 70 – Accounting and Financial Reporting for Nonexchange Financial Guarantees• Debt guaranteed by construction of capital assets
GASB 72 – Fair Value Measurements• Donated assets should be measured at Acquisition Value (previously fair value) on a prospective basis
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
111
PS Asset Management Module• FAACS Data conversion sneak peak:
• 100% Load rate for Mock 3
• Data that will need to be manually input after go-live:• Location• Asset Custodian• PeopleSoft project for CIP Assets
112
Vendor Setup (Exception)
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
City
and
Cou
nty
of S
an F
ranc
isco
Cont
rolle
r’s O
ffic
e
113
Vendor Exception Setup - Post Blackout Date
• Blackout Date: May 19th
– The only exception is for new vendors that need to be paid on or before June 27th
• Please contact [email protected]• June 12th is the last day to set up these exception
vendors.