NATIONAL AGENCY FOR THE CONTROL OF AIDS (NACA)-REQUEST FOR PROPOSAL FOR ENGAGEMENT OF DATA ANALYTICS (ADVANCED EXCEL AND SPSS) TRAINING FOR PUBLIC HEALTH SUPPLY CHAIN STAKEHOLDERS IN NIGERIA SuperAdminRequest for Proposal / QuotationAugust 25, 2023NATIONAL AGENCY FOR THE CONTROL OF AIDS (NACA)ENGAGEMENT OF DATA ANALYTICS (ADVANCED EXCEL AND SPSS) TRAINING FOR PUBLIC HEALTH SUPPLY CHAIN STAKEHOLDERS IN NIGERIANigeria is a top-priority country within the Global Fund (TGF) portfolio. It faces an extreme disease burden across all three diseases, with trailing health outcomes. The country has the world’s greatest malaria burden and has the second largest burden of both HIV and TB in Africa. The country is classified as a high MDR-TB burden country.The current HIV, Malaria and TB grants (2021-2023) seeks to build on the results achieved through previous grants while contributing directly to the attainment of the National HIV, TB and Malaria Strategic Plan goals and objectives by expanding access to prevention, treatment, care, and support interventions for both general and key populations. The National Supply Chain Strategy provides an overarching framework to guide investments to improve access to essential medicines and medical supplies to support the health programs.Performance management and data analysis are key components of monitoring and evaluation and enables organizations track program implementation, routinely monitor expected changes over time and to evaluate the overall performance of the programs. The establishment of the National Health Logistics Management System has improved end to end visibility of the public health commodities. Additionally, data from the NHLMIS is used for planning and programming in the Supply Chain Program. The effective management and monitoring of Key Performance Indicators in the NHLMIS database, as well as triangulation of service data from the DHIS, is a critical gap that is needed by the NPSCMP data management, IT and M&E unit to effectively manage and analyze huge volumes of data contained in these databases. In order to translate data usage to improved Supply Chain Outcomes in-country, it is necessary to improve the skills required for effective data management.As such building skills in data analysis using statistical software like STATA and SPSS is essential to analyze data and draw inferences from the derived data. Therefore, the Global Fund is seeking to engage a service provider to design and deliver a structured and tailored Data analytics (Advance Excel and SPSS) training for selected public health supply chain personnel (NPSCMP, program and states).1. ObjectivesThe service providers will work under the directives of the National Coordinator, through the relevant technical leads in NPSCMP to:• Conduct capacity assessment to determine data analytics/Excel capacities.· Design a tailored data analysis (Advanced Excel and SPSS) training curricular/manual for public health supply chain stakeholders• Coordinate and deliver Data analysis (Advance Excel and SPSS) training sessions for public health supply chain stakeholders2. DeliverablesThe expected deliverables include:· Report on capacity assessment detailing capacity gaps and training needs· Data analysis (Advance Excel and SPSS) training curriculum/manual· Training report3. Timeline: given the urgency to rapidly implement the planned RSSH grant activities to realize benefits during the current implementation period, we expect the selected Service Provider to begin work plan implementation by October 2023 for a period of 30-40 working days. This support is deliverable-based and a contract payment plan that will be agreed with Global Fund, NACA and NPSCMP at the start of implementation. The exact start and end dates of work plan implementation may vary slightly.4. RequirementsThe selected Service Provider will be an IT expert with the following qualifications and experience:· At least a bachelor’s degree in information management, research management, data analytics or related field from a recognized university A relevant Masters’ degree together with at least 10 years’ experience in information management (alternative)· Relevant IT certifications such as Oracle certification, Microsoft certification or similar software development certification· At least ten (10) years’ experience conducting similar trainings in Advanced excel, SPSS and other data analytics packages.· Experience working with the Global Fund, major donors, Principal recipients and/or other partners (USG, USAID, World Bank, BMGF, multinationals etc.) of diverse backgrounds.· Good operational experience handling data, conducting advanced analytics using diverse applications, including MS Excel.· Ability to communicate effectively in a cross-cultural environment both oral and written in English.5. Technical DirectionThe selected Service Provider will work under the overall guidance of the NACA and NPSCMP, with remote support from The Global Fund Supply Chain Team and the Nigeria Country Team.6. Scope of trainingProposed Content1. Introduction(What is SQL? Purpose of SQL, who should learn SQL? What are the subsets of SQL? Data Definition Language, Data Manipulation Language, Data Control Language, and SQL vs. NoSQL)2. Introduction to Databases and RDMBS(What is a Database? Database Objects, Database Tables, Table Records, Types of Database Management Systems, Relational Database Management Systems, and SQL/Relational Databases vs. No SQL Databases)3. Install a Database Engine(Download MS SQL Server or Oracle or MySQL Database Engine, and Install. Launch SQL Server Management Studio, Select New Query, and launch SQL Query. Type SQL Commands and Execute.)4. SQL Syntax(Focus on SQL Syntax, SQL keywords, SQL is not case sensitive, SQL Comments, SQL Commands, and writing SQL Statements.)5. SQL Data Types(SQL Numeric data types, Date and Time data types, Character and String data types, Unicode character string data types, Binary data types, and Miscellaneous data types.)6. SQL Operators(SQL Arithmetic Operators, Comparison Operators, Logical Operators, and Bitwise Operators)7. SQL Expressions(SQL Boolean Expression, SQL Numeric Expression, and SQL Date Expression)8. SQL Comments(SQL Comments, Comments are used to explain sections of SQL statements, or to prevent the execution of SQL statements. Single-Line Comments, and Multi-line Comments)  9. SQL – Data Definition Language Commands and Operations.(SQL Data Definition Language Commands, Create, Alter, Drop, Truncate, and Rename. Data Definition Language Operations, create a Database, Use Database, rename a Database, Drop Database, create a Table, Rename Table, add a Column to exiting Table, add multiple columns to existing Table, modify an existing column, rename a Column, drop a Column, truncate a Table, and Drop a Table.)10. SQL – Data Manipulation Language Commands and Operations(Data Manipulation Language Commands, SELECT, INSERT, UPDATE, and DELETE. Data Manipulation Language Operations, retrieving data from a table, inserting data into a table, updating existing data into a table, and deleting all records from a table.)13. SQL – Data Control Language CommandsDCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions, and other controls of the database system.14. DCL Operations(Providing the users, the access or privileges to the database objects, and taking back or cancelling the privileges or permissions previously allowed or denied to the users.)15. SQL FunctionsSQL has many built-in functions for performing calculations on data. SQL Aggregate Functions, SQL String Functions, SQL Date Functions, and SQL Scalar functions.16. SQL Queries and Sub QueriesA Query is used to traverse over some data that may be of small or large quantity to find the needed information.17. SQL ClausesClauses in SQL are similar to conditionals in high-level languages. We have a large variety in the SQL clauses like the Where clause, Union Close, Order By clause, etc.18. SQL JoinsThe SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.  19. SQL ViewsViews in SQL are kind of virtual tables. A view also has rows and columns as they are in a real table in the database. We can create a view by selecting fields from one or more tables present in the database.20. SQL IndexesAn index is a schema object. It is used by the server to speed up the retrieval of rows by using a pointer. It can reduce disk I/O(input/output) by using a rapid path access method to locate data quickly.21. SQL TransactionsTransactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of database program.22. SQL InjectionSQL injection, also known as SQLI, is a common attack vector that uses malicious SQL code for backend database manipulation to access information that was not intended to be displayed. This information may include any number of items, including sensitive company data, user lists, or private customer details.        PYTHONAdvanced ExcelPython language basics, IPython, and Jupyter notebooksUsing logical functionsBuilt-in data structures, functions, and filesThe IF functionNumPy basics: arrays and vectorized computationCreating nested IF functionsGetting started with pandasThe IFS functionData loading, storage, and file formatsAND and OR functionsData cleaning and preparationCombining logical functionsData wrangling: join, combine, and reshape Plotting and visualizationWorking with LookupsData aggregation and group operationsThe VLOOKUP functionTime seriesThe HLOOKUP functionAdvanced pandasThe XLOOKUP function (365)Introduction to modeling libraies in PythonINDEX & MATCHData analysis examples Advanced NumPyData ValidationMore on the IPython system.Using data validation Setting criteria for entry of text, values and dates Creating a dropdown list   PivotTables and PivotCharts Creating and rearranging PivotTables Using the PivotTable toolbar Creating PivotCharts Using Slicers to manipulate PivotTables   Using analytical options Using Goal Seek Creating, saving and viewing scenarios Creating single input data tables Creating double input data tables Projecting with data tables   Macros Creating macros Using the macro recorder Viewing and editing macro-VBA code   Mail Functions – VBA                                                                                                • Using Outlook Namespace • Send automated mail • Outlook Configurations, MAPI • Worksheet / Workbook Operations Merge Worksheets using Macro• Merge multiple excel files into one sheet• Split worksheets using VBA filters• Worksheet copiers1. DURATION OF CONSULTANCYThe consultant shall work over a period of 30-40 days2. REMUNERATIONHe/she will be paid after confirmation of completion of job by the userEstimated Number of persons to be trained- 35Number of Consultant Required-One (1)Location of Training- Nassarawa6. Documents:While submitting the Technical Proposal, the Applicant shall, in particular, ensure to attach the following:– Profile of the consultant (max 1 page) explaining why they are the most suitable for the work.– Copies of all certificates for all qualifications indicated in the CV.– Relevant Experience (max 2 page) and relevant supporting document.– Detailed methodology and conceptual framework with expected deliverables and timelines, man days required (3-5 pages).– Recent CVThe financial proposal shall specify a total lump sum amount (including a breakdown of costs for fee, travel and number of working days). Payments will be made in installments based upon key outputs/deliveries (mentioned under payment schedule above).7.`COLLECTION OF REQUESTS FOR PROPOSAL (RFP) DOCUMENTThe Request for Proposal (RFP) can be downloaded from this link;https://naca.gov.ng/wp-content/uploads/2023/08/RFP-Data-Analytic-Consultant-Clean-copy.pdf8. GUIDELINE FOR SUBMISSION AND OPENING OF DOCUMENTSThe detailed Proposal must be in English Language and submitted in two (2) hard copies each (one original & One copy) of the requested documents and proposal. The documents shall then be enclosed and sealed in one (1) single outer envelope, and clearly marked at the topmost right corner “Engagement of Data Analytic Consultant” and the Name, Address, Email and Phone Number of the Consultant boldly written at the back of the envelope, with acknowledgement to NACA office at the address below.Deadline for Submission: The deadline for the submission of proposal should not be later than 12 noon of 4th September 2023. The submission(s) received will be opened immediately after closing at NACA Conference Room in the presence of consultants that choose to attend.9. ADDRESS FOR INFORMATION AND SUBMISSION OF PROPOSALSAttention:Head ProcurementNational Agency for the Control of AIDS (NACA)Ground Floor Room 1.08No. 3 Zinguinchor Street, Beside AEDC Office, Wuse zone 4, AbujaOREmail: [email protected]10. GLOBAL FUND CODE OF CONDUCTYou shall get acquainted with the global fund code of conduct for suppliers using this link below: https://www.theglobalfund.org/media/3275/corporate_codeofconductforsuppliers_policy_en.pdf. http://www.theglobalfund.org/en/oig/Accepting this invitation shall serve as an acknowledgement and agreement to abide by the Global Fund Code of Conduct for Suppliers.If the circumstances are such that reporting a suspicion as above is inappropriate, or if the person to whom it is reported is unable to assist, the issue may be reported to the NACA confidential helpline. This helpline is monitored by the Internal Auditor and is accessed as follows E-mail: [email protected] Free Telephone Reporting Service: +1 704 541 6918 Telephone Message – 24-hour voicemail: +41 22 341 5258 Fax – Dedicated fax line: +41 22 341 525710. NOTES/DISCLAIMERi. Late submissions will not be accepted.ii. NACA shall verify any or all documents and claims made by applicants and will disqualify consultants with falsified documents and claims.iii. If it is determined that submitted documents and claims have been falsified, the consultant may face prosecution in a court of Law.ii. NACA shall not be held responsible for any disqualified proposal as a result of any omission or deletion relating to the submission guidelines.iii. This advertisement shall not be construe a contract to any Consultant, nor shall it entitle any Consultant submitting documents to claim any indemnity from NACA.iv. NACA is not bound to shortlist any Consultant, and reserves the right to annul the bidding process at any time without incurring any liabilities or providing reason.SignedManagement. NGO Procurement Portal