year end operational closing procedures

Grants Operational Closing Procedures FY 2017

June 2017 1

Agenda

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

June 2017 2

Grants/Projects Revenue Recognition Closing Timeline FY2017

3 June 2017

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.

4 June 2017

Grant Task 2 - Analyze and take action to close any 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/17 only can be done until 7/12/17.”

5 June 2017

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 end Step 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 Items Step 4: Compare to Accounts Receivable Aging by Chartfield Report (AR30006) Step 5 : Compare to agency internal records

6 June 2017

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

At any point in Time Combining the results of TN_GL048_TRIAL_BALANCE, TN_AR18C_ALL_OPEN_ITEMS_FD_SC and TN_AR21_Account_Analysis Query 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 month end period. Query TN_AR21_ACCOUNT_ANALYSIS has to be run by a single period. (Periods would be combined if you were doing this past one month.)

7 June 2017

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. The AR30006 report will reflect all open invoice items and should be used to complete the reconciliation. The steps to run this report can be found in the Edison Accounts Receivable Foundations manual found in the FSCM Training Catalog.

8 June 2017

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.

9 June 2017

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

10 June 2017

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

Finish Customer Contract Amendment according to updated 9.2 Grant Manual directions

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)

11 June 2017

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

12 June 2017

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.

13 June 2017

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.

Re-pricing can occur with the 9.2 upgrade for specific accounting date range

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!

14 June 2017

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 recognition Step 3: Recognize revenue and expenditures in the

same year to avoid improper SEFA reporting.

15 June 2017

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 make corrections as needed !

16 June 2017

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.

17 June 2017

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

18 June 2017

From the query create a pivot table and analyze…

Unrecognized revenue cannot be resolve 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 3 Run query TN_GR06X_REV_EXP_IN_DIFF_YEAR

19

Revenue Expenditure

June 2017

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.

20 June 2017

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

Run TN_GR22A_PROJ_NOT_ON_CONTRACT and analyze…

If reporting project, it is valid to 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 – needs Agency/business unit

review to ensure values were reallocated.

21 June 2017

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 does not have Project ID and INTEFED or

NONGOV Activity 22 June 2017

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

23 June 2017



Analysis types to use only in AG Journals GLE – General Ledger Expense CGE – Cost Share General Ledger Expense GLR – General Ledger Revenue

24 June 2017



Analysis types to use only in IU Journals GLE – General Ledger Expense CGE – Cost Share General Ledger Expense IUR – Interdepartmental Revenue

25 June 2017



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 manually recording program income.

26 June 2017

Grant Task 10 - 6/24 – 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 28th ! Done daily on these dates from Monday, July 31st to Friday August 4th. Daily through August 9th - last day for DOA to approve JV’s

As various periods close – watch for journals not approved

No reallocation JV/AG/EX journals for grants/projects can be entered for state FY 2017 after August 4th !!!!

27 June 2017

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!)

28 June 2017

Grant Task 11 – 6/19 & 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 SEFA

29 June 2017


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

30 June 2017


Then use Query TN_GR20 to analyze with Pivot Table – Revenue Recognition Status

31

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 2017

Grant Task 11 – 6/19 & ongoing - Evaluate transactions that have not Priced

NEW THIS YEAR because of Upgrade and Possible Missed Processing on Transactions showing priced but not creating the BIL/OLT transactions !!!!

Run TN_GR_A09_EXP_NOT_PRICED query Notify: DOA Accounting Manager – Mary Lou Goins

and Edison Projects/Grants team April Main, Jonathan Beretta, and Jeff Hopkins with Remedy Ticket

32 June 2017

Task 12 – 06/24/2017 Reconcile pass-through Grants BLD & 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 If invoice amount different from IU, does billing need to be

adjusted with a GL Journal to move transactions?

33 June 2017

Task 13 Last Day to enter Project Related Travel Expenditures – 06/30/2017

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

34 June 2017

Grant Task 14 New state FY year begins

Date Reminder on TN_GR03 July 1, 2017

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

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

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

35 June 2017

Grant Task 14 New state FY year begins

Remember to BILL the PREPAIDS

(zero dollar invoice billings) that are June transactional accounting dates

with JUNE Billing date separately!

Can be done until the AR and Billing modules close on July 13th!

36 June 2017

Grant Task 15 - 7/10/17 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/13, 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 to close the open immediate prepaid billing invoice item.

