training symposium fpds tips - office of the under ... training presentations...– all orders, bpa...
TRANSCRIPT
Presented by: G. Brian Davidson Consultant to DPAP PDI
PROCURE-TO-PAY DEFENSE PROCUREMENT AND ACQUISITION POLICY
TRAINING SYMPOSIUM
May 30 – June 1, 2017 ● Hyatt Regency Orlando FL 1
Standard and Ad Hoc Reports
FPDS Tips
What is FPDS-NG?
2017 Procure-to-Pay Training Symposium 2
• Collects federal procurement awards • Is the Authoritative source • Receives Data from
– Contract writing system (CWS) – User Entry using FPDS Web Portal
• FPDS Web Access - https://www.fpds.gov
FPDS EXPERT – Walks on Water
2017 Procure-to-Pay Training Symposium 3
FPDS Expert Has to Know How to Walk on Water to Create Reports. Create the Right Conditions for Accurate Report.
We Walk on Water all the Time. GO PENS!
FPDS EXPERT – Walks on Water • Must know the FPDS Database
– Contains Data for ALL of Federal Government from 1978 • Selects Which Part of FPDS that You Want, i.e. DOD, FY
– Contains ‘Procurement’ and ‘Other Transaction’ Data • Selection of METRICS Field Determines Which Data to Use • MUST select One Metric or NO Summarization Occurs
– Must Have DOD Userid to Access Last 90 Days of Data – DOD Userid is ONLY Created by an Existing DOD User – Place of Performance Is Not on IDV, Just Orders – IDVs are the Parent Document for Orders & BPA Calls – MOD ZERO is the Parent Document to All MODS
2017 Procure-to-Pay Training Symposium 4
FPDS EXPERT – Walks on Water • Must know the FPDS Database (continued)
– Contains Both IDVs and AWARDS – DOD Money is Only on AWARDS – DOD IDVs Have NO Dollars Obligated – Civilian IDVs May Have Dollars Obligated
• Types of Award/IDV CARS – AWARDS are BPA Calls, DCA, Delivery Orders, and PO – IDVs are BPA(A), BOA(G), or IDC(D) for DOD
• Civilian Agencies Also Have GWAC or FSS IDVs – BPA Calls are Under a BPA(A) – Delivery Orders are Under a BOA(G) or IDC(D)
2017 Procure-to-Pay Training Symposium 5
FPDS EXPERT – Walks on Water • Must know the FPDS Database (continued)
– Express CAR report is One Record for Many Orders • All Orders in One Month under One Contract to One Vendor • Totals the Number of Actions and Dollars Obligated
– Transaction Info Is on Every CAR • ‘AWARD or IDV TYPE’ - Selects CAR by Type of Award • Who Prepared, Approved, Last Modified and When
– Must Translate User Request to FPDS Data • Are Orders Counted as Contracts, then Ignore IDVs? • Are New Contracts DCA, PO, IDVs, then Ignore Orders? • Do I need Zero Dollar Mods (non-ZERO Mods)? • Will Express CARs Distort Analysis?
2017 Procure-to-Pay Training Symposium 6
FPDS EXPERT – Walks on Water • Must know the FPDS Database (continued)
– All Orders, BPA Calls Have Parent IDV Data Attached – IDVs have Base and All Options Value on MOD ZERO – Awards have Base & All Options Value on MOD ZERO – ‘NON MOD ZERO’ CARS ONLY Have Changes that
Increase/Decrease the Options Fields Value, or $0.00 – ALWAYS Use FY & Number of Records to Check AdHoc – Maximum 5,000 Rows into EXCEL, HTML, or PDF – Maximum Export 30,000 Rows of CSV Data (TXT file)
• Import into EXCEL or ACCESS or any Compatible Software – IDV PIID, PIID, Mod Num, & Trans Num for EVERY Rec
2017 Procure-to-Pay Training Symposium 7
FPDS EXPERT – Walks on Water • Validate Ad Hoc Report Using Another Method
– Check your Work, Compare to EZSearch Results – Check your Work, Compare to Standard Reports Output – Match Numbers Exactly; ‘Close’ Is Not Good Enough
• Know the Data in FPDS Fields – Experience and FPDS Data Dictionary – Total Dollar Fields are WRONG for 3 or more CARS – Previous Dollar Fields are WRONG for 3 or more CARS – FPDS Sees All Mods, Sums All, Puts Same Total on All Mods;
Subtracts Current Value to get Previous Value – DO NOT USE Contractor Name; Use Vendor Name
2017 Procure-to-Pay Training Symposium 8
2017 Procure-to-Pay Training Symposium 9
Frequently Used Standard Reports • What is Data is Available on one Contract?
– WHAT – Contract Detail Report
• What Products or Services am I Buying? – WHAT – Total Actions by PSC or NAICS – % commercial – Drill to workflow to show PSC and Business Size
• Total Actions and Dollars for all business sizes • Actions, Dollars & Percent for SB, SDB, 8(a), SDVOSB, etc.
• What are the Total Awards? – WHAT – Federal Contract Actions and Dollars Report
2017 Procure-to-Pay Training Symposium 10
Frequently Used Standard Reports • How Am I Doing on Small Business Goals?
– HOW – SB Achievements by Awarding Activity Report
• Education, HBCU, and MI Are NOT on SB Report – Education is not Part of the SBA Goaling Report – WHO – Awards by Contractor Type
• How Am I Doing in Competition Goals?
– HOW – Competition Report – HOW – Competition Report – OLD
2017 Procure-to-Pay Training Symposium 11
Standard Report Has Workflows 1 Ran Total Actions by PSC
for my DODAAC
2 Output is Commercial Procedures vs Total Dollars for
major PSC category
3 Other report formats are in Workflow
2017 Procure-to-Pay Training Symposium 12
Standard Report - Workflows
1 Click on Workflow; window opens with any
existing workflows
2 Click on PSC Socio Economic
Report
2017 Procure-to-Pay Training Symposium 13
Standard Report - Workflows 1 New PSC report format
showing awards by PSC to each business category
2 Export to EXCEL for better analysis capability
2017 Procure-to-Pay Training Symposium
14
Understanding Standard Reports • How Do I Know What is the Selection Criteria?
– Top Left – Click on ‘i’ Beside ACTIVE FILTERS
• How Was a Given Column Calculated from Data? – Between Workflow and SIGMA – Click Header + Footer – Appendix Describes Each Column and any Exclusions
• How Do I Email the Report to Someone? – Click on SEND – email window opens – Type email address separated by commas – no spaces – Send only as an EXCEL or CSV File Attachment – Click on SEND at bottom of the email window
2017 Procure-to-Pay Training Symposium
15
Understanding Standard Reports • Save Selection Parameters for Future Use
– Setup a Report to Run the Last 5 Fiscal Years and SAVE • Drill to Fiscal Year, Agency, Commands, and Offices • Award Detail Drill Down on All Reports
– Most Fields on the CAR are available – Limit is 5,000 Rows of EXCEL, PDF, or HTML Output – 30,000 Row of CSV Output can be Saved or Emailed
• Limited to 5 Years of Data per Report • Has a Report Appendix
– Shows Selection Criteria and Data Excluded from Report – Describes How Each Column is Calculated
2017 Procure-to-Pay Training Symposium 16
Understanding Standard Reports • How Do I Export the Report to My Desktop?
– Click on EXPORT – export window opens – Four File Types are Available
• EXCEL, HTML, or PDF 5,000 Row Limit • CSV (comma separated values) has 30,000 Row Limit
– EXCEL • Regular Formatting or Pivot Table • EXCEL Analysis Available
– HTML • No Analysis Tools; Ready for Web Page Placement
– CSV – Import into Other Software for Analysis – PDF –SELECT PDF to Print & use 100% of Normal Size – Click on EXPORT at bottom of the export window
2017 Procure-to-Pay Training Symposium 18
Ad Hoc – Steps to Create or Review
1 Select Number of Records, Actions, Dollars
2 Select Fields to Output
3 Setup Filter Criteria
4 Format Output or Create Grand Total
5 SAVE and then Execute
2017 Procure-to-Pay Training Symposium 19
Ad Hoc – Step 1 Select Metrics • Step 1 – Click on ‘1 Select Metrics’
– Click PLUS sign (+) to Expand Data Categories • Award – IDV Information Folder OR • Other Transaction (OT) Information Folder
– Click ‘Contract Actions’ or Dollar Values Folder for Fields
– Select Field for Output by Clicking on ADD in Same Line
– Select ‘Number of Records’ first, then ‘Actions’ – Select ‘Dollar Values’ folder, then ‘Action Obligation’ – Change Field Order - Highlight Field, up/down arrow – Delete Field - Highlight Field, X box
• Save Report Frequently – Supply a ‘Report Name’; Click SAVE
2017 Procure-to-Pay Training Symposium 20
Ad Hoc – Save Work Frequently
1 Give the Report a Name
2 Click SAVE
3 Highlight field; Move Up or Down or Delete
from Output
2017 Procure-to-Pay Training Symposium 21
Ad Hoc – Select Field for Output; Where is it?
1 Awarding Agency, Command, Office
2 Contract, Order, Mod Nums
3 FY, Dates
4 Funded Dept ID
5 PSC, NAICS
6 Comp, Fair Opp, Set Aside, Sole Source
7 CAR Type, Who Prepared or Modified it
8 For Orders - IDV info, M/S, IDV Type
2017 Procure-to-Pay Training Symposium 22
Ad Hoc – Select Filters 2 Window opens with values, Select 8(a) Sole Source or what you want
1 Click Select Attribute Values to see Values in field
3 Click OK
5 Click ADD
4 ‘8(a) Sole Source’ would appear here. Repeat 1 – 4, if more values needed
2017 Procure-to-Pay Training Symposium 23
Ad Hoc – Select Filter (continued)
1 Click Metric Filter
5 Check box to apply to every CAR!
2 Now only Actions and Action Obligation are available for a filter
Use Dollar filter to find awards over $100,000
3 Select operation ‘Only Values Greater than’
4 Manually enter 100000
6 Click ADD
2017 Procure-to-Pay Training Symposium 24
Ad Hoc – Add Filter (continued)
1 If Field Name is not in
pull-down for filter, if it was not selected for output
4 Click category to get field names. Click SELECT to use in filter 2 Click Here
3 Window opens with All fields to select for filter
2017 Procure-to-Pay Training Symposium 25
Ad Hoc – Advanced Filter - and/or
1 Click Advanced to get and/or and ( )
2 Select and/or and ( ) as needed
3 ADV = SQL coding to filter
2017 Procure-to-Pay Training Symposium 26
Ad Hoc – Step 5 Execute
2 Save report
3 Execute
1 Review report filters – any missing?
2017 Procure-to-Pay Training Symposium 27
Ad Hoc – Date Range • Decide Date to Use in Selection
– Date Signed • Produces Report based upon Award Date Signed
– Do NOT Use Both Date Pairs • Using Both Dates Will Result in Less Records
– Last Modified Date • Produces Report based upon Last Modified Date • Ignores Where the CAR falls into FY Based upon Date Signed • A Correction Today can change 2013, 2014, 2015 Recs
2017 Procure-to-Pay Training Symposium 28
Ad Hoc – Date Range & Display Report
1 Date Range for CAR Date Signed –
one year maximum
3 Click Display Report to run
2 Date Range for Last Modified Date – five years maximum
FPDS Reporting Future
2017 Procure-to-Pay Training Symposium 29
• Last Time to see Most of these Slides • New Software for FPDS Reports in FY18 • Both Standard and Ad Hoc Reports Affected • Volunteer to Beta Test Reports Software
– Testing may start before the end of the fiscal year, but more likely in FY18
• MicroStrategy, Inc. is the Vendor • Business Intelligence (BI) Software Provider