5.5. What is the Process?

5.5.1. Create an abstract using the excel spreadsheet format provided as an attachment to this chapter. Lower control limit (LCL) and upper control limit (UCL) variances from the IGE are then developed and identified in the spreadsheet. These variances account for normal market price fluctuation. When selecting the variance percentages, you need to substantiate the reason for the limits selected. For example, if the requirement is for a one year contract for paving and the prices of the six competitors in the last competition were consistently within 8% of each other that could indicate that anything over 8% may be too high or too low (Note: The last competition data is only one data point for analysis).

5.5.2. After you have "constructed" your spreadsheet and computed the LCL and UCL, the next step is to stratify the abstract by dollar thresholds to make the results easier to interpret. A chart of 100 items ranging from $.03 to $3,000 per unit is unmanageable. The abstract may be stratified by using the Government estimate as the set point to establish ranges. When you are done you should have created a series of different Excel worksheets which will be used to stratify the abstract by dollar thresholds, e.g., <$3, $3.01-$10, $10.01-$50, etc. The graphs generated by the data can then be displayed in several charts allowing the Government to visually identify variances in the information provided. The number of graphs used to display the data will depend upon the number of stratifications, the number of offerors and the number of items under evaluation.

5.5.3. Once the charts are populated with the prices of all offerors, pick a data sheet and highlight the information. You will be able to create an Excel Chart from the information populated in the spreadsheet. Once created, the chart will reflect the IGE with the LCL and UCL variances and the prices of all offerors, exposing variances among the offerors. At a glance, you can now look for those specific CLINs that warrant further investigation. The latter items will become your primary focus areas for the rest of the analysis.