An integration of spreadsheet and project management software for cost optimal time scheduling in construction

Cost-effective time scheduling is widely recognized as an important topic in management of construction projects. Namely, execution of project activities needs engagement of certain resources and direct costs. Especially, the amount of working time consumed by labour and machines represents an influential indicator that requires to be considered in preparation of construction schedules (Petlíková and Jarský 2017).

Acceleration of project activities from their normal execution modes often demands additional resources, and the related direct costs are consequently amplified. On the other hand, indirect costs usually decrease when project implementation is accelerated. Both aforesaid facts encourage the achievement of project execution in an optimal duration and under the minimum total cost. Another critical success factor that can also come to the fore is keeping available project budget within boundaries and not to surpass contracted deadline.

In conventional construction practice, a cost-effective time schedule of the project is usually attained through a time-consuming cost-duration analysis of different feasible alternatives. However, optimization has proven to be a better way to achieve cost-effective schedules in comparison with traditional approaches. Over the years, a number of different approaches have been successfully applied in cost optimization of project schedules, mostly based either on approximate heuristic approaches or on exact mathematical programming.

In regard to heuristic methods, research works on the topic of optimal project scheduling can be found in recent literature. For example, cost optimization of project schedules has been effectively carried out by genetic algorithms (Eshtehardian et al. 2009), simulated annealing (He et al. 2009), tabu search (Hazir et al. 2011), neural networks (Adeli and Karim 1997), ant colony optimization (Kalhor et al. 2011), particle swarm optimization (Yang 2007), differential evolution (Nearchou 2010), harmony search (Geem 2010) and hybrid methods, such as genetic algorithm and dynamic programming (Ezeldin and Soliman 2009), cutting plane method and Monte Carlo simulation (Mokhtari et al. 2010), genetic algorithm and simulated annealing (Sonmez and Bettemir 2012) among others. Certainly, there are also various extensions of aforesaid techniques that can be found in the literature.

In search of high-quality solutions, different exact techniques have also been proposed to be used for reaching cost-optimized project schedules alongside the mentioned heuristic approaches. Linear and nonlinear programming models have been handled by continuous optimization algorithms. In regard to discrete scheduling, there exist two branches of optimization models that have been mostly suggested to be dealt by exact methods: (1) mixed-integer linear programming (LP) models such as those proposed by Sakellaropoulos and Chassiakos (2004), Vanhoucke (2005), Hazir et al. (2010) and others and (2) mixed-integer nonlinear programming models such as those recently introduced by Al Haj and El-Sayegh (2015) and Klanšek (2016).

Projects often consist of a complex system of activities that should be coordinated and managed to achieve their goals (Crnković and Vukomanović 2016). The dynamic and stochastic environment of construction projects further aggravates the decision-making processes and renders them more complex (Galić et al. 2016a, 2016b). Hence, usage of commercial software packages can significantly contribute to optimally solve project scheduling problems in practice. Core of the software packages is solver engines with different search algorithms that are capable of solving only a certain class or a number of different classes of optimization models.

For instance, spreadsheets are a popular tool for dealing with optimization problems among users. Also, WinQSB and Excel (Microsoft computer package), with add-ins like What’sBest, Evolver or Solver can be exposed as those spreadsheet software that are often employed for optimization. The latter one has proved to be a powerful spreadsheet tool with robust programming capabilities known as Visual Basic for Applications (VBA). Application of Solver in the field of project scheduling was suggested by researchers like Silva Filho et al. (2010), Trautmann and Gnägi (2015), and others.

Project management software (PMS) has been frequently considered for graphical representation and control of time schedules. In this context, Microsoft Project has been widely recognized as a useful tool for project management (Kostalova and Tetrevova 2014), and its successful applications were documented in a number of articles (Kažović and Valenčić 2013; Von Laszewski and Dilmanian 2008).

This paper presents an approach to cost optimal time scheduling, which integrates a spreadsheet application and data transfer to PMS. At this point, the optimization problem of project time scheduling is modelled employing Microsoft Excel and solved to optimality using Solver, while organization of data is dealt by macros. Thereupon, Microsoft Project software is utilized for further managing and presentation of optimized time scheduling solution. An application example is shown in this paper to demonstrate the advantages of proposed approach.

The rest of this article is organized as follows. Section 2 introduces optimization problem formulation. Modelling and solving optimization problems with spreadsheets are presented in Section 3, which is followed by Section 4 that deals with PMS. Section 5 demonstrates integration of spreadsheet and PMS on an application example related to cost optimal time scheduling in construction, and Section 6 provides some conclusions at the end of the paper.

Optimization methods are capable of solving various engineering problems. Although optimization problems can come from different areas and may deal with totally incomparable systems, they could be formulated in a similar manner. Generally, the problem of optimization may be determined as minimize f(x), subject to h(x) = 0 and g(x) ≤ 0, where f(x) denotes the objective function, which is required to be minimized over the vector of decision variables x, h(x) = 0 covers equality constraints while g(x) ≤ 0 indicates inequality conditions.

Here, the objective function formulates the criterion for identification of the optimum solution while constraints set up boundaries for the feasible space. It needs to be exposed here that the objective function can also be maximized within the space of feasible solutions if required. As far as decision variables are concerned, they are commonly calculated among their lower and upper boundaries, x^LO ≤ x ≤ x^UP. Note that the variables can be continuous, x∈R, where R is the set of real numbers, or integer, x∈Z, where Z is the set of integers. Integer variables may also act as binary 0–1 decisions, i.e. x∈{0,1}^m.

Certainly, it is necessary to consider that the selection of an adequate search algorithm for solving a particular optimization problem should be implemented attentively in order to acquire valuable output results. Therefore, prior to choosing the solution technique, the optimization problem needs to be analyzed in terms of its functions, conditions and decision variables.

Modelling advantages of spreadsheet software have been widely recognized quite sometime ago and such program tools are now broadly spread into numerous areas of human activities. One of the leading reasons that support their success lies in their intuitive cell-based structure and reasonably simple interface. The usable features of the application, such as data entry and manipulation, functions, graphs, word processing capacities, workgroup sharing, programmability alternatives, and a wide range of add-in programs, make them one of the essential and often employed tools by many computer users who need to process and deal with a large number of information. Along with their basic usage, spreadsheets have been applied as software tools for developing mathematical models in many different areas. Herein, a spreadsheet program Microsoft Excel 2016 is used for the purpose of modelling the optimization problem of project time scheduling.

Excel includes an add-in tool known as Solver that can be suitably used for the aim of solving the problem of project time scheduling to optimality. Solver tool basically represents a software program where solver engines cover more search algorithms for solving a certain type of optimization problems (Frontline Systems 2017). Here, Simplex LP engine is selected and applied to optimally solve project time scheduling problem, which was presented in the application example of this article. This engine possesses capacity of solving smooth linear optimization problems and represents one of the three engines that are covered by Solver software, others being covered by GRG Nonlinear engine, for solving smooth nonlinear optimization problems, and Evolutionary engine, for nonsmooth ones.

As soon as the project schedule is optimized, the obtained output data should be processed by PMS in which their graphical representation and control during project execution can be performed. Most PMS are capable of importing spreadsheets but only in a specific data arrangement. Excel programming capabilities, VBA, are suggested to be used as a very powerful additional feature at that phase.

VBA represents an implementation of Microsoft’s event-driven programming language Visual Basic which allows building of automated processes. At this point, it is applied to automate the process of designing spreadsheet form, recognizable to selected PMS, from optimized time scheduling data reported in Microsoft Excel file.

PMS is required to support organizing, planning and managing resources in project management. They are capable of dealing with estimation, project scheduling, budget management, resource assignment, cooperation, communication, decision-making, quality management and administration systems. Various PMS solutions are now available in the market. However, trends in the development of recent planning and optimization models are also simulation based (Galić et al. 2017) and this article intends to contribute to these areas.

For successful project management, it is important that all project activities are performed within a deadline, which means that the financial plan is set in accordance with the estimated budget as well as that the project beneficiaries are satisfied with project implementation and benefits derived from it (Biafore 2013; Harris 2016). In this particular paper, Microsoft Project is selected to manage project schedules. As is well-known, it is primarily intended for planning, monitoring and control of project realization and represents one of the most commonly applied software packages for project management in construction. Microsoft Project may be used in (Marmel 2013):

monitoring of all gathered project information,

Mentioned program is applied here in the context of graphical demonstration of optimal project schedules. At the beginning, basic information about the project are required to be inserted in Project options dialog such as calendar type, currency, start date, working days, hours of work per day, and others as demonstrated in Figure 1.

Fig. 1:

Project information about activities, their durations and costs as well as precedence relations need to be generated into PMS (Valenko and Klanšek 2017). Since all required information are gathered in Microsoft Excel, macro written in VBA programming language is implemented to create a new spreadsheet that can be recognized by Microsoft Project. New spreadsheet should contain columns with the following information about project activities for input recognition: ID, name, duration, predecessors and costs. Import wizard should be executed for data transfer as soon as all the data are nested within a new spreadsheet (Figure 2).

Fig. 2:

Visualization of optimization results is attained by Microsoft Project where Gantt chart tool is used for graphical illustration of optimal time schedule for the project. As is known, Gantt chart enables user-friendly identification of starting and finishing times for activities on a time line.

The flowchart for using the proposed integration of spreadsheet and PMS for cost optimal time scheduling in construction is shown in Figure 3.

Fig. 3:

It should be noted here that the proposed approach enables optimal scheduling in project planning phase as well as tracking and optimal update of schedules during project execution. The use of proposed approach is supported by an application example shown in the following section.

In order to show advantages of solving cost optimization problems of project scheduling using Solver and data transfer from Microsoft Excel to Microsoft Project, a modified example project, originally introduced by Hillier and Lieberman (2014), is considered in this section. Example project incorporates 14 activities. Activities and their predecessors are given as follows:

A – excavate,

Cost-duration alternatives and direct cost-duration functions for project activities are given in Table 1. Linear approximation of direct cost-duration relations is developed for project activities based on given cost-duration options. First option denotes crashed activity duration while the second one is representation of activity duration in normal mode.

Targeted project duration is set at 47 working weeks and indirect project cost is determined to be 31.000 € per week. The purpose of optimization was to find the project time schedule with optimal durations and start times of activities at minimum total project costs.

The problem of cost optimal time scheduling was set here as LP task. Optimization model formulation contained objective function, consisted of direct costs of activities and indirect project cost, which was subjected to precedence relationship constraints and project duration restraints, including bounds on durations and start times of activities as well as limitations on project as a whole. Project activities were generated following the principle of activity-on-node approach. Note here that the optimization problem formulation for the example project can be found in the study by Hillier and Lieberman (2014).

After the LP model formulation was set, a spreadsheet application Excel from Microsoft was employed for modelling for data inputs/outputs. Input data for the example project are shown in Figure 4.

Fig. 4:

First spreadsheet covers input data of the addressed example project such as IDs of activities, their descriptions and predecessors, and normal and crashed durations supported by related direct costs accompanied with direct cost-duration functions, including targeted project duration as well as indirect project cost.

For the aim of further data manipulation, an array of preceding and succeeding project activities was created. Preceding activities were determined as columns and succeeding activities were set as rows of the array. As soon as precedence relation between connected activities was established, the intersecting cell included the value of 1. Array was defined by second spreadsheet as shown in Figure 5.

Fig. 5:

Durations and start times of project activities were determined as changing cells in third spreadsheet as introduced in Figure 6. Objective function and (in)equality constraints were also incorporated into the optimization model.

Fig. 6:

Excel’s add-in Solver was allowed to execute the cost optimization of example project time schedule applying the developed model. Settings in Solver were done as shown in Figure 7. Objective was appointed, where minimal value was desired and changing variable cells were described. Objective function was subjected to blocs of constrains and Simplex LP was employed to solve the optimization problem.

Fig. 7:

Optimization problem was solved by a personal computer (Intel Core2 Duo P8600, 2.40 GHz, 4GB RAM DDR3 and 250 GB SSD disc). LP approach applied on considered project scheduling problem represents continuous optimization technique by which optimal values of variables were calculated between their upper and lower bounds.

Minimum total cost necessary to complete the example project, gained by selected solution method, was 5.912.000 €, and the optimal project duration was found to be 42 weeks. Here, minimum total cost contained 4.610.000 € of direct cost and 1.302.000 € of indirect cost. The obtained optimal durations and direct costs of project activities are shown in Figure 8.

Fig. 8:

After the optimization process was executed and optimal solution for the example project time schedule was found, macro written in VBA was applied to create a new output file. Using the activation of the command button labelled “Create output file,” demonstrated in Figure 6, macro was performed.

Output file included a spreadsheet in which array of project data was settled as displayed by Figure 8. Macro transferred input data of project activities from first spreadsheet are shown in the first two columns of the array of Figure 4. Durations of activities were rearranged from spreadsheet containing decision variables, introduced in Figure 6, to third column of the new array. Similar process was executed to direct cost values of project activities that are included in the fifth column.

Precedence relations among example project activities were set as finish-to-start (FS) ones, and therefore, predecessors in the fourth column were labelled with their ID numbers and FS abbreviations. In this connection, precedence relationships were rearranged from array of activities presented in Figure 5. Afterwards, the created output file was applied to import optimized data into Microsoft Office Project where subsequent data management were feasible. The graphical representation of optimal project time schedule, shown in Figure 9, was created using the Gantt chart tool.

Fig. 9:

In Gantt chart, non-critical activities were blue coloured while critical activities were labelled with red colour and their order comprised the critical path of optimized project schedule. As well-known, durations of critical activities directly affect the project duration and, consequently, its total costs. Thus, when execution times of critical activities are crashed, the associated direct costs are increased while indirect costs are reduced on account of shorter project duration. Note here that the acceleration of non-critical activities cannot affect the project duration but may only raise the direct costs.

For further workflow presentation, a delay in project activity was created. While tracking the progress of the project, the delay in activity J, i.e. putting up of the wallboard, has occurred. Aforesaid activity was 1 week behind the schedule, and therefore, the actualization of the project time schedule was necessary. Input data in Excel were changed in such a manner that durations of finished activities were fixed and the activity of putting up the wallboard was changed to a new duration of 7 weeks. Updated optimal duration of the project was rescheduled for a week since delayed activity represented a critical one (Figure 10).

Fig. 10:

Consequently, the total project costs increased to a value of 5.913.000 €. With transfer of data to MS Project, a new time schedule was created (Figure 11).

Figure 11:

Construction projects are frequently recognized as challenging tasks where numerous activities should be performed in a certain mode before the production is finished. For that purpose, production resources like labour, machinery, materials and equipment need to be utilized in construction activities. Furthermore, resources can be assigned to different activities in such a way that activity durations are crashed, and therefore, time schedule is much important for cost-effective realization of construction project.

Significance of tools for project management was widely acquainted in construction business since their applications allow more efficient time scheduling. It is in parallel with the development of various programs in computers, which came to the fore and enabled quicker and easier introduction of management tools to workflow. Nowadays, contemporary PMS are employed for monitoring and visualization of project tasks, information exchange among engaged team, communication with project parties, etc. Cost optimization of time schedules, on the other hand, is commonly achieved by specialized software for solving mathematical models and thus additional information flow to project management programs is required.

This paper presented an approach to cost optimal time scheduling in construction, which integrated a spreadsheet application and data transfer to PMS. Optimization problem of project time scheduling was modelled applying Microsoft Excel and solved to optimality by Solver while data management was dealt by macros. Afterwards, Microsoft Project software was employed for further organizing and presentation of optimized time scheduling solution. Mentioned software was selected here since Microsoft Office suite is broadly used among construction companies and it incorporates all tools required for process implementation.

An application example was presented to expose the advantages of proposed approach. The example demonstrated that the project time schedule modelled and optimized in Microsoft Office Excel can be conveniently transferred to Microsoft Project, where visualization and further treatment of gained results can be executed. Data flow between programs is thus automated and possibilities of error occurrence during scheduling process are reduced to a minimum. Finally, the example introduced integration of spreadsheet and PMS for cost optimal time scheduling in construction within well-known program environment, which increases the possibilities of its wider use in practice.