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

The following concepts and terminology are adopted in the system of network planning and management of construction production.

The concept of a project summarizes the range of organizational and technical tasks to be solved to achieve the final results of construction production. These include: the development of a feasibility study for the planned construction, selection of a construction site, engineering and geological surveys, registration of the territory for development, development and approval of technical documentation necessary for construction, including schedules and schemes for the production of construction and installation works before the delivery of the objects in operation.

The set of works performed to achieve a specific goal that determines a specific part of the project is called a project function. For example, work related to the preparation of construction production (development of working drawings of buildings and structures, a project for the production of work; placing orders for the manufacture of equipment, structures and their delivery to the construction site, etc.) or foundations (installation of cast-off, breakdown of axes, digging of pits, preparation and installation of formwork and reinforcement, preparation of concrete mixture, transporting and laying it in the formwork, stripping and grounding the sinuses of concreted foundations) are functions in the construction project.

The most important indicators of 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 has been established and the sequence of execution and time expenditures are determined for each of them, then by depicting these 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.

It follows from this that the network diagram reflects the logical relationship and interdependence of all organizational, technical and production operations for the implementation of the project, as well as a certain sequence of their implementation.

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

Work means any time-consuming process. In network diagrams, this term determines not only certain production processes that require the expenditure of material resources, but also the expected processes associated with the observance of technological breaks, for example, for hardening of laid concrete.

An event is an intermediate or final result of one or several jobs required to start other jobs. The event occurs after all the work included in it has been completed. Moreover, the moment of the event's completion is the moment of the end of the last (included in it work. Thus, an event is the final results of certain works and at the same time - the starting positions for the beginning of subsequent ones. that has no subsequent work is called final.

Work on the network diagram is depicted with a single solid arrow. The duration of the work in units of time (days, weeks) is put under the arrow, and the name of the work on the arrow. Each event is shown with a circle and numbered (Fig. 115).

Figure: 115. Designation of events and work m - n.

Figure: 116. Designation of the dependence of technological events.

Figure: 117. Designation of dependence of events of an organizational nature.

The duration of a particular work, established depending on the accepted method of its implementation according to the UNIR or labor cost estimates, is called a time estimate. The dependence between individual events, which does not require time and resources, is called fictitious work and is depicted by a dashed arrow on the network graph.

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

Dependency of a technological nature means that the performance of one work depends on the completion of another, for example, the walls of the next floor cannot be laid before the floor slabs of the lower floor are installed (Fig. 116).

Dependence of an organizational nature shows the transitions of teams of workers, the transfer of mechanisms from one site to another, etc. They arise mainly when performing work by flow methods (Fig. 117).

If there are several end events (for example, the commissioning of several objects included in the start-up complex of the enterprise), they should be linked by conditional dependencies or fictitious work together - putting the enterprise into operation (Fig. 118, b).

The start event must be one. In cases where there are several initial events (for example, independently of each other, work begins on a fragment of the foundation pits of several objects), 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 cost of real time, converging at one initial node, should be introduced.

The duration, set taking into account one-shift, and for leading machines of two-shift work and the optimal saturation of the work front, is called the normal duration of work. If the duration of work is due to the maximum load of the work front during two, three-shift work, then it is considered minimal.

Figure: 118. Designation of conditional dependencies.

The term of work differs in terms:

the earliest start date for work is the first day when work can begin;

earliest completion date of work - the day of completion of work, if it was started at the earliest start date;

the latest start date of work - the last day of the start of work without delaying the total construction period;

the latest completion date for the work is the day when the work should be completed without delaying construction, that is, without disrupting the overall construction period.

The difference between the latest and the earliest start date determines the private reserve time, that is, the time by which work can be postponed without increasing the duration of construction. The time for which work can be postponed without delaying the execution of any subsequent work determines the total (total) slack, which is the difference between the total time reserves of the considered and subsequent work. In the case of several subsequent jobs, the job is selected that has the smallest total reserve of time.

The continuous sequence of works and events from the initial to the final, which requires the most time to complete it, determines the critical path, which determines the total duration of construction, since the critical works lying on it have no time reserves.

In network diagrams, the direction of the arrows representing the jobs can be chosen arbitrarily. Typically, these graphs are plotted from left to right. However, the arrows for certain jobs can go up, down, or right to left.

When drawing up a network schedule, each job should be considered in terms of its connection with other jobs and the following questions should be answered:

what work should be completed before starting this work;

what other work can be completed simultaneously with the execution of this work;

what work cannot be started before the completion of this work. Let's look at some examples of graphical representations of links and workflows in network diagrams.

Figure: 119. Schemes of communication between works (a, b, c, d, e, f, g - cases 1,2,3,4,5,6,7).

Case 1 (Fig. 119, a). The relationship between works A (1-2) and B (2-3). Job B cannot start before job A.

Case 2 (Fig. 119.6). Dependence of two jobs on one. Work D (7-8) and E (7-9) cannot be started until work D (6-7) is completed.

Case 3 (Fig. 119, c). The dependence of one job on the completion of two jobs. Work E (10-11) cannot begin until work G (8-10) and D (9-10) are finished.

Case 4 (Fig. 119, d). The beginning of two jobs depends on the completion of two jobs as well. Works E (15-16) and D (15-17) can begin only after completion of works B (13-15) and C (14-15).

Case 5 (Fig. 119, 6). Dependence of two groups of works. Work B (15-16) depends only on the end of work A (14-15), and work D (21-22) depends on the end of work A (14-45) and C (19-21). Linking the network is carried out by including fictitious work D (15-21).

Case 6 (Fig. 119, f). Work Г (47-48) cannot be started before the end of work C (46-47). In turn, work B (50-51) cannot be started until the end of work C (46-47) and A (49-50). Work E (47-50) is fictitious, defining the logical linkage of the network by restraining the start of work B (50-51) until work C (46-47) is completed.

Case 7 (Fig. 119, g). Work D (8-14) cannot be started until the end of work A (2-8) and B (4-6); work Ж (12-16) cannot be started before completion Fig. 120. Scheme of the network schedule, works D (10-12), B (4-6); the relationship between these works is indicated by fictitious work E (6-12). Since work G (12-16) does not depend on the end of work A (2-8), it is separated from the last fictitious work B (6-8).

Figure: 120. Scheme of the network schedule.

In order to understand the methodology for constructing network diagrams, consider the case when the following conditions arose during the construction of an object:

at the beginning of construction, work A and B must be performed in parallel;

work C, D and E can be started before the end of work A;

work B must be completed before work E and G;

while work E also depends on the end of work A;

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

work I depends on the completion of work D and 3;

work K follows the end of work W;

work L follows work K and depends on the completion of work G and 3;

the final work of M depends on the end of work B, I and L.

In fig. 120 shows one of several possible solutions to the problem determined by the given construction conditions. All decisions must be based on the same logical concept, regardless of the type of mesh. The grid must be considered from the point of view of the logical sequence of work. For this purpose, its review should start with the last event at the facility and go back from event to event, checking the following points: whether each work, starting at an event, depends on all works leading to the event; whether all the activities on which the work in question should depend are included in the event. If you can get a positive answer to both questions, then the network schedule satisfies the requirements of the projected construction technology of the facility.

When building a network diagram, the concept of "work", depending on the degree of desired accuracy, can mean certain types of work or complexes of production processes performed at a given facility by one of the organizations involved in the construction. For example, the chief engineer of a trust needs to know fewer details than the contractor. Therefore, to provide construction management at the trust level, the network schedule can be drawn up on the basis of more aggregated indicators.

Service purpose... The online calculator is designed to find network model parameters:
  • early date of the event, late date of the event, early start of work, early end of work, late start of work, late end of work;
  • time reserve for the event, full time reserve, free time reserve;
  • duration of the critical path;
and also allows you to estimate the probability of completing the entire complex of works in d days.
Instruction. Online solution is carried out analytically and graphically. It is formatted in Word format (see example). Below is a video tutorial.
Number of vertices Numbering of peaks from # 1.

The initial data is usually specified either through a distance matrix or in a tabular way.
Data entry Distance matrix Tabular method Graphical method Number of lines
Analyze the network model: t min and t max are given given t min, t max, m opt
Optimization according to the criterion of the number of performers reserves-costs reduction of terms
",0);">

An example. A description of the project in the form of a list of operations performed with an indication of their relationship is given in the table. Build a network schedule, identify a critical path, build a timetable.

Work (i, j) Number of previous works Duration t ij Early terms: beginning t ij R.N. Early terms: ending t ij R.O. Late dates: beginning t ij P.N. Late dates: 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). Duration of the critical path: 15.

Independent runtime reserve R ij Н - part of the full reserve of time, if all previous work ends at a later date, and all subsequent work begins at an early date.
The use of an independent slack does not affect the slack of other jobs. They tend to use independent reserves if the completion of the previous work occurred at a late permissible date, and they want to perform subsequent work at an early date. If R ij Н ≥0, then there is such a possibility. If R ij Н<0 (величина отрицательна), то такая возможность отсутствует, так как предыдущая работа ещё не оканчивается, а последующая уже должна начаться (показывает время, которого не хватит у данной работы для выполнения ее к самому раннему сроку совершения ее (работы) конечного события при условии, что эта работа будет начата в самый поздний срок ее начального события). Фактически независимый резерв имеют лишь те работы, которые не лежат на максимальных путях, проходящих через их начальные и конечные события.

To build a network schedule, it is necessary to identify the sequence and relationship of work: what work needs to be done, and what conditions to ensure so that this work can be started, what work can and should be done in parallel with this work, what work 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 the network schedule and its compliance with the modeled complex of works.

The level of detail in the network schedule depends on the complexity of the facility under construction, the amount of resources used, the amount of work and the duration of construction.

There are two types of network graphics:

tops - works

vertices - events

Network graphs of the "top-work" type.

Elements of such a schedule are work and dependencies. Work is a specific production process that requires time and resources to complete it, and is depicted by a rectangle. Dependency (fictitious work) shows the organizational and technological connection between the works, which does not require time and resources, is depicted by an arrow. If there is an organizational or technological break between works, then the duration of this break is indicated on the dependencies.

If the work of a node-to-work network has no prior work, then it is the original work of that graph. If the work has no follow-up work, then it is the final work of the network schedule. There should be no closed loops (cycles) in the "tops - works" network diagram, i.e. dependencies should not return to the work they left from.

Network graphs of the "vertex-event" type.

Elements of this type of graph are activities, dependencies, and events. The work is depicted by a solid arrow, dependence - by a dotted line. An event is the result of one or more jobs, necessary and sufficient to start one or more subsequent jobs, and is depicted as a circle.

In network diagrams of this type, each job is between two events: the initial one, from which it exits, and the final one, which it enters into. Network events are numbered, so each job has a code consisting of its start and end event numbers.

For example, in Fig. 6.2 works are coded as (1,2); (2,3); (2.4); (4.5)

If an event on a tops-events network has no prior activity, then it is the originating event of that graph. The works immediately following it are called initial works. If the event has no subsequent work, then it is the final event. The work included in it is called final.


for the correct display of the relationships between works, the following basic rules for constructing the "Vertices - Events" network diagram must be observed:

1. When displaying simultaneously or in parallel works performed (for example, works "B" and "C" in Fig.6.2), the dependence (3,4) and an additional event (3) are introduced.

2. If to start work "D" it is necessary to perform work "A" and "B", and to start work<В» - только работу «А», то вводится зависимость и дополнительное событие (рис.6.З.).

H. There should be no closed loops (cycles) in the network diagram, i.e. a chain of work returning to the event from which they left

4. Additional events and dependencies are introduced in the network schedule during the flow organization of construction (Fig. 6.5.).

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

Getting started early -

Early completion of work -;

Late start of work -;

Late end of work -

Full reserve of time - R;

Free time reserve - g.

Getting started early - the earliest moment of starting work. The early start of the original network activities is zero. The early start of any job is equal to the maximum early completion of the preceding jobs:

End of work early - the earliest moment of completion of this work. It is equal to the sum of early start and duration of work.

Late end of work - the latest moment of completion of work, at which the duration of the critical path does not change. The late completion of finishing works is equal to the duration of the critical path. The late completion of any job is equal to the minimum late start of subsequent jobs.

Late start of work - the latest moment of the start of work, at which the duration of the critical path does not change. It is equal to the difference between the late completion of a given work and its duration.

For works on the critical path, the early and late start and end dates are equal to each other, so they have no time reserves. Jobs that are not on the critical path have time reserves .

Full reserve of time - the maximum time by which it is possible to increase the duration of work or postpone its beginning without increasing the duration of the critical path. It is equal to the difference between the late and early start or end date of work.

Free time reserve - the time by 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 end of the given work.

Calculation of the network schedule "tops - works"

To calculate the network diagram "tops-work", the rectangle representing the work is divided into 7 parts (Figure 6.6).

In the upper three parts of the rectangle, the early start, duration and early end of work are recorded, in the lower three, late start, time reserves and late end. The central part contains the code (number) and the name of the work.

The calculation of the network schedule begins with the determination of early dates. Early starts and ends are calculated sequentially from source to finish. The early start of the original work is O, the early end is the sum of the early start and the duration of the work:

Early start of subsequent work is equal to early end of previous work. If this work is immediately preceded by several works, then its early start will be equal to the maximum of the early endings of the previous works:

Thus, the earliest dates of all network activities are determined and entered in the upper right and left parts.

The early completion of finishing work determines the length of the critical path.

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

Late start is defined as the difference between late end and duration:

The late start of subsequent work becomes the late end of the previous work. If this work is directly followed by several works, then its late completion will be equal to the minimum of the later beginnings for the following works:

In a similar way, the late dates of all network activities are determined and recorded in the lower left and right parts.

The full reserve of time, equal to the difference between the late and early dates, is entered in the numerator of the middle of the lower part:

Free reserve time equal to the difference between the minimum early start of subsequent work and the early end of this work is written in the denominator of the middle of the lower part:

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

Network diagrams and rules for their construction

A network diagram is a graphical representation of the processes that must be performed to achieve a goal.

Methods of network planning and management (SPM) are based on graph theory. A graph is a collection of two finite sets: a set of points, called vertices, and a set of pairs of vertices, called edges. In economics, two types of graphs are commonly used: tree and network. A tree is a connected graph without cycles, with an initial vertex (root) and extreme vertices. A network is a directed finite connected graph that has an initial vertex (source) and an ending vertex (sink). Thus, each network graph is a network of nodes (vertices) and directed arcs (edges) connecting them. The nodes of the graph are called events, and the oriented arcs connecting them are called activities. On the network graph, events are depicted by circles or other geometric shapes, and the work connecting them by 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 event is assigned a specific number ( i), and the work connecting the events is denoted by the index ( ij). Each work is characterized by its own duration (duration) t (ij)... Value t (ij) in hours or days, put down as a number above the corresponding arrow of the network schedule.

In the practice of network planning, several types of work are used:

1) real work, production process, which requires labor, time, materials;

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

3) fictitious work (dependence), which does not require any costs, but shows that some event cannot happen earlier than another. When constructing a schedule, such works are usually indicated by a dotted line.

Each work, alone or in combination with other works, ends with events that express the results of the work performed. The following events are distinguished in network diagrams: 1) initial, 2) intermediate, 3) final (final). If the event is of an intermediate nature, then it is a prerequisite for the beginning of the work following it. It is considered that the event has no duration and occurs instantly after the completion of the work preceding it. There is no work preceding the initiating event. It expresses the moment of the onset of conditions for the start of the entire complex of work. The final event does not have any subsequent work and expresses the moment of the end of the entire complex of work and the achievement of the intended goal.

Interconnected activities and network events form paths that connect the originating and ending events, they are called complete. The full path on the network graph is a sequence of activities in the direction of the arrows from the origin to the end event. The full path of the maximum duration is called critical. The duration of the critical path determines the final deadline for completing the entire set of work and achieving the intended goal.

Jobs located on the critical path are called critical or stressful. All other works are considered non-critical (non-stressful) and have time reserves that allow you to move the timing of their completion and the timing of events, without affecting the total duration of the entire complex of works.

Rules for building a network diagram.

1. The net is drawn from left to right, and each event with a higher serial number is drawn to the right of the previous one. The general direction of the arrows representing the works should also generally be located from left to right, with each work having to leave the event with a lower number and enter the event with a higher number.


Incorrect Correct

3. There should be no "dead ends" in the network, that is, all events, except for the final one, must have subsequent work (intermediate events are called dead ends, from which no work leaves). This situation can occur when this work is not needed or some work is missed.


4. There should be no events in the network, except for the initial one, which is not preceded by at least one work. Such events are called “tail” events. This can be the case if previous work is missed.


For the correct numbering of network events, use the following scheme of actions. The numbering starts from the initial event, which is assigned the number 0 or 1. From the initial event (1), all the activities outgoing from it (oriented arcs) are deleted, and on the remaining network an event is again found that does not include any activity. This event is assigned a number (2). The specified sequence of actions is repeated until all network schedule events are numbered. If, during the next deletion, two events occur simultaneously that do not have incoming jobs, then numbers are assigned to them arbitrarily. The ending event number must be equal to the number of events in the network schedule.

Example.


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

There are various options for calculating stochastic time estimates. Let's consider some of them. In the first case, three types of duration of a specific work are established:



1) the maximum period, which is based on the most unfavorable conditions for performing work ( t max);

2) the minimum period, which is based on the most favorable conditions for the performance of work ( t min);

3) the most probable period based on the real provision of work with resources and the presence of normal conditions for its implementation ( t in).

Based on these estimates, the expected time to complete the work (its time estimate) is calculated using the formula

. (5.1)

In the second case, two estimates are given - the minimum ( t min) and maximum ( t max). The duration of work in this case is considered as a random variable, which, as a result of the implementation, can take any value within a given interval. The expected value of these estimates ( t standby) (for beta probability density distribution) is estimated by the formula

. (5.2)

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

. (5.3)

The construction of any network schedule begins with a complete list of works. Then the order of work is established, and for each specific work the immediately preceding and subsequent works are determined. To establish the boundaries of each type of work, questions are used: 1) what should precede the given work and 2) what should follow the given work. After drawing up a complete list of works, establishing their priority and time estimates, they proceed directly to the development and drawing up of a network schedule.

Example.

Consider as an example a program for the construction of a warehouse building. The list of operations, their sequence and time duration will be drawn up in a table.

Table 5.1

List of works of network schedule

Operation Operation description Immediately preceding operation Duration, days
AND Clearing the construction site -
B Excavation of a foundation pit AND
IN Laying of foundation blocks B
D Laying of external engineering networks B
D Construction of the building frame IN
E Roofing D
F Internal plumbing work G, E
Z Flooring F
AND Installation of door and window frames D
TO Floor insulation E
L Laying an electrical conductive network Z
M Plastering of walls and ceilings I, K, L
H Interior decoration M
ABOUT Outdoor decoration E
P Improvement of the territory H, O

Built on the basis of the data in the table. 5.1 preliminary network work schedule is as follows (Fig. 5.1).



Figure: 5.1. Preliminary network schedule

Below is the same schedule for the construction of a warehouse building, numbered and with the time estimates of the work affixed (Fig. 5.2).


Figure: 5.2. Final network schedule

Example 8.Information about the construction of the complex is given by a list of works, their duration, sequence of execution and is given in the table. Build a network schedule for the work package and find the correct numbering of its peaks.

Name of works

List of subsequent works

Duration in months

Road construction

Preparation of quarries for operation

Village construction

Equipment order

Plant construction

Dam construction, dam

Plant and pipeline connection

Preliminary tests

To build a rough network diagram, each job will be depicted as a solid oriented arc, and the links between the jobs as a dashed oriented arc. We will connect this arc from the end of the arc corresponding to the previous work to the beginning of the arc corresponding to the subsequent work. We get the network diagram shown in the figure:

A large number of arcs complicates the solution, so we simplify the resulting network. To do this, let's throw out some link arcs, the deletion of which will not violate the order of work. Let's combine the beginning and end of the ejected arc into one vertex. Vertices that do not include any arc can also be combined into one. We get the following network diagram:

Let's find the correct numbering of the nodes (events) of the network diagram.

Number 1 is given to a vertex that does not include any arcs. We delete (mentally or with a pencil) the arcs going out of the vertex with number 1. In the resulting network graph there is only one vertex, which does not include any arcs. This means that it receives the next number 2 in order (if there are several of them, then all the vertices that do not include any arc receive the next numbers in order). Then again (mentally) we delete the arcs, but already leaving the vertex with number 2. In the resulting network graph there is only one vertex, which does not include any arcs. This means that she gets the next number 3 in order, and so on.

6.4.6. Example of calculating time characteristics

Example 9.Let's say the graph is given:

Early date of events:

Late completion of events:

- the duration of the critical path;

Time reserve:

Early start date:

Early completion date:

Late completion date:

Late start date:

Full work time reserve:

Private reserve of time of the first type:

Private reserve of time of the second type:

Independent time reserve:

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

Let's 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 n (4.7) \u003d (49-23) / (60-23) \u003d 26/37;

Let's take job (1-2) and find the maximum critical path passing through this job: (0-1-2-7-10-11), t (L max) \u003d 48,

=8+9+3+5=25

Let us take work (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 n (4.7) \u003d (48-25) / (60-25) \u003d 23/35;

All calculated parameters can be displayed on a network graph. For this, a four-sector method of fixing parameters is used, which is as follows. The event circle is divided into four sectors. The event number (j) is recorded in the center; in the left sector - the latest date of the event j ( ), in the right - the earliest date of the event j ( ), in the upper - the reserve time for the occurrence of the event j (R j), in the lower - the numbers of previous events through which the path of maximum duration (
).

Display on the graph for our example: