Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Excel: Automating Financial Reports with Power Query

Name: Excel: Automating Financial Reports with Power Query
Rating: 4.5 (143 reviews)

How To Simplify And Automate Financial Reporting using Excel and Power Query | Dynamic Financial Statements reports

Created byexcel eguru

Last updated 12/2021

English

What you'll learn

Build a solid understanding of Microsoft Excel and Power Query to develop your own automated financial reports
Streamline recurring day to day tasks to automate financial reports in a breeze
Strategize on data flows for report automation
Identify and manage raw data efficiently including cleansing and transforming messy data into report friendly database
Create dynamic reports by mastering PivotTables, dynamic formulas with INDEX, MATCH, SUMIFS functions and many more
Leverage on Power Query to expedite on data management activities
Wow your stakeholders with your interactive financial reports that will ease financial analysis
Work efficiently, save time, no more working overtime and late nights for mundane recurring tasks. Time saved could be best channeled to value adding tasks
Take actions to automate the financial reporting activities for a sustainable future!

Course content

5 sections • 22 lectures • 5h 1m total length

Introduction0:49
An introduction to the course on Microsoft Excel: Automating Financial Reports with Power Query. It sets out the overall training flow, practice files / resources provided and course contents of the course.

Load & Transform Data: Trial Balance – 3 Years Data12:22
We start the Case Study and the objective of Lecture 2 is manage the raw data (3-years Trial Balance data) used for financial reporting. You will learn how to load the 3-year trial balance data into Power Query for further cleansing and transformation. There will be tips and tricks and you will be able to see how easy and straightforward it is to simplify the process of managing raw data.
Load & Transform Data: GL Map7:54
In this lecture, we will handle the mapping table on General Ledger (GL). We will load and transform the mapping table into Power Query for further automation steps.
Merge Query: GL Map To TB_3Yr5:35
You will learn how to merge the GL Mapping table into the Trial Balance database. The process is a breeze with Power Query. No complicated formulas to remember, just a few steps and you will be able to have a complete and robust Trial Balance.
Load & Transform Data: Date Map17:08
In this lecture, you will learn how to build on a robust Date mapping table using Power Query. With just a few clicks in Power Query, you will be able to build a flexible Data mapping table that will ease the reporting process.
Merge Query: Date Map To TB_3Yr5:01
Lecture 6 is on merging the Date mapping table into the 3-year Trial Balance database. Once the database is completed, we are ready for Module 2 on Automating Reports using Pivot Tables.
Upload Data To Power Query

Creating Income Statement & Balance Sheet Using Pivot Table Method19:56
Creating Income Statement and Balance Sheet can be just a few clicks away with Pivot Tables as long as you have a good raw dataset in the right format. In part 1 of Module 1, you will learn how to create an Income Statement and Balance Sheet using Pivot Tables.
Creating Income Statement & Balance Sheet Using Pivot Table Method - Part 224:32
Lecture 8 teaches on how to build control elements into the Income Statement and Balance Sheet to ensure data integrity. In this lecture, you will also learn how to calculate monthly value movement from the raw data that was based on YTD numbers in Power Query.
Creating Income Statement & Balance Sheet Using Pivot Table Method - Part 38:43
Once the Income Statement and Balance Sheet are completed, it is time to learn how to format and beautify the reports to look professional. We will also learn how to easily expand the annual Income Statement and Balance Sheet into monthly report in just a few clicks.
Pivot Table Reports

