Short course
Advanced Excel for project monitoring and evaluation
In most projects, monitoring and evaluation exists on paper but not in the files. The logical framework is in one document, the targets in another, field data in a series of workbooks that differ from one area to the next, and the quarterly report is put together in a rush, by copying and pasting, with achievement rates recalculated by hand and rarely consistent between two reports.
- Duration
- Short module, 5 sessions of 3 hours, 15 hours
- Format
- In person or online
- Credential
- Certificate of attendance
Tuition75,000 XOF
By the end of the programme, you will be able to
- Turn a logical framework into a performance measurement framework usable in a workbook
- Formulate measurable indicators and write their full reference sheet
- Build a single indicator database fed by field data
- Set baselines, annual targets and cumulative targets
- Automatically calculate achievement rates, implementation rates and variances
- Systematically disaggregate results by sex, age, area and beneficiary category
- Track beneficiaries and avoid double counting
- Track the physical and financial implementation of a costed annual work plan
- Build a performance dashboard readable by management and the donor
- Automate the production of tables and charts for the periodic report
- Prepare data for a mid-term or final evaluation
- Use artificial intelligence to formulate indicators, clean incoming data and draft performance commentary
Who it is for
Target audience
The module is for monitoring and evaluation professionals and those who depend on them: M&E officers and managers, MEAL focal points, project managers and coordinators, programme officers, field staff responsible for reporting data, donor reporting officers, evaluation consultants, staff of decentralised technical services.
Prerequisites
Two requirements:
- Know the basic concepts of a project: objectives, results, activities, indicators
- Have a laptop, or rent one from the centre for the duration of the module
Each participant is invited to bring the logical framework and monitoring data of their real project: the system built in class is the one for their project, and they take it home. A complete fictitious project is provided for those who have no data available.
Detailed program
5 sessions · 15 h of teaching
- What a logical framework says and does not say, and what must be added to measure
- The results chain: activities, outputs, outcomes, impact, and the right level of indicator
- Formulating a measurable indicator, and formulations that measure nothing
- The indicator reference sheet: definition, calculation formula, unit, source, frequency, responsible person
- Baselines, annual targets, cumulative targets and end-of-project targets
- The structure of the performance measurement framework in a workbook
- Project reference lists: areas, partners, activities, beneficiary categories
- Drop-down lists and data validation to keep a clean workbook
- Protecting sheets and separating input from calculation
- Exercise: build the performance measurement framework for your own project
- The single database principle: one row per report, never one table per area
- Designing the field data reporting form
- Automatically consolidating reports from several areas or partners
- Quality control of the data received: completeness, consistency, outliers
- The beneficiary register and detecting double counting
- The distinction between direct, indirect, new and repeat beneficiaries
- Conditional calculation functions to aggregate by period, area and indicator
- Lookup functions to bring targets and baselines into the database
- Collection through an online form and linking it automatically to the workbook
- Exercise: build your project's indicator database and consolidate its reports
- Calculating the annual achievement rate, the cumulative rate and the rate against the end-of-project target
- The physical implementation rate of work plan activities
- The budget execution rate and comparing physical and financial implementation
- The costed annual work plan and its quarterly monitoring
- The activity tracking chart and spotting delays
- Traffic-light conditional formatting: achieved, in progress, delayed
- Systematic disaggregation by sex, age, area and beneficiary category
- Gender-sensitive indicators and tracking inclusion commitments
- Pivot tables as the engine of the performance report
- Variances and explaining them, what a donor really expects to see
- Exercise: calculate your project's achievement and implementation rates, with disaggregation
- The one-page performance dashboard: what management must see every month
- Choosing which indicators to show and which to leave in the appendix
- Suitable charts: progress towards target, trends, comparison between areas
- Slicers and timelines for an interactive dashboard by area and period
- A simple coverage map of intervention areas
- Automatically generating the tables for the quarterly and annual report
- Exporting to Word and the dynamic link that avoids copying and pasting
- The structure of a performance report and the sequence of tables
- Lessons learned, recommendations and tracking their implementation
- Preparing data for a mid-term or final evaluation
- The accountability mechanism and tracking beneficiary complaints and feedback
- Exercise: produce the dashboard and periodic report tables for your project
- What artificial intelligence brings to an M&E officer, and its limits
- Confidentiality of beneficiary data and anonymisation before any assisted processing
- Having indicators formulated and critiqued, then deciding for yourself
- Having indicator reference sheets drafted from the logical framework
- Having a complex achievement rate formula written and explained, then checking it
- Having heterogeneous field reports cleaned and harmonised
- Having inconsistencies and suspicious data detected in the reports
- Coding and summarising qualitative feedback and beneficiaries' open-ended answers
- Having a first draft of the performance analysis produced, to be systematically reworked
- Writing report commentary, lessons learned and recommendations
- Adapting the presentation to three audiences: management, donor, communities
- The mandatory check: recalculating every rate before including it in a donor report
- Automating data reporting reminders and the monthly dashboard email, without programming
- Final exercise: finalise your monitoring system, produce your performance report and present it to the group
- Presentation of attestations
Assessment and certification
The training attestation is issued to participants who have attended at least four of the five sessions and submitted their finalised monitoring system. It states the module title, the 15-hour duration, the dates and the skills acquired, and carries a unique number with a verification code.
Participants also receive:
- The monitoring system they built on their own project
- A complete model workbook: measurement framework, indicator database, dashboard, report
- Templates for indicator reference sheets, data reporting forms and beneficiary registers
- A costed annual work plan template with implementation tracking
- The complete course material in PDF, session by session
- A quick reference of Excel formulas useful for monitoring and evaluation
- A library of ready-to-use prompts for artificial intelligence assistants
Upcoming sessions
Next dates being prepared
Leave your email: we will let you know as soon as registration opens.
Frequently asked questions
75,000 XOF per participant. Training for a team or an organisation is quoted on request.
5 sessions of 3 hours, 15 hours.
Yes: you can attend onsite or online, as you prefer.
Two requirements: know the basic concepts of a project: objectives, results, activities, indicators ; have a laptop, or rent one from the centre for the duration of the module ; each participant is invited to bring the logical framework and monitoring data of their real project: the system built in class is the one for their project, and they take it home. A complete fictitious project is provided for those who have no data available.
A certificate of attendance is issued at the end of the course.
Fill in the online enrolment request: no payment is taken on the site. The firm reviews your application, then contacts you to confirm your place and the payment terms. No session is scheduled yet: leave your email on this page to be told when registration opens.
Join the next cohort
Cohorts are deliberately small to guarantee genuine individual support. Reserve your place or ask us your questions.