Data and AI Training

Home | Prices | Contact Us | Courses: Power BI - Excel - Python - SQL - Generative AI - Visualising Data - Analysing Data

Automation for Finance Staff Course

Who should attend

Finance staff who find themselves spending time repeating manual procedures on a regular basis.

Course Length

1 day

Learning Objectives

This course helps finance staff to become more productive by automating manual tasks or processes that are repeated on a regular (daily or monthly) basis. The benefits are to:

• free up time for other more useful activities
• increase the robustness of the tasks
• reduce the likelihood of errors occuring in the tasks
• allow easier handover of tasks when new staff join

Since finance staff do much of their work in Excel, this courses focuses on Excel case studies, for example,

• importing data into Excel
• cleaning data
• reconciling data, e.g., purchase orders with invoices
• summarising data
• report generation

Pre-requisites

Basic Excel knowledge, for example gained in by attendance of the Excel foundation course.

Course Content

The course covers three techniques to automate Excel tasks: Power Query, the Excel formula language and Excel tables.

Power Query can import and clean data so that is is ready for anlaysis and reporting. Finance staff automate a set of tasks using the Power Query user interface. These steps form a macro that can be repeated on demand. Power Query is useful to

• import data from any source: another Excel sheet, text file, database
• clean the data, e.g., removing headers and footers that are not part of the data,
• reshape the data, e.g., pivot or unpivot, merge or stack data
• find and fix bad data such as missing valeus and outliers.

The Excel formula language provides calulations and uses functions such as SUM. It is very powerful, and much improved over the last few years. The course will cover some typical challenges that finance staff face and show how to solve these with formulas, for example:

• prepend a fixed string, say “XYZ-“, in front of a column of numbers
• remove the first 4 characters of a string (such as an invoice reference)
• extract the text after in a string after a specific (set of) characters
• remove any leading (or trailing) spaces from a string

Excel tables are very useful whenever our Excel spreadsheets contains data in tabular format (arranged in rows and columns). It allows us to write formulas that do not need to change when the data expands or shrinks, for example if the data is updated on a daily basis. It also allows us to write formulas and build pivot tables more easily.