year end operational closing procedures fy2018...grant task 6 –identify and resolve errors in...

Grants Operational Closing ProceduresState Fiscal Year 2018

June 2018 1

Agenda

Welcome Revenue Closing Timeline Grant Tasks SEFA/SIS Reporting Speedchart/Task Profile Reminders & Notes Question and Answers

June 2018 2

Grants/Projects Revenue Recognition Closing Timeline FY2018

3June 2018

Grant Task 1 – Analyze and take action to close and/or complete Open Grant Items in Accounts

Receivable

Run query TN_AR18C_ALL_OPEN_ ITEMS_FD_SC

Invoice accounting dates greater than 30 days need to be analyze and resolved.

Run Query TN_GR05_BLD_TRANS_BY_INVOICE for each of the items to research source transactions.

4June 2018

Grant Task 2 - Analyze and take action to closeany Open On Account Items in AR

Run Query TN_AR18B_OPEN_OA_ITEMS and analyze all Open “On Account Items”

DOA does not recommend deposits for grants be put on the Customer’s account.

All open OA deposit items should be closed within fourteen (14) days from the accounting date of the OA deposit item.

Create maintenance worksheet with guidelines in the AR manual; change “Accounting Date to 6/30/18 only can be done until 7/12/18.”

5June 2018

Grant Task 3 – Reconcile AR Account 12000001 Trial Balance to Open Items - Overview

Step 1: Run query TN_GL048_TRIAL_BALANCE_ACCT for Account 12000001 for a month and/or quarter endStep 2: Identify all billing invoice items run query TN_AR18C_ALL_OPEN_ITEMS_FD_SC and create a Pivot table to view by Source and Fund Step 3: Compare amounts from Trial Balance to Open ItemsStep 4: Compare to Accounts Receivable Aging by Chartfield Report (AR30006) – this can be run to a CSV report to ExcelStep 5 : Compare to agency internal records

6June 2018

Grant Task 3 – Reconcile AR Account 12000001 Trial Balance to Open Items –

At any point in TimeCombining the results of TN_GL048_TRIAL_BALANCE, TN_AR18C_ALL_OPEN_ITEMS_FD_SC and TN_AR21_Account_Analysis

TN_AR21_ACCOUNT_ANALYSIS –gives one the ability to see all new invoice items created and invoice items which have been closed; allowing one to “back into” balances at any point in time. TN_AR21_ACCOUNT_ANALYSIS has to be run by a single period. (Periods would be combined if you were doing this past one month.) Remember to reverse the total of TN_AR21.

7June 2018

Grant Task 3 – Reconcile AR Account 12000001 Trial Balance to Open Items –

At any point in Time

Note - The reconciliation should involve checking to make sure all open invoice items reconcile to general ledger as well as ensuring the general ledger balance is reconciled with open invoice items in the AR module and any differences should be investigated for propriety.

8June 2018

Grant Task 4 - Identify and complete any Customer (Revenue) Contracts/Grants in a Pending Status or any

Customer (Revenue) Contract Amendments in a Pending Status

Run TN_GR22_CONTRACTS_PENDING Transactions on projects in pending contracts do not

bill or recognize revenue.

Run TN_GR40_PENDING_AMENDMENTS Transactions awaiting the completion of a Customer

(revenue) Contract do no bill or recognize revenue.

9June 2018

Grant Task 4 - Actions Needed to complete a Pending Contract

Finish Customer Contract Information & make it Active To the Award Profile -Add Attributes; Attach

documentation (once uploaded make sure you can download the file – naming too long or complicated naming causing one to be unable to download)

Run TN_PR101_PROJECT_COST_DETAIL to see if transactions have already occurred and need repricing

Check Commitment Control for Project activity

10June 2018

Grant Task 4 - Actions Needed to complete a Pending Contract Amendment

Finish Customer Contract Amendment Run TN_PR101_PROJECT_COST_DETAIL to

see if transactions have already occurred and need repricing

File Edison Remedy ticket if they need to be re-priced (BU, Customer Contract, Project ID, Accounting Date range); if no transactions exist, no ticket is required

11June 2018

Grant Task 4 - Important Notes Domino effect of wrong “Rate Set” If flow-through from another state agency “68090” or if

received from a non-governmental sponsor “68060” Could have recognized 68001 Federal Revenue even if you have

not billed Run TN_GR32_CONTRACTS_REVENUE to see revenue

recognized If agency corrects using GL JV’s instead of creating new

grant/project – remember domino effect that a billing (BI) journal may be left in the system

Do NOT change rate set dates once Contract created! 7XXX expenditures do not bill on STAT rate sets

12June 2018

Grant Task 5 - Identify and resolve Over-the-Limit (OLT) transactions

Run TN_GR19_OLT_CHECK query Options to select:

1) Contract billing limit should be increased.2) Expenditures need to be moved to different grant.3) Expenditures need to be changed to a STATE activity.4) Expenditures need to be moved to state with no

project ID.

13June 2018

Grant Task 5 – Options when addressing OLT transactions - Determine if...

Analyze why these transactions occurred by project ID; other transactions may have been charged in error and what is in OLT may be valid for the project/grant.

Not correcting OLT transactions WILL cause a difference in expenditures and revenue for specific period(s) and for state years as well as differences in SEFA reporting.

Only can increase to the amount awarded!

14June 2018

Grant Task 6 – Identify and resolve errors in Revenue Recognition

Overview of Different Steps:Step 1: Identify Customer Contracts which have

revenue recognized to wrong activity and take corrective action.

Step 2: Resolve other errors in revenue recognitionStep 3: Recognize revenue and expenditures in the

same year to avoid improper SEFA reporting.

15June 2018

Grant Task 6 – Identify and resolve errors in Revenue Recognition Step 1: Identify Customer Contracts which have revenue

recognized to wrong activity and take corrective action.

Run TN_GR24A_REV_WRONG_ACTIVITY

Analyze results and makecorrectionsas needed !

16June 2018

Grant Task 6 – Identify and resolve errors in Revenue Recognition Step 1: Identify Customer Contracts which have revenue recognized

to wrong activity and take corrective action.

Revenue recognized to wrong activity could be avoided: By monitoring TN_GR27_RATE_SET_ERRORS on a

weekly basis or after activation of new grants into Edison.

By monitoring the “TN_GR03” query daily for the “STATE” activity appearing and billing – it should not appear unless it is correcting a previously billed error.

By monitoring the “TN_GL64_REVENUE” or Trial Balance query looking for 68001000 on Projects with “STATE” activity.

17June 2018

Grant Task 6 – Resolve other errors in Revenue Recognition - Identify and resolve Revenue not

recorded to a CN Journal - Step 2

Run query TN_GR_A07_REVENUE_NOT_CREATED

18June 2018

From the query create a pivot table and analyze…

Unrecognized revenue cannot be resolved if a Customer Contract has had it’s Contract Processing Status marked

Cancelled or Superceded! Closed Contracts cannot be undone!

Grant Task 6 – Identify and anticipate year-end effect of revenue recognized in a different year

than expenditures - Step 3Run query TN_GR06X_REV_EXP_IN_DIFF_YEAR

SEFA pulls fromrevenue recognizedfrom the expendituresnot from theexpendituresplus the accruals (YAE)

Revenue for adjusting Periods991, 992, etc. is in Period 12

19

RevenueExpenditures

June 2018

Grant Task 7 – Identify and Resolve Indirect Cost (F&A) Errors

Run TN_GR28_SFA_COM_CNTRL_ERRS query

Run TN_GR29_SFA_INTERACTIVE query

Edison helps to notify you of these errors weekly, but it is up to the agency to resolve the errors.If not corrected, no indirect cost for any Project ID will process for that particular agency.

20June 2018

Grant Task 8 - Review and analyze projects not associated with a Customer Contract.

Run TN_GR22A_PROJ_NOT_ON_CONTRACT and analyze…

If reportingproject, it is validto pull on query.

If costs need to be re-allocated via GL JE, run query TN_PR101_PROJECT_COST_DETAIL to get details.

If Edison does reallocations – the Agency/business unit needs to review to ensure all values were reallocated & clear errors.

21June 2018

Grant Task 9 - Ongoing - Determine and resolve any Abnormal situations which exist or if

“analysis types” have been incorrectly used. Run query TN_GR23_ANL_TYPE_ABNORMAL The Project “Activity” along with “Analysis type” should identify

funding source. 7XXXXX do not belong with GLR 6XXXXX do not normally belong with GLE

Exception with Refund of PY Federal Expenditure paid back via AP 68012000 with Project ID and Federal Activity receives an ACT analysis type

STATE Activity does not normally have GLE FEDERAL Activity does not normally have CGE Federal Rate Set 68001 should not have a Project ID with an INTERFED,

INTERMATCH or NONGOV Activity22June 2018

Grant Task 9 – Ongoing - Determine and resolve any abnormal situations which exist or if “analysis

types” have been incorrectly used

Analysis types to use only in GL/EX Journals

GLE – General Ledger Expense CGE – Cost Share General Ledger Expense SJE – Statistical Journal Entry SFA – Indirect Cost Expenditure OFA – Contra Indirect Cost Expenditure GLR – General Ledger Revenue YAE – Year-End Accrued Expenditures YAR – Year-End Accrued Revenue

23June 2018



Analysis types to use only in AG Journals

GLE – General Ledger Expense CGE – Cost Share General Ledger Expense GLR – General Ledger Revenue

24June 2018



Analysis types to use only in IU Journals

GLE – General Ledger Expense CGE – Cost Share General Ledger Expense IUR – Interdepartmental Revenue

25June 2018



PROGRAMINC can automatically reduce the draw when recorded on a JE or a deposit with Project ID, Federal Activity and analysis type “GLR” if agency is configured.

PROGRAMINC returning as invalid value, but OK if agency is recording program income.

26June 2018

Grant Task 10 - 6/22 – 8/9 - Identify Project-related journals which have not posted

Identify Project-related Journals that have not posted This should be done each Friday through July 27th ! Done daily on these dates from Monday, July 30th to

Friday, August 3rd. Daily through August 9th - last day for DOA to approve JV’s As various periods close – watch for journals not approved in

the period that is closed No reallocation JV/AG/EX journals for grants/projects can be

entered for state FY 2018 after August 3rd !!!!

27June 2018

Grant Task 10 Identify Project-related journals which have not posted

Run TN_GR20A_JRNLS_NOT_POST_GR and analyze: Checking budget status Checking processing status Remember to include all open period dates

Approve journals not approved by agency Evaluate if any have budget errors and resolve errors Budget errors on “ALLOT” have to be resolved through F&A Budget If interagency IU journals, email or call corresponding agency Follow up on journals to determine why they are not posted If Division of Accounts General Ledger group has denied – they can

be re-worked & re-submitted (short time frame!)

28June 2018

Grant Task 11 Deadlines for requesting changeson Speedcharts/Task Profiles/Chartfields

For new speedcharts for FY19 that must be completed by July 1st.

Requests to Edison for iNovah allocations for FY19. Requests to Edison to change existing allocations to a

different speedchart. Requests for new FY19 chartfields to be established by

July 1st.

29June 2018

Grant Task 12 – 6/22 & ongoing - Evaluate transactions that have not yet billed

Run TN_GR20_UNBILLED_TRANS_DETAILS query vs TN_GR20_ELIG_UNBILLED_TRANS_DT Contract Status and Processing Status need to be Active Projects have to be “O” Opened to have transactions to bill Activities have to be “Active” to have transactions to bill OLT does not bill nor recognize revenue Just because you delete the temp bill, the transactions

don’t go away Transactions on TN_GR03 with FEDERAL Activity have

had revenue recognized & will appear on SEFA30June 2018


