Start a conversation ↗

Description

Power Query in Excel for Accounting: Controls, Reconciliations and Reporting

Automate accounting controls and reconciliations with Power Query in Excel

  • 2 days — 14 h
  • In-person or virtual
  • Intermediate
  • Up to 6 participants

Accounting teams spend considerable time reconciling files: trial balance against general ledger, bank statements against the books, payroll reports against posted entries. This work is done with lookup formulas and manual sorting, is repeated at every close, and leaves little usable evidence when a treatment has to be justified.

These two days apply Power Query exclusively to accounting and finance work: importing extracts from the accounting system, consistency controls, reconciliations and the production of recurring statements. The cases addressed are genuine close-process tasks rather than generic office exercises.

Learning objectives

  • Import accounting system extracts into Excel without manual reworking
  • Build automated consistency controls on a trial balance and a general ledger
  • Automate a reconciliation between two accounting data sources
  • Produce recurring close statements refreshed in a single operation
  • Document queries to justify treatments during a review or audit

What makes this programme different

The datasets used are anonymised accounting extracts rather than simplified fictitious data
A bank reconciliation is automated from import through to the statement of differences to be justified
The queries built are documented so that another team member can take them over

Programme

1Importing accounting data

Starting from raw extracts without touching them

  • Connecting to accounting system extracts and the file formats encountered
  • Handling page headers and break lines
  • Setting data types for amounts, dates and account numbers
  • Managing debit and credit columns and the sign of amounts
  • Loading as connection only and organising the workbook

2Controls and consistency of statements

Making the data reveal what is wrong

  • Balance checks and reconciliation between trial balance and general ledger
  • Detecting entries without a counterpart and accounts with an abnormal sign
  • Grouping by account, by journal and by period
  • Conditional columns to flag anomalies
  • Producing an exception report for follow-up

3Automated reconciliations

Comparing two sources without lookup formulas

  • Merging queries and choosing the join type
  • Reconciling bank statements with the accounting records
  • Assisted matching and handling date or amount differences
  • Reconciling third-party accounts and subsidiary ledgers
  • Producing the statement of unreconciled items

4Recurring statements and sustainability

Running the close with a single refresh

  • Building monthly reporting statements fed by the queries
  • Cost-centre allocation and grouping by analysis axis
  • Period-on-period comparison and variance tracking
  • Documenting queries and ensuring traceability of treatments
  • Handing over the workbook so another team member can take it on

Who is it for

Accountants, chief accountants, finance and accounting managers, management controllers and accounting firm staff.

Prerequisites

Confident use of Excel and familiarity with standard accounting statements such as the trial balance and the general ledger.

Dates & locations

24 scheduled dates between November 2026 and December 2027. Seats are confirmed in the order enquiries are received.

November 2026

December 2026

January 2027

February 2027

March 2027

April 2027

May 2027

June 2027

September 2027

October 2027

November 2027

December 2027

None of these dates suit you? We open additional sessions on request, and any programme can be run privately for your team.

Practical details

Before the programme
Online positioning questionnaire. Your development objectives are shared with the trainer, who tailors the practical case studies to your context.
Teaching methods
Theoretical input, workshops and practical case studies. Digital course materials and method sheets provided.
Assessment
Multiple-choice tests and role-play exercises. Assessment of learning at the start and end of the programme, with immediate and 60-day follow-up evaluations.
After the programme
One year of access to the e-learning platform. Self-assessment of the skills acquired and a 30-day follow-up session with your trainer.
How to register
Registration online or on the basis of a quotation.
Lead time
11 working days after confirmation of registration.
Accessibility
Accessible to people of determination. Contact our accessibility coordinator to design a suitable solution: contact@mpf-academy.ae
Start dates
Rolling intake: in addition to the scheduled sessions, this programme can start on request.