DRAFT_PWS_8_Feb18.pdf

Financial Operations, Audit, and Systems Support for United States Transportation Command (USTRANSCOM)

Program Analysis and Financial Management Directorate (TCJ8) Performance Work Statement (PWS)

8 Feb 2018

1.0. DESCRIPTION OF SERVICES. The requirement is to acquire non-personal services to assist in accounting and financial operations and sustainment of audit readiness in compliance with Generally Accepted Accounting Principles (GAAP) as well as provide a broad spectrum of systems support across the USTRANSCOM enterprise.

1.1. Background. Financial management and reporting in terms of consistency, traceability, and transparency is a challenge throughout the Department of Defense (DoD). USTRANSCOM with its Components and current funding and financial management processes face additional complexities due to unique, service-specific financial accounting systems and managerial structures. These factors directly contribute to current deficiencies in data quality, processes, and traceability from order to fulfilment, to billing and payment, and reconciliations. Current Information Management systems drive challenges such as labor intensive reconciliations; limited or non-existent access to information; data quality risks;

complex interfaces and manual processes; and inconsistent financial reporting. Key factors contributing to challenges faced by USTRANSCOM leadership:

• Multiple accounting and financial management systems

Defense Enterprise Accounting and Management System (DEAMS)

• Surface Deployment and Distribution Command (SDDC) Transportation Financial Management System (TFMS)

• Military Sealift Command Financial Management System (MSC-FMS)

• Implementation of USTRANSCOM Operational Blueprint to develop an integrated multi-modal operational process

• Single repository and billing solution across Transportation Working Capital Fund (TWCF)

• Inability to establish complete reconciled universe of transactions tied to the financial statements to include beginning balances

• Standardized financial management processes across TWCF operations

• USTRANSCOM-wide communication of the audit strategy; clarity of guidance and unity of efforts

• Complexity of USTRANSCOM mission operations and financing through TWCF rules of engagement

• Annual audit of TWCF full financial statement audit

• Federal Information System Controls Audit Manual (FISCAM) compliance of multiple core accounting systems and critical feeder systems

• Magnitude of potential audit issues from (Information Technology) IT Systems and Financial Statements to be resolved

• Resource constraints to accomplish required efforts

• Identifying, developing, and remediating of Corrective Action Plans (CAPs) identified through audit efforts and Notice of Findings and Recommendations (NFRs) by the IPA (Independent Public Accountant)

• Establishing and maintaining auditable business processes and supporting systems

1.2. Objective. This PWS defines the specific tasks to be performed for USTRANSCOM audit and financial system efforts. The overall goal of this effort is to ensure USTRANSCOM, which includes its Transportation Component Commands (TCCs) (AMC, SDDC, MSC) meets mandated schedule requirements and resolves all issues which are preventing progress towards becoming auditable and demonstrating continuous improvement toward future audits in compliance with GAAP.

1.3. Scope. The purpose of this PWS is to provide USTRANSCOM/TCJ8 expert advice, assistance, and technical accounting and auditing in support of audit and business improvement effort. The scope includes: technical guidance, integration, risk management, issue resolution, integrated schedule management, communication management, strategic and technical audit program analyses and reports to support the full range of USTRANSCOM Operational and Financial Systems. Core objectives related to this engagement involve an efficient TCJ8 that is agile to address, remediate, and implement core findings identified through self-identification and audit efforts. Specifically:

• Ensure operational business processes and internal control activities are designed and operating effectively to limit the risk of material misstatement of the financial statements by meeting Financial Reporting Objectives (FROs); and

• Develop supporting documentation to address all relevant financial statement assertions for all material transactions and balances as defined by Key Supporting Documents (KSDs).

Specific support requirements will vary, commensurate with the audit schedule and findings/reporting schedule. Throughout the life of this contract, the Contractor is encouraged to seek ways to incorporate innovative and emerging technologies to achieve compliance thereby improving USTRANSCOM mission performance and ultimately improving the USTRANSCOM

TWCF Operations. The services provided within the scope of this contract are considered Covered Defense Information (CDI) as defined in DFARS 252.204-7012 Safeguarding Covered Defense Information and Cyber Incident Reporting.

The PWS includes the following task areas:

Task Area 1: Contract Level Management (FFP) Task Area 2: Continuous Improvement /Audit Sustainment Efforts (LH) Task Area 3: Audit Management Project Team Support (LH) Task Area 4: Internal Control Program (ICP) (LH) Task Area 5: General Support to the JFC’s Mission Operations (LH) Task Area 6: Financial Systems Support for the JFC’s Systems Cell (LH) Task Area 7: Financial Operations Support (LH) Task Area 8: Information Technology Systems - FISCAM Team Support (LH) Task Area 9: Auditable Financial Statements Support (LH) Task Area 10: Audit Advisory Services (LH) Task Area 11: SDDC support (LH) - OPTIONAL

1.4. Specific Tasks.

1.4.1. Task Area 1: Contract Level Management

This task consists of the contractor activities relating to the administration and management of the entire PWS. The contractor shall provide management of all projects and tasks within the scope of this PWS.

The Contract Lead shall provide the following support for all tasks within the PWS:

• Day-to-day oversight and interaction with TCJ8 personnel

• Provide and prepare documents such as point papers, briefings, and meeting minutes related to the status and performance of projects.

The contractor shall assist the Government to ensure cost, schedule, performance, security, and risks are managed for activities and projects. The contractor shall coordinate with the Government to ensure all activities are well synchronized. The Contractor shall provide a Quality Assurance process for all tasks of the contract upon contract award and updated as required. The contractor will provide a written staffing plan with an org chart for each of the contract tasks and will include, monthly, the amount of hours per person, per work stream.

1.4.1.1 Task Area 1 Subtask 1: Monthly Status Reporting (MSR) The contractor shall prepare and sustain a milestone schedule that defines all tasks, durations, timelines, resources, and dependencies for all projects on this contract. In addition the contractor shall provide cost reporting detailing costs incurred in areas such as FTEs; travel or other reports required by the government to ensure cost oversight and manage contract costs by work stream and per person. Reports shall document status of labor and travel funds expensed for the year, for the preceding month, and available for the remainder of the year. Expenses will be broken out against each task with a breakout of major work streams that tracks and reports monthly level of effort (people and hours). It shall include, at a minimum: (a) a brief task description of the area(s) of focus being worked, (b) a narrative review of work accomplished during the reporting period, (c) problem areas, (d) anticipated activity for the next reporting period, (e) audit risks and associated key drivers, and (f) recommended corrective actions. The schedule represents the contractor’s tasks, dependencies, and milestone dates, shall be in Microsoft Project format, and shall be provided monthly, no later than (NLT) the fifth (5th) business day, as part of the MSR. The milestone schedule shall be maintained and kept up-to-date to identify all in-progress and planned projects and tasks as they progress to completion. The contractor shall ensure the schedule includes each of the lowest level tasks not exceeding a four (4) week duration and each lowest level task being dependent on the start or finish of another task. The schedule shall reflect the contractor’s most realistic timelines as it will be used to synchronize other USTRANSCOM and its components efforts. Travel reports are required when contractor is traveling without Government personnel.

1.4.1.1 Task Area 1 Subtask 2: Contractor Manpower Reporting

The Contractor shall report all direct contractor labor hours (including subcontractor labor hours) required for performance of services provided under this contract for USTRANSCOM via a secure data collection site. The contractor is required to completely fill in all required data fields using the following web address: http://www.ecmra.mil/

Reporting inputs will be for the labor executed during the period of performance during each Government fiscal year (FY), which runs October 1 through September 30. While inputs may be reported any time during the FY, all data shall be reported NLT October 31 of each calendar year.

Contractors may direct questions to the help desk at help desk at: http://www.ecmra.mil/

1.4.1.1 Task Area 1 Subtask 3: Accruals

The Contractor shall provide estimates of monthly billings not later than ten (10) business days before the end of each month for the purpose of accruing funds.

1.4.1.1 Task Area 1 Subtask 4: Burn Rate Report

The contractor shall provide a monthly Burn Rate report by task area as part of task order management to track labor hours expended. Burn Rate report is due with MSR NLT than the 5th business day of the month.

1.4.1.1 Task Area 1 Subtask 5: In-Process Review

The contractor shall provide a semiannually In-Process Review (IPR) as required by the government on the status of all current tasks.

The eCMRA site associated with this contract is Air Force.

http://www.ecmra.mil/ http://www.ecmra.mil/

The Command of the Requiring Activity is AFELM US TRANSCOM. The Unit Identification Code (UIC) for this requirement is FFKG40.

The Federal Service Code for this contract is R703.

1.4.2 Task Area 2, Continuous Improvement/Audit Sustainment Efforts

This task consists of support for operational systems and financial statements by performing certification of testing events, recommendations for improving compliance and evaluation of risk assessments. The effort shall also include technical support by providing Corrective Action Plan (CAP) identification, assessment, development, recommendations, training, validation of remediation and guidance to support compliance. If necessary, the Contractor will update USTRANSCOM policy or guidance to support corrective action. In addition, the Contractor will facilitate bi-weekly CAP meetings with USTRANSCOM personnel and Components to ensure CAPs are being remediated. The Contractor will also maintain a CAP tracker database with the necessary fields to ensure deficiencies are adequately recorded and remediated. Notice of Findings and Recommendations (NFRs) are referenced in paragraph 1.4.2.2. NFR and CAP status will be provided bi-weekly.

1.4.2.1 Task Area 2, Subtask 1, Continuous Improvement/Audit Sustainment Efforts In an effort to sustain auditable financial statements, the contractor shall provide support to the Joint Finance Center (JFC) to implement corrective action plans as well as new guidance from OSD. This task consists of, but is not limited to, identification, recommendations, organization and process improvements to allow the JFC, Directorates, and other reporting units to address and respond to key findings related to audit such as universe of transactions, Fund Balance with Treasury, journal vouchers, reconciliations, depreciation, existence and completeness for PP&E, valuation of general PP&E and inventory and related property, and administration of the Tri-annual Review.

1.4.3. Task Area 3, Audit Management Project Team Support

This task order shall provide TCJ8 with audit program management support, development of Support Agreements, Memorandums of Agreement (MOAs), General Terms and Conditions (GT&C) and G-Invoicing, execution of the audit communications and monitoring sustainment efforts and transitioning efforts to the Internal Control Program (ICP). In addition to following the OMB Circular A-123, the contractor shall refer to any and all applicable Government regulations, for example, Generally Accepted Government Auditing Standards (GAGAS), Generally Accepted Accounting Principles (GAAP), Statement of Federal Financial Accounting Standards, and American Institute of Certified Public Accountants. Services provided under this task shall include:

1.4.3.1 Task Area 3, Subtask 1, Audit Management Support

The contractor shall provide TCJ8 with audit management support in executing audit activities.

Tasks in this area include, but are not limited to, hosting a TCJ8/component monthly Audit update meeting, maintaining an organized, logically structured library of documentation required for audit, maintaining the SharePoint audit collaboration site, supporting the Audit Response Center (ARC) tool by maintaining the Provided by Client (PBC) movement, metrics and ensuring all information corresponds with the IPA data, and supporting the Audit Management team and Components. In addition, the Contractor will develop and maintain metrics that portray the status of audit efforts.

The contractor shall provide minutes within two business days following the conclusion of a meeting. Meeting minutes shall be in a format provided by the Government. Meetings shall include OSD (biweekly), DFAS (biweekly), and TCCs (biweekly) and other similar type meetings.

The contractor will also support OSD audit tools including Notice of Findings and Recommendation (NFR) tool.

The contractor shall prepare slides and provide briefings on audit compliancy and progress to various levels of the USTRANSCOM organization that may include, but not limited to, weekly updates to TCJ8-A, bi-weekly updates to TCJ8, monthly updates to USTRANSCOM Corporate Board, USTRANSCOM Oversight Council, and monthly Deputy Chief Financial Officer and Deputy Chief Management Officer meeting as well as monthly USTRANSCOM leadership briefings to include USTRANSCOM Commander, USTRANSCOM Deputy Commander, etc. The contractor shall ensure key attendees as determined by the Government are provided updated hardcopy slides.

The contractor shall assist the Government with scheduling meetings, meeting rooms, teleconference lines, and making copies, and other general audit program support.

The contractor shall provide other administrative and project management tasks as requested by the

TCJ8.

Contractor shall support the use of the Task Management Tool (TMT), Audit SharePoint site, ARC tool as necessary, to prepare recommended suspenses for Government consideration to appropriate parties providing acceptable time to develop responses. Contractor shall maintain a listing of open tasks, provide status, and perform follow-ups as necessary.

Contractor shall maintain and update key documents (Support Agreements, Memorandums of Agreement (MOAs), Communication and Audit Strategic Plan, Process Narratives to include flowcharts and Risk Control Matrices). During the life of this contract, additional key documents may be added.

1.4.4. Task Area 4, Internal Control Program (ICP)

This task shall provide TCJ8 with support to execute a strong internal control program that meets the requirements of:

• Section 1352 of Title 31, United States Code, also known and referred to in this issuance as the “Federal Managers' Financial Integrity Act of 1982 (FMFIA)”

• Section 3512 of Title 31, United States Code, also known and referred to in this issuance the “Federal Financial Management Improvement Act of 1996”

• Public Law 111-352, also known as the “Government Performance and Results Modernization Act of 2010”

• Government Accountability Office 14-704G (also known as and referred to in this issuance as “the Green Book.”)

• Office of Management and Budget (OMB) Circular No. A-11

• OMB Circular No. A-123

• OMB Memorandum M-17-25

• Office of the Deputy Chief Management Officer of the Department of Defense Internal

Control of Operations Guidance

• Office of the Under Secretary of Defense (Comptroller)/Chief Financial Officer, Department of Defense Financial Improvement and Audit Readiness Guidance

• DoDI 5010.40 Managers’ Internal Control Program Procedures

1.4.4.1 Task Area 4, Subtask 1. ICP Sustainment/Improvement Support.

The contractor will provide support and expert guidance to sustain and improve the USTRANSCOM ICP at all levels of the organization to include but not limited to: documenting goals and objectives of the USTRANSCOM internal control program; provide a timeline of key ICP actions NLT 15 October; assist in the proper documentation of end-to-end processes;

identifying, documenting, ranking internal controls; recommend improvement of internal controls;

and assist in the management of risk and perform risk analysis. The contractor shall, in collaboration with USTRANSCOM, provide and document initial, refresher, and task-oriented ICP training, briefing an annual kickoff meeting NLT 31 December to all primary and alternate Assessable Unit Managers (AUMs), and within 30 calendar days of appointment of new personnel which meets the ICP requirement for training.

The contractor will conduct staff assistance visits (SAVs) as needed, but at least annually with each directorate. The contractor will conduct testing to assess operational and design effectiveness of financial, operational, and IT controls and assist in the identification of weaknesses, recommend corrective actions, assist in development of corrective action plans (CAPs) and track until completion.

The contractor will provide TCJ8 with support in executing ICP activities. Tasks include but are not limited to: hosting periodic meetings to provide training or ICP/A-123 updates, documenting meetings with minutes within five business days, develop and maintain metrics that portray the status of the program, maintaining documentation in an organized manner to substantiate program requirements.

The contractor shall prepare slides and provide briefings to various levels of the USTRANSCOM organization that may include but are not limited to: updates to TCJ8-AS, TCJ8-A, TCJ8, quarterly updates to USTRANSCOM Corporate Board.

Contractor shall support the use of the Task Management Tool (TMT) as necessary, to prepare recommended suspenses for Government consideration to appropriate parties providing acceptable time to develop responses. Contractor shall maintain a listing of open tasks, provide status, and perform follow-ups as necessary.

The Contractor shall monitor USTRANSCOM Directorate performance and prompt correction of material weaknesses and compliance with program requirements. This includes tracking of corrective action plans. Additionally, the contractor will coordinate with the audit team to accept responsibility for sustainment of corrective actions for weaknesses that have been remediated as a result of actions to prepare for the financial audit or corrected NFRs as a result of the audit.

1.4.4.2 Task Area 4, Subtask 2. Statement of Assurance.

Annually the contractor shall support accomplishment of Directorate/Chief of Staff /Component Statements of Assurance (SOA) that support the USTRANSCOM statement. The contractor shall draft the TCJ8 SOA NLT 25 July stating the TCJ8 level of assurance over financial reporting and financial systems and operations. Contractor shall draft the USTRANSCOM SOA from a government-provided template that states (based on the documented program) the level of assurance over operations, financial reporting and financial systems. The SOA will highlight risk, internal control compliance, accomplishments, weaknesses and corrective actions and meet or exceed OSD’s suspense, usually 1 September. The contractor will deliver the USTRANSCOM SOA incorporating government comments, as applicable, NLT 1 August.

1.4.4.3 Task Area 4, Subtask 3. Governance, Risk Management and Compliance (GRC) (Optional based on software implementation-DO NOT PRICE) (estimated to begin Option year 2).

The contractor shall assist the government in standing up GRC software designed to use a single framework to manage risk, internal controls, compliance, testing across operational, financial and system functions of USTRANSCOM. This task includes providing a plan to implement the new system to include advice on setup and customization of the software as available, upload/input of existing data, and providing training in the use of the software as required. The contractor will use the GRC, if established to complete program requirements as listed elsewhere in task area 4 in support of the ICP/A-123 effort.

1.4.5. Task 5, General Support to the Joint Finance Center's (JFC) Mission Operations

The contractor shall provide a wide range of technical support, analysis, and other financial related activities in support of the JFC mission operations. As part of audit readiness efforts and audit findings from Military Departments and Other Defense Organizations (ODOs), a number of DoD-wide Generally Accepted Accounting Principles (GAAP) deficiencies were identified to include but not limited to: universe of transactions, Fund Balance with Treasury (FBWT), journal vouchers, property plant & equipment (PP&E), financial reporting compilation, Contracts and Vendor Pay, and Reimbursable Budget Authority and Execution. The contractor will provide expert support surrounding control and oversight at the consolidated and component level regarding sustainment activities as they relate to financial reporting.

Upon IPA full financial statement audit NFR issuance, contractor will assist in CAP creation, execution, and close out. Examples of support the contractor shall provide could include: perform financial data reconciliations with the ability to research and resolve differences/variances and develop solutions to prevent reoccurrence; review/research existing documentation, or provide documentation, pertaining to recording, depreciating/validating assets to include establishing existence and completeness for assets, and asset inventory; perform analysis for cash management, accounts receivables, billing, and disputes, and perform root cause analysis; prepare corrective action plans as appropriate and assist with implementation of the plan by engaging with internal/external customers in the performance of all activities.

Provide reconcile Intra-Governmental Transactions (IGT) support utilizing the Department of Treasury Government Invoicing (G-Invoicing) platform. Provide journal voucher (JV) support in areas such as identifying potential reductions in volume and/or dollar value of JVs, performing or reviewing the root cause analysis of JVs, and working with DFAS to understand their efforts and what efforts USTRANSCOM needs to undertake.

Provide PP&E support including inventory, recording depreciation, and capitalization of assets.

Provide accounting support related to Funds Balance with Treasury (FBwT) and Financial Reporting. Individual task may include cash, Unmatched Transactions (UMT), CCAS variances, etc. The resource may include but not limited to reconcile/review abnormal balances, analyze, and perform root cause analysis, and work with DFAS to understand, coordinate, and correct errors. In addition, the resource will provide findings and recommendations on reoccurring issues/variances to fix the process and/or system. In support of financial reporting, assist in refining the monthly/quarterly/annual financial reporting process in TCJ8 to include gathering reports, performing analysis, reconciliations, developing Standard Operating Procedures, provide training, etc.

Provide support in the administration of the Defense Travel System (DTS) and Government Travel Card (GTC) programs to the Lead Government Travel System Administrator.

Only government personnel should make official entries in the accounting systems and the contractor is prohibited from accessing proprietary information from other contractors.

1.4.6 Task Area 6, Financial Systems Support for the Joint Finance Center’s Systems Cell

The contractor shall provide the following support for the JFC Systems cell in meeting its mission objectives, responsibilities and assigned taskings related to implementation of financial systems that support USTRANSCOM’s financial operations: Financial system integration, risk management, issue resolution, analyses and reports through various forms of communication consistent with audit principles and objectives. This includes the full spectrum of Enterprise Resource Planning implementation guidance and assistance services needed to assist in the Planning (requirement definition), Governance, Design, Business Case Analysis (Use Case, Configuration, etc.), and implementation of requirements for financial systems; i.e., Defense Enterprise Accounting and Management System (DEAMS), Transportation Financial Management System (TFMS), Financial Management System (FMS), and Distribution Component Billing System (DCBS) for USTRANSCOM and its components (AMC, SDDC, and MSC). Contractor shall also assist in providing support to meet financial and auditability requirements for other functional systems such as a transportation management system.

The contractor shall provide program management and subject matter expertise to USTRANSCOM JFC staff. Based on specified instructions from the Government, the contractor shall liaison as directed with the Assistant Secretary of the Air Force for Financial Management and Comptroller, Deputy Assistant Secretary for Financial Operations, USTRANSCOM Components, Deputy Chief Management Officer, OSD Acquisition, Technology and Logistics ((OSD(AT&L)), and the Defense Financial Accounting Service (DFAS). Tasks will support USTRANSCOM from a TWCF Enterprise level encompassing end- to-end financial systems. This will enhance business process decisions and enable the achievement of auditable financial statements. Many issues have risen due to Program Managers making individual system decisions without knowledge of the impact to TWCF end-to-end (Enterprise Level) processes as a whole.

Enterprise Level business process re-engineering must occur to ensure streamlined business processes and full audit compliance across TWCF. Tasks are in support of the President’s Management Agenda and USTRANSCOM’s strategic goals and initiatives. Contractor shall be required to perform temporary duty in support of the TCJ8 director at various meetings.

1.4.6.1 Task Area 6, Subtask 1 Audit analysis and research for the JFC Systems Branch

The Contractor shall provide expert technical and functional recommendations and guidance related to requirements definition and analysis, design, and implementation of audit findings IAW audit strategy and guidance for USTRANSCOM and their Components (AMC, SDDC, and MSC).

The contractor shall integrate the Audit Strategic Plan with the IT Integrated Technical Roadmap and Operational Blueprint for JFC designated core managed systems.

• Enterprise Business Process Improvement/Re-engineering. The contractor shall provide expertise around Business Process Re-engineering opportunities throughout TWCF Enterprise Level systems based on audit findings. Specific recommendations around potential changes shall be provided with primary focus on fixing the issues as required. This expertise will assist USTRANSCOM in defining priorities across operations and gain auditability.

• Requirement Definition. The contractor shall work with TCJ8 leadership and their components to quickly identify key changes to processes and/or systems requirements documentation to address audit findings or other deficiencies resulting in compliance and operational improvements across the command’s financial management functions.

• Business Case Development. The contractor shall work with the appropriate program/functional office to develop required System Change Request and Business Case Development for any new requirements identified. Products may include, but are not limited to Rough Order of Magnitude, Independent Government Cost Estimate, Economic Analysis, and Analysis of Alternatives. The contractor shall provide expertise on reporting technologies and their use to support effective business decision making. Specific expertise shall focus on providing a reporting strategy that includes both short and long-term scenarios. Documents shall be complete, sound and require minimal rework prior to government review, acceptance and approval.

• Repeatable Methodology. As findings are addressed, the contractor shall provide a process to identify, define, plan, design, and implement potential resolution activities into existing processes and systems impacted.

• Enterprise Business Process Flows. The contractor shall assist in functional builds or update business process flows for requirements identified as gaps or issues related to audit readiness. Business process design principles will be utilized to document required processes from Level 1 to Level 5.

• Strategic Business Process/Business System Advice. The contractor shall work with TCJ8 Audit team and financial/critical systems owners to remediate identified CAPs and implement processes to meet and maintain FISCAM, Risk Management Framework (previously known as DoD Information Assurance Certification and Accreditation Process, and additional compliance requirements necessary for Authority to Operate. If required contractor shall advise TCJ8 regarding Strategic Business Process/Business System changes to resolve CAP when deemed as more appropriate fix.

1.4.6.2 Task Area 6, Subtask 2. DEAMS Support

The Contractor shall provide expert technical and functional recommendations and guidance related to requirements definition and analysis, design, and implementation regarding DEAMS, the core financial system for USTRANSCOM and AMC.

• Monitoring DEAMS trouble tickets impacting USTRANSCOM operations. The contractor shall maintain a current list encompassing all DEAMS tickets impacting USTRANSCOM operations, which shall include defects and system change requests, and provide monthly update by the 5th workday of each month. The list at a minimum shall include the ticket number, description of issue, current status, workarounds and their impacts, release/post production support number, and date scheduled for release to production. The contractor shall advise USTRANSCOM regarding tickets regarding their significance and impact to USTC and provide a recommended way forward prior to any pertinent DEAMS governance board where it may be discussed.

• Design, Build and Test Support. The contractor shall validate system issues reported by end users and submit trouble tickets to the SERENA Management System. The contractor shall support the F/PMO in developing and testing test scripts for fixed asset functionality.

The contractor shall also validate test scripts and data to provide resolution methodology for DEAMS trouble tickets, as well as test and confirm the trouble ticket solution in the DEAMS Capabilities Integration Environment and production. The contractor shall document testing results in the Air Force Quality Center.

• Acquire to Retire (A2R) Process Management Asset Management. The contractor shall provide specific guidance relating to fixed asset management processes for TWCF resources within the DEAMS environment. Support shall include providing guidance on creating asset records in the Oracle Assets module of DEAMS, changing asset details, reclassifying assets, adjusting accounting information, performing asset transfers, inventory management, depreciation calculation and asset retirement procedures associated with the A2R functions within Oracle. The contractor shall also provide Capital Project construct guidance for the DEAMS Project Accounting Team.

• A2R Asset Reporting. The contractor shall develop detailed reports to identify asset records for real property, Information Technology (IT) equipment, and internal–use software to reconcile USTRANSCOM Fixed Assets from DEAMS back to the Air Force’s General Accounting & Finance System–Reports module. Specific guidance will provide assistance in documenting/reporting asset descriptions, locations, categories, service dates, depreciation methods and net values during the useful life of TWCF assets based on direction from the DoD Financial Management Regulation (FMR) and DFAS Blue Book Regulation

• A2R Asset Journal Entries. The contractor shall assist the government in researching journal entries for depreciation expense, asset cost, and other accounts that shall summarize expenses in sub-ledger accounts. The contractor shall not post entries in

DEAMS.

1.4.7 Task Area 7, Financial Operations IT Support

The contractor shall provide financial analysts with easy-to-use access to transaction and summary-level financial and operational information required for fact-based analysis and decision making.

This includes enhancing and maintaining the existing USTRANSCOM IBM Cognos-based Enterprise Business Intelligence (EBI) “business layer”, which hides much of the complexity of several underlying data sets from users. This business layer uses standard business terminology versus underlying cryptic database object names; it provides an integrated view of common data sets, so users don’t need to worry about performing table joins; it contains standard, frequently used calculations to increase reporting consistency and accuracy, and to save users time; it also includes frequently-used query data filters objects, which often allow users to more quickly generate queries and reports. The business layer physically consists of a combination of Cognos PowerCubes, and Cognos Analytics packages, which are accessible to users via the IGC Web site, and PowerPlay for Windows. Contractor shall attend meetings, briefings, conferences and workshops. Activities performed and discrepancies or problems identified will be included in the

MSR.

1.4.7.1 Task Area 7, Subtask 1. Data Loading Process

Contractor shall maintain and update TCJ8 Integrated Data Environment/Global Transportation Network Convergence (IGC) Teradata Enterprise Data Warehouse (EDW) data loading processes that replicate key subsets of AMC, MSC, SDDC, and USTRANSCOM TWCF operational, accounting, and billing data sets. Contractor shall work with the applicable AMC, MSC, SDDC, and USTRANSCOM systems’ program management offices and systems administrators to ensure continued access to required source data. TCJ8 current data loading processes retrieve data from DCBS, DDRS, GATES, DEAMS, CRIS, iSDDC, and TFMS, and the DoD TPPS (currently USBank Freight Payment/Syncada, f.k.a. “PowerTrack”). This data is ultimately used to supply initial and updated data to Cognos PowerCubes, and applicable, TCJ8-maintained Teradata-based TWCF data marts on a regular and as-needed basis. The Contractor shall ensure that the data loading process is obtaining source data from the prescribed source systems. The Government will ensure data is available from these source systems in support of developing and maintaining Cognos-based cubes, executive dashboards, metrics, and reporting objects. Contractor shall be responsible for connectivity to feeder systems of different database client software including, but not limited to, Oracle NET 11G, Teradata Client, and the Web-based DoD TPPS. Contractor shall be responsible for loading data into the Teradata-based, TCJ8-maintained TWCF data marts using Teradata utilities such as Basic Teradata Query (BTEQ), FastLoad and MultiLoad. The Contractor shall ensure all needed functionality is provided to enable TCJ8 analysts to load data and generate cubes on their own. However, the Contractor shall be responsible to update cubes and applicable Teradata-based data marts, at minimum, on a weekly basis as well as when needed. Data load processes need to be automated as much as possible. This is a designated Information Assurance (IA) role.

Contractor IA personnel assigned to perform this subtask shall be qualified as an IA Technician Level II (IAT-II), per DoD 8500.1/5200.02. The Contractor shall provide copies of the required certification(s) applicable for IAT-II to the COR and designated government team lead at the start of contract performance.

1.4.7.2 Task Area 7, Subtask 2. Maintain and Update Data Mart Data Transformation and Cleansing Scripts

TCJ8 manages sets of scripts that automate several routine data transformation and cleansing processes within the IGC Teradata-based TCJ8 data marts. These scripts consist of Teradata Structured Query Language (SQL) statements and stored procedures that transform and cleanse data prior to it being used within Cognos BI applications. The end result is far more accurate reporting of information from the various Cognos BI applications. TCJ8 data transformation processes also include deriving new information from existing data. For example, the SDDC ocean liner business area contains miscellaneous expenses and accessorial costs that are not associated down to the individual shipment level. The data transformation processes will allocate these types of expenses down to the shipment unit level by deriving an “Estimated Miscellaneous Expense” and “Estimated Accessorial Costs” values for each shipment movement cost record. The Contractor shall receive requirements and modify/enhance the data transformation and cleansing scripts. The Contractor shall modify the applicable Teradata data transformation and cleansing scripts as necessary to support changing requirements. The Contractor shall, as needed, enhance the current data transformation and cleansing scripts in order to further improve accuracy and efficiencies. The Contractor shall modify Teradata scripts (using SQL and database stored procedures) to allocate costs not otherwise tied to a Transportation Control Number down to the individual transportation shipment level and/or other levels as needed; i.e. AMC mission, SDDC voyage, as needed. The Contractor shall create Teradata SQL Data Definition Language and Data Manipulation Language scripts needed for creating database objects, stored procedures, and triggers that support data transformation, integration, and cleansing processes. The Contractor shall test all modified scripts to ensure they perform as required and that all costs and revenue are accurately spread. The Contractor shall maintain full documentation of all modified scripts to include algorithms used, sources of data, business rules, and data dictionary (as appropriate), etc.

within 90 calendar days of completion. The Contractor shall analyze artifacts produced by scripts, either those scripts newly modified or currently in use.

1.4.7.3 Task Area 7, Subtask 3. IBM Cognos Ad Hoc Query and Reporting Capabilities

The Contractor shall provide applicable USTRANSCOM analysts with the ability to quickly and easily view operational and historical data, allowing them to better identify problems, opportunities, and trends using IBM Cognos Analytics reports, dashboards, stories, and PowerCubes. Contractor shall identify, construct, manage and maintain any needed Cognos Analytics packages, Teradata-based database tables and views, and other reporting objects necessary to assist analysts in performing their mission, including automated billing for DCD and Over Ocean Transportation. Contractor shall design, build, update, and maintain Cognos Analytics packages and sets of corresponding standard reports allowing all applicable users self-service access to TCJ8-maintained TWCF data marts. Most standard reports are developed using the Cognos Analytics Report Web-based application, so report authors need Cognos Report authoring expertise as well as experience writing Java script and HTML in order to enhance report functionality (e.g., adding custom buttons, changing the title of value prompts, and validating textbox prompt user entries).

1.4.7.4 Task Area 7, Subtask 4. Metadata Modeling Using Cognos Framework Manager

IBM Cognos Framework Manager is a metadata model development tool used to create and maintain sets of business layers published out to Cognos Web as packages. These packages provide users with the required self-service ad hoc query and reporting capabilities. The metadata is derived from multiple data sources to include relational databases and Cognos PowerCubes.

Contractor shall be responsible for developing and maintaining metadata models, within Framework Manager, and publishing major subsets of the models (as packages) out to the IGC Cognos Analytics application Web server as required by users. Contractor shall use extensively the metadata modeling development methodologies and best practices as described within the current IBM Cognos Framework Manager User Guide.

1.4.7.5 Task Area 7, Subtask 5. Requirements for IBM Cognos PowerCube Sustainment, Updates and Maintenance.

The Contractor shall identify, construct, manage and maintain any Cognos PowerCubes necessary to assist the applicable USTRANSCOM and USTRANSCOM Transportation Component Commands’ (TCCs) staff members in performing their mission. The Contractor shall support the functional and technical use and maintenance of all Cognos EBI interfaces to a data warehouse, and PowerCubes that support decision makers. The Contractor shall work with users to determine requirements, data sources, and business rules that must be incorporated into the cube model(s).

This work shall include the modification of existing PowerCubes and configuration of new cubes as analytical requirements change. Contractor shall use extensively the PowerCube modeling development methodologies and best practices as described within the current IBM Cognos Transformer User Guide Version.

1.4.7.6 Task Area 7, Subtask 6. Cognos Application Server Administration

Contractor shall perform Cognos server administration tasks to include ensuring the Cognos Windows and Web- based applications are available to authorized users. Administration includes installation, configuration and performance tuning of Cognos client and server applications, managing Cognos resources security access permissions, as directed by Government, for user accounts, groups, and roles, and monitoring system security. This subtask requires Contractor to maintain USTRANSCOM Cognos EBI resources including, but not limited to, Cognos 10.2 client and v11.x server applications. These application server administration tasks fall under the DoD-mandated Information Assurance Workforce Improvement Program (IAWIP, per DoD 8500.1/5200.02); therefore, the Contractor performing these tasks, must be an “IBM Cognos Analytics Certified Administrator”, and also CompTIA Security+ certified. This is a designated IA role.

Contractor IA personnel assigned to perform this subtask shall be qualified as an IA Technician Level II (IAT-II), per DoD 8500.1/5200.02. The Contractor shall provide copies of the required certification(s) applicable for IAT-II to the COR at the start of contract performance.

1.4.7.7 Task Area 7, Subtask 7. IBM Cognos and Teradata SQL Query Performance Tuning

Contractor shall conduct query performance troubleshooting and tuning for Cognos reports that are executing abnormally slow. Contractor shall have expertise to effectively implement Teradata proprietary SQL commands that leverage the Teradata database management system’s massively parallel architecture in order to speed up query performance. Contractor shall also assist TCJ8 with migrating several existing Cognos reports to start using

IBM Cognos’s new dynamic query mode feature, which offers key query optimizations to better address query complexity and large data volumes with improved query execution.

1.4.7.8 Task Area 7, Subtask 8. Documentation And User Guides For All Cognos Capabilities

All existing guides shall be kept current and be updated as changes are made within 90 calendar days after completion of development of new or updated Cognos capability.

1.4.7.9 Task Area 7, Subtask 9. Cognos Ad Hoc Query and Reporting Documentation

The Contractor shall provide documentation and assistance for users of Cognos Report, Dashboard, Story, or any other interface created to better facilitate analysis and reporting. The Contractor shall maintain a document that will assist Cognos users to perform reporting and analysis against TWCF data marts that reside within the USTRANSCOM EDW or any other database used for data consolidation created under this task. This guide shall address each Cognos Analytics package or any other interface created to access the TCJ8-maintained TWCF data marts. It shall include information about the database (both logical and physical names), database tables and columns, folders in each catalog, and the items in each folder, table joins, calculations, user prompts, and user classes including name and access privileges of each user class. The guide will also contain information on all standard Report Studio reports that were developed against the TCJ8-maintained TWCF data marts. Documentation shall provide all business rules, assumptions, allocation methods, data sources as well as any other pertinent information needed.

1.4.7.10 Task Area 7, Subtask 10. Cognos PowerCube Documentation

The Contractor shall provide documentation and assistance for users of Cognos PowerPlay and Analysis Studio. The Contractor shall maintain a user guide and documentation containing information on the current application design of each Cognos PowerCube and the supporting data marts and database views created under this task. This report shall include dimension maps, table record layouts, field/dimension/measure definitions, and descriptions of the cube update processes.

Documentation shall provide all business rules, assumptions, allocation methods, data sources as well as any other pertinent information needed.

1.4.7.11 Task Area 7, Subtask 11. DCD Guidance and Documentation The Contractor shall provide documentation and assistance to users of all DCD PowerCubes, reports, logs, reference tables, and DCD Microsoft Access database interfaces. Furthermore, existing DCD user’s guides shall be maintained to reflect current documentation for using DCD cubes, reports, Microsoft Access interfaces, data dictionary, and logs.

Contractor performance in this task area will be evaluated based of technical expertise of personnel supporting this effort and financial analyst’s feedback regarding the responsiveness, completeness and usefulness of work products provided in meeting their taskings/demands and overall satisfaction they meet or exceed the needs of the analysts in performing their assigned responsibilities.

1.4.7.12 Task Area 7, Subtask 12. System Reconciliations and TWCF Data Mart Expansion.

The contractor shall develop and implement reconciliations and other required IGC Teradata database views which will present integrated TWCF revenue/cost and workload data.

The contractor shall develop Cognos Framework Manager Metadata models that hide the complexity of the underlying reconciliations and other TWCF data sets, and then publish major portions of these models as packages out on the IGC Cognos Web server. These packages will provide authorized IGC users Web-based, self-service access to reconciliation data sets, as well as other integrated TWCF revenue and cost transactions by line of business.

The contractor shall develop a set of SDDC TFMS Cognos revenue and expense PowerCube models for each line of business. The cubes generated from these models will provide HQ DLA, SDDC, and USTRANSCOM analysts a summary-level multidimensional analysis and reporting capability.

The contractor shall develop a set of Cognos Report -based reports that provide the results of reconciliations. Each report shall prompt the user for the applicable query filters (e.g., time period) prior to report execution.

1.4.8 Task Area 8, Information Technology Systems - FISCAM Team Support

The contractor shall review the designated critical feeder and core accounting systems which collectively create the accounting statements that will be subject to Financial Audit. The main objective is to ensure each of these systems is fully compliant with FISCAM standards. This compliance ensures our systems are reliably and accurately capturing and processing the correct financial information. The contractor shall require close coordination with the TCJ8 Financial Statements Project Team; TCJ6; TCJ3; the SMEs and other DoD/Services who own or manage the various systems USTRANSCOM relies on to produce financial information.

The contractor shall perform assessments of USTRANSCOM-owned systems material to the USTRANSCOM financial statements using the FISCAM to perform annual sustainment testing on an agreed upon schedule with the Government. The contractor shall provide review of test results, deficiency report, and corrective action plans (CAP).

If the Government does not concur with a proposed CAP, the contractor shall review functional SME proposed mitigations, requests for risk acceptance, the rationale provided for stated residual risk assessments for IT systems, and provide evaluation results within 5 business days of Government written request to verify awareness of or risk acceptance by USTRANSCOM leadership.

The contractor shall provide support in reviewing and mapping the Statement on Standards for Attestation Engagements 16/18 reports, reviewing and testing Complimentary User Entity Controls (CUECs), and providing the test results.