Pages

  • Home
  • About
  • Download
  • Tutorials

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.

14 comments:

  1. From Suhail.K,

    Nice and valuable information. Is it possible to upload excel spreadsheet that contains above information also?

    Regards K.Suhail

    ReplyDelete
  2. @Anonymous

    Hi Suhail.K,

    As requested, I have posted an excel template for procurement progress calculation procedure for easy reference. I hope you will enjoy it.

    ReplyDelete
    Replies
    1. Hi!
      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)

      Where can we find 'Progress Curve' please?

      Delete
    2. Hi,

      To find the Progress Curve,

      Download the excel template from the link;
      https://sites.google.com/site/toolbox4planning/home/progress-calculation/procurement-progress-calculation

      Then, click on the progress data under Progress Curve tag where you can learn how the details progress data is obtained.

      Delete
  3. Worth Place for Planners....I love it

    ReplyDelete
  4. Please send me excel template for procurement progress calculation procedure phemmywuyi07@yahoo.com

    ReplyDelete
    Replies
    1. Hi Min,
      Great piece of information, but unfortunately none of templates are available for download.
      Would you mind forwarding them to sujith.pratap@gmail.com?

      Many thanks,
      Sujith

      Delete
    2. Dear Sujith,
      Glad that information is useful. It is downloadable link. Click on the downward arrow button (blue color) link.Here is a simple instruction to download file from this site

      Delete
  5. Hello Min Oo, am new to this site. Concerning the procurement progress calculation, I don't know how you got table 3 above. The other steps were clear. Even after downloading the template i could not unravel the expression...=SUMIF('Procurement Progress Sheet'!$F$7:$N$22,'Progress Curve'!C2,'Procurement Progress Sheet'!$F$27:$N$42)

    ReplyDelete
  6. HI Min,
    Pls explain how you got the value in table 3 using the other datasheet pls because SUMIF structure is: =SUMIF(Range, Criteria, Sum Range). Where is your criteria? Thanks.

    Again to plot the actual progress chart, what should be plotted against time from the procurement actual progress calculation sheet. Thanks

    ReplyDelete
  7. Hello,
    This site is really great.

    ReplyDelete
  8. Pls explain to us hw you got table 3 thanks

    ReplyDelete
  9. great post and i realy enjoyed it.

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...