Creating a network schedule in Microsoft Excel. How to build a network schedule in Excel (Excel)

In system network planning And the management of construction production adopted the following concepts and terminology.

Under the concept, the project summarizes the circle of organizational and technical tasks, solved to achieve end results building production. These include: Development of a feasibility study of the planned construction, the choice of the construction site, the conduct of engineering and geological research, the design of the territory for development, development and approval of the technical documentation necessary for the construction of construction, including schedules and schemes for the production of construction and installation work before leaving Objects commissioning.

A complex of works performed to achieve a certain goal that causes a certain part of the project is called the project function. For example, work related to the preparation of construction production (development of working drawings of buildings and structures, project production project; placement of orders for the manufacture of equipment, structures and supply them construction site etc.) or with the production of construction and installation work, with the construction of foundations, (device picking, breakdown axes, digging, blank and installation of formwork and reinforcement, preparation concrete mix, TUNDING AND LOADING it in the formwork, rampage and capture of the sinus of casual foundations) are features in the project of structures.

The most important indicators of the project efficiency are the cost and duration of construction, which are directly dependent on similar indicators of individual project functions. If a list of all project functions is set and defined for each of them a sequence of execution and time spent, then by portraying the specified functions in the form of a graphical network, you can see which of them determine the timing of the remaining functions and the entire project as a whole.

From here it follows that network graph Reflects the logical interconnection and interactiveness of all organizational, technical and production operations to implement the project, as well as a certain sequence of their implementation.

The main parameters of the network graph are the work and event, and derivatives - the network, the critical path and time reserves.

Under work implies any process that requires time spending. In network charts, this term determines not only certain production processes requiring the costs of material resources, but also expected processes associated with the observance of technological breaks, for example, for hardening concrete.

