How to Develop Procurement Progress Calculation Sheet Using Excel

In EPC project, procurement progress calculation sheet is required to monitor the procurement status. Once the procurement phase commences, the status is tracked and monitored closely. Followings are a guide to develop and update procurement progress data.

Steps to develop Procurement Progress Data
1) Understand the terms and confirm procurement work steps and weight factors for equipment work category

• Not Yet Start -0%
• Inquiry Issued to Bidders -10%
• Tender Evaluation and Client Approval -5%
• Purchase Order Placement to Vendor -10%
• Received Key Vendor Drawings -5%
• Approved Key Vendor Drawings -5%
• Fabrication (50%)
• Final Inspection Finished -5%
• Ex-Work -5%
• Delivery at Site -5%

Note: Weight factor (50%) of fabrication work step shall be multiplied with the actual progress data from vendor’s progress report to determine actual weight progress.

2) Confirm procurement work steps and weight factors for material work category

• Not Yet Start -0%
• Inquiry Issued to Bidders -10%
• Tender Evaluation and Client Approval -5%
• Purchase Order Placement to Vendor -10%
• Fabrication (60%)
• Final Inspection Finished -5%
• Ex-Work -5%
• Delivery at Site -5%

Note: Weight factor (60%) of fabrication work step shall be prorated based on the duration between purchase order issued date and final inspection date. Procurement work step groupings and weight factor percentage may be different from your project team’s preferences.

3) Confirm planned procurement items (if possible, list down the each equipment number) and Planned Purchase order amount.

Lets' make an assumption that the following are planned procurement items and planned purchase order amount.

• Pipe & fittings –CS [$15,000]
• Pipe & fittings-SS304 [$25,000] 
• Pipe & fittings-Special items [$10,000]
• Shell & Tube Heat Exchangers (AHE-105, AHE-106) [$40,000]
• Columns & Vessel (ACD-140, ACD-145) [$28,000]
• Reactors (ACR-10, ACR-15) [$18,000]
• Pump AHP-230 A/B/C [$20,000]
• Pump ACP-450 A/B [$15,000] 
• Compressor AHC-235 A/B [$ 27,000]
• Compressor EHC-215 A/B [$ 20,000]
• Control valves [$12,000]
• Instrument cables [$35,000]
• Safety Relief Valve (PRV) [$ 13,000]
• Orifice Plates and flanges [$7,000]
• Electrical Cables [$ 35,000]
• Pressure/Temperature Transmitters [$20,000]

A better forecast plan can be produced when actual purchase order amounts and the revised schedules from vendor are available.

4) Secure the schedule of each procurement work step for each equipment or equipment group based on that particular requisition number.

5) Calculate the weighting for each requisition. Example for Pipe & fittings –CS; weighting = Planned PO amount of pipe & fitting-CS/ Total Project PO amount which is 15000/340,000 =0.04

6) Enter the data of step 1, 2, 3, 4 and 5 to get procurement progress calculation sheet as shown in table-1. Make sure you enter week ending date as the progress data will be shown weekly basic. For example; take the weekending date as 20 Feb 2011 when Plan PO placement date is 18 Feb 2011.

7) Calculate and enter weighted progress data for each requisition number as shown in table-2.
Example for Pipe & fittings-CS; Weighted Progress for Inquiry issued to Bidders = Procurement Work Step Weight factors (Materials)*Weighting which is 10% *0.04=0.44%
The total weighted progress should be 100% as work step weigh factor and weighting of each requisition are based on 100% (1). Check the formula again if you couldn't get 100%.

8) Calculate periodic plan and cumulative plan progress data using Table-1 Schedule data and Table-2 weighted progress data. For example; periodic progress data for week ending 10 Oct 2010 =SUMIF('Procurement Progress Sheet'!$F$7:$N$22,'Progress Curve'!C2,'Procurement Progress Sheet'!$F$27:$N$42)
9) And then, you should get the plan progress data as shown in Table-3.

10) Plot the periodic and cumulative progress chart as shown in procurement progress chart.

How to update Actual Progress

1) Add the procurement work step codes for equipment work category in Table-4; E0 - Not yet Start, E1 - Inquiry Issued to Vendors, E2 - Tender Evaluation and Client Approval , E3 - Purchase Order Placement to Vendor , E4 - Received Key Vendor Drawings , E5 - Approved Key Vendor Drawings , E6 - Fabrication, E7 - Final Inspection Finished , E8 - Ex-Work , E9 - Delivery at Site. Enter the work step codes for material work category; M0 - Not Yet Start, M1 - Inquiry Issued to Vendors, M2 - Tender Evaluation and Client Approval , M3 - Purchase Order Placement to Vendor , M4 - Fabrication, M5 - Final Inspection Finished , M6 - Ex-Work , M7 - Delivery at Site.

2) Insert the additional columns such as Work step codes,Cum Weight factor (Work Step), Weight factor (Fab Step), Overall % Complete, Individual % Complete for actual progress calculation. See Table-4.

• Work step codes are entered when the actual work steps are done. Once the work step code is entered the cum weight factor for work steps is updated automatically using H LOOK UP function. 
• Individual % Complete is derived from the cum weight factor (work step) + weight factor (fabrication step) * fabrication progress. The actual fabrication progress data shall be received from vendor's progress report. 
• Overall % Complete data is derived from the sum of each requisition's weightage * individual % complete.

3) Finally, insert the actual progress data in Table-3 and plot the actual progress chart.

Download a sample template for the procurement progress calculation sheet to learn more.