Intelligent water conservancy EPC project benefit dynamic evaluation model and Excel application method

Abstract

The invention is suitable for the technical field of water conservancy informatization engineering project management, and provides an intelligent water conservancy EPC project benefit dynamic evaluation model and an Exce l application method, and the model comprises an EPC project total value dynamic value model, an EPC subpackage value model, a leading unit project surplus value model, a leading unit project profit model and a stage profit early warning model. Rich index type and coefficient type dimensionless influence factors are adopted, an EPC project comprehensive management and control model penetrating through all stages and all subentry projects of the whole period of EPC project construction is constructed, and dynamic evaluation, decision making and management and control bases are provided for the whole process of bidding, subpackaging and changing to settlement auditing for EPC project construction; the model can be embedded into an existing project budget Exce l report system in the water conservancy industry, dynamic data linkage analysis is supported, and the management efficiency and decision scientificity of an EPC project are remarkably improved.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of water conservancy informatization project management, in particular to a wisdom water conservancy EPC project benefit dynamic evaluation model and an Excel application method. BACKGROUND

[0002] The wisdom water conservancy project has a wide business field, including wisdom irrigation area, flood control four pre, digital twin engineering, digital twin basin, digital twin water network, etc. The construction content covers civil construction, mechanical and electrical installation and informatization construction. Due to the characteristics of short construction period and wide range of required engineering technology, the EPC construction management mode is often adopted. After winning the bid, the leading construction unit mainly undertakes the design and procurement work. Generally, the civil construction, mechanical and electrical installation and part of the informatization construction will be sub-contracted to qualified units for implementation through professional sub-contracting. The EPC project has a shallow design depth in the early stage, the informatization part demand is not clear, and the project benefit risk is relatively high. At present, the quota method static report segmented evaluation is adopted, and the quantitative processing (such as stage sensitivity) of the differences in professional characteristics of civil construction, equipment manufacturing and installation, and informatization engineering is lacking. The dynamic closed loop covering "bidding-construction-settlement" has not been formed, resulting in lagging project benefit risk early warning.

[0003] Therefore, in view of the above status, it is urgent to provide a wisdom water conservancy EPC project benefit dynamic evaluation model and an Excel application method to overcome the deficiencies in current actual application. SUMMARY

[0004] The purpose of the present application is to provide a wisdom water conservancy EPC project benefit dynamic evaluation model and an Excel application method to effectively solve the problems in the background art.

[0005] The present application is realized as follows: the wisdom water conservancy EPC project benefit dynamic evaluation model comprises:

[0006] An EPC project total value dynamic value model is used to decompose the total budget price of the project into the initial static values of four sub-projects of design procurement, civil construction, equipment manufacturing and installation, and informatization, and to dynamically adjust them through progress coefficient, bidding discount adjustment coefficient, design change adjustment coefficient, settlement audit adjustment coefficient, and force majeure risk probability;

[0007] An EPC sub-contract value model is used to adjust the initial static values of civil construction, equipment manufacturing and installation, and informatization engineering in terms of progress, risk and sub-contract coefficient;

[0008] A leading unit project residual value model is used to calculate the difference between the EPC project total value and the sub-contract value;

[0009] The leading unit project profit model is used for calculating the project profit after the value-added tax difference adjustment of the residual value and the deduction of financial cost;

[0010] The stage profit early warning model is used for sensitivity evaluation and early warning of the tender preferential coefficient, the design change coefficient and the audit settlement coefficient.

[0011] As a further scheme of the application, the formula of the EPC project total value dynamic value model is:

[0012] V EPC =[(EP*P EP ) (1-r) +(CE*P CE ) (1-r) +(ME*P ME ) (1-r) +(IT*P IT ) (1-r) ]*(m1 k1 *m2 k2 *m3 k3 );

[0013] In the formula, V EPC is the total value of the project, EP, CE, ME and IT are respectively the initial values of the design procurement, civil construction, equipment manufacturing and installation and informationization of the construction scheme budget; P EP , P CE , P ME and P IT are respectively the corresponding progress coefficients of the project design procurement, civil construction, equipment manufacturing and installation and informationization; r is the probability of the force majeure risk such as war, natural disaster and international financial risk; m1, m2 and m3 are respectively the tender preferential adjustment coefficient, the design change adjustment coefficient and the audit settlement adjustment coefficient, and k1, k2 and k3 are respectively the stage weight index of tender, change and settlement.

[0014] As a further scheme of the application, the formula of the EPC sub-contract value model is:

[0015] C SUB =d CE *(CE*P CE ) (1-r) *m1 k1 +d ME *(ME*P ME ) (1-r) *d IT (m1 k1 *m2 k2 )+(IT*P IT ) (1-r)

[0016] *(m1 k1 *m2 k2 *m3 k3 );

[0017] In the formula: C SUB For the value of professional subcontracting, d CE d ME and d IT These are the subcontracting coefficients for the professional subcontracting parts of civil engineering, equipment manufacturing and installation, and information technology engineering, respectively.

[0018] As a further aspect of the present invention: the formula for the residual value model of the leading unit project is:

[0019] V EP =V EPC -C SUB ;

[0020] In the formula: V EP The residual value of the design, procurement, and other construction work carried out by the lead contractor.

[0021] As a further aspect of the present invention: the formula for the project profit model of the leading unit is:

[0022] P EPC =V EP +ΔT-F;

[0023] In the formula: P EPC ΔT represents the project's pre-tax profit of the leading contractor, ΔT represents the difference between the project's total input VAT and total output VAT, and F represents the leading contractor's total financial costs excluding professional subcontracting costs.

[0024] As a further aspect of the present invention: the stage profit early warning model calculates the sensitivity coefficients of the bidding discount coefficient, design change coefficient, and audit settlement coefficient, and triggers an alarm when the early warning threshold is reached.

[0025] Based on the above model, this method of applying Excel constructs five vertical levels and four functional areas in an Excel spreadsheet:

[0026] The five vertical levels are divided into the regulatory factor layer, the value creation layer, the risk warning layer, the visualization layer, and the evaluation model layer;

[0027] The four functional areas are: initialization parameter area, calculation link area, graphical display area, and model verification area;

[0028] Dynamic linking is used to calculate and display the total value of EPC projects, subcontract value, general contractor share, and general contractor profit in real time.

[0029] As a further aspect of the present invention: the graphical display area includes a stage line chart, a dual-drive pie chart, and a panoramic bar chart, which are used to display the summary data of each stage, the composition of project value and subcontract value, and the main data values ​​of each stage, respectively.

[0030] As a further aspect of the present invention: the initialization parameter area is used to input the probability of force majeure risk, the bid discount adjustment coefficient, the design change adjustment coefficient, the audit settlement adjustment coefficient, and the subcontracting coefficient.

[0031] As a further aspect of the present invention: the method supports dynamic data linkage analysis and dynamic evaluation of the entire project process by embedding the existing project budget Excel report system in the water conservancy industry.

[0032] Compared with the prior art, the beneficial effects of the present invention are as follows:

[0033] By employing a rich array of dimensionless influencing factors, including both exponential and coefficient types, and integrating the complete time dimension of water conservancy informatization projects—from the preliminary stage, bidding stage, construction stage, and final settlement stage—with the engineering entity dimension of design and procurement, civil engineering, equipment manufacturing and installation engineering, and informatization engineering, a comprehensive EPC project management and control model is constructed that spans all stages and sub-projects of the entire EPC project construction cycle. This model provides a basis for dynamic evaluation, decision-making, and control of EPC project construction throughout the entire process, from bidding, subcontracting, changes to settlement auditing. Attached Figure Description

[0034] To more clearly illustrate the specific embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the specific embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For those skilled in the art, other drawings can be obtained from these drawings without creative effort.

[0035] Figure 1 This is a framework diagram of the Excel application method of the present invention.

[0036] In the attached diagram: 1-Initialization parameter area, 2-Calculation link area, 3-Graphical display area, 4-Model verification area. Detailed Implementation

[0037] The technical solution of the present invention will now be clearly and completely described with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0038] The present invention will be further explained below with reference to specific embodiments.

[0039] Referring to Figure 1 , the embodiment of the present application provides a smart water conservancy EPC project benefit dynamic evaluation model, comprising:

[0040] The EPC project total value dynamic value model is used for decomposing the total budget price of the project into initial static values of four sub-item projects of design procurement, civil construction, equipment manufacturing and installation and informatization, and dynamically adjusting through progress coefficients, bidding discount adjustment coefficients, design change adjustment coefficients, settlement audit adjustment coefficients and force majeure risk probability.

[0041] The EPC subcontract value model is used for progress adjustment, risk adjustment and subcontract coefficient progressive adjustment of the initial static values of the civil construction, equipment manufacturing and installation and informatization projects.

[0042] The leading unit project residual value model is used for calculating the difference between the EPC project total value and the subcontract value.

[0043] The leading unit project profit model is used for calculating the project profit after value-added tax difference adjustment and deduction of financial cost of the residual value.

[0044] The stage profit early warning model is used for sensitivity evaluation and early warning of the bidding discount coefficient, the design change coefficient and the audit settlement coefficient.

[0045] The following further analyzes the construction of the above model and the Excel application method of the model:

[0046] Firstly, the EPC project total value dynamic value model is constructed. The total budget price (or bidding limit price) of the project is decomposed, and the initial static values of four sub-item projects of design procurement, civil construction, equipment manufacturing and installation and informatization are determined one by one. The progress coefficient, the bidding discount adjustment coefficient, the design change adjustment coefficient and the settlement audit adjustment coefficient of the sub-item project are matched in the form of coefficient, and the force majeure risk probability and the weight coefficients of the bidding discount adjustment coefficient, the design change adjustment coefficient and the settlement audit adjustment coefficient are matched in the form of index.

[0047] V EPC = [(EP*P EP ) (1-r) +(CE*P CE ) (1-r) +(ME*P ME ) (1-r) +(IT*P IT ) (1-r) ]*(m1 k1 *m2 k2 *m3 k3 )

[0048] In the formula, VEPC Total value of the project; EP, CE, ME, IT are the initial values of the design procurement, civil construction, equipment manufacturing and installation, and informationization, respectively; P EP , P CE , P ME , P IT are the corresponding progress coefficients of the project design procurement, civil construction, equipment manufacturing and installation, and informationization, respectively; r is the probability of force majeure risks such as war, natural disasters, and international financial risks; m1, m2, m3 are the bid discount adjustment coefficient, design change adjustment coefficient, and audit settlement adjustment coefficient, respectively; k1, k2, k3 are the stage weight indexes of bidding, change, and settlement. Except for the value parameters using the legal currency unit, the others are dimensionless parameters, with a value range of 0-1.

[0049] Further, the initial values of m1, m2, and m3 are all 1, m1 is determined after the project bidding, m2 is adjusted after each design change, and m3 is determined after the audit settlement; the initial values of k1, k2, and k3 are all 1, which can be valued by expert scoring method or experience method according to the actual situation of the project; the initial value of r is 0, which can be evaluated comprehensively according to the industry API, the location of the project, and the implementation season; the expected progress coefficient or the actual progress coefficient can be used according to the demand of each sub-project.

[0050] Secondly, the EPC sub-contracting value model is constructed. After the initial static values of civil construction, equipment manufacturing and installation, and informationization engineering are adjusted by progress and risk, the sub-contracting coefficients are adjusted in turn: civil construction only considers the bid discount adjustment coefficient, equipment manufacturing and installation considers the bid discount adjustment coefficient and the design change adjustment coefficient during construction, and informationization engineering considers the bid discount adjustment coefficient, the design change adjustment coefficient during construction, and the settlement audit adjustment coefficient.

[0051] C SUB = d CE *(CE*P CE ) (1-r) *m1 k1 +d ME *(ME*P ME ) (1-r) *d IT (m1 k1 *m2 k2 )+(IT*P IT ) (1-r) *(m1 k1 *m2 k2 *m3 k3 )