Event is an intermediate or final result of one or more works required to start other works. The event is committed after the fulfillment of all works included in it. Moreover, the moment of an accomplishment of the event is the moment of the end of the latter (the work in it. Thus, the event is the final results of certain works and at the same time - the initial positions to begin next. An event that does not have previous work is called the initial; event, No subsequent work is called the final.

Work on the network graph is depicted by one solid arrow. The duration of work in units (days, weeks) is affixed under the arrow, and the name of the work on the arrow. Each event is depicted with a circle and numbered (Fig. 115).

Fig. 115. Designation of events and work M - N.

Fig. 116. Designation of the dependence of technological events.

Fig. 117. Designation of the dependence of organizational events.

The duration of a particular work, established depending on the adopted method of its implementation by YNIR or the costing of labor costs, is called a temporary assessment. The dependence between individual events that do not require the costs of time and resources is called fictitious work and on the network graphics is depicted by a dotted arrow.

These dependencies or fictitious works can be divided into three groups: technological, organizational, conditional.

A technological dependence means that the execution of one work depends on the completion of another, for example, the masonry of the subsequent floor walls cannot be made before installing the panels of the lower floors (Fig. 116).

The dependence of the Organizational nature shows the transitions of the workers team, the transfer of mechanisms from one site to another, etc. They arise mainly NRI performing work by streaming methods (Fig. 117).

If there are several end-end events (for example, the commissioning of several objects included in the enterprise launcher) should be associated with conventional dependencies or fictitious work together - entering the enterprise to action (Fig. 118, b).

The initial event should be one. In cases where the initial events are somewhat (for example, work on each other, work on the passage of the pitfalls of several objects begins), they should be conditionally connected by the designation of fictitious works with a single initial event (Fig. 118, a).

If the timing of the actual initial events of individual objects of the complex is different, the concept of dependencies with the considerable time convergent in one initial node should be introduced.

The duration set by taking into account the single-shifted, and for the leading machines of two-chaired work and the optimal saturation of the front of the work, is called normal performance. If the duration of the work is due to the maximum loading of the front of the work at two, three-day work, it is considered minimal.

Fig. 118. Designation of conditional dependencies.

The term of work differs in terms:

the earliest start time is the first day when the work may begin;

the earliest deadline for work is the end of the work, if it started at the earliest life time;

the latest period of work - the last day of the start of work without delay in the overall construction period;

the latest deadline for the end of work is the day when the work should be completed without delaying construction, i.e. without a breakdown of the overall construction period.

The difference between the most late and earliest period of the start of the work determines the private time reserve, i.e. the time on which you can postpone work without increasing the duration of construction. The time for which you can postpone work without delaying any subsequent work, determines the full (general) time reserve, which is the difference between the full time reserves in question and subsequent work. In the case of several subsequent works, such a work is selected, which has the smallest value of the full time reserve.

The continuous sequence of work and events from the initial to the final, requiring the greatest time for its implementation, determines the critical path that causes the overall duration of construction, since the critical work on it does not have time reserves.

In the network graphs, the direction of the arrows depicting the work can be selected arbitrarily. Usually such graphs are built from left to right. However, the arrows of certain types of work can go up, down or right left.

In compiling a network graph, each work should be considered in terms of its connection with other works and to respond to the following questions:

what work should be completed before the start of this work;

what other work can be completed simultaneously with this work;

what work cannot be started before the completion of this work. Consider some examples graphic image Ties and sequence of work in network graphs.

Fig. 119. Communication schemes between the works (A, B, B, G, D, E, W - cases of 1,2,3,4,5,6,7).

Case 1 (Fig. 119, a). Dependence between works A (1-2) and B (2-3). Work would not be started until the end of A.

Case 2 (Fig. 119,6). The dependence of two works from one. The works of D (7-8) and E (7-9) cannot be started until the work of G (6-7) is completed.

Case 3 (Fig. 119, B). The dependence of one work from the end of two works. The work of E (10-11) cannot begin until the works of G (8-10) and D (9-10) are completed.

Case 4 (Fig. 119, d). The beginning of two works depends on the end of two works. The works of E (15-16) and D (15-17) can begin only after the end of work b (13-15) and in (14-15).

Case 5 (Fig. 119, 6). The dependence of two groups of work. The work of B (15-16) depends only on the end of work A (14-15), and the work of G (21-22) depends on the end of work A (14-45) and in (19-21). Linkage of the network is carried out by inclusion of fictitious work D (15-21).

Case 6 (Fig. 119, e). The work of G (47-48) cannot be launched until the end of work in (46-47). In turn, the work of B (50-51) cannot be launched until the end of work in (46-47) and A (49-50). Work E (47-50) fictitious, determining the logical linkage of the network by containing the start of work B (50-51) until the work in (46-47) is completed.

Case 7 (Fig. 119, g). Work g (8-14) cannot be started until the end of the work A (2-8) and B (4-6); Work w (12-16) can not be started before the proof of Fig. 120. Scheme of network graphics, works d (10-12), b (4-6); The dependence between these works is indicated by the fictitious work E (6-12). Since the work of G (12-16) does not depend on the end of work A (2-8), it is separated from the last fictitious work in (6-8).

Fig. 120. Network graphics scheme.

In order to clarify the methods of building network graphs, we consider the case when the following conditions arose on the construction of any object:

at the beginning of the construction of work A and B must be carried out in parallel;

work in, g and d can be started before the end of work A;

work b should be completed before the start of the work e and w;

at the same time, the work e also depends on the end of work A;

work 3 cannot be started until the end of the work D and E;

work and depends on the end of the work of G and 3;

work K follows the end of work;

work l follows the work to and depends on the end of the work of G and 3;

the ultimate work M depends on the end of work in, and and L.

In fig. 120 shows one of several possible solutions Tasks determined by the terms of construction. All decisions should be based on the same logical concept, regardless of the type of grid. The grid must be considered from the point of view of the logical sequence of work. For this purpose, its review should be started from the last event at the facility and go back from the event to the event, checking these provisions: Every work that starts at the event depends on all the works leading to the event; All work from which the work under consideration should depend on the event. If both questions can be obtained a positive answer, the network schedule satisfies the requirements of the projected technology for the construction of an object.

When building a network schedule under the concept of "work", depending on the degree of desired accuracy, you can mean separate species Works or complexes of production processes performed on this facility of one of the participants in the construction of organizations. For example, the chief engineer of the trust needs to know less details than the manufacturer of the work. Therefore, to ensure the construction management at the Trest level, the network schedule can be compiled on the basis of more enlarged indicators.

Appointment of service. Online calculator is designed to find network model parameters:

early term of the event of an event, the late term of the achievement of the event, early work of the work, early term of work, the late period of work, the late period of the end of work;
time reserve for the accomplishment of an event, full time reserve, free time spent;
the duration of the critical path;

And also allows you to estimate the likelihood of the entire complex of work for D days.
Instruction. The solution in online mode is carried out analytically and graphically. It is drawn up in Word format (see example). Below is a video instruction.

Example. The project description in the form of a list of operations performed indicating their relationship is given in the table. Build a network schedule, determine the critical path, build a calendar schedule.

Work (I, J)	Number of preceding work	Duration t ij.	Early Dates: Start T IJ R.N.	Early deadlines: End of T IJ R.O.	Late deadlines: Start T IJ PN	Late deadlines: End of T IJ P.O.	Time Reserves: Full T IJ P	Time Reserves: Free T IJ S.V.	Time Reserves: Events R J
(0,1)	0	8	0	8	0	8	0	0	0
(0,2)	0	3	0	3	1	4	1	0	1
(1,3)	1	1	8	9	8	9	0	0	0
(2,3)	1	5	3	8	4	9	1	1	0
(2,4)	1	2	3	5	13	15	10	10	0
(3,4)	2	6	9	15	9	15	0	0	0

Critical path: (0.1) (1.3) (3.4). The duration of the critical path: 15.

Independent work time R IJ N is a part of a full time reserve, if all previous works are completed at the later dates, and all subsequent work begin in early terms.
The use of an independent time reserve does not affect the value of other work reserves. Independent reserves seek to use if the end of the previous work occurred in the late permissible period, and subsequent works want to fulfill in early terms. If R ij n ≥0, then such an opportunity is available. If R ij n<0 (величина отрицательна), то такая возможность отсутствует, так как предыдущая работа ещё не оканчивается, а последующая уже должна начаться (показывает время, которого не хватит у данной работы для выполнения ее к самому раннему сроку совершения ее (работы) конечного события при условии, что эта работа будет начата в самый поздний срок ее начального события). Фактически независимый резерв имеют лишь те работы, которые не лежат на максимальных путях, проходящих через их начальные и конечные события.

To build a network schedule, it is necessary to identify the sequence and interconnection of the work: what work must be performed, and what conditions to ensure that this work can be started, which works can be performed in parallel with this work, which works can be started after the end of this work. These questions make it possible to identify the technological relationship between individual works, ensure the logical construction of a network graph and its compliance with the simulated complex of work.

The level of detailing the network graph depends on the complexity of the object under construction, the number of resources used, the volume of work and the duration of construction.

There are two types of network graphs:

verses - work

vertines - Events

Network graphics type "Vertine - Work".

Elements of such a graph are works and dependencies. Work is a specific production process that requires the costs of time and resources for its execution, and is depicted by a rectangle. Dependence (fictitious work) shows the organizational and technological connection between work that does not require the cost of time and resources is depicted by an arrow. If there is an organizational or technological break between the works, then the duration of this break is indicated.

If the work of the network schedule "Vertine - Work" has no preceding work, it is the initial work of this schedule. If the work does not have subsequent work, it is the final operation of the network schedule. In the network chart "Vertine - work" should not be closed contours (cycles), i.e. Dependencies should not return to the work from which they came out.

Network graphics type "Verters - Events".

Elements of this type of graphs are works, dependencies and events. The work is depicted with a solid arrow, dependency - dotted. The event is the result of one or several works, necessary and sufficient to start one or more subsequent works, and is depicted by a circle.

In network schedules of this type, each work is between two events: the initial one of which it comes out, and the final one in which it enters. Network graphics events are numbered, so each work has a code consisting of its initial and final events.

For example, in Fig. 6.2 of work are encoded as (1.2); (2,3); (2.4); (4.5)

If the network graphic event is the "Vertine - Events" has no preceding work, it is the source event of this schedule. The following works directly behind it are called source. If the event has no subsequent work, it is the final event. The works included in it are called final.

to properly display interconnections between work, you must follow the following basic rules for building a network schedule "Vertens - Events":

1. In the image of simultaneously or parallel to work performed (for example, the works "B" and "B" in Fig. 6.2), dependence (3.4) and an additional event (3) are introduced.

2. If you need to perform the works "A" and "b" to start the work "G", and to start work<В» - только работу «А», то вводится зависимость и дополнительное событие (рис.6.З.).

Z. The network graph should not be closed contours (cycles), i.e. chains of work returned to the event from which they came out

4. In the network schedule, additional events and dependencies are introduced during the construction organization (Fig. 6.5.).

To determine the duration of the critical path and the timing of each work, the following is determined temporary parameters :

Early start of work -

Early end of work -;

Later the beginning of work -;

Later ending work -

Full time reserve - R;

Free time spent -

Early start of work - The earliest moment of starting work. The early start of the source work of the network graphic is zero. The early start of any work is equal to the maximum early end of the previous work:

Early end of work - The earliest moment of completion of this work. It is equal to the amount of early start and duration of work.

Later ending work - The lowest moment of completion of work, in which the duration of the critical path will not change. Later, the end of the final work is equal to the duration of the critical path. Later, the end of any work is equal to the minimum late on the sample of subsequent work.

Later the beginning of work - The very late moment of the start of work, in which the duration of the critical path will not change. It is equal to the difference between the late end of this work and its duration.

The works of the critical path the early and later periods of the beginning and the end are equal to each other, so they do not have time reserves. Work not lying on the critical path have time reserves .

Full time reserve - The maximum time on which you can increase the duration of work or transfer its beginning without increasing the duration of the critical path. It is equal to the difference between the late and early start or end of work.

Free time reserve - Time on which you can increase the duration of work or postpone its beginning without changing the early start of subsequent work. It is equal to the difference between the early start of the subsequent work and the early termination of this work.

Calculation of the network schedule "Vertine - Work"

To calculate the network schedule "Vertex - work", a rectangle depicting work is divided into 7 parts (Fig.6.6).

In the upper three parts of the rectangle, an early start, duration and early end of work, in the three lower ones started, time reserves and later ending. The central part contains the code (number) and the name of the work.

The calculation of the network graph begins with the definition of the early timelines. Early starts and endings are calculated sequentially from source to final work. Early start of initial work is equal to, early ending - the sum of early start and duration of work:

The early start of the next work is early to the early end of the previous work. If this work is directly preceded by several works, its early start will be equal to the maximum of the early terminations of the preconditions:

Thus, the early dates of all the works of the network schedule are determined and are recorded in the upper right and left parts.

Early ending the final work determines the duration of the critical path.

The calculation of late terms is carried out in the reverse order from final to its original work. Later, the end of the final work is equal to its early end, i.e. The duration of the critical path.

Later began as the difference in late end and duration:

Later, the beginning of subsequent work becomes late after the previous work. If this work is directly followed by several works, then it will later end the end of the last started on the following work:

Similarly, the later dates of all the works of the network schedule are defined and recorded in the left and right lower parts.

A full time reserve, equal difference in the late and wounds of them deadlines, is entered into the numerator of the middle of the bottom:

A free time reserve, equal difference between the minimum early beginnings of subsequent work and the early termination of this work, is recorded in the denominator of the dyname of the lower part:

The free reserve is always less or equal to the full work reserve.

Network graphics and rules for building them

Network graph is a graphic image of the processes, the execution of which is necessary to achieve the goal.

Network planning and control methods (SPU) are based on graph theory. The graph is called a combination of two final sets: multiple points, which are called vertices, and multiple pairs of vertices, which are called ribs. In the economy, two types of graphs are commonly used: wood and network. The tree is a hosted graph without cycles, having a source vertex (root) and extreme vertices. The network is an oriented end connected graph that has an initial vertex (source) and the final vertex (stock). Thus, each network graph is a network consisting of nodes (vertices) and connecting their oriented arcs (ribs). The graphics of the graph are called events, and connecting their oriented arcs - works. On the network graph, the events are depicted with circles or other geometric figures, and connecting their operations with dimensionless arrows (they are called dimensionless because the length of the arrow does not depend on the amount of work that it reflects).

Each network chart event is attributed to a specific number ( i.), and the work connecting events is denoted by the index ( iJ.). Each work is characterized by its duration (duration) t (ij). Value t (ij) In hours or days are affixed in the form of a number above the appropriate arrow of the network graphic.

In the practice of network planning use several types of work:

1) real work, a production process that requires labor costs, time, materials;

2) passive work (expectation), a natural process that does not require labor and material resources, but the implementation of which can occur only for a certain period of time;

3) fictitious work (dependence) that does not require any costs, but it shows that some event cannot be accomplished before the other. When building a graph, such works are usually denoted by a dotted line.

Each work independently or in combination with other works ends with events that express the results of the work performed. In the network graphs, the following events are distinguished: 1) source, 2) intermediate, 3) final (final). If the event has an intermediate character, then it is a prerequisite for the beginning of the work next. It is believed that the event does not have a duration and is instantaneously after the fulfillment of the works previously. The initial event is not preceded by any work. It expresses the time of the conditions for the beginning of the fulfillment of the entire complex of work. The final event has no subsequent work and expresses the time of the end of the entire complex of work and achieving the intended goal.

Interrelated work and network schedule events form paths that connect source and final events, they are called full. The full path on the network graph is a sequence of work in the direction of the arrow from the source to the final event. The full path of maximum duration is called critical. The duration of the critical path determines the final term for performing the entire complex of work and the achievement of the intended purpose.

Works located on the critical path are called critical or tense. All other works are considered non-critical (unprotected) and have time reserves that allow the timing of their implementation and the timing of the achievement of events without affecting the total duration of the entire complex of work.

Rules for network schedule.

1. The network is drawn from left to right, and each event with a large sequence number is shown to the right of the previous one. The overall direction of the arrows depicting works will also basically be located left to right, while each work should exit the event with a smaller number and enter an event with a large number.

Incorrectly correct

3. The network should not be "deadlocks", that is, all events, except for the final, must have a subsequent work (deadlocks are called intermediate events, of which no work is coming out). This situation may occur when this work is not needed or any work is skipped.

4. The network should not have events, except for the source, which are not preceded by at least one job. Such events are called "tailings". This may take place in the event of the preceding work.

For proper numbering of network schedule events, the following scheme of action is used. The numbering starts from the original event, which is assigned the number 0 or 1. From the initial event (1), they strike out all the outgoing operations (oriented arcs), and the event is re-found on the remaining network, which is not a single job. This event is assigned the number (2). The specified sequence of actions is repeated until all network schedule events are numbered. If, with the next crossing, there are two events that do not have incoming work, the numbers are assigned arbitrarily. The number of the final event must be equal to the number of events in the network graph.

Example.

In the process of building a network graphic, it is important to determine the duration of each work, that is, it is necessary to give it a temporary rating. The duration of work is established either in accordance with applicable standards, or on the basis of expert assessments. In the first case, the duration estimate is called deterministic, in the second - stochastic.

There are various options for calculating stochastic time estimates. Consider some of them. In the first case, there are three types of duration of the specific work:

1) the maximum term that comes from the most unfavorable work conditions ( t Max);

2) the minimum term that comes from the most favorable work conditions ( t min);

3) the most likely time outgoing from the actual security of the resources and the availability of normal conditions for its implementation ( t B.).

Based on these estimates, the expected work time is calculated (its temporary assessment) by the formula

. (5.1)

In the second case, two estimates are set - minimal ( t min) and the maximum ( t Max). The duration of work in this case is considered as a random value, which, as a result of implementation, can take any value at a specified interval. The expected value of these estimates ( t Oh.) (with a beta distribution of probability density) is estimated by the formula

. (5.2)

To characterize the degree of scattering of possible values \u200b\u200baround the expected level, the dispersion indicator is used ( S 2.)

. (5.3)

The construction of any network graphic begins with the preparation of a complete list of works. The next job is installed, and for each specific work is determined directly preceding and subsequent work. To establish the boundaries of each type of work, questions are used: 1) What should precede this work and 2) what should follow this work. After drawing up a complete list of works, establishing their sequence and time estimates, proceed directly to the development and compilation of the network schedule.

Example.

Consider as an example a warehouse building program. List of operations, their sequence and temporary duration of the table.

Table 5.1.

Network graphics list

Operation	Operation description	Directly preceding operation	Duration, day.
BUT	Clearing construction site	-
B.	Recessing pitted under the foundation	BUT
IN	Uklade foundation blocks	B.
G.	Laying outdoor engineering networks	B.
D.	Construction frame building	IN
E.	Roofing	D.
J.	Internal sanitary work	G, E.
Z.	Flooring	J.
AND	Installation of door and window frames	D.
TO	Thermal insulation of overlaps	E.
L.	Laying the electrically conductive network	Z.
M.	Stucco wall and ceilings	And, k, l
N.	Interior decoration	M.
ABOUT	Outdoor finish	E.
P	Improvement of the territory	N, O.

Built based on Table data. 5.1 The pre-network schedule of work is as follows (Fig. 5.1).

Fig. 5.1. Preliminary network graph

Below is the same schedule for the construction of a warehouse building, numbered and with imposed temporary assessments of work (Fig. 5.2).

Fig. 5.2. Final version of network graphics

Example 8.Information on the construction of the complex is asked a list of works, their duration, sequence of execution and is shown in the table. Build a network schedule of a complex of work and find the correct numbering of its vertices.

Name of works	List of subsequent work	Duration in months
Road construction
Preparation of quarries for operation
Construction of the village.
Order equipment
Construction of the plant
Dam construction, dam
Connection of the plant and pipelines
Preliminary tests

To build a draft network chart, each job is depicting in the form of a solid oriented arc, and the relationship between the work is in the form of a dotted oriented arc. This arc communication will be carried out from the end of the arc corresponding to the previous work, to the beginning of an arc corresponding to the subsequent work. We obtain the network schedule shown in the picture:

A large number of arc complicates the solution, so it simplifies the received network. To do this, we will throw out some connection arcs, the removal of which will not violate the procedure for performing work. The beginning and end of the ejected arc merge into one vertex. The vertices that are not included in any arc can also be combined into one. We get the following network schedule:

We find the correct numbering of the vertices (events) of the network schedule.

Number 1 receives a vertex in which no arc is included. We remove (mentally or pencil) arcs coming out of the top with the number 1. In the resulting network graph there is only one vertex, which is not a single arc. So, it receives the following number 2 in order (if there are several of them, then all the vertices in which no arc includes, they receive the following numbers). Next, again (mentally) we remove the arc, but already emerging from the top with the number 2. In the network received by the network only one vertex, which is not included in any arc. It means that it gets the following number 3, etc., and so on.

6.4.6. Example of calculating time characteristics

Example 9.Suppose the graph is set:

Early Death Development:

Late Death of Events:

- the duration of the critical path;

Time Reserve:

Early start of work:

Early deadline for work:

Late term of completion of work:

Late start of start of work:

Full time reserve of work:

Private time reserve of the first type:

Private reserve of the time of the second type:

Independent time reserve:

The coefficient of tension is calculated for several ways that do not coincide with the critical ( ={0,3,5,6,8,9,10,11}=60).

Take work (4-7) and find the maximum critical path passing through this work: (0-3-7-10-11), T (L max) \u003d 49,

=10+8+5=23

K H (4.7) \u003d (49-23) / (60-23) \u003d 26/37;

Take the job (1-2) and find the maximum critical path passing through this work: (0-1-2-7-10-11), T (L MAX) \u003d 48,

=8+9+3+5=25

Take a job (2-7) and find the maximum critical path passing through this work: (0-1-2-7-10-11), T (L MAX) \u003d 48,

=8+9+3+5=25

K H (4.7) \u003d (48-25) / (60-25) \u003d 23/35;

All computed parameters can be displayed on the network graph. To do this, use a four-sector method of fixing the parameters, which is as follows. The circle denoting the event is divided into four sectors. The center records the event number (J); in the left sector - the most late destruction of the eventj ( ), in the right - the earliest deficiency time of the eventj ( ), in the upper - reserve of the time of the event of the eventj (R j), in the lower - the numbers of the preceding events through which the maximum duration goes to this (
).

Display on the graph for our example: