Application of Excel in Project Cost Management
-
Excel is a set of well-known spreadsheet software launched by Microsoft, which is popular all over the world and widely used in all fields of society. Government departments, world-renowned large companies, large enterprises, small factories and families are all using Excel. Excel is mainly used to manage, organize and process all kinds of data. The functional departments of state government organs can use it to handle daily office affairs, deliver various documents and print various forms; The management department of the company can use it to manage the production and sales plan and help the company leaders to complete the investment decision. The financial department can use it to analyze all kinds of data, display the results in the form of various statistical charts, and complete the production of various financial statements. Excel2000 is the latest 2000 edition of Microsoft, which has excellent functions in data calculation, text editing, graphic editing and typesetting output. In Excel2000, you can also use VBA(VisualBasic for Application) to program and calculate data tables. We should give full play to the excellent function of Excel2000 in project cost management.
A, using Excel2000 to prepare the quota unit valuation table.
Quota unit valuation table is mainly composed of words, numbers and tables. In particular, the relationship between the composition of the table and the calculation of the numbers in the table is complicated. When FoxPro database software or other large database software is used to program and calculate the valuation table, other typesetting software such as CCED2000, Huaguang typesetting and Founder typesetting are often used when typesetting and printing the calculated valuation table (printing format is required, and words are inverted with sulfuric acid paper). For example, CCED2000 is used to generate the national unified municipal engineering budget quota evaluation table compiled by Inner Mongolia Engineering Cost Management Office, Huaguang typesetting software is used to generate the national unified installation engineering budget quota evaluation table compiled by Ye Peng Software Company, and 200 1 construction machinery machine-team expense compilation system compiled by Tianjin Construction Engineering Quota Management Research Station is used to generate plates. Using Excel2000 spreadsheet software to program and calculate, we can solve the problems of text editing, data calculation, table typesetting and printout in the quota evaluation table at one time, so that the format we see on the computer is the printed format-what you see is what you get. It saves the trouble and mistakes caused by converting database files into typesetting format files and typesetting them by other methods. It ensures the high efficiency of work and the consistent accuracy of data.
You can use the function to calculate any data on the Quota Evaluation Table compiled by Excel2000 spreadsheet, move the quota subhead as a whole, insert or delete any subhead, any material or machinery, and add or subtract work contents and table comments. With the powerful table typesetting function of Excel2000, you can edit the layout required by the project at will.
After programming with Excel2000, as long as the budget price of labor, materials and machinery is adjusted, the fixed unit valuation table can be generated quickly. Using Excel, the author successfully compiled the National Unified Construction Machinery Machine-shift Expense Quota (Jiangsu 1999 Budget Price) and the National Unified Municipal Engineering Budget Quota Jiangsu Province Valuation Table (200 1), which were promulgated by the Construction Department of Jiangsu Province and implemented in the whole province.
Second, using Excel2000 to realize the quotation of bill of quantities
1, the assumption of bill of quantities quotation method
At present, the Ministry of Construction has not yet promulgated a unified bill of quantities quotation model. The Ministry of Construction is organizing the compilation of the National Unified Bill of Quantities Valuation Method for construction, decoration, installation, municipal administration and landscaping. Some cities in Jiangsu province are also conducting pilot projects in this area, and there are calls to cancel the quota. I don't think quotas should be completely abolished. In Britain, there is the Standard Calculation Method of Building Quantity jointly compiled by the Royal Institute of Chartered Surveyors and the British Building Owners Association, which is similar to the direct fee quota in China, and there is also a labor quota in Japan. We should reform the existing pricing model with reference to the international general quotation principle. We should combine the rules of WTO to reduce the government's administrative intervention in the project cost. We should learn the essence of engineering cost management science at home and abroad, and establish a set of bill of quantities quotation method which is not only suitable for China's national conditions, but also in line with the international general quotation method. The specific methods are as follows: Step 1: Adopt the comprehensive unit price method. At present, the comprehensive unit price method is adopted in the pilot project of bill of quantities method. Establish a bill of quantities project table, so that the labor cost, material cost and machinery cost in the fixed unit valuation table become a part of the bill of quantities project table, and establish a one-to-one correspondence according to the subtitle number. According to the percentage of engineering labor costs, the charging standards of social medical insurance and endowment insurance for enterprise employees are formulated. It is stipulated that the direct cost of the project, social medical insurance premium for employees, endowment insurance premium and fixed management fee calculated by the bill of quantities are the cost price of the project, and it is not allowed to compete to lower the price in bidding. This can not only ensure the quality of the project, but also avoid the "tofu residue" project caused by blind bidding and price reduction. It also makes the verification of project cost price operable.
Determine the content of comprehensive management expenses in the form of documents, but let go of comprehensive management fee rate and planned profit rate. The enterprise shall comprehensively determine the project management fee and planned profit according to its own construction technology level, enterprise management level, operating conditions and market supply and demand relationship. Reform the method of uniformly stipulating the unit price of labor cost by the province, and the cities will calculate and publish the unit price of labor cost according to the local labor market. Realize the marketization of labor force, material price, project comprehensive management fee and planned profit.
Step 2: implement the bill of quantities quotation method. When the construction market in China is fully developed and mature, there will be no more CEOs of state-owned enterprises forced to quote below the cost for the operation of enterprises. When the quality standard system of construction projects is completely established, supporting project cost and bidding laws and regulations have been promulgated one after another. When the cost information network has been successfully operated, the data of completed project cost and bidding quotation can be consulted online in time. At this time, the bill of quantities quotation method can be implemented.
Referring to the British experience, the quantity and quality of the proposed project are comprehensively and accurately described in the bill of quantities, and the measurement of the construction project should be based on the net engineering quantity after installation. The consumption of engineering entities is separated from the consumption of engineering construction measures. The bill of quantities only lists the number of engineering entities calculated by drawings, and the consumption of construction measures is determined by the enterprise according to the construction technology and construction management level. In this way, in our pilot work of bill of quantities, there will be no inconsistency in the quantities calculated by enterprises for the same project, which will lead to the phenomenon that both parties dispute over the number of projects. Because our project division plan includes the quantities of construction measures, and different enterprises have different construction measures and different construction organization designs, resulting in different quantities of construction measures.
On the basis of the above-mentioned comprehensive unit price method, the cost management department can modify the bill of quantities, delete the sub-items belonging to construction measures, and merge the labor cost, machinery cost, engineering comprehensive overhead cost, planned profit and construction measures cost into the engineering construction cost. The construction cost of each listed project is determined by the market through bidding. Delete the labor cost and machinery cost in the fixed unit pricing table and modify it into the fixed pricing table of material consumption of engineering entities, from which the material cost of engineering entities can be calculated. For example, for bored piles, the bill of quantities only lists the number of design pile volumes (net quantities). When quoting, the labor cost, material cost, machinery cost, engineering comprehensive overhead cost, planned profit and taxes of steel casing burying, mud preparation, rotary drilling rig drilling, reinforcing cage fabrication and placement, pile concrete pouring, waste slurry transportation, and residual pile chiseling should be considered.
2. Using Excel2000 to realize the quotation of bill of quantities.
The cost management department should first establish the website of the project cost information network and publish the following information.
(1) project cost policies and regulations
(2) Cost standard, artificial price, material budget price and mechanical machine-team price.
(3) Mix proportion table of concrete, asphalt concrete and mortar
(4) Quota unit valuation table, bill of quantities project table or engineering entity material consumption quota valuation table and supplementary quota.
(5) Analysis of cost index of completed project.
(six) the register of registered cost engineers and the list of budget editors.
(7) Qualification and personnel of the cost consulting unit
Widely absorb builders, construction enterprises, intermediaries and international related units as members of the network to provide timely and accurate cost information for their bidding, budget and settlement.
According to the budget price adjustment of labor, materials and machinery, the cost management department regularly publishes the then and local bill of quantities, quota unit pricing table or engineering entity material consumption quota pricing table in the form of Excel file through the website. Realize the dynamic management of project cost.
When an enterprise quotes, it only needs to adjust the price difference of individual main materials, and it is not necessary to adjust the price difference of all materials item by item. It greatly reduces the workload of adjusting the price difference of labor, materials and machinery during quotation, and also avoids the error caused by adjusting the price difference of materials according to the coefficient. Simplify the quotation procedure and improve the quotation speed and accuracy.
Owners, contractors, cost consulting units, and network personnel of foreign related units use Excel2000' s powerful data and table processing function to copy and paste the relevant data in the bill of quantities, and then fill in the quantities, project overhead fees and profits in the bidding documents or the project construction fees predicted by the enterprise itself, plus taxes and fees payable to the state, and make a quick quotation according to the bill of quantities. By copying and pasting the consumption of labor, materials and machinery in the fixed unit valuation table, multiplying it by the corresponding engineering quantity, and using the classification and summary function of Excel2000, the labor, materials and machinery analysis table of this project can be quickly compiled. After the bill of quantities quotation is realized, the reasonable low price of the bidding project can be determined according to the lowest settlement price of similar projects published on the cost information website.
Excel2000 itself is an excellent data calculation software. Using Excel2000 to compile the project cost, Excel2000 has the common function of quotation calculation except that it can't input drawings to automatically calculate the engineering quantity. With the great influence of Microsoft in the world, most microcomputers at home and abroad are equipped with Office2000, including Excel2000 spreadsheet software, so that cost compilers don't have to spend money to buy professional budget software or spend time learning professional budget software. And directly use Excel2000 to compile the project quotation. This is conducive to the integration of China's project cost management with international standards after China's entry into WTO, and also helps international enterprises to understand China's quotation method and use China's project cost data.