Transcript Core-CT

State of Connecticut
Project Costing (PC)
and
Customer Contracts (CA)
Reporting
Agenda
• Introduction: Sue Maheux
• Project Costing Reports: Tom Vaughan/Bonnie Soucy
• Customer Contracts Reports: Vanessa Hoang
• EPM Reports: Barbara Jacius
2
• Projects: identifiers for cost collection and
for monitoring status of a product or
service
• Customer Contracts: agreements
between a state agency and the customer
that they are billing
3
• Analysis Types: identify different types of
transactions, such as budget amounts, actual
costs, and billed costs.
4
Analysis
Types
5
Commonly Used Analysis Types
Analysis Type
Description
Use
ACT
Actuals
Accounts Payable Voucher
BAJ
Billing Adjustment
Billing Invoice
BD1
Total Cost Budget
Commitment Control Budget Journal
BIL
Billable Amount
CA-Billable row created via pricing
BLD
Billed Amount
Billable to Billed row via finalization
CCA
Commitment Adjustment
Purchasing-Balance on closed PO
COM
Commitment Including PO & Sub Contract
Purchasing-PO Encumbrance
CRV
Commitment Reversal
Purchasing-PO Encumbrance reversal
FRG
Fringe Allocation
Allocations-Reallocate Fringe
GLE
General Ledger Expense
General Ledger Adjustment (5% Acct)
GLR
General Ledger Revenue
General Ledger Adjustment (4% Acct)
IKE
In Kind Expenses
Capture Shared Expense
OLT
Over Limit
CA-Billable row exceeds Contract limit
PAY
Time & Labor Actual
Payroll-Earnings & Fringe expenditure
REQ
Requisitions
E Pro-Requisitions
REV
Revenue
AR–Applied Payments
RRV
Requisition Reversals
E Pro-Requisition Reversal
6
• Analysis Groups: Analysis Types are grouped
to define relationships in order to analyze project
costs
7
Notice the Analysis Group Options – Perform Multiplication
The CTENC (Encumbrance) example shows the PO Encumbrance (COM) less the Commitment
Reversal or Voucher payment (CRV) less Commitment Adjustments or PO Closed balance of
Commitment (CCA).
Example: a PO is created for $100; a voucher is created from the PO Receipt for $75; later the PO is
closed. COM= $100, ACT= $75, CRV= $75, CCA= $25.
Based on the CTENC Analysis Group Multipliers the encumbrance balance is:
minus $25 (CCA) plus $100 (COM) minus $75 (CRV) = $0
8
• Project Transaction Table (PROJ_RESOURCE): the
main record that gets populated by feeder modules with
transactional data. Transaction rows contain the quantity
and amounts associated with each transaction and all of
the identifying information that accompanied those costs.
• Summary Table (CT_PC_RPO): custom summary
record of PROJ_RESOURCE to help with performance.
This is the main record used for most of the Project
Costing Core-CT Reports.
9
Transaction Data on PROJ_RESOURCE
refreshed daily: displayed by day
Transaction Data on CT_PC_RPO
refreshed daily: summarized by month
10
Recommended PC Reports
Project Data by Analysis Group(s) Report: displays project
financial data by one or several analysis groups
Flexible Analysis: analyzing a project’s costs at different levels
PC and GL Reconciliation Report: displays any discrepancies
between Project Costing and General Ledger expenses
PC and KK Reconciliation Report: displays any discrepancies
between Project Costing and Commitment Control
Project Payroll Costs Report: displays project payroll data by
employee and accounting date
11
Project Data by Analysis Group Report
Project Costing> Core-CT Reports> Project Data by Analysis Group
12
Project Data by Analysis Group Report
13
Project Data by Analysis Group Report
14
PDF Format
15
CTAVL Analysis Group – w/o REV
16
CTAVL Analysis Group – with REV
17
Project Data by Analysis Group
Report including REV rows
18
Project Data by Analysis Group: by Project Tree
Click the project tree folder to view
DOT Project Trees and Tree Levels.
19
Project Data by Analysis Group using
CT_Account to Capture Expenditures
20
Project Data by Analysis Group showing CT_Account Tree results
21
Project Data by Analysis Group: Date Impact
22
Project Data by Analysis Group-CRV date issue
23
Project Data by Analysis Group-CRV date issue resolved by dating
to end of fiscal year
24
Flexible Analysis
Project Costing > Flexible Analysis Template
25
Flexible Analysis Template
26
Flexible Analysis
Project Costing> Interactive Reports> Flexible Analysis
27
Flexible Analysis
28
Flexible Analysis
29
Flexible Analysis
30
Flexible Analysis
31
Flexible Analysis
32
PC to GL Reconciliation Report
Project Costing> Core-CT Reports> PC v/s GL Reconciliation
Select Summary Record
33
CSV Version of PC to GL report
34
PDF Version of PC to GL report
35
PC to GL Reconciliation EPM queries
CT_PC_TO_GL_LEDGER
CT_PC_TO_GL_PROJ_RES
• Use these EPM queries to produce transaction details. Compare
results and isolate differences to resolve discrepancies found in
the PC to GL Reconciliation Report.
36
CT_PC_TO_GL_LEDGER
37
CT_PC_TO_GL_PROJ_RES
38
PC to KK (Commitment Control) Reconciliation Report
Project Costing> Core-CT Reports> PC v/s KK Reconciliation
There is no Project Table Selection
for this report. It runs off the
Summary (CT_PC_RPO) record.
39
PDF Version of PC to KK report
Type: ENC PC Amount: 0.00 KK Amount: 1750.00 Difference: -1750.00
40
PC to KK Reconciliation
CSV Format
41
PC to KK Reconciliation-Research Tips
Commitment Control> Review Budget Activities> Budgets Overview: select
ledger, appropriate chartfield combinations, drill down into each account.
Purchase Orders> Reports> PO Balance Report: compare the Purchase Order
to the Commitment Control Ledger
EPM Query: CT_CORE_FIN_PO_DSTRB_KK_AUDIT
42
Project Payroll Costs Report
43
Project Payroll Costs
Project Costing> Core CT Reports> Project Payroll Costs
44
Project Payroll Costs
45
Recommended Customer
Contracts Reports
• Project Billable Summary
• Active Contracts Limit Management Report
46
Project Billable Summary
Project Costing> Core-CT Reports> Project Billable Summary
47
Project Billable Summary
48
Project Billable Summary: Non-DOT Specific
49
Project Billable Summary: Non-DOT Specific
50
Active Contracts Limit Management Report
Customer Contracts> Reports> Limit Amount
51
Active Contracts Limit Management Report:
Remaining
52
Active Contracts Limit Management Report:
Excess
Active Contracts Limit Management Report: Excess
54
EPM Overview
Projects Fields Available on all major Financial Reporting tables:
PROJECT_ID
ACTIVITY_ID
RESOURCE_TYPE
RESOURCE_CATEGORY
RESOURCE_SUB_CAT
BUSINESS_UNIT_PC
ANALYSIS_TYPE
55
EPM: Project Costing Queries
56
EPM Customer Contracts
57
EPM Query Modification
58
Other Project Costing Core-CT Reports
•
Project Financial Management Report -This report is used by Project Costing agencies to provide a summary
of historical & current expenditures together with cumulative budget and available balance.
•
Project Incremental Cost -This report is for DOT use only. The report is used by DOT to meet its legislative
mandate to report Special Tax Obligation Bonds set forth in P.A. 05-4, Section 18. It provides information on any
Project Incremental Cost (cost overrun) for transportation projects financed with Special Tax Obligation Bonds in the
five years preceding the date of the report.
•
Project Approval Statistics (DOT USE ONLY)- This report is for DOT use only. DOT is the only project costing
agency to configure workflow approval events.- This report also provides detail data by project including project ID,
project description, date event created, date event approved and number of days between creation and approval. The
report can be run for a single event or a range of events.
•
Project Forecast- The Project Forecast report is intended to serve as a high level estimate and analytical report
providing the user an opportunity to see the current funding levels based on the previous 12 month spending pattern.
The report provides an average of payroll and/or non payroll related expenses that were incurred during a rolling 12
month period, retroactive 365 days from the date the report is run. The report displays project forecast data and can
be used to determine whether or not a project budget is funded at sufficient levels for the next 30, 60, 90, 120, 150 or
180 days. This information is meant to function as an “early warning” for funding requests between the agency and the
funding source for the project, usually the federal government.
•
Project Estimate Mod Approval Request- This report is used by the Department of Transportation to review
and to obtain required signed approval(s) of project estimate revisions. This report provides project attributes, including
project manager, project location, estimates by activity, fund and SID for the current estimates, regardless of the
pending event and for the most recent prior approval of specific events, based on parameter selections made by the
user.
59
User Productivity Kit (UPK)
60
User Productivity Kit
61