[0052] In the formula: C SUB is the professional sub-contracting value, dCE , d ME , d IT are the subcontracting coefficients of civil construction, equipment manufacturing and installation, and information engineering, respectively, and are dimensionless.

[0053] Further: because civil construction is the basis of engineering, it is the basis guarantee of project progress and quality, d CE value can be taken as a high value; because the equipment profit margin is high, d ME value can be taken as a low value; because the information project construction content changes, in order to control the subcontracting cost and rework risk, d IT value can be taken as no more than 80% of the audit settlement value.

[0054] Thirdly, the remaining value model of the leading unit project is constructed, that is, the total value of the EPC project is dynamically subtracted from the EPC subcontracting value.

[0055] V EP = V EPC -C SUB

[0056] In the formula: V EP is the remaining value of the construction leading unit design procurement and other construction work.

[0057] Preferably, the percentage share of the leading unit can be calculated according to the remaining value of the project.

[0058] Fourthly, the project profit model of the leading unit is constructed, that is, the remaining value of the leading unit project is adjusted by the value-added tax difference, and then the full financial cost of the construction leading unit except for the professional subcontracting cost is subtracted.

[0059] P EPC = V EP + ΔT-F

[0060] In the formula: P EPC is the project pre-tax profit of the construction leading unit, ΔT is the difference between the total input tax and the total output tax of the project, and F is the full financial cost of the construction leading unit except for the professional subcontracting cost.

[0061] Preferably, the project profit rate can be further calculated according to the project profit.

[0062] Fifthly, the stage profit early warning model is constructed. The sensitivity evaluation and early warning are respectively carried out on the tender discount coefficient, the design change coefficient, and the audit settlement coefficient.

[0063]

[0064] S2 = k2 * m2 (k2-1) *m1 k1 * [(EP*PEP +CE*P CE +ME*P ME +IT*P IT )-d IT *IT*P IT ]*(1-r)

[0065] S3=k3*m3 (k3-1) m1 k1 *m2 k2 *[EP*P EP +CE*P CE +ME*P ME +IT*P IT -d IT *IT*P IT ]*(1-r)

[0066] S = S1*m1 + S2*m2 + S3*m3 i is the profit sensitivity coefficient of the i-th phase of the project. If it is greater than the pre-set warning threshold of the project, an alarm is triggered.

[0067] Step 6: Dynamic evaluation and phase control throughout the project. The typical basic steps are as follows:

[0068] (1) Project model initialization. According to the situation, construct the project evaluation model, and use the initial value of each coefficient.

[0069] (2) Bid evaluation and control. According to the budget estimate or bid price of the bidding project, use the project evaluation model to predict the project profit and calculate the bid discount coefficient m1. After winning the bid, determine m1 according to the actual situation.

[0070] (3) Subcontracting evaluation and control. Set the expected project profit, combine the subcontracting demand and supplier situation, and calculate each subcontracting coefficient one by one to serve as the professional subcontracting control index, with the goal of safe delivery of the project and reasonable profit. After signing the subcontracting contract, the actual subcontracting coefficient is brought into the model.

[0071] (4) Change evaluation and control. During the construction process of the project, for changes exceeding 1% of the EPC project budget price, the model can be used to evaluate the impact of the change on the project profit before issuing the design change, in order to select the optimal change scheme. After the change is determined, adjust the change coefficient m2 according to the actual situation.

[0072] (5) Schedule evaluation and control. During the construction process of the project, bring each progress coefficient into the model, compare the dynamic total value of the EPC project with the value of the subcontracting, and perform overall evaluation while checking whether there are unreasonable matters such as lagging behind in total package income or advance payment in subcontracting.

[0073] (6) Settlement evaluation and control. Input relevant data such as the audit and settlement audit drafts at each stage into the project evaluation model, calculate the project's profit and loss situation, and formulate corresponding audit defense strategies. After the settlement audit is completed, determine the actual audit settlement coefficient and conduct project benefit settlement evaluation.

[0074] Furthermore, the EPC contractor can accumulate relevant data according to project categories, use the algorithm of this invention to statistically analyze the stage coefficients of various projects, and establish an experience database of relevant parameters.

[0075] Step 7: Implementation of the Excel application algorithm. Since project construction plans, budgets, and audit settlements often utilize Excel spreadsheets, this invention provides a general Excel application algorithm for dynamic evaluation of the benefits of smart water conservancy EPC projects. This algorithm can be directly added to relevant project calculation spreadsheets for dynamic linking calculations, as shown in the attached diagram of the specification. The implementation method is as follows:

[0076] (1) As attached Figure 1 As shown: The form is vertically divided into five levels: control factor layer, value creation layer, risk warning layer, visualization display layer, and evaluation model layer; the form is also vertically divided into eight columns: level, serial number, project name, project pre-stage, bidding stage, construction stage, acceptance stage, and remarks; the form levels further divide each column into four functional areas: initialization parameter area 1, calculation link area 2, graphic display area 3, and model verification area 4.

[0077] (2) Except for the subcontracting coefficient (p), which is entered in the remarks column, other coefficients (r, m, k, d) are entered in the control factor layer; the value creation layer is used to calculate the total value V of the EPC project. EPC Subcontract value C SUB Total contract share V EP and general contractor profit P EPC The risk warning layer is used for sensitivity calculations; the visualization layer includes three basic display graphics: stage line charts, dual-drive pie charts, and panoramic bar charts, which are respectively associated with the summary data of each stage, the project value and subcontract value composition of the final acceptance stage, and the main data values ​​of each stage; the evaluation model layer provides model calculation text formulas for understanding and verifying table algorithms.

[0078] (3) Add a blank form to the project budget Excel calculation table, paste this calculation form, and associate the corresponding data in the project budget summary table with this form to realize the prediction of subsequent stages. After each stage is completed, update the actual project data to evaluate the benefits of the project at the current stage.

[0079] Embodiment 1: The construction content of a certain intelligent irrigation district project includes 34 straight opening transformation, automatic gate manufacturing and installation, 2 large gate automation transformation, centralized control center, 2 sub-centers, information system construction and other projects. The leading unit actively offers preferential treatment in the bidding, and maintains initiative in the construction change, audit and other stages, and finally the share ratio is 26.18%, the pre-tax profit rate of the project is about 21.46%, the sensitivity analysis reveals that the project risk gradually presents positive deviation according to the bidding, implementation and acceptance, and the project value is realized. The Excel calculation table is as follows:

[0080] Project name: Certain irrigation district 2019 agricultural water price reform measurement water facility construction project Amount: 1000000 yuan

[0081]

[0082] In summary, the present application adopts rich index type and coefficient type dimensionless influence factor, interweaves and fuses the complete time dimension of the water conservancy informatization project early stage, bidding stage, construction stage and completion settlement stage, and the engineering entity dimension of design procurement, civil construction engineering, equipment and safety engineering and information engineering, and constructs an EPC project comprehensive management and control model throughout the whole cycle of EPC project construction stages, each sub-item engineering, which provides dynamic evaluation, decision and control basis for EPC project construction from bidding, subcontracting, change to settlement audit whole process.

[0083] Finally, it should be pointed out that: the above embodiments are only used to illustrate the technical solutions of the present application, but not to limit them; although the present application has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that: it can still modify the technical solutions recorded in the foregoing embodiments, or make equivalent replacement to part or all of the technical features; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the scope of the technical solutions of the embodiments of the present application.

Claims

1. A dynamic benefit evaluation model for a smart water conservancy EPC project, characterized in that, The application relates to a method for dynamic evaluation of EPC project value, which comprises the following steps: An EPC project total value dynamic value model is used to divide the total budget price of a project into initial static values of four subprojects of design procurement, civil construction, equipment manufacturing and installation and informatization, and the initial static values are dynamically adjusted through progress coefficients, bidding discount adjustment coefficients, design change adjustment coefficients, settlement audit adjustment coefficients and force majeure risk probabilities; An EPC subcontract value model is used to adjust the initial static values of the civil construction, equipment manufacturing and installation and informatization projects through progress adjustment, risk adjustment and progressive adjustment of subcontract coefficients; An EPC project residual value model is used to calculate the difference between the total value of the EPC project and the subcontract value; An EPC project residual value model is used to calculate the difference between the total value of the EPC project and the subcontract value; A stage profit early warning model is used to sensitively evaluate and warn the bidding discount coefficient, the design change coefficient and the audit settlement coefficient.

2. The smart water conservancy EPC project benefit dynamic evaluation model according to claim 1, characterized in that, The formula of the EPC project total value dynamic value model is as follows: V EPC = [(EP*P EP ) (1-r) +(CE*P CE ) (1-r) +(ME*P ME ) (1-r) +(IT*P IT ) (1-r) ]*(m1 k1 *m2 k2 *m3 k3 ); In the formula: V EPC is the total value of the project; EP, CE, ME and IT are the initial values of the design procurement, civil construction, equipment manufacturing and installation, and information engineering, respectively; P EP , P CE , P ME and P IT are the corresponding progress coefficients of the project design procurement, civil construction, equipment manufacturing and installation, and information engineering, respectively; r is the probability of force majeure risks such as war, natural disasters, and international financial risks; m1, m2, and m3 are the bid discount adjustment coefficient, the design change adjustment coefficient, and the audit settlement adjustment coefficient, respectively; k1, k2, and k3 are the stage weight indexes of bidding, change, and settlement, respectively.

3. The smart water conservancy EPC project benefit dynamic evaluation model according to claim 2, characterized in that, The formula of the EPC subcontract value model is as follows: C SUB = d CE *(CE*P CE ) (1-r) *m1 k1 +d ME *(ME*P ME ) (1-r) *d IT (m1 k1 *m2 k2 )+(IT*P IT ) (1-r) *(m1 k1 *m2 k2 *m3 k3 ); In the formula: C SUB d is the professional sub-contracting value CE d ME and d IT are the sub-contracting coefficients of the civil construction, equipment manufacturing and installation, and information engineering professional sub-contracting parts, respectively.

4. The smart water conservancy EPC project benefit dynamic evaluation model according to claim 3, characterized in that, The formula of the EPC project residual value model is as follows: V EP = V EPC - C SUB ; In the formula: V EP The remaining value of the design, procurement and implementation of other construction work by the lead construction unit.

5. The smart water conservancy EPC project benefit dynamic evaluation model according to claim 4, characterized in that, The formula of the EPC project residual value model is as follows: P EPC = V EP + ΔT - F; where P EPC is the pre-tax profit of the construction leader, ΔT is the difference between the total input VAT and the total output VAT of the project, and F is the full financial cost of the construction leader excluding the cost of professional sub-contracting.

6. The smart water conservancy EPC project benefit dynamic evaluation model according to claim 1, characterized in that, The stage profit early warning model calculates the sensitivity coefficients of the bidding discount coefficient, the design change coefficient and the audit settlement coefficient, and alarms when the warning threshold is triggered.

7. Excel application method based on the model according to any one of claims 1 to 6, characterized in that, The method constructs five longitudinal levels and four functional areas in an Excel form: The five longitudinal levels are divided into a regulation factor layer, a value creation layer, a risk early warning layer, a visual display layer and an evaluation model layer; The four functional areas are an initialization parameter area, a calculation link area, a graphic display area and a model checking area; The real-time update and visual display of the EPC project total value, the subcontract value, the total package share and the total package profit are realized through dynamic link calculation.

8. The method of claim 7, wherein, The graphic display area comprises a stage line chart, a double-drive pie chart and a panoramic column chart, which are respectively used to display the stage summary data, the project value and the subcontract value composition and the main data values of each stage.

9. The method of claim 7, wherein, The initialization parameter area is used to input the force majeure risk probability, the bidding discount adjustment coefficient, the design change adjustment coefficient, the audit settlement adjustment coefficient and the subcontract coefficient.

10. The method of claim 7, wherein, The method supports dynamic data linkage analysis and project whole-process dynamic evaluation by embedding an existing project budget Excel report system in the water conservancy industry.

Images