37 June 2017

Grant Task 16 - Submodules Closed

Thursday, July 13, 2017

All Edison submodules closed for FY2017 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

38 June 2017

Grant Task 17 July 28, 2017

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

Review for budget errors and missing approvals.

39 June 2017

Grant Task 18 – Friday - July 28, 2017 Grant Related Accrued Liability entries due

Last day for Accrued Liability entries

1. YAE is the analysis type on expenditure line only. 2. Journal Source – LA type journals 3. 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.

40 June 2017

Grant Task 19 July 31, 2017

Last day of entry of IU journals by creating agency related to projects/grants.

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.

41 June 2017

Grant Task 20 – August 3, 2017 Grant 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

42 June 2017

Grant Task 21 August 4, 2017

Last day for entry and submission by

secondary Agency on IU journals. Take care of the impact to Projects/Grants where

Customer Contracts may be near or at the billing limit; an agency does not want OLT transactions on the last day to do any project re-allocations!

Remember to re-run TN_GR04 to reconcile.

43 June 2017


Last day for entry of miscellaneous JV/AG/EX journals to correct a

previous FY17 transaction affecting Projects/Grants!

44 June 2017


Last day for agency approval of Accounts Receivable (source RA) journal vouchers against

Accrued Liabilities

45 June 2017

Grant Task 24 - August 4, 2017 Repeat Task #12 but go through June 30th

Reconcile grants/project related interunit (IU)

revenue with billings with an accounting date through June 30, 2017. 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

46 June 2017

Grant Task 25 – August 4, 2017 Perform Deferred/Advanced Revenue analysis

Run TN_GL048_TRIAL_BALANCE_ACCT for 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

47 June 2017

Grant Task 25 – August 4, 2017 Perform Deferred/Advanced Revenue analysis Create 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…

48 June 2017

Grant Task 26 - August 4, 2017 Analyze 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

49 June 2017


Last day of agency/business unit

APPROVALS of miscellaneous

JV/AG/EX journals to correct a previous FY17 transaction affecting Projects/Grants.

50 June 2017

Grant Task 28

Wednesday - August 9, 2017 Last day of APPROVALS

by Division of Accounts of miscellaneous

JV/EX journals to correct a previous FY17 transaction affecting Projects/Grants

and IU journals.

51 June 2017

Grant Task 29 – August 10, 2017 Interest on Deferred Revenue accounts should be posted and Conversion (type) prepaids added to

Customer Contract(s) and processed.

Refer to Grant Manual Section 4.2 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.

52 June 2017

Grant Task 30 – August 10, 2017 Contracts Module Closed

53

Customer (Revenue) contract module closed for

FY 17 (revenue recognition for FY17 will NOT occur after this date)!

June 2017

Grant Task 31 – August 10, 2017 F&A (Indirect Cost) Closed for FY17

F&A (Indirect Cost) Closed for FY 17 automated

processing for FY17 closed (stops processing for FY17)!

54 June 2017

Grant Task 32 – August 11, 2017 Billing for FY2017 Stops

Last day for temporary bills on TN_GR03 with

separate 2017 State Year accounting dates to occur!

55 June 2017

Grant Task 33 – August 15, 2017 Evaluate 2017 Journals

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

Run query TN_GR20A_JRNLS_NOT_POST_GR

56 June 2017

Grant Task 34 – Create a “no project” journal to adjust for Utilization (reduction of

Deferred/Advanced of zero dollar billings for FY17 with billed dates in FY18

57 June 2017

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 35 – Submit Schedule of Expenditures of Federal Awards (SEFA) and Supplementary

Information Schedule (SIS) September 18, 2017

58 June 2017

For SEFA - Run query TN_GR06_SEFA_EXP


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

TN_GR06S_SEFA_SUPPL

59 June 2017

Grant Task 35– 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 60 June 2017


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

61 June 2017


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.

62 June 2017


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

63 June 2017

Speedchart Reminder ! Last date for requests for new FY18 speedcharts

for FY18 for July 1st is June 23rd! 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

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

64 June 2017

Speedchart Reminder !

Query TN_GL60PE_SPDCHRTS_PRJ_ERRORS is 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

65 June 2017

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

recheck queries…..

66 June 2017

http://www.123rf.com/photo_2818785_troubleshooter-simple-conceptual-illustration.html

Questions and Answers

67 June 2017

year end operational closing procedures

Documents