Consultancy – Technical Support for Finalizing Programming of Education Profiles in Excel VBA – Eastern and Southern Africa Regional Office (ESARO)

UNICEF works in some of the world’s toughest places, to reach the world’s most disadvantaged children. To save their lives. To defend their rights. To help them fulfill their potential. 

Across 190 countries and territories, we work for every child, everywhere, every day, to build a better world for everyone. 

And we never give up. 

                                                                         TERMS OF REFERENCE

Title of Assignment	Technical Support for Finalizing Programming of Education Profiles in Excel VBA
Hiring Section	Education
Location	Remote
Duration	25 working days within 6 Months
Start/End Date	From: 4/1/2024	To: 9/30/2024

Background and Justification

In collaboration with UNICEF Angola, UNICEF ESARO is supporting the Angolan Ministry of Education (MoE) to strengthen its use of education data under the umbrella of the Data Must Speak (DMS) initiative. Established in 2014, the goals of DMS are to improve equity and learning in education through:
• Improved management of education system through strengthening the use of data to inform planning and policy,
• Enhanced social accountability in education through greater citizen involvement, transparency and accountability to communities, and
• Improved global knowledge about what works (or doesn’t) for community engagement, in what context, and how to use data to improve equity and learning outcomes.

In Angola, UNICEF has supported the MoE to develop provincial, municipal, school, and community-level education profiles to feed user-friendly education data back to decentralized levels of the education system to inform planning and local engagement on education system performance. These profiles are being piloted in two municipalities in Huila Province in 2023, with the goal of future scale-up across Angola. The profile templates were developed collaboratively and a short-term international consultant was engaged by ESARO on a 20-day low-value contract from August – October 2023 to program an Excel Profile Builder which would replicate their production across schools, municipalities, and provinces and automate the population of data within them. The consultancy was required because the task of programming the profile templates requires specialized information technology and programming skills (including VBA) which are not available within the ESARO Education team at the depth and for the duration of time required to complete the activity.

The 20 working days allocated to the initial consultancy proved to be insufficient to complete the full profile builder. This was due to various factors, including:
• the complexity of the profiles and the programming they require
• the nature of the EMIS data that feeds into them, and the additional flexibility this required within the programming
• the limited availability of the Angola MoE to provide timely feedback to questions about data and design specifications
• the implications for the flexibility of the MoE recently changing the firm they contract their EMIS data with

As a result, a new follow-up consultancy is being initiated for technical support to complete the programming of the profile builder and provide related documentation, training, and trouble-shooting support in 2024. The content, timeline and deliverables of this new consultancy are based on finalized feedback from the MoE on their desired data and design specifications and the government’s new way forward for its EMIS data system and structure.

Scope of Work

1. Goal and objective: Under the supervision of the Education Specialist – Data and Research / Education Section, the consultant will build on the existing partly-completed Excel profile builder to finalize a Profile Builder Template that automates production of school, community, municipality, province, and national profiles.  The consultant will also develop materials and deliver training to build the capacity of UNICEF ESARO and Angola to support the MoE in Angola in effectively utilizing the Profile Builder.
2. Provide details/reference to AWP areas covered: This assignment contributes to the advancement of ongoing DMS technical support to countries, an activity identified under Key Intervention 5.4 in the ESARO Education 2023 workplan, which is to “support strengthening education information systems development (including EMIS and learning assessment systems) and utilization in the region (DMS, Positive Deviant component).”
3. Activities and Tasks: The consultant will be responsible for the following activities:

1. Finalise programming of primary school profile and primary community profile based on MoE clarifications and requests
2. Program the secondary school profile, secondary community profile, municipality, provincial, and national profiles
3. Complete testing of profile builder and profile outputs
4. Draft high-level user manual and prepare and deliver an initial training session on file use
5. Adjust the profile builder (Excel and VBA) for new data shapes from 2024 EMIS data collection onwards
6. Update and draft the full content of the user manual, documenting the programming completed, how it works, and how to update it when making future changes to the profiles
7. Prepare and deliver a remote training on use of the updated file to UNICEF and MoE staff to build their capacity on the advanced Excel programming utilized for the profiles
8. Ongoing: time to be allocated to meetings for updates, brainstorming, trouble-shooting etc

4. Work relationships:  The Education Specialist – Research & Data will oversee the assignment and provide guidance.  Some consultation with colleagues from UNICEF Angola and the Angolan MoE may also occur.

 

Output/Deliverables

Deliverables	Duration (Estimated # of days or months)	Timeline/Deadline
Tested programmed excel profile template with functioning VBA automation submitted	12 days	Within 1.5 months of contract start (May 15, 2024)
High-level user manual submitted and initial  training conducted	3 days
Updated profile builder submitted for 2024 data collection and beyond; Updated manual submitted and training conducted.	6 days	Within one month of confirmation of 2024 data model from MoE (July 15, 2024)
Final report of consultancy and ongoing support provided	4 days	September 30, 2024
TOTAL	25 days

 

Payment Schedule

The payment schedule is outlined in the table above and split between three time periods.

Please note that the final remuneration will be negotiated by HR.

 

Desired competencies, technical background and experience

1. Education:  An advanced university degree in Social Sciences with a major focus on statistics or economics, or equivalent.  A first University Degree in a relevant field combined with 2 additional years of professional experience may be accepted in lieu of an Advanced University Degree
2. Advanced expertise in Microsoft Excel and VBA programming required; previous experience developing automated monitoring tools for Data Must Speak or a related education initiative is highly preferred
3. A minimum of three to five years of relevant professional experience in fields relevant to education economics, data systems, research, and/or monitoring and evaluation
4. Familiarity and experience working with education indicators and Education Management Information Systems is highly preferred
5. Must exhibit the UNICEF Core Values of:

1. Care
2. Respect
3. Integrity
4. Trust
5. Accountability
6. Sustainability

1. Competencies: list the competencies that the consultant should have for the assignment:  Demonstrates self-awareness and ethical awareness, drive to achieve results for impact, Works collaboratively with others
2. Developing country work experience is considered an asset.
3. Fluency in English is required. Knowledge of Portuguese is an asset.

 

Administrative Issues

• The consultant/contractor is expected to be based remotely with no anticipated travel.  Ideal working hours would include availability between 10 am to 4 pm Nairobi time.
• Progress meetings will take place throughout the contract duration as needed.
• Selected candidates are subject to confirmation of fully vaccinated status against SARS-CoV-2 (Covid-19) with a World Health Organization (WHO)-endorsed vaccine, which must be met prior to taking up the assignment. It does not apply to consultants who will work remotely and are not expected to work on or visit UNICEF premises, programme delivery locations or directly interact with communities UNICEF works with, nor to travel to perform functions for UNICEF for the duration of their consultancy contracts.
• The selected candidate is solely responsible to ensure that the visa (applicable) and health insurance required to perform the duties of the contract are valid for the entire period of the contract.

Conditions

• No office space/equipment will be provided.
• As per UNICEF DFAM policy, payment will be made against approved deliverables. No advance payment is allowed unless in exceptional circumstances against bank guarantee, subject to a maximum of 30 per cent of the total contract value in cases where advances purchases, for example for supplies or travel, may be necessary.
• Individuals engaged under a consultancy or individual contract will not be considered ‘staff members’ under the Staff Regulations and Rules of the United Nations and UNICEF’s policies and procedures and will be entitled to benefits provided therein (such as leave entitlements and medical insurance coverage). Their conditions of service will be governed by their contract and the General Conditions of Contracts for the Services of Consultants and Individual Contractors. Consultants and individual contractors are responsible for determining their tax liabilities and for the payment of any taxes and/or duties, in accordance with local or other applicable laws.

Risks

The main risk is that the consultant will not deliver quality work on the deadlines required.  These will be mitigated by (1) recruiting a candidate who has the required technical skills to complete the assignment with quality and availability for the working days required, (2) implementing quality control of the deliverables, and (3) having weekly (and more regular if needed) meetings with the consultant to check on progress and agree on any adjustments required.

 

How to apply

Interested and qualified candidates should apply online using the button below. As part of their application, candidates should provide:

1. A cover letter that specifies how you meet the desired competencies, technical background and experience (no more than 2 pages)
2. A short CV (no more than 4 pages)
3. A financial proposal that should include the daily rate in USD for the assignment. (Any application without a financial proposal will not be considered)
4. 3 Referees.