Sample construction schedule in excel. Basic project plan templates in Excel. How to Choose the Right Project Plan Template
Most often, the calendar plan is developed by specialists using traditional methods manually and designed in the graphic editor AutoCAD or similar. Since there is no proper degree of automation, the speed of development of such graphs depends significantly on the skills of a specialist in this field and is not much different from those previously developed on paper before using a computer. In actual construction practice, an engineer works on constructing such a schedule, forced to constantly make adjustments due to unforeseen circumstances, delivery delays, etc. Such adjustments often take up almost all of the specialist’s working time during almost the entire construction process.
Currently, software solutions have been developed and are available for purchase to create a construction schedule automatically.
Among them:
Microsoft Office Project 2010 Professional (Approximate price 40,000 rubles)
Hector: designer builder (Estimated price of a workplace is about 210,000 rubles, taking into account the pre-installed AutoCAD)
SPDS Construction site (Estimated price of a workplace is about 185,000 rubles, taking into account the pre-installed AutoCAD and SPDS GraphiCS)
High cost
Need for additional staff training
They have a number of disadvantages that do not allow such solutions to be fully used in the process design process. For example, MS Project does not provide the ability to account for shift work. Other solutions require additional AutoCAD installed
The final appearance of the graph is shown in the figure below.
To build a calendar chart, standard MS Excel functions are used.
The technology for constructing such a graph is described below. The calendar schedule in general consists of tables (labor cost calculations), with data for each process, calendar schedule, as horizontal lines on the timeline and labor flow chart, which determines the number of simultaneously employed people.
1. Table (calculation of labor costs) with the names, volume, list and number of performers of the work performed.
For the convenience of further explanations, cells are filled in in the figures; in the final design, such a fill is absent. Cells with data for manual entry are indicated in yellow, for formulas - in blue. The cells required for constructing the graph are indicated in white, since they are not included in the list of labor cost calculation data and are “hidden” by setting the font to white on a white background and using the minimum column width. If you need to combine columns 1 and 2, just remove the border between them.
2. Work schedule.
3. Labor movement schedule.
1 Labor cost calculation
The labor cost calculation contains 17 points:
1. No. - numbering of all work performed.
2. Job title
3. Code code - includes the number of the work performed in
regulatory documents of EniR or (GESN, FER, TER, TSC)
Scope of work - is divided into 2 subparagraphs:
4. Unit From m. unit of measurement corresponding to normal work (m 2, m 3, kg, t, km, pcs, etc.)
5. Col.- quantitative characteristics taking into account units of measurement
Standard time - the amount of working time spent on performing a unit of work (product) by an employee, or a group of employees in given organizational and technical conditions.
Note: According to ENiR, the time standard is given per link (clause 15 of ENiR “General part”); However, in organizations and educational institutions the time standard is often accepted as for 1 person in a link.
- Right
,
- wrong.
Such an error leads to an unjustified reduction in construction duration (in formulas for determining the duration of work) and an overestimation of already stringent production requirements.
6. Man-ch.- man-hours - unit of work time recording - quantity hours, actually worked out by man.
7. Mash-h.- machine hours - unit of working time accounting - quantity hours, actually worked out by a unit of equipment.
Machines and mechanisms
8. Type, Name - type and name of machinery and equipment used.
9. Col.- quantitative characteristics in pieces.
Squad composition
10. Profession, rank- the number and composition of workers required by ENiR (for entering multi-line text via Alt+Enter).
11. Col.(n people) total number of employees in the unit.
Labor costs
12. Man-cm- person-shift - unit of working time recording
It is customary to use the unit of measurement - man-days, however, for the calculation it is convenient to take man-shifts since two and three shift work schedules can be used. At the request of consultants, the units of this column are usually used in person-days without changing the formula and calculated values.
13. Mash-cm
14. Number of units per shift ( ) After this column, it is recommended to leave 2 empty columns “start” and “offset”, these columns will be used to construct the chart.
15. Number of shifts per day
16. Number of people per day (
17. Duration
One of the main indicators that takes into account the number of workers per shift, the number of shifts. From the “labor costs” column, select the maximum value between columns 12 and 13
Formulas for determining the calculated values and the corresponding formulas entered into Excel cells are given in the table below.
| 12 | Labor costs | man-cm | | =IF(ROUND($E7*F7/8,2)0,ROUND($E7*F7/8,2);"") |
| 13 | mash-cm |  | =IF(ROUND($E7*G7/8,2)0,ROUND($E7*G7/8,2);"") |
|
| Start | =IFERROR(O7+S7-P8,O7-P8) |
|||
| 16 | Number of persons in a day |  | =IFERROR(K7*N7*Q7;"") |
|
| 17 | Duration | | =IFERROR(ROUND(MAX(L7,M7)/(N7*Q7);0);"") |
|
Construction of a calendar schedule.
Sh 
The calendar schedule section is a schedule of working days, taking into account weekends, grouped by month. In the cell of the first working day, enter the date as a function = DATE OF. In all subsequent functions =WORKDAY(T3,1) The Start and Duration columns create a stacked bar chart. The resulting Start column values must be set to 100% transparency. The values of the “beginning” column are, if necessary, edited manually to perform parallel work.
Construction of a labor movement schedule.
In each cell at the intersection of date columns and work rows, the number of workers performing a certain type of work on a specific working day is calculated using the formula:
=IFERROR(IF(V$6>$O9,1,0)*IF(V$6>$O9+$S9,0,1)*$R9,0).
The data obtained will be used to construct a labor movement schedule. To do this, you need to build a linear stacked histogram. In the resulting standard diagram, to bring it to the required form, it is necessary to reduce the gap, remove the bottom and side scales, scale and move them under the corresponding columns.
If the names of jobs are used as the initial data of the legend, then each key color of the legend will correspond to a column of the number of workers employed in a particular process. This type of coloring of the work schedule will be much more informative and visual when analyzing the duration of work and resources.
Automating the construction of a schedule using Excel allows you to do less routine work and pay more attention to solving problems in the technological process. Allows you to reduce the likelihood of errors and reduce the labor intensity when making changes. Ensure the necessary accuracy, reduce the time required for technological design. The possibility of flexible planning allows you to get the most current version of the calendar schedule at any given time.
This method of constructing a calendar schedule was first used when preparing a competitive diploma project in 2003 by the author of the article. During the construction of the OSU library building, the considered method was used to prepare reporting documents to the rector and coordinate the timing of construction work.
Bibliography
1. Uniform standards and prices for construction, installation and repair work. a common part
2. Excel 2007. Igor Pashchenko Publisher: Eksmo. 496с - ill.
3. Price lists for software http://www.csoft.ru/catalog/price.html
Schedule plans (their composition, structure and degree of detail) depend on the composition of the organizational and technological documentation in which they are developed. As part of the PIC, a calendar plan for construction and the preparatory period is developed and compiled on the basis of labor costs from estimated calculations. As part of the PPR, a work schedule or network schedule is developed, which reflects in detail the actual duration of construction.
The most common form of calendar schedule is the network model, which allows you to more clearly reflect the order of work on a construction project.
The procedure for developing calendar plans.
1. Drawing up a list of works in the technological sequence of their implementation;
2. Determination of volumes (according to working drawings);
3. Selecting a production method with a list of necessary machines and mechanisms, calculating standard labor intensity and machine intensity;
3. Determination of the composition of teams and units, the number of shifts.
4. Determining the estimated duration of individual types of work and identifying their possibility of combining;
5. Comparison of the obtained duration according to the schedule with the normative or directive; adjustment.
Based on the calendar plan, schedules of requirements for materials and labor resources are built.
Example of filling out a calendar plan
Column 1. Name of works.
This column indicates a list of works by type and period (preparatory, main period) in a strict technological sequence. It is allowed to consolidate some types of work; in this case, the performers of the work must be the same.
Column 2.3 Scope of work. The scope of work calculated from the working drawings is indicated.
Column 4. Labor costs. The person days are indicated, according to the regulatory documents of the GESN, the same in column 6, only the machine cm for machines and mechanisms.
Column 5. Required machines. They are selected in two stages: first they are selected based on technical parameters (for example, digging depth, lifting capacity, bucket capacity, etc.), and then based on economic comparison (with minimal costs).
Column 6. Number of cars. Depending on the volume and timing of the work, the amount of equipment required is selected.
Column 7. Duration of work. The duration of mechanized work is first calculated using the formula given below, and then the work performed manually.
where is the required quantity of mash-cm;
n M - number of cars (column 6.);
A - number of work shifts per day (column 8.);
α - coefficient of exceeding production standards (within 1.05-1.25).
The duration of work performed manually T r (days) is determined by dividing Q r (person days) by the number of workers n r, the number of shifts A (usually equal to 1) and the over-fulfillment coefficient α (varies in the range of 1.05-1.25) .
Column 8. The number of shifts is usually taken equal to 2 - for mechanized work, 1 - for manual work.
Column 9. Number of workers per shift. Determined specifically by the composition of the brigade (column 10).
Column 11. Graphic part. Work performed in one shift is usually indicated by one line, 2 shifts - by two parallel ones. Above them (lines) the number of workers (drivers) and the number of shifts (for example 2 x 1) are indicated.
Then the normative or directive deadline is compared with the one plotted on the graph. The main condition is that the actual deadline according to the calendar plan coincides with the directive or regulatory deadline or falls within the framework.
To evaluate the schedule for the consumption of labor resources, a diagram is drawn under the schedule in the form of a diagram, where in each period of time the number of workers indicated above the work schedule lines is summed up.
The calendar plan is assessed by the coefficient of uneven movement of workers Kr=N max/N avg, where N max is the maximum number of workers, N avg is the average number of workers;
If Kp does not exceed 1.5, then the schedule is satisfactory.
Example of a calendar plan (schedule) in Excel
Almost all people perceive information better “drawn” than “heard”. And it’s even better if this information is presented in images, rather than a series of numbers and indicators. Imagine that a stranger is talking about his dog. He does not describe her appearance and pedigree, does not specify color and age, etc. The imagination of each listener will draw its own image. And when we already imagine a handsome Great Dane, it turns out that we were told about a lovely pug. In this situation, we will laugh, but when faced with something similar at the enterprise, it will no longer be a laughing matter.
Therefore, in production everyone tries to visualize as much as possible. One of the most important documents, especially in construction, is the work schedule. We can safely say that the entire project without this schedule is wasted time. Since it contains all the adopted engineering and technical decisions, and also optimizes the timing.
What is a calendar plan?
The very name of this document gives an idea of its importance and significance. The calendar schedule for the production of work is a table that displays all of its volume and deadlines. In addition, the graph clearly shows the sequence of work performed, tied to specific dates (or simply the duration of various types of work - for typical projects). Most often, this document also contains information about the resources needed at each stage of construction: basic materials, equipment and personnel.
The ability to draw up a work schedule is one of the most important skills for managers at various levels. The more accurate and detailed the schedule is, the better all planned work will be carried out. Despite the fact that construction is considered the “native” branch of work scheduling, managers of all areas would benefit from knowing the principles
Where to begin
Any job can be broken down into small tasks. The simplest example is preparing a salad from fresh vegetables. What would seem simpler? But this elementary task can be divided into a sequence of actions. First, purchase all the ingredients, then wash them, cut them and mix them, seasoning them with sauce. Moreover, all actions can be separated in time (breaks in work appear), or they can be done sequentially, without breaks in time. In addition, all this can be done by one person, or maybe a whole team of cooks. So, there is a sequence of actions. It remains to calculate the completion time of each stage and determine how many and what kind of personnel are needed for this work. And our work schedule is almost ready.
Regardless of the industry, when planning, you first need to highlight the scope of work: break the entire process into components. Moreover, the criteria can be not only technological differences, but also the number of workers, and the necessary mechanisms and devices, etc.
Deadlines
After dividing everything into a sequence of actions, you can begin to calculate the deadlines for completing the work. For production and construction, there are regulations and standards by which specific deadlines are calculated for a given amount of work. For mental work, it is impossible to calculate the deadlines for completing work using a formula. But a manager with extensive experience, who has information about his staff, can quite clearly set a time frame for solving the task.
Knowing the deadlines for completing each type of work, we can begin to determine the time required to complete the entire process. It should be remembered that some tasks can be solved in parallel, and certain processes require technological breaks.
Resource calculation
Of course, personnel is the most important element of the process. The construction work schedule involves determining the number of performers, the specialization of workers and their qualifications. At this stage, we calculate the number and composition of teams and draw up a calendar plan for their employment at the site.
Next, we move on to determining the necessary equipment, mechanisms and devices. There are also regulations for this in manufacturing industries. And finally, last but not least important is the calculation of the materials required for the work.
Calculation of delivery times for materials
All this information will allow you to combine the work schedule with the delivery schedule of materials and equipment. Uniformity and continuity are the two basic principles of planning. Optimizing the schedule in the direction of reducing deadlines may not give the desired result, because there will be downtime in the work due to a lack of materials (or, conversely, the construction site will be literally filled with them, and therefore it will take a lot of time to find what is needed at the moment).
Force majeure increases the deadlines for completing work
Another important detail is that when drawing up a work plan, it is necessary to provide for possible risks. For construction, this could be anything from bad weather to heavy traffic on the roads. Taking into account force majeure circumstances, it is necessary to slightly increase the deadlines for completing certain types of work. Most often, this also affects the duration of the entire volume.
Despite this, planners should not strive to minimize time. After all, when work is disrupted, the general contractor will have to pay a penalty to both the customer and related contractors.
Automation of plotting
Just a few years ago, the calendar plan was compiled manually. The specialists calculated all the deadlines and the need for personnel and materials, and then visualized it with the help. For small volumes of work, this is an easy task. It’s a different matter if we are talking about a serious contracting organization managing several projects at the same time.
Programmers develop many auxiliary programs designed to automatically calculate and build a work schedule. A sample schedule calculated using Microsoft Office Project 2010 Professional, for example, can be easily found on the Internet. However, not every company will agree to spend additional funds on installing software and training staff to work with it. In addition, each specialized program has its own disadvantages. One does not take into account the possibility of shift work, the other, without writing macros, does not agree with the calculation of materials, for example, etc.
Therefore, most specialists involved in planning have learned to build a work schedule in Excel.
This program has many advantages:
- It's free. In the sense that Excel is part of the standard MS Office package, which is unconditionally installed on almost every computer.
- It's simple. Having minimal knowledge about calculating formulas and linking sheets to each other, you can do planning.
- It's visual. All calculations and results are displayed on one sheet. And making changes is immediately displayed on the graph.
- this is a document on the basis of which construction is carried out at all stages, according to the established deadlines. Production planning is the main condition for organizing the construction and installation process.
Work schedule: purpose and design features
At certain stages of design, project organizational documentation (POD) and work execution documentation (PWD) is drawn up, which also includes schedule plans.
– a document that defines the timing, a clear sequence of performance of certain types of work activities, establishing the relationship between them, taking into account the volumes and nature of the installation and construction process. As part of the PIC, a calendar master plan for the construction of the facility is drawn up, and as part of the PPR, work schedules for each facility are drawn up.
Schedule: appointment
Production planning is an integral part of organizing construction and installation work at all levels and stages. Normal progress in construction is possible only when everything is taken into account in advance: the sequence of completing assigned tasks, the number of machines, equipment, workers, and other resources required.
Neglect of this point will lead to inconsistency in the actions of the appointed performers, disruptions in the work process, missed deadlines, and, accordingly, an increase in the cost of installation and construction work.
Calendar plans will allow you to avoid such situations, since it is a kind of schedule at all stages of construction. But a systematically changing situation may require some adjustments; in addition, in any situation, the person in charge must clearly understand what he must do.
So what is a calendar plan for? Its main purpose is as follows:
When planning in detail significant amounts of work for the entire construction period, careful preparation is required, selection of the optimal sequence of construction and installation work, their timing, the number of participants, and control of various factors. Therefore, during construction, various forms of production planning are used, which provide a real opportunity to optimize the process and take into account the possibility of permissible maneuvers.
Scheduling production work: content
A schedule plan is developed at a specific construction site in order to clearly plan the stages of its construction, the progress of the work process, the number of employees, machines, etc.
Calendar schedules for the facility are drawn up in the following sequence:
- analysis of estimate and design documentation for a specific facility;
- determining the range of installation and construction processes to be included in the production plan;
- calculation of the volume of planned work;
- selection of production process methods, main machines, equipment;
- calculation of labor costs required to perform individual installation and construction works, and
- number of machine shifts;
- determining the duration of execution of individual processes, linking them within a time frame.
The initial information for proper work is:
- working diagrams of a structure or building;
- consolidated final estimate;
- pre-construction project;
- information on the procedure, delivery times of necessary materials, various designs,
- specialized equipment, the number and types of mechanisms intended for use, various machines, and employees by main professions;
- technological reliable maps for heavy construction and installation work and simple technological diagrams tied to a specific object and the local situation of the object’s construction.
That is, all the information contained in this documentation must be fully reflected in the workflow schedule.
Based on the calendar plan, the following should be developed:
- schedule of daily labor demand (by profession);
- plan for the daily need for basic mechanisms, machines (by size, type);
- schedule of daily needs for structures, products, semi-finished products.
Special attention must be paid to planning at the stage of drawing up project documentation.
Make sure that the schedule contains reliable information (otherwise it will not be used), accessible (for those employees who need it), detailed, and clear.
Scheduling during construction: features
The installation schedule for a single object (CP) is planned in the PPR section at the stage of creating a working package of documents. This is the main technical paper, according to which management is carried out, constant monitoring of the progress of the construction and installation process is carried out, and all work of subcontracting enterprises is monitored.
The deadlines established in the documentation are used as initial data: weekly-daily plans, shifts of various tasks.
The initial information when drawing up the CP is:
- complex calendar schedule as part of the PIC;
- construction period standards;
- working estimates, drawings;
- information about the technical potential of the enterprises participating in the installation;
- technological diagrams for construction processes.
During the preparation of the CP, a nomenclature of actions is developed and their volumes are determined. The process of creating a production schedule also includes:
- selection of methods for performing work and the necessary means of mechanization;
- determination of the composition of executive teams and main links;
- installation of a technological uniform sequence of actions and calculation of the number of performers, drawing up an estimate;
- determining the duration of work processes and their relationship, adjusting the number of performers if necessary;
- a plan of basic needs for certain resources is developed, and the estimated duration of installation is compared with standard indicators.
If there are simple standard TCs, it is necessary to clarify their connection to terrain conditions and accept the TC information in the form of calculations.
Features of scheduling during the construction of complexes
The construction projects, which include a complex of structures and buildings, include residential buildings, municipal complexes, as well as industrial organizations.
In the calendar schedule for the construction of entire complexes of structures, various buildings as part of the construction project, the terms are calculated, the order of installation of the main and additional units, buildings and stages of the work process is determined with the distribution of all existing volumes of construction and installation work over the main installation periods.
The duration of the construction is regulated by the relevant standards for the duration of installation and construction of industrial buildings. Providing all stages of construction with material resources, design and estimate package of documents, labor and technical appropriate resources is carried out according to deadlines in the required volumes, ensuring full compliance with established standards.
In addition to the general period for the construction of new and permissible expansion of existing organizations, the standards determine the total duration of the construction of individual complexes, entire production facilities and workshops.
Consequently, a calendar plan is a whole process that makes it possible to simulate the main project and obtain the best version of the production schedule with optimal deadlines.
Properly planned and set tasks are the key to successful construction.
In contact with
This plan is a specific sequence of work stages and deadlines for their completion.
Such a plan is created as an addition to a document on the implementation of any type of work. This may apply to construction, mechanical engineering, etc.
The plan should be simple, beautifully designed, and understandable. But its main goal is to break the work into parts.
Man is designed in such a way that he better perceives what he sees. That's why we are always afraid of big work. To speed up the execution process, it is advisable to divide all the work into small parts.
The plan should indicate responsible persons, deadlines for completion, number of man-hours, as well as detailed instructions and recommendations.
Example:
Such a work schedule should be drawn up based on these resources. Let's consider the situation using the example of a foreman of one of the construction companies. You need to improve an apartment with an area of 90 sq.m.
Do you have:
- 10 people subordinated (human resources)
- 7 kg. putties, 10 l. glue, 4 rolls of wallpaper (material resources)
- 6 days to complete the work (time)
Based on the available resources, you need to start building a plan. By the way, resources should be indicated in the plan. This is necessary if you are forced to postpone the due date.
And even if you break all the work into points, this does not guarantee the delivery of your project on time. The fact is that the person doing the work tends to get everything done at the last moment. For example, work for which a deadline of ten days is assigned is completed in the last two days before the end of the deadline.
Obviously, this approach will not provide the required quality. To control the performer, it is possible to introduce “control points”, i.e. additionally break down each plan item into sub-items.
During the execution of work, unforeseen difficulties may arise that can increase the delivery time (increased volume of work, reduced funding, etc.). To ensure that both parties do not have any complaints, all changes should be made in writing, and at the very beginning of the project. Agree, later it will be much more difficult to explain why oral contracts should be replaced with written ones.
The plan must necessarily include points that would regulate negotiations between the customer and the contractor. For example, a description of negotiating, communicating results, receiving comments, clarifying tasks, and correcting them. This is necessary because communication with the customer usually takes a lot of time during the project.
Below is a standard form and a sample work schedule, a version of which can be downloaded for free.