Then use Query TN_GR20 to analyze with Pivot Table –Billing Status in theColumn

31June 2018


Then use Query TN_GR20 to analyzeWith Pivot Table-Revenue Recognition StatusRevenue maybe recognizedon future datedjournals;Revenue is ignoredon interdepartmental

32

C = Revenue processing has not yet occurred

Distributed = Revenue has

been recognized & posted to GL

Generated = Revenue accounting lines have been created but not

posted to GL

Ignored = Revenue will not be recognized

S = Revenue is in process None = Revenue has not been recognized

June 2018


Run TN_GR_A09_EXP_NOT_PRICED query

Notify: Division of Accounts Grant Accounting Manager – Mary Lou Goins and Edison FSCM Projects/Grants team Lisa Carter, Kyle Petit, and Samantha A. Cox with Remedy Ticket

33June 2018

Task 13 – 6/23 Reconcile pass-throughGrants BLD & Interdepartmental Revenue

Run TN_GR04_INTERDEPT_AR query periods 1-11 & analyze Does BLD and IUR equal? If yes, the billed amounts of the invoices equal the IU revenue

& Supplemental Schedule will be correct! Does BLD and IUR not equal? If yes, validate all bills have been approved & processed

through Single Action. Create IU journals (have to value $1,000.00 or greater) If invoice amount different from IU, does billing need to be

adjusted with a GL Journal to move transactions?

34June 2018

Task 14 Last Day to enter Project Related Travel Expenditures – 6/29

Last day for all travel expenditures with accounting dates of June related to Grants/Projects to be submitted.

Can I enter travel in July and date it with a June accounting date?....only if agency is reallocating on a journal entry travel that has already been entered and processed in FY18.

35June 2018

Grant Task 15 New state FY year begins

Date Reminder on TN_GR03July 1st

Two sets of billing and revenue recognition begins State Fiscal Year 2019 begins

Two temp bills for each Contract/Grant if transactions exist in both state years

JV’s in an Adjustment Period (991-992-993-994) are always dated 6/30/2018!

36June 2018

Grant Task 15 New state FY year begins

Remember to BILL the PREPAIDS (zero dollar invoice billings)

that are June transactional accounting dateswith JUNE Billing date separately!

Can be done until the AR and Billing modules modules close on July 12th but recommend doing

before the last date !

37June 2018

Grant Task 16 - 7/12 Prepaids

Last day for the Creation and Processing of (Immediate Billing) Prepaids associated to Customer Contracts/Grants for deposits received in June or before!

Before the AR module closes 7/12, if you have a prepaid/advance at June 30th that you have collected and is associated to a Grant, it needs to be entered, billed, and deposit recorded in June to close the open immediate prepaid billing Invoice Item.

38June 2018

Grant Task 17 - Submodules Closed

Thursday, July 12All Edison submodules closed for FY2018

except for…….. Customer Contract – Revenue recognition

Remember revenue recognition for transactions in adjusting periods is recognized in Period 12 not in adjusting periods (991-992-993-994)

F&A (Indirect Cost) automated processing

39June 2018

Grant Task 18July 27

Repeat Task 10

Evaluate and take action to resolve project/grant related journals in the system which have not posted for FY18.

Review for budget errors and missing approvals.

40June 2018

Grant Task 19 – Friday - July 27Grant Related Accrued Liability entries due

to be entered (7/27) and approved by 7/30 Accrued Liability entries

1. YAE is the analysis type on expenditure line only.2. Journal Source – LA type journals3. May need to increase grant project budget in order to enter the accruals.4. Auto-reversing journals.5. Associated revenue to Accrued Liabilities must be entered on an RA journal.6. Division of Accounts gives detailed directions on LA’s on Job Aids web site.

41June 2018

Grant Task 20 IU Journal Dates: July 30 – August 2

7/30 - Last day of entry of IU journals by creating agency8/1 – Last day of entry by secondary agency8/2 – Approvals by both creating & secondary agencies

This is a very short window if waiting until the last minute to enter the IU journals. The agency creating the IU needs to call and notify via email the secondary agency that an

IU exists for them to complete!Helpful to begin IU journals with “Billing 32701” and

include name/phone # of creator.42June 2018

Grant Task 21 – August 3Grant Related Accounts Receivable entries

by agency due

YAR is the analysis type on Revenue line only with Project ID

Journal source – RA type journals. Auto-reversing journals; be sure to validate auto-reversing

journals post and do not fail in budget checking. Division of Accounts gives detailed directions on RA’s on

Job Aids website Approvals by agency on these journals due by 08/04/2017

43June 2018

Grant Task 22 August 3

Last day for entry of miscellaneous JV/AG/EX journals to correct a previous FY18 transactionaffecting Projects/Grants!

44June 2018

Grant Task 23 - August 3Repeat Task #13 but go through June 30th

Reconcile grants/project related interunit (IU) revenue with billings with an accounting date through June 30, 2018. May have to run query

TN_GR20A_JRNLS_NOT_POST_GR to make sure no IU journals are in various processing stages

IU journals have to be $1,000.00 or greater to be entered and process

45June 2018

Grant Task 24 – August 4Perform Deferred/Advanced Revenue analysis

Run TN_GL048_TRIAL_BALANCE_ACCTfor Period “0” to get beginning balances Use 35% (wildcard) to only get the deferred accounts

Analyze trial balance to get amounts by grant deferred revenue accounts for period “0”

Run TN_GL66_LIABILITY_ENTRIES_PD for each period and combine into a spreadsheet

46June 2018

Grant Task 24 – August 4Perform Deferred/Advanced Revenue analysisCreate a pivot table to show the activity by period.By adding Period“0” balance from the Trial Balance

the current balance in Deferred revenue will be shown…

47June 2018

Grant Task 25 - August 4Analyze grant related contra accounts for proper

state year-end balancing. Clear errors on F&A (Indirect Cost) processing. Run query on Indirect Cost Expenditure & Contra

TN_GR_A13_89040000_VS_89035000 Run query on Indirect Cost

TN_GR_A13_IDC_NOT_IN_BALANCE

Run query on In-Kind Expenditure & Contra TN_GR_A13_89300000_VS_893010000

48June 2018

Grant Task 26August 6

Last day of agency/business unitAPPROVALS

of miscellaneous JV/AG/EX journals to correct a

previous FY18 transactionaffecting Projects/Grants.

49June 2018

Grant Task 27 Wednesday - August 8

Last day of APPROVALS by Division of Accounts

of miscellaneous JV/EX journals to correct a previous FY18 transactionaffecting Projects/Grants

and IU journals.

50June 2018

Grant Task 28 – August 9Interest on Deferred Revenue accounts should be posted and Conversion (type) prepaids added to

Customer Contract(s) and processed.

Refer to Grant Manual for prepaid creation. Don’t forget about Prepaids created for interest

earned on deferred revenue accounts. Conversion close of interest related prepaids has

to be done by Edison financials.

51June 2018

Grant Task 29 – August 9Contracts Module Closed

52

Customer (Revenue) contract module closed for FY 18 (revenue recognition for FY18 will NOT occur after this date)!

June 2018

Grant Task 30 – August 9F&A (Indirect Cost) Closed for FY18

F&A (Indirect Cost) Closed for FY 18 automated processing for FY18 closed (stops processing for FY18)!

53June 2018

Grant Task 31 – August 10Billing for FY2018 Stops

Last day for temporary bills on TN_GR03 with separate 2018 State Year accounting dates to occur!

54June 2018

Grant Task 32 – August 13Evaluate 2018 Journals

Run query TN_GR20A_JRNLS_NOT_POST_GR

Evaluate miscellaneous JV/EX/AG journals which were NOT approved for FY18 transactions affecting projects/grants and delete the journals not approved.

55June 2018

Grant Task 33 – When all zero dollar invoices are processed, create a “no project” journal to adjust for Utilization (reduction of Deferred/Advanced of zero

dollar billings for FY18 with billed dates in FY19

56June 2018

Run query TN_GR_A06_UTL_IN_NEXT_FY Create and process Journal to correct the Unbilled

AR and the Deferred/Advanced account balances

Grant Task 34 – Submit Schedule of Expenditures of Federal Awards (SEFA) and Supplementary

Information Schedule (SIS) - September 17

57June 2018

For SEFA - Run query TN_GR06_SEFA_EXP



58June 2018

For SEFA – To assist with Subrecipients - Run query TN_GR06_SEFA_DETAILS – partial screen shots



59June 2018

For SEFA – To assist with Subrecipients - Run query TN_GR06_SEFA_DETAILS – partial screen shots cont.


Information Schedule (SIS)For Supplemental (SIS) Run query

TN_GR06S_SEFA_SUPPL

60June 2018

Grant Task 34– Submit Schedule of Expenditures of Federal Awards (SEFA) and Supplementary

Information Schedule (SIS) Reconcile the SEFA and SIS to the GL and Federal Reports SEFA and SIS Reconciling Tips Program Income transactions which automatically reduce the

Federal draws do NOT have associated direct expenditures – run query TN_GR24A_REV_WRONG_ACTIVITY to easily identify the reduction of revenue by the income from Program Income.

TN_GR06X_REV_EXP_IN_DIFF_YEAR from Task 6 can help to identify expenditures and revenue recognized in different state years.

TN_GL048_TRIAL_BALANCE – gives all chartfield values

61June 2018


Information Schedule (SIS)Example of part of a reconciliation between Expenditures and Federal Revenue – most of the differences are due to Refund of PY Federal Expenditures affecting 68001000 revenue; however Project ED0000CB13AAX13 is still out of balance….

62June 2018


Information Schedule (SIS)Note the difference between total Federal revenue and expenditures for the project is 68080005 Program Income Revenue which, when recorded, reduces Federal Revenue on project ED0000CB13AAX13. However the recording of Program Income 68080005 does not reduce total expenditures.

63June 2018


Information Schedule (SIS)It is important when documenting the reconciliations for the SEFA and SIS reports that there is sufficient information to provide a trail that anyone can follow for the reconciling differences…..

64June 2018


Information Schedule (SIS)Special note for FY18: If your agency had transactions

related to CFDA # 84.367; according to the 2018 Compliance Supplement, the Program Title changed from “Improving Teacher Quality State Grants” to “Supporting Effective Instruction State Grants.” Both the SEFA and SIS report duplicates the values returning the same values with the old Program Title and the new Program Title. The current work around is to ignore the transactions returned with the old Program Title and use the transactions returned for the new Program Title.

65June 2018

Speedchart Reminder ! Last date for requests for new FY19 speedcharts for

FY19 for July 1st is June 22nd! Submit 100% speedcharts updates separately than splits If “replicated to Cashiering must have an account and allow

extra time for reviewing by Division of Accounts If in-activating a speedchart – make sure the speedchart

/task profile is not a default for an employee – run HCM query TN_TL142A_EMPL_ACCTG_DEF

Take care not to make a speedchart/task profile with an effective date prior to the begin date of the Project ID

Can’t submit until Project ID/Grant is in system Submit to Jennifer Lewis & Beverly Bean

66June 2018

Speedchart Reminder !

Query TN_GL60PE_SPDCHRTS_PRJ_ERRORSis a query that an agency can use on a regular basis (at least monthly) to identify SpeedCharts/Task Profiles that contain a Project ID that may no longer be valid due to the following situations:

The Project Budget end date in Commitment Control is in the past

The Project ID is used in a Task Profile but does not have a budget for salaries and benefits

67June 2018

NOTE Remember the Domino Effect…. As you do various steps, you WILL have to

recheck queries…..

Questions & Answers ?????

68June 2018

year end operational closing procedures fy2018...grant task 6 –identify and resolve errors in...

Documents