1 / 40

STATE OF CONNECTICUT Core-CT Project

STATE OF CONNECTICUT Core-CT Project. EPM Financial User Group Meeting October 28 2004. 1. Agenda. Welcome Jeanne Skellett Public Queries Update / Reminders Reporting Table Refresh & Status Page Financial Security Update EPM Financial Reporting Scenarios Mary Yabrosky

jirair
Download Presentation

STATE OF CONNECTICUT Core-CT Project

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. STATE OF CONNECTICUTCore-CT Project EPM Financial User Group MeetingOctober 28 2004 1

  2. Agenda • Welcome Jeanne Skellett • Public Queries Update / Reminders • Reporting Table Refresh & Status Page • Financial Security Update • EPM Financial Reporting Scenarios Mary Yabrosky • Financial Reporting Scenario Instructions Steve Soklow • Break Out Session • Group Feedback • EPM Demonstration • EPM Phase II Frank Scalia • Billing • Assets & Inventory • Questions & Discussion EPM Team

  3. Public Query Reminders • EPM Super User Responsibilities • Naming Standards • Documentation • Backup & Recovery • Agency Query Specialist • Review & Remove Unused Queries 3

  4. Core Delivered Queries: Statewide Public Queries Agency PublicQueries CT_CORE_MODULE_FUNCTION_QNAME CT_MODULE_FUNCTION_QNAME CT_AGENCYACRONYM_MODULE_FUNCTION_QNAME Public Query Reminder Public Query Naming Standards

  5. EPM Web Status Update Tool What is it? Where is it located? http://www.core-ct.state.ct.us/epm/ How do I use It? System Updates

  6. Reporting Table Status

  7. Reporting Table Status

  8. Financial Security Update Agencies requested access to DOIT telecommunications billing information EPM Security was updated to allow read access to ITDIT Existing EPM query can be used to display telecom charges CT_CORE_FIN_SAAAS16_6_8 to your agency Financial Security Update

  9. Financial Security Update

  10. Training/Education Financial Reporting Scenarios Breakout Sessions 10

  11. Training/Education • Training Objective: • Pilot New EPM Training Approach • Initiate Discussion Among Users • Gain / Reinforce Knowledge of: • EPM Reporting Tables • Query Tool • Create and Demonstrate New Queries for: • Private Use • Public Use • Excel Pivot Tables 11

  12. Training/Education • Breakout Sessions: • User’s will break out into 8 Groups (Groups of 8-10) • Three (3) Financial Reporting Scenarios provided • Groups will create queries based on scenarios using Query Template and Supporting Job Aids provided • Groups will: • Report results: • Build Query On-Line • Download to Excel Pivot Table 12

  13. Training/Education Breakout Sessions: Query Template we will use in Exercise Reflects Query Component Pages: 13

  14. Training/Education Breakout Sessions: Query Template we will use in Exercise Reflects Query Component Pages: CT_VCHR_TRNS_TBL 14

  15. Training/Education Breakout Sessions Cont. – Tables 1 & 2 AP Scenario 1: You need to identify all Vouchers that have not been posted. Create a query from the Voucher Transaction table on Voucher ID, Accounting Date, Voucher Match Status, Approval Status, Budget Header Status, Date Entered, Gross Amount, Close Status for a date range by Business Unit, where AP Post Status is ‘Unpaid’, Entry Status is ‘Postable’. 15

  16. Training/Education Breakout Sessions Cont. – Tables 1 & 2 AP Scenario 1 - Sample Results: 16

  17. Training/Education Breakout Sessions Cont. – Tables 3 & 4 AR Scenario 2: You are reconciling Deposits against an Account and SID and need to tie Ledger Activity (Journal ID) back to a Deposit ID. Create a query off of the Direct Journal Payment table on Deposit Business Unit, Deposit ID, Payment Sequence, Deposit Type, Bank Code, Accounting Date, Account, Account Description, Fund Code, Program Code, Monetary Amount, Ledger, Journal Date by Department, Account, SID and Journal ID where entries have been distributed to the General Ledger. 17

  18. Training/Education Breakout Sessions Cont. – Tables 3 & 4 AR Scenario 2 – Sample Results: 18

  19. Training/Education Breakout Sessions Cont. – Tables 5 & 6 GL Scenario 3: You want to create a ledger activity report by Expenditures, SID and Department and compare monthly Posted Totals Amounts from FY 2004 to FY 2005 in an Excel Pivot Table . Create a query off of the CT Ledger Balance reporting table on Business Unit, Ledger, Account, Account Description, SID, Program, Fiscal Year, Budget Reference, Posted Total Amount by Department, Account and SID. 19

  20. Training/Education Breakout Sessions Cont. – Tables 5 & 6 GL Scenario 3: 20

  21. Training/Education • Breakout Sessions Cont. • Report Results - All Scenarios • Build Query On-Line - All Scenarios • Create Excel Pivot Table – Scenario 3 21

  22. Training/Education • Breakout Sessions cont. • Excel Pivot Table – Scenario 3 • Demonstrate MS Excel Pivot Table 22

  23. Training/Education • Breakout Sessions cont. • Excel Pivot Table – Scenario 3 • When should I use a PivotTable report? • Use a PivotTable report when you want to analyze related totals, especially when you have a long list of figures to sum and you want to compare several facts about each figure. Because a PivotTable report is interactive, you can change the view of the data to see more details or calculate different summaries, such as counts or averages. 23

  24. Training/Education MS Excel PivotTable and PivotChart Wizard Instructions: Place cursor in first cell of data of your Excel Worksheet:

  25. Training/Education PivotTable and PivotChart Wizard: Click Data Menu Item and select PivotTable and PivotChart Wizard Report option.

  26. Training/Education PivotTable and PivotChart Wizard Step 1: Choose Default MS Excel database and click Next >.

  27. Training/Education PivotTable and PivotChart Wizard Step 2: Because you placed you cursor in the first cell of you data on your worksheet first, Pivot Table and Pivot Chart Wizard Step 2 – 3 window will search for all data, down and to the right, or Range: $A$1:$G$10 in this scenario. Click Next >.

  28. Training/Education PivotTable and PivotChart Wizard Step 3: Save new Pivot Table to a New Worksheet. Click Finish.

  29. Training/Education PivotTable and PivotChart Wizard. Drag and Drop Fields.

  30. Training/Education Breakout Sessions Cont. – Tables 5 & 6 GL Scenario 3 – Pivot Table Sample Results 30

  31. Training/Education Breakout Session Cont. Identify Query for Public Realm Demonstrate Proper Steps in Making Query Public 31

  32. Training/Education New Month/Year End AP Public Queries moved to EPM 32

  33. Break (15 minutes) Please turn in your index cards!!! 33

  34. Billing Reporting Tables January 31, 2005 Asset & Inventory Tables July 1, 2005 Tables Schedule EPM Tables Schedule

  35. Billing Invoice The Billing Invoice reporting table will allow Billing Processors to query on billing/invoice information, consolidation information and gather history information. The Billing Processor would be able to query the open item(s) and its related accounting distribution that is added onto an invoice line(s). Billing Table Descriptions EPM Billing Reporting Tables

  36. Billing Recurring The Billing Recurring reporting table will contain information related to the schedule that is set up for a recurring bill. The user will be able to see the dates on which a recurring bill is generated, and its associated invoice date, for the length of time specified by the user. Billing Interface The Billing Interface reporting table lists the open items and transactions found on an invoice that are run from another system or module into the Billing module. Billing Table Descriptions EPM Billing Reporting Tables

  37. Billing Pending Items The Pending Items table lists the items sent to Accounts Receivable for management of payment/collection of the open item entered in Billing. Billing Account Entry The Accounting Entry table lists the accounting lines created for each activity/transaction that occurs for an invoice line. For example, when a bill is first generated, an AR and Revenue accounting line is created. When payment is made for an invoice line, another accounting line is created to recognize the payment for the open item. Billing Table Descriptions EPM Billing Reporting Tables

  38. Updates / Reminders Security Updates / Reminders Security is driven the same way it has been. • Users will only see their own Business Unit(s). • Users with rights to the Billing Function in Financials only will have access to EPM Billing tables. Data Dictionary Updates / Reminders The Data Dictionary entries will be delivered 1/31/05. • Table definitions. • Field definitions. 38

  39. QUESTIONS? COMMENTS? FEEDBACK? 39

  40. Closing Remarks Thank You for your participation today. We look forward to continuing our work together to improve and make EPM a Success! • Feed Back Forms • Suggestions for Training Areas • Agency Presentations • Look for this presentation and meeting on the Core-CT Website (http://www.core-ct.state.ct.us/epm) • Next Meeting – • 101 E. River Drive, Room 1002 A&B

More Related