City

and

Cou

nty

of S

an F

ranc

isco

Cont

rolle

r’s O

ffic

e

1

FY17 Year End WorkshopPart 1

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 Diane.mai-tran@sfgov.org for your department’s FAMIS

User security report.– Role conflict forms

• For pre go-live, send completed conflict forms to Raymond.Liu@sfgov.org and CC Michael.Rottmayer@sfgov.org

• 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

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

81

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

82

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

83

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

84

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

85

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.

85

City

and

Cou

nty

of S

an F

ranc

isco

Cont

rolle

r’s O

ffic

e

86

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.

86

City

and

Cou

nty

of S

an F

ranc

isco

Cont

rolle

r’s O

ffic

e

87

Grants Year End Close

Lourdes Nicomedes

City

and

Cou

nty

of S

an F

ranc

isco

Cont

rolle

r’s O

ffic

e

88

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

89

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

90

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

91

Fixed Assets

Andrea Gremer

City

and

Cou

nty

of S

an F

ranc

isco

Cont

rolle

r’s O

ffic

e

92

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

93

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

94

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

95

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

96

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

97

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

98

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

99

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

100

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

101

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 vendor.file.support@sfgov.org• June 12th is the last day to set up these exception

vendors.

Purchasing in Fiscal Year 17-18

Presented By: GALEN LEUNGMAY 15, 2017

OCA

ffice of

ontract

dministration

Jaci FongDirector and PurchaserCity and County of San Francisco

City Hall, Room 4301 Dr. Carlton B. Goodlett Pl.San Francisco, CA 94102

Tel: (415) 554.6743Fax: (415) 554.6717Email: oca@sfgov.org

www.sfgov.org/oca

OCA

ffice of

ontract

dministration

Delegated AuthorityProp Q

If your department has been delegated authority under Prop Q, authorized personnel may purchase eligible items.

Items Eligible for Prop Q Purchase Prohibited Items

Purchase up to $10K (including tax and shipping charges) Purchases above $10K Gift cards

Commodities Professional ServicesLease purchase

transactions

General servicesIT Equipment &

Services Vehicles

Construction Heavy Equipment

OCA

ffice of

ontract

dministration

Delegated AuthorityProp Q (continued)

Departments should secure 3 quotes for purchases greater than $5,000 and document these efforts.

No order splitting Keep in mind whenever possible:

o The City’s policy is to bid.o We support local businesses

OCA

ffice of

ontract

dministration

Delegated Authority

Professional Services Contract Thresholds

Formal Sourcing Event (RFP or RFQ) – Must be advertised if the amount

involved is $110,000 and over

OCA

ffice of

ontract

dministration

DeadlinesFor FAMIS Transactions

Vehicle and equipment requisitions due to OCA March 17

Requisitions for non-vehicle, non-equipment April 14

Requisitions for items on City contracts May 19

Changes to Departmental Blankets and Contract Purchase Orders May 19

Office supplies online (initiate a PO release) and Prop Q purchases June 9

Last date to post and complete transactions in FAMIS June 27*

*Note: Open requisitions in FAMIS will not be automatically converted to PeopleSoft and will need to be re-entered into PeopleSoft.

OCA

ffice of

ontract

dministration

Department OMP Blanket Purchase Orders

Memo issued April 18, 2017 from Jaci Fong, Purchaser and Director of OCA

From April 24, 2017 to June 27, 2017Departmental blankets may be changed to end August 31, 2017, and increased up to $60,000 (based on the spending limit of $1,000/day) and not to exceed the current annual value of the blanket (for example, a $10,000 blanket should only increase to $20,000 - not $70,000)

Any blanket with an end date prior to July 3 will not be converted to PeopleSoft.

Starting July 3, 2017, in PeopleSoft, Departments can renew, extend, or make changes to contracts. OCA recommends adding NIGP codes into the “Category” field to facilitate payment processing in the new system.

Remember to create new, change or renew any OMP contracts that were converted from FAMIS by August 31, 2017 for OCA review & approval.

City

and

Cou

nty

of S

an F

ranc

isco

Cont

rolle

r’s O

ffic

e

108

Purchasing Authority

Ordering Document & Encumbrance

Before in FAMIS

BeginningJuly 3rd in PeopleSoft

Blanket

PO Release

Purchase Order

Contract

OCA

ffice of

ontract

dministration

Contracts(formerly Blanket Purchase Orders)

Policies stay the same; the mechanism is different Contract Blanket Purchase Orders are going to be Contracts

o Used for signed formal contracts such as Professional Services, Equipment Maintenance, Lease Agreements and Software-related purchases.

o PeopleSoft will record the formal agreement as a contract. Contracts must be amended if there are changes in amount ($), term or scope of work.

o No daily spending limit.o Contract Blanket Purchase orders with remaining balances and a term end

date of July 1st or later will be converted into PeopleSoft.o All required documents such as evidence of BOS or Civil Service approval

should be attached to the Contract in PeopleSoft after July 3. Departmental Blanket Purchase Orders will also be Contracts

OCA

ffice of

ontract

dministration

Contracts (continued)

What used to be Departmental Blankets (DBs) are going to be Contractso Small dollar, frequent and repetitive purchases of unknown quantities from

a specific vendor for a specified time period. o OCA recommends including Category (NIGP codes) o Appropriate for anticipated purchases over $10,000 annually.o Include the following in the Add Comments section of the Header:

Reasons why the purchase cannot be bid CMD database was checked for possible LBE vendors Reasons why CMD approved LBE was not used (if applicable)

o Be sure to include information elsewhere in the Contract: Pricing, payment (prompt payment discounts) and delivery terms; Beginning and expiration dates; Description of product or service; Supplier name and ID number.

OCA

ffice of

ontract

dministration

Purchase Orders Only purchase orders with open line items will be converted from FAMIS to

PeopleSoft. “Purchase Orders” will replace DP Releases in PeopleSoft starting July 3.

o Purchase Orders must be issued against contracts to encumber funds before orders are placed with vendors (Charter Section 3.105).

o Use an NIGP Code in the Category field in the Purchase Order.o HINT: The “Amount Only” setting should be checked in the Purchase Order

if it was not checked in the Contract. This allows for progressive payments and will avoid match override when making payments.

OCA

ffice of

ontract

dministration

OCA

ffice of

ontract

dministration

Processing of Payments without an Encumbrance (formerly known as Direct Vouchers)

Payments without an encumbrance violate City Charter Section 3.105(i) All disbursements of funds must be authorized by the Controller Justification for such payments must answer 4 questions:

1. Why were funds not encumbered? 2. Who authorized it? 3. Why is the price reasonable? 4. What procedures are being implemented to avoid recurrence?

All transactions must be with compliant vendors All such payments are subject to post-audits

OCA

ffice of

ontract

dministration

Citywide Term Contracts

Commodities and general services (office supplies, fuel, Technology purchases and CopySmart program)

Departments must order term contract items only from the term contract vendor

Departments can create Purchase Orders against the Contract An updated list of term contracts is posted on OCA’s website at

www.sfgov.org/oca on the Vendor Information page If the Contract was awarded as a percentage off a catalog price, the

quote and invoice should both include the following information: 1. the catalog price, 2. the percent off and 3. the resulting price.

OCA

ffice of

ontract

dministration

Paying Invoices

3-way match: purchase order, packing slip and invoice. Descriptions, quantities and prices should match. Invoice must be approved by someone other than the person who placed the

order and other than the person who received the order. Approved invoices must be sent to Accounting for payment. Accounting reviews invoices and, if approved, creates Voucher for payment. Having the Category code from the PO will aid in creating the Voucher.

OCA

ffice of

ontract

dministration

Technology Marketplace and IT Purchases

To utilize the Technology Marketplace contracts in FY16-17 submit requisitions and receive CIO approval in FAMIS (Level 300) by June 2, 2017.

Include the following information with the requisition:o Required CIO review: email approved quote form to cio.review@sfgov.orgo Purchasing type “RT”o Input information as it appears on the quote (quantity, part numbers, and

detailed descriptions for each line item – not all on 1 line with 1 total figure)o After last line item, enter the quote #, expiration date, vendor contact name

and phone, admin fee, TC#, requestor’s name and phone number For professional IT services, notify Local 21 via this website:

http://ifpte21.net/content/information-request-personal-services

OCA

ffice of

ontract

dministration

Vehicle and Heavy Equipment Purchases

Vehicles and heavy equipment are processed by the centralized fleet buyers Send via email – subject line: (req. #, equip. budget #, and type of vehicle or

heavy equip.) in addition to the following items to: fleetteam.oca@sfgov.orgo Screen print of the FAMIS-Purchasing requisition or the PeopleSoft requesto Separate line items for tire tax and extended warrantieso Approved equipment budget number, substitution approvals, carry forward

approvals, etc.o Fleet approval from Tom Fung, Director of Fleet Management on the VARo Term Contract Order Sheet or technical specifications approved by Fleet

Managemento Current Fleet Term Contracts are available at:

http://admweb/adminservices/fleetmgmt/default.htm

OCA

ffice of

ontract

dministration

IT and Vehicle/Heavy Equipment Contacts

Technology Marketplace and IT:Rob Henning, Assistant Director (415) 554-6212Shawn Peeters, Senior Purchaser (415) 554-7030Paul Cheng, Purchaser (415) 554-6732Diane Handa, Purchaser (415) 554-6913

Vehicles/Heavy Equipment:Lin Repola, Supervising Purchaser (415) 554-4564Mark Farley, Senior Purchaser (415) 554-6257Deborah Wells, Purchaser (415) 554-4564

OCA

ffice of

ontract

dministration

OCA Sole Source Waiver Form

If there is only one source for the commodity or service Must submit the Sole Source Waiver Form [Form P-21.5(b)] to OCA before you

finalize and submit for approval a requisition or blanket Follow the form’s instructions and answer all questions in detail Written justification should be in the form of a memo that answers all the

questions on the form Include transaction number (ADPICS or PeopleSoft) when possible If the transaction is for services, include CMD’s 14B Waiver Include the approved waiver when submitting the transaction Plan ahead – don’t assume that the waiver request will be approved

OCA

ffice of

ontract

dministration

Vendor Compliance Reminders Business Tax Status (FAMIS-Purchasing screen 9560) Equal Benefits Ordinance (FAMIS-Purchasing screen 9560) In PeopleSoft, Suppliers will be able to update Business Tax and Equal Benefits

information, and submit other items, like insurance Supplier (formerly vendor) must be in the City file and have submitted a W9 Minimum Compensation Ordinance (MCO) – services only Health Care Accountability Ordinance (HCAO) – services only LBE Compliance Affidavit (CMD Form 3) – service only States called out for allowing discrimination – see bottom of OCA’s webpage,

Qualify to Do Business, for link to latest list

Tax Collector Representative: (415) 554-6718 CMD: www.sfgov.org/cmd (415) 581-2310 OLSE: www.sfgov.org/olse (415) 554-7903

City

and

Cou

nty

of S

an F

ranc

isco

Cont

rolle

r’s O

ffic

e

120

Questions

Thank you for attending!

Agenda for Afternoon:Dual Processing in Action

Grants YE Close DetailsGrants Getting Started in Peoplesoft

Single Audit Entrance ConferenceUniform Guidance Refresher