Creating Income Statement & Balance Sheet Using Formula Method - Part 122:58
In Part 1 of Lecture 10, you will learn how to create an Income Statement using Functions and Formula such as SUMIFS, SUM, etc. You will appreciate that the development process is simple and the reports created are flexible for reporting purposes.
Creating Income Statement & Balance Sheet Using Formula Method - Part 29:08
In Part 2 of Lecture 10, you will learn how to create a Balance Sheet using Functions and Formula. You will also embed internal control tools to the reports to ensure data integrity.
Creating Cash Flow Statement - Indirect Method Using Formula Method - Part 112:21
The automation of Cash Flow Statement involves more advance formula. In this lecture, you will learn these advance formulas from the scratch and thereafter learn how to apply it to create a robust Cash Flow Statement.
There are 2 lectures on Creating Cash Flow Statement.
Creating Cash Flow Statement - Indirect Method Using Formula Method - Part 217:17
The automation of Cash Flow Statement involves more advance formula. In this lecture, you will learn these advance formulas from the scratch and thereafter learn how to apply it to create a robust Cash Flow Statement.
There are 2 lectures on Creating Cash Flow Statement.
Creating Notes To The Accounts: Fixed Assets & Depreciation - Part 114:00
The notes to the financial statements are a required, integral part of a company's external financial statements.
You will learn how to create an automated and dynamic Notes on Fixed Assets using Functions and Formula. You will learn how to create a Fixed Assets Movement Register (Fixed Assets Acquisition, Disposal, Accumulated Depreciation, etc) and calculate Depreciation Charges with a simple formula.
There are 3 lectures relating to Notes To The Accounts on Fixed Assets. This video is Part 1 of the series on Fixed Assets.
Creating Notes To The Accounts: Fixed Assets & Depreciation - Part 29:36
The notes to the financial statements are a required, integral part of a company's external financial statements.
You will learn how to create an automated and dynamic Notes on Fixed Assets using Functions and Formula. You will learn how to create a Fixed Assets Movement Register (Fixed Assets Acquisition, Disposal, Accumulated Depreciation, etc) and calculate Depreciation Charges with a simple formula.
There are 3 lectures relating to Notes To The Accounts on Fixed Assets. This video is Part 2 of the series on Fixed Assets.
Creating Notes To The Accounts: Fixed Assets & Depreciation - Part 319:08
The notes to the financial statements are a required, integral part of a company's external financial statements.
You will learn how to create an automated and dynamic Notes on Fixed Assets using Functions and Formula. You will learn how to create a Fixed Assets Movement Register (Fixed Assets Acquisition, Disposal, Accumulated Depreciation, etc) and calculate Depreciation Charges with a simple formula.
There are 3 lectures relating to Notes To The Accounts on Fixed Assets. This video is Part 3 of the series on Fixed Assets.
Creating Notes To The Accounts: Loan Calculation - Part 116:36
In this lecture, you will learn how to easily create a loan calculator to track and split the loan repayment into principal and interest portion.
There are 2 lectures pertaining to Notes To The Accounts on Term Loan. This video is Part 1 of the series.
Creating Notes To The Accounts: Loan Calculation - Part 214:52
In this lecture, you will learn how to easily create a loan calculator to track and split the loan repayment into principal and interest portion.
There are 2 lectures pertaining to Notes To The Accounts on Term Loan. This video is Part 2 of the series.
Creating Notes To The Accounts: Sales & COGS - Part 115:14
In this lecture, you will learn how to manage a set of raw data pertaining to Sales and COGS. In addition to applying Power Query skills learned in Module 1, you will also be exposed to other advance feature of Power Query in order to have a consolidated Sales and COGS dataset for further granular analysis on Sales and COGS performance.
There are 2 lectures relating to Notes To The Accounts on Sales and COGS. This video is Part 1 of the series.
Creating Notes To The Accounts: Sales & COGS - Part 222:50
In this lecture, you will learn how to manage a set of raw data pertaining to Sales and COGS. In addition to applying Power Query skills learned in Module 1, you will also be exposed to other advance feature of Power Query in order to have a consolidated Sales and COGS dataset for further granular analysis on Sales and COGS performance.
There are 2 lectures relating to Notes To The Accounts on Sales and COGS. This video is Part 2 of the series.
Functions & Formulas

Updating Year 4 Data To Financial Statement - Part 118:26
In this lecture, you will load the new Trial Balance on Year 4 into the model we built in earlier lectures. You will learn how to easily expand the financial reports to cover additional reporting years. The will also apply formulas such as INDEX, MATCH, etc to perform these tasks.
There are 2 lectures on Updating Year 4 Data to Financial Reports. This video is Part 1 of the series.
Updating Year 4 Data To Financial Statement - Part 27:06
In this lecture, you will learn how to update the financial mode with new Year 4 data and see the automation at works!
There are 2 lectures on Updating Year 4 Data to Financial Reports. This video is Part 2 of the series.
Managing Future Years Data

Requirements

Basic Microsoft Excel knowledge required
Microsoft 365 or Microsoft Excel 2016, 2019 loaded on computer to follow along with Instructor lectures is recommended
Willingness to learn a new concept to automate your reports

Description

Are you working overtime, spending too much time on non value adding tasks to prepare reports?

You are looking for a hands-on, step by step course to help you automate your financial reporting?

You've found it here!

Microsoft Excel: Automating Financial Reports with Power Query is a custom-made online course for Business and Finance Professionals i.e. Accountants, Finance Managers, Finance Analysts, Accounts Executives, students, etc to automate the time consuming financial reporting process.

