Cohort 3

Create Excel-based automation processes using Power Query and Dynamic Arrays to revolutionise your month end.

Giles Male

Co-Founder - Full Stack Modeller

Month End Excel Automation Bootcamp

If the answer to that question is "Yes!" then this bootcamp is for you. I'll introduce you to Power Query, an Excel feature that will change your world. Then I'll introduce you to Dynamic Arrays, and show you how they can add value to real-world month end challenges. This is a hands-on course, with plenty of practical experience offered throughout. Your class mates and I will be there every step of the way to support you. You didn't train all those years as a finance professional to just copy &amp; paste numbers all day. Let me give you your time back, so you can do the real value-adding work you are capable of. * Please note, you must have an Office 365 subscription or Excel 2021, and be working on a Windows computer (not a Mac) to get the best experience from this bootcamp. ** This bootcamp includes one month of full access to the Full Stack Modeller training platform and community, giving students the opportunity to take advantage of ALL of our additional training content.

Are you stuck running soul-destroying, manual month end processes in Excel?

Finance professionals involved in the month end process

Excel users who manage data on a regular basis

Anyone with an interest in Excel-based automation

Excel users with little to no experience using Power Query and Dynamic Arrays

Who is this course for

What you will learn

<ul><li>Import data from various sources into the Power Query Editor</li><li>Create clear, effective, auditable queries </li><li>Transform and enrich data</li><li>Best practice principles</li></ul>

The fundamentals of Power Query

<ul><li>The dynamic array functions</li><li>Common uses</li><li>More advanced uses</li><li>Common mistakes</li></ul>

The fundamentals of Dynamic Arrays

<ul><li>Import month end Trial Balance data from multiple files</li><li>Transform and enrich your TB data in the PQ Editor</li><li>Load data back into a standard month end variance report</li><li>Create an accruals model with automated monthly journal entry calculations &amp; journal upload outputs</li></ul>

Real-world application of Power Query & Dynamic Arrays

<ul><li>The difference between an Excel User &amp; Excel Developer</li><li>Scoping &amp; managing projects</li><li>The value of being pedantic</li><li>Tips &amp; tricks to maximise your effectiveness when gathering information</li></ul>

The Developer Mindset

Power Query allows Excel users to build automated and repeatable ETL processes (Extract, Transform, Load) without writing a single line of code. We'll walk through the key features of the Power Query Editor, go through some simple exercises to help you set up your first functioning queries, and guide you through some best practice PQ principles.

Session 1 - Power Query

Dynamic Arrays&nbsp;are resizable arrays that calculate automatically and return values into multiple cells based on a formula entered in a single cell. They open up a whole new world of possibilities to Excel users. We'll walk through the fundamentals of DAs, we'll look at the most useful DA functions, and we'll run through some common mistakes.

Session 2 - Dynamic Arrays

We'll work through two comprehensive case studies, step by step, that fully automate key steps within a typical month end process. Files will be saved in a folder, and Power Query will do all the hard work to turn that data into valuable insights, and we'll use Dynamic Arrays to manipulate our data ready for further use.

Session 3 - Case Studies

It's important to think beyond the spreadsheet; to be more than just a spreadsheet problem solver. In this closing session we'll talk through the differences between a typical Excel User and a Developer, we'll talk about scoping and managing projects successfully, and we'll discuss the positive value of being really, really pedantic in your work!

Session 4 - The Developer Mindset

Session Breakdown

What people are saying

Meet your instructor

Four two-hour training sessions, plus two optional one-hour Open Sessions on a Friday

Tuesdays & Thursdays, Optional Fridays

Practice workbooks and case studies will be provided for students to work through between sessions, if they choose to

Optional Homework

First Session: Tuesday 2nd July

Course schedule

This course builds on live workshops and hands-on projects

Active hands-on learning

You’ll be interacting with other learners as you work through the course material

Interactive and project-based

Join a community of like-minded people who want to learn and grow alongside you

Course completed by

About this course

Get notified about the next cohort

Learn from live sessions