SRSP 2020 Wave 1 Umbrella Administration Agreement (No TF073493) DISCLAIMER (EC Contract No REFORM/GA2020/007) EC REFERENCE #20HR10 The project and respectively this report was funded by the European Union via the Structural Support Reform Programme and implemented by the World Bank in cooperation with the European Commission’s Directorate General for Reform Support (DG REFORM). The views expressed herein can in no way be taken to reflect the official opinion of the European Union. This report is a product of the staff of the World Bank Group with external contributions. The findings, interpretations, and conclusions expressed in this work do not necessarily reflect the views of The World Bank Group, its Board of Executive Directors, or the governments they represent. The World Bank Group does not guarantee the accuracy of the data included in this work, which is drawn from multiple external sources. The boundaries, colors, denominations and other information shown on any map in this work do not imply any judgement on the part of the World Bank Group concerning the legal status of any territory or the endorsement or acceptance of such boundaries. Nothing herein shall constitute, or be considered to be, a limitation upon or waiver of the privileges and immunities of The World Bank Group, all of which are specifically reserved. Measuring Performance of Public Procurement in Croatia RIGHTS AND PERMISSIONS Output 2 The material in this work is subject to copyright. However, because The World Bank encourages dissemination of this knowledge, this work may be reproduced, in whole or in part for non-commercial purposes, as long as full attribution to this work is given. Any queries on rights and licenses, including subsidiary rights, should be addressed to World Bank Report on the presentation of the Publications, The World Bank Group, 1818 H Street NW, Washington, DC 20433, USA; fax: 202-522-2625; e-mail: pubrights@worldbank.org. results of the procurement and contract data analysis through a ACKNOWLEDGMENTS web-based The main authors interactive of this report are Sushmita dashboard Samaddar, Research Analyst, Luiza Cardoso De Andrade, Junior Data Scientist, and Serena Sara Daniela Cocciolo, Young Professional and their contribution is greatly appreciated. The support locally provided by Marko Balenovic, Program Assistant and Ema Banic, Team Assistant are also acknowledged. December 2021 The project was funded by the European Union via the Structural Support Reform Programme and implemented by the World Bank in cooperation with the European Commission’s Directorate General for Reform Support (DG REFORM). This project is funded by the European Union DISCLAIMER The project and respectively this report was funded by the European Union via the Structural Support Reform Programme and implemented by the World Bank in cooperation with the European Commission’s Directorate General for Reform Support (DG REFORM). The views expressed herein can in no way be taken to reflect the official opinion of the European Union. This report is a product of the staff of the World Bank Group with external contributions. The findings, interpretations, and conclusions expressed in this work do not necessarily reflect the views of The World Bank Group, its Board of Executive Directors, or the governments they represent. The World Bank Group does not guarantee the accuracy of the data included in this work, which is drawn from multiple external sources. The boundaries, colours, denominations and other information shown on any map in this work do not imply any judgement on the part of the World Bank Group concerning the legal status of any territory or the endorsement or acceptance of such boundaries. Nothing herein shall constitute, or be considered to be, a limitation upon or waiver of the privileges and immunities of The World Bank Group, all of which are specifically reserved. RIGHTS AND PERMISSIONS The material in this work is subject to copyright. However, because The World Bank encourages dissemination of this knowledge, this work may be reproduced, in whole or in part for non-commercial purposes, as long as full attribution to this work is given. Any queries on rights and licenses, including subsidiary rights, should be addressed to World Bank Publications, The World Bank Group, 1818 H Street NW, Washington, DC 20433, USA; fax: 202-522-2625; e-mail: pubrights@worldbank.org. ACKNOWLEDGMENTS The main author of this report is Serena Sara Daniela Cocciolo (Economist, World Bank) and her contribution is greatly appreciated. The interactive dashboard was developed by Sushmita Samaddar (Data Analyst). The support locally provided by Ema Banic, Team Assistant is also acknowledged. 2 Table of Contents 1 Introduction ........................................................................................................................ 4 2 Structure and content of the interactive presentation ......................................................... 5 2.1 Overall structure .......................................................................................................... 5 2.2 Filters for interactive visualizations of the findings .................................................... 5 2.3 Inputs for reporting...................................................................................................... 7 3 Design of the interactive presentation................................................................................ 8 3.1 Preparation of the datasets linked to the presentation ................................................. 8 3.2 Editing the dashboards ................................................................................................ 9 4 Capacity building and transfer of the tool........................................................................ 10 4.1 Knowledge transfer ................................................................................................... 10 4.2 Transfer of the tool .................................................................................................... 11 5 Conclusion ....................................................................................................................... 12 Annex A: Interactive Presentation ........................................................................................... 13 List of Tables and Figures Figure 1: One of the dashboards in the “Time Delivery” story ................................................. 5 Figure 2: Use of the products filter in one of the dashboards in the “Cost efficiency” story .... 6 Figure 3: Use of the entity filter in one of the dashboards in the “Cost efficiency” story ......... 7 Figure 4: Summary table in one of the dashboards in the “Fairness” story ............................... 8 Figure 5: Datasets linked to the workbook ................................................................................ 9 Figure 6: Editing the style of a graph included in one of the dashboards in the “Overview” story .................................................................................................................................................. 10 Figure 7 : Snapshot of the dashboard created during the training session ............................... 11 3 1 Introduction The general objective of the project “Measuring Performance of Public Procurement in Croatia” is to support the Croatian authorities to improve their administrative capacities to design and implement public projects and policies, through a strengthened public procurement system. The project outcome is that the Ministry of Economy and Sustainable Development (MoESD) has increased capacity to effectively assess and monitor the performance of public spending in Croatia and to make informed procurement decisions and policy interventions. For the realization of the project outcome, the World Bank implemented and delivered the necessary activities for the achievements of the following outputs: - Output 1: Report on the analysis of the effectiveness of the public procurement system in Croatia and proposed actionable recommendations on strengthening the public procurement system. - Output 2: Report on the presentation of the results of the procurement and contract data analysis through a web-based interactive dashboard. - Output 3: Training and report on training provided on the application of the proposed procurement and contract data analytics methodology. Under Output 2, the World Bank prepared an interactive presentation of the findings from the data analytics based on the e-procurement data. The presentation includes 28 dashboards, organized around the dimensions and performance indicators indicated in the project description and ToR. Each dashboard includes a variety of relevant filters for narrowing the findings down to specific situations and flexibly visualize the data and analysis results. As indicated in the ToR, the interactive presentation has been developed in Tableau. It can be consulted through the Tableau Reader application, and it can be uploaded on a dedicated website once obtained a Tableau license and server. The interactive presentation was demonstrated to MoESD, Narodne Novine and EC on June 9, 2021, and it was well accepted by all these stakeholders. The presentation is delivered as Annex to this report, as a Tableau packaged workbook. This report corresponds to Output 2 of the project. Specifically, it describes the structure and content of the interactive presentation (Section 2), the presentation design (Section 3), and the steps for capacity building and transfer of the tool (Section 4). Section 5 concludes. 4 2 Structure and content of the interactive presentation 2.1 Overall structure The interactive presentation consists of a Tableau packaged workbook. The workbook consists of four stories: Overview; Cost Efficiency; Time Delivery; and Fairness. Each story corresponds to a dimension of the analysis as indicated in the project ToR, and each story contains various dashboards, each of them corresponding to one indicator of interest as indicated in the project ToR. For example, as illustrated in Figure 1, the “Time Delivery” story contains 6 dashboards visualizing four indicators: bid submission period; decision period; total processing time; and process failures. Figure 1: One of the dashboards in the “Time Delivery” story 2.2 Filters for interactive visualizations of the findings Each dashboard contains various filters, which allow to flexibly and interactively interrogate the data. For example, all dashboards in the “Time Delivery” story allow to filter the analysis, tables and aggregate statistics by the following filters: procurement method; evaluation method; procuring entity; product; contract value group; year of process initiation; procurement type; and buyer location (Figure 1). All results and analysis visualized in the dashboard are dynamic, and therefore they are responsive to the specific selections of filters. This is one of the main advantages of the 5 interactive presentation, since it allows to dig deeper into specific focus areas depending on the specific interests of each user. For example, the “CPV Division Name” filter is useful for an in-depth analysis of specific products or sectors. Figure 2Figure 7 and Figure 3 provide examples where the user is interested to investigate in details the indicators associated to, respectively, purchases of construction work products and purchases by Grad Zagreb. The use of filters can be combined, for example to show indicators for a specific entity, for a specific product category, and for a specific procurement method. This allows a fully user-driven interaction with the data and analysis results, and ensures that the interactive presentation can effectively serve multiple users with a variety of objectives and focus areas. Figure 2: Use of the products filter in one of the dashboards in the “Cost efficiency” story 6 Figure 3: Use of the entity filter in one of the dashboards in the “Cost efficiency” story As explained in detail in Section 3.1, the appropriate level of the analysis varies across indicators and dashboards. For most indicators, the analysis is at contract level, but for some indicators that appropriate level of the analysis is at process level or product-entity level. Therefore, the filters differ across dashboards, based on the appropriate level of the underlying datasets and of the analysis. For example, the “Market Concentration” indicator is defined at the product-entity level, and therefore in the “Market Concentration” dashboard does not include the contract level or process level filters, such as the evaluation method and the procurement method. 2.3 Inputs for reporting Each dashboard includes aggregate statistics, graphs and tables that can be easily extracted and used for reporting purposes. Aggregate statistics are highlighted in boxes, such as the average bid submission period as shown in Figure 1. The dashboards can be exported in various formats, as well as each graph as an image file. Some dashboards present summary tables of performance indicators at entity or product level, which can be sorted over selected indicators as well as exported as an image or excel table (Figure 4). All elements included in the dashboard are dynamic, and therefore respond to the selections inputted in the filters. 7 Figure 4: Summary table in one of the dashboards in the “Fairness” story 3 Design of the interactive presentation 3.1 Preparation of the datasets linked to the presentation The statistics, graphs and visualizations shown in the interactive presentations are constructed in Tableau from the datasets (in excel or csv format) linked to the workbook. These datasets have been created from the raw datasets provided for this project, after the necessary cleaning and processing steps. As shown in Figure 5, three datasets are linked to this workbook: a process level dataset, a contract level dataset, and a product-entity level dataset. As also discussed during the trained delivered to selected staff of MoESD and Official Gazette, a critical step for data analytics is to identify the level of observation in a dataset and the appropriate level for constructing indicators. For example, the indicator “number of bidders” represents a characteristic of a given tender, not the contract, and therefore this indicator should be constructed on the process level dataset. Instead, the indicator “market concentration” represents a characteristic of a given market, not the tender or a contract, and therefore this indicator should be constructed on the product-entity level dataset. Each dashboard can be linked to a specific dataset, or to multiple datasets connected to the workbook. 8 Figure 5: Datasets linked to the workbook 3.2 Editing the dashboards There are two possible ways to edit the dashboards. First, it is possible to change or add data visualizations from the datasets already attached to the workbook. Second, it is possible to change both the datasets connected to the workbook, as well as the data visualizations. In order to edit the dashboards, it is necessary to unhide all sheets under a given story and a given dashboard. Each dashboard is created from various worksheets, which can be edited. Each worksheet indicates with a blue sign the dataset(s) used in that worksheet and it various elements can be edited, such as the graph style used for a given analysis, as shown in Figure 6. It is also possible to edit the datasets connected to the workbook. The interactive presentation is shared as a packaged workbook, meaning that both the Tableau visualizations and the connected datasets are shared as a unique package. The datasets connected to the workbook can be downloaded in local folders by users, edited and reconnected to the workbook by updating the existing connections. 9 Figure 6: Editing the style of a graph included in one of the dashboards in the “Overview” story 4 Capacity building and transfer of the tool 4.1 Knowledge transfer For the realization of the project Output 3 “Report on training provided on the application of the proposed procurement and contract data analytics in Stata”, the World Bank provided training to selected staff of MoESD and Official Gazette on the procurement and contract data analytics methodology used for the analytical report (Output 1) and for the interactive presentation (Output 2). During the week-long training (from 18th October 2021 to 22nd October, 2021), the World Bank provided knowledge and skills development in: (i) preparing and cleaning periodical procurement and contract data in electronic format; (ii) writing codes in Stata; (iii) analysing procurement data using Stata; and (iv) visualizing the results in Tableau. This training was conducted face-to-face with five government officials (three from the Directorate of Public Procurement and two from Nardodne Novine). The training included lectures and interactive lab sessions where the participants practiced data analysis and STATA coding skills on procurement data. In the segment on “Data visualization in Tableau”, the participants were shown how to produce data visualizations on Tableau. This segment was conducted in a lecture style as the participants did not have the Tableau software on the laptops used for the training. This lecture was recorded to ensure that the participants could refer to it when they download the Tableau software to their laptops and start practicing with it. The lecture style session went over the 10 tasks undertaken in Tableau to produce a dashboard, specifically how to open a dataset, how to create different types of charts, the kind of data fields that are recognized in Tableau, how to customize charts and how to build a dynamic dashboard using charts and data filters. Figure 7 provides a snapshot of the dashboard created during the training session. Figure 7 : Snapshot of the dashboard created during the training session 4.2 Transfer of the tool The interactive presentation is shared as a packaged workbook (“.twbx” file), including the Tableau visualizations and the datasets connected to the dashboards. This allows users to interact with the dashboards at different level of complexity, from consulting the analysis results to editing the analysis, the underlying datasets or publishing the workbook on a dedicated webpage. Users with the Tableau reader app (which can be installed for free) will be able to open the twbx file and consult the interactive presentation, but without being able to edit it. Users with the Tableau desktop all will be able to open the twbx file, consult the interactive presentation, and edit it as explained in Section 3.2. The interactive presentation can be published on a dedicated webpage, made publicly available or accessible to only selected users. This requires the purchase of Tableau server or Tableau online, and the necessary license for hosting in the cloud. These various options and associated costs are described on the Tableau website. 11 5 Conclusion The objective of Tableau dashboard is to provide an intuitive, flexible and interactive tool for the presentation of the empirical findings from the analysis of the e-procurement data used for this project. The Tableau dashboard has been developed by the World Bank and refined through various interactions with the government counterparts. A training to selected staff of MoESD and Official Gazette has been provided to transfer knowledge on how to build, edit and consult dashboards in Tableau. The interactive presentation is shared as a Tableau packaged workbook, and the World Bank remains available to provide further support for implementing expansions of the tool, for additional capacity building, and for the publication of the dashboards on a dedicated webpage. 12 Annex A: Interactive Presentation Annex A: Interactive Presentation Croatia Dashboard_Output2n_20211216.twbx Croatia Dashboard_Output2n_2 0211216 File created on: 12/17/2021 6:17:55 PM This presentation shows the public purchases carried out by the Croatian government between 2012 and 2020. The dashboard is organized by the following topics (See tabs on the bottom of the screen): 1) Overview of the public procurement data; 2) Cost Efficiency; 3) Timely Delivery; 4) Fairness - Hover your mouse over the graphs to access more detailed information. - Use the filters to disaggregate information by different categorical variables. - Click on the panels on the top to move across analysis within each topic - Click on the tab on the bottom to move across topics 13 14 15 16 17 18 19 20 21 22 23 24 Average number of new bidders Average proportion of new bidders Average winning rate Average winning rate of new supplier 25 26 27 APPENDIX B Some of the additional fraud, waste and abuse schemes and inefficiencies that Red Flag Indicators can detect or prevent include: o Failure to collect entitled rebates and discounts o Failure to comply with procurement regulations and best practices o Change order abuse o Co-mingled contracts o Duplicate payments o Exclusion of qualified bidders o Failure to meet contract specifications o False or inflated invoices o Front loading of contract expenses o Improper sole source awards o Leaking of bid data o Manipulation of bids o Overpayments o Rigged specifications o Split purchases o Unbalanced bidding o Unnecessary and excessive purchasing o Product substitution The project was funded by the European Union via the Structural Support Reform Programme and implemented by the World Bank in cooperation with the European Commission’s Directorate General for Reform Support (DG REFORM). This project is funded by the European Union 147 29