The course is based on a Case Study developed to address various practical issues encounter during the preparation of financial reports. It offers clear and simple ways to automate financial reports using Functions and Formulas, Pivot Tables and various tips and tricks in Power Query. Once you have grasp the automation concept, you can easily replicate and apply it to other type of reporting as well.

After completing this course, you will be able to:

use Microsoft Excel and Power Query to automate the preparation of financial reporting;
learn the most relevant formula & functions and Power Query skill required by Business and Finance Professionals;
save time from non value adding routine tasks and channel the time saved to perform more value adding analysis tasks;
impress your bosses with timely and highly accurate reports

Teaching methodology:

Video: 22 videos designed to explain the concept with practical step by step instructions to implement the automation of financial reports using Microsoft Excel and Power Query
Downloadable Excel files: data, practice files and solutions used in each lecture
Quizzes: To revise and confirm your understanding of each module learned

The course has 4 Modules divided into 22 Lessons with 5 hours of video time. You can download the working files to practice along. There will be quizzes at the end of each module to assess your understanding of the lessons learned.

The overview of the course is as follows:

Module 1

The objective of Module 1 is to learn how to manage messy raw data. You will learn how to upload and cleanse messy 3-Years Trial Balance and transform it into a database friendly format for ease of automation. You will learn how to perform these tasks using Power Query.

Lecture 1: Introduction

Lecture 2: Load & Transform Data: 3 Year Trial Balance

Lecture 3: Load & Transform Data: GL Mapping Table

Lecture 4: Merge Query: GL Map to 3-Year Trial Balance

Lecture 5: Load & Transform Data: Date Mapping Table

Lecture 6: Merge Query: Date Map to 3-Year Trial Balance

Module 2

In this module, you will automate the creation of Income Statement and Balance Sheet using Pivot Table method. You will learn how to easily automate the financial reports with just a few clicks of the buttons.

Lecture 7: Creating Income Statement & Balance Sheet using Pivot Tables - Part 1

Lecture 8: Creating Income Statement & Balance Sheet using Pivot Tables - Part 2

Lecture 9: Creating Income Statement & Balance Sheet using Pivot Tables - Part 3

Module 3

The objective of Module 3 is to automate the creation of a full Financial Statements comprising of Income Statement, Balance Sheet, Cash Flow Statement and Notes To The Accounts using Functions and Formula method. You will learn how to easily automate the create a dynamic financial reports with just a few simple and effective formulas.

Lecture 10: Creating Income Statement & Balance Sheet using Functions & Formulas - Part 1

Lecture 11: Creating Income Statement & Balance Sheet using Functions & Formulas- Part 2

Lecture 12: Creating Cash Flow Statement using Functions & Formulas - Part 1

Lecture 13: Creating Cash Flow Statement using Functions & Formulas - Part 2

Lecture 14: Creating Notes To The Accounts - Fixed Assets & Depreciation Calculation - Part 1

Lecture 15: Creating Notes To The Accounts - Fixed Assets & Depreciation Calculation - Part 2

Lecture 16: Creating Notes To The Accounts - Loan Calculation - Part 1

Lecture 17: Creating Notes To The Accounts - Loan Calculation - Part 2

Lecture 18: Creating Notes To The Accounts - Sales & COGS - Part 1

Lecture 19: Creating Notes To The Accounts - Sales & COGS - Part 2

Module 4

Module 4 is about embedding future years data into the automated model we developed in Module 1 to 3. You will see how easily it is to load new dataset into the model and appreciate thee time saved from automation.

Lecture 20: Updating Year 4 Data To Financial Statement - Part 1

Lecture 21: Updating Year 4 Data To Financial Statement - Part 2

Unleash the power to automate your financial statements and reports.

Take actions now to acquire skills that will elevates you from your competitors!

Click the "Buy Now" button now to make change happens!

Who this course is for:

Business and Finance Professionals (Accountants, Finance Managers, Finance Analysts, Accounts Executives, students, etc) who use Microsoft Excel to analyze data and create reports
People who like to learn how to create interactive and dynamic reports with Microsoft Excel
People who are eager to save time by automating recurring and non value tasks
Graduate students who aspire to become an efficient financial advisory professionals, consultants etc.

Excel: Automating Financial Reports with Power Query

What you'll learn

Explore related topics

Course content

Introduction1 lecture • 1min

Module 1: Data Manipulation5 lectures • 48min

Module 2: Automating Reports using Pivot Table3 lectures • 53min

Module 3: Automating Reports using Functions and Formula11 lectures • 2hr 54min

Module 4: Managing Future Years Data2 lectures • 26min

Requirements

Description

Who this course is for: